Script to Validate Subscribers for all articles and publication

declare @pubdb nvarchar(50)
declare @artobj nvarchar(100)
declare @owner nvarchar(50)
declare @params nvarchar(100)
declare @cnt1 nvarchar(50)
declare @cmd nvarchar(1000)
declare @cmd1 nvarchar(1000)
DECLARE pub_validate CURSOR FOR
select publisher_db,source_owner,source_object from distribution.dbo.MSarticles
open pub_validate
fetch next from pub_validate into @pubdb,@owner,@artobj
while @@fetch_status=0
begin
select @cmd = 'select @cnt=count(*) from '+@pubdb+'.'+@owner+'.'+@artobj
select @params=N'@cnt varchar(100) OUTPUT'
exec sp_executesql @cmd,@params,@cnt=@cnt1 output
--select @cnt1
select @cmd1='use '+@pubdb+CHAR(10)+'go'+CHAR(10)+'sp_table_validation @table='+@artobj+',@expected_rowcount='+@cnt1
select @cmd1
--exec sp_executesql @cmd1
fetch next from pub_validate into @pubdb,@owner,@artobj
end
close pub_validate
deallocate pub_validate

Validating Replication Subscriber using TSQL

Last post from me on SQL Server replication was on How to validate subscribers in SQL Server Replication. This article shows how the same can be done using TSQL.

sp_publication_validation:

    This system stored procedure does a validation for each and all articles in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.

sp_publication_validation [ @publication = ] 'publication'

[ , [ @rowcount_only = ] type_of_check_requested ]

[ , [ @full_or_fast = ] full_or_fast ]

[ , [ @shutdown_agent = ] shutdown_agent ]

[ , [ @publisher = ] 'publisher' ]

@publication is the name of the publication.

@rowcount_only is whether to return only the row count for the table.

    0 for perform a SQL Server 7.0 compatible check sum

    1 for perform rowcount only

    2 for perform a row count and binary check sum.

@full_or_fast is used to calculate the row count.

    0 does full count using count(*)

    1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.

    2 does a count using rows column in sysindexes table if there is a difference then it does a full count.

@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.

@publisher specifies a non-sql server publisher.

You need to have sysadmin or db_owner role in the publication database to execute stored procedure.

sp_article_validation:

    This system stored procedure does a validation for each article individually in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.

sp_article_validation [ @publication = ] 'publication'

[ , [ @article = ] 'article' ]

[ , [ @rowcount_only = ] type_of_check_requested ]

[ , [ @full_or_fast = ] full_or_fast ]

[ , [ @shutdown_agent = ] shutdown_agent ]

[ , [ @subscription_level = ] subscription_level ]

[ , [ @reserved = ] reserved ]

[ , [ @publisher = ] 'publisher' ]

@publication is the name of the publication in which the article exists.

@article is the name of the article to validate.

@rowcount_only specifies if only the rowcount for the table is returned.

    0 for perform a rowcount and a Microsoft SQL Server 7.0 compatible checksum.

    1 for perform a rowcount check only.

    2 for perform a rowcount and binary checksum.

@full_or_fast is the method used to calculate the rowcount.

    0 does full count using count(*)

    1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.

    2 does a count using rows column in sysindexes table if there is a difference then it does a full count.

@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.

@subscription_level specifies whether or not the validation is picked up by a set of subscribers. If 0,    validation is applied to all Subscribers. If 1, validation is only applied to a subset of the Subscribers specified by calls to    sp_marksubscriptionvalidation in the current open transaction.

@reserved identified for informational purposes only.

@publisher specifies a non-Microsoft SQL Server Publisher.

You need to have SELECT ALL permissions on the source table for the article being validated

sp_table_validation:

    This system stored procedure does a validation for each table individually in the specified publication in transactional replication. This stored procedure is executed against the publication database. The following is the syntax for this stored procedure.

sp_table_validation [ @table = ] 'table'

[ , [ @expected_rowcount = ] type_of_check_requested OUTPUT]

[ , [ @expected_checksum = ] expected_checksum OUTPUT]

[ , [ @rowcount_only = ] rowcount_only ]

[ , [ @owner = ] 'owner' ]

[ , [ @full_or_fast = ] full_or_fast ]

[ , [ @shutdown_agent = ] shutdown_agent ]

[ , [ @table_name = ] table_name ]

[ , [ @column_list = ] 'column_list' ]

@table is the name of the table to be validated.

@expected_rowcount specifies whether to return the expected number of rows in the table. If NULL, the actual rowcount is returned as an output parameter. If a value is provided, that value is checked against the actual rowcount to identify any differences.

@expected_checksum specifies whether to return the expected checksum for the table. If NULL, the actual checksum is returned as an output parameter. If a value is provided, that value is checked against the actual checksum to identify any differences.

@rowcount_only specifies what type of checksum or rowcount to perform.

    0 for perform a rowcount and a Microsoft SQL Server 7.0 compatible checksum.

    1 for perform a rowcount check only.

    2 for perform a rowcount and binary checksum.

@owner is the name of the owner of the table.

@full_or_fast is the method used to calculate the rowcount.

    0 does full count using count(*)

    1 does a count using rows column in sysindexes table. Since this table is not updated frequently this might be wrong sometimes.

    2 does a count using rows column in sysindexes table if there is a difference then it does a full count.

@shutdown_agent Specifies whether to shutdown distribution agent immediately after completing the validation.

@table_name is the table name of the view used for output messages.

@column_list is the list of columns that should be used in the checksum function.

How to validate subscribers in SQL Server Replication

    There have been many SQL Server DBAs who would like to compare the row count between tables in replication Publisher and Subscriber. SQL Server provides an easy way of doing this and is known as validation subscribers in replication which involves doing a row count between the table that is published and the ones that are subscribed to it.

    To Validate subscribers, you need to do the following.

1. Launch replication monitor.
   
2. Right click the publisher for which you want to validate the row count and click validate subscriptions.
   
3. Choose the subscribers that you want to validate, by default it chooses all subscribers for that publication.
   
4. You will be provided with the validation options.
   
5. Select the validation option that best suits you. It is advisable to choose, "Compute a fast row count: If differences are found, compute an actual row count"
   
6. Click ok.
   
7. Click ok.
   

   Now you will be able to see the row count match are mismatch between the publisher and the subscriber, when you double click the subscriber. The next article would be how to perform the same validation using TSQL.