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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
If that is not possible then of course you need to change the connectionstrings in each app.
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\Instance
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
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\Instance
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
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
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
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
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
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.
Then you can at least take your time to properly migrate your db.
DirkMare
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
ASKER
backup/restore each DB 1 by 1 on a new hostname & reconfigure each app appropriately?