Merge Replication Baseline Collector

The Merge Baseline Collector utilizes SSIS to collect statistics on how merge replication is running overall between publications and subscriptions. Baselines are critical data that is collected to warn of spikes in operations, overall performance of events over time and potential analysis for scalability. Utilizing this SSIS package will allow for the automation of collecting a baseline from merge replication installations. In merge replication, even the addition of subscribers can have a large impact to the overall performance. This baseline data collected can promote better decisions when situations like additional subscribers, server upgrades etc…are considered.

To utilize the Merge Baseline Collector, configure the configuration file as shown below

• DatabaseDestination – The database to insert the statistical data collected
• DatabaseSource – The database name that is the publisher
• DistributorDBName – Distribution database name used for the publication – typical Distribution
• InstanceDestination – Instance housing the database to insert the statistical data collected
• InstanceSource – Instance that houses the replicating database (publication)
• LogFile – Location of the log files for the runtime reporting of the package (included in the download and can be placed on the C drive without configuration changes needed)
• TablePerCollectionFlag – Set to 0

If running the SSIS package from BIDS, configure the package configuration file by right clicking an empty area in the control flow and selecting “package configurations”. This file is preset to be located on the C Drive. Alter this as needed for your environment. A base MergeBaselineConfig.xml file has been included in the download. To utilize this configuration file, save it to your C drive, open it in an XML editor or text editor and alter the instance and database configuration values as needed using the value descriptions above.

The Merge Baseline Collector requires the security level matching what is needed to utilize replication monitor. The executing user requires the replmonitor database role membership or the sysadmin fixed server role.

The SSIS package will create three tables in the database specified for housing the statistics collected

MERGE_STATS – Holds synchronization statistics from subscribers
MERGE_COUNTS – Holds article level change counts
SubscriptionHeader – Holds all known subscribers metadata

The MERGESTATS and MERGECOUNTS tables are based off a column, CollectDate. Grouping by the CollectDate can be utilized in reporting on the collected statistics for baseline spikes or historic synchronization events.

Last edited Jul 18, 2012 at 2:19 PM by onpnt, version 3