Configure SQL Server replication between Amazon RDS Custom for SQL Server and Amazon RDS for SQL Server

Amazon Relational Database Service (Amazon RDS) Custom for SQL Server is a managed database service for legacy, custom, and packaged applications that require access to the underlying operating system (OS) and database (DB) environment. Amazon RDS Custom is now available for the SQL Server database engine. Amazon RDS Custom for SQL Server automates setup, operation, and scaling of databases in the cloud while granting access to the database and underlying operating system to configure settings, install drivers, and enable native features to meet the dependent application’s requirements.

In this post, you setup a SQL Server transactional replication between Amazon RDS Custom for SQL Server (Publisher) and Amazon RDS for SQL Server (Subscriber) using SQL Server running on Amazon Elastic Compute Cloud (Amazon EC2) that acts as distributor. You can use this solution in use cases such as moving Production Data to Test and Development environments and migration parts of Databases to new SQL Server instances.

Overview of SQL Server replication

SQL Server transactional replication is implemented by the replication agents Snapshot Agent, Log Reader Agent, and Distribution Agent.

The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database on the Distributor.
The Log Reader Agent monitors the transaction log of each database configured for transactional replication, and copies the transactions marked for replication from the transaction log into the distribution database, which acts as a reliable store-and-forward queue.
The Distribution Agent copies the initial snapshot files from the snapshot folder and the transactions held in the distribution database tables to Subscribers.

Incremental changes are made at the Publisher flow to Subscribers, according to the schedule of the Distribution Agent. The Distribution Agent can run continuously for minimal latency, or at scheduled intervals.

Figure 1 shows the principal components of transactional replication.

Figure 1: Principal components of transactional replication

Infrastructure setup details

For your initial setup, you need one instance of Amazon RDS Custom for SQL Server (Publisher), Amazon RDS for SQL Server (Subscriber), and a SQL Server instance running on Amazon EC2 (Remote Distributor), as shown in Figure 2.

Figure 2: Initial set up diagram

Prerequisites

Before we begin, we assume that you meet the following prerequisites:

Background knowledge about MS Replication
AWS CLI installed and configured
knowledge on how to setup and launch RDS for SQL Server and RDS for Custom SQL Server instances.

For more information, refer to Creating and connecting to a DB instance for Amazon RDS for custom SQL Server , Creating and connecting to a DB instance for Amazon RDS for SQL Server, and
Microsoft SQL Server Replication.

Since this solution involves AWS resource setup and utilization it is going to incur the costs on your account, refer to AWS Pricing for more information.
We strongly recommend that you set this up in a non-production instance and run end-to-end validations before you implement this solution in a production environment.

Instance creation

The following example shows you how to launch instances that are running on the same Amazon Virtual Private Cloud (Amazon VPC), subnet, and security group. If you want to follow along, you must create an Amazon RDS subnet group and a security group. Take note of your database subnet group name as well as your security group id as we’re going to use them on the next steps.

Overview:

Create an RDS for SQL Server instance
Create RDS Custom for SQL Server
Create SQL Server on windows EC2 Instance

1. To create an RDS for SQL Server instance

Use the following AWS CLI command to create RDS SQL Server instance with the name rdssqlprimary. In the following example we have specified db subnet group (RDS-custom-sng) and security group (sg-0ab05f9777265061a) from our environment. You must replace it with your db subnet group name and security group id.

aws rds create-db-instance \
--engine sqlserver-ee \
--engine-version 15.00.4073.23.v1 \
--db-instance-identifier rdssqlprimary \
--db-instance-class db.m5.4xlarge \
--allocated-storage 1000 --storage-type io1 --iops 10000 \
--master-username admin \
--master-user-password <XXXXX> \
--license-model license-included \
--db-subnet-group-name RDS-custom-sng \
--vpc-security-group-ids sg-0ab05f9777265061a \
--publicly-accessible \
--region us-west-2

For more details, see Creating and connecting to a DB instance for Amazon RDS for SQL Server.

2. To create an RDS Custom for SQL Server instance

Use the following AWS CLI command to create an RDS Custom SQL Server instance with the name rdssqlcustom1. In the following example we have specified db subnet group (RDS-custom-sng) and security group (sg-0ab05f9777265061a) from our environment. You must replace it with your db subnet group name and security group id.

aws rds create-db-instance \
--engine custom-sqlserver-ee \
--engine-version 15.00.4073.23.v1 \
--db-instance-identifier rdssqlcustom1 \
--db-instance-class db.m5.4xlarge \
--allocated-storage 1000 --storage-type io1 --iops 10000 \
--master-username master \
--master-user-password <XXXXX> \
--kms-key-id <kms-key-id> \
--custom-iam-instance-profile RDSCustomIAMProfile \
--db-subnet-group-name RDS-custom-sng \
--vpc-security-group-ids sg-0ab05f9777265061a \
--publicly-accessible \
--region us-west-2

For more details, see Creating and connecting to a DB instance for Amazon RDS Custom for SQL Server.

3. To create an EC2 Instance with SQL Server running on it

Open the Amazon EC2 console.

Choose Launch Instance.
In Step 1: Choose an Amazon Machine Image (AMI), for this example use Microsoft Windows Server 2019 with SQL Server 2019 ami-0bae68af8d6df6b7f.
Note: If you can’t find the exact AMI , select the one which is applicable to your region.
In Step 2: Choose an Instance Type, choose Next: Configure Instance Details. For this example, choose m5.xlarge instance type.
In Step 3: Configure Instance Details, provide the following information:
- For Network, choose the entry for the same VPC that you have specified for RDS SQL Server and RDS Custom SQL Server.
- For Subnet, choose the same subnet as RDS SQL Server and RDS Custom SQL Server.
- Set Auto-assign Public IP to Enable.
Choose Next: Add Storage, change the root storage size to 1000 GiB and volume type to GP2.
Choose Next: Add Tags.
Enter a name for your instance and choose Next: Configure Security Group.
Choose the same security group used on the creation of your RDS for SQL Server and RDS Custom for SQL Server DB instances.
Choose Review and Launch.
Choose Launch.
Select the check box for the key pair that you created, and then choose Launch Instances.

Preparation steps

1. Enable Remote Desktop Protocol (RDP) on RDS Custom SQL Server

To connect RDS Custom for SQL Server instances, follow the instructions to enable Remote Desktop Protocol (RDP).

2. Pause RDS Custom automation temporarily

Follow the instructions to pause RDS Custom automation for the duration of the replication setup, so that RDS automation doesn’t interfere with the configuration tasks that you perform.

3. Validate @@SERVERNAME is set to correct value

For SQL Server running on all the three nodes (Publisher, Subscriber, and Distributor), Host name can change in some scenarios like a host replacement on RDS, Failover in a Multi-AZ (high availability) configuration. Make sure that @@SERVERNAME is set to correct value .

4. Validate SQL Server Agent is running on Publisher, Subscriber, and Distributor

Make sure SQL Server Agent is running on Publisher, Subscriber and Distributor. If not start the agent.

5. Use server name instead of RDS SQL Server endpoints

If you are using endpoints to create Publication, you might run into the following Cannot connect error “SQL Server replication requires the actual server name to make a connection to the server”, as shown in Figure 3.

Figure 3: Cannot connect error.

To resolve the issue:

Check the Amazon RDS Custom for SQL Server name using select @@servername.
Run xp_readerrorlog and look into SQL Errorlog and you should find that SQL Server Name is different, as shown in Figure 4.

Figure 4. SQL Server error log, server name is RDSAMAZ-V26S6JH.
To resolve this, there are two options.
1. The first option is to open SQL Server Configuration Manager and create Alias Name, EC2AMAZ-GF313QT in our case, on Port 1433, as shown in Figure 5.
  
  Figure 5: Create Alias
  
  Do the same for Distributor and Subscriber as well on the client machine where you are going to setup transactional replication.
2. The second option, if you are not comfortable creating Aliases, is that you open the command prompt and use the nslookup command for the RDS endpoint. It returns the IP address of the RDS instance, as shown in Figure 6.
  
  Figure 6. IP address of the RDS instance

Next, you open the host file by navigating to the path C:\Windows\System32\drivers\etc\hosts. In this host file, map the RDS instance IP address and hostname as shown in Figure 7.

Figure 7: Map the RDS instance IP address and hostname

Repeat to add Distributor and Subscriber details in Host file.

Note: Because IP address may change during any patch upgrade or host replacement, the first option of creating aliases through SQL Server Configuration Manager is preferred.

After you connect to SQL Management Studio, it should look like Figure 8.

Figure 8: Subscriber, Publisher, and Distributor in SQL Management Studio

6. Network and communication setup ( Optional step if done earlier)

Use SQL Server Management Studio (SSMS) to make sure that you are able to connect to both RDS Custom SQL Server (Publisher), and Amazon RDS for SQL Server (Subscriber) from SQL Server instance running on Amazon EC2 (Remote Distributor). This might include settings of VPC Security groups, routing tables and Windows Firewall rules. For the example in this post, we launched all the three instances using same VPC and Security group.

On the Amazon EC2 (Remote Distributor) instance, do the following:

Open cmd, nslookup <rds-sqlserver-endpoint>,nslookup <rds-custom-sqlserver-endpoint> to get their IP addresses.
Run xp_readerrorlog and look into SQL Error log to get the hostname.
Navigate to the folder path C:\Windows\System32\drivers\etc.
Edit the host file to include entries for RDS Custom SQL Server (Publisher), Amazon RDS for SQL Server (Subscriber), in the following format <IP address> <Hostname> <RDS-Endpoint>. Figure 9 shows an example host file.

Figure 9: Edited host file

Replication security

Replication uses a number of standalone programs, called agents, to carry out the tasks associated with tracking changes and distributing data. By default, replication agents run as jobs scheduled under SQL Server Agent, and SQL Server Agent must be running for the jobs to run. It’s important to plan for security before you configure your replication topology. These steps shows you how to better secure your replication topology. If you are setting up transactional replication between non-domain-joined servers, you create the following Windows account for replication on Publisher and Distributor. If you are using domain-joined servers for replication, then you can use a domain account with appropriate privileges as documented in Security Role Requirements for Replication – SQL Server | Microsoft Docs. You need Windows Authentication to setup replication.

Create Windows accounts for replication

In this section, you create Windows accounts to run replication agents. You create a separate Windows account on the local server for the following agents. This step is required if you are setting up transactional replication on non-domain joined servers.

Agent	Account Name
Snapshot Agent	<machine_name>\Repl_Snapshot
Logreader Agent	<machine name>\Repl_Logreader
Distributor Agent	<machine_name>Repl_distribution

Note: All these logins must be created on Publisher and Distributor with system administrator permission, however individual replication agents (Snapshot, Logreader, and Distributor) must run from Distributor only, as shown in the following table:

Agent	Location
Snapshot Agent	Distributor
Logreader Agent	Distributor
Distributor Agent	Distributor

Configure distributor on Remote SQL Server on Amazon EC2 (Distributor)

Perform the following steps on SQL Server on Amazon EC2 (Distributor):
To create Windows accounts, prepare snapshot folder and configure Distributor, follow the Microsoft Tutorial: Prepare for replication.

Note: All the replication agents account should have full permission on shared snapshot folder.

Figure 10 demonstrates Distributor Properties after you have configured the Distribution Database.

Figure 10: Distributor Properties

You have now successfully prepared your server for replication. In next section, you configure transactional replication. You create a publication and add the articles that are required to publish on the RDS instance.

Create publication in SQL Server replication on RDS Custom SQL Server (Publisher)

On RDS Custom SQL Server (publisher):

Connect to Amazon RDS Custom for SQL Server instance.
Choose the Replication folder to expand it, then and right-click Publication and select New Publication Wizard as shown below.

Figure 11: New Publication Wizard

Follow the steps to create a Publication as described in the Microsoft Tutorial: Configure Transactional Replication.

Configure a remote subscriber in SQL Server replication for RDS SQL Server (Subscriber)

Perform the following steps on RDS Custom for SQL Server (Publisher). As described earlier, you will use the Amazon RDS for SQL Server as a subscriber for receiving data from Amazon RDS for SQL Server Custom.

To create the subscriber

Go to Amazon RDS for SQL Server Custom and choose New Subscriptions.
Choose Next.

Figure 12: New Subscription Wizard
Select Pub1 Publication. then select Next.

Figure 13: Choose Publication
Select Run all agents at Distributor and choose Next.

Figure 14: Distribution Agent Location
Select subscription server and database and choose Next.

Figure 15: Subscribers
Enter Agent Process Account and choose Next.

Figure 16: Distribution Agent Security
After few steps you will come to last page of the New Subscription Wizard, which will look like the image below:

Figure 17: Verify choices and finish
After completion of transactional replication setup, make sure replication is running successfully. Go to Publisher server and right-click Replication Folder and select Launch Replication Monitor as shown below:

Figure 18: Status of Replication in Replication Monitor

Fig 19: Status of Replication in Replication Monitor

Cleaning up RDS for SQL Server Replication Setup

To avoid future charges and remove all the components created while testing this use case, complete the following steps:

Open the Amazon RDS service console , select Databases from left navigation pane. Select the databases you setup replication, go to Actions, and then choose Delete. It will ask for the final snapshot, and you need to type in ‘delete me’ to delete the RDS for SQL Server databases. For more details you can follow the instructions on the RDS Documentation. On the Amazon EC2 service console, locate the SQL Server EC2 instance that you used as remote distributor and terminate it.

Summary

In this post, you learned about transaction replication between Amazon RDS for SQL Server Custom and Amazon RDS for SQL Server with a remote distributor. Transactional replication is useful if you want subscribers to get incremental changes as they occur, and require low latency between changes made at the publisher and those reflected at the subscriber.

Try out the deployment of the solution in your Amazon RDS for SQL Server Custom instance and if you have any comments or questions, leave them in the comments section.

About the Authors

Sudarshan Roy is a Senior Database Specialist Cloud Solution Architect with World Wide AWS Database Services Organization (WWSO). He has led large scale Database Migration & Modernization engagements for Enterprise Customers and his passionate of solving complex migration challenges while moving database workload to AWS Cloud.

Srikanth Katakam is a Senior Database Engineer at Amazon Web Services. He works on the RDS team, focusing on commercial database engines, RDS Custom and SQL Server. He enjoys working on technical challenges in Amazon RDS and is passionate about learning from and sharing knowledge with his teammates and AWS customers.