Few more Merge Replication Scenarios & Questions….
Centralized DB with Merge Replication:
Problem Description - In this scenario Customer wanted to setup a centralized DB with two location offices. They wanted to make sure that location specific data stays either in the particular location or on central server but it should not traverse across other locations. They had location identified in the table.
Customer had done some research on centralized replication topics on external sites but that made him more confused as he did not have any prior experience of replication topology.
Solution - We recommended them to go for a publication on central DB with two publisher. Each publisher with filtered articles based on location data and bi-directional subscriptions. With this approach, only location specific (filter specific) data could traverse between location and central DB and on central DB contains all data.
Error while setting up Merge:
Problem Description – This is one of the common error when you try to setup merge pub on the same DB where you removed previous merge pub:
Msg 20092, Level 16, State 1, Procedure MSmerge_disabledml_314BFF6E71E546EBA2994DC09C6C0CCF, Line 8
Table '[dbo].[Table_1]' into which you are trying to insert, update, or delete data is currently being upgraded or initialized for merge replication. On the publisher data modifications are disallowed until the upgrade completes and snapshot has successfully run. On subscriber data modifications are disallowed until the upgrade completes or the initial snapshot has been successfully applied and it has synchronized with the publisher.
Msg 3609, Level 16, State 1, Line 1 The transaction ended in the trigger. The batch has been aborted.
Solution - This is typically expected when few of the merge triggers remains in the database from the previous merge configuration (which was earlier removed). Look for and remove any triggers in sys.triggers where the name is like MSmerge%. Also verify Database Roles and Schemas for the same trigger name (MSmerge%) and if it does exist, remove the same as well.
Setting up Merge Sync to near Real-time:
Problem Description – Customer wanted to setup Merge Sync near real time. By default, merge sync (on continuous setup) polling interval is set to 60 seconds.
Solution – This can be achieved by following below steps:
1. Open Replication Monitor.
2. Select the Publication
3. Under All Subscription, your subscription would be listed for each subscriber.
4. Right Click on the subscriber one by one.
5. Select Agent Profile.
6. Click on New and Default Agent Profile.
7. Give a Name and scroll in Profile Parameters.
8. Change the Value for Polling Interval (should be in INT) as per your expected duration.
9. Click OK and select the new profile
Note: This could cause excessive lock in merge replication db. Also, this could cause high resource utilization as well.
Maintenance Recommendation of Merge System Tables:
Problem Description – Customer wanted to know the best practices for Merge System Tables as they had high fragmentation on those tables.
Solution - As part of maintenance for merge replication, occasionally check the growth of the system tables associated with merge replication: MSmerge_contents, MSmerge_genhistory, and MSmerge_tombstone. Periodically re-index these tables by running DBCC Transact-SQL commands. To re-index these system tables, execute the following commands on the publication database:
- DBCC DBREINDEX ('MSmerge_contents')
- DBCC DBREINDEX ('MSmerge_genhistory')
- DBCC DBREINDEX ('MSmerge_tombstone')
Additionally, one should minimize the size of the merge system tables (specifically MSmerge_history) by using sp_mergecleanupmetadata. Reference from below articles/posts:
https://msdn.microsoft.com/en-us/library/aa256294(v=sql.80).aspx
https://msdn.microsoft.com/en-us/library/aa237440(v=sql.80).aspx
https://technet.microsoft.com/en-us/library/cc966386.aspx
Subscription Priority:
Problem Description – Customer wanted to know the concept of Scubscription Priority as whatever the priority they define while setting up Subscriber, always the publisher was winning.
Solution – Reference https://msdn.microsoft.com/en-us/library/ms152513.aspx and https://msdn.microsoft.com/en-us/library/aa237187.aspx
- To manage the conflict resolution, Merge follows Publisher win when any changes happen within a minute (Polling Interval)
- If the changes happen beyond the polling interval, last change would win.
- Priority setting is applicable when we use multiple subscribers.
- By design, Publisher would always have 100% priority (higher than the max subscription priority 99.99%)