Ask a DBA about SQL Server upgrades and listen to the replies:
“Upgrades are difficult.”
“Upgrades are painful.”
There is a lot of uncertainty involved with SQL Server upgrades, and a lot of painful memories. The truth is that the upgrade process itself is straightforward. It is easy for someone to install new software on new hardware and then restore backups.
It is easy to understand why anyone would be cautious about upgrading a database server. Data, and databases, are the most critical asset any company owns.
The secret to a successful upgrade is understanding the impact of early decisions. Small decisions at the start have major ramifications later.
SQL Server upgrades can be either simple or complex. Simple upgrades are taking a database server and upgrading in some manner. Complex upgrades are best planned like any other IT project. Complex upgrades include upgrading clusters, or multi-instance servers.
But even simple SQL Server upgrades can be high-pressure situations with lots of moving parts that need execution in the correct order. Many business units will be involved in each step of the process, adding to the complexity.
And as if that wasn’t enough pressure, at some point you will need to decide if you must rollback to the earlier version. If you do decide to rollback, you had better have a solid plan in place. Do not leave anything to chance. Don’t think of a rollback plan as a failure option. Think of your rollback plan as a way for your business users to be confident that if something goes wrong that the system(s) can be restored to an acceptable working condition.
I’ve written a handful of posts over the years on the topic of upgrading SQL Server, so I decided to put together a page that contains links to all the posts. I am hopeful that you will find everything you need here for a successful SQL Server upgrade or migration project.