Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL Server 2008 transactional replication setup as follows:

  • 192.168.100.1 <-- Distributor
  • 192.168.100.2 <-- Publisher
  • 192.168.100.3 <-- Subscriber

Our network team want to change the IP address of the distributor, for example to: 192.168.200.1

Also, we want to move the publication database to a new server. so, the replication source (publisher) will be also moved and it will have a new IP address as well.

The subscriber will remain as is.

I cannot afford having long downtime as our systems are running for 24x7 and there are so many huge tables that are already in replication and generating a new snapshot will take long time to be replicated.

What is the best way to seamlessly re-configure the replication and keep my data in sync without rebuilding it from scratch

Thanks

share|improve this question

2 Answers 2

Changing the IP addresses is no problem. You'll just need to force AD replication after the machine comes up with the new IP then do an IPCONFIG /flushdns on the other machines so that they can all see each other. Depending on which machine has it's IP changed and if you are doing push or pull replication you may not even need to do that.

Moving the distribution database is a lot harder to do. You can shutdown all the replication agents and do a backup and restore of the distribution database to the new machine, making sure to use the KEEP_REPLICATION flag. You'll then need to recreate all the SQL Agent jobs for replication on the new machine manually. Then you'll need to edit the replication tables manually to change the distributor to the new name, then edit all the agent jobs to give them the name of the new distributor. Once all that is done everything should work again.

Moving the publisher requires that the same approach be taken as moving the distribution database.

share|improve this answer
up vote 1 down vote accepted

As per Microsoft,

If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must re-create all publications and subscriptions after backups are restored.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.