SQL Server migration to Amazon RDS Strategies

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 data migration service. 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 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 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 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 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.

FAQs

How do I import data into Amazon RDS?

You can use MariaDB tools like MySQL dump, MySQL, and standard replication to import data to Amazon RDS when importing data into a MariaDB DB instance. Importing Data into PostgreSQL on Amazon RDS – You can use PostgreSQL tools like pg dump, psql, and the 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 the Replication folder and select Configure distribution. Follow the steps exactly as they are written.

Does AWS RDS support SQL Server?

Yes! setting up, operating, and scaling SQL Server deployments in the cloud is simple using Amazon RDS for SQL Server. Multiple editions of SQL Server (2012, 2014, 2016, 2017, and 2019), including Express, Web, Standard, and Enterprise, can be deployed in minutes with cost-effective and resizable computing capacity using Amazon RDS.

What is Amazon Web Services RDS replication and how does it work?

The Oracle DB engine supports replica databases in mounted mode, and all databases in the source DB instance are replicated via Amazon RDS. A mounted replica cannot serve a read-only workload since it does not accept user connections. Mounted replicas are typically used for disaster recovery across multiple regions.

What’s the difference between AWS Aurora and Amazon Relational Database Service (RDS)?

You can supply up to 15 replicas with Aurora, and replication takes milliseconds. RDS, on the other hand, enables only five copies and has a slower replication procedure than Amazon Aurora. The replicas on Amazon Aurora share the same logging and storage layers, making the replication process more efficient.

Summary

In this migrate the database to the AWS guide, 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. And second, the step of using an AWS database migration service for migrating databases to AWS. 

Agree, disagree? Comment below!

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 *