It was interesting to see the results of my straw poll last week on SQL Server 2005. Quite a few people still run that version, and some aren't looking to upgrade anytime soon. However when support ends, you should be aware that there is potentially an impact to your organization.

If you work for a government, or you are bound by regulations such as PCI, HIPAA, or something else, you need to upgrade. I would hope that an audit sometime in the last 3-4 years would have alerted you to the issues of running unsupported software, but given the wide variability in auditors in the world, perhaps not. In any case, consider this a warning that you should be sure any databases that contain data regulated by law are upgraded by April 12, 2016. Otherwise your organization will likely be out of compliance with the regulations.

If you haven't upgraded, and want to avoid an upgrade for the next decade, this is a tough time. I'm not sure that SQL Server 2016 will RTM before support ends, which is the version you'd like to pick. This means that counting on this event is unwise. Even if you were sure SQL Server 2016 will release on April 1, is that really enough time to test your application?

That's really the bottom line for most of us. We need to test and be sure that an upgrade will, in fact, work for our particular environment. We need extensive testing, which probably means that those of us aiming to upgrade should be aiming for SQL Server 2014 (or SQL Azure Database). Microsoft would like you to choose one of these, as it benefits their bottom line, and they are pushing customers to upgrade. However, that's somewhat the nature of software. We need to upgrade at times in order to maintain our systems, and vendors won't support old software indefinitely.

There are a couple months left, and I think you'll need all that time to be sure your software still runs fine. I'd recommend you use new hardware, a completely separate instance (side by side upgrade) and use Distributed Replay to capture a workload on your existing instance and replay it on the upgraded server to check that your system performs well. To do this, you really need to be sure you are monitoring your current (and the upgraded) server to measure timings and performance.

Monitoring isn't simple, but it's not that hard either. You should always be monitoring your system (we use SQL Monitor at SQLServerCentral), as well as keeping baselines, including wait statistics, that you can use to analyze the performance of your database. This is invaluable in determining if your new database will outperform the old (hint, it should).

However you approach your upgrade from SQL Server 2005, be aware that you need to be on SP4. I recommend you use the Upgrade Advisor, perform a side by side upgrade, and don't be afraid to use the compatibility level to allow the database to continue to behave as a 2005 instance. Above all, be sure you have good backups you've tested, just in case things fall apart.

Lastly, SQL Server 2008 and SQL Server 2008 R2 are both out of mainstream support, so don't forget about future planning for those versions as well. Best of luck with your upgrades, and please let us know if you find specific issues or problems in upgrading. Post a note in the forums to get help, or maybe help someone else complete their own upgrade.