Published on June 6, 2016 by Lloyd Young
Extended support for SQL Server 2005 has ended. That means no more service packs, cumulative updates, security hotfixes or anything else for that matter will be released for SQL Server 2005. Ever again. Goodbye SQL Server 2005! You were definitely an awesome jump ahead in the race to organize corporate data.
If you’re still running SQL Server 2005 in your footprint, do not fear. Microsoft has been busy moving the needle with newer, better, bigger data ecosystems. It seems like every release gets more and more awesome in a number of ways. Not only do the underlying services improve, but so do the tools for developing solutions with SQL Server and the tools for managing the ecosystem. The choice is yours as to which version you migrate to.
Well… That’s not entirely true. The choice can be limited to the versions that your vendor supports. Sorry, there’s always a caveat…
So long story short, it’s time to upgrade! Though we can’t help with the licensing costs, our team of Solvarians can definitely help keep costs low by reducing the amount of time it takes to upgrade. We got our “been there done that badges” long ago. Also, it’s important to keep in mind that while web searches will turn up a few good pages to describe the process of upgrading to a new version of SQL Server, bear in mind that it’s likely you’ll be upgrading more than just the databases. Following is a brief list of things to consider that are often omitted from a Google search when building an initial upgrade plan. Note, this list roughly applies to upgrading SQL Server from any version to any newer version.
- Upgrade the compatibility level for all databases whose applications support a newer version of SQL Server.
- By default, when you restore a database to a newer version of SQL Server, the compatibility level will stay as the version from which you restored.
- If you know that all of your applications for a given set of databases is compatible with a newer version of SQL Server, go ahead and set their compatibility level appropriately.
- Upgrading SQL Server means upgrading your clients. You’ll need to consider how to get the following software installed on all client machines
- SQL Server Native Client for communications to SQL Server
- SQL Server Management Studio for administration
- SQL Server Data Tools, mainly for SSIS package and SSRS report development, but also for database and cube development.
For DTS packages, build a new SSIS project in SSDT, import the packages, upgrade them, and deploy to the SSIS catalog. Remember to set the target SSIS server version at the project level before deploying.