Wednesday, August 19, 2015

How to Reinitialize Single Article in Publication

Testing environment: SQL 2014 Developer Edition

Recently, I had a task to enable copy of non-clustered indexes in transactional replication. It is an option “Copy nonclustered indexes” in Properties of Article of publication. Screen shot of this property is in figure 1.

Figure 1

While scripting it out for all articles using sp_changearticle, I noticed two parameters, @force_invalidate_snapshot and @force_reinit_subscription, that would be of great help with another issue we have been facing. We have been facing issue with a couple of articles in a huge publication that would go out of sync for one reason or another about thrice a month. Validation checks on the publisher and subscribers found that subscriber and publisher row counts did not match. We had to re-snapshot the entire publication and reinitialize the subscriber that took over 3 hours. During the time subscriber was being reinitialized, reporting from subscriber was not reliable/available.. effectively reporting outage.
Figure 1 is a screen shot of a test database “AdventureWorksRepl” that is similar to the real issue.

Figure 2

Highlighted text in Figure 3 shows that row count of TransactionHistory table in publisher and subscriber do not match.

Figure 3

First step to resolve this issue is to get name of the article from sysarticles table of publisher database using following query. Result of this query is shown in figure 4. You can also get Article Name from Article Properties window as in Figure 1.


-- Get name of the article from sysarticles table in Publisher database.

SELECT name FROM dbo.sysarticles WHERE dest_table ='TransactionHistory'


Figure 4

Use the name in below query to reinitialize and re-snapshot single article. Notes of this query are self-explanatory.

Note: Run this query in publisher database
This query will reinitialize one article of publication
Change of schema option is just a way to have this command work. Note that schema_option is not changed.
The same value is re-entered. This could be handy if just one article in a publication is out of sync and you do not want to reinitialize all articles.
Publication in this example is AdventureWorksRepl and article that is out of sync is TransactionHistory.
DECLARE @publication AS sysname;
DECLARE @article AS sysname;
DECLARE @option AS int;

SET @publication = N'AdventureWorksRepl';

SET @article = N'TransactionHistory';

-- Get current Schema_Option for the article

SELECT @option = CAST(schema_option AS int) FROM dbo.sysarticles WHERE name = @article;

-- Change the schema options to replicate schema with XML.
EXEC sp_changearticle
  @publication = @publication,
  @article = @article,
  @property = N'schema_option',
  @value = @option,
  @force_invalidate_snapshot = 1,--Invalidates old snapshot
  @force_reinit_subscription = 1;-- Marks subscribers for reinitialization
--Start snapshot job
EXEC sp_startpublication_snapshot @publication = @publication


Result of query execution is shown in Figure 5.

Figure 5

Figure 6 shows the history of Snapshot agent from Replication monitor. It took snapshot of only article TransactionHistory.

Figure 6

Figure 7 shows details of distributor to subscriber actions. You can see that it delivered snapshot of one article to subscriber.

Figure 7

Running validation again, now the row counts of publisher and subscriber match as in Figure 8.

Figure 8


Don’t like T-SQL, here is the GUI Solution:

In Publication properties, select the article that is out of sync and click “Set Properties of Highlighted Table Article” in the drop down of Article Properties as in Figure 9.

Figure 9

Then, change any one property of the article. Figure 10 shows that Copy foreign key constraints has been changed from false to true. Note that you could change any property. Click OK on Article Properties window and then OK on Publication Properties. Then, go back into Publication Properties and Article Properties the same way and switch that option back to what it was initially. We are just trying to make a change and revert the change so SQL thinks we made a change and marks the article for reinitialization.

Figure 10

Once you have reverted the change and saved, go to Snapshot Agent of the publication and run that as in Figure 11. You can check snapshot action history and details of distributor to subscriber transactions to verify that Snapshot agent took snapshot of that one article you made changes to and also delivered that to subscriber. This is shown in figures 6, 7, and 8.

Figure 11

Alternatively, you could just remove the article from publication and save it. Then, go back in and re-add the article into publication and start snapshot agent. You would lose any existing custom setting on the article in this case unless you note them down.

Note: If "immediate_sync"  is true for a publication, snapshot agent will create snapshot of all articles every time the agent runs. However, it will deliver snapshots for only required articles to required subscribers.

This method saved me from having to snapshot entire publication. Although we do not have complete resolution of the issue I am facing but, I have an easy and short workaround.