Post reply

Need to alter replicated tables. best pratice?

  • July 4, 2011 at 12:31 pm

    #99403

    Hi,

    I have a merge replication between 4 SQL 2000 servers (1 publisher/distributor and 3 subscribers) geographically located on 3 differents sites. I need to alter a few tables, but I can't since there is a replication. Now, there is production 24/24 in all the sites, so data are always inserted/updated in the database. What is the best practice to drop the replication, modify the design of some tables and restart the replication, without loosing any data and to make sure all data are replicated to the other servers?

    If a modify the tables structures, do I really need to apply a new snapshot on the subscribers? By applying a snapshot, all data will be lost?

    What is the best way to do that?

    thanks a lot for your time and help

  • July 5, 2011 at 5:03 am

    #1348177

    Hi,

    Well, unless you can get some downtime, I'm not sure you are going avoid losing some records..but i could be wrong.

    You will need to remove the table from the publication and then apply the schema changes and republish. Then push out a new snapshot of those tables. You can check which site has the most records and use that as the republished data.

    But unless you stop data updates...it's going to be tricky to avoid data loss.

    Can you temporarily direct the remote users to the publisher so updating is only happening in one place ?

    How big are the tables ?

    Graeme

  • July 5, 2011 at 8:56 am

    #1348332

    thanks for your reply.

    I talked with the client yesterday and we came up with a plan. I will stop the replication and the client give me a window where production will also be stop at the subscribers sites, but not at the publisher's (the main production plant). I apply all the changes to the "master" database and restart the replication. This way, all data from the publisher will be replicated to the subscribers and no data will be lost, am I right?

    By stopping the replication, do I have to completly delete it and recreate it, or there is a "stop/start" function that will let me modify the design of some tables?

    thanks!

  • July 6, 2011 at 8:02 am

    #1348990

    You'll want to stop replication - stop the log reader agent job.

    Then remove the tables, the ones that you need to alter, from replication.

    Make your table altercations on the publisher.

    Then re-add the tables to your replication.

    Start the snapshot agent job - it should only need to resnapshot the altered tables.

    Then restart the log reader agent job.

    The only downtime on the publisher should be while you're altering the tables.

  • July 6, 2011 at 11:03 am

    #1349148

    ok, l'Ill try that!

    thanks a lot for your help!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply