Migrating to Amazon RDS for SQL Server using transactional replication: Part 2

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 /system32/drivers/etc folder.

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.

Prerequisites

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

Configuring distribution

To configure your distribution, complete the following steps:

1. Log in to the on-premises SQL Server using a login that is part of the sysadmin server role.
2. In SQL Server Management Studio, navigate to the folder Replication.
3. Choose the Replication folder (right-click) and choose Configure distribution.
4. 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.

use master
declare @dist sysname
select @dist= @@SERVERNAME

exec sp_adddistributor @distributor = @dist, @password = N'xxxxxxxxxxxxx'
GO
exec sp_adddistributiondb @database = N'distribution',
@data_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA',
@log_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA', 
@log_file_size = 2, 
@min_distretention = 0, 
@max_distretention = 72, 
@history_retention = 48, 
@deletebatchsize_xact = 5000, 
@deletebatchsize_cmd = 2000, 
@security_mode = 1
GO


USE [distribution]

IF (
		NOT EXISTS (
			SELECT *
			FROM sysobjects
			WHERE name = 'UIProperties'
				AND type = 'U '
			)
		)
	CREATE TABLE UIProperties (id INT)

IF (
		EXISTS (
			SELECT *
			FROM::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', NULL, NULL)
			)
		)
	EXEC sp_updateextendedproperty N'SnapshotFolder'
		,N'D:ReplSnapshot'
		,'user'
		,dbo
		,'table'
		,'UIProperties'
ELSE
	EXEC sp_addextendedproperty N'SnapshotFolder'
		,N'D:ReplSnapshot'
		,'user'
		,dbo
		,'table'
		,'UIProperties'
GO

EXEC sp_adddistpublisher @publisher = N'WIN-C6AP6CDJIJL'
	,@distribution_db = N'distribution'
	,@security_mode = 1
	,@working_directory = N'D:ReplSnapshot'
	,@trusted = N'false'
	,@thirdparty_flag = 0
	,@publisher_type = N'MSSQLSERVER'
GO

Configuring publication on the source server

You now configure publication on your source server.

1. Log in to SQL Server using SQL Server Management Studio.
2. In the Replication folder, choose Local Publication (right-click) and choose New Publication.
3. Choose the database.

In the following screenshot, we choose our source database AdventureWorks.

4. For Publication Type, choose Transactional publication.

5. Choose the tables that you want to replicate and choose Next.

In the following screenshot, we select our source table AddressType.

6. In the filter window, add filters if you want to replicate specific data to the subscriber.
7. In the snapshot window, select Create snapshot immediately and keep the snapshot available to initialize the subscriptions.

8. 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.

USE [AdventureWorks]

EXEC sp_addsubscription @publication = N'On-Premises-RDS-Multi-AZ'
	,@subscriber = N'rdsrepltest.czud4csqweax.us-east-1.rds.amazonaws.com'
	,@destination_db = N'AdventureWorks_Sub'
	,@subscription_type = N'Push'
	,@sync_type = N'automatic'
	,@article = N'all'
	,@update_mode = N'read only'
	,@subscriber_type = 0

EXEC sp_addpushsubscription_agent @publication = N'On-Premises-RDS-Multi-AZ'
	,@subscriber = N'rdsrepltest.czud4csqweax.us-east-1.rds.amazonaws.com'
	,@subscriber_db = N'AdventureWorks_Sub'
	,@job_login = NULL
	,@job_password = NULL
	,@subscriber_security_mode = 0
	,@subscriber_login = N'admin'
	,@subscriber_password = N'strongpassword'
	,@frequency_type = 64
	,@frequency_interval = 0
	,@frequency_relative_interval = 0
	,@frequency_recurrence_factor = 0
	,@frequency_subday = 0
	,@frequency_subday_interval = 0
	,@active_start_time_of_day = 0
	,@active_end_time_of_day = 235959
	,@active_start_date = 20200831
	,@active_end_date = 99991231
	,@enabled_for_syncmgr = N'False'
	,@dts_package_location = N'Distributor'
GO

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:

1. On the Amazon RDS console, choose Databases.
2. Select the subscriber database instance.
3. From the Actions drop-down menu, choose Reboot.

4. For DB instances, select Reboot with Failover.
5. 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.

Summary

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.