Best way to drop/create a replicated table

Question

I have SQL Server Replication set up to replicate several tables for an accounting application. It's using transaction replication with an initial snapshot. There around about 100 subscribers.

I am trying to figure out the best way to update replicated tables. Right now, when we do an update the end user has to run a utility that upgrades tables by moving data to a temp table, dropping the table, then recreating the table based on the new table definition. The problem is replicated tables cannot be dropped.

So far I've researched and tried a few things. From what i've found out in order to drop/create a replicated table the Article has to be dropped, but it can only be dropped if all subscriptions are dropped. That means in order to upgrade a table I'll have to drop all articles and subscriptions, and then totally recreate the publication.

This is a pain, because this will require quite a bit of refactoring. Also the primary concern is the time it will take to re-do the replication to all 100 subscribers. So i was wondering if there's a better way to handle dropping/creating replicated tables.

Thanks, Makolyte

Answer 1

I'd question why you're replicating a table that's changing schema so frequently. That said, you have a couple of options.

1. Make your table changes in such a way as they're compatible with replication. See Making Schema Changes on Publication Databases.
2. If that's not possible, drop the subscribers from the article with sp_dropsubscription and drop the article with sp_droparticle. Make your table changes, re-add the article with sp_addarticle, re-add the subscribers with sp_addsubscription, and run the snapshot agent to create a snapshot of the newly added article.