Microsoft; s Cloud-Connected On-Prem Database: SQL Server 2022 Rolls Out in Private Preview

Microsoft’s Cloud-Connected On-Prem Database: SQL Server 2022 Rolls Out in Private Preview

At Microsoft’s Ignite digital event, starting today, the company is announcing the private preview of SQL Server 2022, the next version of its three-decade-old relational database management system (RDBMS). While SQL Server is primarily an on-premises software product, Microsoft calls SQL Server 2022 “the most Azure-enabled release of SQL Server to date”.

Also Read: At Ignite, Microsoft Improves Its Cloud Database, Warehouse, and More 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 completing Microsoft’s hybrid cloud story. This ensures that SQL Server 2022, even when running on-premises, can integrate with a number of services that are only available in the cloud.

Also read:

Links to managed instance and synapse

These cloud-connected features include integration with Azure SQL Database Managed Instance – the Microsoft-managed, cloud-based implementation of the SQL Server box product. This integration serves both high availability/disaster recovery (HA/DR) purposes and makes migration to the cloud a smooth, near-zero downtime experience. Another Azure SQL affinity opportunity is SQL Server 2022’s implementation of the ledger feature in Azure SQL Database, announced in May 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. Like its Cosmos DB counterpart, Azure Synapse Link for SQL Server 2022 replicates data from SQL Server to Azure Synapse Analytics, eliminating the responsibility of customers to create their own data pipelines to push operational data to the data warehouse. However, there are important differences between the Cosmos DB and SQL Server implementations. While Azure Synapse Link for Cosmos DB essentially replicates data to a columnar repository that can serve as an extension to the Synapse data lake, its SQL Server counterpart pushes the data directly to a Synapse-specific SQL pool that stores the data warehouse workloads of operates the platform.

Also read:

Governance and performance

SQL Server 2022 also offers integration with Azure Purview, ensuring that the cloud-based data management platform includes SQL Server data, bringing on-premises stored data within management scope. That scope even includes the dissemination of Purview policies for centralized management of management activities.

Also read:

In terms of performance, SQL Server 2022 adds support for multi-write replication, creating corresponding multiple read replicas. This facilitates enabling query hints for the multiple replicas by SQL Server Query Store, improving performance without having to rewrite Transact SQL (T-SQL) code. Another feature, called Parameter Sensitive Plan Optimization, enables automatic generation of multiple active cached query plans for a single parameterized statement, adjusting different data sizes based on provided runtime parameter values. SQL Server 2022 also provides scalability for large memory servers and high concurrency scenarios.

Links outside of 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 based . For now, this API-driven connectivity extends to both Azure storage and object storage platforms compatible with Amazon S3 (including S3 itself). Microsoft describes this as providing data virtualization “for every data lake”. Access will also be more convenient this time around: PolyBase now works with the OPENROWSET command, providing ad hoc access to external data, rather than requiring external tables to be defined before data can be queried. With the 2022 release, SQL Server now also supports database backup/restore from any Amazon S3-compatible object storage system.

Also Read: Microsoft’s PolyBase Mixes SQL Server and Hadoop

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

Microsoft says SQL Server 2022 offers enhanced ANSI SQL compatibility to boot.

SQL Server on Linux: A Canonical Example

Another piece of SQL Server news is not related to the 2022 version, but to the 2017 and 2019 versions currently released. Both releases focused heavily on the ability to run on Linux and in containers. Perhaps to underline how serious a commitment 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 will be able to 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 will receive support for 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 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) to sync with underlying NVMe SSD drives. The Ubuntu Pro 20.04 LTS version of the VM also includes support for high availability scenarios through Corosync, Pacemaker, and a specialized screen agent for Azure. Finally, SQL Server running on these Linux VMs, just like on Windows, can take advantage of persistent memory (PMEM) when it’s available.

More to come

While Microsoft is announcing new capabilities on its data platform, there were enough new features in SQL Server 2022 alone to require this special post. Meanwhile, it’s still early days, so we don’t have any details about possible improvements to other parts of the product (e.g. Machine Learning Services). We will no doubt have more coverage once SQL Server 2022 goes into public preview.

I’ll close this post by reiterating a point I made when covering SQL Server 2019: Despite the platform’s legacy, SQL Server practitioners continue to receive updates to the core platform and accessible customizations of newer technologies from the database and analytics worlds . In other words, despite SQL Server being older than some people using it, Microsoft is constantly modernizing the platform.

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