In this article, we will implement a SQL Server Replication between AWS RDS SQL Server and On-premises SQL Server instance.
Introduction
AWS RDS SQL Server is a managed cloud-based database service from Amazon. It simplifies the task to install, configure, and maintain a relational database because AWS does it for us. Anytime, we require a database instance, connect to the AWS console, provide inputs, and configure the database instance.
Usually, in most organizations, we have a mixed environment that comprises both on-premise and cloud-based SQL Server. Suppose you have an application that connects with the RDS database. You require a few tables’ data in the RDS database from the on-premise SQL database.
Another use of the replication might be data migration. Suppose you want to migrate a few tables from the source (on-premise) to the destination (RDS). There might be several ways to migrate data. The SQL Server transaction replication can be a way for this purpose.
In this article, we look for the following topics.
- Is it possible to configure the replication between On-premises SQL Server and RDS SQL Server?
- How do we configure the replication between On-premises SQL Server and AWS RDS SQL Server?
Environment details
- You should have a running AWS RDS SQL Server instance. Refer to articles for AWS RDS for this purpose. The RDS
instance will act as a subscriber for our replication
- RDS instance name: RDSSubscriber
- Version: SQL Server 2019
- Edition: Standard
- Instance type: db.m5.large
- An On-premise SQL Server instance
- SQL instance: SQLNode1\INST1
- Version: SQL Server 2019
- Edition: Standard
- Publisher Database: AdventureWorks2019
Note: You cannot replicate data from a higher version of the on-premises SQL Server to a lower version of AWS RDS SQL Server
- You should go through existing replication articles for an overview of transaction replication, and its various components such as Log reader agent, Snapshot agent, Distribution agent
Configure transaction SQL Server Replication between On-premises SQL Server and AWS RDS SQL Server
At a high-level, the transaction replication architecture looks like below. In this architecture, the publisher and distributor instance is in the on-premise, and the subscriber is an RDS database. We can use a separate publisher and distributor instance. For this article, our publisher and distributor SQL instance is [SQLNode1\INST1].
Let’s start the replication configurations using the following steps.
Step1: Configure the distribution for the on-premise SQL instance
In this step, connect to your publisher instance (on-premise) using SSMS. Navigate to the Replication folder and select Configure distribution.
You use the SQL Server Replication, and it should be installed for the SQL instance.
You can rerun the SQL Server installation, add the following Replication feature and finish the installation wizard actions.
Now, launch the configure distribution wizard. It automatically selects the connection instance as the distributor. In my environment, the distributor is SQLNode1\INST1.
SQL Server agent should be running because internally replication uses agent jobs for running the various agents.
Specify the snapshot folder location. The snapshot folder should have sufficient free space to hold snapshot data for your publisher database.
Enter the distributor database name and its file locations.
Enable the publisher on the connection SQL instance. In the below image, we see the distributor [SQLNode1\INST1] and its corresponding database [distributor].
Click Next and configure distribution.
Click Finish, and it configures the distribution for on-premise SQL Server instance.
Step2: Create a Publication in SQL Server Replication for on-premises SQL Server instance
In this step, we create a publication and add the articles that are required to publish on the RDS instance.
Select the database that acts as a publisher in your transaction replication topology.
We use transaction replication between the on-premise and RDS SQL Server. You can understand the difference between replications using the SQLShack articles.
In the next step, we select the articles for publication. In the transaction SQL Server Replication, you can publish specific or all eligible tables from the publisher to the subscriber database. The table requires a primary key defined for eligibility.
For the demo purpose, I add a single table [person].[person] in the publication.
In the snapshot agent, select the immediate snapshot or a specific schedule.
In the agent security, specify the credentials for the log reader and snapshot agent. In most cases, we use SQL Server agent security accounts.
Create the publication.
Specify a name for the Transaction replication publication, as shown below.
Finish the publication configuration and view the publication status. It starts the snapshot agent at the end of the publication.
Step3: Configure a remote subscriber in SQL Server Replication for AWS RDS SQL Server
As I specified earlier, we will use the RDS SQL Server as a subscriber for receiving data from an on-premise SQL Server database.
For the RDS instance, we use the RDS endpoint that we get from the RDS dashboard.
We can verify the RDS instance connection in the SSMS.
To create the subscriber, go back to the on-premise SQL instance and click on New Subscriptions.
In the first step, select the publication we configured in step2.
We use the PUSH subscriptions for the transaction replication between on-premise and RDS SQL Server. It runs the agents at the distributor SQLNode1\INST1.
In the next step, specify the RDS endpoint to add an RDS instance as a subscriber instance.
Click on Add subscriber, specify the RDS endpoint, credentials for SQL Server authentication and connect it.
We get the error message. For the replication, SQL Server requires the actual server name, but we specified the RDS endpoint. We cannot connect with the actual server name in SSMS to the RDS.
To resolve the issue, first, check the RDS server name using the Select @@servername.
Open the command prompt and use the nslookup command for the RDS endpoint. It returns the IP address of the RDS instance.
Now, 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 below.
We should be able to connect with the RDS server name using SSMS.
Switch back to subscription wizard. Specify the RDS instance server name and create a new database or choose an existing database.
In the distribution agent security, you can use the SQL Server agent account if you have added the RDS in your on-premises active directory. My RDS instance is not part of the on-premise active directory but to show you an error, let’s go with the SQL Server agent security.
You can choose the synchronization schedule. By default, the agent runs continuously and replicates transactions.
Initialize the subscription database. It uses the existing snapshot of the publication database and implements it on the subscription database.
Create the subscription.
It creates the AWS RDS SQL Server as a transaction replication subscriber.
Step4: Monitor transaction replication between on-premises SQL Server and AWS RDS SQL Server
Launch the replication monitor, and you get the following login failed message. It complains about the untrusted domain and says that we cannot use it with the integrated Windows authentication.
In step3, we specified the SQL Server agent service account for connecting with the RDS subscriber. It cannot connect with RDS using our on-premise SQL Server service account because the RDS server is not a member of the active directory.
To resolve the error, open the subscriber properties. In the subscriber connection, use the SQL Server authentication and specify your credentials.
Click Ok and wait for some time. It takes time to replicate the existing transactions from publisher to subscriber (AWS RDS).
You can use the tracer token to measure the latency between Publisher to Distributor, and Distributor to Subscriber.
Verify the table record counts on both publisher and subscriber database. We have the same record count for the [Person].[Person] table as replication is synchronized.
You can also validate by making a change in the existing record. For example, in the below image, we modified the first name for a specific business entity. You can validate the updated first name in the AWS RDS SQL Server.
Conclusion
In this article, we explored the push transaction replication between the on-premise SQL Server and AWS RDS SQL Server. You can use it to replicate your data or for the migration of a small database or subset of the database. You should consider your network bandwidth requirements before implementing the SQL Server replication.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023