Hi there,
I'm using Sql 2005 merge replication and I have noticed something, I'm not sure if this is true or not:...
My publication is set to replicate schema changes (replicate_ddl = 1). Now, I have noticed that schema changes are only replicated if the current snapshot is valid. Is this right? If so why?
My next question carries on from the first. If I'm about to run a TSQL script on my publisher that will add a column or two to a published table, how do I ensure my snapshot is valid inorder for the ddl changes to be replcated? Should I be using:
EXEC sp_mergearticlecolumn
@.publication = <publicationname>,
@.article = <article name>,
@.force_invalidate_snapshot = 1,
@.force_reinit_subscription = 1
on each table I modifiy, after I have added the new column?
Thanks for your help
Graham
As for the first question, we don't have special requirement for snapshot. After DDL on publisher side, you just run merge agent, changes will be propagated to the subscriber.
As for the second question, how to ensure your snapshot is valid, you can just reinitiate subscriptions and regenerate snapshot, although it is not necessary for your ddl changes.
THanks
Yunjing
|||Thanks for the reply Yunjing, but I can say for sure it seems to only replicate DDL in MERGE when the snapshot is current.
If I add a column to a table that has been published then look at the properties for the publication using SQL Management studio I can see the Articles. Drilling in to the articles reveals the new column, but when the snapshot is not current then this new column is not ticked to indicate it will be replicated.
Graham
|||Supposedly, merge agent should auto-detect if regenerating a new valid snapshot is necessary for propogating the ddl changes.|||Hi Graham,
If you are using SQL Server 2005 for the publisher, subscriber, and distributor, then creating a merge publication with a table article that has replicate_ddl enabled should allow you to use the more robust schema change features built into replication. You are now able to use "Alter Table" syntax to physically add/remove columns from tables that are published for replication. There is no dependancy on snapshot to do this and the changes are propagated during the next sync with the subscriber. Check out the "How to: Replicate Schema Changes" topic in SQL Server 2005 Books Online. This is a new feature in SQL 2005.
As for the stored procedure you are executing above, sp_mergearticlecolumn. This stored procedure is used to vertically filter out or add columns to an existing table article. This is done at the logical layer in the article definition, not in the physical table definition. I believe you want to physically add a column to the table, so using Alter Table syntax is appropriate, not sp_mergearticlecolumn. Also, if the table you are publishing does not have any vertical filtering of columns, then when you add the column to the table via Alter Table syntax, the column will automatically get added to your table article definition with no extra effort.
Hope this helps and that I am interpreting the question correctly,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
|||
Thanks Tom,
you have interpreted it correctly. However, what I found is that SQL 2005 does correctly replicate any schema change done at the publisher with ALTER TABLE statement, but...
This is only true if the current snapshot is upto date - I used sp_helpmergepublication and look at the snapshot_ready column.
If however, the snapshot becomes out of date (I forced this by adding a new table, then adding a new merge article for that table before issuing an ALTER TABLE statement), then the merge agent does not replicate the new DDL, it continually says a new snapshot is required.
Does this make sense?
Graham
|||Hi Tom,
I have now isolated the time when ALTER TABLE does NOT replicate schema changes.
I appears the if the publication uses row filtering on any of its articles / tables (and the filter uses HOT_NAME()), then using ALTER TABLE to add a new column does not mark this new column for publication in the existing table article.
Did you know this? If so, is there anyway I can get the new column added to my article for publication without removing the article and re-adding it?
Regards
Graham
|||Hi Graham,
This is surprising.
Double check a couple items. The backward_comp_level should be 90 and replicate_ddl should be 1.
select backward_comp_level, replicate_ddl from sysmergepublications
Though I could not reproduce this issue, and I tried with all four row filtering partitioning options, here is something for you to try. Since the new column seems to be vertically partitioned out of the table article definition, now you should be able to use sp_mergearticlecolumn to add the column back into the table article. This will require the full snapshot to be regenerated because you are making a change to the schema of the pubished table article. This can be done without removing and re-adding the table article to the publication.
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Tom,
the results for the select statement return 90 and 1 respectively.
This is very bizzare. Can I ask you a question please? When you tried it did you do it through script (TSQL) or did you use the Sql Wizards?
If I create the publication and subscription using the wizards then it fails as I describe. If however I use TSQL to create everything it works as you expect and indeed found.
This is all sounding a bit strange!
Graham
|||We did experienced same problems: "alter table" is not propagating the new column to sql server mobile database (and yes, we use hostname on row filtering).
What we see is that it is propragated if we create a new subscription...
strange for sure since it was working fine under SS2K (trough enterprise manager).
Thanks for your help.
Fab
|||Hi guys,
some more info for you. I seem to be able to replicate this without a problem.
Using sp_helpmergearticle @.publication='<my publication>' I can predict if the schema will be replicated.
This sp will return the state of all articles for the given publication. Using the Status column I noted that after a snapshot is taken the status is 2 (Active) for my table. As soon as I add a subscription and initialize it then the status of the articles goes to 1 (Inactive). If an alter table statement is issued on the table when the status is 1, then the new column will NOT be part of the article and hence not replicated.
To fix it, I have to re-generate a new snapshot of the publisher BEFORE any alter table statements. Regenerating the snapshot seems to set the article status back to 2.
Does anyone know why this is happening? What does this Status mean on the articles?
Thanks
Graham
|||Hi Graham!
Did you solve this problem?
It turned out that I've the same problem - the changes become *visible* just after I re-generate new snapshot.
It's very annoying, because if snapshot was not re-generated, we must re-init all subs :(
Paul
|||Hi Paul,
sorry I never solved what was causing this. It became so unreliable that whenever we schema changes we drop replication :( then recreate the subscribers.
Graham
|||I've just independently discovered this problem. It is exactly as Graham describes.
Is there anyone out there from Microsoft with anything to say about this? I'm starting to lose confidence in replication as a solution to anything other than the most trivial of problems.
|||Can someone tell us what scenario you are getting into.
I see it is mentioned that the status of the article turns to 1 after adding a subscription and initializing it. I do not see this happening. What type of subscription is this? Client/Server, push/pull, local/remote, SQL Server 2000/SQL Server 2005?
If someone can give more information, I can try to reproduce this help with the resolution.
No comments:
Post a Comment