Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.
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?
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.
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
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?
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.
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
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
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.