Data Validation for Merge Replication


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

    Alexander Chigrik
    Alexander Chigrik
    I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

    Latest Articles