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.
--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
Now create one SSIS package and have three package level variable as shown in following screen capture.
After having all three variables, take one “Execute SQL” Task in your package and use following query inside the task.
--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
Here is the screen capture of “Execute SQL” Task:
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.
Now, take one foreach loop container and iterate it with “Obj_Result” variable.
Map two variables with the result set coming from “sp_replmonitorsubscriptionpendingcmds” in the Foreach Loop container itself:
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.
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
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.
Now, you are having package ready to run.
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.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.