SQL Server replication is a powerful feature that allows you to distribute and synchronize data across multiple servers. However, the built-in Replication Monitor tool provided by Microsoft lacks the ability to view detailed information at the article level. In this article, we will discuss a script that can help you monitor the performance and status of your replication setup.

Step 1: Gather information about current transactional replication

The first step in monitoring your replication setup is to gather information about the articles being replicated. This can be done by querying system tables such as distribution.dbo.MSpublications, distribution.dbo.MSsubscriptions, and distribution.dbo.MSarticles. By joining these tables with the sys.servers table, you can obtain details about the publisher, subscriber, and the articles being replicated.

IF OBJECT_ID('tempdb..#tempTransReplication') IS NOT NULL 
    DROP TABLE #tempTransReplication

CREATE TABLE #tempTransReplication (
    publisher_id INT,
    publisher_srv VARCHAR(255),
    publisher_db VARCHAR(255),
    publication VARCHAR(255),
    subscriber_id INT,
    subscriber_srv VARCHAR(255),
    subscriber_db VARCHAR(255),
    object_type VARCHAR(255),
    source_owner VARCHAR(255),
    source_object VARCHAR(255),
    destination_owner VARCHAR(255),
    destination_object VARCHAR(255),
    rowcount_publisher INT,
    rowcount_subscriber INT,
    rowcount_diff INT
)

INSERT INTO #tempTransReplication
SELECT s.publisher_id, ss2.data_source, a.publisher_db, p.publication, s.subscriber_id, ss.data_source, s.subscriber_db, NULL, a.source_owner, a.source_object, ISNULL(a.destination_owner, a.source_owner), a.destination_object, NULL, NULL, NULL
FROM distribution.dbo.MSarticles AS a
INNER JOIN distribution.dbo.MSsubscriptions AS s ON a.publication_id = s.publication_id AND a.article_id = s.article_id
INNER JOIN [master].sys.servers AS ss ON s.subscriber_id = ss.server_id
INNER JOIN distribution.dbo.MSpublications AS p ON s.publication_id = p.publication_id
LEFT OUTER JOIN [master].sys.servers AS ss2 ON p.publisher_id = ss2.server_id
WHERE s.subscriber_db <> 'virtual'

Step 2: Gather rowcount at Publisher side

Next, we need to obtain the rowcount for each replicated table at the publisher side. This can be done by querying the sys.objects and dbo.sysindexes tables in each replicated database. By using dynamic SQL, we can dynamically query each database and retrieve the rowcount for all replicated tables.

IF OBJECT_ID('tempdb..#tempPublishedArticles') IS NOT NULL 
    DROP TABLE #tempPublishedArticles

CREATE TABLE #tempPublishedArticles (
    publisher_db VARCHAR(255),
    source_owner VARCHAR(255),
    source_object VARCHAR(255),
    object_type VARCHAR(255),
    rowcount_publisher INT
)

DECLARE @pub_db VARCHAR(255), @strSQL_P VARCHAR(4000)
DECLARE db_cursor_p CURSOR FOR SELECT DISTINCT publisher_db FROM distribution.dbo.MSpublications

OPEN db_cursor_p
FETCH NEXT FROM db_cursor_p INTO @pub_db

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL_P = 'SELECT ' + '''' + @pub_db + '''' + ' AS publisher_db, s.name AS source_owner, o.name AS source_object, o.Type_Desc AS object_type, i.rowcnt AS rowcount_publisher FROM ' + @pub_db + '.sys.objects AS o INNER JOIN ' + @pub_db + '.sys.schemas AS s on o.schema_id = s.schema_id LEFT OUTER JOIN ' + @pub_db + '.dbo.sysindexes AS i on o.object_id = i.id WHERE ' + '''' + @pub_db + '''' + ' + ' + '''' + '.' + '''' + ' + s.name' + ' + ' + '''' + '.' + '''' + ' + o.name' + ' IN (SELECT publisher_db + ' + '''' + '.' + '''' + ' + source_owner + ' + '''' + '.' + '''' + ' + source_object FROM #tempTransReplication) AND ISNULL(i.indid, 0) IN (0, 1) ORDER BY i.rowcnt DESC'

    INSERT INTO #tempPublishedArticles
    EXEC (@strSQL_P)

    FETCH NEXT FROM db_cursor_p INTO @pub_db
END

CLOSE db_cursor_p
DEALLOCATE db_cursor_p

Step 3: Gather rowcount at Subscriber(s) side

Similarly, we need to obtain the rowcount for each replicated table at the subscriber(s) side. This can be done by querying the sys.objects and dbo.sysindexes tables in each subscriber database via linked servers. By using dynamic SQL and the linked servers we set up earlier, we can retrieve the rowcount for all replicated tables on the subscriber(s) side.

IF OBJECT_ID('tempdb..#tempSubscribedArticles') IS NOT NULL 
    DROP TABLE #tempSubscribedArticles

CREATE TABLE #tempSubscribedArticles (
    subscriber_srv VARCHAR(255),
    subscriber_db VARCHAR(255),
    destination_owner VARCHAR(255),
    destination_object VARCHAR(255),
    object_type VARCHAR(255),
    rowcount_subscriber INT
)

DECLARE @sub_srv VARCHAR(255), @sub_db VARCHAR(255), @strSQL_S VARCHAR(4000)
DECLARE db_cursor_s CURSOR FOR SELECT DISTINCT subscriber_srv, subscriber_db FROM #tempTransReplication

OPEN db_cursor_s
FETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_db

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL_S = 'SELECT ' + '''' + @sub_srv + '''' + ' AS subscriber_srv, ' + '''' + @sub_db + '''' + ' AS subscriber_db, ' + 's.name AS destination_owner, o.name AS destination_object, o.Type_Desc AS object_type, i.rowcnt AS rowcount_subscriber FROM ' + @sub_srv + '.' + @sub_db + '.sys.objects AS o INNER JOIN ' + @sub_srv + '.' + @sub_db + '.sys.schemas AS s on o.schema_id = s.schema_id LEFT OUTER JOIN ' + @sub_srv + '.' + @sub_db + '.dbo.sysindexes AS i on o.object_id = i.id WHERE ' + '''' + @sub_srv + '.' + @sub_db + '''' + ' + ' + '''' + '.' + '''' + ' + s.name' + ' + ' + '''' + '.' + '''' + ' + o.name' + ' IN (SELECT subscriber_srv + ' + '''' + '.' + '''' + ' + subscriber_db + ' + '''' + '.' + '''' + ' + destination_owner + ' + '''' + '.' + '''' + ' + destination_object FROM #tempTransReplication) AND ISNULL(i.indid, 0) IN (0, 1) ORDER BY i.rowcnt DESC'

    INSERT INTO #tempSubscribedArticles
    EXEC (@strSQL_S)

    FETCH NEXT FROM db_cursor_s INTO @sub_srv, @sub_db
END

CLOSE db_cursor_s
DEALLOCATE db_cursor_s

Step 4: Update table #tempTransReplication with rowcount

Now that we have obtained the rowcount for each replicated table at the publisher and subscriber(s) side, we can update the #tempTransReplication table with these values. We can also calculate the difference in rowcount between the publisher and subscriber(s) side.

UPDATE t
SET rowcount_publisher = p.rowcount_publisher, object_type = p.object_type
FROM #tempTransReplication AS t
INNER JOIN #tempPublishedArticles AS p
ON t.publisher_db = p.publisher_db AND t.source_owner = p.source_owner AND t.source_object = p.source_object

UPDATE t
SET rowcount_subscriber = s.rowcount_subscriber
FROM #tempTransReplication AS t
INNER JOIN #tempSubscribedArticles AS s
ON t.subscriber_srv = s.subscriber_srv AND t.subscriber_db = s.subscriber_db AND t.destination_owner = s.destination_owner AND t.destination_object = s.destination_object

UPDATE #tempTransReplication
SET rowcount_diff = ABS(rowcount_publisher - rowcount_subscriber)

Step 5: Display final results

Finally, we can display the final results of our monitoring script. We can filter the results to show only the replicated tables that have a difference in rowcount between the publisher and subscriber(s) side. This will help us identify any issues or discrepancies in the replication process.

-- rowcount result by replicated database
SELECT publisher_srv, publisher_db, subscriber_srv, subscriber_db, sum(rowcount_diff) AS rowcount_diff
FROM #tempTransReplication
WHERE object_type = 'USER_TABLE' -- tables only
GROUP BY publisher_srv, publisher_db, subscriber_srv, subscriber_db
HAVING sum(rowcount_diff) > 0 -- only show those databases which fall behind
ORDER BY rowcount_diff DESC

-- rowcount result by publication
SELECT publisher_srv, publisher_db, publication, subscriber_srv, subscriber_db, sum(rowcount_diff) AS rowcount_diff
FROM #tempTransReplication
WHERE object_type = 'USER_TABLE' -- tables only
GROUP BY publisher_srv, publisher_db, publication, subscriber_srv, subscriber_db
HAVING sum(rowcount_diff) > 0 -- only show those publications which fall behind
ORDER BY rowcount_diff DESC

-- rowcount result by table
SELECT publisher_srv, publisher_db, subscriber_srv, subscriber_db, publication, object_type, (source_owner + '.' + source_object) AS source_objectname, (destination_owner + '.' + destination_object) AS destination_objectname, rowcount_diff AS rowcount_diff
FROM #tempTransReplication
WHERE object_type = 'USER_TABLE' -- tables only
AND rowcount_diff > 0 -- only show those tables which fall behind
ORDER BY rowcount_diff DESC

By following these steps, you can effectively monitor the performance and status of your SQL Server replication setup. This script provides a comprehensive view of your replication environment and helps you identify any discrepancies or issues that may arise. Regularly running this script can ensure that your replication is functioning smoothly and data is being synchronized accurately.

Remember to run this script from the SQL instance where both the publisher and distributor reside. Additionally, make sure to set up linked servers and configure them for “Data Access” in order for the script to work properly.

Happy monitoring!