Beginning with SQL Server 2012, Microsoft has launched a new release of SQL Server every two years at most. These new releases put IT professionals in a situation where we must decide if updating to the latest version is worth the time and costs. To help with this decision, we have looked at the key features that are new or have been enhanced in the latest version, SQL Server 2022.

The new features of SQL 2022 are syntactical, making it easier to write and extract data. If you are already running your database in Azure, you will already be benefitting from SQL 2022 through auto upgrades in your system.

Analytics

Azure Synapse Link for SQL Server

This feature allows you to run near real-time analytics with minimum impact on the operational workload and reduces complexity because it doesn’t require using ETL processes to deal with data movement.

Always On and High Availability

SQL Server 2022 allows you to connect SQL Server instances to an Azure SQL Server managed instance, allowing you to offload read-only workloads to the cloud.

Security

SQL Server 2022 integrates with Microsoft Defender for Cloud in your VMs if you have installed the SQL Server IaaS Agent extension

Ledger

This new version of SQL Server introduces an implementation of blockchain. Other than crypto currency, blockchain has other uses for this technology and make it useful to guarantee the integrity of data. This is impossible to turn off in SQL 2022 making it more secure and prevents users covering tracks of changes.

Performance

SQL Server 2022 extends segment elimination capabilities to string, binary, guid, and datetimeoffset for scale greater than two.

Another interesting new feature is the introduction of an XML compression option in the CREATE TABLE and CREATE INDEX commands. This option allows us to compress off-row XML data for both XML columns and indexes.

Speaking of indexes, another very useful feature is included in this release: Resumable add table constraints. As its name suggests, now we can pause and resume the creation of constraints if they were created using the RESUMABLE = ON option. This option requires that you add the ONLINE = ON option.

Yet another index-related improvement, but this time on columnstore indexes, is the introduction of ordered clustered columnstore indexes to improve performance for queries based on ordered column predicates. This is done by sorting the data prior to index compression.

There are also changes in the way SQL Server manages files. Concurrent updates to global allocation map (GAM) pages and shared global allocation map (SGAM) pages reduce page latch contention while allocating/deallocating data pages and extents. The number of Virtual Log Files (VLF) created if the log file growth is less than or equal to 64 MB and more than 1/8 of the current log size is now set to 1 VLF instead of the 4 VLFs on previous SQL Server versions.

Benefits

By upgrading to SQL 2022 you will see many benefits for your organisation, including future proofing your SQL estate. This will make your SQL estate more robust and stable as well as will be crucial for preparing your organisation for the future releases from Microsoft.

Finally, previous SQL release go out of support every year. Currently, SQL 2014 has recently gone out of support. This puts you at threat of security and data risks. For regulated businesses this could also be of concern for your meeting your regulatory obligations.

For more information about upgrade your SQL estate, contact our Data and AI Team.