General concepts
Restrictions
Example
Literature

General concepts

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

When you choose rowcount only validation, then SQL Server calculates a rowcount at the publisher level, then compares the result to the rowcount at the subscriber level to verify that the data is in synchronization. When you choose rowcount and checksum validation, then SQL Server calculates a rowcount and checksum at the publisher, then compares the result to the rowcount and checksum at the subscriber. So, if the size of replication's 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 if the validation fails, or the 20575 system message if the validation passes. 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 the 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 the administrator that the validation has passed or failed.

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) that 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). This is necessary for correct calculation of the control sum.

  • SQL Server uses the 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, then 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.



    Figure 1.

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



    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.



    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.



    Figure 7.

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



    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.



    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.



    Figure 10.

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



    Figure 11.

    Literature

    1. SQL Server Books Online.

    2. Validating Replicated Data http://msdn.microsoft.com/library/en-us/replsql/replmon_9pwh.asp

    3. Validate All Subscriptions http://msdn.microsoft.com/library/en-us/repwizrd/repwizrd_9qwj.asp

    4. sp_table_validation http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_repl3_7eb2.asp

    5. Agents and Monitors http://msdn.microsoft.com/library/en-us/replsql/replintro_3a0j.asp

    6. Replication Alerts http://msdn.microsoft.com/library/en-us/replsql/replmon_3ek3.asp


    » See All Articles by Columnist Alexander Chigrik