Azure Sql Database Ledger — Updatable / Append only Ledger Tables

Over a week before Microsoft released Azure Sql Database Ledger to its Blockchain service. Database ledger is not Blockchain , But Microsoft has introduced a service similar to AWS QLDB where transaction details in database are tracked and provides a guarantee to other parties that data are not manipulated/tampered using Cryptography signatures.

What is Azure Sql Database Ledger ?

A service that guarantees data integrity and provides trust to third party that data exposed is tamper proof using Crypto patterns. Microsoft offers two flavors of Ledger DB

a)Ledger Updatable Tables.

b) Append only Tables.

How is this different from writing a trigger/constraint in SQL and restricting / tracking changes ?

The trigger / constraint is still owned by data owner and there is no guarantee that owner can manipulate it, while utilizing service is different.

Getting Hands-on with Sql Database Ledger :

Microsoft has eased the job of developers and its quite easily configurable. Currently SQL Ledger is available in West Central US region.

The below screenshot is from Azure portal for SQL Database with Ledger Enabled.

I have used VS to connect to the Azure Ledger Sql DB and would walk through append only tables. Below is the script to create table with Ledger Enabled.

CREATE SCHEMA [AccessControl] 
CREATE TABLE [AccessControl].[KeyCardEvents]
    (
        [EmployeeID] INT NOT NULL,
        [AccessOperationDescription] NVARCHAR (MAX) NOT NULL,
        [Timestamp] Datetime2 NOT NULL
    )
    WITH (
    	  LEDGER = ON (
    	               APPEND_ONLY = ON
    	               )
);

An Insert record would create an entry into ledger and you could view history as below

Now Lets try to update the same record , we would land up with error message claiming its append-only database an cannot be updated.

The other version is updatable Ledger that enables version tracking which would be case for most scenarios.

Summary:

Azure Sql Ledger DB is useful for enterprise companies who only wants to prove to other parties that data is tamper proof and want be transparent when dealing with data /sharing data to other parties. Microsoft has simplified service and has made it user friendly for consumption, we would see lot of use-cases/ implementation with such DB.