Walkthrough of Merge Replication with AlwaysOn High Availability

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 3^rd 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.

-- SQLSERVER-0 
-- Primary Replica, role of Merge Publisher (AlwaysOn with Automatic Failover), Distributor, and Subscriber

-- 1) Enabling the replication database
use
master
exec sp_replicationdboption
@dbname = N'AdventureWorksLT2012', @optname = N'merge publish', @value = N'true'
GO

-- 2) Configure Distribution

-- SQLSERVER-1 - Secondary Replica for Merge Publisher

-- 3) Verify Replication is installed
USE
master;
GO
DECLARE @installed int;
EXEC @installed = sys.sp_MS_replication_installed;
SELECT @installed;

-- SQLSERVER-1 - Secondary Replica for Merge Publisher

-- 4) Specify Server 1 as alternative Publisher
EXEC sys.sp_adddistpublisher 
 @publisher ='SQLSERVER-1',
 @distribution_db ='distribution',
 @working_directory ='\\cluster-fsw\ReplData'
-- 5) In SQL Server Management Studio, right-click Replication folder,
--    Distributor Properties \ Publishers, create a Remote Distributor Password
-- SQLSERVER-1 - Secondary Replica for Merge Publisher
-- 6) Configure Distribution via Wizard with Remote Distributor or run command below.
EXEC sp_adddistributor
@distributor ='SQLSERVER-0',
@password ='P@ssword1';

-- 7) Setup Linked Server for Merge Subscriber
EXEC sys.sp_addlinkedserver
@server ='SQLSERVER-0'
-- SQLSERVER-1 - Secondary Replica for Merge Publisher

-- 8) Create the merge publication
use [AdventureWorksLT2012]

exec
sp_addmergepublication
@publication =
N'MergeCustomer', @description = N'Merge publication of database ''AdventureWorksLT2012'' from Publisher ''SQLSERVER-0''.', @sync_mode = N'native', @retention = 14, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_subdirectory = N'ftp', @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'false', @conflict_retention = 14, @keep_partition_changes =
N'false', @allow_synctoalternate = N'false', @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = N'false', @publication_compatibility_level = N'100RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot =
N'false', @allow_web_synchronization = N'false', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'both', @automatic_reinitialization_policy = 0
GO
exec
sp_addpublication_snapshot
@publication =
N'MergeCustomer', @frequency_type = 4, @frequency_interval = 14, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 5, @active_start_time_of_day = 500, @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_grant_publication_access
@publication =
N'MergeCustomer', @login = N'NT AUTHORITY\SYSTEM'
GO
exec
sp_grant_publication_access
@publication =
N'MergeCustomer', @login = N'ao\sqlservice'
GO
exec
sp_grant_publication_access
@publication =
N'MergeCustomer', @login = N'NT SERVICE\SQLSERVERAGENT'
GO
exec
sp_grant_publication_access
@publication =
N'MergeCustomer', @login = N'NT Service\MSSQLSERVER'
GO
exec
sp_grant_publication_access
@publication =
N'MergeCustomer', @login = N'distributor_admin'
GO
 
-- Adding the merge articles
use [AdventureWorksLT2012]
exec
sp_addmergearticle
@publication =
N'MergeCustomer', @article = N'Customer', @source_owner = N'SalesLT', @source_object = N'Customer', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption =
N'auto', @pub_identity_range = 10000, @identity_range = 1000, @threshold = 80, @destination_owner = N'SalesLT', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver =
N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0
GO
 
-- Adding the merge subscriptions
use [AdventureWorksLT2012]
exec
sp_addmergesubscription
@publication =
N'MergeCustomer', @subscriber = N'SQLSERVER-0', @subscriber_db = N'MergeSubscriber', @subscription_type = N'Push', @sync_type =
N'Automatic', @subscriber_type = N'Global', @subscription_priority = 75, @description = N'', @use_interactive_resolver = N'False'
GO
exec
sp_addmergepushsubscription_agent
@publication =
N'MergeCustomer', @subscriber = N'SQLSERVER-0', @subscriber_db =
N'MergeSubscriber', @job_login =
null, @job_password =
null, @subscriber_security_mode = 1, @publisher_security_mode = 1, @frequency_type = 1, @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 = 0, @active_end_date = 0
GO
 
-- 9) Run the Snapshot Agent and Merge Agent once to initialize the Subscriber.
 
-- 10) Execute command below to redirect Publisher connections
--    to Availability Group Listener Name AO-listener
USE distribution;
GO
EXEC
sys.sp_redirect_publisher
@original_publisher =
'SQLSERVER-0',
@publisher_db =
'AdventureWorksLT2012',
@redirected_publisher =
'AO-listener';
 
 
-- 11) Validate Publication -- Linked Server WARNING can be ignored
USE distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC
sys.sp_validate_replica_hosts_as_publishers

@original_publisher =
'SQLSERVER-0',
@publisher_db =
'AdventureWorksLT2012',
@redirected_publisher = @redirected_publisher output;
 
-- 12) Using Availability Group Wizard, Failover to Secondary Replica
 
-- 13) Make data change then run Merge Agent on Subscriber to synchronize with new Primary

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.