Software

To Avoid Security Pitfalls, Make a Plan to Upgrade to SQL Server 2014

With SQL Server 2005 support discontinued, here’s a clear strategy for making a smooth transition to a newer SQL version.

Brien M. Posey

Twitter

Brien M. Posey is a freelance technical writer who has contributed to dozens of books and is a six-time Microsoft MVP award winner.

Many IT professionals live by the maxim that if something isn’t broken, don’t fix it.

This philosophy has its merits, especially for small to medium-sized businesses trying to grow on sometimes shoestring IT budgets. But in practice, it can lead to using software that has become obsolete.

Although Microsoft officially discontinued support for SQL Server 2005 in mid-April, the software obviously continues to function. Given the cost and complexity of migrating SQL Server installations, some businesses may be tempted to maintain SQL Server 2005.

Several factors make this ill-advised. But the most important reason to migrate to a newer version — SQL Server 2014 (on premises), SQL Azure Database (in the cloud) or SQL Server 2016 (expected later this year) — is that Microsoft will no longer provide security patches.

1. Inventory Your SQL Server Assets

The first step in the migration process is to compile a detailed inventory of your environment. You need to determine which servers have SQL Server 2005 installed (and which applications are using SQL Server 2005 databases) as well as how many licenses you need.

Many tools are available to inventory IT resources, but it's smart to start with the Microsoft Assessment and Planning Toolkit. MAP includes tools designed to assist with migrations to SQL Server 2014. It supports discovery and inventory of SQL Server resources and provides detailed SQL-related reporting.

2. Evaluate Your Existing Infrastructure

The next step is to assess your existing infrastructure for SQL Server 2014 readiness. Given SQL Server 2005’s age, you may find that your servers are not up to running the newer version. After evaluation, decide if you instead might want to move to SQL Azure or even prepare for SQL Server 2016.

The SQL Server 2005 hardware requirements vary based on edition, architecture (32-bit versus 64-bit) and other factors.

For the sake of comparison, SQL Server 2005 Enterprise Edition (32-bit) requires a Pentium III or higher processor, running at 600 megahertz or higher (1 gigahertz recommended), and 512 megabytes of RAM (1 gigabyte recommended). The 32-bit version SQL Server 2014, on the other hand, requires a 1GHz processor (2GHz recommended) and at least 1GB of RAM (4GB recommended).

If you want to run the 64-bit edition, you will need at least a Pentium IV processor running at 1.4GHz. Also, it can run only on Windows Server 2008 or higher.

3. Decide Whether to Upgrade or Migrate

One of the most important decisions in transitioning to SQL Server 2014 is whether to perform a migration or an in-place upgrade. An upgrade involves installing SQL Server 2014 on top of your existing SQL Server deployment, whereas a migration involves setting up and migrating data to new SQL Servers.

In most cases, a migration is preferable. Here’s why:

You can’t perform an in-place upgrade on top of SQL Server 2005. You first must upgrade to SQL Server 2008. A migration lets you avoid this middle step.
You start with a clean deployment, rather than carrying over baggage from an older version of SQL Server (and possibly an older OS).
If your SQL Server 2005 deployment runs the 32-bit edition of SQL Server, you cannot switch to 64-bit as part of an upgrade because that requires the continued use of your same architecture

Vladimir_Timofeev/Thinkstock