Benefits - Analyse the benefits of a Sql Server upgrade. Upgrades can be a significant effort , consideration should be given benefits and opportunity cost arising from dedicating resources to an upgrade
Related Articles
Application Lifecycle -
Mainstream support includes functional, performance, scalability and security updates.
Extended support includes only security updates.
Analyse these support levels in the context of your organisations requirements . If the organization has a large footprint with a large multi-year upgrade cycle - than that will have different considerations to smaller scale
Discontinued & Deprecated features - An important aspect of a SQL Server upgrade is to consider any discontinued and deprecated features , that may negatively impact an application.
-SQL Server 2019 does not introduce breaking changes.
- SQL Server 2019 does not deprecate any features beyond those deprecated in prior releases,
- SQL Server 2019 discontinued features - Starting with SQL Server 2019 db scoped configuration option names have changed:
DISABLE_INTERLEAVED_EXECUTION_TVF changed to INTERLEAVED_EXECUTION_TVF
DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK changed to BATCH_MODE_MEMORY_GRANT_FEEDBACK
DISABLE_BATCH_MODE_ADAPTIVE_JOINS changed to BATCH_MODE_ADAPTIVE_JOINS
- SQL Server 2017 - No features discontinued
- SQL 2016 - From sql 2016 - sql server is 64 bit ( some components run as 32 bit)
- compatibility level 90 discontinued
- Active X subsystem
- Secure Sockets Layer discontunued , switch to TLS
Database Compatibility Levels - is a database level setting , influencing how the database functions and how SQL Server uses certain features. It causes them to act like a specific SQL Server version and allows a degree of backward compatibility. It is a database property - therefore the compatibility level affects database level features only.
From Microsoft docs "For pre-existing databases running at lower compatibility levels, as long as the application does not need to leverage enhancements that are only available in a higher database compatibility level, it is a valid approach to maintain the previous database compatibility level"
Microsoft has worked on getting this right. Upgrading SQL Server to a new version, has become a much less onerous task from an application certification perspective. It is in Microsoft's interest to decouple the application code . Read more on How to use Database Compatibility to increase your migration success
Remain flexible with flipping database compatibility levels.
- 1) You are not stuck at a certain level
2) Since 2014 - the Cardinality Estimator and the Query Optimizer changes are tied to the database compatibility level
Query Optimiztion is made up of a Cardinality Estimate, which then feeds into the query cost model.
Cardinality estimation (CE) (or Cardinality Estimator ) in SQL Server is derived mainly from histograms, generated when indexes or statistics are created.
Performance enhancements . Compatibility Level 150
- Intelligent Query processing(IQP) by default
- IQP defined as query processing and execution features with wide impact , enhancing performance of existing workloads with minimal implementation effort to adopt.
- zero need to refactor workloads in many cases
- Memory Grant feedback - minimise diskspill - memory allocation up or down for increase resource efficiency -Batch or Row Mode
- Anti-Patterns :
- T-SQL scalar UDF inlining - moving from iterative to set based
- Table variable Deferred compilation - using cadinality at first compilation rather than fixed guess
Security enhancements - A number of security enhancements
- Data Discovery & Classification - classify\label columns in user tables. Facilitates monitoring access to sensitive data & Identifying location of sensitive \regulatory data
New servers or inplace - I prefer new servers. Roll back plan if user acceptance testing doesn't go to plan
Preupgrade checks - e.g identifying connection drivers , cname aliases, 3rd party components e.g backup
