Module 5: Implementing Merge Replication

1. Module 5:Implementing Merge Replication

2. Overview • Understanding Merge Replication Architecture • Implementing Conflict Resolution • Planning and Deploying Merge Replication • Troubleshooting Merge Replication • Tuning Merge Replication

3. Lesson: Understanding Merge Replication Architecture • How the Snapshot Agent Prepares a Publisher • How Merge Agent Creates and Synchronizes Subscriptions • How Generation Numbers Assist Synchronization • The Limited Role of the Distribution Database • Location of the Merge Agent

4. Table Table Table Table rowguid rowguid rowguid rowguid DELETE Saves the row content UPDATE 2 1 3 UPDATE INSERT MSmerge_contents MSmerge_tombstone MSmerge_tombstone MSmerge_contents rowguid rowguid TriggersStored Procedures TriggersStored Procedures rowguid How the Snapshot Agent Prepares a Publisher DELETE Saves the row content UPDATE UPDATE INSERT rowguid rowguid rowguid

5. Merge Agent Reads MSmerge_contents on Subscriber and Publisher Passes Subscriber changes to Publisher Passes Publisher changes to Subscriber If both changed same row identified by rowguid, initiates conflict resolver How Merge Agent Creates and Synchronizes Subscriptions

6. How Generation Numbers Assist Synchronization • Generation numbers: • Help determine what rows need to be updated without reading the entire table. • Indicate the last change that has occurred. • Are kept independently by Publisher and Subscriber.

7. The Limited Role of the Distribution Database • The distribution database stores: • The snapshot location. • The identity of the Publisher. • The identities of the Subscribers. • The time when each Subscriber last synchronized. • Locate the distribution database on the Publisher

8. Location of the Merge Agent • Subscriber for pull subscriptions • Distributor for push subscriptions

9. Lesson: Implementing Conflict Resolution • Conflict Detection • Conflict Resolution

10. Customer Priority Contact Original Row ALFKI Med 3/4/01 ALFKI ALFKI High High 4/7/01 3/4/01 Change by Manager Change by Sales ALFKI High 4/7/01 Changes are Merged Conflict Detection

11. Conflict Resolution • The Publisher has the highest priority so it wins all conflicts that it has with Subscribers. • All Subscribers gain the priority of the Publisher with which they synchronize, but only when they synchronize. • Whichever Subscriber synchronizes with the Publisher first gains the advantage of the Publisher’s priority first and will win subsequent conflicts with other Subscribers.

12. Practice: Implementing Conflict Resolution • Create table, merge publication, subscription • Make changes to table • Synchronize the tables and check for conflicts • Resolve conflicts

13. Lesson: Planning and Deploying Merge Replication • How to Manage Identity Values • Implementing Attachable Subscription Databases • Using Dynamic Horizontal Filters • Using Dynamic Snapshots • Using Join Filters • How SQL Server Implements Join Filters

14. How to Manage Identity Values

15. Practice: How to Manage Identity Values • Create table, merge publication, subscription • Add rows • Synchronize subscription

16. Create and configure the publications Create database and pull subscriptions Copy the database and transfer the .msf file Attach the subscription database Start the appropriate Agent Implementing Attachable Subscription Databases

17. Practice: Implementing Attachable Subscription Databases • Create a new database • Create a merge publication • Configure the publication to allow attachable subscription databases • Create a pull subscription • Copy the subscription database • Copy the .msf file to your partner’s server and attach the database • Modify a row • Synchronize the subscription database

18. Using Dynamic Horizontal Filters • Benefit of dynamic filters • Provide different partitions of data to different Subscribers • SUSER_SNAME() • Returns the login name for the user’s connection • HOST_NAME() • Returns the host name for the user’s connection • User-defined functions

19. Using Dynamic Snapshots • Create snapshots in advance for later application to disconnected Subscribers • Create only the snapshots you need • Create snapshots at a convenient time

20. Practice: Dynamic Filtering with Dynamic Snapshots • Create table • Add rows • Create merge publication • Create pull subscription • Create dynamic snapshot • Create second subscription

21. Using Join Filters

22. Second View 2 1 3 First View Joined Joined How SQL Server Implements Join Filters Views based on views based on views Filtered Table

23. Practice: Join Filters • Create a merge publication with a join filter • Create a pull subscription • Verify and test the publication and subscription

24. Lesson: Troubleshooting Merge Replication • Troubleshooting by Using the Merge Agent • Troubleshooting Slow Merge Performance by Using SQL Profiler • Troubleshooting Slow Performance When Using Merge Filters • Troubleshooting Subscriptions Using Merge Replication Data Validation

25. Troubleshooting by Using the Merge Agent • Running the Merge Agent • Practice Merge Agent "C:\Program Files\Microsoft SQL Server\80\COM\replmerg" -Publisher [VANCOUVER] -PublisherDB [Northwind] -Publication [MergeFilterCustOrdGermany] -Subscriber [DENVER] -SubscriberDB [NorthwindSubscriber] -SubscriptionType 1 -SubscriberSecurityMode 1 -Distributor [VANCOUVER]

26. SQL Profiler Select events Select data columns Filter trace information Identify the operations Troubleshooting Slow Merge Performance by Using SQL Profiler

27. Practice: Troubleshooting Slow Merge Performance By Using SQL Profiler • Create a trace file with SQL Profiler

28. Troubleshooting Slow Performance When Using Merge Filters • sp_MSsetupbelongs • Determines which set of rows in each table should be replicated to the Subscriber • sp_MSinitdynamicsubscriber • Used to query published tables by the merge process

29. Troubleshooting Subscriptions Using Merge Replication Data Validation • Scheduled data validation • Select type of validation with -Validate parameter • Select how often to validate with -ValidateInterval parameter • Special replication validation alert • Partial reinitialization • Returns the Subscriber to a previous state when data was synchronized

30. Lesson: Tuning Merge Replication • Planning for Efficient Merge Replication Tuning • Tuning the Merge Agent’s Performance • Tuning Publications • Tuning Filters, Indexes, and Filter Queries • Tuning the Merge Cleanup Process

31. Planning for Efficient Merge Replication Tuning • Reduce simultaneous merge requests • Use faster hardware • Optimize replication configuration • Stagger the timing of Subscribers’ merge processes • Create a hierarchy of Publishers • Use Republisher for scale-out

32. Tuning the Merge Agent’s Performance • Select the appropriate Merge Agent profile • Default profile • High Volume Server-to-Server profile • Slow Link Agent profile • Force the merge process to upload • ExchangeType 3—upload and download (default) • ExchangeType 1—download only • ExchangeType 2—upload only

33. Tuning Publications • Reconfigure tables that contain text or image columns • Use global subscriptions • Select the optimize synchronization option

34. Tuning Filters, Indexes, and Filter Queries • Use static filters • Partition data into small data sets • Use indexes on columns used in filters • Rebuild indexes • Optimize filtering expressions for indexes • Consider denormalization to simplify partitions

35. Tuning the Merge Cleanup Process • Metadata based on retention • MSmerge_contents • MSmerge_tombstone • MSmerge_genhistory • Cleanup process • Default retention period

36. Review • Understanding Merge Replication Architecture • Implementing Conflict Resolution • Planning and Deploying Merge Replication • Troubleshooting Merge Replication • Tuning Merge Replication

37. Lab 5: Implementing Merge Replication • Exercise 0: Lab Setup • Exercise 1:Using Dynamic and Join Filters with Merge Replication