Validate Data at the Subscriber

This topic describes how to validate data at the Subscriber in SQL Server 2012 by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).

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, and the publications node in Replication Monitor. For information about starting Replication Monitor, see Start the 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.

In This Topic

• Before you begin:

  Limitations and Restrictions

• To validate data at the Subscriber, using:

  SQL Server Management Studio

  Transact-SQL

  Replication Management Objects (RMO)

Before You Begin

Limitations and Restrictions

• 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.

• 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 Compare Replicated Tables for Differences (Replication Programming).

[Top]

Using SQL Server Management Studio

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.

[Top]

Using Transact-SQL

To validate data for all articles in a transactional publication

1. At the Publisher on the publication database, execute sp_publication_validation (Transact-SQL). Specify @publication and one of the following values for @rowcount_only:

   • 1 - rowcount check only (the default)

   • 2 - rowcount and binary checksum.

   Note

   When you execute sp_publication_validation (Transact-SQL), sp_article_validation (Transact-SQL) is executed for each article in the publication. To successfully execute sp_publication_validation (Transact-SQL), you must have SELECT permissions on all columns in the published base tables.

2. (Optional) Start the Distribution Agent for each subscription if it is not already running. For more information, see Synchronize a Pull Subscription and Synchronize a Push Subscription.

3. Check the agent output for the result of the validation. For more information, see Validate Replicated Data.

To validate data for a single article in a transactional publication

1. At the Publisher on the publication database, execute sp_article_validation (Transact-SQL). Specify @publication, the name of the article for @article, and one of the following values for @rowcount_only:

   • 1 - Rowcount check only (the default)

   • 2 - Rowcount and binary checksum.

   Note

   To successfully execute sp_article_validation (Transact-SQL), you must have SELECT permissions on all columns in the published base table.

2. (Optional) Start the Distribution Agent for each subscription if it is not already running. For more information, see Synchronize a Pull Subscription and Synchronize a Push Subscription.

3. Check the agent output for the result of the validation. For more information, see Validate Replicated Data.

To validate data for a single subscriber to a transactional publication

1. At the Publisher on the publication database, open an explicit transaction using BEGIN TRANSACTION (Transact-SQL).

2. At the Publisher on the publication database, execute sp_marksubscriptionvalidation (Transact-SQL). Specify the publication for @publication, the name of the Subscriber for @subscriber, and the name of the subscription database for @destination_db.

3. (Optional) Repeat step 2 for each subscription being validated.

4. At the Publisher on the publication database, execute sp_article_validation (Transact-SQL). Specify @publication, the name of the article for @article, and one of the following values for @rowcount_only:

   • 1 - Rowcount check only (the default)

   • 2 - Rowcount and binary checksum.

   Note

   To successfully execute sp_article_validation (Transact-SQL), you must have SELECT permissions on all columns in the published base table.

5. At the Publisher on the publication database, commit the transaction using COMMIT TRANSACTION (Transact-SQL).

6. (Optional) Repeat steps 1 through 5 for each article being validated.

7. (Optional) Start the Distribution Agent if it is not already running. For more information, see Synchronize a Pull Subscription and Synchronize a Push Subscription.

8. Check the agent output for the result of the validation. For more information, see Validate Data at the Subscriber.

To validate data in all subscriptions to a merge publication

1. At the Publisher on the publication database, execute sp_validatemergepublication (Transact-SQL). Specify @publication and one of the following values for @level:

   • 1 - Rowcount-only validation.

   • 3 - Rowcount binary checksum validation.

   This marks all subscriptions for validation.

2. Start the merge agent for each subscription. For more information, see Synchronize a Pull Subscription and Synchronize a Push Subscription.

3. Check the agent output for the result of the validation. For more information, see Validate Data at the Subscriber.

To validate data in selected subscriptions to a merge publication

1. At the Publisher on the publication database, execute sp_validatemergesubscription (Transact-SQL). Specify @publication, the name of the Subscriber for @subscriber, the name of the subscription database for @subscriber_db, and one of the following values for @level:

   • 1 - Rowcount-only validation.

   • 3 - Rowcount binary checksum validation.

   This marks the selected subscription for validation.

2. Start the merge agent for each subscription. For more information, see Synchronize a Pull Subscription and Synchronize a Push Subscription.

3. Check the agent output for the result of the validation.

4. Repeat steps 1 through 3 for each subscription being validated.

Note

A subscription to a merge publication can also be validated at the end of a synchronization by specifying the -Validate parameter when running the Replication Merge Agent.

To validate data in a subscription using Merge Agent parameters

• Start the Merge Agent at the Subscriber (pull subscription) or at the Distributor (push subscription) from the command prompt in one of the following ways.

  • Specifying a value of 1 (rowcount) or 3 (rowcount and binary checksum) for the -Validate parameter.

  • Specifying rowcount validation or rowcount and checksum validation for the -ProfileName parameter.

  For more information, see Synchronize a Pull Subscription or Synchronize a Push Subscription.

[Top]

Using Replication Management Objects (RMO)

Replication enables you to use Replication Management Objects (RMO) to programmatically validate that data at the Subscriber matches data at the Publisher. The objects you use depend on the type of replication topology. Transactional replication requires validation of all subscriptions to a publication.

Note

For an example, see Example (RMO), later in this section.

To validate data for all articles in a transactional publication

1. Create a connection to the Publisher by using the ServerConnection class.

2. Create an instance of the TransPublication class. Set the Name and DatabaseName properties for the publication. Set the ConnectionContext property to the connection created in step 1.

3. Call the LoadProperties method to get the remaining properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.

4. Call the ValidatePublication method. Pass the following:

   • ValidationOption

   • ValidationMethod

   • A Boolean that indicates whether to stop the Distribution Agent after validation is completed.

   This marks the articles for validation.

5. If not already running, start the Distribution Agent to synchronize each subscription. For more information, see Synchronize a Push Subscription or Synchronize a Pull Subscription. The result of the validation operation is written to the agent history. For more information, see Monitoring Replication.

To validate data in all subscriptions to a merge publication

1. Create a connection to the Publisher by using the ServerConnection class.

2. Create an instance of the MergePublication class. Set the Name and DatabaseName properties for the publication. Set the ConnectionContext property to the connection created in step 1.

3. Call the LoadProperties method to get the remaining properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.

4. Call the ValidatePublication method. Pass the desired ValidationOption.

5. Run the Merge Agent for each subscription to start validation, or wait for the next scheduled agent run. For more information, see Synchronize a Pull Subscription and Synchronize a Push Subscription. The result of the validation operation is written to the agent history, which you view by using Replication Monitor. For more information, see Monitoring Replication.

To validate data in a single subscription to a merge publication

1. Create a connection to the Publisher by using the ServerConnection class.

2. Create an instance of the MergePublication class. Set the Name and DatabaseName properties for the publication. Set the ConnectionContext property to the connection created in step 1.

3. Call the LoadProperties method to get the remaining properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.

4. Call the ValidateSubscription method. Pass the name of the Subscriber and subscription database being validated and the desired ValidationOption.

5. Run the Merge Agent for the subscription to start validation, or wait for the next scheduled agent run. For more information, see Synchronize a Pull Subscription and Synchronize a Push Subscription. The result of the validation operation is written to the agent history, which you view by using Replication Monitor. For more information, see Monitoring Replication.

Example (RMO)

This example marks all subscriptions to a transactional publication for rowcount validation.

          // Define the server, database, and publication names
            string publisherName = publisherInstance;
            string publicationName = "AdvWorksProductTran";
            string publicationDbName = "AdventureWorks2012";

            TransPublication publication;

            // Create a connection to the Publisher.
            ServerConnection conn = new ServerConnection(publisherName);

            try
            {
                // Connect to the Publisher.
                conn.Connect();

                // Set the required properties for the publication.
                publication = new TransPublication();
                publication.ConnectionContext = conn;
                publication.Name = publicationName;
                publication.DatabaseName = publicationDbName;

                // If we can't get the properties for this publication, 
                // throw an application exception.
                if (publication.LoadProperties())
                {
                    // Initiate validataion for all subscriptions to this publication.
                    publication.ValidatePublication(ValidationOption.RowCountOnly,
                        ValidationMethod.ConditionalFast, false);

                    // If not already running, start the Distribution Agent at each 
                    // Subscriber to synchronize and validate the subscriptions.
                }
                else
                {
                    throw new ApplicationException(String.Format(
                        "Settings could not be retrieved for the publication. " +
                        "Ensure that the publication {0} exists on {1}.",
                        publicationName, publisherName));
                }
            }
            catch (Exception ex)
            {
                // Do error handling here.
                throw new ApplicationException(
                    "Subscription validation could not be initiated.", ex);
            }
            finally
            {
                conn.Disconnect();
            }

' Define the server, database, and publication names
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksProductTran"
Dim publicationDbName As String = "AdventureWorks2012"

Dim publication As TransPublication

' Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

Try
    ' Connect to the Publisher.
    conn.Connect()

    ' Set the required properties for the publication.
    publication = New TransPublication()
    publication.ConnectionContext = conn
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName

    ' If we can't get the properties for this publication, 
    ' throw an application exception.
    If publication.LoadProperties() Then

        ' Initiate validataion for all subscriptions to this publication.
        publication.ValidatePublication(ValidationOption.RowCountOnly, _
         ValidationMethod.ConditionalFast, False)

        ' If not already running, start the Distribution Agent at each 
        ' Subscriber to synchronize and validate the subscriptions.
    Else
        Throw New ApplicationException(String.Format( _
         "Settings could not be retrieved for the publication. " + _
         "Ensure that the publication {0} exists on {1}.", _
         publicationName, publisherName))
    End If
Catch ex As Exception
    ' Do error handling here.
    Throw New ApplicationException( _
     "Subscription validation could not be initiated.", ex)
Finally
    conn.Disconnect()
End Try

This example marks a specific subscription to a merge publication for rowcount validation.

          // Define the server, database, and publication names
            string publisherName = publisherInstance;
            string publicationName = "AdvWorksSalesOrdersMerge";
            string publicationDbName = "AdventureWorks2012";
            string subscriberName = subscriberInstance;
            string subscriptionDbName = "AdventureWorks2012Replica";

            MergePublication publication;

            // Create a connection to the Publisher.
            ServerConnection conn = new ServerConnection(publisherName);

            try
            {
                // Connect to the Publisher.
                conn.Connect();

                // Set the required properties for the publication.
                publication = new MergePublication();
                publication.ConnectionContext = conn;
                publication.Name = publicationName;
                publication.DatabaseName = publicationDbName;


                // If we can't get the properties for this merge publication, then throw an application exception.
                if (publication.LoadProperties())
                {
                    // Initiate validation of the specified subscription.
                    publication.ValidateSubscription(subscriberName,
                        subscriptionDbName, ValidationOption.RowCountOnly);
                    
                    // Start the Merge Agent to synchronize and validate the subscription.
                }
                else
                {
                    throw new ApplicationException(String.Format(
                        "Settings could not be retrieved for the publication. " +
                        "Ensure that the publication {0} exists on {1}.",
                        publicationName, publisherName));
                }
            }
            catch (Exception ex)
            {
                // Do error handling here.
                throw new ApplicationException(String.Format(
                    "The subscription at {0} to the {1} publication could not " +
                    "be validated.", subscriberName, publicationName), ex);
            }
            finally
            {
                conn.Disconnect();
            }

' Define the server, database, and publication names
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publicationDbName As String = "AdventureWorks2012"
Dim subscriberName As String = subscriberInstance
Dim subscriptionDbName As String = "AdventureWorks2012Replica"

Dim publication As MergePublication

' Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

Try
    ' Connect to the Publisher.
    conn.Connect()

    ' Set the required properties for the publication.
    publication = New MergePublication()
    publication.ConnectionContext = conn
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName

    ' If we can't get the properties for this merge publication, then throw an application exception.
    If publication.LoadProperties() Then
        ' Initiate validation of the specified subscription.
        publication.ValidateSubscription(subscriberName, _
         subscriptionDbName, ValidationOption.RowCountOnly)

        ' Start the Merge Agent to synchronize and validate the subscription.
    Else
        Throw New ApplicationException(String.Format( _
         "Settings could not be retrieved for the publication. " + _
         "Ensure that the publication {0} exists on {1}.", _
         publicationName, publisherName))
    End If
Catch ex As Exception
    ' Do error handling here.
    Throw New ApplicationException(String.Format( _
     "The subscription at {0} to the {1} publication could not " + _
     "be validated.", subscriberName, publicationName), ex)
Finally
    conn.Disconnect()
End Try

[Top]