How to: Validate Data at the Subscriber (SQL Server Management Studio)

  • Article

Validating data is a three-part process:

  1. A single subscription or all subscriptions to a publication are marked for validation. Mark subscriptions for validation in the Validate Subscription, Validate Subscriptions, and Validate All Subscriptions dialog boxes, which are available from the Local Publications folder and the Local Subscriptions folder in Microsoft SQL Server Management Studio. You can also mark subscriptions from the All Subscriptions tab, the Subscription Watch List tab (for Distributors running SQL Server 2005 and later), and the publications node in Replication Monitor. For information about starting Replication Monitor, see How to: Start Replication Monitor (Replication Monitor).
  2. A subscription is validated the next time it is synchronized by the Distribution Agent (for transactional replication) or the Merge Agent (for merge replication). The Distribution Agent typically runs continuously, in which case validation occurs immediately; the Merge Agent typically runs on demand, in which case validation occurs after you run the agent.
  3. View the validation results:
    • In the detail windows in Replication Monitor: on the Distributor to Subscriber History tab for transactional replication and the Synchronization History tab for merge replication.
    • In the View Synchronization Status dialog box in Management Studio.
      The validation results indicate whether validation succeeded or failed, but do not specify which rows failed validation if a failure occurred. To compare data at the Publisher and Subscriber, use the tablediff Utility. For more information about using this utility with replicated data, see How to: Compare Replicated Tables for Differences (Replication Programming).

Note

The procedures for Replication Monitor are for push subscriptions only because pull subscriptions cannot be synchronized in Replication Monitor. However, you can mark a subscription for validation and view validation results for pull subscriptions in Replication Monitor.

To validate data for subscriptions to a transactional publication (Management Studio)

  1. Connect to the Publisher in SQL Server Management Studio, and then expand the server node.

  2. Expand the Replication folder, and then expand the Local Publications folder.

  3. Right-click the publication for which you want to validate subscriptions, and then click Validate Subscriptions.

  4. In the Validate Subscriptions dialog box, select which subscriptions to validate:

    • Select Validate all SQL Server subscriptions.
    • Select Validate the following subscriptions, and then select one or more subscriptions.

  5. To specify the type of validation to perform (row count, or row count and checksum) click Validation Options, and then specify options in the Subscription Validation Options dialog box.

  6. Click OK.

  7. View validation results in Replication Monitor or the View Synchronization Status dialog box. For each subscription:

    1. Expand the publication, right-click the subscription, and then click View Synchronization Status.
    2. If the agent is not running click Start in the View Synchronization Status dialog box. The dialog box will display informational messages regarding validation.

    If you do not see any messages regarding validation, the agent has already logged a subsequent message. In this case, view the validation results in Replication Monitor. For more information, see the Replication Monitor how to procedures in this topic.

To validate data for a single subscription to a merge publication (Management Studio)

  1. Connect to the Publisher in SQL Server Management Studio, and then expand the server node.

  2. Expand the Replication folder, and then expand the Local Publications folder.

  3. Expand the publication for which you want to validate subscriptions, right-click the subscription, and then click Validate Subscription.

  4. In the Validate Subscription dialog box, select Validate this subscription.

  5. To specify the type of validation to perform (row count, or row count and checksum) click Options, and then specify options in the Subscription Validation Options dialog box.

  6. Click OK.

  7. View validation results in Replication Monitor or the View Synchronization Status dialog box:

    1. Expand the publication, right-click the subscription, and then click View Synchronization Status.
    2. If the agent is not running, click Start in the View Synchronization Status dialog box. The dialog box will display informational messages regarding validation.

    If you do not see any messages regarding validation, the agent has already logged a subsequent message. In this case, view the validation results in Replication Monitor. For more information, see the Replication Monitor how to procedures in this topic.

To validate data for all subscriptions to a merge publication (Management Studio)

  1. Connect to the Publisher in SQL Server Management Studio, and then expand the server node.

  2. Expand the Replication folder, and then expand the Local Publications folder.

  3. Right-click the publication for which you want to validate subscriptions, and then click Validate All Subscriptions.

  4. In the Validate All Subscriptions dialog box, specify the type of validation to perform (row count, or row count and checksum).

  5. Click OK.

  6. View validation results in Replication Monitor or the View Synchronization Status dialog box. For each subscription:

    1. Expand the publication, right-click the subscription, and then click View Synchronization Status.
    2. If the agent is not running, click Start in the View Synchronization Status dialog box. The dialog box will display informational messages regarding validation.

    If you do not see any messages regarding validation, the agent has already logged a subsequent message. In this case, view the validation results in Replication Monitor. For more information, see the Replication Monitor how to procedures in this topic.

To validate data for all push subscriptions to a transactional publication (Replication Monitor)

  1. In Replication Monitor, expand a Publisher group in the left pane, and then expand a Publisher.

  2. Right-click the publication for which you want to validate subscriptions, and then click Validate Subscriptions.

  3. In the Validate Subscriptions dialog box, select which subscriptions to validate:

    • Select Validate all SQL Server subscriptions.
    • Select Validate the following subscriptions, and then select one or more subscriptions.

  4. To specify the type of validation to perform (row count, or row count and checksum) click Validation Options, and then specify options in the Subscription Validation Options dialog box.

  5. Click OK.

  6. Click the All Subscriptions tab.

  7. View validation results. For each push subscription:

    1. If the agent is not running, right-click the subscription, and then click Start Synchronizing.
    2. Right-click the subscription, and then click View Details.
    3. View information on the Distributor to Subscriber History tab in the Actions in the selected session text area.

To validate data for a single push subscription to a merge publication (Replication Monitor)

  1. In Replication Monitor, expand a Publisher group in the left pane, expand a Publisher, and then click a publication.

  2. Click the All Subscriptions tab.

  3. Right-click the subscription you want to validate, and then click Validate Subscription.

  4. In the Validate Subscription dialog box, select Validate this subscription.

  5. To specify the type of validation to perform (row count, or row count and checksum) click Options, and then specify options in the Subscription Validation Options dialog box.

  6. Click OK.

  7. Click the All Subscriptions tab.

  8. View validation results:

    1. If the agent is not running, right-click the subscription, and then click Start Synchronizing.
    2. Right-click the subscription, and then click View Details.
    3. View information on the Synchronization History tab in the Last message of the selected session text area.

To validate data for all push subscriptions to a merge publication (Replication Monitor)

  1. In Replication Monitor, expand a Publisher group in the left pane, and then expand a Publisher.

  2. Right-click the publication for which you want to validate subscriptions, and then click Validate All Subscriptions.

  3. In the Validate All Subscriptions dialog box, specify the type of validation to perform (row count, or row count and checksum).

  4. Click OK.

  5. Click the All Subscriptions tab.

  6. View validation results. For each push subscription:

    1. If the agent is not running, right-click the subscription, and then click Start Synchronizing.
    2. Right-click the subscription, and then click View Details.
    3. View information on the Synchronization History tab in the Last message of the selected session text area.

See Also

Tasks

How to: Synchronize a Pull Subscription (SQL Server Management Studio)
How to: Synchronize a Push Subscription (SQL Server Management Studio)

Concepts

Validating Replicated Data

Other Resources

How to: Validate Data at the Subscriber (Replication Transact-SQL Programming)

Help and Information

Getting SQL Server 2005 Assistance