At Microsoft Ignite A digital event, which kicks off today, the company announces the private preview of SQL Server 2022, the next release of its three-decade-old relational database management system (RDBMS). Although SQL Server is primarily an on-premises software product, Microsoft nonetheless calls SQL Server 2022 “the most Azure compatible version of SQL Server to date.”
Also Read: At Ignite, Microsoft Improves Cloud Database, Warehouse And Lake Services
While the previous version (SQL Server 2019), focused on core functionality like a new tooling platform (Azure Data Studio), Big Data Clusters, and support for Java stored procedures, this release has a strong focus on optimizing Microsoft’s hybrid cloud history, so that SQL Server 2022, even when running on-premises, can integrate with a number of services available only in the cloud.
Read also :
Links to Managed Instance and Synapse
These cloud-connected features include integration with Azure SQL Database Managed Instance, the cloud-based, Microsoft-managed deployment of the SQL Server Box product). This integration serves both high availability / disaster recovery (HA / DR) purposes and to make the migration to the cloud a smooth and seamless experience. Another Azure SQL affinity capability is the SQL Server 2022 implementation of the ledger function in Azure SQL Database which was announcement in May of this year, bringing the same blockchain capabilities to SQL Server.
Microsoft is also using version 2022 as a vehicle to introduce a SQL Server version of Azure Synapse Link. Similar to his counterpart for Cosmos DB, Azure Synapse Link for SQL Server 2022 replicates data from SQL Server in Azure Synapse Analytics, allowing customers to avoid the responsibility of creating their own data pipelines to push operational data into the data warehouse. There are, however, some important differences between the Cosmos DB and SQL Server implementations. While Azure Synapse Link for Cosmos DB essentially replicates data in 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 SQL pool dedicated to Synapse that serves the platform’s 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 encompasses SQL Server data, integrating 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 facilitates SQL Server Query storeenabling query hints for multiple replicas to improve performance without requiring a rewrite of Perform SQL transactions (T-SQL). Another feature, called Parameter Sensitive Plan Optimization, automatically enables the generation of multiple cached active query plans for a single parameterized statement, supporting different data sizes depending on the runtime parameter values provided. SQL Server 2022 also offers scalability for large-memory servers and high-concurrency scenarios.
Links beyond Azure
Other features include a new version of PolyBase (SQL Server Big Data Connectivity Solution) that uses REST APIs to connect to data lakes, in addition to using the ODBC drivers that the latest version of PolyBase was based on. For now, this API-based connectivity extends to both Azure storage and object storage platforms compatible with Amazon S3 (including S3 itself). Microsoft describes this as “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, rather than requiring the definition of external tables before the data can be queried. With release 2022, SQL Server will now also support database backup / restore from any Amazon S3 compatible object storage system.
Also read: Microsoft’s PolyBase Blends SQL Server and Hadoop
Additionally, there are several T-SQL benefits 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 themselves are based on Azure Stream Analytics) and other new functions based on items voted by customers. And Microsoft claims that SQL Server 2022 features have been improved ANSI SQL compatibility, to boot.
SQL Server on Linux: a canonical example
Another SQL Server update is not about SQL Server 2022, but the 2017 and 2019 versions that are currently released. Both of these versions placed a strong emphasis on being able to run under Linux and in containers. Perhaps to underline how serious this commitment is, Microsoft and Canonical (makers of the popular Ubuntu Linux distribution) announced yesterday the availability of a set of fully supported SQL servers 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 Ubuntu Pro 18.04 LTS and Ubuntu Pro 20.04 LTS. ” The press release further explains that “customers receive support for the entire solution, including security updates and joint technical support from Canonical and Microsoft.” The images of the virtual machine 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 Device Access (FUA) for synchronization with the underlying NVMe SSDs. Additionally, the Ubuntu Pro 20.04 LTS version of the virtual machine includes support for high availability scenarios through Corosync, Pacemaker and a specialized fencing officer for Azure. Finally, as on Windows, SQL Server running on these Linux virtual machines can take advantage of persistent memory (PMEM) when it is available.
While Microsoft is announcing new features on its data platform (which I cover in a separate article), there were enough new features in SQL Server 2022 to require this dedicated article. In the meantime, it’s still early days, so we don’t have details yet on possible improvements in other areas of the product (Machine learning services, for example). We’ll definitely get 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 updates. accessible adaptations of new technologies in the world of databases and analysis. In other words, although SQL Server is older than some of the people who use it, Microsoft is perpetually modernizing the platform.
Microsoft is a client of Brust’s consulting firm, Blue Badge Insights. He is also a Microsoft Data Platform MVP.