Hi there,
I wonder if I can ask for some advise I wish to use SQL 2005 and merge replication but I have a few concerns over the schema changes that I am allowed to do on the publisher.
Ideally I want sql replication to push all my schema changes from the publisher to the subscribers but I don't know if it can. or even if it is recommended.
I see the BOL suggesting that schema repl. was mainly intended for ALTER type of statemenets, but can I use it for CREATE and DROP type DDL too
For example, if I add a new table at the publisher I presume I have to add a new article too for this table. Then how can I create a new snapshot Is this even the right way of doing it Similarly if I want to DROP a table at the publisher I'm guessing I have to also delete any existing articles too, then recreate the snapshot.
Are there better / safer ways of doing schema changes Perhaps I'm best disabling publishing and asking each remote site / db to update their schema manually and then re-enabling the publisher
Any help would be much appreciated.
Regards.
Schema changes and Merge replication
paulking123
Hi
Just to clarify when you reinitialise a subscription you are simply reapplying a new snapshot and not dropping the whole publisher/subscriber setup (as you describe in your message)
There's more info here explaining reinitialising subscriptions and why it's done.
http://msdn.microsoft.com/library/default.asp url=/library/en-us/replsql/repltypes_30z7.asp
Also to address the question of best practice you may want to review this section on enhancing merge replication.
http://msdn.microsoft.com/library/default.asp url=/library/en-us/replsql/repltypes_30z7.asp
Many Thanks
Nabila Lacey
This posting is provided "AS IS" with no warranties, and confers no rights.
vishal_UK
Have you read topic "Making Schema Changes on Publication Databases" in Books Online yet
http://msdn2.microsoft.com/en-us/library/ms151870.aspx
donnyG
Thanks for the info Greg.
I have spent a few hours reading the various sections of the BOL and I think I understand it, however, I do have another question.
If I want the subscribers to have the same schema as the publisher Is there an alternative to replicating the schema from the publisher For example, is it feasable to manually upgrade subscribers without having to drop the whole publisher / subscriber setup. My concern is that if I have to drop the publisher / subsciber links then when I come to set them back up it is going to take a very long time to re-initialise the subscribers from a new snapshot
Does this make sense Is there a best practice for maintaining schema between publisher and subscirber where the schema may change 3 or 4 times a year
Regards