You probably hear or view that on the security point of view, the next version of SQL Server: SQL Server 2022, has a feature named Ledger.
It’s already used in Azure but what is Ledger?
In 2 points, I will say:
- Ledger helps streamline audits, providing cryptographic proof of data integrity to auditors.
- Ledger is also a protection of the data from any attacker or high-privileged user, including database or system administrators.
Microsoft provides us a good schema to explain how it’s working:
If any changes is done (DELETE,UPDATE) in your table, the previous value goes in a history table.
This is like Temporal Table, isn’t it? Have a look on here (I know the author 😉 )
Yes and No. Yes for the principle but no because it’s also protected and going deeper for where does what…
The technology used is the blockchain. Each records will be crypted and have the previous block crypted and a timestamp.
I this blog, I will just explain how it’s working without the crypted side.
Let’s start our first steps…
We can create directly a database with the option WITH LEDGER = ON
CREATE DATABASE [test_ledger]
( NAME = N'test_ledger', FILENAME = N'D:\DATA\test_ledger.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
( NAME = N'test_ledger_log', FILENAME = N'L:\LOG\test_ledger_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
With LEDGER = ON
You can easily see with sys.databases, if it’s enable or not with the column name is_ledger_on:
select name, is_ledger_on from sys.databases
I try also to do an alter database to disable it but it’s not working, I would say “not yet”…
After that, I create a table with the syntax of the temporal table and adding the ledger syntax:
CREATE TABLE [test_table_ledger]
Id int not null primary key clustered,
test1 varchar(10) NOT NULL,
test2 varchar(10) NOT NULL
SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.test_table_ledger_hist),
LEDGER = ON
As you can see in the new SSMS, the table is marked as Updatable Ledger and you have an History table associated
If I have a look at these 2 tables, they are empty:
I insert 5 values to have some rows in my table:
No changes with these inserts, the history table is logically empty.
Now, I will do a simple update of the first row:
As you can see, the history table has the old value and my table is up to date with the new value
The recommendation of Microsoft is to use the ledger view for the history table and not directly the history table.
3 views are possible. The first one is sys.database_ledger_transactions and I will take only about this one in this blog
This view give us the history of the database transactions:
No very digest to read, but if I look, a new view is created though the Ledger with the name of my table + _Ledger.
In this case you can easily see the operation type
Combining this last view and sys.database_ledger_transactions, you have a trace of the operations.
In my case, 5 insert and 1 update (INSERT + DELETE)
select dlt.commit_time, dlt.principal_name, test.id,test.test1, test.test2, test.ledger_operation_type_desc
from test_table_ledger_Ledger test join sys.database_ledger_transactions dlt on test.ledger_transaction_id = dlt.transaction_id
Another interesting part of this new feature is the stored procedure to copy data from a normal table to a ledger table: sp_copy_data_in_batches
But this will be another story…
Hoping to have piqued your curiosity! 👍