Walkthrough of Merge Replication with AlwaysOn High Availability


Chris Skorlinski
Microsoft SQL Server Escalation Services

I’ve been working with a customer to setup and test Merge Replication Publisher in an AlwaysOn High Availability Group. To quickly spin up the scenario, I used our Microsoft Azure AlwaysOn Template for SQL Server 2014 and within a few minutes had a complete AlwaysOn environment with 2 SQL Server 2014 Replicas, Primary and Secondary domain controllers, and file share witness I also used as my \\cluster-fsw\ReplData snapshot folder. From the Azure Portal, I connected to the Primary Domain Controller, then Remote Desktop onto SQLSERVER-0 to install AdventureWorksLT2012 from CodePlex. Within 1 hour, start to finish, I was all set to test Merge Replication with AlwaysOn.

In a production environment I would have a 3rd SQL Server configured as my Distributor, perhaps other SQL Servers as Merge Subscribers. 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, @fre
quency_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 replica is Primary to Merge Subscribers.

 

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.

 

Next question came to mind, what about setting up Subscribers in AlwaysOn Availability Group, well my recommendation is don’t. It doesn’t quite work as you might expect. Instead, set up both replicas as their own Merge Subscribers. This way data flow from Publisher to both Subscribers, not to one subscriber then to secondary replica. Simply publish to both servers directly as Merge Subscribers.

Comments (0)

Skip to main content