Announcing Azure SQL Database ledger
Published May 25 2021 08:00 AM 41.8K Views
Microsoft

Blockchain has been transformational for enterprises, digitizing trust for multi-party workflows. Blockchain decentralizes the data and the business logic which produces the data that augments, or replaces, the necessary business contracts in place between parties to ensure agreed-upon business practices are followed.

 

But blockchain isn't always the right fit for all systems that require digitization of trust.  What if you have a centralized system where you want to prove your data's integrity to other parties?  For example, proving to auditing or regulatory authorities that your data has not been tampered by a malicious party?  Today I'm excited to announce Azure SQL Database ledger, which adds tamper-evident capabilities to your Azure SQL Databases, available in Preview starting in West Central US with all regions following shortly.

 

The power of blockchain, the simplicity of SQL

Blockchain is still relatively nascent, particularly in the enterprise.  Protocols are rapidly evolving, development tooling is continuing to mature, and standards are beginning to take hold.  However, many enterprises are attracted to blockchain to enable digital trust for systems which are fundamentally centralized at their core.  Building a new system based on blockchain, where data must be migrated and business logic must be implemented into smart contracts, is not a small undertaking.  While worth the effort for systems that are low-trust and require decentralization, for centralized systems traditional blockchain may just be overkill.

 

Azure SQL Database ledger provides a simpler solution for these centralized systems where strengthening trust between parties is required.  As a feature of Azure SQL Database, the ledger feature does not require any migration of data or changes to your applications.  You can enable ledger functionality on tables in your database and interact with them the same ways you would for any other tables.

 

Avanade Color Logo RGB.png

"In the time we've been working with it, Azure SQL Database ledger has proven incredibly powerful for organizations that need attestation, and auditing for their end-to-end data, without the overhead of a whole blockchain consortium. With the ledger feature, you have a best-in-class database, allowing you to build with your existing enterprise stack, providing security, with increased speed of development.  Read the use cases that Avanade sees for Azure SQL Database ledger, including a sample application showcasing how to use the ledger feature for healthcare tracking.

Chris-Lloyd Jones

Product & Engineering Lead, Emerging Technology

Trust, but verify you data

With the ledger feature, you will be able to detect if data in your Azure SQL Database has been maliciously altered and if so, restore it back to the original value.  Using the same cryptographic patterns seen in blockchain technology, each transaction is cryptographically hashed and inserted in a blockchain data structure.  The computed hashes of your database are then stored outside of Azure SQL Database in tamper-proof storage (such as Azure immutable Blob storage, or Azure Confidential Ledger), as database digests.  Database digests are used later to verify that data in the database has not been tampered with, by comparing the hash values in the digests against the calculated hashes stored in Azure SQL Database ledger.  If even a single bit is altered in the database, the database verification process will detect and report the tampering. 

 

Picture1.png“Azure SQL Database ledger delivers our platform with all of the benefits that blockchain technology provides, without the additional heavy lifting of the blockchain infrastructure, performance considerations and challenges associated with data residency. Working closely with Microsoft on this technology has enabled us to refine our proposition, providing additional benefits to the worlds commercial banks and regulators alike.”

Andrew Smith

Chief Technology Officer

 

Azure SQL Database ledger provides two types of ledger-enabled tables for your database: updatable ledger tables and append-only ledger tables.

 

Updatable ledger tables

Most existing applications have a pattern of inserting, as well as updating and deleting data.  How, then, do you provide tamper-evidence when your applications modify data in your database?  Updatable ledger tables capture the historical values of rows that have been changed or deleted in your database in an associated history table.  However, capturing the historical values of rows changed by itself isn’t helpful unless you know which transactions initiated the change, along with the user who made those changes.

 

Ledger-Updatable-Architecture.png

 

Each transaction in an updatable ledger table is assigned a unique transaction ID, as well as tracking the sequence of row modifications in the transaction.  This information allows you to perform the forensics necessary to understand the data lineage of all changes to data in your database over time.

 

For example, in a simple banking transaction scenario, Nick originally had a balance of $50 (a) in his account.  Later he deposits an additional $50 into his account, updating his total balance to $100.  This is represented in the ledger view as a DELETE of his original balance of $50, and an INSERT of a new row of $100 (b).  A ledger view is created for your updatable ledger table which shows both the history of the data, as well as the transaction IDs that initiated the data changes.

 

Updatable.png

 

While a real-world account ledger schema would be much more complex with normalized tables, this is just a simple example to show the relationship of managing historical data in updatable ledger tables.  Follow this tutorial, which uses the example above, on how to use updatable ledger tables.

 

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.  By blocking UPDATE and DELETE operations, there is no need for an associated history table.  Additionally, append-only ledger tables provide an extra layer of security, preventing privileged users from modifying the data in the append-only ledger table.

 

SQL Ledger Architecture Append.png

 

Another benefit of append-only ledger tables is its ability to extend blockchain applications.  Blockchains are not optimized for high-throughput queries, nor do they provide the relational capabilities needed for business intelligence reporting.  By replicating the blockchain data to Azure SQL Database ledger, customers have the ability to easily query blockchain data in a performant manner, and more importantly, persist the data integrity from the blockchain to the off-chain append-only ledger tables.  Follow this simple tutorial to learn how to use append-only ledger tables.

 

bp.png

 "bp decided to explore alternatives to address the major impediments of the blockchain solution they were using, e.g. developer velocity, querying of the ledger and infrastructure costs.  Azure SQL Database ledger provides a tamper evident data store for a centralised solution that relies on a high degree of trust in the integrity of the data. Using Azure SQL Database ledger has helped increase developer velocity and we expect it to enhance bp’s ability to build secure trusted applications to support our core value of safety.”

Christian Mare

Azure Architect - Digital Production & Business Services

 

Learn more

Ready to get started and learn more?  Check out the below resources and we look forward to seeing what you build with this Azure SQL Database ledger!

 

 

4 Comments
Co-Authors
Version history
Last update:
‎May 25 2021 08:51 AM
Updated by: