Sql-server – Modifying replication distributor & publisher

replicationsql serversql-server-2008

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 24×7 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

Best Answer

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.

Related Question