Randolph West has a series on ledger tables in SQL Server. First up is a primer on the topic:
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.
Part two separates out the two types of ledger table:
This week we will look at the different types of ledger table: append-only and updatable.
Unlike temporal tables, a ledger table can be append-only which makes it immutable. You can only insert data and therefore it does not need a history table. In fact, you may be using append-only tables in your data warehouse already. While this is secure, it may not be practical.
Part three covers limitations:
Every choice we make is a trade-off. New features have limitations, and ledger tables are no exception.
Some of these limitations are perfectly sensible. For example, the whole point of ledger tables is to ensure that we can provide tamper evidence. This necessarily means you can’t turn it off once it’s enabled, unless you drop the database entirely — this is just one scenario where a full defence-in-depth strategy is required.
Part four is the one I’ve been waiting for—an explanation why you probably don’t need this:
After writing several posts about a neat feature in Azure SQL called system-versioned ledger tables, it reminded me about something I’ve wanted to say for a number of years now, outside of snarky tweets.
Here goes:
You don’t need a blockchain.
In the vast majority of use cases, you need a properly audited relational database system with ACID compliance and a good recovery strategy.
There are very specific use cases in which data hashes and ledger tables make sense.