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 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 |
-- 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 |
|
1 |
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.
Did you test failover on the cluster and if that was seamless for the subscribers?