Replication: any way to change table schema w/o reinitializing subscriptions -

Hi all,

I'm admitedly a bit new to the world of replication, so please bear with me. I've got two SQL Server 2000 servers running in different locations. Server A does transactional replication over a push subscription to server B. If I need to make a minor change to one of the replicated tables (for example, dropping a no longer used column or changing a varchar field's length) do I need to drop the subscription, make the changes and then re-initialize the schemas and data?

For minor changes, I really hate having to knock out the site runnign off server B while the subscription is re-initialized and data is bulk copied back over. If I want to just make the changes manually on both servers will that cause problems down the line?

Any help is greatly appreciated.

tia.

-mYes, there are a number of ways to do this (run schizoid schemas), but they can be dangerous. Tampering with a subscriber schema will often force you to reinit all of the subscriptions to the affected tables.

Without knowing exactly what your configuration is, I'd only be guessing. With replication, guessing can be dangerous!

-PatP|||http://www.databasejournal.com/features/mssql/article.php/1477441 for information on replication for schema changes.

In SQL Server 7.0, if you have to change the schema of a replicated table, first you have to unsubscribe to all the publications, which are based on this table. Then, delete all the publications. Do the schema change. Republish the table and resubscribe. Whereas in SQL 2K its not required you can perform schema changes without re-defining replication.