Walkthrough Publisher, Distributor, Subscriber in AlwaysOn Availability Groups

Share this Post

The steps below walkthrough setting SQL Server 2016 Replication Publisher, Distributor, and Subscriber each in an Always On Availability Group with one set of replicas residing one 1 subnet and 2nd set on another subnet simulating 2 different data centers (Pub1, Dist1, Sub2) <–> (Pub2, Dist2, Sub2).

 

These steps below combine the following postings:

 

Configure replication with Always On availability groups

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-2017

 

Set up replication distribution database in Always On availability group

https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availability-group?view=sql-server-2017

 

Replication Subscribers and Always On Availability Groups

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replication-subscribers-and-always-on-availability-groups-sql-server?view=sql-server-2017

 

Multi-Subnet Environment:

 

This walk-through contains a domain controller and 6 SQL Server. As you can see from the names below, each replication roles (Distributor, Publisher, Subscriber) will be running on their own pair servers in their own AlwaysOn Availability Group configuration across two subnets.

 

The two networks subnets simulate Replication roles running in two different data centers. For this configuration, both data centers are on the same “JAMES” domain, named after my late father and long-time teacher at Cochrane-Fountain City high.

 

 

 

 

The SQL Server installations have been upgraded to SQL 2016 CU13 to support distribution role in an Availability Group. As required, the Distributor role in replication topology will be in their own SQL Server instances in their own AlwaysOn Availability Group.

 

 

SQL Server Management Studio 17.9 upgrade for failover Distributor support was installed on all servers either configuring replication or running Replication Monitor https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

 

 

Initial Publisher Setup

 

The sample database AdventureWorks was restored as “AW“; the database rename was not required.

 

https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

 

USE [master]

RESTORE DATABASE [AW] FROM

DISK = N’C:\temp\AdventureWorks2016.bak’ WITH
FILE = 1,

MOVE N’AdventureWorks2016_Data’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AW2016_Data.mdf’,

MOVE N’AdventureWorks2016_Log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AW2016_Log.ldf’,

NOUNLOAD,
STATS = 5

GO

 

— To remove personal ownership, database owner changed to ‘sa’

— This helps resolves security issues later by Log Reader.

USE AW

GO

 

sp_changedbowner ‘sa’

GO

 

Database AW set into Full Recovery, 1 full and 1 transaction log backup were taken.

 

— To support AlwaysOn Availability Groups, change to FULL recovery and take backups

— Settings and backup via SSMS GUI or TSQL commands

USE master

GO

 

ALTER DATABASE [AW] SET RECOVERY FULL WITH NO_WAIT

GO

 

 

BACKUP DATABASE [AW] TO

DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AW-FULL.back’

WITH NOFORMAT, NOINIT,
NAME = N’AW-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,
STATS = 10

GO

 

BACKUP LOG [AW] TO

DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AW-LOG.trn’

WITH NOFORMAT, NOINIT,
NAME = N’AW-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,
STATS = 10

GO

 

The Agent XP server configuration setting was enabled for SQL Agent Jobs and a database maintenance plan was configure to ensure ongoing FULL and Transaction Log backups were maintained.

 

EXEC SP_CONFIGURE ‘show advanced options’,1

GO

RECONFIGURE

GO

 

EXEC SP_CONFIGURE ‘show advanced options’

 

EXEC SP_CONFIGURE ‘Agent XPs’,1

GO

RECONFIGURE

GO

 

— Configuration option ‘Agent XPs’ changed from 0 to 1. Run the RECONFIGURE statement to install.

 

Using Availability Group Wizard in SQL Server Management Studio, PubAG with listener PubAGListener was created for the AW database for replicas PUB1 and PUB2.

 

 

 

In PubAG properties, PUB1, and PUB2 were set to “Readable Secondary” = Yes as required for Replication. Optionally, Automatic Seeding allowed database AW to be copied to Secondary without manual backup-restore.

 

 

In this example, with 2 subnets, 2 IP address are added for the PubAGListener.

 

 

Optional Custom Listener Port : If you’re using custom port for your listener, as shown below, you’ll need to create alias for your listener on each server using that listener.

 

 

Optional: Alias are configured using SQL Server Configuration Manager.

 

 

Subscriber Initial Setup

 

Since the subscriber is initialized via Replication Snapshot, simply create a new empty database, here called AWTEST on SUB1. Like the AW database, the AWTEST subscriber database was set to FULL recovery, a full and transactional log database backups were taken and a database maintenance plans backup database and TLOG (*.trn) file at regular intervals keeping TLOG at reasonable size and supporting recovery.

 

 

Like the Publisher, an AlwaysOn Availability Group SubAG with listener SubAGListener was created for the AWTEST subscriber database using the SQL Management Studio Wizards.

 

 

Replica nodes are SUB1 and SUB2, both again configured for Readable Secondary to allow load balance “read” activity from either subscriber and Seeding Mode = Automatic making initial setup without backup-restore.

 

 

In this topology, the Log Reader and Distributor jobs run on the Distributor server. Therefore SQL Agent is not required for Replication on the PUB1, PUB2, SUB1, or SUB2. However, it is required for non-replication SQL Server Agent jobs such as maintenance plans.

 

 

Distributor Setup

 

https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution-availability-group

 

The @password is assigned to the repl_distributor linked server used by Replication for administrative tasks. This same password is used in later scripts.

 

Step 1: Configure DIST1, DIST2 as distributors using TSQL only

 

:Connect DIS1 

use master

exec sp_adddistributor @distributor = N’DIS1′, @password = N’Password!’

GO

 

:Connect DIS2

use master

exec sp_adddistributor @distributor = N’DIS2′, @password = N’Password!’

GO

 

— Step 2: Configure distribution database on DIST1

 

:Connect DIS1 

use master

GO

 

exec sp_adddistributiondb @database = N’distribution’,

@data_folder = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA’,

@data_file = N’distribution.MDF’, @data_file_size = 50,

@log_folder = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA’,

@log_file = N’distribution.LDF’, @log_file_size = 10,

@min_distretention = 0, @max_distretention = 72, @history_retention = 48,

@deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000,

@security_mode = 1

GO

 

— To support AlwaysOn Availability Groups, change to full recovery and take full backup

— Settings and backup can be performed using SSMS GUI or TSQL commands

ALTER DATABASE [distribution] SET RECOVERY FULL WITH NO_WAIT

GO

 

BACKUP DATABASE [distribution]

TO
DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\distribution.bak’

WITH NOFORMAT, NOINIT,

NAME = N’distribution-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,
STATS = 10

GO

 

BACKUP LOG [distribution] TO

DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\distribution.trn’

WITH NOFORMAT, NOINIT,
NAME = N’distribution-LOG Database Backup’, SKIP, NOREWIND, NOUNLOAD,
STATS = 10

GO

 

 

Step 3: Create Availability Group for DIST1 and DIST2 using SSMS Wizard or TSQL scripts

 

 

Here we’ve created the Availability Group DisAg for replicas DIS1 and DIS2. You can configure the distributor role listener when creating the availability group.

 

 

Next step is to add the ‘distribution‘ into the DisAg availability group. Notice the distribution database, being a system database, does not appear in the list of available database.

 

 

Configuring the AG for distribution databases can only be done through scripts. The “GRANT CREATE ANY DATABASE;” supports automatic seeding.

 

— YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE

— OR CONNECT TO EACH SERVER INDEPENDENTLY

 

:Connect DIS1

 

USE [master]

GO

 

ALTER AVAILABILITY GROUP [DisAg]

MODIFY REPLICA ON N’Dis2′ WITH (SEEDING_MODE = AUTOMATIC)

GO

 

USE [master]

GO

 

ALTER AVAILABILITY GROUP [DisAg]

ADD DATABASE [distribution];

GO

 

:Connect DIS2

ALTER AVAILABILITY GROUP [DisAg] GRANT CREATE ANY DATABASE;

GO

 

 

Step 4: Configure listener (DisAgListener). Can be performed when DisAG was created.

 

While not all configuration use multiple subnets, in this example, the Distributor listener DisAGListener has 2 subnets.

 

 

Resulting cluster configuration contains Client Access Point for both virtual IP subnet addresses.

 

 

When the distribution database is participating in an Availability Group, the status in SSMS Object Explorer doesn’t show “synchronizing” as it does with user databases. Use AlwaysOn Dashboard to confirm “Healthy”.

 

 

 

 

Step 5: For recovery and log truncation configure Full and TLOG Backups and maintenance plans

 

 

 

Step 6: On DIST2 add distribution database, no additional parameters.

 

:Connect DIS2

sp_adddistributiondb ‘distribution’

GO

 

Step 7: Add all publisher(s) to all distributor(s)

 

In this walkthrough Publishers PUB1 and PUB2, also participate in an Availability Group with Distributors DIS1 and DIS2. These steps connect each distributor and to both publisher servers. Notice reference to DNS server names, not FQND, and not listener names. The working_directory is shared location for Replication Snapshot files. For this walkthrough I’m using “\\dc\share\repl” but can be any network location accessible from all servers in the topology.

 

Optionally, if setting up subscribers using backup\restore, snapshot folder is not used and location can be any valid local folder.

 

:Connect DIS1 

sp_adddistpublisher @publisher= ‘PUB1’, @distribution_db= ‘Distribution’, @working_directory= \\dc\Share\Repl

GO

 

sp_adddistpublisher @publisher= ‘PUB2’, @distribution_db= ‘Distribution’, @working_directory= \\dc\Share\Repl

GO

 

:Connect DIS2

sp_adddistpublisher @publisher= ‘PUB1’, @distribution_db= ‘Distribution’, @working_directory= \\dc\Share\Repl

GO

 

sp_adddistpublisher @publisher= ‘PUB2’, @distribution_db= ‘Distribution’, @working_directory= \\dc\Share\Repl

GO

 

When step completes, linked servers on DIST1 and DIST will point to PUB1, PUB2, and repl_distributor.

 

 

Publisher Workflow

 

:Connect PUB1

— To add the distribution database AG listener as the distributor, on PUB

Use MASTER

GO

sp_adddistributor @distributor = ‘DisAGListener’, @password = ‘Password!’

GO

 

When completes, linked server on PUB1 will have a linked server to repl_distributor which is mapped in sys.sysservers to DISAGLISTENER.

 

 

:Connect PUB2

 

The error below occurs when not logged on directly to PUB2 while executing sp_adddistributor. Make Remote Desktop connection to PUB2 then execute sp_adddistributor.

 

/*

OLE DB provider “SQLNCLI11” for linked server “repl_distributor” returned message “Unable to complete login process due to delay in opening server connection”.

Msg 7303, Level 16, State 1, Procedure sp_adddistributor, Line 168 [Batch Start Line 102]

Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server “repl_distributor”.

*/

 

— To add the distribution database AG listener as the distributor, on PUB

— ** Must be executed directly on the Pub2 **

sp_adddistributor @distributor = ‘DisAGListener’, @password = ‘Password!’

GO

 

Here we can see PUB2 now has linked server to repl_distributor which is mapped in sys.sysservers to the distributor listener DISAGLISTENER.

 

 

Create Publication and specify redirected publisher

 

:Connect PUB1

USE master;

GO

EXEC sys.sp_replicationdboption


@dbname = ‘AW’,

@optname = ‘publish’,

@value = ‘true’

 

Create publication using SSMS Replication wizard or via TSQL replication scripts.

 

— exec sp_addpublication @publication = N’TranProducts’,…

 

 

:Connect DIS1 

— On Primary Distributor,add Publisher’s listener

USE distribution;

GO

EXEC sys.sp_redirect_publisher

@original_publisher = ‘pub1’,

@publisher_db = ‘AW’,

@redirected_publisher = ‘PubAGListener’;

GO

 

Validate redirected publisher

 

Make remote desktop connection to DIST1 and execute command to validate redirected publisher. 

 

USE distribution;

GO

DECLARE @redirected_publisher sysname;

EXEC sys.sp_validate_replica_hosts_as_publishers

@original_publisher = ‘pub1’,

@publisher_db = ‘AW’,

@redirected_publisher = @redirected_publisher output;

PRINT @redirected_publisher

 

— Sample Output: PubAGListener

 

 

sp_Add_Subscription using TSQL Script to specify subscriber’s listener.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replication-subscribers-and-always-on-availability-groups-sql-server

 

In this walkthrough our subscriber also participates in an availability group. We’ll use PUSH subscription and specify the subscriber’s Listener SubAGListener as the @subscriber.

 

:Connect PUB1 

— On PUB1, where publication was created, add subscription for SubAGListener to publication TranProducts

 

USE AW

GO

 

EXEC sp_addsubscription @publication = N’TranProducts’,

@subscriber = N’SubAGListener’,

@destination_db = N’AWTEST’,

@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’TranProducts’,

@subscriber = N’SubAGListener’,

@subscriber_db = N’AWTEST’,

@job_login = null, @job_password = null, @subscriber_security_mode = 1;

GO

 

 

Checking the Distribution Agent job step properties, notice the listener name for the -Subscriber parameter. The re-directed Publisher entry we made earlier will handle the PUB1 to PubAGListener mapping.

 

 

The DIS2 needs to connect to Subscriber’s Listener when replication changes. Execute command below on DIS2 to add a linked server to the Subscriber’s Listener SubAGListener. Note connection is using Listener name, not FQDN, not server name. If the subscriber was not participating in an Availability Group, then @server would equal subscriber’s SQL Server name.

 

 

:Connect DIS2

— On DIST2, add a linked server for Subscribers Listener

 

EXEC master.dbo.sp_addlinkedserver @server =N’SubAGListener’, @srvproduct=N’SQL Server’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’SubAGListener’,

@useself=N’True’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

 

 

To support Replication, a linked server from Publishers to Subscribers is also required. This was created on PUB1 when sp_addsubscription was executed. For PUB2, we’ll need to directly call sp_addlinkedserver command using the subscriber’s Listener SubAGListener.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server

 

 

:Connect PUB2

 

— On PUB2, create a linked server to Subscribers Listener

EXEC master.dbo.sp_addlinkedserver @server =N’SubAGListener’, @srvproduct=N’SQL Server’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’SubAGListener’,

@useself=N’True’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

 

When setup, Publisher(s) will show linked server connections to repl_distributor (mapped to DISAGLISTENER and to the subscribers Availability Group listener SubAGListener.

 

 

Replication + Multi-Subnet Support

https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/

 

As of March 2019, when using Replication in a multi-subnet environment using RegisterAllProvidersIP =1 setting, Replication Agents may timeout when starting. To work around this problem, change the cluster properties to RegisterAllProvidersIP = 0 and reduce the HostRecordTTL = 10 (10-120 seconds) registering only the “active” Listener\IP in DNS.

 

In our environment we have 3 different listeners. We changed all 3 listeners to RegisterAllProvidersIP
= 0, and 10 second DNS refresh rate.

 

DisAg_DisAGListener

 

 

Using Windows Powershell (administrator), configure DNS for RegisterAllProvidersID = 0

 

 

For each availability group configured, supply the correct “Network Name” for the Listener. In this example, the resource name for the Distributor’s Listener is DisAg_DisAGListener.

 

Import-Module FailoverClusters

Get-ClusterResource

    This displays name “DisAg_DisAGListener” used in next two commands

Get-ClusterResource DisAg_DisAGListener | Set-ClusterParameter -Name HostRecordTTL -Value 10

Get-ClusterResource DisAg_DisAGListener | Set-ClusterParameter -Name RegisterAllProvidersIP -Value 0

 

 

Connect to PUB1 for Publisher’s listener setting, then connect to SUB1 and repeat for Subscriber’s listener settings.

 

PubAG_PubAGListener

 

 

Import-Module FailoverClusters

Get-ClusterResource

    This displays name “PubAg_PubAGListener” used in next two commands

Get-ClusterResource PubAG_PubAGListener | Set-ClusterParameter -Name HostRecordTTL -Value 10

Get-ClusterResource PubAG_PubAGListener | Set-ClusterParameter -Name RegisterAllProvidersIP -Value 0

 

 

SubAG_subaglistener

Import-Module FailoverClusters

Get-ClusterResource

    This displays name “SubAg_SubAGListener” used in next two commands

Get-ClusterResource SubAG_subaglistener | Set-ClusterParameter -Name HostRecordTTL -Value 10

Get-ClusterResource SubAG_subaglistener | Set-ClusterParameter -Name RegisterAllProvidersIP -Value 0

 

 

Missing Replication Agent Jobs

 

Before failover, if you compare SQL Agent Replication Job from DIST1 and DIST2 you may notice difference. However, after an Availability Group failover, a new Agent job called “Monitor and sync replication agent jobs” running every 1 minute, and updates SQL Agent jobs on DIST2 with the correct parameters.

 

 

Testing Publisher Failover

 

Using Replication Monitor register the Publisher’s listener PUBAGLISTENER.

 

 

 

 

Connect to the Distributor’s listener to retrieve publisher settings.

 

 
 

 
 

If SQL Server Management Studio 17.9 is installed, Replication Monitor will correctly display the Publisher’s listener PubAGListener, fully supporting all functionality including Tracer Tokens.

 

 

When initiate failover from SQL Management Studio you’ll notice the Log Reader agent enter failed state and attempt to reconnect to the Publisher’s listener PubAGListener.

 

Remember to always initiate failover from SQL Management Studio or TSQL, never from Cluster Administrator.

 

 

As expected, Replication Monitor, Log Reader details shows connection to publisher’s Listener was closed during Publisher’s failover

 

 

HostRecordTTL

 

In this walkthrough with 2 subnets, following the failover, DNS will map PubAGListener to IP address registered for the 2nd subnet. When RegisterAllProvidersIP = 0. The Log Reader Agent, a client connection, will refresh its IP address table within 10 seconds (HostRecordTTL = 10) picking up the PubAGListener new IP address on 2nd subnet.

 

Looking at the Log Reader SQL Agent Job history, you’ll see normal delivery, then “connection forcibly close”, “The replication agent encountered an error and is set to restart within the job step retry interval.” (fishhook), followed by “The replication agent has been successfully started” reconnection looking something like this:

 

 

Testing Distributor Failover

 

Again, using SSMS, you can initiate failover of the Distributor or Subscriber role from either the Primary or Secondary replica.

 

 

When the Distributor Availability Group fails over, both the Log Reader (Pull) and the Distribution Agent (Push) jobs fail, but then are restarted on the new Primary distributor.

 

Expected Log Reader messages during Distributor failover:

 

Error messages:

The process could not execute ‘sp_repldone/sp_replcounters’ on ‘PUB2’. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)

Get help: http://help/MSSQL_REPL20011

Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure. (Source: MSSQLServer, Error number: 18752)

Get help: http://help/18752

The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)

Get help: http://help/MSSQL_REPL22017

The process could not execute ‘sp_repldone/sp_replcounters’ on ‘PUB2’. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)

Get help: http://help/MSSQL_REPL22037

 

 

 

To verify when Replication agents have restarted on the new distributor replica and reconnected, insert Tracer Token. Here the latency from Distributor failover to fully replicated the pending Tracer Token was 7 second.

 

 

Testing Subscriber Failover

 

Similar, when subscriber would failover to synchronized secondary replica, the Push Distribution agent will enter “retry”, then reconnect via Subscribers “SubAgListener” to the new primary replica.

 

Error messages:

Agent ‘PUB1-AW-TranProducts-SUBAGLISTENER-1002’ is retrying after an error

 

 

 

Once again Replication Monitor Tracer Tokens validate re-connection are complete and data is once again flowing through the topology.

 

 

Chris Skorlinski
Microsoft SQL Server Escalation Services


Share this Post
Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.