February 7, 2012 at 10:06 pm
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.
February 7, 2012 at 10:31 pm
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/
February 7, 2012 at 11:54 pm
Thanks for the Reply.
Is there any way that this can be achieved from SQL Server Management Studio?
February 13, 2012 at 12:18 am
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.
June 5, 2013 at 8:12 am
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.
June 5, 2013 at 8:08 pm
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.
June 5, 2013 at 8:19 pm
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.
June 6, 2013 at 7:20 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply