Cannot Alter Schema of Replicated Article
Suppose you encounter an error message wherein you have a replication setup and want to change the schema of the article. You will try using Alter Table command on the replicated table and it will fail. The error message will be following:
Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'ColumnName' because it is 'REPLICATED'.
Reason:
The replication metadata has got inconsistent. This error is thrown when the "is_non_sql_subscribed" property of the column is not reverted back to 0 after the snapshot process is completed. Every time snapshot is run, it changes this value to 1 during the time of snapshot generation. After snapshot is complete, this should get reverted back to 0. If this process fails, we start getting the above mentioned error. Few issues have been reported wherein the reason why snapshot is not reverting back is when one article is a part of two or more publications. Such issues have also been reported when there has been publication enabled for Hetero subscribers. But these are not the only reasons for the snapshot problems, there could be many more for these issues.
Steps To Reproduce:
Note:
1. All tests have been done on SQL Server 2005
2. The issue was not tested with Merge Replication
3. The issue was not tested with Heterogeneous Subscribers
1. Create a transactional publication.
2. Check the "is_non_sql_subscribed" column in sys.columns catalog. All the rows will be set to 0.
3. Select one of the columns from one of the replicated articles and try to make a change in its schema (I made change in the data type). It will be successful.
4. Manually change the value of "is_non_sql_subscribed" column in sys.columns from 0 to 1 for the article id for which you have made the change using following steps:
a. Find the publication id using
select * from syspublications
b. Then using pubid from the above output find out the article id on which you wish to make a change (artid):
select * from sysarticles where pubid=<publication id>
c. Use the artid from above output to change the value of is_non_sql_subscribed in sys.columns using following command under DAC * connection
EXEC sys.sp_MSarticlecol @artid=<articleid>, @colid=NULL, @type='nonsqlsub', @operation='add'
This would change the value of "is_non_sql_subscribed" column to 1 for the above article id.
5. Then try reproducing the issue by making a change in the schema of the article which you have selected in above steps and it will fail with same error message.
This means you have successfully reproduced the issue.
Steps to Resolve the Issue:
There are several methods to resolve this issue. You would be the best judge to select one of the methods that suits your business requirement:
1. Since this problem occurs due to some issues while applying snapshot, reapplying the snapshot again will resolve the issue. After applying the snapshot you will be able to see the value of "is_non_sql_subscribed" flip back to 0. And then you will be able to run Alter command on the replicated article.
2. You could try to run [EXEC sys.sp_MSarticlecol @artid=<articleid>, @colid=NULL, @type='nonsqlsub', @operation='drop'] in a DAC * connection. This command will also flip the value of "is_non_sql_subscribed" column to 0.
3. Start SQL Server in a single user mode using –m** switch and update "is_non_sql_subscribed" column manually and set its value to 0 in the sys.columns catalog under a DAC connection.
4. You can also drop*** the replicated articles from publication properties and add them back in the publication after making the DDL changes. This will generate the snapshot for all the articles but the snapshot will be applied only for the article which you have added.
5. The best way to resolve this issue is to run sp_removedbreplication on the database and reset the publication to have clean metadata. I bet not many people would go for this plan J
Generating the snapshot again for the publication (1.) or only for the article in question (4.) would be the most authentic solution which I could recommend as there was some inconsistency in the replication metadata.
More Information:
* We are using DAC (Dedicated Administrator Connection) on number of occasions to make changes to the system catalogs which cannot be achieved through normal connections. For more information on DAC, visit https://msdn.microsoft.com/en-us/library/ms189595.aspx
** To start SQL Server in Single-User Mode, visit https://msdn.microsoft.com/en-us/library/ms188236.aspx
***How to add and drop articles from existing publication, visit https://msdn.microsoft.com/en-us/library/ms152493(SQL.90).aspx
I hope this article will give you relief with the problem reported above.
Sumit Sarabai
SE, Microsoft Sql Server
Reviewed By
Mukesh Nanda
TL,Microsoft Sql Server
Akbar Farishta
TL,Microsoft Sql Server
Anurag Sharma
TL,Microsoft Sql Server