MSSQL Upgrade
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.
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-ver15Backup-Restore Method
TODO: AlwaysOn Upgrades
Bibliography & References
https://docs.microsoft.com/en-gb/archive/blogs/psssql/sql-server-2012-setup-just-got-smarterhttps://www.brentozar.com/archive/2020/12/sql-constantcare-population-report-fall-2020/https://www.brentozar.com/archive/2015/03/why-you-shouldnt-upgrade-sql-server/https://www.brentozar.com/archive/2019/01/which-version-of-sql-server-should-you-use/
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
AlwaysOnhttps://medium.com/tech-at-nordstrom/successfully-upgrading-both-windows-sql-server-without-downtime-e1fde448b18bhttps://learn.microsoft.com/en-GB/sql/sql-server/failover-clusters/windows/upgrade-a-sql-server-failover-cluster-instance
Upgrade Assistant(1) https://social.technet.microsoft.com/wiki/contents/articles/2558.upgrade-assistant-tool-for-sql-server-2012-ch-1-of-14.aspx
Upgrade Advisor(2) https://social.technet.microsoft.com/wiki/contents/articles/2642.upgrade-assistant-for-sql-server-ch-6-of-14-run-upgrade-advisor.aspx
SQL2022https://www.brentozar.com/archive/2021/11/is-microsoft-sql-server-2022-a-big-release/
SQL2019https://www.techrepublic.com/article/sql-server-2019-ctp-2-1-a-first-look
SQL2016https://www.zdnet.com/article/microsoft-sql-server-2016-will-be-generally-available-june-1