Post reply

Moving Merge Publisher

  • June 20, 2005 at 5:09 pm

    #87767

    First off, I'm a newbie so be gentle...

    I am looking to move my publisher to a new server and am researching the best method to accomplish this task.

    I have 100+ subscribers over distributed slow links with repl occurring every 15 minutes throughout the day so I need to do this with as little pain as possible.  We are using Win2k3, Sql2k std and have a db of about 200mb. 

    My initial thought was to have my new server be a subscriber of the current publisher and then publish from the new.  'Learning by doing' showed me that this isn't possible. 

    My next thought is to somehow (within the limitations of sql2k) have a parallel setup between new and old and slowly and methodically dump the subscribers from the old box and recreate them on the new (though without clustering capability, I don't think I can do this). 

    Lastly, I could brute force the process and simply dump everyone off the old, shut it down and resubscribe all to the new, though managing this without some data loss would be quite difficult.

    If any of you can provide any insight or recommendations, I would be greatly appreciative!

    Thanks, Chris

     

  • June 21, 2005 at 3:12 am

    #566970

    Recreating from scratch and having all the subscribers resubscribe would definitely work but would be my last resort. If you can create the new server with the same name as the old server, you should be able to set up replication there and then restore distribution, master, publication and msdb databases from the old server and have it work seamlessly. Have a look in BOL (mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\replsql.chm::/replbackup_9v3i.htm) for backup and restore operations involved with replication.

    HTH,

    Paul Ibison, http://www.replicationanswers.com


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • June 21, 2005 at 6:56 am

    #567002

    Thanks Paul.  I am anxiously awaiting the release of Hillary's book on this subject (as shown on your web).

    Unfortunately, the server is already set up and in production running SPS (among other things) so making further adjustments would be lengthy. 

    When I do migrate the subscribers over (under any method) will the local db's have to be blown away and recreated?

    Thanks a lot!

     

  • June 21, 2005 at 9:27 am

    #567095

    The servername is hardcoded into too much metadata to be able to move the publisher from one server to another and have it work, unless the names are identical. In this case I'd synchronize all subscribers, backup the production database and restore on the new server. On the existing production box, script out replication and run this script on the new publisher, having changed the servername and any job owners and the PAL - if they refer to logins not available on the new server. Script out the subscriptions and then drop the publication on the old system and recreate the subscriptions on the new box then initialize. This'll overwrite all data on the subscribers.

    Hilary Cotter is working on the merge volume and I'm sure it'll be another useful contribution to the subject.

    Cheers,

    Paul Ibison (http://www.replicationanswers.com)


    Paul Ibison
    Paul.Ibison@replicationanswers.com

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

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