Validating Row Counts in Transactional Replication

One part of administering replication should be validating that the articles being replicated are actually making it to the subscriber. There have been many times that replication has had no errors and the agents seem to be running without issue only to find out that the data on the subscriber is stale.

Luckily, there are some stored procedures that will do most of the leg work for you. In particular, exec sp_publication_validation @publication = ‘<publication name>’ which can only be used on transactional replication. According to Books Online, the procedure calculates the rowcount or checksum on each of the articles at the Publisher, posts the results to the distribution database, and the next time the distribution agent runs it checks those rowcounts against the subscriber.

After this procedure is ran, you can look inside the replication monitor to check the results.

If you would like to be automatically notified if an article fails the data validation, a SQL agent alert called “Replication: Subscriber has failed data validation” can be enabled to look for alert_error_code 20574 in the MSDB..SysReplicationAlerts table. This alert should have been creates when replication was configured. Alternatively, you could set up a SQL Agent job to look for these alert codes and email you.

For my environment, I’ve setup the sp_publication_validation procedure to run every two hours and enabled the “Replication: Subscriber has failed data validation” alert (having it email me if it is triggered).