Remove a column in an article from Replication without reinitializing the replication

Question

Remove a column in an article from Replication without reinitializing the replication

KrishDBA

Right there with Babe

Points: 715
More actions
February 7, 2012 at 10:06 pm

#391502

Hi,
I have a Publication with 10 huge tables. I need to remove one column (for security reasons) from an article. when i tried this it is asking to re-initialize the publication and did so. Since the other tables are huge it took 5+ hours to complete the snapshot.
Is there a simpler way to run the snapshot agent for a single article???
Thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I had to do it once, and I changed the insert and update procedures that the replication created in the target database. In the procedures instead of using the value of the column that was not suppose to be replicated I used null. I have to admit that this is not a very good way because next time that there is a modification on the table's schema the procedures will be created again. Another problem is that the values can be seen by profiler (or sniffer), but it did allow me not to reinitialize the replication of a huge table. I'll also be glad to hear about a better way.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

KrishDBA Right there with Babe Points: 715 More actions · Answer 2

Thanks for the Reply.

Is there any way that this can be achieved from SQL Server Management Studio?

Brandon J Williams Say Hey Kid Points: 699 More actions · Answer 3

To drop a column from an existing publication you'll need to use ALTER TABLE <Table> DROP <Column> syntax at the publisher. By default the schema change will be propogated to subscribers on the next synchronization, publication property @replicate_ddl must be set to true. Reinitialization is not required.

There are some considerations to make which can be found in Making Schema Changes on Publication Databases.

brad.corbin SSC-Addicted Points: 405 More actions · Answer 4

This is an older question, but I wanted to add a reply, I don't think the above answers are what you are looking for.

To remove a column from replication without doing a snapshot of the entire publication, remove the article from the subscription, then add it back in, checking only the appropriate columns.

Then run the snapshot job (without re-initializing the entire subscription), and it should push a new snapshot copy of just that one article.

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 5

brad.corbin (6/5/2013)
This is an older question, but I wanted to add a reply, I don't think the above answers are what you are looking for.
To remove a column from replication without doing a snapshot of the entire publication, remove the article from the subscription, then add it back in, checking only the appropriate columns.
Then run the snapshot job (without re-initializing the entire subscription), and it should push a new snapshot copy of just that one article.

Brad,

Can't we run ALTER TABLE...DROP COLUMN to remove the column from the table?

Could you please explain how to run the snapshot job without re-initializing the entire subscription? Because everytime I run the snapshot job manually, it creates a snapshot of alll the articles.

Thanks,

Sunny.

Brandon J Williams Say Hey Kid Points: 699 More actions · Answer 6

You do not need to remove an article to drop a column. Dropping a column does not require reinitialization either. You do not need to generate a new snapshot and you do not need to reinitialize subscribers. Make sure publication property @replicate_ddl is set to true.

Per Make Schema Changes on Publication Databases in BOL:

To drop a column from an existing publication and drop the column from the table at the Publisher, execute ALTER TABLE <Table> DROP <Column>. By default, the column is then dropped from the table at all Subscribers.

brad.corbin SSC-Addicted Points: 405 More actions · Answer 7

The way I read the original question, the OP doesn't want to drop the column from the table at the publisher, they simply want to stop a specific column from being replicated to the subscriber.

Yes, ALTER TABLE would work if they did truly want to remove the column from the table at both the publisher and subscriber.