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!