Promote to Primary

Documentation

Applicable to SQL Server Migration Cut-Over and Read-Replica promotions to primary in DR scenarios.

If primary SQL Server is lost (due to a zone/region outage, VPC/Private Network configuration issues, instance is corrupted), the DR/read replica databases are immediately available to become primary. The DR set of applications can be pointed to the SQL Server read replica databases.

However to fully complete the process and prepare replica DBs to operate as primary, a Promote DB Replica To Primary operation must be performed on database level.

If performing the operation over the API (as part of automated fail-over), see /documentation/api/PromoteDbReplicaToPrimary and the disaster recovery automation architecture (Automated Cross-Region Disaster Recovery on Amazon RDS for SQL Server)

If performing the operation in the console UI, go to /Replications/Replication Jobs, check Promote To Primary:

If performing a planned fail-over, in order to avoid any risk of data loss, follow below steps:

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.

FAQ:

Q. What does the Promote to Primary process entail ? In the case where the CloudBasic server becomes inaccessible along with the primary SQL Server server (if primary region/zone is down, or VPN is misconfigured etc, and CloudBasic server was deployed in the region of the primary server against best practice recommendation) and  Promote to Primary can not be performed in the CloudBasic UI or over its REST API, can I promote the replica to the primary manually ?

A. "Promote To Primary" does the following:

1. Enables Triggers (kept disabled during continuous replication)
2. Removes NO CHECK from Constraints/FKs

Note: It does not just go and enable above for all (as some triggers and constrains might be disabled/with NO CHECK at the source). If source is reachable, it compares source and destination and enables objects accordingly. If source is unreachable, then it compares replica schema with the last known cached copy of the source schema.

Alter statements are generated and executed based on above schema comparison outcome.

If you cannot perform "Promote To Primary" in the CloudBasic UI or over its REST API (see , then you have to implement above manually against the replica being promoted to primary manually.

Sample script for manual promotion to primary:

ALTER TABLE [dbo].[AspNetUserClaims] WITH CHECK CHECK CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId];
ALTER TABLE [dbo].[AspNetUserLogins] WITH CHECK CHECK CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId];
ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId];
ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId];
ALTER TABLE [dbo].[JobEntity] WITH CHECK CHECK CONSTRAINT [FK_RecurringJobEntity_JobType];
ALTER TABLE [HangFire].[JobParameter] WITH CHECK CHECK CONSTRAINT [FK_HangFire_JobParameter_Job];
ALTER TABLE [HangFire].[State] WITH CHECK CHECK CONSTRAINT [FK_HangFire_State_Job];