Microsoft Cloud-Connected On-Premises Database: SQL Server 2022 Rolls Out in Private Preview

SQL Server 2022 “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-decade-old relational database management system (RDBMS). Although SQL Server is primarily an on-premises software product, Microsoft nevertheless calls SQL Server 2022 “the most Azure-compatible version of SQL Server yet.”

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

While the previous release (SQL Server 2019) focused on core features such as Azure Data Studio, big data clusters, and support for Java stored procedures, this release focuses heavily on finishing the story. of Microsoft’s hybrid cloud. This ensures that SQL Server 2022, even when running on-premises, can integrate with a number of services only available in the cloud.

Read also :

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 and makes migrating to the cloud a smooth, zero-downtime experience. Another Azure SQL affinity capability is SQL Server 2022’s implementation of ledger functionality 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 a 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, allowing customers to avoid the responsibility of building their own data pipelines to push operational data into the warehouse of data. There are, however, important differences between the Cosmos DB and SQL Server implementations. While 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 peer pushes data directly into a dedicated Synapse SQL pool that serves the platform data warehouse workloads.

Read also :

Governance and performance

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

Read also :

On the performance side, SQL Server 2022 adds support for multi-write replication, creating multiple corresponding read replicas. This makes it easier for SQL Server Query Store to enable query hints for multiple replicas, improving performance without requiring a Transact SQL (T-SQL) code rewrite. Another feature, called Parameter Sensitive Plan Optimization, automatically allows the generation of multiple cached active query plans for a single parameterized statement, supporting different data sizes based on the runtime parameter values ​​provided. SQL Server 2022 also provides 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) which uses REST APIs to connect to data lakes in addition to using ODBC drivers – which the last version of PolyBase was based on . For now, this API-driven connectivity extends to both Azure Storage and Amazon S3-compatible object storage platforms (including S3 itself). Microsoft characterizes this as providing data virtualization “for any data lake”. Access will also be more convenient this time around: PolyBase will now work with the OPENROWSET command, providing ad hoc access to external data, instead of requiring external tables to be defined before the data can be queried. With the 2022 release, SQL Server will now also support database backup/restore from any Amazon S3 compatible object storage system.

Also read: Microsoft’s PolyBase mixes SQL Server and Hadoop

Additionally, there are several T-SQL goodies in SQL Server 2022. These include an improved set of functions for working with JSON data; new time series features similar to Azure SQL Edge (which itself is based on Azure Stream Analytics); and other new features based on customer voted items.

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

SQL Server on Linux: a canonical example

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

As a result, according to Canonical’s press release, “Microsoft Azure customers can launch fully supported instances of SQL Server 2017 or SQL Server 2019 – Web, Standard, and Enterprise editions – on Ubuntu Pro 18.04 LTS and Ubuntu Pro 20.04 LTS.” The press release further explains that “customers receive support across the entire solution, including security updates and joint technical support from Canonical and Microsoft.” VM images are available on Azure Marketplace.

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

More soon

As Microsoft announces new features on its data platform, there were enough new features in SQL Server 2022 to warrant this dedicated post. In the meantime, it’s still in its infancy, so we don’t have details on possible improvements in other areas of the product (Machine Learning Services, for example). We will no doubt have more coverage once SQL Server 2022 goes into public preview.

I’ll end this article by reiterating a point I made while covering SQL Server 2019: Despite platform legacy, SQL Server practitioners continue to receive core platform updates and accessible adaptations of the latest technologies from the database and analytics world. In other words, although SQL Server is older than some people who use it, Microsoft is constantly modernizing the platform.

Microsoft is a client of Brust’s consulting firm, Blue Badge Insights. He is also MVP of Microsoft Data Platform.

Maria H. Underwood