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


Set up replication distribution database in Always On availability group


Replication Subscribers and Always On Availability Groups


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.


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

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.

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


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

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.

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.

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.

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

Publisher Workflow

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


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

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

Validate redirected publisher

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

sp_Add_Subscription using TSQL Script to specify subscriber’s listener.


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.

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.

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.


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


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.


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


    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.



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


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

One thought on “Walkthrough Publisher, Distributor, Subscriber in AlwaysOn Availability Groups”

  1. Just in time, looking for the AG-Replication combination details of this sort. Excellent Article…!! Thank you Chris.

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.