We talked about availability and security best practices on Amazon RDS. Completing the series of posts, today we will learn about migration on Amazon RDS using SQL Server.
In this article, best practices will be defined for the native backup/restore and AWS Database Migration Service.
Native backup/restore is straightforward
The database is backed up to a file that is uploaded to S3. Then a stored procedure is called passing as basically the bucket name and the file name and AWS restores it.
Enable compression
Backup compression should be enabled.
Compression algorithms have gotten better, they do not consume as much CPU as in the older versions.
When the compression ratios are good, this can save storage costs and perform significantly faster on restoring huge databases (like 9TB in size).
Shrink transaction log
We also recommend shrinking the transaction log file before a backup. It will save time when instant file initialization runs.
Use encrypt storage
Even if an instance is not running on encrypted storage, if Transparent Data Encryption (TDE) is not used, you can just pass an additional parameter to the stored procedure and it will get encrypted.
Database Migration Services (DMS)
AWS Database Migration Services (DMS) helps migrate databases to AWS. The source database remains fully operational during the migration, minimizing downtimes.
It uses Change Data Capture (CDC) technology and reads the SQL Server transaction log to extract DML. Then it applies to the target.
Through the parameters, DMS allows a full database backup restore and then instructs DMS to start applying changes from a particular Log Sequence Number (LSN) in Microsoft SQL Server.
The service is essentially just reading tables, creating cursors on target, and bulk inserting.
Change the maximum number of tables to load in parallel
By default, AWS DMS loads eight tables at a time.
Change this number in the AWS Management Console as follows:
- Open the console
- Select Tasks
- Choose to create or modify a task
- Select Advanced Settings
- Go to Tuning Settings
- Change the Maximum number of tables to load in parallel options:
- Increase this number when using a very large replication server (dms.c4.xlarge or larger instance) to boost performance
- However, if your replication server is relatively small (such as a dms.t2.medium), we recommend that you reduce the number of tables loaded in parallel.
Disable transaction logging and backups
When migrating to an Amazon RDS database, disable backups and Multi-AZ on the target until you’re ready to cut over.
Similarly, when migrating to non-Amazon RDS systems, disabling any logging on the target until after cutover is usually an excellent choice.
Optimizing change processing
AWS Database Migration Service processes change in a transactional mode (by default) which preserves transactional integrity.
If you can afford temporary lapses in transactional integrity, you can use the batch-optimized apply option instead. This option efficiently groups transactions and applies them in batches for efficiency.
Using the batch optimized apply option almost always violates referential integrity constraints, so disable these during the migration process and enable them again as part of the cutover process.
