This article was originally written by Avanade alum Barny Self.

Hot off the press from MS Build 2021 is the news that SQL Server is implementing one of the hottest technologies currently in the news, Blockchain. Yes, you read that correctly, Blockchain!

So what is blockchain and why is this such big news you may ask? From Wikipedia: “A blockchain is a growing list of records, called blocks, that are linked using cryptography. Each block contains a cryptographic hash of the previous block, a timestamp, and transaction data (generally represented as a Merkle tree). By design, a blockchain is resistant to modification of its data. This is because once recorded, the data in any given block cannot be altered retroactively without alteration of all subsequent blocks.”

This is a huge leap forward for the SQL relational engine as this now allows developers to implement secure transactional systems directly into the existing application database using simple T-SQL commands rather than having to implement a whole new application stack just to handle the blockchain or ledger of transactions and the secure history of those transactions.

Whilst this is not is a way of creating a distributed transactional cryptocurrency such as the almighty Bitcoin (other cryptocurrencies are available), it does open up a whole raft of new ways of working within SQL.

The SQL team have given us two new types of tables, called ledger tables, that can be either append only or the rather more traditional type of SQL table that is updateable.

As you may guess, append only ledger tables only allow the addition of new data to the table and blocks both updates and deletes to the data once written. This opens up the opportunity to allow implementation of secure storage of security access logging for audit and compliance with industry regulation standards. It also allows for storing data for other hot topics, like Healthcare, Dispatch notices – and the big one, IOT sensor data, which also can come under regulatory scrutiny in industries such as food or vaccine transport where correct refrigeration of the produce is critical in public health safety.

Updateable ledger tables on the other hand allow for the full use of all CRUD operations but with one caveat, the full history of every change to every row is securely stored and can be retrieved at any time. This is useful in situations where you want to be able to easily retrieve the current status of a dataset but still need the full history of all transactions.

So what problems will this help me to solve you may ask? A few different scenarios come to mind.

In the first instance a developer is looking into implementing some form of security access logging, be it to sensitive files or even a secure building. In this instance they could use an append only ledger table and log userids and timestamps of the resource that the user is accessing. This give a secure, immutable and verifiable log of all access to the resource.

A second scenario could be on the ingestion of IOT sensor data for a haulage firm. Let’s assume industry regulations require logging of various different metrics such as vehicle position, speed, cargo type and refrigerator temperatures when carrying specified loads. In this case you could use a hybrid approach and utilise both types of tables. In the update only table, you have an easy to query full log of all sensor data so that you can map metrics over time and build out data mining models to predict failures. The second updateable table can be used to hold a live snapshot of the vehicles within a fleet that are currently in transit and their current status. This again would hold the full audit trail of the ledger for verification that it has not been in some way tampered with.

We’ve been enjoying putting this cool new technology through its paces, and we’re continuing to explore the capabilities and limitations of it. Have a look at our GitHub repository, and look out for more posts on the underlying technical aspects and implementations as the ledger technology evolves.

Are you as excited about this announcement as we are? Leave your thoughts in the comments below.