1 / 37

Module 5: Implementing Merge Replication

Module 5: Implementing Merge Replication. Overview. Understanding Merge Replication Architecture Implementing Conflict Resolution Planning and Deploying Merge Replication Troubleshooting Merge Replication Tuning Merge Replication. Lesson: Understanding Merge Replication Architecture.

zorion
Download Presentation

Module 5: Implementing Merge Replication

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related