When a business needs to move a distribution database to a new MS SQL Server instance, the most common solution is to drop and recreate a replication topology with a new distributor instance. However, this approach can be time-consuming and may cause downtime in production systems. In this article, we will discuss a better solution that involves restoring the existing distribution database on the new distributor.

Step-by-Step Guide

Follow these steps to move a distribution database to a new SQL Server instance:

1. Create a distribution database on the new distributor instance.
2. Add the publisher values on the new distributor instance.
3. Create the missing linked servers on the new distributor.
4. Make sure the msdb.dbo.MSagent_profiles are matching between distributors.
5. Set the distribution database to full recovery mode.
6. Create the missing logins on the new distributor and add distribution agent and log reader agent accounts to the db_owner role on the distribution database.
7. Create a missing distribution agent login and add it to the db_owner role on the subscriber databases for pull subscriptions.
8. Stop or disable replication jobs on the current distributor and pull subscribers.
9. Backup the distribution database on the current distributor.
10. Restore the distribution database on the new distributor instance.
11. Update the MSpublisher_databases table with the correct publisher id.
12. Script out log reader and snapshot jobs from the current distributor and create these jobs in a disabled state on the new distributor with updated server names in all job steps.
13. Change the publisher_id in the restored distribution database.
14. Change the subscriber_id/publisher_id in the restored distribution database.
15. Script out push subscription jobs from the current distributor and create these jobs in a disabled state on the new distributor with updated server names in all job steps.
16. Alter pull subscribers replication jobs to update the new distributor name.
17. Alter the distributor name on pull subscribers.
18. Update log reader agent entries to ensure the replication monitor is working.
19. Change the distributor name on the publisher.
20. Enable subscriber jobs one-by-one and resolve any permission issues.
21. Enable log reader agent jobs one-by-one and resolve any permission issues.
22. Add the distribution database to the backup schedule and perform regular database backups.
23. If no errors exist, delete disabled replication jobs on the old distributor.
24. Drop the old publisher on the old distributor server.
25. Open replication monitor and verify transactional replication activity.

This custom solution allows SQL DBAs to quickly move a distribution database to a new SQL Server instance without losing data and causing downtime in the production environment. It also provides a way to revert back to the previous setup if any issues occur during the change process.

It is important to note that if log-reader agents are enabled and new transactions have been processed on the new distributor, the correct way to rollback would be to drop and recreate replication objects and perform data synchronization afterwards.