Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Merge replication changing the distributor Expand / Collapse
Author
Message
Posted Monday, September 03, 2012 5:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 9:08 AM
Points: 18, Visits: 230
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.
Post #1353460
Posted Tuesday, November 27, 2012 2:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 9:08 AM
Points: 18, Visits: 230
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....
Post #1389037
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse