I recently wanted to monitor the latency between publisher/subscriber within a SQL transactional replication setup. This can be done quite easily within the GUI, but I wanted to automate the process and make it run as a frequent check on the database.
I have written a script and stored procedure as to how I managed to achieve this.
SET QUOTED_IDENTIFIER ON GO /* Author: Rahman Agoro Description This stored procedure will insert tracer tokens and monitor SQL replication Date 14/02/2011 USE declare @alarm int exec spCheckReplicationLatency '00:02:00', '%SUBSCRIBER%',@alarm output select @alarm */ CREATE procedure [dbo].[spCheckReplicationLatency] (@DelayLength char(8)= '00:00:00', @Exclusion varchar(100),@Alarm int output ) as Begin SET NOCOUNT ON DECLARE @LoopStart int DECLARE @LoopEnd int DECLARE @Database varchar(50) DECLARE @Publication varchar(100) DECLARE @SQL NVARCHAR(4000) DECLARE @ReplicationIssue int DECLARE @Error int DECLARE @Seperator char(10) DECLARE @Message varchar(1000) DECLARE @operatorEmail varchar(100) SET @Seperator = char(9) --Check to see if the distribution database is present. IF DB_ID('distribution') is null RAISERROR ('distribution database not present',16,1) IF OBJECT_ID('tempdb..##replicationpublicationlatency') is not null drop table ##replicationpublicationlatency create table ##replicationpublicationlatency ( pubID int identity(1,1), databasename varchar(50), publication varchar(100) ) insert into ##replicationpublicationlatency (databasename,publication) select publisher_db,publication from distribution..MSpublications SET @LoopStart = 1 SET @LoopEnd = (SELECT COUNT(*) from ##replicationpublicationlatency) WHILE (@LoopStart <= @LoopEnd) BEGIN select @Database = databasename, @Publication = publication from ##replicationpublicationlatency WHERE pubID = @LoopStart SET @SQL = '' SET @SQL = @SQL + CHAR(10) + 'USE ' + @Database + ' exec sp_posttracertoken ' + '''' + @Publication + '''' + char(10) + char(13) SET @LoopStart = @LoopStart + 1 exec master..sp_executesql @SQL END DECLARE @ReturnInfo varchar(255) IF ISDATE('2000-01-01 ' + @DelayLength + '.000') = 0 BEGIN SELECT @ReturnInfo = 'Invalid time ' + @DelayLength + ',hh:mm:ss, submitted.'; -- This PRINT statement is for testing, not use in production. PRINT @ReturnInfo GOTO ErrorHandler END WAITFOR DELAY @DelayLength IF OBJECT_ID('tempdb..##TokenHistory') IS NOT NULL DROP TABLE ##TokenHistory ;with CTE_Latest_Tracer_Token_History(publication,publication_id,agent_id,agent_name,publisher_commit,distributor_commit,subscriber_commit,publisher_distributor,distributor_subscriber,Total_latency, RANKING) as ( SELECT a.publication,publication_id, agent_id, a.name, t.publisher_commit, t.distributor_commit, h.subscriber_commit,datediff(ss,t.publisher_commit,t.distributor_commit )publisher_distributor, datediff(ss,t.distributor_commit, h.subscriber_commit)distributor_subscriber,datediff(ss,t.publisher_commit,t.distributor_commit) + datediff(ss,t.distributor_commit, h.subscriber_commit) Total_latency, ROW_NUMBER() OVER (PARTITION BY publication_id, agent_id ,a.name ORDER BY t.publisher_commit DESC) AS RANKING FROM distribution..MStracer_tokens t JOIN distribution..MStracer_history h ON t.tracer_id = h.parent_tracer_id JOIN distribution..MSdistribution_agents a on a.id = agent_id and a.name not like @Exclusion ) select * into ##TokenHistory from CTE_Latest_Tracer_Token_History where RANKING in (1) order by publication SET @ReplicationIssue = (select count(*) from ##TokenHistory where Total_latency is null) SET @Message = 'Replication Latency Report ' + @@SERVERNAME if ISNULL(@ReplicationIssue,0) > 0 Begin Set @Alarm = 1 print cast(@ReplicationIssue as nvarchar)+N' replication agents has errors' select @operatorEmail = email_address from msdb..sysoperators where name = 'DBA' EXEC msdb.dbo.sp_send_dbmail @recipients=@operatorEmail, @subject = @Message, @body = 'Please see attached replication latency report', @body_format = 'HTML', @query ='SELECT agent_name,publisher_distributor,distributor_subscriber,Total_latency FROM ##TokenHistory ' , @query_attachment_filename = 'Replication_Latency_Report.txt', @attach_query_result_as_file = 'TRUE', @query_result_separator = @Seperator --WE have an issue if the issue flag = 1 Insert into tblReplicationLatencyLog (AgentName,LogDate,Issue) SELECT agent_name,GETDATE(),1 from ##TokenHistory where Total_latency is NULL Set @Error = @@Error if @Error <> 0 GoTo ErrorHandler End Else Insert into tblReplicationLatencyLog (AgentName,LogDate,Issue) SELECT 'No Issue',GETDATE(),0 Set @Alarm = 0 ErrorHandler: IF @Error <> 0 Begin Print 'Error Occured !' Return(@Error) End END GO
In this example, I wanted to log cases where there had been replication latency issues. Calling the stored procedure also gives an option to specify a wait for period, this is because when you insert a tracer token, you need to allow for some time for the token to get onto the subscribers and report back to the publisher. This is an example of how the stored procedure is actually used.
declare @alarm int exec spCheckReplicationLatency '00:02:00', '%SUBSCRIBER%',@alarm output select @alarm
The reason why I had the subscriber to exclude was because in my case, there was a particular subscriber across the atlantic which was like a DR database server and according to SLA’s, its allowed a few minutes lag behind, and I didnt want to raise alarms all the time due to this particular server.