Implementing Ledger Database using SQL Server 2022

Have you ever wondered - how you can you be absolutely sure that no one has tampered with your data after it's been stored? Or how you can create a database that provides an unchangeable history of every update, insert, or delete operation? With SQL Server 2022, this is possible thanks to ledger databases.

In this post, we are going to cover what ledger databases are, why they’re cool, and how you can start using them today. By the end of this, you’ll be ready to implement and explore the tamper-evident features of SQL Server’s latest addition!

Now, what exactly is a ledger database?

A system that automatically records every change made to the data and makes it impossible to tamper with the past. That’s what ledger databases do. In SQL Server 2022, these special tables store your data in a way that ensures all DML operations are recorded — whether it's adding, changing, or deleting data, even dropping tables!

You can use ledger databases for audits, legal records, or anywhere that transparency and integrity are vital.

Before we jump into the "how", SQL server offers two types of ledger tables:

1. Updatable Ledger Tables
   Data changes (insert, update, delete) are allowed, but every past version of each record is saved in a history table. So, even though your data can change, nothing is ever lost or forgotten.

2. Append-Only Ledger Tables
   These are more restrictive. Once data is added, it can’t be updated or deleted—kind of like writing in pen rather than pencil. Perfect for logs or any scenario where you need an unchangeable record.

How Do Ledger Tables Work?

Ledger tables rely on cryptographic hashing to ensure that everything stays tamper-proof. Every time you change a record, it gets bundled into what’s called a commit block. These blocks are then strung together into a hash chain. Each block relies on the previous one, so if anyone tries to alter even a single bit of data, the entire chain gets messed up—and SQL Server will notice.

And you can export it's digest, which is like a tamper-proof receipt that you can use later to verify that no funny business has occurred with your data.

Well, how do I create a ledger table?

1. Create an updatable ledger table -

CREATE TABLE dbo.LedgerTransactions ( 
  TransactionID INT PRIMARY KEY, 
  AccountID INT NOT NULL, 
  Amount DECIMAL(18, 2) NOT NULL, 
  TransactionDate DATETIME NOT NULL ) 
WITH (LEDGER = ON);

That WITH (LEDGER = ON) part is what tells SQL Server, “Hey, I want this table to track all my changes in a tamper-evident way!”

2. Retrieving historical data

SELECT * FROM dbo.LedgerTransactions
FOR SYSTEM_TIME ALL;

3. Create an Append-Only Ledger Table

CREATE TABLE dbo.AuditLogs
(
    LogID INT PRIMARY KEY,
    LogMessage NVARCHAR(500) NOT NULL,
    LogDate DATETIME NOT NULL
)
WITH (LEDGER = ON, APPEND_ONLY = ON);

This way, every log entry is etched in stone—nothing can be overwritten or erased.

4. Verifying your data with Ledger Digests

To make sure everything is as it should be, you can generate a ledger digest, which is like a proof of integrity. This can be stored outside SQL Server to verify your data's integrity over time:

EXEC sp_generate_ledger_digest;

When to use Ledger Features?

Financial Audits: Imagine you're keeping track of financial transactions. Ledger tables will help you ensure that every transaction is auditable and no past records can be altered without leaving a trace.

Supply Chain Tracking: Keep an unchangeable log of each step in your supply chain. From manufacturing to delivery, ledger tables can provide an unbreakable history.

Healthcare Data: In industries like healthcare, where patient records need to be securely stored and unchangeable, ledger tables can provide a reliable solution.

Legal Documentation: Need to keep transparent, untouchable records? Ledger tables ensure that no one can modify important legal documents without a clear history of every change.

Are there alternates in this space?

SQL Server comes with licensing cost and if you are looking for alternates, you can explore any one or more of options below -

1. ImmuDB - opensource and specialized immutable database

2. Amazon QLDB - specialized immutable DB but AWS announced EOL by July-2025.

3. Postgres with pgAudit implementation is able to achieve immutability and ledger features

Well that's it for now, hope you find this post useful.