AWS RDS SQL Server Replication: 2 Easy Steps

With the introduction of Cloud technologies, the demand to Replicate the on-premises data to Cloud technologies has increased.

1. AWS, an abbreviated term for Amazon Web Services, is the world’s leading and most used Cloud service.
2. This blog is aimed at providing a detailed step-by-step guide of 2 different methods to implement the AWS RDS SQL Server Replication.
3. A complete walkthrough of the content will ensure hassle-free data Replication from on-premises SQL Server to the AWS RDS (Relational Database Services) hosted SQL Server. Read along to decide which method suits you the most!

What Is AWS Relational Database Services (RDS)

1. It is designed to simplify setting up, operating & scaling up a Relational Database in the cloud. RDS is not actually a Database, it’s a service that manages Databases & various operations related to their administration.
2. Amazon RDS supports a wide variety of Database instances such as PostgreSQL, MySQL, MariaDB, etc. It is highly scalable, easy to work on, and ensures data security using its virtual private cloud.

What is SQL Server

1. It is a Relational Database developed by Microsoft to store Structured Data. It is known as MS SQL. SQL Server is built on top of SQL (Structured Query Language) to interact with Database objects.
2. SQL Server is tied to Transact-SQL or T-SQL (Microsoft propriety language) for its programming interface, such as declaring the variable, stored procedure, exception handling, etc.
3. Initially, SQL Server ran only on Windows Server and Windows devices, however now it is supported on Linux as well.

Prerequisites

• SQL Server should be hosted on AWS RDS.
• SQL Server should be available on the host workstation along with root access.
• Unrestricted firewall access.

Two Methods to perform AWS RDS SQL Server Replication

• Method 1: Using Custom Code
• Method 2: Using Hevo Data

Method 1: Using Custom Code

1. The SQL Server hosted on RDS doesn’t directly support replication as when the data is being hosted on AWS RDS, the replication subsystem of the SQL Server Agent isn’t running.
2. Replication is made possible by using a push subscription mechanism that pushes data from the host SQL Server to the target AWS RDS hosted SQL Server.
3. This can be done using the following steps:

• Step 1: Creating Database on the Target Server
• Step 2: Connecting with the Target Database
• Step 3: Enabling Distribution in SQL Server
• Step 4: Creating Publications
• Step 5: Enabling Push Subscription
• Step 6: Final Configurations

Step 1: Creating Database on the Target Server

The Database can be created on the target server by making use of the scripts obtained from the source Database. Scripting the source database gives information about the tables, views, etc and it will help Replicate them on the target Database.

• Open the SQL Server management studio, also called SSMS.
• Right-click on the Database to select a task and then generate scripts.
• Save the scripts.

This will generate both DDL & DML scripts for the selected Database and its objects.

Step 2: Connecting with the Target Database

Create another instance of SQL Server management studio and establish a connection with the target. Once the connection is made, create the Database & its objects using the source Database Scripts.

Step 3: Enabling Distribution in SQL Server

1. To enable the distribution in SQL Server, right-click on the Replication folder and select configure. This will convert a local SQL Server to a Distribution Server.
2. A distribution database is thus created, & this Database contains the complete Dataset that needs to be migrated.

Step 4: Creating Publications

Create a new publication by selecting the Replication folder. Select the folder and right-click on it to select the new publication option.

Once the new publication is created, select the desired Database and the type of publication as a Transactional publication.

In the articles section, select the desired tables & necessary Database objects that are required.

In the snapshot agent page, check the box to immediately create a snapshot and facilitate subscription initialization.

Give a name to the publication thus created and select finish. This is how you can create a new publication for AWS RDS SQL Server Replication.

Step 5: Enabling Push Subscription

1. The push subscription method must be enabled to migrate data from the source SQL Server to the target Database. Select the newly created publication and select the run all agents at the distributor option. This option enables the push subscription.
2. The next step is to provide the URL to the AWS RDS SQL hosted server. This can be done using the add subscriber option. Provide the desired credentials and click on next.

Replication to the SQL Server requires the server name to be the same as the name of the Windows Server hosting the RDS for SQL Server. This often generates the following error:

To make the source server name same as the target server name, use the following command:

nslookup <aws rds sql server URI>

Run this command on the source command line. This will return the IP address of the RDS hosted SQL Server.

Example query:

 nslookup rwsqlserver.xxxxxxxxxx.us-east-1.rds.amazon.com

Navigate to the folder Windows/system32/drivers/etc and open the file called the host and append the following lines to it:

105.0.124.53 EC2AMAZON-RDS// Here 105.0.124.53 is the returned IP.

Upon updating the host file, close the error and establish the connection.

Step 6: Final Configurations

The connection will be established successfully after providing the credentials. To provide further details, select the options property and give security details for the RDS connection.

Now as a final step, configure the schedule synchronization, this can be done by selecting the run continuously option and selecting perform synchronization immediately.

This is how you can use Transactional Replication for AWS RDS SQL Server Replication.

Method 2: Using Hevo Data for AWS RDS SQL Server Replication

• Step 1: Configure MS SQL as Source for Hevo
• Step 2: Configure Amazon Redshift as Destination for Hevo

Step 1: Configure MS SQL as Source for Hevo

Log into your MS SQL account and open the SQL Server Configuration Manager. In the left navigation pane under SQL Server Network Configuration, click Protocols for MS SQL Server Instance Name. The default instance name is MSSQLSERVER.

In the right pane, right-click the TCP/IP Protocol Name, and select Enable (if not enabled already) in the Status field. This is shown in the below image.

Also, the Change Tracking mechanism captures changes made to a Database. To enable, or disable change tracking, the database user must have the ALTER DATABASE privilege.

To enable Change Tracking, connect your MS SQL Server in your SQL Client tool, and enter these commands:

ALTER DATABASE [<database>] ;
SET CHANGE_TRACKING = ON;
ALTER TABLE [<schema>].[<table>] ;  
ENABLE CHANGE_TRACKING;

After this, you can set up your Hevo Pipeline and customize it according to your requirements.

Step 2: Configure Amazon Redshift as Destination for Hevo

1. You need to whitelist the Hevo IP address for your region to enable Hevo to connect to your Amazon RDS MySQL Database.
2. This can be done in the Edit Inbound rules section of your AMzon Redshift account. Click Add Rule and In the Type column, select Redshift from the drop-down.
3. In the Port Range column, enter the port of your Amazon Redshift cluster. The default value is 5439. In the Source column, select Custom from the drop-down and enter Hevo’s IP addresses for your region, and click Save as shown in the below image.

After this, you need to just grant privileges to the user and configure Amazon Redshift as your destination. This is shown in the below image.

To learn more about configuring Amazon Redshift as the destination for Hevo, visit here.

Key features of Hevo:

1. Schema Management
2. Minimal Learning
3. Secure
4. Live Monitoring
5. Live Support
6. Real-Time Data Export

Hevo can perform the AWS RDS SQL Server Replication with ease using its intuitive UI and in real-time without compromising performance. We have also written an article about AWS SQL server deployment.

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand.