As Bala Narasimhan from PernixData and I discussed in a webinar last week, SQL Server 2005 hit end of it’s extended support life on April 12th of this year. Any existing SQL Server 2005 instances should be first and foremost in an IT organization’s agenda for modernization this year. If you still have 2005 instances in your environment, the organization is now at risk. If undiscovered bugs or other issues come out of nowhere and cause trouble, your only course of action is to upgrade. So, why wait? Start the process now!

If you need ammunition to help convince your organization about the need to upgrade, just look at the lack of official support. That fact alone should be enough to push the organization to upgrade. If you need additional help, take a solid look at all of the new and compelling features included in SQL Server since 2005!

As discussed in the webinar, a thorough checklist should be developed and scrutinized to help an organization through the upgrade process. Following such a checklist can help you identify and remediate any challenges that might come from the upgrade process.

This list is by no means exhaustive. It’s my personal high-level checklist of things to watch out for, and feel free to add to this checklist anything that matters to you and your organization.

• Migration process
  • Side-by-side migrations are generally better than in-place upgrades.
  • In-place upgrades leave few roll-back strategies other than VM snapshots, while side-by-side migrations give you the chance to practice the migration and explore test copies of the application to look for any functionality changes.
  • Older installations of SQL Server 2005 could be 32-bit, or the Windows OS version could be 32-bit as well. Migrations help you finally get to 64-bit stacks and leave 32-bit in the dust.
  • What is your upgrade path? Backup and restore? Detach and attach? Replication? Log shipping? Determine your SLAs and select a migration strategy that fits the available migration window.
• Target instance
  • Is the new SQL Server instance going to be out of support soon? Is it as current as either the organization, licensing, or application allows? Is it fully patched?
  • Can the target hardware (and hopefully virtualization layer) handle the features you intend to use? For example, In-Memory OLTP has higher than expected hardware recommendations, and your target hardware needs to live up to these expectations or else it might artificially hold back performance.
• Performance expectations
  • Do you have ongoing performance baselines and benchmarks from the current SQL Servers to use for a performance comparison of the target environment?
  • Have you stress tested the target platform to determine if it can handle your workloads? Synthetic workload testing is a great place to start, but real-world testing can help you validate or rule out that the target platform will suffice.
• Code upgrade
  • Have you performed a high level check of the code with the SQL Server Upgrade Advisor? It helps you review the code for anything that it may find that could break as part of the upgrade process that you should fix ahead of the upgrade.
  • What about SSIS packages, SSRS reports, and SSAS process?
  • Do you have any unnecessary garbage inside the database that is being migrated? For example, look for unused indexes, log or temp tables, or anything else that could be cleaned up before you migrate the database.
• Application changes
  • Read the release notes for the target version of SQL Server. Then read them again.
  • Have you read the ‘breaking changes‘ and ‘behavioral changes‘ sections of Books Online? These documents contain great insight into any behavioral or other expected functionality in the engine that could change as part of the upgrade.
  • Is the application able to use the latest version of the connection libraries? For example, do you need to upgrade the SQL Native Client or ODBC drivers to take advantage of new SQL Server features?
  • Are any items in the new instances changing expected behavior of items such as query execution plans, ETL processes, long-running tasks, etc.? These can be identified well in advance with the test instance and validation processes.
  • Verify that the application functions as normal after updating the database compatibility level to current. Beware the 2014 cardinality estimator improvements, as occasionally I find applications that respond poorly to the changes and should be set to 2012 compatibility to maintain performance.
• Upgrade process
  • Perform the upgrade process as normal, and validate that things appear as normal.
  • Once completed, perform the following tasks:
    • Check all logs (SQL Server error log, Agent error log, cluster log (if applicable), Windows event logs, and any virtualization logs to ensure that nothing of importance is lurking.
    • Run DBCC CHECKDB WITH DATA_PURITY to help with any database that has been migrated forward since the bronze age by checking for values that are not valid for the table column datatypes.
    • Change the database compatibility level to current (or as high as you can go).
    • Rebuild all user database indexes and statistics. You may need to even go as far as updating statistics WITH FULLSCAN.
    • Execute a DBCC UPDATEUSAGE to correct any borked page and row counts.
    • Take a backup!

In addition to the technicalities of the migration process, step back a bit and look at the entire architecture around the data. Is it time to revisit any areas of the design? Is the HA architecture overly complicated and Availability Groups can help simplify the design and reduce management overhead? Do we look at consolidating databases or instances? Is it virtualized? Is it time to check out Azure SQL DB or an Azure VM for hosting this data in the cloud? What databases exist on these instances that have not been accessed in years?

Take the time to revisit these architectural decisions as part of the upgrade process. It will usually simplify your architecture, reduce the management overhead, improve availability, and increase agility in the datacenter. The business wins and you can sleep more soundly at night!