SQL Server 2019 upgrade considerations

Benefits - Analyse the benefits of a SQL Server upgrade. Upgrades can be a significant effort , consideration should be given to  benefits and opportunity cost arising from dedicating resources to an upgrade

Let's first look at some information to help decrease the risk associated to a SQL Server upgrade process & to help simplify the process.

SQL Server Application Lifecycle - Each SQL Server version gets 10 years support. 5 years in mainstream support & 5 years in extended support 

                                    Mainstream support includes functional, performance, scalability and security updates.
                                    Extended support includes only security updates.

If the organization has a large footprint with a large multi-year upgrade cycle - than that will have different considerations to smaller scale.

Gating of performance optimizations , feature enhancements and scalability improvements become attached to the latest version.

Discontinued & Deprecated  features - An important aspect of an  upgrade is to review 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 -  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 supports a degree of backward compatibility.  It is a database property - therefore the compatibility level affects database level features only.

Why is this important????

1)Upgrading SQL Server  to a new version, has become a much less onerous task from an application certification perspective. Decoupling the application code from the SQL Server version allows greater flexibility for version upgrades and long-term mobility onto other platforms such as Public Cloud .  Read more on How to use Database Compatibility to increase your migration success

2)We can 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.

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"

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

High level upgrade plan 

On new systems - Windows 2019

Pre

Preupgrade checks - e.g identifying connection drivers , cname aliases, 3rd party components e.g backup