Validating Replicated Data
As with snapshot and merge replication, SQL Server can validate data replicated via a transactional replication publication. You can choose to verify merely that the publication tables on the subscriber and the publisher have the same number of rows, or you can verify checksums or binary checksums of the data in the tables. When you use checksum validation, the server compares a 32-bit cyclic redundancy check (CRC) for each table article on the publisher and subscriber. Because this CRC is computed on a column-by-column basis, the precise column order for an article can vary between the publisher and subscriber without affecting the comparison. Data from text and image columns is not included in the comparison.
To validate replicated data, follow these steps.
1. | Expand the Publishers node under the Replication Monitor in Enterprise Manager at the distributor and select the publisher of the publication you want to validate. |
2. | In the list of publications on the right, find the publication you want to validate and right-click it. Select Validate Subscriptions. |
3. | In the Validate Subscriptions dialog, select the subscriptions you want to validate. (The subscription list may be empty if you have only anonymous pull subscriptions—select Validate all subscriptions if that's the case.) |
4. | Click the Validate Options button to specify the type of validation to do. You can choose to compute a “fast” row count (based on cached information) or to retrieve an actual row count for each table via a T-SQL query. You can opt to compare checksums or, if both the publisher and subscriber are running SQL Server 2000 or later, binary checksums. |
5. | Click OK once you've chosen your validation options, then click OK in the Validate Subscriptions dialog to validate your data. |
This results in the sp_article_validation stored procedure being called on the publisher. Then sp_article_validation calls sp_replpostcmd to post a call to sp_table_validation into the distribution database. This call includes the row count and/or checksum values from the publisher table so that they can be compared with those on the subscribers. The Distribution Agent then picks up the call to sp_table_validation and runs it on the subscriber. If the validation fails, sp_table_validation raises errors via the T-SQL RAISERROR command, causing them to be logged in the MSdistribution_history and MSrepl_errors tables in the distribution database.
To see this firsthand, let's work though a simple exercise to force a data validation failure.
Exercise 22.1 Validating a Transactional Publication
1. | Create a transaction publication for the Northwind Customers table. Enable anonymous subscriptions for it and accept the defaults for the rest of the settings. |
2. | Create an anonymous subscription for your new publication. Set its target database to be the pubs database. |
3. | Check for the Northwind Customers table to be replicated to your pubs database. Once it's there, proceed to step 4. |
4. | Update one of the columns in the Customers table in pubs such that it differs from the publisher's version of the table. |
5. | Connect to the distributor from Enterprise Manager. In the Replication Monitor node in the Enterprise Manager tree, expand the Publishers subnode, find your publisher, and click it. |
6. | Right-click the publication in the list on the right and select Validate Subscriptions. |
7. | Leave the Validate all subscriptions radio button selected and click the Validation Options button. |
8. | Click the Compare checksums to verify row data option and the This subscriber is a server running SQL Server 2000, use a binary checksum option, then click OK. |
9. | Click OK to begin the data validation process. |
10. | After the Distribution Agent next runs, double-click it in the AgentsDistribution Agents list under Replication Monitor on the distributor to display its agent history. Click the Session Details button to list details for the sessions listed in the agent history. |
11. | You should see an entry indicating that data validation may have failed for the table article in question. You can click the Error Details button to view additional information about the failure. Click Close to exit the Session Details dialog, then click Close again to exit the Distribution Agent History dialog. |
12. | You can also view the errors directly from the tables in the distribution database. To do this, execute the following queries from Query Analyzer: SELECT * FROM distribution..MSdistribution_history SELECT * FROM distribution..MSrepl_errors where distribution is the name of your distribution database. You should see entries in both tables indicating potential data validation problems. |
You can configure replication alerts to notify you when a data validation fails, and you can also reinitialize the affected subscription(s) automatically. To set up either one of these, follow the steps below.
1. | Click the Replication Alerts node under the Replication Monitor on the distributor. |
2. | Right-click the Subscriber has failed validation entry in the list on the right and select Properties. |
3. | In the General tab in the Properties dialog, click the Enabled checkbox to enable the alert. You can configure specific operators on the Response tab. |
4. | If you want to automatically reinitialize a subscription that has failed validation, click the Execute job checkbox on the Response tab and select the Reinitialize subscriptions having data validation failures job in the drop-down box to the right of it. |
5. | Click OK to save the alert. |
Note that immediate updating subscribers can cause data validation to fail between the time a change is made on the subscriber and the time it is propagated to the publisher. Naturally, during the period of time the two copies of the published article do not match, a data validation will fail. The only sure way to avoid this is not to make changes on the subscriber during the validation process.
Note that checksum validations are not supported with publications where DTS is used to transform the data because transformation implies different data values at the publisher and subscriber, so the checksum values would not likely match.
Also, row count validation is not supported for articles configured as DTS horizontal partitions because the filter criteria for the partition is saved with the DTS package; it's not in a view on the publisher as is the case with regular replication filters.
You also can't validate subscriptions published to heterogeneous subscribers for obvious reasons—the SQL Server stored procedures used to carry out the validation will not likely exist on these subscribers.