370 likes | 533 Views
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.
E N D
Overview • Understanding Merge Replication Architecture • Implementing Conflict Resolution • Planning and Deploying Merge Replication • Troubleshooting Merge Replication • Tuning Merge Replication
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
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
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
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.
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
Location of the Merge Agent • Subscriber for pull subscriptions • Distributor for push subscriptions
Lesson: Implementing Conflict Resolution • Conflict Detection • Conflict Resolution
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
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.
Practice: Implementing Conflict Resolution • Create table, merge publication, subscription • Make changes to table • Synchronize the tables and check for conflicts • Resolve conflicts
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
Practice: How to Manage Identity Values • Create table, merge publication, subscription • Add rows • Synchronize subscription
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
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
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
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
Practice: Dynamic Filtering with Dynamic Snapshots • Create table • Add rows • Create merge publication • Create pull subscription • Create dynamic snapshot • Create second subscription
Second View 2 1 3 First View Joined Joined How SQL Server Implements Join Filters Views based on views based on views Filtered Table
Practice: Join Filters • Create a merge publication with a join filter • Create a pull subscription • Verify and test the publication and subscription
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
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]
SQL Profiler Select events Select data columns Filter trace information Identify the operations Troubleshooting Slow Merge Performance by Using SQL Profiler
Practice: Troubleshooting Slow Merge Performance By Using SQL Profiler • Create a trace file with SQL Profiler
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
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
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
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
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
Tuning Publications • Reconfigure tables that contain text or image columns • Use global subscriptions • Select the optimize synchronization option
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
Tuning the Merge Cleanup Process • Metadata based on retention • MSmerge_contents • MSmerge_tombstone • MSmerge_genhistory • Cleanup process • Default retention period
Review • Understanding Merge Replication Architecture • Implementing Conflict Resolution • Planning and Deploying Merge Replication • Troubleshooting Merge Replication • Tuning Merge Replication
Lab 5: Implementing Merge Replication • Exercise 0: Lab Setup • Exercise 1:Using Dynamic and Join Filters with Merge Replication