by Andy Patrizio

What end of support for SQL Server 2005 means for CIOs

Nov 23, 2015
Data CenterMicrosoftServers

Microsoft’s support for SQL Server 2005 ends in April and just like with Server 2003, you should take the end-of-life announcements from Microsoft seriously.

End-of-support is headache for CIOs, because it means replacing and/or upgrading hardware and software with minimal to no return on investment. IT departments tend to stall on the process to delay the inevitable of disruption, expense and replacement, but it has to be done.

Many firms have already been through this headache with Windows XP in 2014 and Windows Server 2003 last summer … and now they’ll have to do it again. Come April 2016, Microsoft will no longer support SQL Server 2005. Like Windows, SQL Server 2005 will continue to work after end of life (EOL), but it won’t be patched if an exploit is found.

The good news is that if you have been doing Server 2003 upgrades, you’ve likely replaced SQL Server 2005 already. The Server 2003 upgrade process forced many firms to take a complete look at everything they had – not just the server operating system – and most took the opportunity to do significant system-wide upgrades. So as old Server 2003 instances were retired, so were SQL Server 2005 installs.

“The discovery process on Windows Server 2003 has driven awareness of SQL Server 2005 as well,” says Tiffany Wissner, senior director for data platform marketing at Microsoft. “When they upgraded Server 2003 infrastructure, they upgraded [the database] as well. But people want to be mindful of how they migrate. Do they go modern server, SQL in the cloud or other options.”

The bad news is there’s a huge amount of Server 2003 and likely SQL Server 2005 still out there. Greg O’Connor, CEO of AppZero, a Windows Server 2003 migration specialist, estimates that one in six of the estimated 13-15 million existing Windows Server 2003 installations also has SQL Server 2005 on it. And he said Server 2003 migration will go on for several more years, because people waited too long and it takes longer than they thought to migrate.

[Related: Haven’t migrated from Windows Server 2003 yet? You’re not alone]

However, most people with critical workloads have already migrated to a newer version of SQL Server, either 2012 or 2014, according to Carl Olofson, research vice president for data management software at IDC.

“Where you will find a lot of SQL Server 05 is those little servers in the corner generating reports, not on a critical path to anything. I’d be willing to bet most enterprises don’t even know how many copies of SQL Server 05 they are running,” Olofson says.

Multiple options for SQL Server 2005 EOL

A lot of smaller SQL Server 2005 installations are already unsupported because people just stopped renewing service contracts, says Olofson. For a long time, Microsoft sold maintenance agreements separate from the product and for smaller installations doing less than major work, they simply let the contract lapse.

The lack of support for SQL Server 2005 will have a similar impact as Server 2003’s EOL because you face the same compliance issues.

Compliance is as problematic for SQL Server 2005 as it is with Server 2003. HIPAA and PCI compliance both require up-to-date, patch databases, just like they do with the operating system. So if you’re using SQL Server 2005 for anything related to HIPAA or PCI, you will be out of compliance come next April.

When enterprises migrate off Windows Server 2003, they have two options: Server 2008 or Server 2012. The few firms that choose Server 2008 do so only because they already had Server 2008 deployed internally and they wanted consistency across the line, even if that meant a fairly old operating system.

For SQL Server customers, there are several options: SQL Server 2012, SQL Server 2014 and Azure SQL Database. SQL Server 2016 is currently in beta and customers are testing it out, but Microsoft does not advocate waiting for it to be released.

SQL Server 2014 comes in four flavors, while Azure SQL Database comes in three. There’s also a marked difference between the 2012 and 2014 versions.

The two major differences between 2012 and 2014 are in-memory analytics and OLTP, says Wissner. With the in-memory technologies, SQL Server 2014 saw 100-fold query performance gains and a 30-fold improvement in OLTP processing. SQL Server 2014 also takes advantage of Windows Server 2012 R2 with virtualization and storage capabilities.

Paul Turley, a SQL Server MVP and BI architect with SolidQ, says he’s known clients who preferred to be one version behind on a product rather than on the bleeding edge. “A large portion of companies live by the belief is to stay one version behind is the safe zone. It’s been tested and debugged. So a lot of folks will stay behind purposely,” he claims.

But in the case of SQL Server, he encourages people to get the newest version. “I can’t think of any good reason to stay one version behind. Five years from now, how far behind do you want to be?” he asks.

[Related: Is Windows Nano Server a data center game-changer?]

Olofson argues that this migration is a time when some shops might want to consider migrating to Azure instead of on-premises, since the Azure version is mostly similar to the on-premises product. “It shouldn’t be a one-off project, it should be part of an evolving strategy. It could be a part of a program for using the program for using the cloud for development testing in general,” he says.

Potentially pain-free migration

There are two steps to a SQL Server migration; the database itself and apps built on the database. If it’s just the database, that’s a whole lot faster than a Server upgrade, says O’Connor. “Just migrating the SQL engine is a two-hour process. You can move the engine and point back to data on a SAN. When it becomes apps that talk to database, that can take time,” he notes.

Turley says Microsoft tools like SQL Server Upgrade Advisor and Installation Center make it simple to install and migrate the product, plus analysis and reporting services migrate. For SQL Server 2014, there is an ISV certification program so you can test your custom database apps against 2014 to help with the move.

“Let’s say they use Web services and custom extensions. We can migrate a lot of those things. In general there is a lot of prescriptive guidance around making that migration. It all depends on how custom that install is,” he says.

If there is a challenge, it could be finding a skilled DBA who knows how to use the old versions. DBAs trained in recent years haven’t been trained on the 2005 version and earlier. “Most new DBAs have never even seen SQL Server 2000. Finding a junior to mid-level DBA to support it can get challenging quickly,” says Turley.