Data Loss and data redeundancy when forced failed over to secondary replica on DR Data Center in SQL Server 2016
0
votes
0
answers
49
views
We are using SQL Server 2016 Enterprise edition and planning to have 2 datacenters primary and the remote Disaster recovery(DR). We have 2 replicas (primary replica and secondary replica) in the primary datacenter and it is running under synchronous-commit mode with automatic failover to provide high availability. The third replica (secondary replica) is in a disaster recovery datacenter and is running under asynchronous commit mode to provide datacenter level disaster recovery. I have below questions with respect to data integrity in case of forced manual failover when whole primary datacenter is completely down.
Scenario 1:
Say we have table named Table1 and it has 4 records; Record 1,2,3 and 4 whereas record4 was committed on Table1 on primary replica and before committing to secondary data center; whole server was down. How to Sync these data once the Primary datacenter is up again. What is the process we need to follow and is there any automated way to do this.
Scenario 2:
Say I make DR as primary replica and as there are no Record4 in DR replica and it will start insert Record4 with different data. Even though Id is same for Record4 but the data in Primary data center and secondary data center for Record4 is different and how to sync it as lot of other tables depends on Record4. I am thinking to reseed DR to some number say 100 and then changed DR to primary one. Is there any automated or alternate better way to do this.
What is your experience on above scenarios. Can you please provide some suggestions?
Asked by Manoj Jeganathan
(1 rep)
Apr 1, 2024, 09:09 AM
Last activity: May 3, 2024, 11:21 AM
Last activity: May 3, 2024, 11:21 AM