As long-time readers of this blog know, I’m a big fan of temporal tables, also known as system-versioned temporal tables. Until recently, temporal tables were synonymous with system-versioned tables, but all that changed a short while ago with the introduction — in Azure SQL Database — of system-versioned ledger tables.
This new series of posts leans heavily on the content from the official documentation (as well as early access to the feature), so not much of it is original work. I might explain things in a slightly different way, but your best bet for more information is always going to be Microsoft Docs.
Refresher on temporal
As a refresher, I want to remind you that temporal tables allow you to keep track of a table’s history (stored conveniently in a page-compressed history table in the same database as your temporal, or current, table). Both the current and the history table have a start and end column (in DATETIME2) to specify the validity of the data. This allows you to write temporal SELECT queries that can reconstruct the data in that table at some point of time in the past, provided that the information is available1It’s a cool feature, but it’s not magic. in the history table.
There are also some serious limitations if you’re hoping for a trusted audit trail or chain of custody. For instance, someone with the right permissions can change or delete data in the current and history tables. For example, my favourite demo in my talk on temporal tables is showing how if you drop a column in a temporal table, it is also dropped from the history table without warning. Unless those DDL (data definition language) operations were being logged elsewhere through database auditing, no one would know.
Ledger
System-versioned ledger tables leverage the same technology: there is a table with current data in it, and an underlying history table which keeps track of changes. However, it uses a cryptographic chain that provides digital forensic evidence of tampering. Yes, if you’ll pardon the use of this phrase, I’m talking about a blockchain.
This is not a cryptocurrency. No one is using expensive graphics cards to produce a fiat currency in someone’s basement. Instead, each transaction affecting the database in question is cryptographically hashed using a SHA-256 algorithm and then stored somewhere off-site.
Yes, my eagle-eyed readers, Azure SQL Database Ledger is available at the database level as well as the table level. If the whole database is ledger-enabled, every table you create will also be ledger-enabled. You will therefore have to monitor your storage requirements closely, because tamper evidence is not free, and if you’re keeping a history of all the changes in your database you could end up with serious storage requirements. It’s also worth mentioning that some data types are not supported. We’ll get into that later in the series.
Crypto
Each time a transaction happens in the database, the ledger feature uses a hash of the current transaction as well as the hash of the previous transaction as its input for the hash function. This is what creates the blockchain (known as the database digest). Each transaction relies on the hash of the previous transaction. Then, each row in a ledger-enabled table keeps track of these hashes so you can point to the exact transaction when a change occurred (in a similar way to how a temporal table works).
Of course, this is only as secure as the storage location of the database digest. Microsoft naturally prefers that you keep your digest stored within the Azure family and suggests one of two perfectly amenable and immutable Azure services to do so: Azure Blob Storage, and Azure Confidential Ledger. The database digest is copied and stored off-site in this immutable area so that you have something to compare against if you need to.
That is after all the entire point of this service: if at any point you need to confirm to lawyers, auditors, or law enforcement that your database has not been compromised, you have the means to do so. This is the one feature of temporal tables that was seriously lacking, and I’m very pleased that it is finally available in Azure SQL Database.
Some of you may be wondering if this will ever appear in SQL Server as well. Even though I’m an MVP and I’ve seen and heard things I’m not allowed to share, I can say I have no idea what the next version of SQL Server will be called or when it will be released, but I can guess that this feature will make its way into the boxed product. As for where to store the off-site digest, I assume it will work in a similar way to Azure SQL Database, and likely use Azure Storage or Azure Confidential Ledger as well.
Tune in next time as we get into the details of how ledger tables work, and in the meantime share your thoughts in the comments below.