SQL Server 2005 End of Life Migration Tips

SQL Server 2005 End of Life Migration Tips

November 9

April 12, 2016 was the end of life for one product. Some might even call it the end of an era. On that day Microsoft officially ended extended support for its enterprise database software SQL Server 2005. What that means is the company is no longer distributing updates for new features, application bugs, or security issues. If you’re among the 800,000 or so IT departments still using SQL Server 2005, this impacts you in a big way.


Afterlife concept as an ecg or ekg medical heart monitor lifeline showing a flatline transforming into white doves flying upward towards heaven as a spiritual faith metaphor for believing in life after death.

What’s are the Risks?

SQL Server is a database server that provides an efficient way to store and manage information. The 2005 version was heralded for making enterprise data sets easier to deploy, optimize, and keep under wraps. But a lot has changed since 2005. Even small companies are now sitting on ‘big data’. Most of it is unstructured data, growing at an overwhelming rate. If you factor in the many major changes to supporting OS platforms, it’s clear that any software launched back then is probably outdated. Just a tad.

Companies that started early with SQL Server 2005 enjoyed roughly a decade under Microsoft’s protective wing. That’s counting five years of mainstream support with another five years in extended support. It’s been an epic run. But while database administrators have been on cruise control, cyber attackers have been hard at work. Ten years is ample time for hackers to poke new holes and exploit vulnerabilities in an application that was fairly secure at one time. By ending support, Microsoft essentially retired the security guards and left the goods unprotected.

Once the life cycle of a given product ends, users basically have two choices: continue to use the product as is, or upgrade to something better. Of course Microsoft would prefer the latter, but believe it or not, its reasons aren’t purely selfish. Continuing to use a an unsupported product exposes you to security risks, stability and compatibility issues. In fact, every application connected to SQL Server could suffer due to lack of support and simply being out of sync over time. The longer you operate like this, the more problems you’ll encounter.

virtual migrations meme

What’s Holding You Back?

By now we know that more than a handful of organizations have opted to keep using the live wire that is SQL Server 2005 despite knowing the risks. Cost is one of the biggest reasons why. Costs for the upgrade may include professional migration services, licensing fees, and any additional software or hardware. You must also take into account the cost of downtime. Free database software might save you money upfront. But those savings will likely fly right out the window if it somehow attributes to downtime you didn’t plan for.

The sheer amount of data that needs to be moved is another potential stumbling block. Ten years of SQL Server could mean many petabytes of information, much of it with corporate, customer, and compliance-related implications. Both core and supported data sets such as login credentials and user permissions must be successfully migrated to the new platform. With so much sensitive data caught in between and everything that could potentially go wrong during the move, you can at least sympathize with those who have decided to stand pat.

Best Practices For SQL Server 2005 Migration

Migrating from a data-intensive application like SQL Server is no walk in the park. Here are some best practices to consider when setting your plans in motion:

Pick a Platform

So you’ve decided to let go of SQL Server 2005. Wise decision, but where do you go from here? You have a few options. There’s SQL Server 2014 as well as the recently launched 2016 edition. SQL Azure, which offers database management in the cloud, is another option to consider. If the best of both worlds sounds appealing, you may even want to consider the hybrid route. Blending SQL Azure with one of the newer SQL Server platforms will allow you to benefit from the flexibility of shuffling your database operations between on premise and cloud environments.

While upgrading to another Microsoft platform is probably the easier, more logical move, stepping outside of that ecosystem is yet another option on the table. For example, there are open source alternatives such as long-time SQL Server rivals MySQL and Postgre SQL. And now that big data is all the rage, the enterprise is steadily ditching the relational database model in favor of scale-friendly concepts such as NoSQL. There is no right or wrong and luckily for those in transition, no shortage of viable alternatives.

Evaluate Manpower and Expertise

Migrating from a complex application like SQL Server will no doubt challenge your IT personnel. Even if the system is user-friendly, staff will most likely need tospend time to learn new features, processes, and policies. Things get even more complicated when your team performs the actual migration. The more they have to learn or the more they stumble, the more time it takes to move over. Depending on your manpower and expertise, it may be a good idea to either choose a migration solution with a minimal learning curve, or hire an outside firm for the job.

Plan For Downtime

In some cases SQL Server is not only a database administration tool, but the very backbone of mission-critical applications. For instance, an online merchant may use an SQL Server database to power its shopping cart software. In this case you’d want to migrate your data while making sure customers can continue to securely make purchases from your web store. Obviously downtime is best avoided, but what’s best is not always what’s realistic. Determining what is and what is not acceptable downtime is crucial when drawing up your migration strategy.

Maintain Data Integrity

The ability to maintain integrity can make or break the migration efforts. No matter how much or how little you have, you need to ensure your data remains consistent throughout the process. That means everything is transferred over to the platform successfully and functions the way it did prior to the move. Any data corruption or loss could hinder the migration, which might mean downtime you didn’t plan on. When it comes to maintaining integrity, the right tools can make all the difference. And that leads  to our next section …

Make a backup of hard disk reminder. Calendar with text and hard drive. Selective focused on text backup and magnetic head.

Come Strong With Your Backup Plan

In the event that something goes wrong during an SQL Server migration, recovering from backups may be the only way to restore any lost or corrupt data. A comprehensive backup plan is crucial here because it gives you the power to bounce back from disaster and validate data after the move. For example, the team may discover database inconsistencies. But IT can compare those attributes with those of the databases in your backup copies and address them accordingly. A good backup strategy will provide data integrity, validation, and protection for your migration project.

If SQL Server was a ticking time bomb before, that bomb has surely exploded by now. Of course the system still works, but those who think it will continue to run reliably are only kidding themselves. You can keep gambling with your data and putting your business at risk, or get the upgrade and have peace of mind. What will it be?