Rajendra Gupta

Configure SQL Server Replication between AWS RDS SQL Server and On-premises SQL Server

November 20, 2020 by

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

    AWS RDS SQL Server instance

  • 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].

Transaction SQL Server Replication

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.

Configure the distribution

You use the SQL Server Replication, and it should be installed for the SQL instance.

Distribution feature

You can rerun the SQL Server installation, add the following Replication feature and finish the installation wizard actions.

Install SQL Server Replication feature

Now, launch the configure distribution wizard. It automatically selects the connection instance as the distributor. In my environment, the distributor is SQLNode1\INST1.

configure distribution wizard

SQL Server agent should be running because internally replication uses agent jobs for running the various agents.

SQL Server Replication agent jobs

Specify the snapshot folder location. The snapshot folder should have sufficient free space to hold snapshot data for your publisher database.

snapshot folder location

Enter the distributor database name and its file locations.

distributor database name

Enable the publisher on the connection SQL instance. In the below image, we see the distributor [SQLNode1\INST1] and its corresponding database [distributor].

Enable the publisher

Click Next and configure distribution.

configure distribution

Configure Distribution wizard

Click Finish, and it configures the distribution for on-premise SQL Server instance.

Status for distribution configuration

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.

Create a Publication

Select the database that acts as a publisher in your transaction replication topology.

Select Publisher DB

We use transaction replication between the on-premise and RDS SQL Server. You can understand the difference between replications using the SQLShack articles.

Publication type

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.

Select the Articles for publication

In the snapshot agent, select the immediate snapshot or a specific schedule.

Snapshot agent

In the agent security, specify the credentials for the log reader and snapshot agent. In most cases, we use SQL Server agent security accounts.

SQL Server agent security

Create the publication.

Create the publication

Specify a name for the Transaction replication publication, as shown below.

Transaction replication publication

Finish the publication configuration and view the publication status. It starts the snapshot agent at the end of the publication.

Publication status

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.

Configure a remote subscriber

We can verify the RDS instance connection in the SSMS.

verify RDS instance connection

To create the subscriber, go back to the on-premise SQL instance and click on New Subscriptions.

New Subscriptions

In the first step, select the publication we configured in step2.

select the publication

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.

PUSH subscriptions

In the next step, specify the RDS endpoint to add an RDS instance as a subscriber instance.

RDS endpoint

Click on Add subscriber, specify the RDS endpoint, credentials for SQL Server authentication and connect it.

Add subscriber

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.

SQL Server Replication error message

To resolve the issue, first, check the RDS server name using the Select @@servername.

Get RDS server name

Open the command prompt and use the nslookup command for the RDS endpoint. It returns the IP address of the RDS instance.

nslookup command for the RDS endpoint

Now, open the host file by navigating to the path C:\Windows\System32\drivers\etc\hosts.

open the host file

In this host file, map the RDS instance IP address and hostname, as shown below.

RDS instance IP address

We should be able to connect with the RDS server name using SSMS.

RDS server name

Switch back to subscription wizard. Specify the RDS instance server name and create a new database or choose an existing database.

subscription wizard

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.

distribution agent security

You can choose the synchronization schedule. By default, the agent runs continuously and replicates transactions.

synchronization schedule

Initialize the subscription database. It uses the existing snapshot of the publication database and implements it on the subscription database.

Initialize the subscription database

Create the subscription.

Create the subscription

Verify configuration

It creates the AWS RDS SQL Server as a transaction replication subscriber.

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.

Monitor transaction replication

To resolve the error, open the subscriber properties. In the subscriber connection, use the SQL Server authentication and specify your credentials.

subscriber properties

Click Ok and wait for some time. It takes time to replicate the existing transactions from publisher to subscriber (AWS RDS).

replicate the existing transactions

You can use the tracer token to measure the latency between Publisher to Distributor, and Distributor to Subscriber. use the tracer token

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.

Verify the table record

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.

change in the existing record

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.

Rajendra Gupta
151 Views