Everything Dev Needs to Know About SQL Server 2019 Cumulative Updates

As SQL Server 2019 continues to evolve, Microsoft is releasing cumulative updates (CUs) to address bugs and inject new features on an ongoing basis. Devs who work with SQL Server need to stay up-to-date on these updates to ensure optimal performance and security.

What Are Cumulative Updates?

Cumulative updates are packages that include all previously released updates for their respective SQL Server version. These updates provide bug fixes, security updates, and other enhancements. SQL Server 2019 has seen several cumulative updates released since its debut.

How to Check for Updates

Before diving into the details of specific cumulative updates, Dev needs to know how to check for updates in the first place.

There are a couple of ways to approach this:

Why Are Cumulative Updates Important?

There are several reasons why Dev needs to stay on top of cumulative updates:

• Bug Fixes: Cumulative updates address known bugs and ensure smooth operation of SQL Server.
• Security Patches: Regular updates help to keep SQL Server secure and less vulnerable to attack.
• New Features: Some updates may introduce new functionality or tools to use with SQL Server.
• Compatibility: Some updates are designed to improve compatibility with other Microsoft tools and services.

How Often Are Cumulative Updates Released?

Cumulative updates are released regularly, but not on a set schedule. Microsoft releases them to address specific issues or to introduce new functionality.

Overview of SQL Server 2019 Cumulative Updates

Let’s take a closer look at the specific cumulative updates that have been released for SQL Server 2019:

Cumulative Update 1 (CU1)

Released in November 2019, CU1 introduced several new features and enhancements, including:

• PolyBase enhancements
• Query Store enhancements
• Intelligent Query Processing enhancements

This update also addressed several known bugs and security vulnerabilities.

PolyBase Enhancements

With CU1, PolyBase includes support for additional file formats, including JSON and ORC. There are also improvements to the connector for Oracle databases and better handling of Hadoop credentials.

Query Store Enhancements

Query Store received several improvements, including the ability to configure its retention period and better handling of queries with missing execution plans.

Intelligent Query Processing Enhancements

CU1 introduced several improvements to Intelligent Query Processing, including changes to how SQL Server handles table variables and the introduction of batch mode on rowstore for batch mode operators.

Cumulative Update 2 (CU2)

Released in January 2020, CU2 focused on bug fixes and performance improvements rather than new features. This update addressed issues with SQL Server’s built-in functions and temporal tables, among other things.

Built-in Functions

CU2 addressed a number of issues with SQL Server’s built-in functions, such as incorrect results or crashes. These issues affected functions including CONCAT_WS, TRIM, and STRING_SPLIT, among others.

Temporal Tables

Temporal tables are a new feature in SQL Server 2019. CU2 addressed several issues related to these tables, including error messages and performance problems.

Cumulative Update 3 (CU3)

Released in March 2020, CU3 introduced several new features and enhancements, including:

• Intelligent Query Processing enhancements
• Query Store enhancements
• New database-scoped configuration settings

CU3 also addressed several known bugs and security vulnerabilities.

New Configurations

CU3 introduced several new database-scoped configuration settings, including support for UTF-8 character encoding and the ability to control whether SQL Server caches query plans for ad-hoc workloads.

Intelligent Query Processing Enhancements

CU3 introduced a number of improvements to Intelligent Query Processing, including improvements to scalar UDF inlining and better handling of table variables.

Query Store Enhancements

Query Store received several improvements in CU3, including the ability to view execution plans for each version of a query and support for compatibility level 150.

FAQ About SQL Server 2019 Cumulative Updates

What Is the Latest Cumulative Update for SQL Server 2019?

The latest cumulative update for SQL Server 2019 is CU7, which was released in September 2020.

How Do I Install a Cumulative Update?

To install a cumulative update, download the appropriate package from Microsoft’s website and run the setup program. You will need administrative privileges on the SQL Server instance to run the setup program.

Do I Need to Install Every Cumulative Update?

While it’s a good idea to stay up-to-date on cumulative updates, you don’t necessarily need to install every single update. It’s generally a good idea to install updates that address critical security vulnerabilities or that fix bugs that may be impacting your SQL Server installation. However, if your installation is stable and you haven’t experienced any issues, you may not need to install every update that comes out.

Are There Any Risks to Installing Cumulative Updates?

Installing cumulative updates is generally safe and won’t cause any issues with your SQL Server instance. However, it’s always a good idea to back up your databases before installing an update, just in case something goes wrong.

How Do I Roll Back a Cumulative Update?

If you encounter issues after installing a cumulative update, you may need to roll back to a previous version. To do this, uninstall the cumulative update as you would any other program. This will restore your SQL Server instance to its previous state. However, any data changes that were made while the update was installed will remain.

Conclusion

Cumulative updates are an important part of keeping SQL Server 2019 running smoothly and securely. Devs who work with SQL Server should stay up-to-date on the latest updates and install them as needed to ensure optimal performance.