Replication: Disable Schema Change Replication

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Replication: Disable Schema Change...

Replication: Disable Schema Change Replication

Rate Topic

Display Mode

Topic Options

Author

Message

holidasa

Posted Wednesday, January 19, 2011 11:18 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 17, 2013 10:11 AM
Points: 120, Visits: 209

SQL Server 2008 R2 (v10.50.1746)
Transactional Replication (non-updatable subscribers)

When I execute sp_changepublication and set replicate_ddl to 0 at the publisher, an ALTER TABLE [x] DROP COLUMN [y] action is replicated to the subscriber.

/* Example: */
    EXEC sp_changepublication 
    @publication = N'my_publication_name', 
    @property = N'replicate_ddl', 
    @value = 0;

/* Execution Output:
   The publication was updated successfully.
*/

Interestingly, if I add a new column to the same table (as a test), the new column is not replicated to the subscriber.
I have not found any documentation suggesting dropped column actions are always replicated. In fact, I can only find information stating with replicate_ddl disabled, statements which add or drop columns are not replicated.

I verified in SSMS the Subscription Option "Replicate Schema Changes" has a value of "False".
When I use SSMS to script the publication, the @replicate_ddl parameter is 0 for the sp_addpublication stored procedure.

I've tried a new snapshot as well as stopping/starting the log reader and distribution agents.
The table is not vertically partitioned.

Is anyone aware of Transactional Publication/Subscriptions always replicating drop column statements even when you have replicate_ddl set to 0?

Obviously, I can work around it by removing the article from the publication, doing what I want at the publisher, and subscriber and re-add the article to the publication but I would prefer to avoid "seemingly unnecessary" steps.

Post #1050243

David Benoit

Posted Wednesday, January 19, 2011 3:21 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, June 14, 2013 10:04 AM
Points: 2,063, Visits: 3,447

Hmmm, that is interesting. I can't think of why that would happen but I will try to test it tomorrow sometime and see if I can come up with anything. Out of curiosity, have you tried, changing "Replicate Schema Changes" to False with the GUI and then testing it. I'm wondering if some of the metadata is not being updated correctly. Based on your view of the publication properties and the script that doesn't sound likely but still want to check.

Also, what do you show with the following query;

select replicate_ddl from syspublications where name = 'YourPub'

Obviously I'm grasping at a few straws here... :)

David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Post #1050418

holidasa

Posted Wednesday, January 19, 2011 4:55 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 17, 2013 10:11 AM
Points: 120, Visits: 209

I tried setting it to false via the GUI and experienced the same results--dropping a column at the publisher replicates to the subscriber.

When I executed the query against the publication database's syspublications table, the value for replicate_ddl is, indeed, 0.

This isn't a big issue. I am working around it and rather prefer the workaround since it is a bit cleaner and easier to read--even if there are more steps. I do appreciate your comments on this topic. I'll probably right this off as, 'hmmm, oh well'. :)

Post #1050437

David Benoit

Posted Thursday, January 20, 2011 7:44 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, June 14, 2013 10:04 AM
Points: 2,063, Visits: 3,447

Out of curiosity, what are you using as a workaround?

David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Post #1050805

holidasa

Posted Thursday, January 20, 2011 9:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 17, 2013 10:11 AM
Points: 120, Visits: 209

In short, I am dropping the subscription, removing the article from the publication, dropping the column, performing any DML statements, adding the article back to the publication, and recreating the subscription with no sync (@sync_type = N'replication support only').

The reason I am doing this is because I have multiple publishers replicating to the same subscription database to roll up data from multiple sources. If I were to have DDL changes replicated, the changes at the first publisher would succeed. However, the remaining publishers would fail when replicating the DDL changes citing 'invalid column name "my_dropped_column"' or if adding a column, it would throw and error citing 'column names must be unique'.

Granted, I could create an Agent Profile to ignore these errors but that just seems a bit haphazard.

Post #1050887

David Benoit

Posted Thursday, January 20, 2011 11:57 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, June 14, 2013 10:04 AM
Points: 2,063, Visits: 3,447

The only variation I can think of with that would be to make the ddl change on one of the publishers and allow it to be replicated. Then delete the other msrepl_command / msrepl_transaction records associated with the other publishers. So the first one would make the change on the subscriber and when you executed the change on the other publishers you would be deleting the command / transaction from the distributor that would be an error on the subscriber. You can pull the sequence number for the error from Replication Monitor so that you are only deleting the command / transaction that is associated with that change. A bit less work than dropping subscriptions and adding them back at the end. Of course, that is opinion. Smile

One note though, if other statements come through in that transaction you could be deleting those as you could potentially have multiple statements in the transaction. Then again, using the method you described you would have to stop all activity to that table to avoid lost dml changes while the subscriptions are removed as well. Neither is perfect.

David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Post #1051082

Greg Shinder

Posted Thursday, August 25, 2011 1:54 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 41, Visits: 272

It's been a long time since the first post but still... to make clear all the doubts:

Note
ALTER TABLE DROP COLUMN is always replicated to all subscriptions whose partition contains the columns being dropped, regardless of the value of the @replicate_ddl parameter.

http://msdn.microsoft.com/en-us/library/ms147331.aspx

Post #1165718

David Benoit

Posted Thursday, August 25, 2011 1:58 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, June 14, 2013 10:04 AM
Points: 2,063, Visits: 3,447

The glowing yellow box around the text you quoted gets your attention doesn't it. :) Thanks Greg - appreciate the feedback on this.

David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Post #1165721

« Prev Topic | Next Topic »

Permissions