We talked about availability and security best practices on Amazon RDS. Completing the series of posts, today we will learn about migration to Amazon RDS using SQL Server.
In this article, we will outline the best practices for the Native Backup/Restore and AWS Database Migration Service (AWS DMS).
But let’s first learn what Amazon RDS is. Why do you even need to migrate to Amazon RDS? And what benefits does it contain for you?
What is Amazon RDS?
Amazon Relational Database Service (Amazon RDS) is an Amazon Web Services managed SQL database service.
Amazon RDS assists with database management chores like migration, backup, recovery, and patching by supporting a variety of database engines for storing and organizing data.
AWS users can utilize the AWS Management Console, Amazon RDS APIs, or the AWS Command Line Interface to manage Amazon RDS.
Why You Should Migrate to Amazon RDS?
On Amazon Web Services, Amazon RDS is a fully managed solution for running relational databases (AWS).
It supports six different database engines, including commercial options like SQL Server and Oracle.
Not convinced?
AWS manages your database operations using Amazon RDS, allowing your team to focus on innovation and sustainability.
Amazon RDS takes care of instance failover, data backups, and software updates, allowing you to take advantage of the AWS Cloud’s efficiency for a quick and dependable database solution.
Once you know what Amazon RDS is and why you should use it, let’s move to the best practices of SQL Server migration to Amazon RDS.
Native backup/restore is straightforward
The database is backed up to a file that is uploaded to Amazon S3. Then a stored procedure is called, passing as 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.
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 CDC (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 the user do a full database backup restore and then tells 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.
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.
Summary
In this guide to migrating the database to AWS, we learned that Amazon Web Services (AWS) is a fully managed solution for running relational databases. The database operations are managed using Amazon RDS, allowing your team to have some extra time for innovation and creativity.
We also learned best practices to migrate the SQL databases to RDS. These practices involve the first step of backing or restoring the database by enabling backup compression, shrinking transaction logs, and using encrypted storage. Second, they involve using an AWS Database Migration Service for migrating databases to AWS.
Frequently asked questions
How do I import data into Amazon RDS?
\copy command to import data to Amazon RDS when importing data into a PostgreSQL DB instance.
How do I migrate to Amazon RDS for SQL Server using transactional replication?
- Log in to the on-premises SQL Server with a login associated with the sysadmin server role.
- Navigate to the Replication folder in SQL Server Management Studio.
- Right-click on the Replication folder and select Configure distribution.
- Follow the steps exactly as they are written.
