Promote to Primary

 

Promote RDS/SQL Server Read-Replica to Primary

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. See details below.

Important:
In a non-planed fail-over scenario, If access to the primary source is lost, there will be
no option to run discrepancy analysis, to determine if the fail-over will result into data loss.

Important: The ongoing replication processes do not maintain replica standby databases execution 
plans/statistics. If table statistics are not up to date, upon fail-over, the performance of 
the newly promoted databases might be poor.

If statistics have not been maintained properly prior to fail-over, you must update table 
statistics to optimize execution plans/performance.

Important:
Replication of data is one-way. Upon promote-to-primary/fail-over, the newly promoted primary 
server becomes primary, new updates to the new primary will not be synchronized back to the 
previous primary when/if access is restored. Upon promote-to-primary, the replication jobs 
are disabled, and no further data replication takes place.
To fail-back to the original primary region/zone, you must initiate a new set of replication 
jobs, from the new primary region/zone to the original primary region/zone (new server(s) 
required as replica databases must not exist), followed by planned cut-over. During a planned 
cut-over/promote-to-primary, as there is access to the primary server, must follow steps below 
(see planned fail-over) to ensure the fail-back is performed with no data loss.

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:

Version 12.348 and above:

The Promote-To-Primary process allows optional enabling of constraints with NOCHECK (not validating existing data referential integrity), which reduces overall time to complete the process.
Important: Utilizing this option is not recommended, but can be useful during unplanned fail-over of very large database scenarios, where validating referential integrity takes substantial time and the standby database must become operational as the primary database is totally lost and inaccessible.

Option to ignore inaccessible source connection, discrepancies, and forcing running processes to be canceled, data replication turned off, are available. If selecting to turn off data replication for primary and secondary jobs, requirement is to have  ignoreInaccessibleSourceConnection=true, ignoreDbDiscrepancies=true & cancelRunningCtProcess=true.
Important: This is generally applicable to non-planned fail-over scenarios, where the primary DB is not accessible.

POST /api/PromoteDbReplicaToPrimary
{
"replicationId": "998acaec-9211-4dac-b90b-xxxxxxxxxx",
"ignoreInaccessibleSourceConnection" : "true",
"ignoreDbDiscrepancies" : "true",
"cancelRunningCtProcess" : "true",
"turnOffDataReplication" : "true", -- default = False; a value of True, which is generally applicable to non-planned fail-over scenarios, which forces data replication for all primary and secondary jobs to be turned off, requires ignoreInaccessibleSourceConnection=true, ignoreDbDiscrepancies=true & cancelRunningCtProcess=true

"enableConstraintsWithCheck" : "true"
}

Version 12.282-12.347:

In API, a new tag enableConstraintsWithCheck is added (see below) to  /api/PromoteDbReplicaToPrimary

POST /api/PromoteDbReplicaToPrimary
{
	"replicationId": "998acaec-9211-4dac-b90b-xxxxxxxxxx",
	"ignoreInaccessibleSourceConnection" : "false",
	"ignoreDbDiscrepancies" : "false",
	"cancelRunningCtProcess"  : "false",
       "enableConstraintsWithCheck" : "true"
}

Prior to version 12.282:

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 /Replications/Analyze (/Advanced/Analyze in versions 7.14 or earlier), select the respective database and press [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 \Replication\Replication Jobs, 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 CloudBasix server becomes inaccessible along with the primary SQL Server server (if primary region/zone is down, or VPN is misconfigured etc, and CloudBasix server was deployed in the region of the primary server against best practice recommendation) and  Promote to Primary can not be performed in the CloudBasix 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 CloudBasix 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];