In Part 1, we learned how to configure transactional replication from on-premises Microsoft SQL Server or SQL Server on an Amazon Elastic Compute Cloud (Amazon EC2) instance to Amazon RDS for SQL Server by adding the host entries with the name of Windows server hosting your Amazon RDS for SQL Server DB instance and the IP address of the endpoint in the
hosts file located in the
When a host replacement occurs during maintenance activities or failover events, the transactional replication fails to replicate data to the Amazon RDS for SQL Server DB instance in the Multi-AZ configuration. This is due to the host entries with the IP address of the endpoint before failover, which force the distribution agent to connect to the previous primary.
The purpose of this post is to maintain continuous transactional replication from an on-premises or Amazon EC2 hosted SQL Server instance to an RDS for SQL Server DB instance in the Multi-AZ configuration when a host replacement occurs during maintenance activities or failover events.
To get started, you must have the following:
- An on-premises SQL Server instance or SQL Server on Amazon EC2 instance
- An Amazon RDS for SQL Server DB instance with Multi-AZ enabled
- Connectivity between on-premises SQL Server or SQL Server on Amazon EC2 to Amazon RDS for SQL Server
- A login that is part of a sysadmin fixed server role to configure a distributor at on-premises SQL Server
To configure your distribution, complete the following steps:
- Log in to the on-premises SQL Server using a login that is part of the sysadmin server role.
- In SQL Server Management Studio, navigate to the folder Replication.
- Choose the Replication folder (right-click) and choose Configure distribution.
- Follow the steps as instructed.
You could also create distribution using a T-SQL script. The following T-SQL script configures local distribution on the on-premises SQL Server. Set up the folder structure for the distribution database data, log files, and a snapshot folder.
Replace the publisher server name, data file path, log file path, and the snapshot folder in the following script and run it on the on-premises SQL Server. The script has default values; choose the values that best suit your environment.
Configuring publication on the source server
You now configure publication on your source server.
- Log in to SQL Server using SQL Server Management Studio.
- In the Replication folder, choose Local Publication (right-click) and choose New Publication.
- Choose the database.
In the following screenshot, we choose our source database
- For Publication Type, choose Transactional publication.
- Choose the tables that you want to replicate and choose Next.
In the following screenshot, we select our source table
- In the filter window, add filters if you want to replicate specific data to the subscriber.
- In the snapshot window, select Create snapshot immediately and keep the snapshot available to initialize the subscriptions.
- Configure the snapshot agent and log reader agent security as needed and create a publication.
In the following screenshot, we specify a process account that the snapshot agent uses.
Configuring the subscriber
Amazon RDS for SQL Server supports transactional replication as subscriber only with a push type subscription. While creating a subscriber on SQL Server Management Studio, it forces you to input the actual server name of the subscriber instead of the endpoint. It throws an error (see the following screenshot) if we use an endpoint to create the subscriber.
To work around this, use the following sample T-SQL code to create a subscriber using the system stored procedure and the endpoint. Replace the publication name, the endpoint of the Amazon RDS for SQL Server DB instance, database name, and the password for the subscriber login. Run the script on the publisher SQL Server instance to create a subscriber and the distribution agent to push the transactions from the distribution database to the subscriber.
Now you created the subscription using the endpoint of an Amazon RDS for SQL Server DB instance instead of the actual server name. In the event of failover, if the subscriber is configured for continuous synchronization, the replication fails for a minute or two until the DNS changes the endpoint take place.
You can also use the listener endpoint to create the subscriber in case the subscriber is a Multi-AZ Amazon RDS for SQL Server DB instance with enterprise edition. The DNS changes in the listener endpoint can typically take less than 10 seconds.
Testing the failover
To conduct a failover test, complete the following steps:
- On the Amazon RDS console, choose Databases.
- Select the subscriber database instance.
- From the Actions drop-down menu, choose Reboot.
- For DB instances, select Reboot with Failover.
- Choose Reboot.
After failover of the Amazon RDS for SQL Server DB instance, perform the DML operations on the source database instance and verify them on the subscriber (the Amazon RDS for SQL Server DB instance).
You could also monitor the replication status using the replication monitor at the publisher.
In this post, we showed how to configure transactional replication that continues to replicate data from an on-premises SQL Server or SQL Server on Amazon EC2 instance to a Multi-AZ enabled Amazon RDS for SQL Server DB instance in the event of failover. Please leave a comment with any questions or feedback you may have!
About the authors
Ranga Cherukuri is a Associate Consultant with the Professional services team at AWS. Ranga focuses on helping customers to build high-available, cost-effective database solutions and migrate their large scale SQL Server databases to AWS. He is passionate about Databases and Analytics
Ramesh Babu Donti is a Database Consultant with the Professional services team at AWS. Ramesh focuses on helping customers to build high-available, cost-effective database solutions and migrate their large scale SQL Server databases to AWS.