February 8, 2018 at 12:38 pm
I would like to know if there is a possibility or a hack that can be used to move the distribution database from one server to another without breaking/dropping replication. I've read through many blog posts and they all show a methodology to break replication and recreate it. I would like to do a quick switch so need help in getting it to as minimum as possible.
February 8, 2018 at 2:15 pm
ffarouqi - Thursday, February 8, 2018 12:38 PMI would like to know if there is a possibility or a hack that can be used to move the distribution database from one server to another without breaking/dropping replication. I've read through many blog posts and they all show a methodology to break replication and recreate it. I would like to do a quick switch so need help in getting it to as minimum as possible.
There are several articles on how to do that without recreating or reinitializing. One of the issues is that it can be a bit different for the different setups. You need to test them in your non-production environment. Here is one that seems to address most things:
SQL Server Transactional Replication Moving Distribution Database – step-by-step guide
Sue
February 8, 2018 at 4:54 pm
Sue_H - Thursday, February 8, 2018 2:15 PMffarouqi - Thursday, February 8, 2018 12:38 PMI would like to know if there is a possibility or a hack that can be used to move the distribution database from one server to another without breaking/dropping replication. I've read through many blog posts and they all show a methodology to break replication and recreate it. I would like to do a quick switch so need help in getting it to as minimum as possible.There are several articles on how to do that without recreating or reinitializing. One of the issues is that it can be a bit different for the different setups. You need to test them in your non-production environment. Here is one that seems to address most things:
SQL Server Transactional Replication Moving Distribution Database – step-by-step guideSue
I am not sure if you understood my question. Article referenced is actually generating scripts to drop and recreate.
February 8, 2018 at 6:54 pm
ffarouqi - Thursday, February 8, 2018 4:54 PMSue_H - Thursday, February 8, 2018 2:15 PMffarouqi - Thursday, February 8, 2018 12:38 PMI would like to know if there is a possibility or a hack that can be used to move the distribution database from one server to another without breaking/dropping replication. I've read through many blog posts and they all show a methodology to break replication and recreate it. I would like to do a quick switch so need help in getting it to as minimum as possible.There are several articles on how to do that without recreating or reinitializing. One of the issues is that it can be a bit different for the different setups. You need to test them in your non-production environment. Here is one that seems to address most things:
SQL Server Transactional Replication Moving Distribution Database – step-by-step guideSue
I am not sure if you understood my question. Article referenced is actually generating scripts to drop and recreate.
It's as quick as it's going to get - that's the point as they could not reinitialize so it would be quicker. The distributor has information in msdb and master, not just the distribution database. So you are really just scripting out properties more than anything else for the distribution database. The metadata needs to be moved as well. Jobs, replication agents, configuration options all needed. When you enable the instance for publishing it creates a lot of information in different databases. It's not like you can detach the distribution database and attach it on the another server. It's not like moving a user database.
Sue
February 8, 2018 at 8:17 pm
Have you considered log shipping by restoring the DB to the new server with NORECOVERY and then shipping the logs there? I imagine you then have to agree on a cutoff period then you stop all transactions then backup and restore the last tlog and online the DB.
You can read-only the old db so you can be assured no new transaction goes in.
February 8, 2018 at 11:18 pm
I'd ask why you need to do this. Two or three years ago, I was asked to do it because the hardware is out of warranty. Since there are lots of stuffs there including distribution and minimum downtime is needed. I did the following thing.
1. install MSSQL in new replacement and keep everything the same, such as SQL version, installation path, components, etc.
2. Stop MSSQL in old server
3. Copy data&log files to new server. Of course, in the same locations.
4. Rename old server to something else
5. Change new server name
6. Start MSSQL in new server
If you face the similar situation, you can have a try with your own risk.
GASQL.com - Focus on Database and Cloud
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply