January 19, 2011 at 11:18 am
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.
January 19, 2011 at 3:21 pm
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“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 19, 2011 at 4:55 pm
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'. 🙂
January 20, 2011 at 7:44 am
Out of curiosity, what are you using as a workaround?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 20, 2011 at 9:15 am
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.
January 20, 2011 at 11:57 am
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. 🙂
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“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 25, 2011 at 1:54 pm
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.
August 25, 2011 at 1:58 pm
The glowing yellow box around the text you quoted gets your attention doesn't it. 🙂 Thanks Greg - appreciate the feedback on this.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 14, 2014 at 2:00 pm
I know this is an old thread, but I have a related issue and hope that somebody can comment. I noticed that when I ALTER a table column on a publisher (for example change VARCHAR(100) to VARCHAR(200)), the change gets propagated to the subscriber even though the "replicate_ddl" setting if set to "false" (I checked both from the GUI and with T-SQL).
Is this expected behavior?
Thank you!
March 14, 2014 at 2:05 pm
Read the last post on this thread.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 14, 2014 at 2:13 pm
Thank you, David!
March 14, 2014 at 2:23 pm
A better answer from this technet article;
[ @replicate_ddl = ] replicate_ddl
Indicates if schema replication is supported for the publication. replicate_ddl is int, with a default of 1 for SQL Server Publishers and 0 for non-SQL Server Publishers. 1 indicates that data definition language (DDL) statements executed at the publisher are replicated, and 0 indicates that DDL statements are not replicated. Schema replication is not supported for Oracle Publishers. For more information, see Make Schema Changes on Publication Databases.
The @replicate_ddl parameter is honored when a DDL statement adds a column.
The @replicate_ddl parameter is ignored when a DDL statement alters or drops a column for the following reasons.
When a column is dropped, sysarticlecolumns must be updated to prevent new DML statements from including the dropped column which would cause the distribution agent to fail. The @replicate_ddl parameter is ignored because replication must always replicate the schema change. When a column is altered, the source data type or nullability might have changed, causing DML statements to contain a value that may not be compatible with the table at the subscriber. Such DML statements might cause distribution agent to fail. The @replicate_ddl parameter is ignored because replication must always replicate the schema change.
When a DDL statement adds a new column, sysarticlecolumns does not include the new column. DML statements will not try to replicate data for the new column. The parameter is honored because either replicating or not replicating the DDL is acceptable.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 14, 2014 at 10:25 pm
Wow, thank you, David, for such detailed information! I really appreciate it and I am sure others will as well.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply