October 20, 2014 at 8:25 am
Hello- I am a DBA for a school district. We have a problem with multiple legacy systems running outdated versions of SQL server 2000, 2003, 2005 . I would like to update these instances to 2012+. These instances reside on their own servers and have very little use at times and peak periods are not that heavy either. I was thinking of having one virtual server built and moving each of these to this server as their own named instance on the server. There would be a dozen or so instances living on this server and the resource usage and allocation would be much more efficient. This is a new world for me as we have always put a dedicated server to each piece of software. Can anyone offer suggestions or fears to help guide me on this path? Thanks.
October 20, 2014 at 8:41 am
Have you considered consolidating your databases to a smaller number of instances, or even to a single instance?
John
October 20, 2014 at 8:49 am
Thanks for the feed back. I have thought about this and will do so where we can. That said some of our software requires a named instance but I don't see why that couldn't be the consolidated named instance. I know some applications have internal connection strings that look for a specific named instance but I think most of our apps we can configure and point to what ever we want. I will give that some thought. Your thinking like one instance that houses all the databases right? Thanks.
October 20, 2014 at 8:53 am
If you can get it down to one instance, yes. There might be reasons why you can't: some applications may not support the version of SQL Server you wish to move to; other databases may require a certain server collation.
John
October 20, 2014 at 8:57 am
First of all, consolidation of SQL servers (especially physical ones) can provide several large benefits ranging from reduced licensing costs to simplifying administration. Upgrading to a newer version also provides more tools that previous versions didn't have. That said, I want to point out a few common things that get overlooked when doing consolidation and version upgrades.
1) Check the SQL version requirements for the applications that will be accessing the new instance. Very often, applications that run on SQL 2000 are older versions themselves. And those older application versions may not play well with newer versions of SQL (Especially when moving from SQL 2000 to any newer platform!). So if you need to do some basic testing, consider putting an instance of SQL 2012 express somewhere to play with to test connectivity. You might find that you'll need to upgrade your application version as well.
2) Make sure you know all of the configuration points for your applications. Ensure any users (AD or sql based) that the applications users exist and have the proper rights on the new instance.
3) When upgrading from SQL 2000 to newer versions, some rather significant changes were made to how SQL functions. Starting in SQL 2005, the concept of schemas was introduced. Before that, users directly could own objects. Now, objects are part of a schema and those schemas have owners. As an example, in SQL 2000, you might have a user named "sqlUser" that created a table named "coolTable" which you'd reference with sqlUser.coolTable. Now, when sqlUser creates a table, there's also a SCHEMA called sqlUser that is owned by the USER sqlUser. Then coolTable is in turn owned by the SCHEMA sqlUser, not the USER sqlUser. That additional layer can present some challenges when you're migrating a database that has objects owned by various users. This is an over simplification of the change, but it gives you an idea of what to research.
4) Go slow! Move one thing at a time, and test, test, test. Then after that, test again. There's not much that slows you down more than making a bunch of changes to an environment, discovering a problem, and then having to track it down. If you make one change at a time and test between steps, researching any issues that pop up can usually be narrowed down to the most recent or next recent change to the environment.
These few things are certainly not an exhaustive list, but should give you an indication of the types of things you'll need to consider. Have fun with your migrations and of course, feel free to post if you run into any specific issues!
-G
October 21, 2014 at 11:30 am
Thanks everyone, some great information and steps to follow. Greg, great information and guidelines. Thankyou all.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply