Sunday, February 12, 2012

Can not replicate DDL changes

Merge replication. We switched a publication over from push to pull and are now initiating everything within an application. We have just encountered a situation where it is now completely impossible to replicate DDL.

When this was a push subscription, we could execute the following and it would fly straight through the engine and hit every subscriber without having to do anything at all:

ALTER TABLE <tablename>

ADD <columnname> <datatype> NULL

Now that it is a pull subscription when I issue an ALTER TABLE and add a nullable column to the end of the table, it does NOT replicate at ALL. We get the following error message:

The schema definition of the destination table 'dbo'.'Player' in the subscription database does not match the schema definition of the source table in the publication database. Reinitialize the subscription without a snapshot after ensuring that the schema definition of the destination table is the same as the source table. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199478)
Get help: http://help/MSSQL_REPL-2147199478

The process was successfully stopped. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199481)
Get help: http://help/MSSQL_REPL-2147199481

It apears that we are now required to either reinitialize every subscriber every time we add a column or we are required to first distribute the DDL change to each subscriber, make sure they all have it, then add it to the publisher before anyone replicates, and then reinit every single one of them without a snapshot. This makes absolutely no sense at all.

The interesting thing is that we can add articles at will and those get applied with absolutely no problems at all to the subscribers without having to do anything other than add the article and generate a new snapshot.

Version 9.00.3042.00

I am experiencing the same problem. It appears to only occur if there are both data and metadata changes to the replicated table. Has that been your experience?|||

No. We have this problem even if only DDL needs to replicate.

There is a KB artilcle of a bug with DDL replication that is related to kicking synchronization off via Replication Monitor, but that does not apply here either. We are initiating it using RMO from the client and have verified that it is not an RMO issue by directly launching it from the command line using replmerg.exe. We have the latest service pack and hotfixes applies. There is nothing that we can find that narrows this down at this point.

DDL plain and simply does not replicate. We have built this on a test environment using a centrally manage configuration, aka push, and all DDL replicates without any problems. We then dropped the push subscribers off and configured pull subscribers to this same publication and at that point DDL refuses to replicate via any mechanism.

The only way we can get DDL changes shoved down to a subscriber is to do one of the following:

1. Move the DDL change to a new table and add that as a new article

2. Drop the existing article from the publication, have everyone synch to remove it from their machines, apply the DDL change, add the article back in, have everyone synch to get the incremental snapshot and add the table back with the updated schema

3. Reinitialize all of the subscribers

Every one of those options is horrible, for obvious reasons when you are dealing with hundreds of subscribers moving all over the globe.

|||I am using push subscriptions, initializing the sync using RMO. I continue to see the problem when the subscriptions are set to reinitialize. The workaround supplied by the error message works but is impractical.|||We aren't reinitializing ours. They are just blowing up this way. In our case, you can't do what is suggested in the error message, because schema changes are not allowed on the subscriber.|||Is there any official response to this problem?|||Nope. Since we can't even get the support case moving forward, we are taking the alternative approach of blowing away each of the publications, issuing the DDL that we need, putting the publication back together and then reinitializing. We've reverted our application design and deployment to what I've been using for the past decade, prior to SQL Server 2005. I no longer trust the DDL replication component and will not use it. This creates a major bottleneck in our ability to deliver solutions quickly as well as enhance existing solutions, but we are left with no choice.

No comments:

Post a Comment