asked on SQL 2008 move merge replication - remote distributor
I have a client who's SQL Server is being used as a OLTP database server and a distributor to 42 subscribers using merge replication. Each of the 42 subscribers are running SQL 2008 R2 Express so can only subscribe to a push publication.
The client is looking to purchase a new server and migrate the OLTP database to it and the plan is to use the existing server as a remote distributor (all the data that is replicated resides in the OLTP database). The idea here is to reduce the amount of work the OLTP server is doing. Is it possible to migrate replication without having to set it up from scratch again? The volume of data that needs to be replicated means there isn't a long enough window to reapply new snapshots to the subscribers should this be the case.
Just to recap:
In the currency scenario they have SERVER1. This is the OLTP server and distributor.
In the new scenario they have a new server SERVER2. This will be the OLTP server and SERVER1 will be the remote distributor for replication.
Any advice would be appreciated.
The client is looking to purchase a new server and migrate the OLTP database to it and the plan is to use the existing server as a remote distributor (all the data that is replicated resides in the OLTP database). The idea here is to reduce the amount of work the OLTP server is doing. Is it possible to migrate replication without having to set it up from scratch again? The volume of data that needs to be replicated means there isn't a long enough window to reapply new snapshots to the subscribers should this be the case.
Just to recap:
In the currency scenario they have SERVER1. This is the OLTP server and distributor.
In the new scenario they have a new server SERVER2. This will be the OLTP server and SERVER1 will be the remote distributor for replication.
Any advice would be appreciated.
Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft Development
Last Comment
Russell_Harper
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Yes this is correct. I thought that was the case, looks like there's some long nights ahead
ASKER
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....
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....