Are You Still Running SQL Server 2005?

Know the Options

1. SQL SERVER 2008 R2 (WITH LATEST SP)

   1. Easiest upgrade
   2. Licensing can be tricky if you are not already licensed on a different server

2. SQL SERVER 2012 (WITH LATEST SP)

   1. Not too difficult
   2. Simple to license
   3. Lots of new features: High Availability, Business Intelligence, and more

3. SQL SERVER 2014 (WITH LATEST SP)

   1. Risky, but still possible
   2. BEST option if your application is compatible

Questions to Ask

• What is this database for? (Accounting, Human Resources, Critical Application…)
• 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, 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
   1. Recommend running run the Microsoft Assessment and Planning Toolkit to get a full inventory of you database servers
   2. https://www.microsoft.com/en-us/download/details.aspx?id=7826
2. Install the SQL Upgrade Advisor on any machine on the same network as the identified SQL Servers
   1. https://msdn.microsoft.com/library/ee210480.aspx
3. Launch the Upgrade Advisor Report Viewer
   1. Dial down to your server/instance and select a component
   2. Click Open Report
4. The report will have a list of detected issues identified by Importance
5. Review the entire report carefully!!
   1. This report just tells you what could be wrong
   2. It does nothing to actually fix anything
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
   1. 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
   2. If you choose a physical host, be well aware of how Microsoft licenses SQL Server now (by core count) and purchase the CPU with the highest clock rate you can afford
   3. Know that licensing will by far be the most expensive line item on this project!
   4. Provision your server, check out my other blogs to start with a nicely optimized operating system (Build a Small SQL Server, Configure Your OS, Install SQL Server)
8. Time to test!
   1. Backup production database(s)
   2. Restore databases to same, local SQL Server 2005 instance (with a new name for the database)
   3. Change database compatibility level to 90
      1. This will make sure that if this database came from SQL Server 2000 or older, that SQL Server 2014 will be able to use it
      2. SQL Server 2012 and SQL Server 2014 will not be able to read databases with a lower compatibility level
   4. Purchase, license, and install your chosen version of SQL Server on your new server
   5. Backup the Level 90 database and restore to the new SQL Server 2014 instance
      1. Or use the Enterprise Evaluation edition for this testing phase
      2. SQL Server Express is also an option for small databases, under 10GB
   6. Run these commands:
      1. USE MyDB DBCC CHECKDB WITH DATA_PURITY;
      2. DBCC UPDATEUSAGE(MyDB);
      3. USE MyDB EXEC sp_updatestats;
   7. Take a full backup
   8. Point your application to the new instance (temporarily, outside of business hours) and run through a few simple tasks
      1. There are several places where you may get a failure…
      2. Definitely view each screen, add a few records, and run a few reports to see how it is performing
   9.  Record your results and point your application back to the original server
   10. Review your results with stakeholders/management
   11. 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. Or be sneaky and don’t tell them, like Folger Crystal’s commercials back in the 80s
    1. “Let’s see if they can tell the difference?”
11. Cutover and party like it’s 2016!!!

Benefits

By upgrading your organization, you not only get your databases into a fully supported state, but you are also taking advantage of over 10 years of innovation and improvements in Microsoft’s database engine 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 the on-premises edition of SQL Server. SQL Server 2016 hit Release Candidate 0 here recently and promises another deluge of improvements, particularly in the Business Intelligence space.

Need help getting the ball rolling on a SQL Server 2005 assessment and upgrade? Contact me at 1(972) 987-0637 or email me at Paul@PBITPro.com.

For more information on Microsoft infrastructure topics, please visit the rest of my blog or follow me at @pdgb.