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).