February 8, 2006 at 10:33 pm
I need to move our SQL Server applications to another computer with minimal disruption and there is quite a complx replication set up on it.
Is it possible to simply backup the Distribution database form one machine and restore it to the the other?
If this works, are there any other steps required?
Failing this, I have gerenated a Create Replication script and I plan to run this after we change the name of the SQl Server to the the name of the current one on the day of change over.
If both options are viable, is one better than the other?
Thanks,
Dick Campbell
February 8, 2006 at 11:00 pm
anything is possible
but it is always better to test on a test server prior to implementation, different environments may result differently (especially when it involves network restrictions)
HTH
February 9, 2006 at 3:26 pm
Dick,
I have had to do this myself and the approach I took was to Script the entire Replication Process. Disable it and then restore the Databases to the new server and then execute the Replication Scripts to re-create the Replication.
But as Jen has suggested, test it in a test environment first!
February 9, 2006 at 3:54 pm
Thanks,
When you say that you restored the database, I assume that you mean the application database not the distribution database. correct?
I am leaning towards using the script myself. Did you have to set the server up as a Distributor (which I assume creates the distribution database) before you ran the script or does that happen automatically when you run run the script? I would expect the former.
Dick Campbell
February 14, 2006 at 6:59 am
I haven't done this exactly myself but I have split off a Distributor to a remote Distributor.
Therefore I believe you will need to script of replication settings, drop replication environment then re-create on your new Server, followed by re-initialisation
This was in SQL 7.0 environment though, not sure if it is different in 2000 (as backup/restore scenario is easeir than in 7.0).
I tested this several times before I was happy with the procedure because of the no. subscribers we had (150+) so it will obviously depend upon your topology how much testing you feel is adequate but you should certainly test it at least once.
Paul R Williams.
February 14, 2006 at 12:33 pm
Thanks Paul,
I have concluded that I should use the scripting approach. I won't need to drop Replication on the new server but I think that you are saying that I should drop scripting on the old server so that all subscriptions are removed on subscriber computers. There are all push subsciptions but part of the information is stored on the subscriber (I think).
We intend to rename the computer, SQL Server (to the orignal names) and use the same IP address so I am not sure whether this reduces the need to drop replication.
On the other hand, re-running the create script with the subscribers still active could create problems.
We have too multiple subcribers, only 20 though.
Thanks for the reply,
Dick Campbell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply