With the introduction of Cloud technologies, the demand to Replicate the on-premises data to Cloud technologies has increased. AWS, an abbreviated term for Amazon Web Services, is the world’s leading and most used Cloud service.
This blog is aimed at providing a detailed step-by-step guide of 2 different methods to implement the AWS RDS SQL Server Replication. 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!
Table Of Contents
What Is AWS Relational Database Services
AWS RDS stands for Amazon Relational Database Services. It is a cloud-based service from Amazon. 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.
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.
AWS is the world’s leading and most used cloud service, with approximately 175 fully-featured services from data centers globally. Millions of customers, including the fastest-growing startups, largest enterprises (Netflix, Uber), and leading government agencies are using AWS to lower their costs, become more agile, and innovate faster.
For further information on AWS RDS, you can check the documentation manual here.
What is SQL Server
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. 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. Initially, SQL Server ran only on Windows Server and Windows devices, however now it is supported on Linux as well.
Key features of SQL Server:
- Scalable: It is easy to scale and supports large amounts of data.
- Performance: It performs exceptionally well on both Windows & Linux.
- Secure: It ensures data security & availability irrespective of whether the data is at rest or being worked on. It further supports data recovery during crashes/failures.
- Analytics Support: MS SQL supports data analytics & machine learning. It uses languages like Python or R to perform such operations.
- Integrations: It is very easy to integrate MS SQL with tools like Hadoop for big-data analytics using T-SQL commands.
- Threading: MS SQL efficiently supports multi-threading & parallel processing even with a massive amount of data & ensures powerful analytics.
For further information, you can check the Microsoft site on SQL Server here.
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.
Methods To Perform AWS RDS SQL Server Replication
There are many ways to replicate a SQL Server Database. The two main methods to perform AWS RDS SQL Server Replication are:
Method 1: Using Custom Code for AWS RDS SQL Server Replication
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. 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. This can be done using the following steps:
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
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. 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
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.
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
Hevo Data, a No-code Data Pipeline can help you Replicate data from SQL Server (among 100+ sources) swiftly to a Database/Data Warehouse of your choice. Hevo is fully-managed and it completely automates the process of monitoring and replicating the changes on the target database rather than making the user write the code repeatedly. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss. Hevo provides you with a truly efficient and fully-automated solution to replicate your data in a jiff.
With Hevo Data, you can perform the AWS RDS SQL Server Replication by using the following 2 steps:
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.
Image Source
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.
To learn more about setting MS SQL as a source for Hevo, visit here.
Step 2: Configure Amazon Redshift as Destination for Hevo
You need to whitelist the Hevo IP address for your region to enable Hevo to connect to your Amazon RDS MySQL Database. 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.
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.
Image Source
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.
Image Source
To learn more about configuring Amazon Redshift as the destination for Hevo, visit here.
Key features of Hevo:
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls
- Real-Time Data Export: Hevo with its strong integration with 100+ sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
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 HERE FOR A 14-DAY FREE TRIAL
Conclusion
This article teaches you the Transactional Replication method to successfully perform AWS RDS SQL Server Replication. It provides a step-by-step guide to help you grasp the complete concept behind each step and efficiently implement them. It further introduces an alternative method Hevo Data, a No-code Data Pipeline that will help you perform AWS RDS SQL Server Replication without having to write any code in a completely secure and efficient manner. It provides a truly consistent & reliable solution and ensures data security at each step.
visit our website to explore hevo
SIGN UP for a 14-day free trial and see the difference!
Why don’t you share your experiences with AWS RDS SQL Server Replication in the comments? We would love to hear from you!
Vishal has a passion towards the data realm and applies analytical thinking and a problem-solving approach to untangle the intricacies of data integration and analysis. He delivers in-depth researched content ideal for solving problems pertaining to modern data stack.