Link to home
Avatar of Howzatt

asked on

Migrate SQL Server 2008 instance from Windows Svr 2003 to Windows Svr 2012 R2

We have an instance of SQL 2008 running on a Win Server 2003 VM.
Win Svr 2003 is a pain in the arse to resize partitions & its probably time to decommission it anyway.

I am competent with SQL svr, but I am not an expert. What is the best/easiest method to relocate to a newer host?
Avatar of Abhigyan Srivastava
Abhigyan Srivastava
Flag of India image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Howzatt


Thanks for that. Do you think it would be best to replicate the HostName & IP address for connectivity purposes?
backup/restore each DB 1 by 1 on a new hostname & reconfigure each app appropriately?
Well you can replicate the host name and IP address if they are on the same network. That way you will save the time spent in re configuring each app.

If that is not possible then of course you need to change the connectionstrings in each app.

While it may be easier to rename the server (and SQL) than reconfigure each application, its probably better to reconfigure each application to point to the new database. If you have a truck load of applications then that means you can cut them over piece-meal and not have to do everything at once..

When renaming the server, you need to do that inside SQL as well. And that requires a SQL restart once the server is renamed. ( What I'm saying is this: Intitially the new server will be something like OurSQLServer-temp\InstanceName. You need the two servers side-by-side to copy the backups across, so that means different names. At some point, either before or after the restore step, the servers need to be renamed so original becomes OutSQLServer-old\InstanceName and the new server looses the -temp from the name.

Of course you could do an in place OS upgrade, but running up the new machine give the opportunity to redo the disk layout, and resize c: drive - its amazing how much space the new OS's need.

Consider at this time doing the following: Install an instance of SQL 2014 alongside the upgraded 2008 instance, and migrate those databases whose apps can support the new database version. It will take only a little more work if that, and achieve two results for the same work.

SQL 2008 - exactly which version/edition? If you have the feature, make sure to test the backup compression setting - it may make a substantial difference to the speed of backup/copy/restore due to writing significantly less to disk in the backup step.

1. Backup SQL server schemas on Win 2003 server
2. Set up SQL server 2008 on Win 2012 server
3. Restore backup files (on 1 above) using SQL server management studio within Win 2012 server

For moving users look up the sp_help_revlogin procedure and implement that. It scripts out logins including sids and the password (encrypted) so the new logins are identical on the new server.

Even though it is an excellent idea to move away from server 2003 to newer and shinier OS have you considered doing a V2V conversion using VMware VConverter in the meantime? As you can resize the partitions when you convert.
We have an instance of SQL 2008 running on a Win Server 2003 VM

Then you can at least take your time to properly migrate your db.