[Best Practices] SQL Server migration to Amazon RDS

We talked about availability and security best practices on Amazon RDS. Completing the series of posts, today we will know about Migration on Amazon RDS using SQL Server.

In this article, best practices will be defined for the Native Backup/Restore and AWS data 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 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. If 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 should run.

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 Service

AWS Database Migration Service (DMS) helps migrate databases to AWS. The source database remains fully operational during the migration, minimizing downtimes.

It uses CSC (Change data capture) technology and reads the SQL Server transaction log to extract DML. Then it applies to the target.

Through the parameters, DMS lets to do a full database backup restore and then tell 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 Maximum number of tables to load in parallel

By default, AWS DMS loads eight tables at a time.

To change this number in the AWS Management Console, open the console, choose Tasks, choose to create or modify a task, and then choose Advanced Settings. Under 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.

Agree, disagree? Comment below!

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *