SQL Server Log Shipping From Mirrored DB To Mirrored DB

Question

I'm attempting to setup a Hybrid Cloud (private to AWS) HA Sql solution with SQL Server 2014 Standard Edition (not my first choice, but was the requirement that was given too me).

I'm wondering if it is possible and/or a best practice to log ship to a secondary mirrored set. In other words, I would configure two sets of mirrored databases and log ship between set a and set b. The configuration would be:

Server A <-Mirror->Server B---Log Ship->Server C<-Mirror->Server D

Or, the other option is to log ship into a single instance and enable mirroring on fail-over:

Server A <-Mirror->Server B--Log Ship->Server C

P.S. I know there are other HA options with SQL Server 2014; however, I'm not prepared to pay Enterprise Edition prices. I'm going to pay development cost to move to MySQL (replication)

Answer 1

O.k. After much research and trial and error, I've discovered that the pattern that can be followed for a log ship mirror.

First, read this technical article from MS: Database Mirroring and Log Shipping (SQL Server)

The basic steps are to:

1. Configure Mirroring on Server A and B
2. Configure backup log shipping on Server A
3. Manually fail over to server B and configure backup log shipping
4. Fail back to Server A if desired
5. Configure log shipping restore jobs on Server C and Server D (this will keep them 'transactionally' in sync)

On a "failure event" (failover to Server C and Server D):

1. Manually restore the log shipping logs on Server C and Server D (or wait for the log shipping restore job to run) and disable Log Shipping Restore Job
2. Bring Server C out of 'Recovering' mode RESTORE DATABASE <db name> WITH RECOVERY
3. Configure Mirroring on C and D

Note: This was tested on Sql Server 2012