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.

Find undistributed replication command in SQL Server

Find undistributed replication command in SQL Server

I have written one article to find replication latency sometime back. If I find latency of 10 seconds or more for longer time then I always try to find the bottleneck and fix it because in the environment I am working, won’t afford big latency.

In finding the bottleneck, my first step would be to find how many commands are pending to apply in replicated database. To find undistributed command, you can use replication monitor which comes with SQL Server itself or use sp_replmonitorsubscriptionpendingcmds” system stored procedure which you can find in distributor database of replication.

 

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

 

Here is the screen capture of output.

ReplicationPendingCommand

This screen capture shows that total number of 64 commands are waiting to apply from “AdventureWorks2012” database to “AdventureWorks2012ReplDB” and that would take 0 (<1) seconds.

I have written few more replication related articles, have a look if you are interested.

  • Understanding Replication with implementation of Snapshot replication in SQL Server 2012 (Click Here)
  • Remove Replication from SQL Server 2012 (Click Here)
  • Error Fix – Cannot execute as the database principal(Click Here)
  • Setup Transaction Replication in SQL Server 2012 (Click Here)
  • Add new article in transactional replication in SQL Server 2012 (Click Here)
  • Delete article from transactional replication in SQL Server 2012 (Click Here)
  • Find highest latency in transactional replication in SQL Server 2012 (Click Here)
  • Be familiar with important terms of replication in SQL Server (Click Here)
  • Find Object participated in replication (Click Here)
  • Add table in transactional replication from script in SQL Server (Click Here)
  • Add stored procedure in transactional replication by script in SQL Server (click Here)
  • Forcefully remove replication publisher, subscriber and distributor in SQL Server (Click Here)
  • Move replicated database file to new location in SQL Server 2012 (Click Here)
  • Script backup of replication setup of SQL Server by SSIS and SMO (Click Here)
  • Insert Tracer Token to see replication latency (Click Here)
  • Generate script to add multiple table in replication as article (Click Here)

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

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

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