Introducing SQL Server by Bob Ward

This article first appeared in the SQLServerGeeks Magazine.
Author: Bob Ward
Subscribe to get your copy.

A few weeks ago we announced the next major release of SQL Server: SQL Server 2022. I wanted to give you more insights into what we announced, answer some of the common questions we have received so far including the private preview program and point you to the resources we have created so you can learn more.

SQL Server 2022 can be summarized as the most cloud-connected version of SQL Server to date with major innovations in built-in query intelligence, security, scalability, availability, data virtualization, and the T-SQL language.

Think of this release having new features in the following five areas:

I’ve nicknamed this visual with our team “the wheel of power”. Let me explain more about the features in these areas and why these could solve challenges you face every day.

Cloud Connected
In SQL Server 2022 we will solve familiar challenges by connecting your SQL Server to Azure. For example, if you want a managed disaster recovery site, we will easily connect you with Azure SQL Managed Instance using the power of built-in Availability Group (AG) technology (and you don’t have to have an existing AG to do it). We have just shown you a glimpse of what this can do. By the time we release SQL Server 2022 this link will be a fully-functional bi-directional failover system.

Let’s say you want to invest more in Azure Synapse Analytics but you are struggling to copy your data from SQL Server. The data is never really up to date and you have to build expensive ETL applications. SQL Server 2022 allows you to link SQL Server to Synapse. You can now select what tables in your SQL Server database you want synchronized to Synapse and we take care of the rest. We will synchronize your initial data set and then capture changes and feed them directly into SQL Pools. You now have the full data warehouse capabilities of Synapse on your data leaving you to focus your SQL Server on operational workloads and eliminate ETL jobs.

We have other cloud connected capabilities like integration with Azure Purview for centralized policy management, Azure Active Directory Authentication, and Microsoft Defender for SQL.

All of these cloud connected options are independent of each other. You choose what option you want when you need them.

Built-in Query Intelligence
We believe getting your application faster and reducing the need for expensive query tuning should be built into SQL Server. SQL Server 2022 builds on innovations in the previous releases to enhance the Query Store and Intelligent Query Processing (IQP).

Query Store will now be on by default in SQL Server 2022 for new databases. We believe we have made the right investments where most customers will benefit having this on by default. In addition, we will now allow Query Store to be enabled for read-only secondary replicas that are part of an Availability Group. Query Store data is kept on the primary replica but metadata tracks the source of the query (primary or secondary). Query Store hints are also part of SQL Server 2022 allowing you to shape query plans without changing the application and persist this for future execution.

Intelligent Query Processing (IQP) is all about fast and consistent performance with no code changes, all built into the query processor. Our customers have made significant use of these enhancements in Azure, SQL Server 2017, and SQL Server 2019. IQP has always been about solving problems we see customers face every day and SQL Server 2022 is no exception. For example, parameter sensitive plans have been a thorn in developers and SQL Server professionals for years. Now by moving to the latest database compatibility level with SQL Server 2022 (160), the query processor can cache multiple plans for the same stored procedure or parameterized statement. No longer will parameters that are sensitive to skewed data values cause unpredictable performance. We are also smart about how we determine how many plans can be cached so we don’t bloat your plan cache. We achieve this functionality by creating variants of the query but the variants are hashed to the same query text. There are other next-gen IQP features like DOP and Cardinality Estimation (CE) feedback. These features will use the Query Store to take and store feedback about query execution to adjust query plans for consistent performance. IQP is a huge area of investment for SQL Server 2022. You can learn more with the resources I’ve listed at the end of this article.

Security, Scalability, and Availability
Many of you have heard me call innovations to the core engine the “meat and potatoes” of SQL Server. I borrowed that term from Conor Cunningham who always counseled me to always let our customers know about what we are doing with the core engine, because without it we don’t have a product.

I’ll call out one feature from each of these areas but there are many others that are part of SQL Server 2022:

Security
SQL Server Ledger solves the challenge of to have tamper evidence records of changes to data. Also in preview in Azure, we are bringing the power of blockchain to SQL Server and integrating this directly into the database. Ledger provides historical records of transaction changes but also uses crypto hashing algorithms to store information about these changes. It also includes capabilities to store a digest of hashed blocks in a separate trusted storage site. This allows a complete verification of changes to data at all levels. Hackers can try to gain access to the raw data stored in SQL Server tables but the ledger has verification mechanisms to detect any intruder. I’ve personally tried to hack the ledger myself using my knowledge of SQL Server internals but it appears to be “Bob Ward proof”.

Scalability
We are also always looking for ways to make the engine more scalable for applications. One example in SQL Server 2022 is our continued effort to remove bottlenecks for tempdb. We have improved latch concurrency with more key system allocation pages. Combined with past innovations, we are close to having a latch free worry tempdb system for your applications.

Availability
Customers have used peer-to-peer transaction replication with SQL Server to implement a multi-write application across servers. The problem is that conflicts could result in a halt of replication and manual conflict detection used ID numbers which may not be a very logical answer. With SQL Server 2022, you have the choice now of configuring replication so that conflict detection is automatic and based on a last-writer wins concept using UTC datetimes synchronized across servers.

There is more and we look forward to keep giving you more details so you can see how we are innovating the core database engine.

Data Virtualization and Object Storage
We introduced the concept of data virtualization in SQL Server 2016 with Polybase. Data virtualization is the idea to keep “data where it lives”. Use the power of T-SQL to access this data and bring back results, instead of moving the data. In SQL Server 2019, we introduced new Polybase capabilities with ODBC drivers to sources such as Oracle and MongoDB. In SQL Server 2022, we are extending Polybase to use a new technique to access data with REST APIs. You will now be able to build external tables or use the T-SQL OPENROWSET function to access files in Parquet, JSON, CSV or Delta formats with sources such as S3 compatible storage.

In addition, you can now use T-SQL to backup or restore a native SQL Server backup to any S3 compatible storage provider. We have found many customers shifting to common object storage systems and S3 is a popular protocol to access this storage.

Extending T-SQL
We still believe T-SQL is one of the best and most popular database languages in the industry. So instead of making you learn a new language, we are constantly extending the T-SQL language to support new capabilities. For SQL Server 2022, T-SQL extensions include new JSON functions, improve existing functions around string processing and others, and support for T-SQL functions to process time-series data as supported today with Azure SQL Edge.

You have questions
You may have heard that we announced SQL Server 2022 as a private preview. I’ve had several questions around what, how, and why. Let me share with you some of the most common questions and our answers.

Q: Why is it called a Private Preview and what does this mean?

A: A private preview means the release is not available to publicly download. Today, in order to try out this release you must register through our Early Adopter Program (EAP) at https://aka.ms/eapsignup. Not everyone who signs up will be accepted into the program. We built the program to work with customers who can directly and regularly interact with us and get feedback to make this a great release. This is not the first time we announced a major release of SQL Server with a private preview. It allows us to work with you to refine the product and gain directed feedback before we move to a public preview.

Q: When will SQL Server 2022 be available for public preview and General Availability?

A: I’ll answer this question the same way I did for previous SQL Server releases. We won’t miss the name (insert mild laughter here). Our plans are to release a public preview and general availability in calendar year 2022. We just are not ready to disclose the exact dates yet.

Q: Do I have to connect to Azure to use SQL Server 2022?

A: Definitely not. We are offering you the ability to connect to Azure with SQL Server 2022 on your terms for managed disaster recovery, near real-time analytics, and policy management. There are plenty of great capabilities just “built-into the box” without having to connect to Azure.

Q: What features will go into which editions and how will licensing and pricing work?

A: Like all past SQL Server releases, we don’t make any announcements about pricing, licensing, or edition and features until the product goes to Generally Availability. We do ask for feedback on these topics during our preview program.

Q: Can I share information about SQL Server 2022 with my colleagues or customers?

A: Yes. Anything that Microsoft has released publicly can be shared with anyone. For example, we have PDF versions of our slides we have been presenting available at https://aka.ms/sqlserver2022decks. However, since the release is in private preview, only Microsoft will be showing demonstrations at this time.

Q: Where is the pre-release documentation for SQL Server 2022?

A: Members of the private preview program have access to GitHub repos for documentation. The official public pre-release of the documentation for SQL Server 2022 will be available to everyone when the product moves to public preview.

SQL Server 2022 resources
We are as excited as everyone else for SQL Server 2022. I’m sure you want to know more. Here are some resources I think you will find valuable.

https://aka.ms/sqlserver2022 – This is the main public website for SQL Server 2022

https://aka.ms/eapsignup – This is the site to sign-up for the private preview program

https://aka.ms/sqlmechanics22 – Enjoy this brief video as I share insights into SQL Server 2022 with the famous Microsoft Mechanics team.

https://aka.ms/sqlserver2022webinar – Sign-up for this on-demand webinar about SQL Server 2022

https://aka.ms/sqlserver2022decks – Get PDF versions of the decks presented by the Microsoft team on SQL Server 2022

http://aka.ms/dataexposed-sqlserver2022 – Keep up with the latest info on SQL Server 2022 in this series with Data Exposed.

Follow me at @bobwardms, my colleague @SQLPedro, or @AzureSQL for announcements on future public presentations myself and others from our team will be giving on SQL Server 2022. We all look forward to sharing more about SQL Server 2022 with all of you.

This article first appeared in the SQLServerGeeks Magazine.
Author: Bob Ward
Subscribe to get your copy.