Just in case you didn’t see the news, Microsoft will end extended support for SQL Server 2005 on April 12, 2016. Do you still have critical databases running on this version of SQL Server? Dare I ask, are they running on the ancient Windows Server 2003 R2 OS, which has been unsupported by Microsoft since last summer? It’s time to upgrade! Here is an overview of the process to get that done.
Know the Options
SQL Server 2008 R2 SP3
Download the Enterprise Evaluation edition
2. SQL Server 2012 SP3
Not too difficult
Simple to license, included with any current licensing purchase
Lots of new features: High availability, business intelligence, and more
3. SQL Server 2014 SP1
Risky due to the high number of deprecated features and breaking changes across four versions of SQL, but still possible
Best option if your application is compatible
Questions to Ask
What is this database for? (accounting, human resources, critical application, etc.)
Is my vendor still supporting it?
What versions of SQL Server are on the application compatibility list?
What does it take to upgrade that application to be compatible with the latest version of SQL?
Does that effort/cost outweigh the risk of not being able to call Microsoft with any SQL Server issue?
What costs do I have (server, licensing, and professional services) to upgrade to SQL Server 2014?
1. Make sure you know exactly how many servers/databases you have running SQL Server 2005. We recommend running the Microsoft Assessment and Planning Toolkit to get a full inventory of your database servers.
2. Install the SQL Upgrade Advisor on any machine on the same network as the identified SQL Servers.
3. Launch the Upgrade Advisor Report Viewer.
Dial down to your server/instance and select a component
Click Open Report
4. The report will have a list of detected issues identified by Importance.
5. Review the entire report carefully! This report just tells you what could be wrong—it does nothing to actually fix
6. Click the “Tell me more…” links to view fixes and keep track of your progress.
7. At this point we need to decide if the new SQL Server will be a virtual machine, a new physical host, or a cloud VM.
The only operating system you should be installing at this time (March 2016) is Windows Server 2012 R2. Do not be tempted to install anything older
If you choose a physical host, be aware of how Microsoft licenses SQL Server now (by core count) and purchase the CPU with the highest clock rate you can afford. Know that licensing will be the most expensive line item on this project by far
8. Time to test!
Backup production database(s)
Restore databases to same, local SQL Server 2005 instance (with a new name for the database)
Change database compatibility level to 90
This will make sure that if this database came from SQL Server 2000 or older, SQL Server 2014 will be able to use it
SQL Server 2012 and SQL Server 2014 will not be able to read databases with a lower compatibility level
Purchase, license, and install your chosen version of SQL Server on your new server
Backup the Level 90 database and restore to the new SQL Server 2014 instance
Or use the Enterprise Evaluation edition for this testing phase
SQL Server Express is also an option for small databases, under 10 GB
Run these commands:
USE MyDB DBCC CHECKDB WITH DATA_PURITY;
USE MyDB EXEC sp_updatestats;
Take a full backup.
Point the non-production instance of your application to the new SQL Server instance
Run through a few simple tasks. There are several places where you may get a failure, so definitely view each screen, add a few records, and run a few reports to see how it is performing
Record your results and point your application back to the original server
Review your results with stakeholders/management
Consider upgrading the compatibility mode to SQL 2014 to take full advantage of the newest SQL Server 2014 optimizations
9. Prepare your users for a cutover.
10. Cutover and party like it’s 2016!
By upgrading your organization, you not only get your databases onto a fully supported platform, you are also taking advantage of more than 10 years of innovation and improvements in Microsoft technology. SQL Server has been battle-tested in the cloud for several years now and Microsoft is using knowledge gained in that endeavor to make incredible improvements in SQL Server. SQL Server 2016 recently hit release candidate 0 and promises another assortment of improvements, particularly in the business intelligence space.
Need help getting started on a SQL Server 2005 assessment and upgrade? Contact Credera at firstname.lastname@example.org or call 972.692.0010. For more information on Microsoft infrastructure topics, please visit our blog or follow us at @CrederaIT.