
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

Data Validation for Transactional Replication

Alexander Chigrik
chigrik@mssqlcity.com

General concepts

Restrictions

Example

General concepts

Microsoft SQL Server 7.0/2000 allows you to validate transactional replication. You can specify rowcount only validation or rowcount and checksum validation of the article's data.

When you choose rowcount only validation, the SQL Server calculates a rowcount at the publisher then compares the result with the rowcount at the subscriber to verify that the data is in synchronization. When you choose rowcount and checksum validation, the SQL Server calculates a rowcount and checksum at the publisher then compares the result with the rowcount and checksum at the subscriber. So, if the size of replicated data is very large, you can perform a rowcount only validation. The validation process works in the latent mode, i.e. it does not interrupt transactions on the publisher and the current replication process will not be stopped.

You can use the sp_publication_validation stored procedure to validate data for transactional replication. This stored procedure validates the data associated with each article by calling sp_article_validation (after the articles associated with a publication have been activated).

The sp_article_validation stored procedure invokes sp_table_validation stored procedure, which calculates the number of lines and the control sum of the published table. Then the sp_article_validation system stored procedure inserts a call of sp_table_validation directly into a transaction log at the publisher. After that Distribution Agent sends a call of the sp_table_validation to the subscribers and checks the data at the subscriber with the data at the publisher.

The Distribution Agent raises the 20574 system message or the 20575 system message, if the validation fails or if the validation passes accordingly. The Distribution Agent will replicate changes to a subscriber even if the validation shows, that the subscriber is out of synchronization. You can check the Windows NT or Windows 2000 Application log to see validation's result (20574 and 20575 system messages will be written into Application log). You can also configure the Replication Alert on the 20574 and 20575 system messages to send E-Mail, Pager, or Network notification to administrator that validation fails or passes.

Restrictions

Rowcount only validation is available only for SQL Server 6.x or later subscribers, and rowcount and checksum validation is available only for SQL Server 7.0/2000 subscribers.

The checksum validation cannot be used when the base table has been filtered vertically, but it can be used when the table has been filtered horizontally, because the checksum is calculated on the entire row.

The checksum algorithm is a 32-bit redundancy check (CRC), calculates checksum value for all columns, ignoring data in text and image columns.

The tables at the publisher and at the subscribers must have identical structure (the same columns in the same order, the same data types and lengths, and the same NULL/NOT NULL property). It is necessary for correct calculation of the control sum.

SQL Server uses bcp utility to copy data from publisher to subscribers. This utility can work in native or in character mode. If you use columns with float data, and your application has heterogeneous subscribers, the bcp utility will work in character mode and checksum on publisher and subscriber will not equal. You cannot use rowcount and checksum validation in this case, only rowcount validation.

You should stop the MSDTC service at the Publisher during validation (to ensure that the values at the Subscriber and Publisher do not change during validation process).

Example

Here, I want to illustrate how you can validate Transactional replication step by step.

First of all, you should enable the following replication alerts:

Subscriber has failed data validation

Subscriber has passed data validation

These alerts generate 20574 and 20575 system messages accordingly.

Replication Alert 20574

Then you will see the following:

Enable Replication Alert 20574

Check Enabled box and press Apply button.

Enable Replication Alert 20574 confirmation

Choose Yes button.

Make the same actions for alert Subscriber has passed data validation.

Replication Alert 20575

Then you will see the following:

Enable Replication Alert 20575

Check Enabled box and press Apply button. Then you will see the following:

Enable Replication Alert 20575 confirmation

Choose Yes.

You can validate your data on a regular schedule by creating a Transact-SQL job or you can run sp_publication_validation or sp_article_validation from the Query Analyzer.

Using sp_publication_validation

Start Snapshot Agent.

If everything is okay, you will receive the 20575 message.

This alert will be written into the Windows NT/Windows 2000 Application log.

Application log events

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc