A Quick Guide to SQL Server 2005 End of Support

As Brad Sams reported back in October, Microsoft is terminating extended support for SQL Server 2005 on April 12^th. This means that, unless you pay hundreds of thousands or millions of dollars for a custom support contract, Microsoft will terminate all support for SQL 2005. No more technical support, no more hotfixes, and no more security fixes.

Microsoft’s End of Support Help Page

As Microsoft has done before for products, such as Windows XP, Microsoft has published a website specifically to deal with the challenges of upgrading or migrating customers from SQL Server 2005.
On this site, you can learn why you need to upgrade from SQL Server 2005, tools that will help you upgrade or migrate, and several different guides to help you through the process. There are also useful links to Microsoft services, videos, whitepapers, tools, and documentation, so this site should be your upgrade starting point.

Microsoft’s Free Tool for Finding SQL Server 2005 Installations

The label “accidental DBA” was coined in response to the organic way that SQL Server pops up on servers. Many line-of-business (LOB) apps include some version of SQL, from the free Express Edition all the way up to the Enterprise Edition. These ‘shadow databases’ often go unnoticed for years and can be tricky to deal with when trying to find those hidden-away database servers to ensure that they don’t become a security or technical support risk to the business.
If you already have a software auditing service, such as System Center Configuration Manager (SCCM), then finding SQL Server 2005 installations will be just a matter of running a report. If you don’t have that tooling, then Microsoft has a free tool for you. It always puzzles me, but few people know about or use the free Microsoft Assessment and Planning (MAP) Toolkit. If you follow Microsoft’s deployment best practices for Windows clients, Windows Server, SQL Server, and more, then all upgrade projects should start with MAP. This tool will reach out to your network to discover and audit machines. A database is populated and, you can run a series of reports to identify SQL installations, licensing types, and editions.

Is an Upgrade Possible?

Once you have identified all SQL 2005 installations, you can start planning. But we don’t live in a simple world where all things are possible.
There are a number of things that will prevent upgrades from happening. What that in mind, you should still try to balance the real business risks of running outdated and unsupported software in production:

• Obsolete critical LOB apps: My customers (resellers) encounter this scenario a lot. Some medical or manufacturing system runs on an old piece of software and an upgrade is impossible because the LOB app doesn’t have any new versions. The company is stuck with the old software. All possible measures should be taken to isolate those systems that are a business risk so that they cannot be infected or be used as a point of an attack.
• Budget/Time: The business might decide that there just isn’t bandwidth or funding available to perform an upgrade, as a database upgrade can have wide-reaching implications. Ideally, the risks and rewards should be measured with an open mind and a business decision should be reached while documenting the understood risks. And who knows, there might be some options that will assist with funding issues.
• Opposition: I’m not going to call it anything more flattering; some people just won’t upgrade because they won’t.

SQL Server 2005 Replacement Options

There are three options available if you decide to replace a SQL Server 2005 installation:

• Upgrade to a newer version of SQL Server.
• Migrate to Microsoft Azure virtual machines.
• Migrate to Azure SQL Database.

Of course, there are other options, such as completely replacing an application, but I’m focusing on upgrade and migration scenarios in this article.

Upgrade to New Version of SQL Server

Let’s start with the technical side of things. I’m a not a SQL expert. Instead, I’m the perfect example of an accidental DBA. With that said, there’s tooling to assist, such as the SQL Upgrade Advisor.
Your biggest issue with upgrading to a newer version of SQL Server is licensing. You will need Software Assurance (SA) for SQL Server and the SQL Server CALs if non-processor licensing was purchased. If you don’t have SA already, then you cannot add it; you’ll have to purchase all-new licensing.
You might decide that instead of doing an upgrade of a machine that probably is also running Windows Server 2003/R2, you will deploy a new machine with Windows Server 2012 R2 and SQL Server 2014. If so, check out the SQL Database Migration Wizard.

You can minimize that spending if you are careful with your SQL designs in mid-to-large environments. The first option is to start consolidating your SQL installations. You don’t necessarily need one SQL Server installation for every LOB app. Maybe you can consolidate to many SQL instances on one server/cluster?
If you have live migration of SQL Server, then every host must be licensed for SQL Server. Larger customers that are using virtualization might deploy a virtualization cluster that will have the sole role of hosting SQL machines. This reduces the number of hosts that must be licensed for SQL Server. Talk to your licensing supplier to learn more about how this solution can save you money.

Migrate to Azure VMs

The cost of an Azure virtual machine that runs Microsoft software includes:

• The cost of running the virtual machine.
• The cost of Windows Server, without the need for any CALs.
• The cost of pre-installed software, including SQL Server, without the need for any CALs.

For example, I could deploy a D3 virtual machine, which is four cores and 14 GB RAM, in North Europe with the cost of Windows Server and SQL Server Standard included and no CALs required for $682 per month.

• VM with Windows: $384
• SQL Server Standard: $298

Remember, that includes the machine, the software, and no need for any CALs. This is a monthly drip feed instead of a big bang up-front expense. As with the upgrade scenario, I can deploy a smaller number of machines to consolidate the expense of SQL Server.
Note that there would be additional costs for storage, site-to-site networking, Azure backup, and so on.
Microsoft has even done quite a bit of work to simplify the deployment of SQL in Azure virtual machines, which is great news for us non-experts in SQL Server.
This is a migration scenario, so once again, you should look at the SQL Database Migration Wizard, in conjunction with the advice from Microsoft for this scenario.
If you have SA for your on-premises licensing, then talk to your licensing reseller about options for moving your licensing to Azure to reduce the above costs.

Migrate to Azure SQL Database

I suspect that this option will be the least commonly adopted one for SQL Server 2005-based LOB applications, but it might be good for some. Azure SQL Database is an Azure service that provides you with capacity to run databases in the cloud, without the pain of deploying or upgrading SQL Server ever again. You get a hugely scalable platform that Microsoft supports, and it reduces your workload and business risks. It sounds perfect, right? But unfortunately, the SQL client must be compatible with SQL Serve. For example, I cannot use Azure SQL Database instead of SQL Server for System Center.

Start Planning Your Migration Path Now

So, there you have it. Assuming that you aren’t switching to MySQL, Oracle, or something else, Microsoft gives you more options than ever for replacing obsolete versions of SQL Server. There’s plenty of documentation and tooling to help. My advice is that you start planning now because databases aren’t easy to upgrade or migrate, and changes to them include technology and business risks. Use the tools that are available — for example, try an Azure Replica test failover and do the test upgrades and documentations in the cloud with identical installations before impacting on-premises production systems.
Please share any ideas or advice that you have with us below.