Validating Replication (All or Just One Article)

Do I want to only count the white beans, or all of them?

You may sometimes have reports or other processes that are dependent on transactional replication being current.  If that is the case, you will probably need a mechanism to check and see if, in fact, replication is caught up.  Here is my solution to that, without having to resort to Replication Monitor all the time. The bonus?  This could be inserted into conditional workflows to help streamline processes (i.e., validate publications before moving on to Step 2 of process).

To do this, I chose to make three stored procedures.  The first one to just check all publications on a server, one to check just one publication on a server, and one central sproc to rule them all.  You simply execute the master stored procedure, and based on the parameters you feed, it decides which of the other two to execute.

So, let’s get coding!

For our first sproc, let’s check all our publications. Let’s look at the code. This guy will go through and check to see if all the publications are caught up.  If not, it will wait a minute and try it again, until they all are.  I would personally make synonyms for any four-part names, which I have left as such so you can see how it would work if your distributor is on a linked server.  All of these procedures would be run from the publisher server instance.

USE [PublisherDatabaseNameHere]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ValidateAllReplication]
(
    @PublisherDatabase sysname
)


AS
BEGIN

SET NOCOUNT ON;

    DECLARE @PublicationInfo TABLE
    (
        Publisher sysname NULL,
        Publication sysname NULL,
        Article sysname NULL,
        alert_error_text NVARCHAR(MAX) NULL,
        Time DATETIME2 NULL
    );

    WHILE
    (
        SELECT COUNT(DISTINCT Publication)
        FROM @PublicationInfo
        WHERE [Time]
              BETWEEN DATEADD(MINUTE, -5, GETDATE()) AND GETDATE()
              AND alert_error_text LIKE '%passed data validation%'
    ) <>
    (
        SELECT COUNT(*)
        FROM [DistributorServer].[DistributorDatabaseName].dbo.MSpublications
        WHERE publisher_db = @PublisherDatabase
              AND description NOT LIKE 'Snapshot publication %'
    )
    BEGIN

        DECLARE @lcPubName sysname;
        DECLARE @dbCursor CURSOR;
        DECLARE @lcSql NVARCHAR(MAX);

        SET @dbCursor = CURSOR FAST_FORWARD FOR
        SELECT name
        FROM dbo.syspublications
        WHERE description NOT LIKE 'Snapshot publication%'
        ORDER BY name;

        OPEN @dbCursor;

        FETCH NEXT FROM @dbCursor
        INTO @lcPubName;

        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            EXEC sys.sp_publication_validation @publication = @lcPubName,
                                               @rowcount_only = 1, -- 1 = don't do checksums
                                               @full_or_fast = 2;  -- 2 = fast unless fail then full
            FETCH NEXT FROM @dbCursor
            INTO @lcPubName;
        END;

        DELETE FROM @PublicationInfo;
        INSERT INTO @PublicationInfo
        (
            Publisher,
            Publication,
            Article,
            alert_error_text,
            Time
        )
        SELECT Publisher,
               Publication,
               Article,
               alert_error_text,
               [Time]
        FROM [DistributorServer].[DistributorDatabaseName].dbo.sysreplicationalerts
        WHERE [Time]
              BETWEEN DATEADD(MINUTE, -5, GETDATE()) AND GETDATE()
              AND alert_error_text LIKE '%passed data validation%';


        CLOSE @dbCursor;
        DEALLOCATE @dbCursor;


        IF
        (
            SELECT COUNT(DISTINCT Publication) FROM @PublicationInfo
        ) <>
        (
            SELECT COUNT(*)
            FROM [DistributorServer].[DistributorDatabaseName].dbo.MSpublications
            WHERE publisher_db = @PublisherDatabase
                  AND publication NOT LIKE 'Use whatever naming mechanism you have for snapshot publications here'
        )
                WAITFOR DELAY '00:01';

        SELECT 'Everything done successfully' AS StatusCheck;

    END;

END;


Now, we’ll make the one for a specific publication:

USE [PublisherDatabaseNameHere]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ReplicationValidateSpecificPublication]
(
    @PublisherDatabase sysname,
    @Publication sysname
)


AS
BEGIN

SET NOCOUNT ON;

    DECLARE @PublicationInfo TABLE
    (
        Publisher sysname NULL,
        Publication sysname NULL,
        Article sysname NULL,
        alert_error_text NVARCHAR(MAX) NULL,
        Time DATETIME2 NULL
    );

    WHILE
    (
        SELECT COUNT(DISTINCT Publication)
        FROM @PublicationInfo
        WHERE [Time]
              BETWEEN DATEADD(MINUTE, -5, GETDATE()) AND GETDATE()
			  AND Publication = @Publication
              AND alert_error_text LIKE '%passed data validation%'
    ) <>
    (
        SELECT COUNT(*)
        FROM [DistributorServer].[DistributorDatabaseName].dbo.MSpublications
        WHERE publisher_db = @PublisherDatabase
			  AND publication = @Publication
              AND description NOT LIKE 'Snapshot publication %'
    )
    BEGIN

        DECLARE @lcPubName sysname;
        DECLARE @dbCursor CURSOR;
        DECLARE @lcSql NVARCHAR(MAX);

        SET @dbCursor = CURSOR FAST_FORWARD FOR
        SELECT name
        FROM dbo.syspublications
        WHERE name = @Publication
		AND description NOT LIKE 'Snapshot publication%'
        ORDER BY name;

        OPEN @dbCursor;

        FETCH NEXT FROM @dbCursor
        INTO @lcPubName;

        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            EXEC sys.sp_publication_validation @publication = @lcPubName,
                                               @rowcount_only = 1, -- 1 = don't do checksums
                                               @full_or_fast = 2;  -- 2 = fast unless fail then full
            FETCH NEXT FROM @dbCursor
            INTO @lcPubName;
        END;

        DELETE FROM @PublicationInfo;
        INSERT INTO @PublicationInfo
        (
            Publisher,
            Publication,
            Article,
            alert_error_text,
            Time
        )
        SELECT Publisher,
               Publication,
               Article,
               alert_error_text,
               [Time]
        FROM [DistributorServer].[DistributorDatabaseName].dbo.sysreplicationalerts
        WHERE [Time]
              BETWEEN DATEADD(MINUTE, -5, GETDATE()) AND GETDATE()
			  AND Publication = @Publication
              AND alert_error_text LIKE '%passed data validation%';


        CLOSE @dbCursor;
        DEALLOCATE @dbCursor;


        IF
        (
            SELECT COUNT(DISTINCT Publication) FROM @PublicationInfo
        ) <>
        (
            SELECT COUNT(*)
            FROM [DistributorServer].[DistributorDatabaseName].dbo.MSpublications
            WHERE publisher_db = @PublisherDatabase
                  AND publication NOT LIKE 'Use whatever naming mechanism you have for snapshot publications here'
				  AND publication = @Publication
        )
            WAITFOR DELAY '00:01';

        SELECT 'Everything done successfully' AS StatusCheck;

    END;

END;


Finally, we’ll make the “one ring to rule them all” stored procedure.

CREATE PROCEDURE dbo.ReplicationValidationMasterProc
(
    @PublisherDatabase sysname,
    @Publication sysname
)


AS
BEGIN

SET NOCOUNT ON;

    --In the first instance, we want to validate all the publications.  

    IF (@Publication IS NOT NULL)
        EXEC [dbo].[ReplicationValidateSpecificPublication] @PublisherDatabase = PublisherDatabaseNameHere,
                                                             @Publication = @Publication;


    --This time, we only want to do a specific publication.

    ELSE
        EXEC [dbo].[ValidateAllReplication] @PublisherDatabase = @PublisherDatabase = PublisherDatabaseNameHere;
END;

Sample Output:

/*————————

EXEC [dbo].[ValidateAllReplication] @PublisherDatabase = N’PublisherDatabaseNameHere’, @Publication = NULL;

————————*/

Generated expected rowcount value of 0 for Article1OfPub1.

Generated expected rowcount value of 0 for Article2OfPub1.

Generated expected rowcount value of 5669 for Article3OfPub1.

Generated expected rowcount value of 4330 for Article1OfPub2.

Generated expected rowcount value of 566 for Article2OfPub2.

Generated expected rowcount value of 729 for Article3OfPub2.

Generated expected rowcount value of 410 for Article4OfPub2.

Generated expected rowcount value of 552 for Article1OfPub3.

Generated expected rowcount value of 1 for Article2OfPub3.

Generated expected rowcount value of 1 for Article3OfPub3.

/*————————

EXEC [dbo].[ValidateAllReplication]   @PublisherDatabase = N’PublisherDatabaseNameHere’, @Publication = N’SpecificPubNameHere’;

————————*/

Generated expected rowcount value of 0 for Article1OfPub1.

Generated expected rowcount value of 0 for Article2OfPub1.

Generated expected rowcount value of 5669 for Article3OfPub1.

Hope you find this helpful!

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s