Failover strategy for SQL Server 2016 Standard Edition

by user9516827   Last Updated August 13, 2019 20:06 PM - source

I am using SQL Server 2016 Standard Edition.

I have two database servers, SQL01 and SQL02 with only one database, and I am utilizing transactional replication for bringing data from SQL01 to SQL02. Users should be able to read from database in both server at any time and transactional replication allows that.

I am thinking of a failover strategy to failover to SQL02 and then back to SQL01 if there is such a situation (patches/maintenance).

I know that transactional replication is not a HA solution, but since I am using SQL Server 2016 Standard edition I have Log shipping/Database mirroring options only.

Current failover strategy for a patch/maintenance in SQL01:

  • Transactional Replication on from SQL01 to SQL02.
  • So point the application to SQL02. Now new data comes into SQL02.
  • To keep SQL01 in synch with SQL02, restore full backup of the database from SQL02 and keep the SQL01 initialized.
  • Implement Log Shipping from SQL02 to SQL01.

For me this looks like a solution I can use. Possible issues I see are:

  • My database is like 2TB, so a backup of that in SQL02 will require 1 hour and restore in SQL01 will require 3 hours. Maybe I can avoid that by using a previous-night full backup for SQL02 and restore that at an earlier time. Please comment if it is okay to do that.
  • Other issue is, if I have regular transactional log backups of a SQL02 database (not from log shipping), would that also be restored by log shipping?

Do you see any issues other than these?

Please add your suggestion(s).

Related Questions

Transaction Replication as DR solution

Updated June 12, 2019 17:06 PM

LogShipping replicated database

Updated April 09, 2018 08:06 AM

Log Shipping and Transactional Replication

Updated May 23, 2019 16:06 PM

Publication shows up in secondary server

Updated September 24, 2018 16:06 PM