Announcing SQL Server 2022 public preview: Azure-enabled with continued performance and security innovation

Announcing SQL Server 2022 public preview: Azure-enabled with continued performance and security innovation

May 24th, 2022

Today, we are excited to announce the public preview of SQL Server 2022. Anyone can now download SQL Server 2022 preview to try the new features in this release.

Button to download SQL Server 2022, public preview.

SQL Server 2022 is the most Azure-enabled release of SQL Server yet, with continued innovation across performance, security, and availability. It is part of the Microsoft Intelligent Data Platform, which unifies operational databases, analytics, 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. 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. Performance and scalability are automatically enhanced via built-in query intelligence.

Over the past six months since our private preview announcement, we saw tremendous interest in our SQL Server 2022 Early Adoption Program (EAP). Thousands of companies applied for our early adoption program, representing strong momentum relative to prior releases.  

Top-requested features for SQL Server 2022 EAP applicants included:

  • Link feature for Azure SQL Managed Instance1: To ensure uptime, SQL Server 2022 is fully integrated with the new link feature in Azure SQL Managed Instance. With this capability, you now get all the benefits of running a platform as a service (PaaS) environment applied to DRallowing you to spend less time on setup and management even when compared to an infrastructure as a service (IaaS) environment. The general availability of the bidirectional disaster recovery capability of the Link feature for Azure SQL Managed Instance will occur after the general availability of SQL Server 2022.
  • Azure Synapse Link for SQL: Azure Synapse Link for SQL Server 2022 provides near real-time analytics and hybrid transactional and analytical processing with minimal impact on operational systems.
  • Microsoft Purview integration: SQL Server 2022 is integrated with Microsoft Purview, a unified data governance and management service, for greater data discovery, allowing you to break down data silos, as well as policy management for access control.
  • Ledger: A new technology that 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. By baking trust into the SQL Server Database Engine, Ledger makes it easy to implement multi-party business processes, such as supply chain systems, allowing participants to verify the integrity of the centrally housed data, without the complexity and performance overheads of a blockchain network. Ledger can also help customers streamline compliance audits, providing cryptographic proofs of data integrity to auditors and eliminating manual forensics.
  • Intelligent Query Processing enhancements: We are expanding more scenarios based on common customer problems. For example, the “parameter sensitive plan” problem refers to a scenario where a single cached plan for a parameterized query is not optimal for all possible incoming parameter values. With SQL Server 2022’s Parameter Sensitive Plan optimization feature, we automatically enable the generation of multiple active cached plans for a single parameterized statement. These cached execution plans will accommodate different data sizes based on the provided runtime parameter values.
  • Query Store: We are adding support for read replicas and enabling query hints to improve performance and quickly mitigate issues without having to change the source T-SQL. Also, Query Store hints arrive on SQL Server 2022. Query Store hints provide an easy-to-use method for shaping query plans without changing application code.
  • Security enhancements in SQL Server 2022 include Azure Active Directory authentication, support for richer queries in Always Encrypted with secure enclaves, backing up and restoring certificates using the PFX format, and multiple other improvements to enhance default cryptography in SQL Server to meet the evolving threat landscape.
  • Contained Availability Groups:Supports managing objects (users, logins, permissions, SQL Agent jobs, and more) at the availability group level using specialized contained system databases within the availability group.
  • Multi-write replication: We are automating the last-writer wins rule. Now, when a conflict is detected, the most recent modification time will be chosen to be persisted on all replicas. This helps keep your multi-write scenarios running smoothly.
  • Data lake virtualization and object storage: Query any data lake and backup/restore SQL to S3-compatible object storage.
  • JavaScript Object Notation (JSON) data: New JSON functions to increase the ability to query and manipulate JSON data.
  • Memory and concurrency: In-memory online transaction processing (OLTP) memory enhancements and system page latch concurrency enhancements.

SQL Server 2022: Azure-enabled with continued performance and security innovation from edge to cloud.

Collaboration with our customers and partners

We have collaborated closely with customers and partners over this past half year to test these new features. We have received positive feedback about how the innovation in SQL Server 2022 will empower users to do more.

“Features like Query Store hints and the next generation of Intelligent Query Processing are awesome. The possibility of improving and maintaining great performance over time without code changes is getting better on every new SQL Server version.”Javier Villegas, IT Director for DBA and BI Services, Mediterranean Shipping Company

“With the arrival of Synapse Link for SQL Server 2022, we can finally achieve near real-time analytics over our most important operational data, all without the need to build and manage complex packages and pipelines.”Nic Hopper, Data Architect, Atrium Underwriters   

icon

Learn more


1Public preview for the bidirectional disaster recovery capability of the Link feature for Azure SQL Managed Instance will occur at a future date. Sign up to apply for early access to the capability.

2SQL Server 2022 public preview is currently only available on Windows. SQL Server 2022 public preview for Linux will be available in the near future.

The post Announcing SQL Server 2022 public preview: Azure-enabled with continued performance and security innovation appeared first on Microsoft SQL Server Blog.

Source: Microsoft Blog – SQL Server News – Announcing SQL Server 2022 public preview: Azure-enabled with continued performance and security innovation

Amplify your database development experience with Azure Data Studio

April 22nd, 2022

It is that time again! We are excited to bring new and exciting updates to Azure Data Studio. These updates include the general availability of the Azure SQL Migration Extension, the introduction of advanced features to the Table Designer feature that was initially released in February, some updates to notebooks, Query Plan Viewer, SQL Projects, and Schema Compare.

Announcing general availability for the Azure SQL migration extension in Azure Data Studio

We are excited to announce the general availability of the Azure SQL Migration extension for Azure Data Studio. Among many other capabilities, this extension can be used for migrating SQL Server databases to Azure for an enhanced user experience. With this extension, users can get right-sized Azure recommendations based on performance data collected from your source SQL Server databases to optimize for cost and scale. The migration experience is powered by the Azure Database Migration Service which provides a scalable, resilient, and secure way to meet the needs of your organization. See below for a snapshot UI of this extension.

Azure SQL Migration UI Snapshot

With this extension, you can view and monitor the details of your database migrations. This is as simple as migrating your database using the Azure SQL extention and tracking progress and health of these migrations directly on the Azure portal.

To learn more, take a look at our Tech Community blog.

Support for .NET Interactive Notebooks extension

The .NET Interactive Notebooks extension in Azure Data Studio now provides support for additional languages such as C#, F#, and PowerShell. This multi-language experience enables users to use the best language for the task at hand. Even more exciting is that there is now the ability to use multiple languages in the same Interactive notebook and share variables between those languages using SQL Integration.

.NET Interactive Notebooks Extension

New Table Designer features

Based on user feedback, we have made available more table attributes with advanced features. Azure Data Studio now offers system versioning, graph tables, and Memory-Optimized Tables.

System versioned tables

System versioned tables, also known as Temporal Tables, can now be created in Azure Data Studio, and these tables provide historical information about column data at any given point in time. All data in Temporal Tables exists in direct relation to the time the data was created, updated, or deleted.

So why Temporal Tables?

You guessed it, time travel! System versioning makes it easy to perform “time travel” to see the history of all modifications made to data, which makes tracking changes and data auditing more feasible. Time travel also makes data recovery possible as all changes made to data are tracked in the system-versioned tables, making it easier to revert to the values which existed at the exact time the data was lost. When system versioning is enabled during table creation, the engine automatically creates a separate version of that table. System versioned tables follow the same schema as the original tables from which they were generated.

A system versioned enabled table design

In the table above, we have a system version enabled table named “Department”. A separate historical table, “dbo.MSS…” is automatically created to track all changes made to this table. In the original Department table, a series of changes were made. The table below shows all these changes relative to the time frame they existed. For more information, take a look at our documentation on system versioned tables.

A system versioned enabled table

Graph tables

Graph tables have been available since SQL Server 2017 and can now be designed in Azure Data Studio. Graph tables provide an easier way to create relationships between different datasets using nodes and edge tables. Node or edge tables can be created under any schema in the database, but they all belong to one logical graph. Azure Data Studio brings a newly improved visualization to differentiate node and edge tables. In the table below, node tables are represented by a single dot, while edge tables are depicted by two dots with a line in between connecting them.

Snapshot Depiction of Node vs Edge Table Types

The image below shows a graph node table in the Table Designer. When a table is indicated as a node, the engine automatically assigns an ID to that table (as seen in the UI as “$node_id”) as this becomes the unique identifier to this table. The script is also updated to indicate that this table is a node table.

Snapshot of a Node Table Design

Edge tables, on the other hand, are used to show the relationships between node tables. When an edge table is created, multiple internal columns are created. The ID specific to the edge table, and then the IDs representing the nodes that will be related to each other as seen below. Also, notice the change in the script.

Snapshot of an Edge Table Design

The Table Designer fully supports working with edge constraints for SQL graph edge tables. In the image below, our edge table (named “Likes”) is constrained by “Person to Product” and “Person to Restaurant” clauses. This means that an edge can either link between “Person to Product” or “Person to Restaurant” based on the “likes”. Azure Data Studio brings a one-of-a-kind, easy-to-grasp UI to depict edge and node relationships.

For more information take a look at our documentation on graph tables.

Snapshot of an Edge Table Design showing Constraint configurations

Memory-Optimized Tables

Memory-Optimized Tables can now be created via Table Designer in Azure Data Studio. With Memory-Optimized Tables, data is persisted in memory, rather than on disk, with the option to include durability to schema and/or data. This is particularly useful for applications that have databases that undergo multiple concurrent transactions. See image below for memory-optimized configuration and accompanying script changes. For more information take a look at our memory-optimized tables documentation.

Snapshot of a Memory Optimized Table Design

Query Plan Viewer updates

There have been a small number of visible changes to Query Plan Viewer in this release of Azure Data Studio. We have added icons to note warnings and parallelism when they occur for an operator, and we’ve introduced additional Find options when searching within a plan. There is a new option on the plan toolbar (added to the bottom) which provides the option to disable tooltips, this is particularly helpful when navigating plans with numerous operators.

Snapshot of plan toolbar showing the tooltip enable/disable functionality

Finally, we’ve added support for opening saved .sqlplan files in Azure Data Studio, and we addressed some display issues in the Properties window.  Expect additional enhancements in the next release.

SQL Projects and Schema Compare

In the latest release of the SQL Database Projects extension for Azure Data Studio and Microsoft Visual Studio Code, we are introducing a new SQL Projects format based on an SDK-style project file. The SQL project SDK is named, Microsoft.Build.Sql and it is now available in preview via NuGet and the 0.16.1 version of SQL Database Projects for Azure Data Studio and Visual Studio Code. With Microsoft.Build.Sql, cross-platform pipeline builds are simplified, and the complexity of the project file is significantly reduced. For more information on SDK-style SQL Projects and updates to cross-platform SQL development take a look at this blog.

Other improvements for developers in this release focus on increasing the flexibility of local development. Updating a SQL project from the current state of a database is now enabled in the integration between Schema Compare and SQL Projects and can be accessed directly from the dashboard of a database. For developers focused on working with Azure SQL Database, a new project template has been introduced that enables local validation and testing of SQL Projects for Azure SQL Database.

Snapshot of the dialog for updating a project from a database when launched from a database dashboard

Learn more

We are excited to continue this path to modernizing database experiences for developers and database administrators alike. Watch this space for future updates. To learn more about the Azure Data Studio release and journey, check out the release notes for Azure Data Studio.

The post Amplify your database development experience with Azure Data Studio appeared first on Microsoft SQL Server Blog.

Source: Microsoft Blog – SQL Server News – Amplify your database development experience with Azure Data Studio

We provide excellence in Database Administration

Let's work together