0

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 ?

3

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?

1
  • Usually (always check with your MSFT rep) the DR server is free, as long as it's not used for anything. So your AG secondary is free providing that no one does any work on it outside of keeping it ready for it's role as secondary. – Jonathan Fite Jan 15 '20 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.