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
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 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
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 6594
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
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 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
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 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
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1073 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
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 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
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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