Configure SQL Server transactional replication with Always On Availability Groups

Opening

This article aims to give you a step-by-step guide on correctly configuring a SQL Server transactional replication when the involved databases are part of Always On Availability Groups. The main difficulty is to have the transactional replication resilient to a failover. Let’s explore the steps to achieve this!

First things first, let’s see the test environment. We’ll work with six SQL Server instances, two for each transactional replication component: Publisher, Distributor and Subscriber.

Each two SQL Server instance pair is configured in its own Always On Availability Group.

In the next chapters, we’ll set up a transactional replication to deliver data between PubDB and SubDB. The Distribution database and replication jobs will be hosted on the DisAG.

Middlegame

1. Setting up the Distribution database

First, we’ll create the distribution database, alter its recovery model to FULL and take a full backup to prepare it for joining the DisAG availability group.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:SETVAR PrimaryDistributor		"Dis1"
:SETVAR DistributionPassword	"Password!"
:SETVAR BackupLocation			"C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup"

/*
Installing the server as a Distributor
SP_ADDDISTRIBUTOR Creates an entry in the sys.sysservers table, marks the server entry as a Distributor, and stores property information. 
This stored procedure is executed at the Distributor on the master database to register and mark the server as a distributor.
*/

:CONNECT $(PrimaryDistributor)

USE [master]
GO
EXEC sp_adddistributor @distributor = @@SERVERNAME, @password = N'$(DistributionPassword)';
GO

/*
Create the primary distribution database
SP_ADDDISTRIBUTIONDB Creates a new distribution database and installs the Distributor schema. 
The distribution database stores procedures, schema, and metadata used in replication.
*/

USE [master]
GO
EXEC sp_adddistributiondb @database = N'distribution', @security_mode = 1;
GO

/*
Set distribution recovery model to FULL and take a full backup
*/
ALTER DATABASE [distribution] SET RECOVERY FULL;
GO
BACKUP DATABASE [distribution] TO DISK = N'$(BackupLocation)\distribution_FULL.BAK'
GO

Now that we successfully configured the distribution database on the primary distributor, we’ll add it to the availability group.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:SETVAR PrimaryDistributor		"Dis1"
:SETVAR SecondaryDistributor	"Dis2"

:CONNECT $(PrimaryDistributor)

USE [master]
GO
 
ALTER AVAILABILITY GROUP [DisAG]
MODIFY REPLICA ON N'$(SecondaryDistributor)' WITH (SEEDING_MODE = AUTOMATIC);
GO
 
ALTER AVAILABILITY GROUP [DisAg]
ADD DATABASE [distribution];
GO
 
:CONNECT $(SecondaryDistributor)

USE [master]
GO
ALTER AVAILABILITY GROUP [DisAg] 
GRANT CREATE ANY DATABASE;
GO

The last step will be setting up the distribution database on the secondary distributor.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
 
:SETVAR SecondaryDistributor    "Dis2"
:SETVAR DistributionPassword    "Password!"
 
/*
Installing the secondary server as a Distributor
SP_ADDDISTRIBUTOR Creates an entry in the sys.sysservers table, marks the server entry as a Distributor, and stores property information. 
This stored procedure is executed at the Distributor on the master database to register and mark the server as a distributor.
*/
 
:CONNECT $(SecondaryDistributor)
  
USE [master]
GO
EXEC sp_adddistributor @distributor = @@SERVERNAME, @password = N'$(DistributionPassword)';
GO
 
EXEC sp_adddistributiondb 'distribution'; 
GO

At this point, the availability group dashboard for DisAG should turn green, and the distribution database should be synchronised.

Because the recovery model of the distribution database was changed from SIMPLE to FULL, we need to ensure the log backup solution will include this database. If you use Ola Hallengren’s SQL Server maintenance solution, the easiest option is adding the distribution database to the [DatabaseBackup – USER_DATABASES – LOG] SQL job on Dis1 and Dis2.

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES, distribution',
@Directory = '',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y';

2. Configure publishers on all distribution databases

A prerequisite for this step is configuring a network share as the default working directory to store snapshot data and schema files for all publications. Ensure the service account used by the SQL Server Agent on both distributors has full access to this network share.

With the prerequisite complete, we can now configure both publishers to use the distribution database created in Chapter 1.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:SETVAR PrimaryPublisher		"Pub1"
:SETVAR SecondaryPublisher		"Pub2"
:SETVAR PrimaryDistributor		"Dis1"
:SETVAR SecondaryDistributor	"Dis2"
:SETVAR DistributionDatabase	"distribution"
:SETVAR ReplicationShare		"\\dis1\ReplicationShare"

:CONNECT $(PrimaryDistributor)

USE [master]
GO
sp_adddistpublisher @publisher = '$(PrimaryPublisher)', @distribution_db = '$(DistributionDatabase)', @working_directory = '$(ReplicationShare)';
GO 
sp_adddistpublisher @publisher = '$(SecondaryPublisher)', @distribution_db = '$(DistributionDatabase)', @working_directory = '$(ReplicationShare)';
GO

SELECT [Server] = @@SERVERNAME, * FROM sys.servers;
GO
SELECT * FROM [distribution].[dbo].[MSreplservers]
GO
 
:CONNECT $(SecondaryDistributor)

USE [master]
GO
sp_adddistpublisher @publisher = '$(PrimaryPublisher)', @distribution_db = '$(DistributionDatabase)', @working_directory = '$(ReplicationShare)';
GO 
sp_adddistpublisher @publisher = '$(SecondaryPublisher)', @distribution_db = '$(DistributionDatabase)', @working_directory = '$(ReplicationShare)';
GO

SELECT [Server] = @@SERVERNAME, * FROM sys.servers;
GO
SELECT * FROM [distribution].[dbo].[MSreplservers]
GO

You will also notice that new linked servers have been created on both distributors.

Now is also a good opportunity to grant permissions to the distribution service account on the publisher and subscriber databases.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:SETVAR PrimaryPublisher		"Pub1"
:SETVAR SecondaryPublisher		"Pub2"
:SETVAR PrimarySubscriber		"Sub1"
:SETVAR SecondarySubscriber		"Sub2"
:SETVAR PublicationDatabase		"PubDB"
:SETVAR SubscriptionDatabase	"SubDB"
:SETVAR DistributionServiceAcc	"AD\dist_svc"

:CONNECT $(PrimaryPublisher)

USE [master]
GO
CREATE LOGIN [$(DistributionServiceAcc)] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

USE [$(PublicationDatabase)]
GO
CREATE USER [$(DistributionServiceAcc)] FOR LOGIN [$(DistributionServiceAcc)]
GO

USE [$(PublicationDatabase)]
GO
ALTER ROLE [db_owner] ADD MEMBER [$(DistributionServiceAcc)]
GO

:CONNECT $(SecondaryPublisher)

USE [master]
GO
CREATE LOGIN [$(DistributionServiceAcc)] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

:CONNECT $(PrimarySubscriber)

USE [master]
GO
CREATE LOGIN [$(DistributionServiceAcc)] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

USE [$(SubscriptionDatabase)]
GO
CREATE USER [$(DistributionServiceAcc)] FOR LOGIN [$(DistributionServiceAcc)]
GO

USE [$(SubscriptionDatabase)]
GO
ALTER ROLE [db_owner] ADD MEMBER [$(DistributionServiceAcc)]
GO

:CONNECT $(SecondarySubscriber)

USE [master]
GO
CREATE LOGIN [$(DistributionServiceAcc)] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

3. Configure publishers

We need to configure DisListener (listener configured for the distributor Availability group) as the remote distributor on both publishers.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:SETVAR PrimaryPublisher		"Pub1"
:SETVAR SecondaryPublisher		"Pub2"
:SETVAR DistributionListener	"DisListener"
:SETVAR DistributionPassword	"Password!"

:CONNECT $(PrimaryPublisher)

USE [master]
GO
EXEC sp_adddistributor @distributor = '$(DistributionListener)', @password = '$(DistributionPassword)'
GO
SELECT * FROM sys.servers
GO

:CONNECT $(SecondaryPublisher)

USE [master]
GO
EXEC sp_adddistributor @distributor = '$(DistributionListener)', @password = '$(DistributionPassword)'
GO
SELECT * FROM sys.servers
GO

We can confirm by checking Publisher properties in SSMS.

We notice that the repl_distributor linked server is now pointing to DisListener on both publishers.

4. Create a publication

On the primary publisher, enable replication on the published database PubDB. This setting will be replicated by the availability group mechanism on the secondary publisher.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:SETVAR PrimaryPublisher		"Pub1"
:SETVAR PublicationDatabase		"PubDB"

:CONNECT $(PrimaryPublisher)

USE [master]
GO
EXEC sp_replicationdboption @dbname = '$(PublicationDatabase)', @optname = 'publish', @value = 'true';
GO
SELECT name, is_published FROM sys.databases WHERE name = '$(PublicationDatabase)'
GO

On the primary distributor, redirect both publishers to PubListener (listener for the publication availability group).

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:SETVAR PrimaryPublisher		"Pub1"
:SETVAR SecondaryPublisher		"Pub2"
:SETVAR PrimaryDistributor		"Dis1"
:SETVAR SecondaryDistributor	"Dis2"
:SETVAR PublicationDatabase		"PubDB"
:SETVAR PublisherAG				"PubListener"

:CONNECT $(PrimaryDistributor)

USE [distribution]
GO
EXEC sp_redirect_publisher 
	@original_publisher		= '$(PrimaryPublisher)'
	, @publisher_db			= '$(PublicationDatabase)'
	, @redirected_publisher	= '$(PublisherAG)';
GO
EXEC sp_redirect_publisher 
	@original_publisher		= '$(SecondaryPublisher)'
	, @publisher_db			= '$(PublicationDatabase)'
	, @redirected_publisher	= '$(PublisherAG)';
GO
SELECT [Server] = @@SERVERNAME, * FROM [dbo].[MSredirected_publishers];
GO

:CONNECT $(SecondaryDistributor)

USE [distribution]
GO
SELECT [Server] = @@SERVERNAME, * FROM [dbo].[MSredirected_publishers];
GO

We can also use the sp_validate_replica_hosts_as_publishers to make sure redirecting works as expected.

Next, we’ll create a transactional replication between PubDB and SubDB. Make sure you use availability group listeners when setting up your own transactional replication.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:SETVAR PublisherListener		"PubListener"
:SETVAR SubscriberListener		"SubListener"
:SETVAR PublisherDatabase		"PubDB"
:SETVAR SubscriberDatabase		"SubDB"

:CONNECT $(PublisherListener)

USE [$(PublisherDatabase)]
GO
EXEC sp_addpublication 
	@publication = N'TestPublication'
	, @description = N'Transactional publication of database ''$(PublisherDatabase)''.'
	, @sync_method = N'concurrent'
	, @retention = 0
	, @allow_push = N'true'
	, @allow_pull = N'true'
	, @allow_anonymous = N'false'
	, @enabled_for_internet = N'false'
	, @snapshot_in_defaultfolder = N'true'
	, @compress_snapshot = N'false'
	, @ftp_port = 21
	, @allow_subscription_copy = N'false'
	, @add_to_active_directory = N'false'
	, @repl_freq = N'continuous'
	, @status = N'active'
	, @independent_agent = N'true'
	, @immediate_sync = N'false'
	, @allow_sync_tran = N'false'
	, @allow_queued_tran = N'false'
	, @allow_dts = N'false'
	, @replicate_ddl = 1
	, @allow_initialize_from_backup = N'false'
	, @enabled_for_p2p = N'false'
	, @enabled_for_het_sub = N'false';
GO
exec sp_addpublication_snapshot 
	@publication = N'TestPublication'
	, @frequency_type = 1
	, @frequency_interval = 1
	, @frequency_relative_interval = 1
	, @frequency_recurrence_factor = 0
	, @frequency_subday = 8
	, @frequency_subday_interval = 1
	, @active_start_time_of_day = 0
	, @active_end_time_of_day = 235959
	, @active_start_date = 0
	, @active_end_date = 0
	, @job_login = null
	, @job_password = null
	, @publisher_security_mode = 1;
GO
EXEC sp_addarticle 
	@publication = N'TestPublication'
	, @article = N'PubTable'
	, @source_owner = N'dbo'
	, @source_object = N'PubTable'
	, @type = N'logbased'
	, @description = null
	, @creation_script = null
	, @pre_creation_cmd = N'drop'
	, @schema_option = 0x000000000803509F
	, @identityrangemanagementoption = N'manual'
	, @destination_table = N'PubTable'
	, @destination_owner = N'dbo'
	, @vertical_partition = N'false'
	, @ins_cmd = N'CALL sp_MSins_dboPubTable'
	, @del_cmd = N'CALL sp_MSdel_dboPubTable'
	, @upd_cmd = N'SCALL sp_MSupd_dboPubTable';
GO
EXEC sp_addsubscription 
	@publication = N'TestPublication'
	, @subscriber = N'$(SubscriberListener)'
	, @destination_db = N'$(SubscriberDatabase)'
	, @subscription_type = N'Push'
	, @sync_type = N'automatic'
	, @article = N'all'
	, @update_mode = N'read only'
	, @subscriber_type = 0;
GO
EXEC sp_addpushsubscription_agent 
	@publication = N'TestPublication'
	, @subscriber = N'$(SubscriberListener)'
	, @subscriber_db = N'$(SubscriberDatabase)'
	, @job_login = null
	, @job_password = null
	, @subscriber_security_mode = 1
	, @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 = 20230702
	, @active_end_date = 99991231
	, @enabled_for_syncmgr = N'False'
	, @dts_package_location = N'Distributor'
GO

The transactional replication is now up and running, delivering data from PubDB to SubDB.

While the replication deployment automatically configures a linked server on Dis1 pointing to the newly added subscriber SubListener, on Dis2 we need to manually deploy the same linked server.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:SETVAR SecondaryDistributor	"Dis2"
:SETVAR SubscriberListener		"SubListener"

USE [master]
GO
EXEC sp_addlinkedserver @server = N'$(SubscriberListener)', @srvproduct = N'SQL Server';
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'$(SubscriberListener)', @useself = N'True', @locallogin = NULL, @rmtuser = NULL, @rmtpassword = NULL;
GO

Endgame

Test, test, test! Now that we have everything set up, we need to ensure that failing over the publisher, distributor, or subscriber will not break the transactional replication in any way.

We’ll start with the easy ones, publisher and subscriber. Because none of the replication jobs sits on any of these two, failing over should work without issues.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:SETVAR SecondaryPublisher		"Pub2"
:SETVAR SecondarySubscriber		"Sub2"
:SETVAR PublisherAG				"PubAG"
:SETVAR SubscriberAG			"SubAG"
:SETVAR PublisherListener		"PubListener"
:SETVAR SubscriberListener		"SubListener"

:CONNECT $(SecondaryPublisher)

USE [master]
GO
ALTER AVAILABILITY GROUP [$(PublisherAG)] FAILOVER;
GO

:CONNECT $(SecondarySubscriber)

USE [master]
GO
ALTER AVAILABILITY GROUP [$(SubscriberAG)] FAILOVER;
GO

:CONNECT $(PublisherListener)

USE [PubDB]
GO
INSERT INTO [dbo].[PubTable] (ID) VALUES (1);
GO
SELECT [Server] = @@SERVERNAME, ID FROM [dbo].[PubTable];
GO
WAITFOR DELAY '00:02'; 
GO

:CONNECT $(SubscriberListener)

USE [SubDB]
GO
SELECT [Server] = @@SERVERNAME, ID FROM [dbo].[PubTable];
GO

We failed over PubDB from Pub1 to Pub2 and SubDB from Sub1 to Sub2, inserted a new row in PubTable, and the transaction was successfully replicated from PubDB to SubDB.

Next, we will failover the distributor and observe what happens with the replication jobs.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:SETVAR SecondaryDistributor	"Dis2"
:SETVAR DistributionAG			"DisAG"

:CONNECT $(SecondaryDistributor)

USE [master]
GO
ALTER AVAILABILITY GROUP [$(DistributionAG)] FAILOVER;
GO

At first glance, the Log Reader and Distribution agent jobs seem to stop running on Dis1 but fail to start on Dis2.

But if we wait a minute until the Monitor and sync replication agent jobs SQL job runs on both distributors, we’ll see the jobs cancelled and disabled on Dis1, while on Dis2, they are enabled and running.

Add a new row to PubTable to ensure the replication can deliver data successfully.

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:SETVAR PublisherListener		"PubListener"
:SETVAR SubscriberListener		"SubListener"

:CONNECT $(PublisherListener)

USE [PubDB]
GO
INSERT INTO [dbo].[PubTable] (ID) VALUES (2);
GO
SELECT [Server] = @@SERVERNAME, ID FROM [dbo].[PubTable];
GO
WAITFOR DELAY '00:00:10'; 
GO

:CONNECT $(SubscriberListener)

USE [SubDB]
GO
SELECT [Server] = @@SERVERNAME, ID FROM [dbo].[PubTable];
GO

We have confirmation that the transactional replication will work as expected even if the publisher, distributor or subscriber failover to a secondary.

Useful links:

Share the Post:

Related Posts