SQL Server 2022

Preview Feature Announcements

At the recent Ignite conference, Microsoft announced some new details for the upcoming release of SQL Server 2022. In a video with Microsoft’s Bob Ward, four interesting new features were showcased for the new SQL version.

• Hybrid Disaster Recovery using Azure Managed Instance
• Azure Synapse Link integration to provide hybrid transaction analytical processing with SQL Server.
• SQL Server Ledger to provide immutable histories of data changes.
• Multiple plan caching to reduce parameter sensitive plan executions.

SQL Server 2022 and Azure Managed Instance Disaster Recovery

SQL Server 2022 will undoubtably provide increased Azure integration and one of the new features announced by Microsoft is the ability to use an Azure Managed Instance as a failover target for databases running on premises.  Currently, Azure Managed Instances are nearly 100% compatible with existing on premises SQL Server instances and with SQL Server 2022, it is expected that full compatibility will be provided.  SQL Server 2022 will allow you to link a database to an Azure Managed Instance to use as a failover target, which can also be used for read only workloads.  This process looks very easy to implement and SQL Server will create a distributed availability group to manage the synchronisation of data between the on premises and cloud hosted infrastructure.  Failover of the database can be managed manually via SSMS or T-SQL.  Also provided is the ability to move a failed over database back from Azure to on premises, something that isn’t available in current versions of SQL Server. The ability to do this is only available via a backup and restore process, rather than using the failover wizard used to initially migrate a database workload to Azure.

This feature looks useful and will allow businesses to spin up a DR site quickly and very easily with minimal effort. Unlike Availability Groups, this functionality looks to be managed at the database level, so failover of multiple databases at once doesn’t seem possible and fail back to on premises is managed via backup and restore processes, which could mean significant downtime is required.

Azure Synapse Link Integration

Traditional reporting workloads are usually managed by taking data from transaction systems and importing relevant data to an analytical system for analysis. This process usually has an element of lag between extraction and load, so that undue pressure isn’t put on the transactional system.  Azure Synapse Link provides a way to perform analysis during the ETL process in near real time without affecting the production transaction database by using a HTAP model, “Hybrid Transaction Analytical Processing”, which is based on using memory computing technologies to enable data analysis at the data source.

Azure Synapse Link currently provides the HTAP model between Cosmos DB and Azure Synapse Analytics.  SQL Server 2022 will support Azure Synapse Link, allowing automatic changes to be fed straight into Azure Synapse without the need to build a ETL pipeline. It will provide near real time analysis and hybrid transactional processing with minimal impact of the source server.  This should allow very quick analysis of data without having to wait for ETL processes to run or delays with updating the analysis data sources.

SQL Server Ledger

SQL Server Ledger has been in public preview in Azure SQL Database for a while now and this feature will now be available in SQL Server 2022.  The ledger component protects data from attackers or highly privileged users by preserving historical data.  If a record is updated in the database, its previous value is maintained and protected in a history table.  The ledger and historical data are managed transparently and so doesn’t require any application changes.

Each transaction in the database is cryptographically hashed along with the hash from the previous transaction, and so each transaction is therefore linked together. This provides a historic record of every transaction in the database which cannot be amended or tampered with.  This feature is useful for Audits and storing critically sensitive information.

Multiple Plan Cache

In current versions of SQL Server, the execution of a stored procedure will generate an execution plan based on the values of the parameters used during its initial compilation.  This plan will then be used for all subsequent executions of the stored procedure, and this can sometimes cause inconsistent performance when the cached plan has been produced using values which favour specific data distributions in the database tables. 

This problem is referred to as parameter sniffing.  With SQL Server 2022, SQL Server intelligently identifies if an existing plan is inefficient and, if so, will generate a new plan for the different values being used with the procedure, storing multiple plans for each different execution path. This should provide more consistent execution times and operations and improve the performance of applications with no additional code changes.

Conclusion

With this SQL Server 2022 preview, Microsoft has showcased some very innovative new features that should allow organisations to gain improvements in performance, availability, and security without having to amend existing applications or services. More details of the features including demonstrations, can be found on the Microsoft Mechanics YouTube Channel here:

https://www.youtube.com/embed/ncF-zFzBDAY