March 19, 2010 at 8:16 am
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
March 29, 2010 at 11:02 am
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.
March 30, 2010 at 2:05 am
look forward to hearing back from you. I haven't been able to find a solution to this
March 31, 2010 at 1:11 pm
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...
April 2, 2010 at 11:46 am
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
April 2, 2010 at 5:49 pm
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!
April 15, 2015 at 9:03 am
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.
April 15, 2015 at 9:05 am
EXEC sp_helpdistributor;
EXEC sp_helpdistributiondb;
EXEC sp_helpdistpublisher;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply