SQL Server 2019 upgrade considerations

20 October,2021 by Tom Collins

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

 

 

 

 

 


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server 2019 upgrade considerations


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer