What is SQL Server Ledger?

Introduction

SQL Server Ledger is a feature of SQL Server that provides tamper-evident capabilities for your database. It does this by using a blockchain and Merkle tree data structures to track the history of changes to your database. This allows you to cryptographically attest to other parties, such as auditors or other business parties, that your data hasn't been tampered with. 

The ledger feature provides tamper-evidence capabilities in your database. You can cryptographically attest to other parties, such as auditors or business parties, that your data hasn't been tampered with. Ledger helps protect data from any attacker or high-privileged user, including database administrators (DBAs), system administrators, and cloud administrators. As with a traditional ledger, the feature preserves historical data. If a row is updated in the database, its previous value is maintained and protected in a history table. Ledger provides a chronicle of all changes made to the database over time.

Ledger and the historical data are managed transparently, offering protection without application changes. The feature maintains historical data in a relational form to support SQL queries for auditing, forensics, and other purposes. It guarantees cryptographic data integrity while maintaining the power, flexibility, and performance of the SQL database.

How does SQL Server Ledger Work?

When you make a change to a ledger table in SQL Server, the change is first recorded in a blockchain. The blockchain is then used to create a Merkle tree. The Merkle tree is a blockchain hash that can be used to verify that the blockchain hasn't been tampered with. The Merkle tree is also used to create a history of changes to the ledger table. The history of changes is stored in a history table. The history table allows you to see the original value of a row, as well as the changes that have been made to the row over time.

Any rows modified by a transaction in a ledger table are cryptographically SHA-256 hashed using a Merkle tree data structure that creates a root hash representing all rows in the transaction. The transactions that the database processes are then also SHA-256 hashed together through a Merkle tree data structure. The result is a root hash that forms a block. The block is then SHA-256 hashed through the root hash of the block, along with the root hash of the previous block as input to the hash function. That hashing forms a blockchain. The root hashes in the database ledger, also called Database Digests, contain the cryptographically hashed transactions and represent the state of the database. 

What are the benefits of using SQL Server Ledger?

There are several benefits to using SQL Server Ledger, including:

• Tamper-evident: SQL Server Ledger provides tamper-evident capabilities for your database. This means that you can cryptographically attest to other parties that your data hasn't been tampered with.
• Auditability: SQL Server Ledger makes it easy to audit your database. The history of changes to your database is stored in a history table, which allows you to see the original value of a row, as well as the changes that have been made to the row over time.
• Compliance: SQL Server Ledger can help you comply with regulations that require you to maintain the integrity of your data. For example, SQL Server Ledger can be used to comply with the Sarbanes-Oxley Act (SOX) and the Health Insurance Portability and Accountability Act (HIPAA).

How to use SQL Server Ledger?

To use SQL Server Ledger, you first need to create a database. You have two options either create a ledger database or a non-ledger database(normal database in SQL Server). 

Ledger Database

1. In this database, by default, all tables will be ledger tables. You cannot create a normal table in the ledger database.
2. Syntax to create a ledger database

CREATE DATABASE database_name WITH LEDGER = ON;

Non-Ledger Database

1. In this type of database, you can create both ledger and non-ledger tables.
2. Syntax to create a non-ledger database

CREATE DATABASE database_name

Once you have created a database as per your requirement, you can create ledger tables by using the LEDGER = ON clause in the CREATE TABLE statement. There are two kinds of ledger tables you can create. 

1. Updatable Ledger Table
2. Append Only Ledger Table

Their name already explains the meaning and what kind of scenarios they are intended for.

Updatable Ledger Table

This type of table maintains the history of the updated and deleted data from the main table. That is why an updatable ledger table creates two more tables apart from the main table, as given below.

• The main table
• A history table: The history table holds the history of all changes on the table.
• A View: Contains a collection of both main and history table data.

The syntax to create an updatable ledger table is given below.

CREATE TABLE Product(table_name)
(
     [ID] INT NOT NULL PRIMARY KEY CLUSTERED,
     [Amount] VARCHAR (100) NOT NULL,
     [CreatedDate] DATETIME NOT NULL,
     [IsActive] bit NOT NULL
)
WITH (
     SYSTEM_VERSIONING = ON,
     LEDGER = ON
)

Output

As you can see in the above output, you have one main table, one history table, and one view table. The product table also contains some hidden columns added by default in the table. ledger_start_transaction_id, ledger_end_transaction_id, ledger_start_sequence_number, and ledger_end_sequence_number. 

• [ledger_start_transaction_id]: The Id of the last transaction which changed this record, turning it into the values it has.
• [ledger_end_transaction_id]: This field is the ID of the transaction that changed this record into another.
• [ledger_start_sequence_number]: Sequence number of the statement at the start of the transaction.
• [ledger_end_sequence_number]: Sequence number of the statement at the end of the transaction.

Now let's do some operations on the Product ledger table.

INSERT

First, insert some data in the table using the below query.

INSERT INTO Product
     VALUES (1, '100', GETUTCDATE(),1),
	        (2, '200', GETUTCDATE(),1),
			(3, '100', GETUTCDATE(),1)

Now execute the below query to see the data in all the tables.

 --main table
	 select [ID], [Amount], [CreatedDate], [IsActive],
      [ledger_start_transaction_id], [ledger_end_transaction_id],
      [ledger_start_sequence_number], [ledger_end_sequence_number]
     from Product

	 --view table
	select * from Product_Ledger
	order by ledger_transaction_id,ledger_sequence_number

	  --history table
    select * from MSSQL_LedgerHistoryFor_1013578649

Output

As you can see, we do not have any data in the history table because we have not updated or deleted anything yet.

UPDATE

Now update some data in the table.

UPDATE Product SET Amount=500 WHERE ID=3

Output

When we do any update in the Updatable Ledger Table, the view maintains two operations first, delete old data and then insert new data. That is why the view has two new entries, as given in the above image. The history table also now has an entry of old data from the main table, which you just updated. ledger_end_transaction_id of this entry is equal to the ledger_start_transaction_id of the third entry in the main table. Now move to the delete operation.

DELETE

DELETE FROM Product WHERE ID=3

Output

When we delete something from the ledger table, as you can see, deleted data moves to the history table, and the view table has one more entry. 

Conclusion

Updatable ledger tables are system-versioned tables on which users can perform updates and deletes while also providing tamper-evidence capabilities. When updates or deletes occur, all earlier versions of a row are preserved in a secondary table, known as the history table. The history table mirrors the schema of the updatable ledger table. When a row is updated, the latest version of the row remains in the ledger table, while its earlier version is inserted into the history table by the system, transparently to the application.

To learn more about how to use SQL Server Ledger, you can refer to the following resources:

• SQL Server Ledger Overview: Ledger Overview
• Create and use updatable ledger tables: Create and User Updatable Ledger Table

I hope this article has helped you to understand what SQL Server Ledger is and how it works. In the next article, we will see about the Append-Only ledger Table. If you have any other questions, please feel free to ask me.

Thank You, and Stay Tuned for More