Setting up Replication on a database that is part of an AlwaysOn Availability Group

This blog post gives detailed steps on setting up Transactional replication on a database that is part of an AlwaysOn availability Group. The below technet article lists the same with T-SQL statements:

https://technet.microsoft.com/en-us/library/hh710046.aspx

In this blog, I am going to list out the steps and screenshots too, wherever applicable. This blog doesn't include the steps to setup AlwaysOn AG.

 

What is Supported?

1) SQL Server replication supports the automatic failover of the publisher, the automatic failover of transactional subscribers, and the manual failover of merge subscribers. The failover of a distributor on an availability database is not supported.

2) In an AlwaysOn availability group a secondary database cannot be a publisher. Re-publishing is not supported when replication is combined with AlwaysOn Availability Groups.

 

 

 

 

Environment

AlwaysOn

SRV1: Synchronous Replica - Current Primary

SRV2: Synchronous Replica

SRV3: Asynchronous Replica

Availability Group :MyAvailabilityGroup

AG database : MyNorthWind

AG Listener : AGListener

 

 

Below is the environment we will be building at the end of this blog:

SRV1: Original Publisher

SRV2: Publisher Replica

SRV3: Publisher Replica

SRV4: Distributor and Subscriber (You can choose a completely new server to be the distributor as well, however do not have a distributor on any of the publishers in this case as the failover of a distributor is not supported in this case).

Overview

The following sections build the environment described above:

- Configure a remote distributor

- Configure the Publisher at the original Publisher

- Configure Remote distribution on possible publishers

- Configure the Secondary Replica Hosts as Replication Publishers

- Redirect the Original Publisher to the AG Listener Name

- Run the Replication Validation Stored Procedure to verify the Configuration

- Create a Subscription

 

1. Configure a remote distributor

The distributor should not be on the current (or intended) replica of the availability group of which the publishing database is part of. This just means that Distributor in our case, should not be on SRV1, SRV 2, SRV3 because these servers are part of the AG that has the publishing database (MyNorthWind).

We can have a dedicated server (which is not part of the AG) acting as a distributor or we can have the distributor on the subscriber (provided subscriber is not part of an AG).

 Let's configure distribution on SRV4.

• Right click on Replication and select "Configure Distribution"?. The below screen comes up.

 

 

• We'll select the first option as we want SRV4 as a distributor.

 

•  Specify the snapshot folder location.

 

 

• We'll go with the default distribution database folder.

 

 

 

• In the below screen, we need to specify SRV1, SRV2 and SRV3 as publishers. Click on Add and then Add SQL Server Publisher. Connect to the 3 servers that can act as publishers. Note that SRV4 already exists in the list and you can choose to leave it that way.

.

 

 

• This is how it should look like with all publishers added.

 

 

• Enter in the password that the remote publishers will use to connect to the distributor.

 

 

• Click on Next and then "Configure Distribution" and then next.
• Click on Finish and now, the distribution is successfully set up.

 

2. Configure the Primary Replica as the original Publisher

Define SRV1 as the original publisher as it is currently the primary replica. You can have any of the AG replica as the original publisher, as long as it is the current primary replica.

 

• In SQL Server Management Studio, use Object Explorer to connect to SRV1 and drill into Replication and then Local Publications.

 

• Right click Local Publications and choose New Publication. Click Next.
• In the Distributor dialog, choose the option 'Use the following server as the Distributor', click the Add button and add SRV4. Click Next.
• Enter the same password that was used in Step 7 of "Configure the distributor".

 

 

• Select the database to be published: MyNorthWind

 

•  We'll be setting up Transactional Publication.

 

 

• In the Articles and Filter Table rows dialogs, make your selections.
• In the Snapshot dialog, for now, choose the 'Create a snapshot immediately..' and click Next.
• In the Agent Security dialog box, specify the account under which Snapshot Agent and Log Reader Agent will run. You can also use the SQL Server Agent account to run the Snapshot Agent and Log Reader Agent.
• In the Wizard Actions dialog, select 'Create the publication' and click Next.
• Give the publication a name and click Finish in the Complete the Wizard dialog.

 

 

3) Configure Remote distribution on possible publishers

For the possible publishers and secondary replicas: SRV2 and SRV3, we'll have to configure the distribution as a remote distribution that we created on SRV1.

• Launch SQL Server Management Studio. Using Object Explorer, connect to SRV2 and right click the Replication tab and choose Configure Distribution. Choose 'Use the following server as the Distributor' and click Add. Select SRV4 as the distributor.

 

 

• In the Administrator Password dialog, specify the same password to connect to the Distributor.

 

• In the Wizard Actions dialog, accept the default and click Finish.
• Click finish and follow the same steps on SRV3 to configure the distribution as SRV4.

 

4) Configure the Secondary Replica Hosts as Replication Publishers

 In the event that a secondary replica transitions to the primary role, it must be configured so that the secondary can take over after a failover. All possible publishers will connect to the subscriber using a linked server. To create a linked server to the subscriber, SRV4 , run the below query on the possible publishers: SRV2 and SRV3.

EXEC sys.sp_addlinkedserver

@server = 'SRV4';

 

5) Redirect the Original Publisher to the AG Listener Name

We have already created an AG listener named AGListener. At the distributor (Connect to SRV4) , in the distribution database, run the stored procedure sp_redirect_publisher to associate the original publisher and the published database with the availability group listener name of the availability group.

 

USE distribution;

GO

EXEC sys.sp_redirect_publisher

@original_publisher = 'SRV1,

@publisher_db = 'MyNorthWind',

@redirected_publisher = 'AGListener';

 

6) Run the Replication Validation Stored Procedure to verify the Configuration

At the distributor (SRV4), in the distribution database, run the stored procedure sp_validate_replica_hosts_as_publishers to verify that all replica hosts are now configured to serve as publishers for the published database.

 

USE distribution;

GO

DECLARE @redirected_publisher sysname;

EXEC sys.sp_validate_replica_hosts_as_publishers

@original_publisher = 'SRV1',

@publisher_db = 'MyNorthWind',

@redirected_publisher = 'AGListener';

 

The stored procedure sp_validate_replica_hosts_as_publishers should be run from a login with sufficient authorization at each availability group replica host to query for information about the availability group. Unlike sp_validate_redirected_publisher, it uses the credentials of the caller and does not use the login retained in msdb.dbo.MSdistpublishers to connect to the availability group replicas.

 

7) Create a subscription

• Right click on the publication: Publication_AlwaysOn and select New Subscriptions.

 

•  Select the publication on SRV1.

 

 

• We'll create a push Subscription, however a pull subscription will work as well.

 

 

• Select the subscriber instance as SRV4 and a subscriber database

 

 

• Select the SQL Server Agent credentials to run the Distribution Agent.

 

 

 

• Select "Initialize at First Synchronisation" on the subscriber SRV4.

 

• Select the subscriber instance as SRV4 and a subscriber database.

 

How to use the Replication Monitor ?

After failover to a secondary replica, Replication Monitor is unable to adjust the name of the publishing instance of SQL Server and will continue to display replication information under the name of the original primary instance of SQL Server. After failover, a tracer token cannot be entered by using the Replication Monitor, however a tracer token entered on the new publisher by using Transact-SQL, is visible in Replication Monitor.

At each availability group replica, add the original publisher to Replication Monitor.

 

Publisher failover Demonstration

In this section, we'll failover the Availability Group from the current primary replica and replication publisher: SRV1 to secondary replica and possible publisher:SRV2. This will not impact the working of Replication in any way.

 

• The Failover Availability Group Wizard comes up.

 

• Select the secondary replica you want to failover the AG to, in this case, SRV2.

 

 

• Connect to SRV2 which is the SQL instance acting as the secondary replica.

 

 

• Click on Finish and the failover to SRV2 should complete successfully.
• We can also failover to asynchronous secondary replica and possible publisher, SRV3 in the same way.

 

 

• This will cause data loss as SRV3 is an Asynchronous Replica.

 

• Click on Finish.
• However, after the failover to a Asynchronous secondary replica, the data movemnet on the AG database, MyNorthWinds is paused on the 2 secondary replicas-SRV1 and SRV2.
• The database state will show "Not Synchronizing" on SRV2 and SRV1.

 

 

• Right-click the availability database, MyNorthwind under AlwaysOn High Availability drop-down and select "Resume Data Movement". Follow the same on SRV1.

 

• We can go with the default selection "Continue executing after error".

 

 

 

• Resume data movemnet on SRV1 as well and AlwaysOn database MyNorthWind will show as "Synchronizing" instead of "Synchronized" as SRV3 is the primary replica now and it was et as an Asynchronous Replica initially.
• After making these changes, Replication will function as usual.

 

 

Prabhjot Kaur

SQL Server AlwaysOn Support Team

Comments

• Anonymous
  May 13, 2014
  Why can the distributor not be on the current (or intended) replica of the availability group of which the publishing database is part of ? what is the reasoning behind this requirement ?

• Anonymous
  June 18, 2014
  LoganHaving the distribution database on any of the replicas of the AG is not a good design. If any of those replicas was to go down, replication would fail as the Distribution DB will be inaccessible.So, it is recommended to have the distribution DB outside of the AG. You can also think of having the distribution DB on a SQL FCI.

• Anonymous
  July 17, 2014
  Thx Prabhjot Kaur, this was very helpful, BTW snapshot, p2p and merge are replication options to configure for an AG db? or only Transactional?

• Anonymous
  August 20, 2014
  Glad it helped Alfonso.  Transactional, Snapshot and Merge replication are supported within an AG.

• Anonymous
  August 26, 2014
  nice workbut when i am gonna create an AG the following error shown > the following folder locations do not exist on the server instance that hosts secondary replicacdn.imghack.se/.../e1111ce11e21144ac7c99baf05fe6429.jpg ??

• Anonymous
  September 01, 2014
  HelmyYou are seeing that error because the database file locations are not the same on all your replicas.The AG creation wizard checks for the same path on all the secondary replicas you are trying to add if you select the Full synchronization method, and if the data and log file paths are not the same across all the replicas, throws up the error you mentioned.The workarounds to this error are:1) Detach and attach the database to have the same file paths across all the replicas. This would mean you can use the full synchronization method to restore the copy of database on the secondary.2) Use the "Join Only" method and do the database restore manually on the secondary replicas. This will skip the database file location check while creating the AG.HTH!

• Anonymous
  November 06, 2014
  The comment has been removed

• Anonymous
  November 11, 2014
  If we can't setup AG for the Distributor, what are the options for a distributor to failover "DR Site" to a remote site that is the destination for Publishers & Subscribers replicas?

• Anonymous
  November 13, 2014
  The comment has been removed

• Anonymous
  November 25, 2014
  can you give more details of the linked server that is needed to be created in each secondary replica? linked server is needed for both push and pull replication? what kind of data flows from publisher to subscribers thru linked servers? and what are the minimum security settings neededthanks

• Anonymous
  November 26, 2014
  If SRV1 is totally power off, will the data replication be suspended?

• Anonymous
  September 03, 2015
  Thanks for the procedure to setup replication with AGs! Replication worked for me as well after the primary replica fails over to secondary server. However, once the original primary server was taken offline(shutdown), replication stopped working completely. The log reader did not even pick up transactions from the secondary replica(which becomes primary when the original primary goes down) publisher. Taking the example in this article, after MyNorthWind DB is failed over from SRV1 to SRV2 replication keeps working only until SRV1 is online. Once SRV1 is taken offline, replication stops syncing data between SRV2 and SRV4.

• Anonymous
  November 02, 2015
  Hello Prabhjot, Thank you for the amazingly detailed article - great help. My question, our current SQL2008-physical FCI is the publisher and distributor. If the primary node-SRV1 is made the distributor, other than being the single point of failure, are there any technical road blocks in terms of smooth functioning of the SQL Server? Thank you,

• Anonymous
  December 08, 2015
  Great Article, exactly what I was looking for !

• Anonymous
  January 11, 2016
  I have the same issues Rajeev Chaudhary, we can failover and things still work, but when we take the first server down, it stops working.  We can watch the distributer dashboard and the replication agent stays on one server and doesn't fail over to a different AG node when the AG is failed over.

• Anonymous
  February 06, 2016
  thanks for the article. Do you know if the transactional with immediate update is supported. I have a publisher which is not part of AG, and want AG as a subscriber, but some updates are done on the subscriber.

• Anonymous
  March 02, 2016
  Hi,while performing the above replication scenario i got the following problems.problem 1 : job invocation engine is still running(error no : 311) Problem 2 : Engine threats failed to stepto stop after 2 seconds of waiting(error no : 240)windows event log :------------------ The application specific permission settings do not grant Local Activation(Error no : 10016)can you please help me regarding those errors what i have mentioned above.Earlier reply is greatful to me.Thanks and Regards,venubabu

• Anonymous
  March 02, 2016
  Data is not travelling from original publisher to subscriber.Regarding above scenario i got following problems.After failover also data is not reflecting from publisher to subscriber.problem 1 : job invocation engine is still running(error no : 311)Problem 2 : Engine threats failed to stepto stop after 2 seconds of waiting(error no : 240)windows event log :——————The application specific permission settings do not grant Local Activation(Error no : 10016)can you please help me regarding those errors what i have mentioned above.Earlier reply is greatful to me.Thanks and Regards,venubabu

• Anonymous
  March 02, 2016
  Data is not travelling from original publisher to subscriber.Regarding above scenario i got following problems.After failover also data is not reflecting from publisher to subscriber.can you please focus on this issue regardng above scenario.windows event log :——————The application specific permission settings do not grant Local Activation(Error no : 10016)can you please help me regarding those errors what i have mentioned above.Earlier reply is greatful to me.Thanks and Regards,venubabu

• Anonymous
  May 12, 2016
  Can we use local distributor in each node, in Always on Availability group , for replication ?

• Anonymous
  November 01, 2016
  Dear Team,Would you please give an explanation that if one of the replica is crashed, will the replication work again? I did a bunch of tests, only to find it failed. Is it by design and we don't have other ways to make it work?In this way, what's the usage that we need an AG with replication?Thanks

• Anonymous
  December 05, 2016
  Great article, have one question or has anyone tried to setup transaction replication with secondary set to NOT readable. Thanks

• Anonymous
  January 04, 2017
  The comment has been removed

• Anonymous
  January 04, 2017
  The comment has been removed

  • Anonymous
    February 07, 2017
    The comment has been removed

• Anonymous
  April 28, 2017
  Amazing !! Thank you so much. That's the exact steps required to do it all. Only one issue I faced that my replica server already configured with distribution. after a while I realized that I need to disable them first in order to get configure it again. Otherwise, everything was seamless. thank you ones again.

• Anonymous
  May 22, 2017
  Hello,Say that I want to fail over to a new node with replication in tact, but decommission the original publisher. Are you able to change the original publisher so that it is view-able from the new primary node in replication monitor?This is a great article. I have a highly transactional publication that failed over with no issues.

• Anonymous
  August 17, 2017
  The comment has been removed

• Anonymous
  November 15, 2017
  Nice Blog Prak :) Very detailed and helpful

• Anonymous
  January 02, 2018
  Very well and easily explained. Has all information under one article. Thank You!!

• Anonymous
  May 18, 2019
  The comment has been removed