The strange thing is that there are likely other tables in the same Publication that are properly being replicated to the same Subscriber.
What is happening here? Why is replication ignoring this table?
This is a problem I have run into on a number of occasions and it all boils down to the Subscription on the individual Articles within the Publication.
Wait, we were talking about a table, what do I mean by Articles? I'm glad you asked!
Articles that can be replicated are a number of object types within the database. Tables, Stored Procedures, Indexed Views and User Defined Functions as you see below:
In this case, I am referring to a table, but the problem could affect any of the replicated Articles, so I will refer to Articles for the remainder of this post.
When you first create a Publication, you are defining what Articles to replicate. You then create a Subscription to define what Subscriber you wish to push the data to. When you create the Subscription, under the hood SQL Server actually activates the Subscription on each Article within the publication. You can have multiple Subscriptions on a single Publication to replicate the Articles to multiple Subscribers.
When you add articles to an existing Publication with an existing Subscription, the GUI automatically executes a Stored Procedure (specifically sp_RefreshSubscriptions) to then activate the existing Subscription(s) to the Article you just added.
Usually, this whole process executes without issue and data starts flowing for the new article once a snapshot has been generated and delivered (or immediately, if you used the 'replication support only' option in the Subscription).
Unfortunately, the process to create the Subscription on the Article can sometimes fail. This seems to be more prevalent when the Article you are adding to the Publication is very active and there is a potential of blocking the replication setup processes. Even more unfortunately, it fails silently, so that there is no indication that anything has gone awry. The only symptom is that data does not flow for that Article to the Subscriber.
To find the issue, I run the following script:
CREATE TABLE #PublicationList
(DatabaseName SYSNAME,
PublicationName SYSNAME,
ArticleName SYSNAME)
INSERT INTO #PublicationList
EXEC sp_MSForEachDB
'USE [?]
IF EXISTS (SELECT name FROM sys.tables WHERE name = ''syspublications'')
BEGIN
SELECT DB_NAME(), SP.name, SA.name
FROM dbo.syssubscriptions AS SS
JOIN dbo.sysarticles AS SA ON SA.artid = SS.artid
JOIN dbo.syspublications AS SP ON SP.pubid = SA.pubid
WHERE SS.status = 1
END'
SELECT * FROM #PublicationList
ORDER BY DatabaseName, PublicationName, ArticleName
DROP TABLE #PublicationList
This will show a list of all Articles in all Publications in all databases on the current server that have an inactive subscription.
After confirming the problem with the above script, I use this script to correct the issue:
USE DBName; -- Update database name as appropriate
UPDATE dbo.syssubscriptions
SET status = 2
WHERE status = 1
The script to address the issue only addresses one database at a time. Since you will likely have to manually sync data for the Articles that were not properly replicating, this script allows you to address the out-of-sync problem in smaller, more easily addressable pieces. There are many options to choose from when manually syncing objects and a tool like Redgate's SQL Data Compare may come in handy for that task.
The status values in the syssubscriptions table are as follows:
0 - Inactive - There is a Subscription, but it is not associated with this Article
1 - Subscribed - The Article is associated to the Subscription, but it is not active
2 - Active - The Article is active in the associated Subscription
The above process has helped us resolve this issue on numerous occasions. Hopefully, it helps you if you encounter the same issue!
