Merge replication changing the distributor

  • I have a client who has a single SQL server that is currently their OLTP database server and is handling merge replication to 45 subscribers (SERVER1).

    The client has purchased a new SQL server (SERVER2) which they want to move their OLTP database to and use their existing server as a remote distributor. Just to clarify:

    SERVER1 - Current Role

    OLTP Database Server

    Publisher

    Distributor

    SERVER1 - New Role

    Distributor

    SERVER2 - Current Role

    N/A

    SERVER2 - New Role

    OLTP Database Server

    Publisher

    Is it possible to move replication without having to set it up again from scratch? Each of the 45 subscribers are connected via VPN and will need to receive approx 3-4GB data as initial snapshots if this has to be setup again from scratch. Any shortcuts or tips would be appreciated.

  • I found a way around this - I had a window of downtime where nothing was happening on any of the servers.

    First I setup the new server as a publisher setting the old server as the distributor. I then ran the following statement on the publication database and at each of the subscriber databases:

    UPDATE sysmergearticles SET preserve_rowguidcol = 1

    This means that when you drop replication the rowguid column that's added when replication is setup remains on the table. I then scripted all the publications by right clicking on the publication and selecting "Generate Scripts". Bear in mind that the passwords for the snapshot agent, agent process account and subscriber connection are not scripted and will need to be added in manually. Also if for any reason you've created your own job schedule removing the default one by editing the SQL Server Agent schedule directly it will not script correctly.

    Once scripted I modified each of the sp_addmergesubscription steps so that @sync_type = N'None'. This tells the publication that the subscriber already has the snapshot and there's no need to sync. I also replaced any reference to the old publisher name with the new one.

    I then dropped the subscriptions at each of the subscribers using the following:

    EXEC sp_dropmergesubscription @publication = N'Publication Name', @subscriber = N'Subscriber Server Name', @subscriber_db = N'Subscriber Database'

    I then deleted the publications from the current publisher. The database was then backup up and copied to the new server and restored. I then ran the scripted publications on the new server then went through each publication and ran a synchronisation. I managed to do this in a 8 hour window with 5 publications referencing 66 articles to 50 subscribers. Better than the 3 day job night shift job I was planning on....

Viewing 2 posts - 1 through 1 (of 1 total)

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