SQL Server Ledger is a SQL Server tool that provides tamper-proof capabilities for your database. It accomplishes this by tracking the history of updates to your database using a blockchain and Merkle tree data structures. This enables you to cryptographically certify to outside parties, such as auditors or other business partners, that your data has not been tampered with.
The ledger component in your database provides tamper-evidence capabilities. You can use cryptography to prove to third parties, such as auditors or business partners, that your data has not been tampered with. Ledger protects data against any attacker or high-privileged user, including DBAs, system administrators, and cloud administrators. The functionality, like a typical ledger, saves historical data. When a row in the database is modified, its previous value is saved and protected in a history table. The ledger keeps track of all modifications made to the database throughout 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
- In this database, by default, all tables will be ledger tables. You cannot create a normal table in the ledger database.
- Syntax to create a ledger database
CREATE DATABASE database_name WITH LEDGER = ON;
Non-Ledger Database
In this type of database, you can create both ledger and non-ledger tables.
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.
Updatable Ledger Table
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
Updatable Ledger Table
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
output updatable ledger table
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.
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.
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.
HostForLIFEASP.NET SQL Server 2022 Hosting
