What’s New in Microsoft’s SQL Server 2022
In November of 2021, Microsoft announced the long-awaited next update to SQL Server. While currently in private preview, this new SQL Server update is expected to be released as a general availability sometime later this year (2022).
SQL Server 2022 has many new and exciting features that are sure to not only improve user experience but also offer key enhancements to factors such as performance and security. Knowing what new features are coming your way is essential for making the most of the 2022 release when it becomes a general availability.
In this article, we will cover what SQL Server 2022 is, what new features it includes, and a breakdown of the latest innovation from Microsoft. Keep reading to learn everything you need to know about Microsoft’s SQL Server 2022 release!
What is SQL Server 2022?
Microsoft’s SQL Server is a relational database management system used for a wide range of purposes including transaction processing and business analytics.
While many features make up the SQL Server, the core component of the server is its Database Engine – a multi-part engine comprised of a relational engine used to process queries and a store engine that serves as a storage and management system for database files, tables, pages, procedures, and more.
By using the SQL Server, users have access to a wide variety of tools and services used for data management and business intelligence. SQL Server also helps users to gain valuable data insights.
There are four main editions of SQL Server to choose between:
- SQL Server Developer: Used by database developers for database development, testing, and implementation.
- SQL Server Expression: Used by database administrators with small databases, with a size cap of up to 10 GB in storage.
- SQL Server Enterprise: A larger-scale version of SQL Server used by enterprises – this version of SQL Server includes all features.
- SQL Server Standard: A version of SQL Server Enterprise with partial features and configuration limits.
SQL Server 2022 is the newest release of the server, offering a plethora of new features that are as functionally beneficial as they are innovative.
Before diving into just what these new features are and what they have to offer, let’s first look at the focal points of this release and some of Microsoft’s key objectives:
The Focal Points of SQL Server 2022 Release
Microsoft’s latest version of the SQL server aims to enhance 3 key elements:
- Performance: In SQL, performance is a measurement that looks at how efficiently SQL queries are performed in relation to their required function. Performance can be affected by many factors, such as the connection between analytical and transactional stores, as well as failover.
- Security: Any time you work with data – especially private or sensitive data – it is crucial to have the proper security and governance measures in place to protect this data. This includes protecting data from tampering by maintaining a high-level record tracking system.
- Availability: Availability refers to how available and reachable SQL Server instances and databases are in the event of a server crash or failure. Microsoft aims to maintain High Availability, meaning the SQL Server is reachable with the least possible amount of downtime if such a scenario occurs.
According to Microsoft’s official announcement of SQL Server 2022:
“The rise of data represents a tremendous opportunity and also poses challenges. Companies are seeing their relational and nonrelational data proliferate exponentially on-premises, in the cloud, at the edge, and in hybrid environments.”
Additionally, the announcement notes the importance of SQL Server 2022 to provide customers with better capabilities for enabling deeper insights and predictions, as well as driving data governance, all while ensuring a high level of scalability.
This new release builds upon the updates included within the previous version, SQL Server 2019. The 2019 version of the server placed a heavy emphasis on improving performance, which is further developed within this latest release.
The 4 Key Features New to SQL Server 2022
While many new features come with SQL Server 2022, four major changes are particularly notable. These main changes include:
1. Azure Synapse Link
In previous versions of Microsoft’s SQL Server, moving data between the SQL Server and Synapse required complex ETL (Extraction, Transformation, Loading) jobs. These ETL tasks were time-consuming and complicated – and they could cause insights to lag during the process.
SQL Server 2022 is addressing this problem with the introduction of Azure Synapse Link. Azure Synapse Link is an analytical processing feature that enables a user to continue running real-time analytics while also automatically changing feeds responsible for capturing changes in the server.
Additionally, the Azure Synapse Link allows for a hybrid processing model that includes both transactional and analytical processing. This processing can occur without resulting in significant impacts on the operational system.
To make this new addition to the SQL Server even more beneficial to users, the data within the Synapse can be combined with other data sources of all sizes, scales, and formats. Plus, you can choose your preferred analytical tools including Azure Machine Learning, Spark, or Power BI.
While the Azure Synapse Link still presents complexities of its own to SQL Server users, it offers a useful alternative to more tedious ETL solutions.
2. SQL Server Ledger
Blockchain technology has become a hot topic within many industries – with some praising the technology and others leveraging heavy critiques against it.
Although blockchain has its issues within the financial realm, there are many useful applications when it comes to the SQL Server. Specifically, blockchain enables the use of the SQL Server Ledger – a new feature in the 2022 version that allows for the creation of immutable data records.
The ability to maintain an immutable track record is one of the key advantages of blockchain. With this type of ledger system, data is much more secure and protected from tampering. It also makes auditing processes simpler and more efficient, as well as improves multiple-party business processes.
There are two main forms of this ledger’s functionality used within the SQL Server:
- Updatable Ledger Tables: With updatable ledger tables, you can update and delete rows within the tables. These are considered ideal for application patterns with expected updates or deletions and track the history of changes to any rows within the database.
- Append-only Ledger Tables: Append-only ledger tables differ from updatable ledger tables in that UPDATE and DELETE operations are blocked by SQL Server. This makes append-only ledger tables ideal for applications that expect an insert-only pattern, such as Security Information and Event Management (SIEM) systems.
However, there is a drawback to this ledger feature – the size. An immutable ledger is essentially an endless transaction log, which presents a storage problem. As such, adoption of this feature may vary, with users who are already enthusiastic about blockchain technology likely to favor the feature the most.
3. Improved Disaster Recovery
Disaster recovery has historically been a pain point for SQL Server users due to the high maintenance required for a disaster recovery site. These disaster recovery sites not only require an offsite data center to be built and managed but also require continuous updating and testing.
SQL Server 2022 includes features specifically designed to address this problem. With the new version of the server, users can failover SQL Server to an Azure SQL Managed Instance – a cloud database platform-as-a-service (PaaS) that is combined with the SQL Server database engine compatibility. PaaS features have many advantages for the user, such as:
- Eliminating the need to purchase hardware
- Providing full management for overhead and underlying infrastructure
- Automatically patching and version upgrading
- Offering integrations with other PaaS services
- Scaling service and provisioning quickly
In fact, the SQL Managed Instance has almost 100% compatibility with the Enterprise Edition of the SQL Server database engine.
Additional benefits of the Azure SQL Managed Instance include:
- Full Management: As a PaaS solution, the SQL Managed Instance is fully managed and built on the same service infrastructure. This includes a wide range of PaaS features that ensure users always benefit from a high level of operational efficiency and proper functionality.
- Excellent Security & Isolation: With the SQL Managed Instance, users have access to both a native VNet implementation and a private IP address. This makes an isolated environment that is a single-tenant service and includes transparent data encryption.
- Frictionless Migration: The Azure SQL Managed Instance offers deployment options that allow SQL apps to have a frictionless migration. With the near 100% compatibility, the SQL Managed Instance makes it simple to lift and shift the SQL Server onto the managed instance.
4. Built-In Query Intelligence & Parameter-Sensitive Plan Optimization
Two major objectives that Microsoft aimed to achieve with the SQL Server 2022 release are improvements to performance and scalability. In the 2022 version, both performance and scalability receive enhancements by way of built-in query intelligence.
Built-in query intelligence – also called intelligent query processing (IQP) helps to improve the performance of existing workloads by internally optimizing query plans.
To make query processing even easier for the user, Microsoft also introduced parameter-sensitive plan optimization within SQL Server 2022. This type of plan optimization allows multiple plans to be cached per parameterized query or stored procedure. The key benefit to this is that users will not need to hassle with code changes or the Query Store when working with parameter-sensitive query plans.
Additional changes involving built-in query intelligence and parameter-sensitive plan optimization include:
- Query Store Improvements: Microsoft has added support for read replicas to the Query Store, along with enabling query hints that help to quickly mitigate issues and improve performance.
- IQP Scenario Expansion: Microsoft is expanding IQP by including more common customer problem scenarios, using parameter-sensitive plan optimization to generate multiple active cached plans all within a single statement.
Additional New Changes to SQL Server 2022
Along with the big changes discussed above, there are many additional smaller changes and features coming along with SQL Server 2022.
- Azure Purview Integration: To deal with governance, Microsoft is providing Azure Purview with general availability, as well as integrating the SQL Server with Azure Purview. This allows users to experience greater data discovery, automatically scan their on-premises SQL Server for free-to-capture metadata, utilize better customization capabilities to classify data, and more.
- Bi-Directional HA/DR to Azure SQL: With SQL Server 2022, the server is fully integrated with the link feature in Azure SQL Managed Instance. This allows users to benefit from the PaaS environment benefits discussed above, rather than having to deal with the complexities of the formerly used Infrastructure as a Service (IaaS) environment.
- Performance Enhancements: SQL Server 2022 offers differentiated performance, as well as adding many new improvements to the Query Store. Along with the Query Store improvements we discussed above, Microsoft has also made the Query Store enabled for all new databases. Plus, the Query Store can now help resolve a wider range of performance issues.
- Availability Enhancements: SQL Server 2022 offers high availability by allowing for the quick creation of a distributed AG (availability group) between the on-premises SQL Server and the Azure SQL Managed Instance. This distribution allows for manual failover that is much faster and more efficient compared to failovers of the past, making disaster recovery more efficient.
Why the Changes to SQL Server 2022 Matter
For anyone familiar with SQL Server, you know the potential impact the changes and new features within the 2022 version can have.
By focusing on improving performance, security, and availability, Microsoft has created a version of SQL Server that is far more efficient and user-friendly. Rather than making any drastic changes, Microsoft instead focused on fixing the problems at hand, providing users with logical solutions to the most pressing pain points experienced when using SQL Server.
Moreover, by further integrating with Azure and offering useful features such as the Azure SQL Managed Instance and the Azure Synapse Link, Microsoft has made it much more possible to run real-time analytics with minimal disruptions or impacts on the operational system.
The changes present in SQL Server 2022 show a true prioritization of the customer and their needs, rather than a push for added complexities or monumental innovation. While some changes are clearly significant and more on the innovative and experimental side – such as the addition of the SQL Server Ledger – overall the new features in SQL Server 2022 are highly practical and designed to help the user.
Microsoft’s 5 Key Capabilities in SQL Server 2022
It is tremendously apparent that the integration of Azure to offer more Azure-enabled capabilities was a key priority for Microsoft when creating SQL Server 2022.
With the general availability of SQL Server 2022 still to come, it is crucial to look at insights offered by Microsoft from the private preview. The new changes to SQL Server aim to help users get more out of their data and analytics through the application of new processes and features.
Here are the 5 key capabilities Microsoft outlined as part of SQL Server 2022:
1. Enabling Better Business Continuity through Azure
When failover occurs and disaster recovery is needed, it is essential to have an effective business continuity solution that provides all SQL Server instances to the business and end-users as needed.
SQL Server 2022 places special emphasis on this business continuity, including offering high-availability solutions with Always On availability groups. These availability groups can be created at both a database level and an instance level. Additionally, you can include an added layer of protection by creating availability groups that create redundancy at both levels.
Plus, the cloud-based disaster recovery offered via the Azure Synapse Link feature allows users to continuously replicate data both to and from the cloud platform.
2. Providing Seamless Analytics
The Azure Synapse Link is one of the major changes to SQL Server 2022 – and for good reason.
Using this link, you can enable seamless analytics for on-premises servers and operational data. This allows you to break down walls and silos between your operational and analytical stores using cloud computing, Azure Synapse Analytics, and Azure Machine Learning.
Along with allowing users to access real-time analytics, Microsoft added an extra layer of emphasis on improving analytic processing by not requiring users to establish ETL connections to cloud analytics. As a result, database administrators are offered a much higher level of flexibility, while end-users experience a lower chance of impact.
3. Increasing Visibility Over an Entire Data Estate
For customers and users of SQL Server, having a clear view of all data is crucial. With the help of the Azure Purview integration, users have greater visibility over their entire data estates, with capabilities that include:
- Automatic scanning and capture of SQL Server metadata
- Built-in and custom data classifiers that include Microsoft Information Protection sensitivity labels
- Control over specific access rights with your SQL Server
By increasing data visibility, Microsoft has made it easier for the user to work with and gain valuable data from all parts of the SQL Server – including the data map, data catalog, and data insights.
4. Achieving the Highest Security Level within the Last Decade
With the introduction of the SQL Server Ledger, the SQL Server is the most secure it has been within the past decade. Along with the SQL Server Ledger, additional adjustments and changes to security have been made by Microsoft to ensure full data integrity for all users.
As mentioned earlier in this article, the SQL Server Ledger employs blockchain technology to create immutable tables that are protected from tampering and easy to track. This use of blockchain within a centralized system allows Microsoft to establish a high level of digital trust that is not always found in decentralized blockchain systems.
Plus, the use of blockchain makes it far easier to work with multiple parties by enabling users to attest and prove that data integrity remains uncompromised. This is possible thanks to the long track records kept by immutable ledgers.
5. Maintaining Industry-Leading Performance and Availability
The SQL Server has long been a staple within the database management and analytics industry. To remain a leader in this space, Microsoft focused heavily on improvements to performance and availability when determining the new features to add to SQL Server 2022.
A key factor in the newest performance and availability improvements is that acceleration and tuning can occur without the need for code changes. Additionally, Query Store is turned on by default for users to help gain the most benefit out of the feature.
To make SQL Server 2022 better for users spread across multiple locations, Microsoft also ensured multi-write environments run smoothly.
How SQL Server 2022 Supports AI In Its Data Analytics Capabilities
Within the SQL Server is a feature called Machine Learning Services, which provides the ability to run R or Python scripts with relational data. By executing Python and R scripts, a user can deploy machine learning models within a database.
The SQL Server 2022 release takes this ability to deploy machine learning or artificial intelligence (AI) to the next level by ensuring workloads can improve while operating at scale.
By supporting AI in its data analytics capabilities, SQL Server 2022 is also enabling:
- AI-powered business intelligence tools and dashboards
- Predictive analytics forecasting
- Simpler development, testing, and implementation of analytics and AI solutions
Final Thoughts: When to Expect the GA Release of SQL Server 2022
SQL Server was announced by Microsoft in November 2021. Soon after this, the newest version of the server was released into private preview – an invite-only release that is available to specific Azure customers for the purpose of analysis and evaluation.
Microsoft also announced an Early Adoption Program for SQL Server 2022, in which interested customers could apply to adopt the server and test it out before it becomes publically available.
As of April 2022, SQL Server 2022 is still in the private preview phase of release. While a set date is not yet announced, many expect the GA (general availability) release for SQL Server 2022 to happen sometime in mid-2022.
To learn more about SQL Server 2022 and stay up to date on its release timeline, make sure to check out Microsoft’s official SQL Server 2022 and Resources page.