SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
KrishDBA
KrishDBA
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 80
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.
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3725 Visits: 6512
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
KrishDBA
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 80
Thanks for the Reply.

Is there any way that this can be achieved from SQL Server Management Studio?
Brandon J Williams
Brandon J Williams
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 930
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
brad.corbin
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 216
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
sunny.tjk
SSC-Addicted
SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)SSC-Addicted (497 reputation)

Group: General Forum Members
Points: 497 Visits: 1344
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
Brandon J Williams
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 930
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
brad.corbin
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 216
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search