I need to move my distributor server to a brand new server without resnapshotting it. The scenario I have is similar to the one defined in below link.
http://www.sqlservercentral.com/Forums/Topic844095-291-1.aspx
Environment:
1 publisher
1 distributor
4 subscribers
Replication type: Transactional replication with updatable subscription
I want to know if someone has tried the solution given in above link or if you have any alternate solution.
Best Answer
I didn't try this but just giving an approach I would follow.
The log reader scans all the log records and keeps it in distributor DB. The subscriber reads the records from the distributor DB, then inserts it in subscribers and marks the last record it read.
Considering the above topology, I don't see any issues with moving the distributor DB. Assuming you are moving the publisher(push) or subscriber(pull) involved and the server name remains the same.
In a Push model, the distributor DB resides in publisher and in pull model, the distributor resides in the subscriber DB.
Replication stores all the server information for all publishers, distributors, and subscribers in the database. So if you go with this approach I would suggest the below steps:
The only issue I see here is the instance name being the same as the old one, which can be tricky. You also can try testing this yourself with a single table replication and with the same setup.