Native SQL Server replication options on Amazon RDS Custom for SQL Server

Modern applications require reliable data distribution and high availability across multiple databases to support critical business operations. SQL Server replication helps database administrators (DBAs) meet these requirements by automatically distributing data between databases in near real-time. With SQL Server replication, you can create data copies that enable analysts to run complex reports without impacting production workloads. You can also synchronize data across remote locations and maintain copies in different AWS Regions to meet compliance requirements and support disaster recovery strategies.

Amazon RDS Custom for SQL Server combines the flexibility of self-managed databases with the automation of managed services. It handles routine tasks like high availability setup, backups, and patching, freeing you to focus on database design and replication architecture. With RDS Custom, you can use native SQL Server replication features while AWS manages the underlying infrastructure. This approach reduces operational overhead, allowing you to concentrate on optimizing your replication topology, monitoring performance, and ensuring data consistency across your distributed environment.

In this post, we explore SQL Server replication implementation on Amazon RDS Custom. You’ll learn about different replication types supported on RDS Custom SQL Server, including snapshot, transactional, peer-to-peer, and merge replication. Finally, we provide a step-by-step guide to setting up replication, from configuring the distributor to creating publications and subscriptions.

SQL Server Replication Support Matrix: Components and Roles

SQL Server Replication Support Matrix: Replication Types

* Merge and peer-to-peer or bidirectional replication can be configured on RDS Custom using host names instead of CNAME. This dependency would require a reconfiguration of your replication in case of host replacement event.

Solution overview

The following architecture diagram illustrates a replication configuration between two RDS Custom for SQL Server instances within a Region.

Prerequisites

Before you begin, ensure you meet the following prerequisites:

1. Two Amazon RDS Custom for SQL Server instances in your AWS account. If you don’t have existing RDS Custom for SQL Server instances, refer to Setting up your environment for Amazon RDS Custom for SQL Server.

   The following table highlights the demo environment we provisioned to demonstrate the configuration.

   	RDS Custom instance 1	RDS Custom instance 2
   DB identifier	Publisher	Subscriber
   DB instance class	r6i.2xlarge	r6i.xlarge
   Multi-AZ	Yes	No
   Domain joined	Yes	Yes
   Domain name	example.com	example.com
   RDS Custom endpoint	publisher.c9ccxxx.us-east-1.rds.amazonaws.com	subscriber.c9ccxxx.us-east-1.rds.amazonaws.com
   Kerberos endpoint	publisher.c9ccxxx.us-east-1.awsrds.example.com	subscriber.c9ccxxx.us-east-1.awsrds.example.com
   Role	Publisher, distributor	Subscriber
   SQL Server Agent	Startup type changed to Automatic	Startup type changed to Automatic
   Databases	Distribution, PublicationDB	SubscriptionDB

2. SQL Server Management Studio (SSMS) installed on a bastion host.
3. Make sure that name resolution and network connectivity are properly configured between all servers.
4. Configure the security group rules to allow SQL Server traffic (port 1433) between publisher and subscriber instances.
5. Make sure that the RDS Custom DB instances are joined to the domain to enable Kerberos authentication. For more information, refer to RDS Custom for SQL Server now supports Windows Authentication.

Configure transactional replication

This section covers the configuration of transactional replication between your publisher and subscriber databases. We’ll set up the publication, add the required articles, and create a push subscription using T-SQL scripts.

Follow these steps:

1. Open SQL Server Management Studio (SSMS)
2. Connect to the publisher instance and open a new query window.
3. Paste the following script in a new query window and update @@servername to match your publisher CNAME.

   -- Update SQL Server Name with DNS CNAME (requires restart to take effect)
   SELECT @@SERVERNAME AS 'Current_Server_Name'
   
   EXEC sp_dropserver 'old_server_name'
   EXEC sp_addserver 'new_cname', 'local'
   
   -- Verify after restart
   SELECT @@SERVERNAME AS 'Updated_Server_Name' 
   

   SQL

   The Kerberos-based endpoint follows a specific format. You should replace the command to match your RDS Custom environment:

   <rds-instance-name>.<account-region-hash>.<aws-region>.awsrds.<fully-qualified-domain-name(FQDN)>

   Markup
4. Repeat steps 2 and 3 on the subscriber instance.

   The following screenshot shows @@servername value for both DB instances.
   

5. Create sample database and table on the publisher and subscriber DB instances:

   -- Run on Publisher
   CREATE DATABASE PublicationDB;
   GO
   
   USE PublicationDB
   GO
   CREATE TABLE Tracker (
       Date DATE,
       RPO TIME,
       Status VARCHAR (255),
       PRIMARY KEY (RPO)
   );
   INSERT INTO Tracker
   SELECT CAST(GETDATE() AS Date), CAST(GETDATE() AS time(7)), 'Success'
   SELECT * FROM Tracker
   
   -- Run on Subscriber
   CREATE DATABASE SubscriptionDB;
   GO
   
   

   SQL
6. Configure the distributor and enable publication. Update the script with your database name.

   ----------Configure the distributor------------
   USE master
   GO
   EXEC sp_adddistributor 
   @distributor = @@SERVERNAME, 
   @password = 'Start1234!'  --Replace with your secure password
   GO
   
   ----------Create the distribution database------------
   EXEC sp_adddistributiondb @database = 'distribution', 
   @data_folder = 'D:\rdsdbdata\DATA', 
   @log_folder = 'D:\rdsdbdata\LOG'
   GO
   
   EXEC sp_adddistpublisher 
   @publisher = @@SERVERNAME, 
   @distribution_db = 'distribution',
   @working_directory = 'D:\rdsdbdata', --Snapshot folder on D:\
   @publisher_type = 'MSSQLSERVER'
   GO
   
   ----------Enable Database for Publication------------
   USE [PublicationDB]
   EXEC sp_replicationdboption 
   @dbname = 'PublicationDB', --Replace with your database name 
   @optname = 'publish', 
   @value = 'true'
   GO
   USE [master]
   GO
   ALTER DATABASE distribution SET RECOVERY FULL WITH NO_WAIT
   GO
   

   SQL
7. Execute the following script to configure transactional replication, create publication, add articles, and set up push subscription. Ensure to replace the placeholder values (YourDatabaseName, YourPublicationName, YourTableName, YourSubscriberServerName, YourDestinationDatabaseName) with your actual server and database information:

   ----------Create Transactional Publication and add Article---------
   USE PublicationDB
   GO
   EXEC sp_addpublication 
   @publication = 'MyTranPub', 
   @description = 'Transactional publication of database ''PublicationDB'' from Publisher ''PublisherServerName''', 
   @repl_freq = 'continuous', 
   @status = 'active', 
   @independent_agent = 'true', 
   @immediate_sync = 'true'
   GO
   
   EXEC sp_addpublication_snapshot 
   @publication = 'MyTranPub', 
   @frequency_type = 1, 
   @job_login = null, –-Replace with your Domain User Name 
   @job_password = null –-Replace with your Domain User Password
   GO
   

   SQL

   NOTE: Make sure the Domain user is created in the Publisher instance.

   EXEC sp_addarticle 
   @publication = 'MyTranPub', 
   @article = 'Tracker', 
   @source_object = 'Tracker', 
   @destination_table = 'Tracker'
   GO
   
   ----------Create Push Subscription------------
   USE PublicationDB
   EXEC sp_addsubscription 
   @publication = 'MyTranPub', 
   @subscriber = 'subscriber.csxxxxxxg5.us-east-1.awsrds.example.com', 
   @destination_db = 'SubscriptionDB', 
   @subscription_type = 'Push', --This is a push subscription example
   @article = 'all'
   GO
   
   EXEC sp_addpushsubscription_agent 
   @publication = 'MyTranPub', 
   @subscriber = 'subscriber.csxxxxxxg5.us-east-1.awsrds.example.com', 
   @subscriber_db = 'SubscriptionDB', 
   @job_login = null –-Replace with your Domain User Name, 
   @job_password = null –-Replace with your Domain User Password
   GO
   

   SQL

   NOTE: Make sure the Domain user is created in the Publisher instance.

8. After executing the script successfully in SSMS, refresh and expand the Replication > Local Publications folders in Object Explorer to verify that your new publication appears there.
9. Right-click on the newly created publication and choose View Snapshot Agent Status, as shown in the following screenshot.
   
10. In the View Snapshot Agent Status window, choose Start to initialize the subscriber
    

Generate test load and validate the replication in near real-time

Once the replication setup is complete, it’s crucial to validate its functionality by generating test data and monitoring the replication process. The following steps will help you verify that data changes are properly propagating from the publisher to the subscriber in near real-time.

1. Connect to the publisher DB instance and run the following script to generate some load:

   DECLARE @Count int =0;
   DECLARE @DateTime datetime2(7) = GETDATE();
   DECLARE @Date date = CAST(@DateTime AS date);
   DECLARE @RPO time = CAST(@DateTime AS time(7));
       
   WHILE @Count <= 100
   BEGIN
       INSERT INTO [PublicationDB].[dbo].[Tracker](Date,RPO,Status)
       VALUES(@Date, @RPO,'Success');
       
       WAITFOR DELAY '00:00:01';
           
       SET @DateTime = GETDATE();
       SET @Date = CAST(@DateTime AS date);
       SET @RPO = CAST(@DateTime AS time(7));
       SET @Count =@Count +1;
       END
   

   SQL
2. Check the number of rows being inserted in the publisher database:

   SELECT COUNT(RPO) as PUBLISHER FROM [PublicationDB].[dbo].[Tracker]

   SQL
3. Verify data synchronization in the subscriber database in near real-time:

   SELECT COUNT(RPO) as SUBSCRIBER FROM [SubscriptionDB].[dbo].[Tracker]

   SQL

   The following image is the side-by-side comparison of the row count on Publisher and Subscriber. This confirms that the replication is working and changes are propagating as expected.
   

Best practices and Considerations

Consider the following best practices to optimize your replication setup:

1. Monitor replication latency regularly using built-in SQL Server replication monitors.
2. For a Single-AZ instances, make sure the SQL Server Agent service startup type is set to ‘Automatic’ for all DB instances involved.
   NOTE: SQL Server Agent service startup type on a Multi-AZ instances should be left as ‘Manual’. Setting it to Automatic will not be persisted. RDS Custom automation will revert the change back to ‘Manual’.
3. Make sure the distribution database is configured with full recovery model to allow point-in-time recovery (PITR).
4. For large-sized databases, consider using a backup restore method to initialize the subscriber.
5. Choose push or pull subscription type based on your needs, as this determines how the publication will be received and where the Distribution Agent runs. Push subscription makes it easier to administer the synchronization of subscriptions centrally.
6. Choose the replication topology to meet your specific use case, such as transactional replication for near real-time reporting, disaster recovery, data integration and consolidation.
7. Consider using a dedicated login for each replication agent to improve security and simplify troubleshooting. Follow the principle of least privilege when assigning permissions to replication logins.

Clean Up

The resources deployed in this post incur costs in your account. To avoid incurring future charges, delete the resources you created for testing the use case described in this post.

To remove the replication setup, complete the steps below:

1. To delete the subscription, execute the following command on the subscriber instance:

   USE YourDatabaseName
   EXEC sp_dropsubscription @publication = 'YourPublicationName', @article = 'all', @subscriber = 'SubscriberServerName'
   

   SQL
2. To disable publication on a database and delete the publication execute the following command on the publisher instance:

   USE YourDatabaseName
   EXEC sp_droppublication @publication = 'YourPublicationName'
   
   -- Disable replication on the database
   EXEC sp_replicationdboption @dbname = N'YourDatabaseName', @optname = N'publish', @value = 'false'
   

   SQL
3. To disable the distributor and drop all publications, execute the following command on the publisher and distributor instance:
   NOTE: Run with Caution as the below command will perform a FORCE CLEANUP operation that removes the Distributor and all dependent components.

   EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

   SQL

To delete environment:

1. On the Amazon RDS console, select the database you set up. On the Actions menu, choose Delete.
2. On the Amazon Elastic Compute Cloud (Amazon EC2) console, select the EC2 instance that you used for SSMS. On the Actions menu, choose Terminate.

Conclusion

In this post, we demonstrated how to implement native SQL Server replication on Amazon RDS Custom for SQL Server, combining the flexibility of self-managed databases with the automation benefits of managed services. We walked through setting up transactional replication between publisher and subscriber databases, including detailed configuration steps, validation procedures, and operational best practices. This solution enables you to maintain data consistency across distributed environments while reducing administrative overhead.

For more information about Amazon RDS Custom for SQL Server, visit the RDS Custom for SQL Server User Guide. If you have questions or want to share your experience with SQL Server replication on RDS Custom, please leave them in the comments section below.

About the authors