-
SQL Server on AWS RDS/EC2? Read Replicas ?
DMS/migration | scale down to SE with HADR | geo-replication | offload of primary | data locality
RDS SQL Server Snapshot-Restore
The purpose of this resource is to facilitate seamless initial replication/seeding when the CLOUDBASIX semi-automatic backup-restore method of replication is employed. CLOUDBASIX handles SQL Server zone-to-zone (Multi-AZ with readable-replicas) and cross-region (Geo-Replicate) replication continuously, achieving a near real-time replication with potential data loss in the seconds for DR scenarios. It does not rely on RDS snapshotting, which would result into substantially larger potential data loss.
When choose to handle initial replication seeding via the Semi-Automatic method:
two options are available:
- Initiating replication jobs per database, one by one, in which case you would need to initiate initial seeding for each database, followed by backup each database via S3 and restore into the replica RDS.
- Select to handle initial seeding for all databases on the source RDS at once, in which case, after initiating an [X] (All) replication, you would need to follow steps to snapshot the RDS and restore into a new replica instance.
If planning to restore a snapshot into an existing RDS, you can test connectivity to the replica RDS. Otherwise, if the replica RDS does not exist, select [X] Replica Server does NOT exist yet.
At this point you can click [Start Replication], which will take you to /Replications/Runtime Logs.
All databases form the source RDS will be placed in a queue to be pre-processed. The header bar will be showing "Initialization in progress...Backup/Snapshot source DB(s)/RDS after this process completes." until statuses of all pending jobs turn to AwaitingBackupRestore. A job status turning to AwaitingBackupRestore also indicates that a job entry under /Replications/Replication Jobs is created successfully (with Data Replication = off)
Wait until this process completes, which will be indicated by the header showing button [Finalize All]
At this time you must take the RDS snapshot. It is possible to also use the scheduled nightly snapshot which will take place after that point. This however will extend the overall time.
After restoring the snapshot, click [Finalize All] in the header bar. In the popup populate the replica server connection, Test Connection, then click [Finalize All] in the popup.
At this time all jobs in AwaitingBackupRestore status will be activated (Data Replication under /Replications/Replication Jobs will be turned to ON), and processing of pending changes for all jobs will begin.
Follow/Replication/Runtime Logs and /Replication/Analyze Report to confirm successful processing.