
	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 Merge Replication

Alexander Chigrik
chigrik@mssqlcity.com

General concepts
Restrictions
Example
Literature

General concepts

Microsoft SQL Server 7.0/2000 allows you to validate merge 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.

The Merge Agent raises the 20574 system message or the 20575 system message, if the validation fails or if the validation passes accordingly. The Merge 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 Merge 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.

Expand Replication Monitor, click on Replication Alerts, right-click a Subscriber has failed data validation alert, and then click Properties, as shown in Figure 1.

Subscriber has failed data validation alert

Figure 1.

Check Enabled box and press Apply button, as shown in Figure 2.

Alert 20574

Figure 2.

Expand Replication Monitor, click on Replication Alerts, right-click a Subscriber has passed data validation alert, and then click Properties, as shown in Figure 3.

Figure 3.

Check Enabled box and press Apply button, as shown in Figure 4.

Alert 20575

Figure 4.

Now, you can use SQL Server Enterprise Manager or Merge Agent command line parameters to validate Merge replication.

To validate merge data using SQL Server Enterprise Manager, expand Replication Monitor, expand Publishers, choose a specific Publisher, right-click a merge publication, and then click Validate All Subscriptions, as shown in Figure 5.

Figure 5.

Now, you can choose rowcounts only, rowcounts and checksum, or rowcounts and comparing binary checksum validation. Choose Verify the row counts only and click OK button, as shown in Figure 6.

Validation will occur the next time the Merge Agent runs.

Figure 6.

To validate merge data using a Merge Agent command line parameters, expand Replication Monitor, expand Agents, click Merge Agents, right-click a specific Publication, and then click Agent Properties, as shown in Figure 7.

Merge Agent Properties

Figure 7.

Choose Steps tab and double click the Run agent step, as shown in Figure 8.

Run agent step

Figure 8.

Now, you can specify rowcount only validation or rowcount and checksum validation by typing -Validate 1 or -Validate 2 accordingly in the command text box.

You can set -ValidateInterval parameter to the number of minutes, when you want the validation to occur.

In this example, I specify rowcount only validation with validation interval equal to 30 minutes, as shown in Figure 9.

Edit Job Step

Figure 9.

If you correctly specify all parameters and there are no problems with your Merge replication, then after the next time the Merge Agent start, you will receive the message 20575, as Figure 10 shows.

Subscriber has passed data validation

Figure 10.

This alert will be written into the Windows NT/Windows 2000 Application log, as Figure 11 shows.

Event Log

Figure 11.

Literature

1. SQL Server Books Online.

2. Validating Replicated Data

3. Validate All Subscriptions

4. sp_table_validation

5. Replication Alerts

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, 2010 Bits on the Wire, Inc