Microsoft® SQL Server™ can validate the data being updated at a Subscriber as the replication process is occurring. It is not necessary to stop further updates to the Publisher and wait for the Subscriber to become fully synchronized before testing that all data has been received and applied correctly. You can validate the data in either transactional or merge publications.
You can use sp_publication_validation to validate data after the articles associated with a publication have been activated. This procedure validates the data associated with each article by calling sp_article_validation. You can specify rowcount only validation (the default) or full checksum validation of the article data.
Note Only Subscribers that are SQL Server version 6.x or later databases can use the data validation mechanisms described in this topic. Subscribers that are SQL Server 6.x can use only rowcount validation, while Subscribers that are SQL Server 7.0 can use either rowcount or checksum.
The checksum algorithm is a 32-bit redundancy check (CRC) against the entire row image of the underlying base table, excluding test and image columns. Because the checksum is calculated on the entire row, checksum validation can still be used even when the table has been filtered horizontally; however, it cannot be used when the base table has been filtered vertically. Furthermore, checksums can be unreliable if used in applications where not all Subscribers are running SQL Server and there are character conversions involving columns with float data. If your application has heterogeneous Subscribers, do not use checksum if float data is being converted or use it only if the column has binary data. The actual values at the Publisher are then delivered to the Subscriber through the normal replication flow and are checked at the Subscriber.
Because the validation command is delivered using the normal replication mechanism, you are assured that the validation occurs at the point when the Subscriber should have the same data set as the Publisher had, when it performed the same check. After each table validation completes at the Subscriber, the Distribution Agent fires either the Replication: Subscriber has passed data validation or Replication: Subscriber has failed data validation alert depending on the outcome. This alert can be used to notify operators or execute automated response jobs.
You can validate your data on a regular schedule by creating a Transact-SQL job that uses sp_publication_validation or sp_article_validation. Because checksums can require large amounts of processor resources for a large data set, you should either schedule validation during off-hours or use rowcount only validation. For example, you may create a job that schedules a row count every four hours and a checksum every 24 hours.
You can use the Merge Agent utility -Validate parameter at the command prompt to validate data at a Subscriber after the merge replication process is complete. You can specify rowcount only validation (the default) or full checksum validation of the article data.
Starting the Merge Agent utility with -Validate causes SQL Server to lock the Subscriber tables to prevent further changes. SQL Server then computes either a rowcount or checksum of each replicated table at the Subscriber and at the Publisher. If there is a difference, SQL Server locks the discrepant table at the Publisher and any new data changes are downloaded to the Subscriber. After downloading is complete, SQL Server recalculates the rowcount or checksum at the Subscriber and Publisher and compares them again. After validation is complete, SQL Server removes all locks on Subscriber and Publisher tables.
You can validate your data on a regular schedule by adding -Validate to the Merge Agent profile at a specified time. Because inline validation may be time-consuming or may result in undesirable contention between the Publisher and Subscriber, you should schedule validation for a time when Publisher and Subscriber activity is at a minimum.
| Monitoring Replication Alerts | sp_article_validation |
| Replication Merge Agent Utility | sp_publication_validation |
| sp_add_job |