Keep watch on replication undistributed command in SQL Server via SSIS

Keep watch on replication undistributed command in SQL Server via SSIS

In my earlier blog “Find undistributed replication command in SQL Server” I have exaplained the importance and usage of “sp_replmonitorsubscriptionpendingcmds” system stored procedure to find out undistributed command.

If we execute “sp_replmonitorsubscriptionpendingcmds”, you may get undistributed command one time manually but I always try to capture important information in my monitoring application so that If there is any issue, it comes to notice of our support people and they can inform DBA team immediately.

This is the requirement which triggered this article.

Here is my requirement and logic I want to implement.

I have one of the main production database (let us call this ProdDB) which is replicated to three different instance with same publication so we have three subscriber to ProdDB database. Out of these three databases, one database (let us call ProdDBSub1) is being used to display the report in our application which is being used by approx 12000 concurrent users.

Higher latency or huge undistributed command will provide wrong information to the client which is not at all acceptable and this is why I have captured latency of replication in my monitoring application (Find the script here) and also captured total undistributed command in my monitoring application, if undistributed command number keeps increasing for long time and doesn’t decrease, our support team member immediately call DBA to look at replication.

I have created one table in “DBAdb” database which has row for each publication/subscriber combination along with other important details. Each time I query “sp_replmonitorsubscriptionpendingcmds”, I used to store result in that table (update the current row only). I have this process in one SSIS package which is scheduled to run every 15 seconds .

Let us create some foundation in SQL Server before we start creating SSIS package.

[sourcecode language=”sql”]
–create new database, if it does not exists already
CREATE Database DBAdb
GO

–create one table which stored information for undistributed command
USE [DBAdb]
GO

CREATE TABLE [UndistributedCommands](
[Seq] [int] IDENTITY(1,1) NOT NULL,
[PublicationName] [varchar](100) NULL,
[SubscriberName] [varchar](100) NULL,
[PendingCommands] [int] NULL,
[PendingDuration] [int] NULL,
[LastRefreshed] [datetime] NULL,
[LastNonEmpty] [datetime] NULL
) ON [PRIMARY]
GO

–insert one record for your publication/subscriber.
–this record will be updated everytime for same publication/subscriber
INSERT INTO UndistributedCommands
SELECT ‘ProdDBPub’,’ProdDBSub’,0,0,GETDATE(),GETDATE()
GO
[/sourcecode]

Now create one SSIS package and have three package level variable as shown in following screen capture.

1Variable

After having all three variables, take one “Execute SQL” Task in your package and use following query inside the task.

[sourcecode language=”sql”]
–system stored procedure to run in distribution database
execute sp_replmonitorsubscriptionpendingcmds
–replication publisher server
@publisher =’PubServer’,
–replication publisher database
@publisher_db = ‘ProdDBPub’,
–replication publication name
@publication =’ProdDBSub’,
–replication subscriber server
@subscriber =’SubServer’,
–replication subscriber database
@subscriber_db =’ProdDBSub1′,
–choose type of subscription you have
@subscription_type =’1′ –0 for push and 1 for pull
GO
[/sourcecode]

Here is the screen capture of “Execute SQL” Task:

2ExecuteSQL

System Stored Procedure “sp_replmonitorsubscriptionpendingcmds” will return result set and that should be mapped with one of the variable we have created above. Follow the screen capture.

Please note that, I have had database connection of distribution database of replication for above given screen capture.

3ExecuteSQL

Now, take one foreach loop container and iterate it with “Obj_Result” variable.

4ForEachLoop

Map two variables with the result set coming from “sp_replmonitorsubscriptionpendingcmds” in the Foreach Loop container itself:

5ForeachLoop

Now take one more execute SQL Task and kept it inside the foreach loop container. That “Execute SQL” task will have following query to update data in table.

[sourcecode language=”sql”]
DECLARE @PendingCMDCount BIGINT
DECLARE @ExstimatedProcessTime BIGINT

SET @PendingCMDCount = ?
SET @ExstimatedProcessTime =?

UPDATE
UndistributedCommands
SET
PendingDuration = @ExstimatedProcessTime ,
PendingCommands = @PendingCMDCount,
LastRefreshed = getdate()
WHERE
SubscriberName = ‘ProdDBSub’ AND PublicationName=’ProdDBPub’

IF @PendingCMDCount <> 0
BEGIN
UPDATE
UndistributedCommands SET LastNonEmpty = getdate()
WHERE
SubscriberName = ‘ProdDBSub’ AND PublicationName=’ProdDBPub’
END
[/sourcecode]

6ExecuteSQL

Please note that this Execute SQL task is having connection with “DBAdb” database and my “UndistributedCommand” table is there and data from this table will come to my monitoring application.

We have collected two valued in two different variable in Foreach Loop Container which we will pass to “Execute SQL Task” inside the loop.

7ExecuteSQL

Now, you are having package ready to run.

8FinalPackage

Execute this package from SQL Server Job or from Windows Schedule task via .BAT file as per the frequency needed for your business logic, I used to keep it at 15 seconds. Display the rows of “UndistributedCommands” in monitoring application, if you have, if undistributed command is >0.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Keep watch on replication undistributed command in SQL Server via SSIS”

Comments are closed.