Three decades on, SQL Server is still a database workhorse that powers both an internal line of business applications and large-scale public websites like Stack Overflow. While some database workloads have migrated to the cloud—and the next version of SQL Server has more tools and integration with Azure services for backup or analytics to help with that if it’s the right choice for a business—there are plenty of new features to support a wide range of database scenarios.
“As a database platform, SQL Server provides customers the maximum flexibility from edge to cloud,” said Asad Khan, director of program management for Microsoft Azure. “Depending on their workload and application requirements, customers can deploy SQL Server in the environment of their choice.
“Our customers run SQL Server in small footprint edge devices supporting IoT-type scenarios to huge servers running mission-critical applications to hybrid footprint in public cloud.”
So while SQL Server 2022 enables migrating workloads to Azure SQL Managed Instance with virtually no downtime using distributed availability groups, users can also restore databases back to SQL Server on their own infrastructure.
If you’re doing a hardware upgrade on a server or physically moving it somewhere, the workload can temporarily be moved up to Azure and then brought back down when ready.
SEE: Best cloud certifications in 2022: From Azure to Google and beyond (TechRepublic)
Use cloud without migrating
SQL Server can use Azure services for backup, business continuity, analytics, policy and data governance.
“SQL Server 2022 integrates with Azure Synapse Link and Microsoft Purview to enable customers to drive deeper insights, predictions and governance from their data at scale,” Khan said. “Cloud integration is enhanced with managed disaster recovery (DR) to Azure SQL Managed Instance, along with near real-time analytics, allowing database administrators to manage their data estates with greater flexibility and minimal impact to the end user.”
The SQL Managed Instance effectively becomes a secondary replica you can fail over to and fail back from, without the need to set up an availability group. The hybrid capabilities also use Azure to fine tune how a SQL Server 2022 system runs.
“With more intelligence built into the actual database operations, workloads automatically learn how to run better, faster and smarter without manual tuning or oversight,” Khan said.
The integration is helpful for hybrid cloud scenarios that go in the other direction. Microsoft Defender for Cloud can protect on-premises SQL databases through SQL Server Extension for Azure, which can now be installed when setting up SQL Server, and users can use Azure AD authentication, including multi-factor authentication (MFA), to access an on-premises SQL Server 2022.
Also, Azure includes running Azure services on an organization’s own infrastructure.
“With Azure Arc enabled SQL Server, customer workloads connect to Azure to give customers always on database access, evergreen software updates and actionable intelligence from their deployment,” Khan said.
And it’s not just Azure; the new object storage integration means users can integrate with S3-compatible storage for backup and restore, which includes storage fabrics from Dell, HPE, Nutanix, Pure Storage, Red Hat and Scality as well as AWS. According to Khan, the object storage can also be used for big data.
“Data Lake Virtualization integrates PolyBase with S3-compatible object storage, which adds support for querying parquet files with T-SQL,” said Khan.
But if Azure is being used for big data, users can effectively do away with ETL (extract, transform, load) and use their SQL Server operational data for analytics.
SEE: Microsoft Power Platform: What you need to know about it (free PDF) (TechRepublic)
“Azure Synapse Link for SQL Server 2022 provides near real-time analytics and hybrid transactional and analytical processing with minimal impact on operational systems,” Khan said. “With a seamless integration between operational stores in SQL Server 2022 and Azure Synapse Analytics dedicated SQL pools, Azure Synapse Link for SQL enables customers to run analytics, business intelligence and machine learning scenarios on their operational data with minimum impact on source databases with a new change feed technology.”
That’s the same approach Microsoft has already taken with Cosmos DB, so this is a very traditional SQL database getting features first seen in database services designed for the cloud.
A ledger without a blockchain
If you’ve ever looked at the claims for blockchains and thought that an append-only database could do that without all the work of designing and maintaining a distributed system that likely doesn’t scale to high-throughput queries (or the environmental impact of blockchain mining), another feature that started out in Azure SQL and is now coming to SQL Server 2022 is just what you need.
“Ledger brings the benefits of blockchains to relational databases by cryptographically linking the data and their changes in a blockchain structure to make the data tamper-evident and verifiable, making it easy to implement multi-party business process, such as supply chain systems, and can also streamline compliance audits,” Khan explained.
For example, the quality of an ice cream manufacturer’s ice cream depends on both the ingredients that its suppliers send and the finished ice cream it delivers being shipped at the right temperature. If the refrigerated truck has a fault, the cream might curdle, or the ice cream might melt and then refreeze once it’s in the store freezer.
By collecting sensor information from everyone in its supply chain, the ice cream manufacturer can track down where the problem is. But, everyone has to be able to trust that the temperature readings are correct and haven’t been tampered with to shift the blame.
“Ledger allows participants to verify the integrity of the centrally housed data,” Khan said.
When creating a ledger table, any rows that are modified by a transaction are cryptographically hashed along with the transactions themselves. That’s stored in the same Merkle tree data structure other blockchains use, and users can periodically write digests that represent the state of the database to tamper-proof storage, so they can check later that the live database hasn’t been tampered with by an attacker or a malicious admin.
For SQL Azure that’s Azure Blob storage of Azure Confidential Ledger, which uses secure enclaves in Azure Confidential Computing. These tools can be used with SQL Server 2022 or with an organization’s own WORM (write once read many) storage.
But unlike many blockchain systems, Ledger is environmentally sustainable.
“It does not rely on expensive consensus protocols, such as proof of work (PoW), commonly used by blockchain technologies,” said Khan. “The PoW mechanism requires multiple servers to compete to solve complex mathematical problems, which consumes a lot of energy.”
SEE: What is Azure Database for PostgreSQL Flexible Server? (TechRepublic)
There isn’t any specific server hardware needed to run Ledger, like a CPU with secure enclave support. Users can create both updatable and append-only ledger tables depending on whether they need to update and delete rows or just insert new ones. Either way, users get a tamper-proof record they can show to an auditor.
“As with a traditional ledger, the feature preserves historical data,” said Khan. “If a row is updated in the database, its previous value is maintained in a relational form to support SQL queries and is protected in a history table.
“Ledger provides a chronicle of all changes made to the database over time, while maintaining the power, flexibility and performance of the SQL database.”
Improving query performance
SQL Server 2022 has more Intelligent Query Processing features to speed up existing workloads and database applications with what Khan calls “minimal implementation effort.”
“Applications can automatically benefit from these features without any code changes by just enabling Query Store, running on the latest application database compatibility level or turning on a database level configuration,” said Khan.
The new features tweak settings that are complex or just tedious for database admins to handle and improve some of the existing automatic performance enhancements, which can sometimes backfire.
Getting the degree of parallelism for a query can be tricky, so DOP (Degrees of Parallelism) feedback uses a feedback loop to check if using more CPU cores actually makes the query faster or just takes resources from other database tasks where the cores would be more useful.
“DOP feedback automatically adjusts the degree of parallelism for repeating queries to optimize for workloads where inefficient parallelism can cause performance issues,” Khan explained. “Memory Grant feedback adjusts the size of the memory allocated for a query based on past performance.”
SQL Server 2022 can use percentiles to improve the feedback algorithm for queries where the amount of memory changes a lot each time users run them, and persist memory grants feedback even if users evict the cache or restart the server.
The SQL Server query optimizer estimates the cost of running a query plan using the number of rows that will be processed, which is the cardinality for the query. There are some queries where it’s difficult for SQL Server to make a good estimate of what the cardinality will be, so it gets the cost wrong.
SEE: How to combine values from a column into a single cell using Microsoft Excel’s Power Query (TechRepublic)
“Cardinality Estimation feedback identifies and corrects suboptimal query execution plans for repeating queries, when these issues are caused by [those] incorrect estimation model assumptions,” Khan explained.
The speed of some queries can vary dramatically because the data users are looking at can be very different. When looking at how many items a supplier has in stock, that’s naturally going to be much faster if they only carry a few lines rather than hundreds or thousands. Parameter sensitive plan optimization looks for non-uniform data distributions like that and tells SQL Server to store multiple plans for how to run the stock query because the parameters will vary so widely.
Batch mode operations will be faster if an organization has server CPUs with Advanced Vector Extension (AVX) 512 extensions.
“[This] improves the performance of buffer pool scan operations on large-memory machines by utilizing multiple CPU cores,” Khan said.
There are also some specific Query Store improvements, like using it on secondary replicas to get insights about workloads running there. Users also get the Query Store hints that database developers can already use to tell in Azure SQL DB and Managed SQL Instances to help speed up queries without rewriting them.
That’s the promise that Microsoft has made about Azure versions of server products all along—that new features will be built for the cloud first. But when they will be useful running on an organization’s own hardware, they’ll show up in the next release of the server software.
How can I get SQL Server 2022?
Microsoft hasn’t announced when it expects to ship SQL Server 2022, but it’s common for new releases to arrive around the time of Microsoft Ignite, which is at the end of October this year. The Community Technology Preview 2.1 release of SQL Server 2022 is available now as a 180-day trial Evaluation Edition with all the features of the Enterprise edition, or users can run the Developer Edition as a 180-day trial in an Azure VM from the marketplace.