Schema changes in a Merge Replication
-
Forgive me if this question has been asked before, and summarily answered (I'm relatively new to SQL server's replication functions)...
Anyway, for one of our clients, it's been determined that Merge Replication offers the best way to pass data back-n-forth between our main server and several Tablet PCs. But in the future, if a new column or two needs to be added, or existing ones changed or dropped, is it a big deal to make changes?
I've noticed that Enterprise Manager won't simply let me add a new column to a published table, since it's part of a replication. What little info I've found on the Internet, though describes schema changes as a rather unpleasant, though necessary evil. The steps are: drop all subscriptions, remove the publication(s), make changes to the required tables, re-publish and then add subscriptions.
Am I making a mounting out of a molehill, or am I in fact facing a mountain?
-
First of all I have to agree, schema changes in a replicated table can be very unpleasant and under SQL 7.0 dropping all subscriptions, remove the publication and when change the schema is the only option. However if you use SQL2K there are a couple of stored proceudres which allow you some schema changes. Look for sp_repladdcolumn and sp_repldropcolumn. Unfortunately there's nor procedure to change existing columns so if you need to change the Nullabilty of a column you still need to drop the whole replication.
M
[font="Verdana"]Markus Bohse[/font]
-
Thanks, MarkusB. I should have mentioned that we're using MSDE at the moment. But if memory serves correctly, that procedure should be available to work with MSDE.
The client may well upgrade to SQL for the main server in the future anyway; however, the Tablet PCs will keep using MSDE.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply