Why Can't I Reinitialize My Subscriber?

I recently had a colleague request help with a replication problem he was having with a client.  The client was running transactional replication from a SQL Server 2005 Publisher to multiple SQL Server 2005 Subscribers and was having issues after attempting to add several articles (tables) to an existing publication.

--

The initial error reported on multiple subscribers looked like this in Replication Monitor:

“Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber.”

To me this said that some of the article changes that had been made during the publication change hadn’t been properly sent to the subscribers, so I decided to reinitialize all subscriptions:

(Another colleague pointed out that I could have reinitialized only the broken subscriptions one-by-one, but in this case a majority of the subscriptions were broken and the publication was relatively small.)

--

Three of the four subscriptions failed re-initialization with two different errors, one with a object reference error and the other two with a replication error.

--

The first subscription, on SubServer1, failed re-initialization with the following error:

Cannot DROP TABLE 'dbo.Table99' because it is being referenced by object 'View1'.

I found that the referenced view was created with SCHEMABINDING, which was preventing the related table from being dropped and recreated.  The view didn’t have any explicit permissions on it, so I scripted the view, dropped it, re-ran initialization for that subscription, which succeeded, and then recreated the view (again with SCHEMABINDING as it was originally).

--

SubServer2 and SubServer3 had a different but actually related issue:

In both of these cases, the re-initialization was failing because a table on the subscriber couldn’t drop a table being used in replication.  What this essentially means is:

In Publication #1, PubServer1 is publishing to SubServer2

In Publication #2, SubServer2 is publishing one or more of the tables from Publication #1 to some other server or servers.

In essence, SubServer2 is both a subscriber to Publication #1 and a publisher of Publication #2 (often called a “re-publisher”).  This results in the error seen above, because part of the default options for a re-initialization of a subscription is to drop and recreate the tables, and when a table is an article in a publication (in this case Publication #2 above) it cannot be dropped to reinitialize the subscription to Publication #1.

For information on re-publishing and the order of steps required to use it, see the TechNet article here and the blog post here.  There are many limitations on both the publications and the subscriptions involved in such an arrangement.

The fix to this is alter the properties of the offending articles, or all articles, in the publication (in this case the change was made to all articles, but it could have been made one-by-one as well:

 

Changing article properties in this fashion automatically marks all subscriptions for re-initialization (assuming you don’t cancel out of the above dialog box).  Your options are to accept the default as shown, in which case you need to manually run the snapshot agent job on the Distributor before the re-initialization will occur, or to check the “Generate the new snapshot now” box which will reach out to the Distributor and immediately  fire the snapshot agent job, beginning the re-initialization process right away.  

The decision on what to do here should be based on how critical the replication is to your infrastructure (if you need it fixed *now* you may have no alternative but to generate it right away) and how large the publication itself is (a large publication can be time- and resource-intensive to generate and transfer a snapshot during business hours).

In this case based on the tables included in the publication it was possible to “Generate the new snapshot now” because of its relatively small size.  This re-initialization completed successfully on all subscribers with the “Delete Data” option configured.

--

The final item here is that my notes above about the SCHEMABINDING view would not be completely relevant had I first discovered the republishing situation.  The SCHEMABINDING view broke that single subscriber because the SCHEMABINDING prevented the table from being dropped, but had the article's property been set to “Delete Data” rather than “Drop and Recreate” the SCHEMABINDING issue would have been handled without dropping and recreating the view.

--

Hope this helps!