Does this plan work as a Failover strategy-SQL Server 2016 Standard Edition

Question

We have two SQL Server 2016 Standard edition (standard edition due to budget issues) configured one as primary sql server and another one as fail-over server. I have implemented Transactional replication from primary to secondary server.

To do some maintenance and patching in primary,we have decided to do failover to the other server.But since SQL Server is standard edition,we have no other option but to break replication.I know that Replication is not a Failover solution. Anyways after patching/maintenance is done we will switch the application back to primary server.But to bring the records which went to failovr server i am thinking to implement Log shipping.So i will restore a latest backup from fail-over to primary and then implement log shipping to synch with the data.

I don't get huge maintenance window in between,that is why i have to do this way. Do you think this plan will work ?

Answer 1

Do you think this plan will work?

Without a lot of headaches, no I don't. Most times I have witnessed an attempt to use replication for DR it has failed. Every time I've witnessed an attempt to 'fail-back' from a replication 'failover' it has failed miserably.

As you've mentioned, replication is not a HA/DR technology and shouldn't be used as such. If you're looking for a simple solution you can implement quickly, and you're going to log ship back to the primary for fail-back already, why not log ship to the secondary now and use an actual DR technology?

This gives you the option of performing tail log backups on the primary to leave it in a RESTORING state and makes fail-back much simpler. Your whole process is this -

1. Tail-log backup the primary and disable log shipping jobs on primary
2. Run the LSCopy and LSRestore jobs on secondary then apply tail-log backup to the secondary. Disable log shipping jobs.
3. Bring secondary Dbs online. Ensure a log backup job is running against the DBs on the secondary.
4. Point apps at secondary
5. Patch the primary
6. Tail-log backup the secondary
7. Apply logs and tail-log from secondary to primary
8. Bring primary online
9. Point apps at primary
10. Re-enable all log shipping jobs.

Lastly, if you're on SQL Server 2016 Standard Edition - implement Basic Availability Groups. They're available in Standard Edition, you're paying for an additional server anyway with Replication or Log Shipping, and the whole process is much, much simpler.

Since you have an outage for the apps to re-direct them to the secondary, why not take the opportunity to point them at an AG listener and eliminate this whole problem going forward?