Validating Replicated Data

Setting up and maintaining SQL Server replication can be a frustrating experience. Here are some strategies I've developed that you can use to deal with one common replication gotcha: verifying the subscriber's data. I'll show you how to use the SQL Server 7.0 inline replication validation capability to ensure that the subscriber is in sync with the publisher, and I'll show you how to discover problems before they cause serious trouble in your production environment.

SQL Server 7.0's replication validation capability is one of the coolest new features for administering a replication environment, but few administrators know that it exists. Also, SQL Server Books Online (BOL) doesn't fully explain how this validation feature works. The full replication validation process calculates a rowcount and checksum at the publisher, then compares the result to the rowcount and checksum at the subscriber to verify that the data is in sync. The process calculates one checksum value for the whole table, ignoring data in text or image columns. You can perform a rowcount-only validation, which is slightly faster than a validation that uses both rowcount and checksum. SQL Server 7.0 validation is an inline process, which means that validation doesn't interrupt transactional activity on the publisher or stop the ordinary flow of replication. (The exception is validating bidirectional transactional replication, which I'll discuss later in the article.) The validation process is somewhat different for transactional replication and merge replication, so I'll discuss each separately.

Validating a Transactional Publication
The validation process for a transactional publication starts by calling sp_publication_validation. You can run this procedure manually or set up a job to run it on a schedule. The sp_publication_validation procedure calls sp_article_validation for every article in the publication. If you want to validate only one article in a large publication, you call sp_article_validation directly rather than calling sp_publication_validation.

To see the results of the validation check, you need to run the logreader and the distribution agents, then look in the distribution agent's history log. Screen 1 shows an out-of-synchronization message in the distribution agent history log. Note that if you run sp_publication_validation in a query window, you will see a message such as Generated expected rowcount value of 3 for tab1. Ignore this message. It's a byproduct of the validation mechanism that is generated when the sp_table_validation procedure is executed at the publisher. But this message doesn't indicate whether the subscriber succeeded or failed the validation check.

Here's how the process works: The sp_article_validation procedure calls sp_table_validation, which calculates the rowcount and checksum for the published table. The sp_article_validation procedure then inserts an sp_table_validation call directly into the publisher's transaction log. The process passes the rowcount and checksum values as parameters to the sp_ table_validation call that is inserted into the transaction log. The ordinary transactional replication mechanism replicates the sp_ table_validation call to subscribers and validates the subscriber's data at the point when the subscriber should have the same data as the publisher. This step renders the validation procedure an inline process—a process that works within the usual flow of replication.

The sp_table_validation procedure raises a system message that reports the success or failure of the validation check at the subscriber. The distribution agent checks for the system message that sp_table_validation raises. Then the distribution agent raises the 20574 system message if the validation fails, or the 20575 system message if the validation passes. The distribution agent records the success or failure message in the distribution agent history log. Note that the distribution agent continues replicating transactions to a subscriber even if the validation check reports that the subscriber is out of sync.

Validating a Merge Publication
The validation process for a merge publication starts when the merge agent is run with the -Validate parameter set to 1 (for rowcount-only validation) or 2 (for rowcount and checksum validation). You can set these parameters by right-clicking the merge agent in Enterprise Manager and selecting Agent Properties, then selecting the Steps tab and editing the command line for the Run agent step, as Screen 2 shows. When the merge agent runs, it executes sp_table_validation against the publisher and subscriber and compares the values.

The merge agent raises the 20574 system message if the validation fails, or 20575 if it passes, then records the success or failure message in the merge agent history log, as Screen 3 shows. Note that the merge agent continues replicating changes to a subscriber even if the validation check finds that the subscriber is out of sync.