Not all database upgrades require the same level of painstaking planning and effort as the biggest, most complex...
projects do. Sometimes, you can use a simpler methodology for your Microsoft SQL Server upgrade, lose nothing in the process and save time for whatever you plan to do next. However, if you want to simplify the upgrade process, it's vital to know when it's safe to make things easy on yourself and when you have to put in the extra effort.
The goal of this article is to provide you with a framework for deciding the right level of effort and how much of your resources to dedicate to the task of upgrading the particular database in question.
Classification of SQL Server upgrades
There are a number of factors that you can use as indicators for how much effort your upgrade is going to take. Here, I'll discuss some of the most common ones. There are others that will play into the decision about the level of effort required for SQL Server upgrades, but this should serve as a starting point.
Third-party applications. In many organizations, the majority of databases are purchased through independent software vendors (ISVs) that provide their own prescribed process for upgrading to a more recent version of SQL Server. If the database you're looking to upgrade came from a third-party ISV, following the application vendor's prescribed upgrade path is the best course of action. That will predetermine how much work needs to be done to complete the upgrade.
Mission-critical applications. A mission-critical application is going to require a more robust upgrade plan and much more testing. For a lesser application, the effort should be lower because the risks of a Microsoft SQL Server upgrade breaking some functionality, and the work needed to recover from that, won't have as much of an impact on the business. This factor is also closely aligned with whether or not the current database architecture has a high availability and disaster recovery component. If there is, it's to be expected that minimizing downtime is paramount. This adds to the preparation time for the upgrade plan and testing of the upgrade itself.
Ad hoc SQL. It adds to the amount of upgrade effort needed when the application running on a database generates a substantial amount of SQL queries that aren't embedded in stored procedures. Tools like Microsoft's built-in Upgrade Advisor examine the metadata of the database itself, including stored procedures and other database objects. Nonetheless, Upgrade Advisor is still helpful here because it can examine trace files for ad hoc SQL statements. The issue that typically arises is capturing and analyzing particular trace files that provide substantial SQL code coverage.
Large databases. The definition of large is subjective in this case and changes as technology advances. For example, a 1 TB database was considered a large database 10 years ago, but is not uncommon in today's landscape. The size in terms of gigabytes or terabytes and that number of database objects can affect the time it takes to upgrade a database, and for a rollback to be done successfully if one is required. (I'll cover rollbacks later in the article.)
Replication. This won't affect all organizations, but if there's SQL Server replication in your database environment, the order in which the replication components are upgraded is critical so as to not break it during the upgrade process. Replication can become a major factor in which systems are addressed and in what order, which can affect how much of an effort will be needed.
Upgrade project classification
Once you consider the various factors, you can classify your Microsoft SQL Server upgrade project according to how involved it's likely to be. Here are several types of upgrade projects, arranged from most-to-least complicated; their relationship to each other and to the upgrade factors can be seen in figure one.
Complex upgrade. This is the type of upgrade project that takes significant effort in planning and execution. It's crucial to make sure that everything is accounted for and tested during the process, in order to minimize the database and application downtime associated with the upgrade. These projects tend to be stressful, and extra effort needs to be taken to ensure success. Often, there is formal project planning and management to coordinate the efforts from across the organization, including a test run of the process and the failback plan in case something goes wrong.
Basic upgrade. This type of project takes into consideration some basic testing, but it usually doesn't result in the deployment of teams of workers to complete the tasks at hand. Even so, it's still a serious effort, involving mission-critical applications, and reasonable precautions should be taken. At a minimum, that includes backing up the database before upgrading and creating a solid failback plan.
Noncritical upgrade with failback plan. This type of process is used for those databases that are important in their own domain, but not critical to the business or management of regulatory compliance issues. Here, the database is upgraded with the expectation that the failback plan will be put into motion if circumstances warrant.
Vendor-guided plan. This is perhaps the most common Microsoft SQL Server upgrade project. It can be as simple as the vendor stating that it now supports version X. Or it may be more complicated. For instance, you could have to implement a new version of the front-end application in conjunction with the database on the back end. The particulars will vary quite a bit from vendor to vendor. You may want to treat this as a basic upgrade project internally.
As the classification of databases and upgrade projects is determined, be sure to get sign-off on plans from the internal application owners. Set expectations and manage them around the level of effort and the projected benefits of the upgrade. At the minimum, Upgrade Advisor should be your starting point for all SQL Server database upgrades with attention to the remediation of issues that are discovered. In no way should an upgrade be done without basic due diligence.
What to think about when upgrading away from SQL Server 2005
DH2i's containerization service ups high availability and disaster recovery
See if you know what it takes to upgrade to SQL Server 2016