1

I am trying to use sp_publication_validation stored procedure to validate that my replication setup is working correctly. This procedure is pretty straightforward and is described here.

I am running this command:

EXEC sp_publication_validation @publication = 'MyPublication'
    ,@rowcount_only = 1     -- (1 - rowcnt only, 2 - rowcnt and checksum)
    ,@full_or_fast = 0      -- (0 - full count using COUNT(*), 1 - use sysindexes.rows, 2 - conditional)

Initially everything seems to be ok and the results indicate that all of the tables match. Next I go over to the subscriber and manually delete a few rows from some of the tables. I manually verify that the row counts in my tables are now different between the publisher and the subscriber. Finally I run the sp_publication_validation procedure again and .... it says that everything is still OK. This is wrong! I also tried to return both rowcnt and checksum and it still doesn't detect the fact that there are differences between the publisher and subscriber.

I appreciate any ideas.

Thank you!

  • What's your version of SQL server? – Stoleg Aug 23 '14 at 0:04
  • It is SQL Server 2012 – SQL_Guy Aug 24 '14 at 17:49
  • Check this out: sqlmag.com/sql-server/validating-replicated-data – Stoleg Aug 24 '14 at 19:39
  • Thank you, Stoleg! This is very helpful article and explains how these stored procedures are being used. Basically now I understand that they are meant to work in conjunction with the rest of replication infrastructure and not by themselves. Microsoft could certainly do a better job explaining this fact in the documentation! – SQL_Guy Aug 27 '14 at 14:32
2

My explanation is that sp_publication_validation validates a Publication: set of Articles sent by Publisher and received by Subscriber. Since there were no data corruption in transit this SP does not report any errors.

To detect differences in replicated tables use SP_TABLE_VALIDATION.

Either returns rowcount or checksum information on a table or indexed view, or compares the provided rowcount or checksum information with the specified table or indexed view. This stored procedure is executed at the Publisher on the publication database and at the Subscriber on the subscription database.

SP_TABLE_VALIDATION on MSDN

  • Thank you for your suggestion! So I checked and it seems that sp_table_validation works the same way as sp_publication_validation, except at the individual table level. It does not compare the tables between the publisher and subscriber. For example, if I run it on publisher I get, say, 1000 records for "TableX". I then run it on subscriber and get 999 records fro "TableX". I guess it's up to me to capture the output and compare the two. If this is the case, I really don't see the value these stored procedures provide. I might as well write my own script. – SQL_Guy Aug 24 '14 at 0:36
0

OK, here is how I ended up addressing this. I basically wrote a script to compare row counts between tables on publisher and the distributor. One per-requisite is to have a linked server between the two servers. In my case it is called "distributor_ls" (since distributor is on the same server as the subscriber). First part of the script gather information about published databases into a temporary table called #tmp_replcationInfo. The credit for this first part of the script belongs here

Second part of the script then uses this information to gather the table row counts into a temporary table called #result for those tables where row counts differ. The differences are then displayed. I know it may not be not super-reliable because it relies on row counts stored in sys.partitions table, but it does what I need.

I hope somebody finds it useful.

    --==========================================================================
    -- 1. Gather publication information
    --==========================================================================

    DECLARE @Detail CHAR(1) 
    SET @Detail = 'N' 

    IF OBJECT_ID('tempdb..#tmp_replcationInfo') IS NOT NULL DROP TABLE                 #tmp_replcationInfo
    CREATE TABLE #tmp_replcationInfo ( 
PublisherDB VARCHAR(128),  
PublisherName VARCHAR(128), 
TableName VARCHAR(128), 
SubscriberServerName VARCHAR(128), 
    ) 

    EXEC sp_msforeachdb  
'use ?; 
IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1 
    insert into #tmp_replcationInfo 
    select  
          db_name() PublisherDB 
        , sp.name as PublisherName 
        , sa.name as TableName 
        , UPPER(srv.srvname) as SubscriberServerName 
    from dbo.syspublications sp  
    join dbo.sysarticles sa on sp.pubid = sa.pubid 
    join dbo.syssubscriptions s on sa.artid = s.artid 
    join master.dbo.sysservers srv on s.srvid = srv.srvid 
' 
    IF @Detail = 'Y' 
       SELECT * FROM #tmp_replcationInfo 
    ELSE 

    SELECT DISTINCT  
        PublisherDB 
        ,PublisherName 
        ,SubscriberServerName  
    FROM #tmp_replcationInfo 

    --==========================================================================
    -- 2. Determine row count descrepancies
    --==========================================================================

    IF OBJECT_ID('tempdb..#result') IS NOT NULL DROP TABLE #result
    CREATE TABLE #result (DBName SYSNAME, TableName VARCHAR(128), PublisherRowCnt BIGINT, SubscriberRowCnt BIGINT)

    DECLARE @DBName SYSNAME,
            @nSQL NVARCHAR(4000)

    DECLARE db_cur CURSOR FOR 
    SELECT DISTINCT PublisherDB
    FROM #tmp_replcationInfo 

    OPEN db_cur
    FETCH NEXT FROM db_cur INTO @DBName

    WHILE @@FETCH_STATUS = 0
    BEGIN

SET @nSQL = 'INSERT #result (DBName, TableName, PublisherRowCnt, SubscriberRowCnt)
SELECT ''' + @DBName + ''', ISNULL(p.TableName, s.TableName), p.RowCnt, s.RowCnt
FROM 
(
    SELECT
            t.name AS TableName,
            SUM(p.Rows) AS RowCnt
    FROM    ' + @DBName + '.sys.tables t
    JOIN    ' + @DBName + '.sys.partitions p ON t.object_id = p.object_id
    WHERE   t.is_ms_shipped = 0
    AND     p.index_id < 2 -- 0:Heap, 1:Clustered
    AND     t.is_published = 1
    GROUP BY t.name
) p
FULL JOIN 
(
    SELECT
            t.name AS TableName,
            SUM(p.Rows) AS RowCnt
    FROM    distributor_ls.' + @DBName + '.sys.tables t
    JOIN    distributor_ls.' + @DBName + '.sys.partitions p ON t.object_id = p.object_id
    WHERE   t.is_ms_shipped = 0
    AND     p.index_id < 2 -- 0:Heap, 1:Clustered
    -- add more filters as needed
    AND     t.name NOT LIKE ''%Delta''
    AND     t.name NOT LIKE ''%Global''
    AND     t.name NOT LIKE ''RowVer%''
    GROUP BY t.name
) s
ON  p.TableName = s.TableName
WHERE ISNULL(p.RowCnt, 0) <> ISNULL(s.RowCnt, 0)
'

-- PRINT @nSQL
EXEC sp_executesql @nSQL

FETCH NEXT FROM db_cur INTO @DBName

    END 

    CLOSE db_cur
    DEALLOCATE db_cur

    --==========================================================================
    -- 3. Return results
    --==========================================================================

    SELECT *
    FROM #result
    ORDER BY DBName, TableName

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.