SQL Server 2022's "hero capabilities"

Credit: Microsoft

At Microsoft's Ignite digital event this week, the company is announcing the private preview of SQL Server 2022, the next version of its three-decades-old relational database management system (RDBMS). Though SQL Server is primarily an on-premises software product, Microsoft is nevertheless calling SQL Server 2022 "the most Azure-enabled release of SQL Server yet."

Also read: At Ignite, Microsoft enhances its cloud database, warehouse and lake services

While the previous release (SQL Server 2019) focused on core features like Azure Data Studio, Big Data Clusters, and support for Java stored procedures, this release focuses heavily on rounding out Microsoft's hybrid cloud story. This ensures SQL Server 2022, even when it runs on-premises, can integrate with a number of services available only in the cloud.

Also read:

• SQL Server 2019 reaches general availability
  
• The big data odyssey of SQL Server 2019, and more data and AI news from Microsoft Ignite
  
• Review: SQL Server 2017 adds Python, graph processing and runs on Linux
  

Links to Managed Instance and Synapse

These cloud-connected features include integration with Azure SQL Database Managed Instance -- the Microsoft-managed, cloud-based deployment of the SQL Server box product. This integration serves both high availability/disaster recovery (HA/DR) purposes as well as makes migration to the cloud a smooth, near-zero-downtime experience. Another Azure SQL affinity capability is SQL Server 2022's implementation of the ledger feature in Azure SQL Database, which was announced in May of this year, bringing the same blockchain capabilities to SQL Server.

Microsoft is also using the 2022 release as a vehicle to introduce an SQL Server version of Azure Synapse Link. Similar to its counterpart for Cosmos DB, Azure Synapse Link for SQL Server 2022 replicates data from SQL Server into Azure Synapse Analytics, letting customers avoid the responsibility of creating their own data pipelines to push operational data into the data warehouse. There are important differences between the Cosmos DB and SQL Server implementations, though. Whereas Azure Synapse Link for Cosmos DB essentially replicates data into a columnar storage repository that can serve as an extension to the Synapse data lake, the SQL Server counterpart pushes the data directly into a Synapse dedicated SQL pool that serves the platform's data warehouse workloads.

Also read:

• Azure Synapse Analytics combines data warehouse, lake and pipelines
  
• A closer look at Microsoft Azure Synapse Analytics
  
• Azure Synapse Analytics: A progress report
  
• Azure Synapse Analytics data lake features: Up close
  

Governance and performance

SQL Server 2022 also features integration with Azure Purview, which assures that the cloud-based data governance platform encompasses SQL Server data, bringing data stored on-premises into its governance scope. That scope even includes propagation of Purview policies for centralized administration of management operations.

Also read: 

• Microsoft introduces Azure Purview data catalog; announces GA of Synapse Analytics
• Azure Purview data governance service heads to GA
  

On the performance side, SQL Server 2022 adds support for multi-write replication, creating corresponding multiple read replicas. This facilitates SQL Server Query Store's enablement of query hints for the multiple replicas, improving performance without requiring a rewrite of Transact SQL (T-SQL) code. Another feature, called Parameter Sensitive Plan Optimization, automatically enables the generation of multiple active cached query plans for a single parameterized statement, accommodating different data sizes based on provided runtime parameter values. SQL Server 2022 also sports scalability for large memory servers and high concurrency scenarios.

Links beyond Azure

Other features include a new version of PolyBase (SQL Server's big data connectivity solution) that uses REST APIs to connect to data lakes in addition to using the ODBC drivers -- on which the last version of PolyBase was premised. For now, this API-driven connectivity extends to both Azure storage and object storage platforms compatible withAmazon S3 (including S3 itself). Microsoft characterizes this as providing data virtualization "for any data lake." The access will be handier this time around as well: PolyBase will now work with the OPENROWSET command, providing ad hoc access to external data, instead of requiring definition of external tables before data can be queried. With the 2022 release, SQL Server will now also support database backup to/restore from any Amazon S3-compliant object storage system.

Also read: Microsoft's PolyBase mashes up SQL Server and Hadoop

In addition, there are several T-SQL goodies in SQL Server 2022. These include an enhanced set of functions for working with JSON data; new time series capabilities similar to those in Azure SQL Edge (which themselves are based on Azure Stream Analytics); and other new functions based on customer-voted items. 

Microsoft says SQL Server 2022 features improved ANSI SQL compatibility, to boot.

SQL Server on Linux: A Canonical example

Another piece of SQL Server news pertains not to the 2022 version, but to the currently released 2017 and 2019 versions. Both of these releases had a strong focus on the ability to run on Linux -- and in containers. Perhaps to underscore how serious a commitment that is, Microsoft and Canonical (makers of the popular Ubuntu Linux distribution) announced yesterday the availability of a set of fully-supported SQL Server on Ubuntu Pro virtual machine images.

As a result, according to Canonical's press release, "Customers on Microsoft Azure can launch fully supported instances of SQL Server 2017 or SQL Server 2019 -- Web, Standard, and Enterprise editions -- on both Ubuntu Pro 18.04 LTS and Ubuntu Pro 20.04 LTS." The press release further explains that "customers receive support on the entire solution, including security updates and joint technical support from Canonical and Microsoft." The virtual machine images are available in the Azure Marketplace.

There are some nice performance optimizations in these VM images as well. For example, the images are configured to use Direct I/O and Forced Unit Access (FUA) for synchronization with underlying NVMe SSD drives. Also, the Ubuntu Pro 20.04 LTS version of the VM includes support for high availability scenarios through Corosync, Pacemaker, and a specialized fencing agent for Azure. Finally, as on Windows, SQL Server running on these Linux VMs can take advantage of persistent memory (PMEM) when it's available. 

More to come

While Microsoft is announcing new capabilities across its data platform, there were enough new features in SQL Server 2022 alone to require this dedicated post. Meanwhile, it's still early days, so we don't have details on possible enhancements to other areas of the product (Machine Learning Services, for example). We'll no doubt have more coverage once SQL Server 2022 moves to public preview.

I'll close this post by reiterating a point I made in covering SQL Server 2019: Despite the platform's legacy, SQL Server practitioners continue to receive updates to the core platform and accessible adaptations of newer technologies from around the database and analytics world. In other words, despite SQL Server being older than some of the people using it, Microsoft perpetually modernizes the platform.

Microsoft is a customer of Brust's advisory firm, Blue Badge Insights. He is also a Microsoft Data Platform MVP.

More from Ignite

Cloud-connected on-prem database: SQL Server 2022 rolls out in private preview Microsoft is adding more AI smarts and other new tools to Office Power Apps gets pay-as-you-go licensing; Dynamics 365 gets more new modules Meet Loop, a new collaboration tool built on Fluid Framework Edge browser for Linux is now available for all users Microsoft enhances its cloud database, warehouse and lake services Microsoft to extend its 'enterprise metaverse' strategy with Mesh for Teams

• Cloud-connected on-prem database: SQL Server 2022 rolls out in private preview
• Microsoft is adding more AI smarts and other new tools to Office
• Power Apps gets pay-as-you-go licensing; Dynamics 365 gets more new modules
• Meet Loop, a new collaboration tool built on Fluid Framework
• Edge browser for Linux is now available for all users
• Microsoft enhances its cloud database, warehouse and lake services
• Microsoft to extend its 'enterprise metaverse' strategy with Mesh for Teams