Data Security with SQL Server 2022

In March 2021, Microsoft previewed SQL Server 2022. For organizations that house, process and analyze data, SQL Server 2022 has been predicted to be a significant game-changer regarding performance, stability, connectivity, and data security. These improvements come from its improved integration with Azure SQL and the brand-new SQL Server Ledger, which makes use of blockchain technology.

Microsoft unveiled its preview of SQL Server 2022 at its Ignite 2021 conference, touting its stronger Azure integration concerning disaster recovery, analytics, reporting, and query intelligence. Furthermore, its long-standing relational database management system (RDBMS) now integrates with various cloud-based Microsoft services.

Concerning your data, here’s what sets SQL Server 2022 apart.

General Improvements

With each iteration, Microsoft strives to advance cloud and on-premises database experiences. With SQL Server 2022, the areas addressed with this upgrade pertain to database recovery, fluidity between operational data and analytics features, data visibility, and examining changes made by outside parties to ensure complete data accuracy.

In examining some of the improvements:

• Core performance optimizes queries by default.
• The Query Store now supports read replicas and factors in query hints.
• Intelligent Query Processing anticipates several custom issues, particularly regarding cached plans and including all potential parameter values. The Parameter Sensitive Plan generates multiple cached plans for a single parameter to factor in a variety of data sizes.

Azure SQL Integration

Microsoft considers SQL Server 2022 its most Azure-integrated solution yet, particularly Azure Synapse Analytics, designed to streamline data warehousing, integration, analytics and visualization, and Purview, ideal for insights, organizing, and overseeing data. A stronger relationship with Azure Synapse Link and Purview improves the types of insights you can discover and your predictive analytics capabilities.

With Azure integration and the possibilities it provides for your data being one of the primary selling points for SQL Server 2022, the key takeaways here are:

• Improved data recovery, by allowing for failover from SQL Server 2022 to Azure SQL Managed Instance operating in the cloud. Using an integrated distributed availability group (DAG), this feature builds off the Azure SQL Managed Instance link capabilities, which turn Azure SQL Managed Instance into a disaster recovery replica resource and allow for multi-subnet clusters and groups with more advanced configurations. This is an upgrade to SQL Server Always On Availability that also lets you discharge significant read requests to Azure SQL Managed Instance and allows for more seamless data replication within the cloud.
• Azure Synapse Link is further critical for flowing data from SQL Server to Azure Synapse Analytics. This change eliminates having to set up an ETL pipeline, the previous solution for migrating data into Synapse’s data warehouse. Instead, this feature replicates data from SQL Server, where it travels to a specific SQL group.
• Integration with Azure Purview improves data discovery. With Microsoft using Azure Purview for managing and overseeing data, this integration improves the types of insights you can develop through strong data discovery, metadata analysis, and custom data classification. This feature further lets you configure who can access the SQL server and to what degree.

SQL Server Ledger and Blockchain Technology

As the latest advancement for data security, SQL Server 2022 features the innovative SQL Server Ledger, powered by blockchain technology. Although SQL Server is known for having fewer vulnerabilities, the SQL Server Ledger upgrades security by utilizing blockchain technology to create a stronger, more accurate record of all data changes.

This update takes inspiration from Azure SQL, which includes a ledger service providing a full record that can’t be overwritten of all updates and transactions that are kept in a secure location.

In considering this baseline, SQL Server Ledger starts here and goes beyond by:

• Preventing data alterations from individuals intent on doctoring or usurping data in some form.
• Providing a more accurate, trustworthy record in the event of a data audit.
• Using blockchain technology to create this list of records, each including a cryptograph record of the previous “block,” plus a timestamp. Through this linked format, altering one block changes the rest, thus posing challenges for internal and third-party individuals attempting to erase, steal or change the data present.
• Supporting a range of applications, including banking and other financial transactions, as well as health care, Internet of Things (IoT), public health, and other data requiring a higher degree of security based on industry regulations.
• Using a mix of append-only and update-only ledger tables. Through this combination, append-only ledger tables let new data be added while preventing updates to any written data. Update-only tables secure all changes ever made and allow for complete data retrieval. What results creates a more secure solution for accessing and updating the data, knowing when changes have been made, and finding the latest information without altering the history of all records and provides a more authentic and secure access log. The ledger further lets you use one or a combination of both table types for storing data types.

Streamlining the process for adding secure transactional systems to the application database. This can be achieved by a T-SQL command, instead of having to create and add a new application stack to manage, record, and secure all transactions.

As of December 2021, Microsoft continues to offer SQL Server 2022 in Private preview, with access granted to a select number of customers.