AWS Database Blog

Amazon RDS Custom for SQL Server supports SQL Server 2022

Amazon Relational Database Service (Amazon RDS) Custom for SQL Server now supports Microsoft SQL Server 2022 for Web, Standard, Enterprise and Developer Editions (via BYOM). 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 addition, SQL Server 2022 has native support for Amazon S3. Refer to Native backup and restore with Amazon S3 integration on Amazon RDS Custom for SQL Server 2022 for more details.

In this post, we show you how to launch a new RDS Custom for SQL Server 2022 instance and perform a major version upgrade from SQL Server 2019 to SQL Server 2022. We also discuss some SQL Server 2022 key features.

You can choose from two options to create and upgrade your RDS Custom for SQL Server instance: the AWS Management Console or AWS Command Line Interface (AWS CLI). We provide instructions for both options in this post.

Prerequisites

Before you create and connect to your custom DB instance for SQL Server instance, you need to set up some prerequisites, including an AWS Identity and Access Management (IAM) role and VPC, among others. To simplify setup, you can use an AWS CloudFormation template to create a CloudFormation stack.

If you need to configure your environment prerequisites with AWS CloudFormation (recommended) see Setting up your environment for Amazon RDS Custom for SQL Server.

Launch RDS Custom for SQL Server using SQL Server 2022

In this section, we show you how to create a new RDS Custom instance with SQL Server 2022 using the console. For more details, refer to Creating and connecting to a DB instance for Amazon RDS Custom for SQL Server.

Complete the following steps:

  1. On the Amazon RDS console, choose Databases in the navigation pane.
  2. Choose Create database.
  3. Select Standard create.
  4. For Engine options, select Microsoft SQL Server.
  5. For Database management type, select Amazon RDS Custom.

  6. For Edition, select your SQL Server edition.
  7. For Engine version, choose the latest SQL Server 2022 version.
  8. Select the template to use for your environment.
  9. For DB instance identifier, enter a name for your instance.
  10. Enter your primary user name and password.
    Alternatively, you can select Auto generate a password, available via AWS Secrets Manager.
  11. Select the instance type of your choice. In this example, we use a standard class db.m5.xlarge.
  12. Choose a storage type, and assign values for Allocated storage and Provisioned IOPS.
  13. Select your preference for Multi-AZ deployment:
    1. If you’re using the latest CloudFormation template, your environment will support Multi-AZ for RDS Custom, and you can select Create a standby instance.
    2. If you’re not sure, select Do not create a standby instance. You can modify this setting later.

  14. For Compute resource, select Don’t connect to an EC2 compute resource.
  15. Choose the VPC to host your RDS Custom for SQL Server.
  16. Choose the appropriate DB subnet group.
  17. For Public access, select No.
  18. For VPC security group (firewall), select Choose existing, then choose the corresponding security group.
  19. You have the option to change the database port under Additional configuration.
  20. In the RDS Custom security section, choose the IAM instance profile created for RDS Custom and the encryption key.
  21. Leave the remaining values as default.
  22. If your environment meets all the prerequisites, choose I’m all set.
  23. Choose Create database.

Alternatively, you can create an RDS Custom DB instance by using the create-db-instance command in the AWS CLI:

aws rds create-db-instance \
--db-instance-identifier <<rds-cli-sql-2022-01>> \
--engine custom-sqlserver-ee \
--kms-key-id <<your key id>> \
--engine-version 16.00.4085.2.v1 \
--master-username admin \
--master-user-password <<your password>> \
--db-instance-class db.m5.2xlarge \
--allocated-storage 100 \
--storage-type gp3 \
--backup-retention-period 0 \
--region <<your region>> \
--custom-iam-instance-profile <<RDS Custom IAM Profile>> \
--db-subnet-group <<rdscustom-db-subnet-group>> \
--vpc-security-group-ids <<sg-009999999f99d9f99>>

After you create the RDS Custom DB instance, you can connect to it using Session Manager, a capability of AWS Systems Manager, or an RDP client. For instructions, refer to Connect to your Windows instance. Ensure that the VPC security group associated with your DB instance allows inbound connections on port 3389 for TCP to enable RDP connections.

You need the key pair associated with the instance to connect to the custom DB instance via RDP. RDS Custom creates the key pair for you. The pair name uses the prefix do-not-delete-rds-custom-DBInstanceIdentifier. Secrets Manager stores your private key as a secret. Choose the secret that has the same name as your key pair and retrieve the secret value to decrypt the password later.

To launch the SQL Server 2022 database instance using a custom engine version (CEV) on RDS Custom, you need to prepare a CEV from an AWS EC2 Windows AMI (Amazon Machine Image) by either a pre-installed SQL Server 2022 CU9 (LI) or via bring your own media (BYOM). After you customize the OS and SQL Server configurations, the AMI is saved as a golden image. This golden image is then used to create a CEV, which can subsequently be used to create new RDS Custom for SQL Server 2022 DB instances.

Perform a major version upgrade from SQL Server 2019 to SQL Server 2022

In this section, we show you how to upgrade your instance using the console. Complete the following steps:

  1. On the Amazon RDS console, choose Databases in the navigation pane.
  2. Select your database and choose Modify.
  3. For DB engine version, choose the DB engine version for SQL Server 2022.
  4. Choose Continue.
  5. Read the message carefully before proceeding and review the Summary of modifications section.
  6. Select the option to apply immediately or during the next scheduled maintenance window.
  7. Choose Modify DB instance.

This process requires downtime because the upgrade will shut down SQL Server services. On Multi-AZ configurations, the process begins a rolling update, which starts with the secondary instance, then fails over the primary to the upgraded instance, thereby minimizing downtime.

Alternatively, you can upgrade an RDS Custom DB instance by using the modify-db-instance command in the AWS CLI:

aws rds modify-db-instance \
--db-instance-identifier <<rds-qhog-sql-2019-01>> \
--engine-version <<16.00.4085.2.v1>> \
--allow-major-version-upgrade \
--region <<your region>> \
--apply-immediately

In order to perform a major version upgrade of an existing SQL Server 2019 CEV DB instance, first you have to create a golden AMI by using either a pre-installed SQL Server 2022 (LI) or BYOM. This golden AMI is used to create a new CEV and then modify the existing SQL Server 2019 CEV DB instance by specifying --engine-version in the newly created CEV.

SQL Server 2022 key features

In this section, we discuss some important features that you can explore on SQL Server 2022 while continuing to take advantage of RDS Custom.

Native backup to Amazon S3 and integration with S3 object storage

With SQL Server 2022, the backup to URL feature now supports Amazon S3-compatible object storage. Amazon S3 compatibility means the storage solution uses the Amazon S3 API to provide an Amazon S3-compliant interface. You can use the Backup to URL feature by utilizing Amazon S3-compatible object storage as your backup destination. This allows you to store your SQL Server backups in an Amazon S3-compliant interface, providing a scalable and durable storage solution.

This feature offers the following benefits:

  • Cost-effective storage – Amazon S3 offers a cost-effective option for storing backups, because you only pay for the storage you use.
  • Scalability – You can scale your backup storage with ease by using the scalability of Amazon S3 storage.
  • Durability – Amazon S3 provides high durability, ensuring that backups are protected against data loss. It replicates data across multiple Availability Zones, offering a reliable backup storage solution.
  • Integration with existing workflows – You can seamlessly integrate the backup to URL feature with your existing backup and restore workflows to perform backups and restores to and from Amazon S3-compatible object storage.

TempDB performance improvements

In SQL Server 2022, several issues related to TempDB were addressed to improve its performance and scalability. The following are the key improvements:

  • Concurrent GAM and SGAM updates – SQL Server 2022 introduced concurrent updates to the Global Allocation Map (GAM) and Shared Global Allocation Map (SGAM) pages in TempDB. This allows parallel threads to modify these pages simultaneously, reducing contention and improving throughput.
  • Metadata contention – SQL Server 2022 improved the handling of metadata contention in TempDB. Previous versions introduced temp table caching and memory-optimized TempDB metadata tables to reduce metadata contention. SQL Server 2022 further enhances these features to minimize contention and improve performance.
  • Object allocation contention – The best practice of having multiple equally sized data files for TempDB to distribute object allocation across files remains relevant in SQL Server 2022. However, with the improvements in concurrent GAM and SGAM updates, the impact of object allocation contention is significantly reduced.

Enhanced data protection with SQL Server ledger

RDS Custom for SQL Server 2022 supports ledger databases. The SQL Server ledger provides tamper-evident auditing and data protection capabilities for relational databases. It allows organizations to track and monitor changes made to their data, ensuring data integrity and compliance with regulatory requirements.

The ledger feature includes the following key benefits:

  • Tamper-evident records – The ledger feature creates a tamper-evident record of all data changes, making it difficult for unauthorized users to modify or manipulate data without detection.
  • Auditing and tracking – With the ledger feature, organizations can easily track and audit all changes made to the database. This can be useful for compliance purposes or investigating any suspicious activities.
  • Protection against insider threats – The ledger feature enhances security by providing a transparent and verifiable record of all data modifications, deterring unauthorized access or malicious activities by trusted individuals.
  • Integration with existing security measures – You can seamlessly integrate the ledger with existing security measures in SQL Server, such as encryption, access controls, and data masking, to provide a comprehensive security solution for relational databases.

Asynchronous auto update statistics concurrency

In RDS Custom 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 data analysis and manipulation

Time series data is a set of values organized in chronological order, and is commonly used for historical comparisons, anomaly detection, predictive analysis, and reporting. SQL Server 2022 has introduced time series capabilities, including enhancements to existing T-SQL functions and the addition of two new functions. The GENERATE_SERIES relational operator simplifies the creation of a numbers table for generating contiguous datetime values. The DATE_BUCKET function helps group time series data into fixed intervals or buckets. Additionally, FIRST_VALUE and LAST_VALUE functions now offer new syntax for handling NULL values in time series analysis.

Conclusion

In this post, we provided a step-by-step guide on launching SQL Server 2022 and performing a major version upgrade of an RDS Custom for SQL Server instance using the Amazon RDS console and AWS CLI commands. We also provided an overview of some of the new and exciting features of SQL Server 2022 that can be explored on RDS Custom for SQL Server. A major engine version release like SQL Server 2022 brings significant changes to the engine. We highly recommend testing database workloads in lower environments before upgrading to this new engine version. Leave a comment if you have any questions.

To learn more about where or when to use RDS Custom, check out Working with RDS Custom for SQL Server.

About the authors

Jose A. Ramirez is a Database Admin, pet lover, and Database Specialist Solutions Architect at AWS, with over 25 years of experience in the technology industry, from manufacturing, pharma, to startups. He now focuses on architecting solutions for customers looking to modernize their applications and databases in the cloud.

Kalyan Banala is an accomplished Database Engineer, and works with the Amazon RDS Custom for SQL Server team at AWS. He enjoys working on technical challenges and is passionate about learning from and sharing knowledge with his teammates and AWS customers.