What is the database ledger?

APPLIES TO: Azure SQL Database

Note

Azure SQL Database ledger is currently in public preview.

The database ledger is part of the ledger feature of Azure SQL Database. The database ledger incrementally captures the state of a database as the database evolves over time, while updates occur on ledger tables. It logically uses a blockchain and Merkle tree data structures.

To capture the state of the database, the database ledger stores an entry for every transaction. It captures metadata about the transaction, such as its commit timestamp and the identity of the user who executed it. It also captures the Merkle tree root of the rows updated in each ledger table. These entries are then appended to a tamper-evident data structure to allow verification of integrity in the future.

Diagram that shows a Merkle tree for the ledger feature.

For more information on how Azure SQL Database ledger provides data integrity, see Digest management and database verification.

Where are database transaction and block data stored?

The data for transactions and blocks is physically stored as rows in two system catalog views:

  • sys.database_ledger_transactions: Maintains a row with the information of each transaction in the database ledger. The information includes the ID of the block where this transaction belongs and the ordinal of the transaction within the block.
  • sys.database_ledger_blocks: Maintains a row for every block in the ledger, including the root of the Merkle tree over the transactions within the block and the hash of the previous block to form a blockchain.

To view the database ledger, run the following T-SQL statements in SQL Server Management Studio or Azure Data Studio.

Important

Viewing the database ledger requires the VIEW LEDGER CONTENT permission. For details on permissions related to ledger tables, see Permissions.

SELECT * FROM sys.database_ledger_transactions
GO

SELECT * FROM sys.database_ledger_blocks
GO

The following example of a ledger table consists of four transactions that made up one block in the blockchain of the database ledger:

Screenshot of an example ledger table.

A block is closed every 30 seconds, or when the user manually generates a database digest by running the sys.sp_generate_database_ledger_digest stored procedure.

When a block is closed, new transactions will be inserted in a new block. The block generation process then:

  1. Retrieves all transactions that belong to the closed block from both the in-memory queue and the sys.database_ledger_transactions system catalog view.
  2. Computes the Merkle tree root over these transactions and the hash of the previous block.
  3. Persists the closed block in the sys.database_ledger_blocks system catalog view.

Because this is a regular table update, the system automatically guarantees its durability. To maintain the single chain of blocks, this operation is single-threaded. But it's also efficient, because it only computes the hashes over the transaction information and happens asynchronously. It doesn't affect the transaction performance.

Next steps