How to move the distribution database to another server

Our replication distributor shares a server being decommissioned and the distribution database must land on a new machine. Publisher and subscribers stay where they are.

Is this a detach and attach or something worse?

Something worse, said plainly so the planning is honest: the distribution database is not portable, its contents are wired to the specific distributor by name throughout the replication metadata on every participant. The supported move is a reconfiguration, tearing replication down and rebuilding it pointed at the new distributor. The good news is that scripting makes the rebuild mechanical.

1

Script the entire topology first, this is the step that makes everything else survivable: in SSMS right click each publication, Generate Scripts, capturing publications, articles and subscriptions as T-SQL. Review the scripts for completeness against the Replication Monitor's view of the world, they are your rebuild.

2

Quiesce and tear down in order: let subscribers catch up fully, confirmed by tracer tokens or the undistributed commands count reaching zero, then drop subscriptions, drop publications, then sp_dropdistributor on the publisher which releases it from the old distributor.

3

Build the new distributor: on the new server, configure distribution through the wizard or sp_adddistributor plus sp_adddistributiondb, sizing the distribution database's files properly this time since you know the workload. Point the publisher at it with sp_adddistributor naming the new server, using a strong distributor_admin password recorded somewhere responsible.

4

Rebuild from your scripts: run the publication and subscription scripts against the new topology, choosing initialization per subscription, a fresh snapshot for small tables against initialize from backup for the large ones where a snapshot window hurts. Verify with tracer tokens flowing end to end before calling it done.

Timing honesty for the change request: the teardown to rebuilt window is subscriber staleness, not downtime, publishers and subscribers keep serving reads and writes throughout, subscribers just stop receiving changes until their subscription reinitializes. Sequence the big table initializations against the business calendar and the move becomes an evening rather than an emergency.

Scripted everything, tore down after Friday close and rebuilt on the new distributor with backup initialization for the two big tables. Tracer tokens flowing by Saturday noon. Not the detach I hoped for but the script first approach made it boring, which is the compliment.