Promote RDS/SQL Server Read-Replica to Primary
Applicable to SQL Server Migration Cut-Over and Read-Replica promotions to primary in DR scenarios.
In order to avoid data loss, follow below step:
1. Take the necessary measure on the applications' or DNS end to stop changes to the source SQL Server database being promoted. Then go to ReplicationAnalyze (AdvancedAnalyze in versions 7.14 or earlier), select the respective database and hit [Start Analyze]. The report should indicate if there are pending changes. You can apply a filter or look for tables highlighted in orange.
Note: if you see discrepancies (red color), without indication that the respective tables have no Primary Keys and therefore have not been tracked for changes (see prerequisites), that would indicated there are discrepancies and there will be data loss. One option is to request reseeding of the tables with discrepancies by clicking the [Reseed Table] button which will cause the table to be queued fir reseeding.
2. Wait for all pending changes to be synchronized.
3. Disable the data replication schedule (related column is called "Active" in version 7.14 and below, and "Data Replication" in 7.15 and above) for the database being promoted to primary.
4.Go to ReplicationReplication Schedules, check on the "Promote to Primary" checkbox of the replication being promoted to primary - enabling of constraints and triggers process is initiated.
5. If the process in (4) completes with status "All constraints and triggers are enabled", this completes the promotion to primary exercise.
Note that the "Analyze" report referred to in (1) & (2) compares table row counts only. If you need to confirm 100% data integrity, you need to run a data integrity check.