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?
HowzattAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Abhigyan SrivastavaCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HowzattAuthor 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?
Abhigyan SrivastavaCommented:
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.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

David ToddSenior Database AdministratorCommented:
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 resolverCommented:
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 AdministratorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.