Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remove a column in an article from Replication without reinitializing the replication Expand / Collapse
Author
Message
Posted Tuesday, February 7, 2012 10:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 28, 2013 1:54 AM
Points: 37, 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.

Post #1248700
Posted Tuesday, February 7, 2012 10:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 5:28 AM
Points: 2,114, Visits: 5,502
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/
Post #1248706
Posted Tuesday, February 7, 2012 11:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 28, 2013 1:54 AM
Points: 37, Visits: 80
Thanks for the Reply.

Is there any way that this can be achieved from SQL Server Management Studio?
Post #1248726
Posted Monday, February 13, 2012 12:18 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 36, Visits: 770
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.
Post #1251008
Posted Wednesday, June 5, 2013 8:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 3, 2014 10:16 AM
Points: 13, 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.
Post #1460247
Posted Wednesday, June 5, 2013 8:08 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:04 AM
Points: 299, Visits: 1,122
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.
Post #1460506
Posted Wednesday, June 5, 2013 8:19 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 36, Visits: 770
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.
Post #1460507
Posted Thursday, June 6, 2013 7:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 3, 2014 10:16 AM
Points: 13, 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.
Post #1460680
Posted Wednesday, December 4, 2013 3:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 9:02 PM
Points: 3, Visits: 3
Adi Cohn-120898 (2/7/2012)
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

good idea, i'll try that too


شركه تنظيف منازل بالرياض تنظيف شقق شركة تنظيف مكافحة البق مكافحة البق المنزلي
مبيد حشرى للصراصيرشركات مكافحة البق في جدةمكافحة النمل الابيض في المنزل
Post #1519822
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse