What’s new in SQL Server 2022 on AWS?

Customers running SQL Server on AWS have enjoyed running their workloads with higher reliability and performance, lowered total cost of ownership (TCO), flexible licensing options, and using the broad and deep capabilities of AWS, all while leveraging greater security and identity services.

Microsoft released SQL Server 2022 for general availability (GA) on November 16th 2022, which boasts many new features, enhancements, and updates spanning a variety of categories including analytics, availability, security, performance, management, platform, language, and tools.

Customers can now deploy and run SQL Server 2022 on AWS, leveraging the new features and enhancements of SQL Server 2022, while continuing to take advantage of the benefits of AWS.

New SQL Server 2022 AMI

AWS continues to provide customers with flexible licensing options for their SQL Server 2022 workloads. Customers can bring their existing licenses to AWS with Amazon Elastic Compute Cloud (Amazon EC2) Dedicated Hosts, Dedicated Instances, or EC2 instances with shared tenancy using Microsoft License Mobility through Software Assurance.

Customers have access to AWS-provided licenses (license included) Amazon Machine Images (AMIs) with SQL Server 2022 installed and configured on Windows, Red Hat Enterprise Linux, and Ubuntu. Amazon EC2 AMIs with SQL Server 2022 provide additional defense-in-depth to help customers secure SQL Server databases from threats that persist across reboots.

AWS License Manager simplifies tracking the usage of software licenses and reduces the risk of non-compliance. Besides “bring your own license” (BYOL) and “license included” (LI), you can also use SQL Server 2022 Developer edition. For non-production, customers can download SQL Server 2022 Developer today from the Microsoft website and install it on Windows EC2 instances.

SQL Server native backup to Amazon S3

Customers have been asking for the capability to back up SQL Server databases directly to Amazon Simple Storage Service (Amazon S3) object storage to help reduce backup/restore times, increase backup storage scalability, and drive down backup costs. With SQL Server 2022, customers can now directly backup and restore to Amazon S3 using a simple T-SQL Backup command.

You can now access Amazon S3 storage from SQL Server T-SQL directly. If the SQL Server engine detects the word “S3” as part of Backup URL Syntax, it will use the S3 protocol to send data to Amazon S3 object storage. Full database, differential database, and transaction log backups are all supported. Integration with S3 object storage is not limited to backups, and extends to Data Lake Virtualization, which integrates with PolyBase with S3-compatible object storage, adding support for querying Apache Parquet files with T-SQL. This means you can run direct SQL Server queries with data stored in Amazon S3 using PolyBase. These types of integrations between SQL Server and Amazon S3 continue to leverage the broad and deep capabilities of AWS while increasing performance and scalability, reducing management overhead, and lowering SQL Server’s TCO.

Higher reliability with contained availability groups

We often hear from customers and database administrators (DBAs) that it is difficult to manage the system database objects synchronization with Always On availability groups between nodes using Multi-AZ deployments, making contained availability groups a top feature request. The Multi-AZ deployment provides enhanced availability and data durability by automatically replicating database updates between two AWS Availability Zones. Availability Zones are physically separate locations in an AWS Region with independent infrastructure designed to be insulated from failures in other Availability Zones.

With contained availability groups, SQL Server logins, database users, SQL Server Agent jobs, and database permissions are contained at the availability group level, as well as the SQL Server instance level. This reduces the management overhead to replicate system objects and reduces the risk of objects being out of sync, making it easier to work with Always On availability groups.

With Distributed availability groups, customers with more than six databases per availability group struggled to manage performance on their secondary nodes due to single redo threads. SQL Server 2022 now provides “Parallel Redo Thread Pool” and “Parallel Redo Batch Redo,” which can reduce the time to recover a database after a failover event. Failing over SQL Server databases in availability groups between AWS Availability Zones and Regions with SQL Server 2022 is now easier and faster.

Security and identity services

Security is a high priority and we provide various methods to encrypt SQL Server data at rest, in transit, and in use. Today, customers can use SQL Server’s Transparent Data Encryption (TDE) to protect data and log files at rest, and Transport Layer Security (TLS) to encrypt SQL Server data in transit between the database and applications. Always Encrypted is used to encrypt data in use to separate between those who own the data and can view it and those who manage the data but should have no access. Customers can use AWS Key Management Service (AWS KMS) to encrypt their storage volumes, and encryption in transit using SSL/TLS with AWS Certificate Manager (ACM).

SQL Server 2022 improves adherence to the principle of least privilege with new built-in, server-level roles and granular UNMASK permissions for dynamic data masking. SQL Server 2022 also supports using TDS 8.0 and TLS 1.3 to make encryption mandatory. Securing SQL Server database network traffic while reducing access with new granular roles and permissions is now easier with SQL Server 2022 on AWS.

Performance enhancements

AWS provides customers with a variety of deployment and configuration options to meet their SQL Server performance needs. Some of these include using locally attached NVMe storage for tempdb and deploying memory-optimized EC2 instances, as well as providing high-memory instances for high throughput built on the AWS Nitro System.

AWS customers running memory-optimized instances, such as the x2iedn EC2 family that provides a 32:1 ratio of memory to vCPU and up to 4TB of RAM, could see a performance improvement due to buffer pool scan operations now using multiple CPU cores.

SQL Server tempdb contention is addressed in SQL Server 2022 and these benefits are on by default. With these improvements in SQL Server 2022, AWS allows concurrent updates to the GAM and SGAM under a shared latch rather than using the update latch.

With SQL Server 2022 query store enabled by default and available on secondary replicas, customers who leverage query store hints can shape query plans without changing application code. Customers can use the scale and elasticity of AWS to run SQL Server 2022 Always On availability groups with read replicas while taking advantage of query store to manage their read-only workloads.

Parameter Sensitive Plan (PSP) optimization is a new feature which automatically enables multiple, active cached plans for a single parameterized statement, reducing query plan management and providing more predictive performance.

Degree of parallelism (DOP) feedback automatically adjusts DOP for repeating queries to optimize for workloads where inefficient parallelism can cause performance issues, leading to a potential reduction in CPU utilization and cost savings opportunity.

With the performance enhancements in SQL Server 2022 running on AWS, customers can expect an increase in performance, a reduction in required resources, and more predictive performance for their database workloads.

Conclusion

AWS has helped hundreds of thousands of customers transition their SQL Server workloads to the cloud. Customers continue to choose AWS for migrating and modernizing SQL Server for optimized price/performance, license flexibility, and security, leveraging AWS innovations to right size, configure, and deploy SQL Server applications. With SQL Server 2022, you now have access to new features and enhancements like Amazon S3 integration, contained availability groups, support for TLS 1.3, updated query store and DOP feedback, and native performance improvements.

If you’re currently evaluating your migration and modernization options for your SQL Server workloads, including SQL Server 2022, please contact us. We’d be happy to help you with your SQL Server plans and initiatives.

AWS can help you assess how your company can get the most out of cloud. Join the millions of AWS customers that trust us to migrate and modernize their most important applications in the cloud. To learn more on modernizing Windows Server or SQL Server, visit Windows on AWS. Contact us to start your modernization journey today.