Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: 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.

    -m

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, 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

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    http://www.databasejournal.com/featu...le.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.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •