Custom SQL Replication reports for System Center 2012 Configuration Manager

Posted on by — No Comments ↓

This post has been republished via RSS; it originally appeared at: Device Management in Microsoft articles.

First published on TECHNET on May 02, 2012

Have you been looking for a way to visually see the status of SQL replication within your System Center 2012 Configuration Manager (ConfigMgr) environment? If you have, keep reading; this blog introduces three custom reports I use for watching replication in a ConfigMgr client environment here at Microsoft.


Replication Initialization Report


When a site is first added, replication is initialized, meaning the global data from the CAS is sent down to the primary site and the site data is sent up to the CAS. After initialization, the changes made on the sites are sent to the receiving site to be processed and keep the databases in sync. There may be times when you need to re-initialize (or re-init) replication for whatever reason. During either of these processes you can use a custom report to track the status of initialization.
A coworker built the rdl for this report and I don’t want to forget to give him credit - thanks Partha!


To get this report to work all you will need is the rdl file; the queries are built in to the report. Upload the rdl file into SSRS, update the data source and make sure to check the box “Hide” for the parameter and you’ll be all set to use this report.


You can get the Replication Initialization Report file here .



Replication Status Reports


After initializing, the sites will begin to send delta changes from site to site. The changes are sent in messages via SQL Service Broker and are picked up and processed within SQL. A site can go into a “Degraded” or “Failed” state. You’ll want to know when a site is degraded because that usually means there is a backlog or there will be.

I’ve created two versions of this report; one that only requires the rdl file but does not show the SQL backlog counts and one that requires a little more setup (linked servers and a stored procedure) but does show the backlogs by site. I’ll refer to the simple report as “Replication Status Report (No Backlogs)” and the more involved report as “Replication Status Report (With Backlogs)”.


Replication Status Report (No Backlogs)


Like the previous report, all that is needed to implement this report is to upload the “rdl” file into SSRS. You’ll then need to update the data source and make the parameters “hidden” by checking the “Hide” checkbox (for each parameter). The “OverallLinkStatusThreshold” parameter can be used to control how the primary and secondary sites are displayed in the overall status section. By default, if you have 8 or fewer sites (primary and secondary) the overall status will show all sites in one horizontal row (where the first table in the screenshot is). If you have more than 8 sites then only the primary sites will show up in the first table and the secondary sites will be displayed in a separate rollup table beneath the primary sites.


You can download the Replication Status Report (No Backlogs) here .


Replication Status Report (With Backlogs)


This report is a little more involved than the “Replication Status Report (No Backlogs)” report to implement. The reason for this is because this report queries all the primary sites to determine the size of each servers’ queue (the backlog count).


In order to query the backlogs from each site, your CAS SQL Server will need to have a linked server defined for each of the primary SQL Servers in your hierarchy. Any time you add a primary site you’ll need to make sure to add a linked server for the primary on the CAS in order for the reports to continue to work. There are many ways to create the linked servers, however, in order for the reports to work (in their current design) the linked server name should be the name of the SQL Server. If you have named instances the name should only contain the server name and the instance name can be defined in the “data_source” or “provider_string”. If you’d like to use a script to create your linked servers you can use this one:



SET NOCOUNT ON;
DECLARE @ServerName nvarchar(20)
,@InstanceName nvarchar(544)
,@SQL nvarchar(4000);
DECLARE linkedsrvrs CURSOR FAST_FORWARD FOR
SELECT UPPER(SUBSTRING(srv.SQLInstance,1,CHARINDEX('.',srv.SQLInstance,1)-1)) SQLServer
,srv.SQLInstance
FROM dbo.ServerData srv
LEFT OUTER JOIN sys.servers lnk
ON SUBSTRING(srv.SQLInstance,1,CHARINDEX('.',srv.SQLInstance,1)-1) = lnk.name
WHERE lnk.name IS NULL
AND srv.ServerRole = 'Peer';

OPEN linkedsrvrs
FETCH NEXT FROM linkedsrvrs INTO @ServerName, @InstanceName;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Use this portion if you don't have named instances:
SET @SQL = 'EXECUTE sp_addlinkedserver '''+@ServerName+''';'
-- Use this portion if you have named instances or you aren't sure:
--SET @SQL = '
-- EXECUTE sp_addlinkedserver
-- @server = '''+@ServerName+'''
-- ,@srvproduct = ''''
-- ,@provider = ''SQLNCLI''
-- ,@datasrc = '''+@InstanceName+''';
-- EXECUTE sp_serveroption
-- @server = '''+@ServerName+'''
-- ,@optname = ''rpc''
-- ,@optvalue = N''true'';
-- EXECUTE sp_serveroption
-- @server = '''+@ServerName+'''
-- ,@optname = ''rpc out''
-- ,@optvalue = N''true'';
-- ';
EXECUTE sp_executesql @SQL;
PRINT @ServerName+' added'

FETCH NEXT FROM linkedsrvrs INTO @ServerName, @InstanceName;
END
CLOSE linkedsrvrs;
DEALLOCATE linkedsrvrs;
GO

This report also requires one stored procedure to be created in order to get the backlog count. The code for the stored procedure “MaxCMBacklogBySite” is found below.


MaxCMBacklogBySite

Make sure to update the “USE” database and the name of your CAS server DB for the cursor (the highlighted fields). Remember, the ConfigMgr database cannot be used to store custom objects so you’ll need to use a different database!



USE [The database on which you want to store this procedure];
GO

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'MaxCMBacklogBySite')
DROP PROCEDURE dbo.MaxCMBacklogBySite
GO

CREATE PROCEDURE dbo.MaxCMBacklogBySite
@NumOfSeconds int = 15
AS
BEGIN
SET NOCOUNT ON

/*** Testing Variable ***/
--declare @NumOfSeconds int = 15
/************************/

CREATE TABLE #Temp (SiteName char(3), GlobalQueueCount int, SiteQueueCount int, Run int)

DECLARE @Site nvarchar(3)
,@CMDB nvarchar(7)
,@ID tinyint
,@SQLInstance nvarchar(25)
,@SQL nvarchar(4000) = ''

DECLARE Sites CURSOR FAST_FORWARD FOR
SELECT SiteCode
,SUBSTRING(SQLInstance,1,CHARINDEX('.',SQLInstance,1)-1) Instance
,ConfigMgrDatabase
,ID
FROM [CM_123 - UPDATE ME!!!!].dbo.ServerData srv /****** UPDATE THIS PORTION FOR THE CAS YOU ARE CREATING THIS ON!!!! ******/
WHERE ServerRole != 'Proxy'

OPEN Sites
FETCH NEXT FROM Sites INTO @Site, @SQLInstance, @CMDB, @ID

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = @SQL + '
SELECT '''+@Site+''' AS [Site]
,SUM(CASE que.name WHEN ''ConfigMgrDRSQueue'' THEN dps.row_count END) AS [GlobalQueueCount]
,SUM(CASE que.name WHEN ''ConfigMgrDRSSiteQueue'' THEN dps.row_count END) AS [SiteQueueCount]
FROM '+CASE WHEN @ID = 0 THEN @CMDB ELSE '['+@SQLInstance+'].'+@CMDB END+'.sys.dm_db_partition_stats dps
INNER JOIN '+CASE WHEN @ID = 0 THEN @CMDB ELSE '['+@SQLInstance+'].'+@CMDB END+'.sys.internal_tables tbl
ON dps.object_id = tbl.object_id
AND dps.index_id < 2
INNER JOIN '+CASE WHEN @ID = 0 THEN @CMDB ELSE '['+@SQLInstance+'].'+@CMDB END+'.sys.service_queues que
ON tbl.parent_object_id = que.object_id
AND que.name IN (''ConfigMgrDRSQueue'',''ConfigMgrDRSSiteQueue'')'

FETCH NEXT FROM Sites INTO @Site, @SQLInstance, @CMDB, @ID
IF @@FETCH_STATUS = 0
SET @SQL = @SQL + CHAR(13) + 'UNION ALL ' + CHAR(13)
END
CLOSE Sites
DEALLOCATE Sites

DECLARE @i int = 1

IF @NumOfSeconds < @i
BEGIN
INSERT #Temp (SiteName, GlobalQueueCount, SiteQueueCount)
EXECUTE sp_executesql @SQL
UPDATE #Temp SET Run = @i
END
ELSE
BEGIN
WHILE @i <= @NumOfSeconds
BEGIN
IF @i = 1 OR @i = @NumOfSeconds
BEGIN
INSERT #Temp (SiteName, GlobalQueueCount, SiteQueueCount)
EXECUTE sp_executesql @SQL
UPDATE #Temp SET Run = @i WHERE Run IS NULL
SELECT @i = @i+1
WAITFOR DELAY '00:00:01'
END
ELSE
BEGIN
SELECT @i = @i+1
WAITFOR DELAY '00:00:01'
END
END
END

SELECT tmp.SiteName
,ISNULL(SUM(CASE WHEN tmp.Run = 1 THEN tmp.GlobalQueueCount END),0) GlobalQueueAtStart
,ISNULL(SUM(CASE WHEN tmp.Run = @NumOfSeconds THEN tmp.GlobalQueueCount END),0) GlobalQueueAtEnd
,ISNULL(SUM(CASE WHEN tmp.Run = 1 THEN tmp.SiteQueueCount END),0) SiteQueueAtStart
,ISNULL(SUM(CASE WHEN tmp.Run = @NumOfSeconds THEN tmp.SiteQueueCount END),0) SiteQueueAtEnd
,ISNULL(SUM(CASE WHEN tmp.Run = 1 THEN tmp.GlobalQueueCount END),0) TotalQueueAtStart
,ISNULL(SUM(CASE WHEN tmp.Run = @NumOfSeconds THEN tmp.GlobalQueueCount END),0) TotalQueueAtEnd
FROM #Temp tmp
GROUP BY tmp.SiteName

DROP TABLE #Temp
END
GO


After creating the linked servers and installing the stored procedure upload the “rdl” file into SSRS. You’ll then need to update the data source and the parameters. I suggest making the parameters “hidden” by checking the “Hide” checkbox for each parameter. You can also update the default value for when the backlogs will show a red background in the report in the “Backlogs_RedThreshold” parameter. The default is 100k; meaning, if the backlogs reach greater than 100k messages the background will be red in the backlogs section. The other parameters were mentioned in the “SQL Replication Status (No Backlogs)” report.


You can download the Replication Status Report (With Backlogs) here . Note: this file is simply named “Replication Status Report”.


SQL Replication Backlogs


This report uses the same stored procedure in the SQL Replication Status report. Thus, no additional setup is required to use this report. Even though the SQL Replication Status report does show a ‘snapshot’ of the backlogs, this report looks at the backlogs at two different times and then shows whether the backlogs are increasing or decreasing. By default the report/stored procedure captures the backlogs when first run and then 15 seconds later, but you can provide a larger number of seconds if desired.



Upload the rdl file into SSRS, change the data source, and then update the parameters like you did with the SQL Replication Status report (or allow users to choose their own number of seconds to wait by not checking the “Hide” checkbox).


You can download the SQL Replication Backlogs report here .


These reports, especially the “SQL Replication Status” report, helps my team keep a proactive eye on replication because these reports are sent via subscriptions regularly throughout the day. I hope you find them as helpful or useful as we have!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.