Moving the Distribution Database to a server with the same name without breaking replication

Question

Moving the Distribution Database to a server with the same name without breaking replication

schwizla

SSCrazy

Points: 2668
More actions
March 19, 2010 at 8:16 am

#218419

Sorry for the double posting, hopefully this is the correct section now.
I am using SQL 2008. There is no section for SQL 2008 replication 🙁
I have built a duplicate remote distributor server and I am trying to move
the distribution database to the server without breaking the existing
replication but I am having a few problems.
This was the steps I followed.
Original remote Distributer called SQLDIST1
1. Built and configured a SQL Server called SQLDIST2 with base install of
SQL2008 Ent
2. Shut Down SQLDIST1
3. Renamed SQLDIST2 to SQLDIST1 including sp_addserver stuff (all good so far)
4. Restored master database, then msdb and then Distributor from original
SQLDIST1 to new SQLDIST2 (note I did not enable the new server for
Distribution, just restored the databases as after restoring master I could
see a Distribution database entry and I figured all the config is in the
system tables anayway)
5. Trans Replication now working from the publisher. I inserted a few rows
and these were replicated.
6. however heres the screwy bit. Replication Montior not allowing me to
register the Puiblisher properly. I can see the Log Reader and snapshot
agents in ReplMon but cannot see the distribution agent.
I do not trust the end result. Also cannot find any article on the net with
regards to migrating, recovering from a disaster of the distribution database!
Any help please?
thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

mike mcneer Ten Centuries Points: 1189 More actions · Answer 1

Ugghh had some documentation on this but cant find it. So basically there is a table that holds subscriber-distributor info that the repl monitor uses to populate the gui. It sounds like the old information is still there. It has to be updated to reflect the new distributor. the changeserver doesnt get this particular table. Hopefully I will find the exact tables and report back.

schwizla SSCrazy Points: 2668 More actions · Answer 2

look forward to hearing back from you. I haven't been able to find a solution to this

Leeland SSCrazy Eights Points: 8959 More actions · Answer 3

in the past we have migrated servers like this "the whole server" from physical to virtual.

We have had to do the following

Have a current production server (call it DBServer1)

Create new server (call it DBServer1_NEW) similar config

I am paraphrasing to some extent...

Run log reader on servers, run distribution jobs shut down DBServer1 server.

Copy mdf and ldf files for all databases (noting the file path locations for the db files)

rename DBServer1 to DBServer1OLD

take DBServer1 out of the domain (infrastructure stuff here)

rename DBServer1New to DBServer1

Put DBServer1 back into the domain

Install SQL server with replication in the correct file paths / correct collation / correct sp level.

Stop DBServer1

Copy out the current mdf & ldf files

replace with the old mdf and ldf files

Start DBServer1 and everything should be the same as it was, the databases won't know the difference

Verify error log on start up and verify replication.

It has worked for SQL 7 and SQL 2000 migrations...haven't done the above on sql 2005 or 2008...

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 4

can't say the same thing for everyone, but a lot of times its easier and faster to just reinitialize replication than troubleshoot it or migrate it. script it out, and then recreate the publications from script and rerun the snapshots

schwizla SSCrazy Points: 2668 More actions · Answer 5

Only problem for me here is that I meed to push out 3TB data that takes three days and I can't have that downtime unfortunatley. The snapshot takes about 8 hours as well 🙁

We have decided to take another route now, going to take 2 hour publisher downtime, build new dist server and set up pubs from scratch. When creatIing the subs will specify that data already exist.

You would have thought migrating the dist should have been a lot easier!

jvkondapalli SSCommitted Points: 1587 More actions · Answer 6

@schwizla - did you find the solution to this? we are planning on doing similar upgrade. Do you mind posting the solution?

Thanks

Jagan K

Thanks
Jagan K

MiketheDBA SSC Enthusiast Points: 154 More actions · Answer 7

I think the original post would have worked but you needed to update the stored distributor server info that replication is using.

If you look at the SP "sp_get_distributor" I believe it would have shown your old distributor server name. Explore that proc and you will see where it is pulling that server name from and update accordingly.

MiketheDBA SSC Enthusiast Points: 154 More actions · Answer 8

EXEC sp_helpdistributor;

EXEC sp_helpdistributiondb;

EXEC sp_helpdistpublisher;