How to migrate to Amazon RDS for SQL Server using transactional replication: Part 1

You can migrate your databases into Amazon RDS for Microsoft SQL Server in multiple ways. Typically, you perform a simple backup and restore of the databases (along with scripting system objects such as logins). If you want a higher availability or lower downtime option, you might use the AWS Database Migration Service (AWS DMS). In this blog post, we describe a third mechanism, using transactional replication, to migrate your databases to RDS for SQL Server. Using this approach, you can move data to Amazon RDS for SQL Server without having to use a service offering and take advantage of your existing infrastructure.

RDS for SQL Server doesn’t support SQL Server replication. This is largely because the replication subsystem on SQL Server Agent isn’t running when hosted on an RDS for SQL Server instance. However, push subscriptions, where the SQL Server Agent services are running on either an on-premises or Amazon EC2–hosted instance of SQL Server, are supported. To the RDS for SQL Server instance, the arriving push subscription transactions are just individual Transact-SQL statements.

Using the methods in this blog post, you can set up replication to perform a one-time migration of one or more SQL Server databases into an RDS for SQL Server instance. This technique has a dependency upon the EC2 hostname, and a hostname can change either on a host replacement or a failover in a Multi-AZ (high availability) configuration. Thus, we recommend a single AZ configuration when replicating your data into AWS using this technique. After the replication process has completed, you convert the instance to Multi-AZ for high availability. Because of this limitation, we recommend that you use this only for a one-time migration of your data to RDS for SQL Server. You can learn how to migrate to an Amazon RDS for SQL Server Multi-AZ DB instance from the blog Migrating to Amazon RDS for SQL Server using transactional replication: Part 2.

For this blog post, we use an instance of SQL Server 2017 on Microsoft Windows Server 2016 running on an EC2 host. (This setup is called the “source SQL Server” for the rest of the post.) This blog post assumes a working knowledge of SQL Server transactional replication.

Start by enabling distribution on the source SQL Server

To begin, enable distribution on your source SQL Server (if that’s not already configured). In SQL Server Management Studio (SSMS), open the context (right-click) menu on the replication tree entry and choose Configure Distribution. You might get an error about your server name not matching, which can happen if you rename your computer after SQL Server is installed. If you do, run the following script:

Exec sp_dropserver ‘oldname’;
Go
Exec sp_addserver ‘newservername’,local;
Go

Then restart the SQL Server service and try again. Follow the wizard, making your local server a distribution server and creating a distribution database.

On the RDS for SQL Server side, ensure that the security group or groups allow SQL Server connectivity from your EC2 or on-premises SQL Server system. Security groups allow connectivity on TCP port 1433 by default, or on another port as configured. You can validate connectivity by connecting to your RDS for SQL Server instance from SSMS on your EC2 or on-premises SQL Server system. (We call this system the publisher going forward.)

Create the RDS database and database objects

From this point, you take these steps:

1. Create the RDS database
2. Script out your database from on-premises and recreate it on RDS for SQL Server
3. Create publications on-premises for each table
4. Set up a push subscriber to RDS for SQL Server

Create the database on your RDS server as normal in SSMS. In our case, we replicate the database REPLTEST, and we use the same database name on the RDS for SQL Server instance.After the database is created, run a script to recreate the database objects on the RDS for SQL Server database. If you don’t already have a script, you can use the Generate Scripts Wizard in SQL Server. To run this, open the context (right-click) menu for your database, choose Tasks, Generate Scripts, and then follow the instructions. Script out all of your database objects, and then run that script on your RDS for SQL Server instance.At this point, your database and all database objects exist on your RDS for SQL Server instance. Now it’s time to transfer the data into your RDS environment.

Transfer data

Back on your source SQL Server system, your next step is to create a publication for each table with the data that you want to migrate to the RDS environment. To do so, open the context (right-click) menu for Local Publications under the Replication node on your source server, and then choose New Publication.

When the wizard launches, choose Next, then select your database (REPLTEST in our example). Then choose Next, and choose Transactional publication for the publication type. On the Articles page, select all objects that you want to replicate. Assuming you have primary keys on all your tables, you might select the vast majority of your objects to replicate, or perhaps just the tables are enough. For this blog post, we just replicate the tables so the data is moved.Choose Next again, and Next another time to skip filtering table rows. The database used for this blog post is trivial, so I create a snapshot immediately (doing a bulk copy of the initial data in the table). You might choose to schedule the initial snapshot to occur when your system is less busy or during a maintenance window.Use either your service account or a custom account for the snapshot agent and the Log Reader Agent, then choose Next. Ensure that Create the publication is checked, and choose Next. Name the publication something meaningful for you, like MigrateToAWS. Then choose Finish.When the New Publication Wizard has completed successfully, choose Close.

Now that the tables are published, our next step is to create a PUSH subscription to push the data into your RDS for SQL Server instance. Open the context (right-click) menu for Local Subscriptions in SSMS on your source server, and choose New Subscriptions to launch the New Subscription Wizard. Choose Next, then select the publication you just created, in our case MigrateToAWS.Choose Next, and then select the option to Run all agents at the Distributor to enable the PUSH subscription. Choose Next, and then choose Add Subscriber to add your link to your RDS for SQL Server instance. Add the connection to your SQL Server, using credentials that have permissions to create objects, and then load data in your RDS for SQL Server database. Choose Connect. You then get the error shown in the dialog box following. This is expected.SQL Server replication requires that the server name be the same as the Windows Server computer name. The actual name of the Windows server hosting your RDS for SQL Server instance is helpfully returned in the error message.

To address this, we get the IP address of the RDS for SQL Server installation. Open a command window on the source SQL Server system (or your desktop) and run the following command:

Nslookup <<RDS SQL Server>>

In our blog example, I ran the following:

Nslookup rwsqlserver.cb2xxxxxxx.us-west-2.rds.amazonaws.com

Nslookup returned 203.0.113.14  as my address. Now, navigate in Windows Explorer to the Windows system32/drivers/etc directory on your local computer. Edit the hosts. file with something like Notepad. Add an entry for your server, in our case:

203.0.113.14    EC2AMAZ-SL55HDG

Then close your text editor, saving your changes.

Now go back to SSMS, and cancel the error message. Change the Server Name value to match the EC2 hostname expected, then choose Connect again. Now the connection succeeds. The subscriber connection is now in the Subscribers list. Choose Next.

To set security for the connections, in the Distribution Agent Security dialog box, choose the ellipsis button next to your RDS for SQL Server name.For your Distribution Agent security, choose your Windows account or use the service account for SQL Server Agent to connect to your server. For the subscriber, specify your RDS for SQL Server login information. Choose OK, then choose Next. For the synchronization schedule, accept the default of Run Continuously and choose Next. For the Initialize Subscriptions option, you can either perform synchronization of the data immediately or at a later point. Alternatively, you can run a manual initialization (such as taking a full database backup and restoring it on the RDS for SQL Server). For this blog post, we accept the default of an immediate synchronization. For more about your options for replication synchronization, see Initialize a Transactional Subscription Without a Snapshot in the Microsoft documentation.

Choose Next, accept the default to Create the subscription, then choose Next and Finish. SSMS takes the snapshots and starts the snapshot agent on your source system. Choose Close.

Doing this begins the replication process.

Finish the migration process

After the synchronization is complete, validate that your objects exist on the RDS for SQL Server instance. When the two databases are in sync, stop activity on your on-premises or EC2 instance and ensure that replication has completed. Then you can cut over your production workloads to the RDS for SQL Server instance. Finally, stop the push subscription and delete it, and start using your Amazon RDS for SQL Server installation as your production server.

Using SQL Server replication is one of several possible ways to migrate to use Amazon RDS for SQL Server. Using the techniques outlined in this blog post, you can migrate with minimal downtime using your existing SQL Server software capabilities.

In part 2, you will learn how to maintain continuous transactional replication from an on-premises or Amazon EC2 hosted SQL Server instance to an RDS for SQL Server DB instance in the Multi-AZ configuration when a host replacement occurs during maintenance activities or failover events.

About the Author

Richard Waymire is a principal database specialist solutions architect at Amazon Web Services. He works with AWS customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.