MSSQL Upgrade

MSSQL-Matrix
MSSQL-Matrix

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

MSSQL-Matrix

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.

For full information see: https://docs.microsoft.com/en-us/sql/relational-databases/databases/upgrade-a-database-using-detach-and-attach-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15 

Backup-Restore Method

TODO: AlwaysOn Upgrades

Bibliography & References