Know the Options
SQL SERVER 2008 R2 (WITH LATEST SP)
- Easiest upgrade
- Licensing can be tricky if you are not already licensed on a different server
SQL SERVER 2012 (WITH LATEST SP)
- Not too difficult
- Simple to license
- Lots of new features: High Availability, Business Intelligence, and more
SQL SERVER 2014 (WITH LATEST SP)
- Risky, but still possible
- 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?
- Make sure you know exactly how many servers/databases you have running SQL Server 2005
- Recommend running run the Microsoft Assessment and Planning Toolkit to get a full inventory of you database servers
- Install the SQL Upgrade Advisor on any machine on the same network as the identified SQL Servers
- Launch the Upgrade Advisor Report Viewer
- Dial down to your server/instance and select a component
- Click Open Report
- The report will have a list of detected issues identified by Importance
- Review the entire report carefully!!
- This report just tells you what could be wrong
- It does nothing to actually fix anything
- Click the “Tell me more…” links to view fixes and keep track of your progress
- 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 well 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 by far be the most expensive line item on this project!
- 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)
- 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, that 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 10GB
- Run these commands:
- USE MyDB DBCC CHECKDB WITH DATA_PURITY;
- DBCC UPDATEUSAGE(MyDB);
- USE MyDB EXEC sp_updatestats;
- Take a full backup
- Point your application to the new instance (temporarily, outside of business hours) and run through a few simple tasks
- There are several places where you may get a failure…
- 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
- Prepare your users for a cutover
- Or be sneaky and don’t tell them, like Folger Crystals commercials back in the 80s
- “Let’s see if they can tell the difference?”
- Cutover and party like it’s 2016!!!
By upgrading your organization, you not only get your databases into a fully supported state, you are 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 Paul@PBITPro.com. For more information on Microsoft infrastructure topics, please visit the rest of my blog or follow me at @pdgb.