Amazon RDS for SQL Server now supports SQL Server 2022

Amazon Relational Database Service (Amazon RDS) for SQL Server now supports Microsoft SQL Server 2022 for Express, Web, Standard, and Enterprise Editions. You can use SQL Server 2022 features such as accelerated database recovery, intelligent query processing, intelligent performance, monitoring improvements, and resumable online index creations.

In this post, we summarize the new features on SQL Server 2022 that are supported in Amazon RDS for SQL Server. We also explain when and how the compatibility level of a database is set and review a few caveats with respect to some of the new features.

Amazon RDS for SQL Server 2022 supported features

The following are the SQL Server 2022 new and improved features now supported on Amazon RDS for SQL Server 2022.

Accelerated database recovery improvements

Accelerated database recovery (ADR) was introduced in SQL Server 2019 with the objective of improving database availability in the presence of long-running transactions by not impacting recovery time regardless of the number of active transactions or their sizes. It provides instantaneous transaction rollback irrespective of the time the transaction has been active or changes that have been performed. This is achieved through versioning all physical database modifications and only undoing logical operations. ADR improvement in SQL Server 2022 offers the following:

Multi-threaded version cleanup – An improvement was made to use multi-threaded version cleanup (MTVC), allowing multiple databases to be cleaned up in parallel. To adjust the number of threads for version cleanup, you can configure the value through a parameter group.
Reduced memory footprint for PVS page tracker – SQL Server 2022 implements a persistent version store (PVS) cleaner thread per database instead of per instance, and the memory footprint for PVS page tracker has been improved.
User transaction cleanup – Another improvement is to allow user transactions to run cleanup on pages that couldn’t be cleaned up by the regular cleanup process due to lock conflicts. This helps ensure that the ADR cleanup process doesn’t fail because user workloads can’t acquire table-level locks.

See Accelerated database recovery for more details.

Shrink database wait with low priority

In previous releases, shrinking databases and database files to reclaim space often leads to concurrency issues. In SQL Server 2022, an additional option for DBCC SHRINKDATABASE and DBCC SHRINKFILE 'WAIT_AT_LOW_PRIORITY' changes the default behavior. When specifying WAIT_AT_LOW_PRIORITY behavior, new queries requiring Sch-S locks aren’t blocked by the waiting shrink operation until the shrink operation stops waiting and begins running.

In our example, we have a total of three tasks being run. Two of them are transactions performing inserts, whereas one is performing the shrink file operation. The sequence of running the tasks is as follows: 79, 70, and 88. Session 70 is the shrink file process.

In earlier versions of SQL Server, when session 79 completes, session 70 (shrink file) would be next on the queue to run, blocking session 88. However, in SQL Server 2022, session 88 continues to run while session 70 waits until it times out.

See WAIT_AT_LOW_PRIORITY for more details.

Dynamic data masking

Granular UNMASK permissions for dynamic data masking are now supported on Amazon RDS for SQL Server 2022.

Asynchronous auto update statistics concurrency

In Amazon RDS for SQL Server 2022, you can avoid potential concurrency issues using an asynchronous statistics update if you enable the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY database-scoped configuration.

Time series functions

You can store and analyze data that changes over time, using time-windowing, aggregation, and filtering capabilities. The following time series functions are now supported:

The following add support to IGNORE NULLS and RESPECT NULLS:

Memory grant feedback

Memory grant feedback adjusts the size of the memory allocated for a query based on past performance. The SQL Server 2022 introduces Percentile and Persistence mode memory grant feedback, and is also supported on Amazon RDS for SQL Server 2022:

Persistence – This capability allows the memory grant feedback for a given cached plan to be persisted in the Query Store so that feedback can be reused after cache evictions.
Percentile – This new algorithm improves performance of queries with widely oscillating memory requirements, using memory grant information from several previous query runs, instead of just the memory grant from the immediately preceding query run. Note that it requires enabling Query Store.

Resumable add table constraints

Amazon RDS for SQL Server 2022 supports pausing and resuming an ALTER TABLE ADD CONSTRAINT operation. You can resume the operation after maintenance windows, failovers, or system failures.

Provisioning a SQL Server 2022 DB instance

You can provision an instance of Amazon RDS for SQL Server 2022 in two different ways:

Create a new RDS instance with engine version SQL Server 2022 16.00
Upgrade an existing RDS instance to engine version SQL Server 2022 16.00

Amazon RDS for SQL Server supports upgrading directly to SQL Server 2022 from all supported versions. The oldest supported engine version is SQL Server 2014 (engine version 12.00)

You can migrate databases from an older version of SQL Server to Amazon RDS for SQL Server 2022 as long as the source database compatibility level is set to 90 or higher. The database compatibility level is automatically upgraded to 100 when restoring a database backup taken on SQL Server 2005 (engine version 9.00). The database compatibility level remains 100 for database backups taken from SQL Server 2008 or 2008 R2.

We highly recommend testing database workloads on the new engine version prior to upgrading the DB instances. Amazon RDS for SQL Server makes this easy to do so. To test the upgrade, complete the following high-level steps:

Take a snapshot of the DB instance.
Restore the snapshot as a test DB instance.
Upgrade the test DB instance to the new engine version.
Test the newly upgrade instance.
After testing is complete, you can stop the test DB instance.

For more information about testing and upgrading to new major versions, see Best practices for upgrading SQL Server 2008 R2 to SQL Server 2016 on Amazon RDS for SQL Server.

You can provision a SQL Server 2022 DB instance on Amazon RDS for SQL Server by using the AWS Management Console, AWS Command Line Interface (AWS CLI), AWS SDK, or AWS CloudFormation. While provisioning the DB instance, the engine version must be set to 16.00.

Compatibility level

The compatibility level defines the Transact SQL (T-SQL) and query processing behavior in SQL Server. For more information, see ALTER DATABASE (Transact-SQL) compatibility level. The compatibility level is set at the database level and the native compatibility level of a newly created database on SQL Server 2022 is 160.

Irrespective of the provisioning method (creating a new DB instance or upgrading an existing DB instance), a newly created database on an RDS for SQL Server 2022 DB instance has a compatibility level of 160 by default. On an upgraded RDS for SQL Server 2022 DB instance, existing databases that were created on older engine versions prior to the engine version upgrade remain on the older compatibility level. For example, if an RDS for SQL Server 2017 DB instance was upgraded to SQL Server 2022, prior to the upgrade, databases created on SQL Server 2017 had a compatibility level of 140. These databases continue to have a compatibility level of 140 even after the upgrade. However, after the upgrade, you can change the compatibility level using the ALTER DATABASE T-SQL command:

alter database <db_name> set compatibility_level=160

SQL Server Management Studio (SSMS) provides an option to change the compatibility mode via the SSMS graphical user interface (GUI). This requires elevated privileges that aren’t available in Amazon RDS, so you can’t change the compatibility level using the SSMS GUI. Instead, use the T-SQL command to change the compatibility level.

TempDB improvements

On very busy databases, the use of TempDB to manage temporary objects can become a bottleneck and cause processes to be blocked. One way to solve this issue is by creating multiple data files for TempDB, but that doesn’t always solve the problem and there could still be TempDB contention. With SQL Server 2022, system page latch concurrency enhancements have been added to eliminate Global Allocation Map (GAM) contention and for concurrent Shared Global Allocation Map (SGAM) updates. This again is something where no code changes are needed to take advantage of this improvement.

This feature is enabled by default. Complete the following steps to verify the improvement:

Create two RDS DB instances (SQL Server 2019 and 2022):

aws rds create-db-instance --db-instance-identifier rdsmssql-2019 \
--no-multi-az --master-username master --master-user-password P@ssw0rd \
--db-instance-class db.m5.xlarge --allocated-storage 100 --storage-type gp2 \
--engine sqlserver-ee --engine-version 15.00 --license-model license-included \
--region <your region>
aws rds create-db-instance --db-instance-identifier rdsmssql-2022 \
--no-multi-az --master-username master --master-user-password P@ssw0rd \
--db-instance-class db.m5.xlarge --allocated-storage 100 --storage-type gp2 \
--engine sqlserver-ee --engine-version 16.00 --license-model license-included \
--region <your region>

Create a user database (on both DB Instances) for testing purposes:
```
USE [master];
GO
CREATE DATABASE myTestDB;
GO
```

Create a stored procedure on database myTestDB on both DB instances, to cause the GAM contention in TempDB:

USE [myTestDB];
GO
CREATE PROCEDURE ProcTest
AS
BEGIN
   CREATE TABLE #t1 
   ( c1 INT,
     c2 sysname);
   INSERT INTO #t1
   SELECT TOP 5000 column_id, [name]
   FROM sys.all_columns WITH(NOLOCK)
   ORDER BY [name]
END;

Using the SQLQueryStress tool, let’s set Number of Iterations to 100 and Number of Threads to 25 so that the preceding stored procedure is run 2,500 times.

Enter the following query into the stress tool:

SET NOCOUNT ON;
DECLARE @i INT;
SET @i = 1;
WHILE @i <= 100
    BEGIN
         EXEC ProcTest;
         SET @i = @i +1;
    END

Set the database connection parameters.
Choose GO to start the run.

It’s possible to have two stress tests running at the same time (one connected to each DB instance) or run one at a time.

Check for GAM contention by running the following query:

USE master
GO
SELECT
er.session_id, er.wait_type, er.wait_resource,
OBJECT_NAME(page_info.[object_id],page_info.database_id) as [object_name],
er.blocking_session_id, er.command,
    SUBSTRING(st.text, (er.statement_start_offset/2)+1,
           ((CASE er.statement_end_offset
               WHEN -1 then DATALENGTH(st.text)
               ELSE er.statement_end_offset
             END - er.statement_start_offset)/2) +1) AS statement_text,
page_info.database_id, page_info.[file_id], page_info.page_id, page_info.[object_id],
page_info.index_id, page_info.page_type_desc
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
CROSS APPLY sys.fn_PageResCracker (er.page_resource) AS r
CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info
WHERE er.wait_type like '%page%';

For SQL Server 2019, we can see GAM contention, as shown in the following screenshot.

For SQL Server 2022, there is no contention, as shown in the following screenshot.

Support for ledger databases

Amazon RDS for SQL Server 2022 supports ledger databases, which allow you to run applications that require the integrity of your sensitive data to be protected throughout the life of the database. With the ledger feature, you can attest to auditors or business partners that your data has not been tampered with. The management of the ledger feature is transparent to the application and requires no code changes. In a ledger database, you will only be able to create ledger tables, which can be either updatable ledger tables or append-only ledger tables. You are not allowed to create regular tables. A ledger database is defined during creation and can’t be converted into a regular database after. However, you can create a ledger table in a regular database.

Use the following steps to create a ledger database and table on Amazon RDS:

Connect to your RDS instance using SSMS and launch a new query window.

Create a ledger database and table:

--Create ledger database--
CREATE DATABASE db_ledger01 WITH LEDGER = ON;
GO
use db_ledger01
go

--Create updatable ledger table--
CREATE TABLE [Balance]
(
 [CustomerID] INT NOT NULL PRIMARY KEY CLUSTERED,
 [LastName] VARCHAR (50) NOT NULL,
 [FirstName] VARCHAR (50) NOT NULL,
 [Balance] DECIMAL (10,2) NOT NULL
)
WITH (
 SYSTEM_VERSIONING = ON,
 LEDGER = ON);

--Insert test data--
INSERT INTO [Balance] VALUES (1, 'Jones', 'Nick', 50);

The updatable ledger table automatically creates a history table and a ledger view. The history table preserves the version of the row prior to an update or delete. The ledger view joins the updatable ledger table and the historical table to report all row modifications that have occurred.

SELECT
ts.[name] + '.' + t.[name] AS [ledger_table_name]
, hs.[name] + '.' + h.[name] AS [history_table_name]
, vs.[name] + '.' + v.[name] AS [ledger_view_name]
FROM sys.tables AS t
JOIN sys.tables AS h ON (h.[object_id] = t.[history_table_id])
JOIN sys.views v ON (v.[object_id] = t.[ledger_view_id])
JOIN sys.schemas ts ON (ts.[schema_id] = t.[schema_id])
JOIN sys.schemas hs ON (hs.[schema_id] = h.[schema_id])
JOIN sys.schemas vs ON (vs.[schema_id] = v.[schema_id])
GO

Update the balance of the record with CustomerID = 1:

update dbo.Balance set BALANCE = 30
where CustomerID = 1

Query the balance ledger table, history table, and ledger view for information using the following script:

SELECT *
,[ledger_start_transaction_id],[ledger_end_transaction_id]
,[ledger_start_sequence_number],[ledger_end_sequence_number]
FROM dbo.Balance
GO
SELECT * FROM dbo.MSSQL_LedgerHistoryFor_901578250
GO
SELECT * FROM dbo.Balance_Ledger ORDER BY ledger_transaction_id
GO

We can see the following:

The initial record with a balance of $50 from the history table before the update
A delete and insert operation from the ledger view, indicating the balance amount was updated to change the value from $50 to $30

Amazon RDS for SQL Server features such as native backup and restore, RDS snapshot and restore, point-in-time restore, read replicas, and MS-CDC integrate seamlessly with ledger databases. However, the automatic generation and storage of the database digest is not supported on Amazon RDS. You have to manually generate and store the database digest (JSON document) in a secure location for future verification process. For more information, refer to Digest management.

To drop the ledger table, use the following code:

USE db_ledger01;
GO
DROP TABLE dbo.Balance

When you drop a ledger table, its dependent objects (history table and ledger view) are also dropped. Instead of deleting the data, the dropped objects are renamed to logically remove it from the user schema but physically remain in the database. For more information, refer to

Dropping columns and tables.

Features not currently supported

Here is the list of features not currently supported by RDS for SQL Server 2022:

Category	Features
High Availability	Contained Availability Group Link to Azure SQL Managed Instance
Security	Always Encrypted with Secure Enclaves Support for MS-TDS 8.0 protocolExternal Data Source
Performance	Backup compression algorithm – Integrated acceleration and offloading with QAT
Management	Backup and restore to S3 compatible object storage
Tools	Distributed Replay

Conclusion

In this post, we discussed some of the new and exciting features of SQL Server 2022 that are supported in Amazon RDS for SQL Server. We highlighted cases where the features differ, provided instructions to enable the features, and advised on any prerequisites they might have. A major engine version release like SQL Server 2022 brings significant changes to the engine—some visible and others not. We highly recommend testing database workloads using the Amazon RDS easy clone mechanisms as described in this post before upgrading to this new engine version. Leave a comment if you have any questions.

About the authors

Sudarshan Roy is a Senior Database Specialist Cloud Solution Architect with World Wide AWS Database Services Organization (WWSO). He has led large scale Database Migration & Modernization engagements for Enterprise Customers and his passionate of solving complex migration challenges while moving database workload to AWS Cloud.

Barry Ooi is a Senior Database Specialist Solutions Architect at AWS. His expertise is in designing, building, and implementing data platforms using cloud-native services for customers as part of their journey on AWS. His areas of interest include data analytics and visualization. In his spare time, he loves music and outdoor activities.