Data Center

Checklist: How to Upgrade to Microsoft SQL Server 2014

As Microsoft ends support for SQL Server 2005 in April, users must upgrade or face serious risks.

Twitter

Russell Smith is a technology consultant and trainer specializing in management and security of Microsoft server and client technologies. He is a Microsoft Certified Systems Engineer with more than 15 years of experience.

With change comes great opportunity. That’s the message from Microsoft surrounding the SQL Server 2005 end-of-support deadline on April 12. After the deadline, Microsoft will no longer offer security updates and hotfixes.

Luckily, Microsoft SQL Server 2014 represents a considerable advance from previous versions. Performance and security improvements include a new in-memory OLTP engine to reduce disk I/O, AlwaysOn Availability Groups with support for up to eight secondary replicas, read-only replicas, and Azure integration.

Plus, SQL Server 2014 backup includes built-in database encryption, so there’s no need for a third-party solution, and integration with Microsoft’s Power BI cloud platform.

Those benefits aside, upgrades can prove difficult to begin or complete successfully. Here are some tips for a successful transition.

Solution Review

For starters, SAN-based databases can connect to SQL Server 2014, or admins can create snapshots to mount on the new server. But for systems based on traditional storage, SQL Server 2014 offers different options for migrating databases. Databases are restorable from SQL Server 2005 backups using the Log Shipping Wizard, although testing this solution could reveal issues that require custom scripts to resolve errors.

Database mirroring is still supported in SQL Server 2014, but it’s limited. Once failed over to the new server and live, there’s no easy way back because mirroring is unidirectional. Availability Groups replace database mirroring in SQL Server 2014 but are more complicated to set up because they require Windows Server Failover Clustering. And unlike mirroring, Availability Groups support up to eight secondary databases instead of just one.

Moving Forward

Those who have experience with upgrading Windows Server likely know that in-place upgrades are generally frowned upon because the outcome can lead to issues not found in clean installs.

The same advice applies to SQL Server 2014, and while migrating databases to a clean install requires new hardware or a new VM deployment, there are several advantages over in-place upgrades.

First, administrators can test the new server while keeping the old one running in production. Next, the original server remains untouched, so a rollback plan is easier to devise. And finally, downtime and risk are minimized by keeping the old server online while moving to the new server.

For more on upgrade options for SQL Server 2005, check out this post on the CDW Solutions Blog.

Upgrade Checklist

Before starting an upgrade, check that databases are in consistent condition. Then proceed as follows:

Check the minimum and recommended software and hardware requirements for SQL Server 2014.
Generate a SQL .trc file using Microsoft SQL Server Profiler to ensure that the SQL Server 2014 Upgrade Advisor can analyze T-SQL originating from business applications.
Run the SQL Server 2014 Upgrade Advisor, and use the recommendations to plan the upgrade.
Make sure a working backup and a watertight rollback plan exists.
Fix page count inaccuracies and run database integrity checks.
Test business apps against the new server before going live.

Peter Dazeley/Getty Images