Are You Still Running SQL Server 2005?

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

1. SQL Server 2008 R2 SP3

• Easiest upgrade

• 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?

Process

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

• Provision your server and check out my other blog posts to start with a nicely optimized operating system (Build a Small SQL Server, Configure Your OS, Install SQL Server)

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;

  • DBCC UPDATEUSAGE(MyDB);

  • 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!

Benefits

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 sales@credera.com or call 972.692.0010. For more information on Microsoft infrastructure topics, please visit our blog or follow us at @CrederaIT.

• SQL
• SQL Server 2005
• SQL Upgrade