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
later subscribers, and rowcount and checksum validation is available
only for SQL Server 7.0/2000 subscribers.
filtered vertically, but it can be used when the table has been filtered
horizontally, because the checksum is calculated on the entire row.
checksum value for all columns, ignoring data in text and image columns.
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.
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.
(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:
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.
Check Enabled box and press Apply button, as shown in 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.
Check Enabled box and press Apply button, as shown in 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.
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.
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.
Choose Steps tab and double click the Run agent step,
as shown in 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.
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.
This alert will be written into the Windows NT/Windows 2000
Application log, as Figure 11 shows.
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
