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

Howzatt
Howzatt used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The best way is to restore the backup from SQL 2008 on Win2k3 to SQL 2008 on Win2k8.

Do take care of the users. They are a pain to create after restore.

Let me know if you face any problem in that. I have a script somewhere on the disk that would help you. Have a client who is a security freak - even more than I am. Consequently the database has several users for different functions like one user to read from Shopping Cart, another to update from it , one to read from Customer Details and so on.

Author

Commented:
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.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

David ToddSenior Database Administrator

Commented:
Hi,

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. (https://msdn.microsoft.com/en-us/library/ms143799.aspx?f=255&MSPPError=-2147217396) 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.

HTH
  David
HuaMin ChenProblem resolver

Commented:
Hi,
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
David ToddSenior Database Administrator

Commented:
Hi,

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.

HTH
  David
Dirk MareSystems Engineer (Acting IT Manager)

Commented:
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.

DirkMare

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial