Post reply

If very large databases are out of sync, how to resync?

  • February 17, 2009 at 2:10 am

    #383947

    Hi

    100GB database, set up without initialization of data (100GB database copied to each server and replication set up afterwards).

    Now... hypothetically, if replication fails, subscriptions expire, or anything else happens which might cause the databases to be out of sync - what is the preferred option for restoring the synchronization?

    For smaller databases I use RedGate SQL Data Compare (the greatest thing since Joy Division), however for very large databases, it simply isn't practical due to the volume of data (tables with tens of million of rows, etc)

    So what's the recommended approach?

    Brian

  • February 17, 2009 at 1:54 pm

    #945479

    Brian,

    It's possible to initiate replication without doing the initial snapshot. This is much easier to do in 2005, but is possible in 2000 by restoring the the subscriber from a backup. There are articles out there on how to do this, here's one http://www.sqlmag.com/Articles/ArticleID/20005/20005.html?Ad=1 With a large database, this is really the only practical way to do it.

    The best thing is not to let your subscriber get out of sync - if possible.

    visit my SQL Server Google search

  • February 18, 2009 at 6:10 am

    #945839

    Hi

    Thanks. I knew I could do that but I always regard it as a last resort because the production database is on a site which is not ours, and the subscriber database is on one of our own LANs.

    If it's the only option, then I'm better doing RedGate table-by-table for the most critical tables.

    Unless anyone else has any other ideas?

    Brian

  • February 18, 2009 at 8:14 am

    #945969

    The only other option I see is Bulk Copy.. And that is nasty as well.. 🙂

    -Roy

  • February 18, 2009 at 9:29 am

    #946070

    There's no automatic way to sync data up. You could use BCP or SSIS to look for things that might be out of sync and move them, but I'm not sure it would be better than Data Compare, and might not be accurate.

    If you drop the link, replication will Q and pick up later, so that's pretty solid. If it's really broken, backup and restore is the best way to restart replication from scratch

  • February 19, 2009 at 3:40 am

    #946542

    Is there even a way to get a list of tables which are different, with maybe some additional information about the difference? Or even just a list of tables?

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

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