SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving distribution database from one server to another with minimal downtime and without breaking...


Moving distribution database from one server to another with minimal downtime and without breaking replication

Author
Message
Feivel
Feivel
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5800 Visits: 1973
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.
Sue_H
Sue_H
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67018 Visits: 13928
ffarouqi - Thursday, February 8, 2018 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.


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



Feivel
Feivel
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5800 Visits: 1973
Sue_H - Thursday, February 8, 2018 2:15 PM
ffarouqi - Thursday, February 8, 2018 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.


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

I am not sure if you understood my question. Article referenced is actually generating scripts to drop and recreate.

Sue_H
Sue_H
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67018 Visits: 13928
ffarouqi - Thursday, February 8, 2018 4:54 PM
Sue_H - Thursday, February 8, 2018 2:15 PM
ffarouqi - Thursday, February 8, 2018 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.


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

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



Ivan R.
Ivan R.
SSC-Addicted
SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)SSC-Addicted (480 reputation)

Group: General Forum Members
Points: 480 Visits: 366
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.
Alexander Zhang
Alexander Zhang
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1071 Visits: 264
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search