Microsoft Unveils SQL Server 2022 with Azure Synapse Integration and Enhanced T-SQL Capabilities
SQL Server 2022 brings a new integration with Azure Synapse, a data warehousing and analytics service that includes Apache Spark, and Azure Purview, for information classification and protection. T-SQL, the query language of SQL Server, has been enhanced in this release with new JSON functions, and support for the AWS S3 API enables scenarios such as backup and restore to AWS S3. Other important additions are also worth noting.
Both pillars are underpinned by integration with Azure Synapse Analytics, Azure SQL Database Managed Instance (MI), Azure Active Directory (Azure AD), and Microsoft Purview. Compatibility with S3-compatible object storage, while also important on-premises, solidifies Microsoft’s arguments about moving toward the cloud. “SQL Server 2022 is a core part of the Intelligent Data Platform. This platform seamlessly integrates operational databases, analytics, and data governance,” said Rohan Kumar, Corporate Vice President, Azure Data.
Kumar added, “It enables customers to scale in real time, add layers of intelligence to their applications, gain fast, predictive insights, and manage their data wherever it resides.” In a blog post, Kumar noted that Azure’s integration also includes managed disaster recovery (DR) to Azure SQL Managed Instance, as well as near real-time analytics, allowing DBAs to manage their data assets with greater flexibility and minimal impact on the end user.
Depending on the company, performance and scalability are automatically improved with built-in query intelligence. Security innovation, which builds on SQL Server’s track record as the least vulnerable database over the past decade, continues with “Ledger for SQL Server,” which uses blockchain to create a tamper-proof time record of all changes made to the database. With Synapse Link, users can now run real-time analytics on their database without having to set up a complex infrastructure.
“Just tick a box and say, ‘replicate this data in near real-time.’ They land in Synapse and you can have your Power BI q reportIt reads that data and this whole pipeline is just built for you,” Kumar said. And for enterprises that do have a hybrid configuration, support for the Purview data governance service now allows them to set their policies, whether the data resides in SQL Server in the cloud or on-premises. In addition to the cloud-related aspect, the team also improved the overall performance, stability, and security of the database.
At the heart of this work, at least for this release, is the database’s intelligent query processing engine, which can now optimize queries in a number of more complex scenarios. Another cloud-related feature is an optional billing model based on Azure Arc. With a connection to Azure Arc, which is now part of the SQL Server 2022 configuration process, on-premises users can now opt for cloud-based billing to manage consumption spikes or for ad hoc use cases.
T-SQL, SQL Server’s query language, has been enhanced with new JSON functions, bit manipulation functions such as LEFT_SHIFT and GET_BIT, time series functions, and a new IS DISTINCT FROM expression that simplifies the processing of null markers in Boolean expressions. The company announced that the Query Store feature, which captures query history and helps adjust performance and query plan, is now enabled by default, whereas it was previously disabled, as it can have a slight impact on performance.
Also of note is the enhancement of SQL Server’s PolyBase, a data virtualization and connectivity to big data feature, so that it is compatible with AWS S3 and all object storage systems that are compatible with its API. Here again, the “cloud” or “on-premises” paradox arises, because several storage platforms compatible with the S3 API, such as Minio, operate on-premises. Therefore, Microsoft touts the new PolyBase as offering access to any data lake. This technology also allows databases to be backed up to S3-compatible object storage.
Ironically, however, PolyBase will no longer support connectivity to on-premises Hadoop clusters. But, as a result, PolyBase’s reliance on the Java runtime has been eliminated, raising the possibility that more customers will install it. If so, it would probably be a good thing for integrating SQL Server with the modern open source data analytics stack, much of which is in the cloud. As Microsoft pushes users to its cloud, it seems that built-in features for things like analytics and reporting are giving way to cloud services like Power BI.
But these new services would cost customers more. “We continue to support what we have, but increasingly, the capabilities in the cloud exceed the capabilities that are on-premises. For analytics and data lakes, customers are more willing to embrace the cloud. For operational data, for historical or compliance reasons, They still prefer on-premises for many workloads. This approach is directly based on what we’ve seen from customer adoption and feedback,” said Asad Khan, Microsoft’s vice president of SQL products and services.
Server 2022 adds support for important new cloud, database, and analytics technologies, while maintaining consistency and fidelity to the traditional platform that has a large community of skilled professionals. While Microsoft is interested in newer platforms, like Azure Cosmos DB NoSQL, and supports open source databases like PostgreSQL, it doesn’t want to give up on its investments in SQL Server. The market seems to reward Microsoft for this policy. It will be interesting to see what the 4th decade of SQL Server can bring.
