Move to New Server

Question

Move to New Server

Colin Betteley

Hall of Fame

Points: 3095
More actions
March 29, 2006 at 7:46 am

#112569

Afternoon All,
     I have to move SQL Server 200 to a new machine, as the lease is now expiring on the current hardware. I have done much research in trying to formulate a plan and am making this post to check if anybody can see any problems and also to aid any others who may face this issue in the future - but I cannot accept any responsibility for any action taken.
    I have found many postings on the Web, but a clear plan is seldom given. Our issue is compounded by the fact the new server will have a different name and also a different drive structure!
Step 1.   Check that the new server is patched at the same level as the existing server.
Step 2.    Install SQL Server on the new machine - to the same patch level. Retain the same file structure, albeit it on different drives.
Step 3.     In order to retain logins, Jobs and DTS packages. Restore the Master, msdb and Model databases in that order. I believe that there is no need to restore the tempDb.
Step 4.     Restore user Databases
Step 5.     Test and test again.
Step 6.     If all Ok, restore ALL Dbases again, over a weekend (no users).
Step 7.     Test again.
Step 8.      Amend DNS so that any request for the "old" server is redirected towards the new server.
Step 9.      Monitor new live system and revert to old server in case of disaster.

All comments welcomed.
Thanks
Colin

Viewing 5 posts - 1 through 4 (of 4 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Your plan sounds good to me. It is always nice to be able to stage a server. I have done it that way and also the not-so-nice way of having to build a new server and cutover all at once. Being able to stage a server, test the application, and then use DNS to cutover has always been my preferred method.

You may want to add a step to shut down SQL Server on the old box after you cutover. This will ensure that any workstation that is still trying to connect to the old instance (either because host file or DNS issues) are identified and can be looked at.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

This is a good plan to add entries to DNS to point the new server. Is the IP address changing? You have to account for the users who connect by IP.

There could be one problem: If there were logins /users who were granted access to SQL Server as Windows local users. Your machine will be changed, so if they re-create local Windows users they will have different SIDs. Someone I know did have this issue because his users were all local Windows users.

Regards,Yelena Varsha

skeane SSC Eights! Points: 877 More actions · Answer 3

Good plan, with a couple of additions. There are a couple of places where the server name is kept - since you are renaming the server, you will need to update them after restoring your system databases. This should do it for you:

exec sp_dropserver @server = 'OldServer'

exec sp_addserver @server = 'NewServer', @local = 'local'

UPDATE sysjobs SET originating_server = 'NewServer' where originating_server = 'OldServer'

Colin Betteley Hall of Fame Points: 3095 More actions · Answer 4

Thanks for the encouraging responses. I will report back in a few days with any issues that I encountered during the process.

Thanks

Colin