Microsoft releases SQL Server 2022, preferring hooks to Azure over enhancing the on-premises product

Microsoft releases SQL Server 2022, preferring hooks to Azure over enhancing the on-premises product

Microsoft has released SQL Server 2022 to general availability, at the PASS (Professional Association for SQL Server) Community Summit under way in Seattle and online. It succeeds SQL Server 2019, released just over three years ago.

Headline new features include a failover link to Azure SQL Managed Instance for disaster recovery, and SQL Ledger which provides an immutable record of data changes based on blockchain cryptography. There is also new integration with Azure Synapse – a service for data warehousing and analytics which includes Apache Spark – and Azure Purview, for information classification and protection. New support for the AWS S3 (Simple Storage Service) API, which is also used by other storage providers, enables scenarios like backup and restore to S3.

T-SQL, the SQL Server query language, has been enhanced in this release with new JSON (JavaScript Object Notation) functions, bit manipulation functions like LEFT_SHIFT and GET_BIT, time series functions, and a new IS DISTINCT FROM expression that simplifies dealing with null values in Boolean expressions.

The SQL Server 2022 query optimization has also been enhanced. The Query Store feature, which captures query history and enables performance and query plan tuning, is now on by default, where previously it was off as it can slightly impact performance.

New query tuning features in SQL Server 2022

Another cloud-related feature is an optional pay as you go billing model based on Azure Arc, now a default part of the SQL Server 2022 setup process. Azure Arc enables management of SQL Server from Azure, as well as use of Azure services like log analytics and Azure defender. Users are able to pay by the hour, upscaling for consumption peaks and downscaling for quiet periods.

DevClass spoke to Microsoft’s Asad Khan, VP of SQL Products and Services and asked that given SQL Server only runs on x64 (including AMD) CPUs, are there any plans to support ARM64?

“With SQL Edge we support Arm,” he told us, “but we did [so] using the translation layer at the compiler level, because it is simple … we are investing in how we can not use the translation layer.” Microsoft sees Arm growing in the server business, he said, “and in anticipation of that we want to make sure that we support all three chipsets,” these being Intel, AMD and Arm. Not yet though: SQL Edge is a cut-down version as well as being compromised by use of the translation layer.

Is the range of SQL Server editions, from Express to Enterprise, the same as before? “It is the same. We have enhanced some capabilities for Standard,” Khan said. “One of the examples is unlimited virtualization of containers, because containers is becoming a standard at least when it comes to dev, test and CI/CD pipelines.”

As Microsoft pushes users towards its cloud, it seems that built-in features for things like analytics and reporting are giving way to cloud services like Power BI, that cost extra. “We continue to support what we have, but more and more, the capabilities in the cloud outpace the capabilities which are on premises,” Khan told us. “For example, with the latest version we updated those engines which shipped in the box, but our primary focus was how can I use the cloud-based Power BI on top.” He added that, “for analytics and data lakes, customers are more willing to embrace cloud. For operational data, for historic or compliance reasons, they still prefer on-premises for many of the workloads.” The approach, Khan claimed, is “directly based on what we saw from customer adoption and feedback.”

Azure Data Studio, based on VS Code, is the future preferred tool for managing SQL Server

What is happening with SQL Server tools? It has become confusing, with the old Windows-only SQL Server Management Studio (SSMS) still current, the new Azure Data Studio (ADS) which despite the name is a desktop application based on Visual Studio Code, that runs cross-platform, as well as official extensions for VS Code that are different again.

“We could have gone with the extension approach for VS Code, and maybe will go back to it,” Khan told us. “The idea was that we need the flexibility on the UI for our data products,” which pointed towards the dedicated Azure Data Studio. Further, “VS Code is very developer-focused whereas Azure Data Studio tries to bridge the gap between the developer, the data-centric person, and the DBA (Database Administrator),” he said.

Inevitably, not all the features of SSMS are in ADS, but Khan said that this is the direction of travel. “We are looking into how we can bring more of SSMS into ADS,” he said. Nevertheless, the separate VS Code extensions will also continue, because of the sheer number of developers who use it.

What is the take-up of the Linux version of SQL Server? “We’ve seen a huge success,” claimed Khan. “Some of the cloud providers, for example Google, their SQL [Server] is built on Linux. As a company they are more comfortable running Linux. A lot of the telemetry we get from EC2 (Elastic Compute Cloud] on AWS, where customers are running SQL [Server], they are running on Linux primarily. We are not confined to a certain OS. The second thing, if you look at the container story or the Kubernetes story, it is primarily on Linux,” said Khan. In fact, even Microsoft’s own Arc data services, which runs on premises, “that is all Linux because it uses containers and Kubernetes,” Khan said.

Nevertheless, the legacy of SQL Server applications in the enterprise means that “there is a huge customer base so … running SQL [Server] on Windows will always overshadow Linux,” he added.