How to conquer the SQL Server 2005 migration challenge
As we discussed when we outlined the challenges of migration, it's believed most enterprises have long since moved off the old database in favor of something newer, either on-premises or possible in the cloud. At best, SQL Server 2005 has been relegated to simple report generation on noncritical data.
But if it hasn't, there's good news and not-so-good news. The good news is if you just have to migrate the data, and that can be done in as little as 30 minutes, so long as you don't store your data on the same server as the database app. The not so good news is if you have a lot of apps running on top of the database, your migration could take a little or a lot longer.
Help starts with Microsoft, which has tools like SQL Server Installation Center, which makes it simple to install and migrate the product from an older version to new. There is also an ISV certification program for SQL Server 2014 so you can test custom database apps against SQL Server 2014 to test for compatibility.
Carl Olofson, research vice president for data management software research at IDC, thinks app migration should be relatively simple to do. "App migration shouldn't be a problem except queries written for idiosyncrasies for older versions. But the SQL itself should be totally compatible. I'd be real surprised if there was an issue with apps," he says.
If you've written app in-house, you might need to recompile it but he does not think you will need to change the source code drastically. "If you have a lot of apps, that could be a hassle. You'll need to set up test systems which could be expensive and time consuming," says Olafson.
Given SQL Server 2005 is 11 years old, though, there's liable to be some problems, namely vendors that have gone under. "The thing that kills upgrades is the magical phrase 'my vendor is no longer business,' which is shockingly common these days. A lot of vendors no longer exist from 2005. So you are on your own," says Denny Cherry, president of Denny Cherry & Associates Consulting and a Microsoft MVP.
[Related: What end of support for SQL Server 2005 means for CIOs]
Government customers will be particularly impacted because of regulations. Cherry says stating in the certification documents that the app is certified on "SQL Server" vs. "SQL Server 2005 R2" can make all the difference, because one certification is open-ended while the other ties the certification to a specific version.
"It's the stupidest thing you've ever heard but that's the federal government," he says. "It costs huge amounts of money to get recertified on the new platform and some don't bother."
Fortunately, that isn't a widespread a problem. Many of those companies get bought, so someone picks up the support, Cherry says, although smaller vendors might have been allowed to die off without being acquired.
Beyond that, Cherry says there shouldn't be any upgrade problems. "Microsoft has done a good job of making sure the upgrade process goes really smoothly. The only hard apps are the bigger ones," he said.
Tiffany Wissner, senior director for data platform marketing at Microsoft, says that through the versions Microsoft has retained "near complete compatibility" of SQL code. That way customers can migrate apps to any version, including the Azure versions of SQL Server, which are near-feature identical from the on-premises versions. "If there were previous apps and they want to move to the cloud, they can move both the app and the database to the cloud and maintain compatibility," she says.
When Microsoft first launched Azure Cloud SQL, it was not feature complete with the on-premises version, which the company admits might have put people off. Microsoft has been adding features since then, bringing the cloud and on-premises version to near-parity. "If [customers] looked a year ago, they should look again because we closed those core compatibility gaps that may have had them look at other options," says Wissner.
However, a migration is not something you can do alone. There are still liable to be challenges and issues from migrating old apps and an old database, and even if that goes smoothly, there is liable to be a challenge with third-party apps, notes Greg O'Connor, CEO of AppZero, a Windows Server 2003 migration specialist which has also done SQL Server migrations, since Server 2003 has passed its end-of-life and customers are doing total upgrades and not just an OS upgrade.
"Often SQL machines have other pieces tied to them. You have things like configuration settings, reporting services, and analytical services. The bare bones SQL engine is easy [to migrate] but by the time you layer the services and configurations, you have dump and reload and the exact configuration, which might have to be done by a DBA," he says.
And then there's the licensing. Microsoft changed the licensing model from SQL Server 2005, where it charged on a per-socket basis, to a per-core basis in newer versions. "Many people don’t want to do a one-to-one upgrade because of this. They might want to consolidate. So there is a desire to do database consolidation, which means a topology change," says O'Connor.
A final potential gotcha: third-party ODBC drivers that will probably have to be upgraded after ten years as well.
The deadline is now just four months away but Microsoft has been beating the drum for some time now, just like it did with Server 2003 and Windows XP. "The move will require planning to decide what apps are modernized and how you want to do it, on-premises or in the cloud, is why we start the awareness early," says Cherry.
Olofson says most people who have not made the migration have held off for cost, since many IT budgets are tight. This might be a time when some shops might want to consider migrating to Azure. "The Azure SQL database is similar but not the same. That will require source changes, but if you're going to go through changes you might want to consider this. So instead of buying new servers, the alternative is to set everything up in a cloud environment," he says.
"It shouldn't be a one-off, it should be part of an evolving strategy," he adds. "It could be a part of a program for using the cloud for development testing in general."