Preparation

SQL Server Upgrade Assistant

Upgrade Assistant guides you through the steps required to create a test workload on an instance of SQL Server ..., upgrade the database  to a later version, and replay that same workload to identify differences in behavior, if any. (1)

SQL Server Upgrade Advisor

Upgrade Advisor analyzes the objects in your SQL Server database, and generates a report that identifies issues to address to ensure a successful upgrade.   Typical issues are deprecated or changed TSQL syntax and deprecated SQL Server features that your application is using. (2)

Note that many of the issues that the Upgrade Advisor finds can be detoured by setting the COMPATABILITY LEVEL to an earlier version. (2)

In-Place Upgrades

Side-By-Side Upgrades

Detach-Attach Method

It is possible to create a new SQL Server and Detach databases from one version and Attach them to the new version. In many cases this can be acheived without enabling any new functionality. i.e. the new database remains compatible with the old database meaning the behaviour of the database will not change. However it is recommended to schedule in a separate step to increase the compatibility level in order to avoid problems with later upgrades.

Backup-Restore Method

AlwaysOn Upgrades

1. Verify that source version to target is a supported upgrade path (using the table from the In-Place Upgrades section above)

2. On your AG, disable or suspend automatic failover to avoid unintended failovers during the upgrade.

3. Upgrade the secondary replicas first (one at a time) to your target version.

4. Failover the AG so that the new primary is on the target version.

5. Upgrade the old primary, now a secondary) to the target version.

6. Restore your failover configuration (failover mode, etc.).

7. Test everything.

Bibliography & References