Walkthrough of Merge Replication with AlwaysOn High Availability

Share this Post

I’ve been working with a customer to setup and test Merge Replication Publisher in AlwaysOn Availability Groups. To quickly spin up a test scenario, I used our Microsoft Azure AlwaysOn template for SQL Server 2014 and within a few minutes had a complete AlwaysOn Availability Groups environment with 2 SQL Server 2014 availability replicas, Primary and Secondary domain controllers, and file share witness.  Next I create a shared folder on the file share witness \\cluster-fsw\ReplData as my default Replication snapshot folder.  Using Remote Desktop connecting to SQLSERVER-0, I installed sample database AdventureWorksLT2012. Within 1 hour, start to finish, I was all set to test Merge Replication with AlwaysOn Availability Groups.

In a production environment I would have a 3rd SQL Server configured as my Distributor, perhaps other SQL Servers as Merge Subscribers. In SQL 2016\2017 the remoted Distributor, running in its own instance, could also be in an AlwaysOn Availability Groups.  For this testing, I used replica SQLSERVER-0 as Publisher, Distributor and Subscriber with replica SQLSERVER-1 as failover Publisher.

Verify all Replicas allow READ when running as Secondary Role. To confirm, right-click your AlwaysOn group, select PROPERTIES, the verify “Readable Secondary” = “Yes”.

At first I tried connecting to the Listener “AO-listener”, then creating the Merge Publication. It appears to work, however the Merge Agent failed when trying to push down the initial snapshot.

Number: 14050
Message: No subscription is on this publication or article.

Searching, technet, I found following article on Replication and AlwaysOn

Configure Replication for Always On Availability Groups (SQL Server)

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

While the article had all the steps, it took a few times until I got it working correctly. Below are my steps for both SQLSERVER-0 (primary) and SQLSERVER-1 (secondary) to setup Merge Publisher with automatic failover in AlwaysOn environment. I’ve numbered the steps to show the correct order. Steps (3), (6), and (7) wrapped in “SQLSERVER-1 – Secondary Replica for Merge Publisher” should be executed on the failover secondary replica.

All works as expected, data changes flow from whichever availability replica is Primary to Merge Subscribers.  Again, not typical production environment with local Distributor and Subscriber roles running on SQLSERVER-0, but quick to setup when learning.

If you get “read only” type message similar to the one below, make sure your all Replicas allow READ when running as Secondary Role.

Message: The target database, ‘MyPublishedDB’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access.

Merge Subscribers

Support for Merge Replication Subscribers in AlwaysOn Availability Group is less integrated.  If you need redundant subscribers, simply publish directly to each replicas as their own Merge Subscribers. This way data flow from Publisher to both Subscribers, not to one subscriber then to secondary replica.  Or better yet, use Transactional Replication topology which is fully integrated with AlwaysOn Availability Groups.


Share this Post

One thought on “Walkthrough of Merge Replication with AlwaysOn High Availability”

Leave a Reply to John Cancel 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.