{"id":1867,"date":"2023-04-24T06:00:08","date_gmt":"2023-04-24T13:00:08","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=1867"},"modified":"2023-04-23T16:02:12","modified_gmt":"2023-04-23T23:02:12","slug":"using-ledger-in-azure-sql-database","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/using-ledger-in-azure-sql-database\/","title":{"rendered":"Using Ledger in Azure SQL Database"},"content":{"rendered":"

Transactions have to be protected and you need to know when something is altered or prevent altering of records from the start. Ledger, available in Azure SQL Database and SQL Server 2022, helps to protect your database and establish trust, accuracy and integrity in your data.<\/p>\n

Ledger accomplishes this goal in a couple of ways. First, it provides you with a history of records in your database; if a row was updated or deleted in the database, its previous value is maintained and protected in a history table. This provides you with a chronicle of all changes made to the table over time. You can even enable this feature database wide so that all tables can have this historical information.<\/p>\n

The second way Ledger helps is by creating insert only tables. Update and deletes cannot be done on these tables making them perfect for scenarios such as security information and event management (SIEM) applications. Both these features incorporate a blockchain that reside in the Azure SQL Database ledger. More on how Azure SQL Database uses blockchain at the end of this post.<\/p>\n

For database and application developers, this is a very simple to enable feature that will help to create trust between the data and your stakeholders.<\/p>\n

Create an Updatable Ledger Table<\/h2>\n

<\/i>Important!<\/strong><\/p>Creating ledger tables requires the ENABLE LEDGER permission.<\/div><\/p>\n

Let’s create some of these tables and see how they work. We will start with an Updatable Ledger Table<\/strong>. To note, both updatable ledger tables and temporal tables<\/a> are system-versioned tables; they are created with the SYSTEM_VERSIONING clause. When set, the database engine captures historical row versions in a secondary history table (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [SCHEMA].[HISTORY_TABLE_NAME]). <\/span>The main difference is that updatable ledger tables make both the current and historical data tamper evident.<\/p>\n

The code to create this table is quite simple, just like a regular table, but with LEDGER = ON<\/strong> in the SYSTEM_VERSIONING\u00a0<\/span>clause. For this example, we can use a Video Game high scores table so that we can record who got what score on a particular game and know if someone went and inflated\/changed their score.<\/p>\n

\n
CREATE TABLE [dbo].[HighScores]\r\n(\r\n \u00a0  [ScoreID] INT IDENTITY,\r\n  \u00a0 [PlayerID] INT NOT NULL,\r\n  \u00a0 [FirstName] VARCHAR (50) NOT NULL,\r\n  \u00a0 [LastName] VARCHAR (50) NOT NULL,\r\n  \u00a0 [Game] VARCHAR (50) NOT NULL,\r\n  \u00a0 [Score] INT NOT NULL,\r\n \u00a0  [Date] DATE NOT NULL\r\n)\r\nWITH\r\n(\r\n SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[HighScoresHistory]),\r\n LEDGER = ON\r\n);<\/pre>\n
\n
The database automatically adds four\u00a0GENERATED ALWAYS<\/strong><\/code> columns that contain metadata noting which transactions made changes to the table and the order of operations by which rows were updated.<\/p>\n
\n\n\n\n\n\n\n\n\n
Default column name<\/th>\nData type<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
ledger_start_transaction_id<\/td>\nbigint<\/td>\nThe ID of the transaction that created a row version<\/td>\n<\/tr>\n
ledger_end_transaction_id<\/td>\nbigint<\/td>\nThe ID of the transaction that deleted a row version<\/td>\n<\/tr>\n
ledger_start_sequence_number<\/td>\nbigint<\/td>\nThe sequence number of an operation within a transaction that created a row version<\/td>\n<\/tr>\n
ledger_end_sequence_number<\/td>\nbigint<\/td>\nThe sequence number of an operation within a transaction that deleted a row version<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
And we can see this in the table structure after the create table event (Azure Data Studio even marks the table as an Updatable Ledger<\/strong> table as well):<\/p>\n
\"Image<\/a><\/p>\n
Working With the Table<\/h3>\n
Time to add some data to the table:<\/p>\n
\n
INSERT INTO [dbo].[HighScores]\r\nVALUES (1, 'Steve', 'Wobble', 'Horse Monkey', 11099912, GETDATE()),\r\n(2, 'Bill', 'Mickelson', 'Horse Monkey', 11099911, GETDATE()),\r\n(3, 'John', 'Hill', 'Universe Invaders', 218870, GETDATE());<\/pre>\n<\/div>\n<\/div>\n
and look at the table with the generated columns:<\/p>\n
\n
select\r\n   [ScoreID],\r\n  \u00a0[PlayerID],\r\n  \u00a0[FirstName],\r\n  \u00a0[LastName],\r\n  \u00a0[Game],\r\n  \u00a0[Score],\r\n  \u00a0[Date],\r\n  \u00a0[ledger_start_transaction_id],\r\n  \u00a0[ledger_end_transaction_id],\r\n  \u00a0[ledger_start_sequence_number],\r\n  \u00a0[ledger_end_sequence_number]\r\nfrom [dbo].[HighScores];<\/pre>\n<\/div>\n<\/div>\n<\/div>\n
For each updatable ledger table, there is a view created. For our HighScores table, the view [dbo].[HighScores_Ledger] was created.<\/span><\/div>\n
\"Image<\/a><\/div>\n
<\/div>\n
This table has four special columns as well:<\/div>\n
\n\n\n\n\n\n\n\n\n
Default column name<\/th>\nData type<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
ledger_transaction_id<\/td>\nbigint<\/td>\nThe ID of the transaction that created or deleted a row version.<\/td>\n<\/tr>\n
ledger_sequence_number<\/td>\nbigint<\/td>\nThe sequence number of a row-level operation within the transaction on the table.<\/td>\n<\/tr>\n
ledger_operation_type<\/td>\ntinyint<\/td>\nContains\u00a01<\/code>\u00a0(INSERT<\/strong>) or\u00a02<\/code>\u00a0(DELETE<\/strong>). Inserting a row into the ledger table produces a new row in the ledger view that contains\u00a01<\/code>\u00a0in this column. Deleting a row from the ledger table produces a new row in the ledger view that contains\u00a02<\/code>\u00a0in this column. Updating a row in the ledger table produces two new rows in the ledger view. One row contains\u00a02<\/code>\u00a0(DELETE<\/strong>), and the other row contains\u00a01<\/code>\u00a0(INSERT<\/strong>) in this column.<\/td>\n<\/tr>\n
ledger_operation_type_desc<\/td>\nnvarchar(128)<\/td>\nContains\u00a0INSERT<\/code>\u00a0or\u00a0DELETE<\/code>. For more information, see the preceding row.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
Now, what if someone went into the database and updated that row? And can the database tell us who? Join the ledger view to the sys.database_ledger_transactions table and get that “who”.<\/span><\/div>\n
\n
\n
SELECT\r\n t.[commit_time] AS [CommitTime],\r\n t.[principal_name] AS [UserName],\r\n h.[ScoreID],\r\n h.[PlayerID],\r\n h.[FirstName],\r\n h.[LastName],\r\n h.[Game],\r\n h.[Score],\r\n h.[Date],\r\n h.[ledger_operation_type_desc] AS Operation\r\n FROM [dbo].[HighScores_Ledger] h\r\n JOIN sys.database_ledger_transactions t\r\n ON t.transaction_id = h.ledger_transaction_id\r\n ORDER BY t.commit_time DESC;<\/pre>\n<\/div>\n
\"Image<\/a><\/div>\n
<\/div>\n<\/div>\n
Here Comes Bill<\/h3>\n
I am currently logged into the database as sqladmin and it shows because I created those inserts. What if someone else were to do this with another account, maybe Bill?<\/div>\n
(Bill is using the new Azure Data Studio Create User feature!)<\/strong><\/div>\n
\"Image<\/a><\/div>\n
<\/div>\n
Then, Bill does an update to the table:<\/div>\n
<\/div>\n
\n
\n
UPDATE [dbo].[HighScores]\r\n  \u00a0 SET Score = 11099913\r\n  \u00a0 WHERE ScoreID = 2;<\/pre>\n
<\/div>\n
What’s the ledger view look like now?<\/div>\n<\/div>\n<\/div>\n
\"Image<\/a><\/div>\n
<\/div>\n
In the blue box, we see that Bill performed the record change. In the green box, we see the old record marked as a delete and in the orange box, the new updated record.<\/div>\n
Why a delete? From the docs:<\/div>\n
\n\n\n\n
Contains\u00a01<\/code>\u00a0(INSERT<\/strong>) or\u00a02<\/code>\u00a0(DELETE<\/strong>). Inserting a row into the ledger table produces a new row in the ledger view that contains\u00a01<\/code>\u00a0in this column. Deleting a row from the ledger table produces a new row in the ledger view that contains\u00a02<\/code>\u00a0in this column. Updating a row in the ledger table produces two new rows in the ledger view. One row contains\u00a02<\/code>\u00a0(DELETE<\/strong>), and the other row contains\u00a01<\/code>\u00a0(INSERT<\/strong>) in this column.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
 <\/p>\n
Create an Append-only Ledger Table<\/h2>\n
Append-only ledger tables<\/strong> allow INSERT operations on the table; DELETES and UPDATES cannot be done. This is great for use cases such as recoding entry into a building or room with a key card; any scenario where you need to insert events as they happen.<\/p>\n
Creating one is similar to the Updatable table, but with the following clause: WITH<\/span> (LEDGER = ON<\/span> (APPEND_ONLY = ON<\/span>))<\/strong>. For this next example, we can imagine a special room where these arcade games are kept and only by using a special ID card, can you get access to play them for high scores. You need to use a badge to enter and exit this room.<\/p>\n
CREATE TABLE [dbo].[ArcadeRoomAccess]\r\n<\/span>  \u00a0(\r\n \u00a0 \u00a0  [AccessID] INT IDENTITY,\r\n  \u00a0 \u00a0 [PlayerID] INT NOT NULL,\r\n  \u00a0 \u00a0 [AccessEvent] NVARCHAR (100) NOT NULL,\r\n  \u00a0 \u00a0 [Timestamp] Datetime2 NOT NULL\r\n  \u00a0)\r\n  \u00a0WITH (LEDGER = ON (APPEND_ONLY = ON));<\/pre>\n
\n
Again, just like the Updatable table, the database automatically adds <\/span>GENERATED ALWAYS<\/strong><\/code> columns. Here we only have two:<\/span><\/div>\n<\/div>\n\n\n\n\n\n\n
Default column name<\/th>\nData type<\/th>\nDescription<\/th>\n<\/tr>\n<\/thead>\n
ledger_start_transaction_id<\/td>\nbigint<\/td>\nThe ID of the transaction that created a row version<\/td>\n<\/tr>\n
ledger_start_sequence_number<\/td>\nbigint<\/td>\nThe sequence number of an operation within a transaction that created a row version<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Which we can see in Azure Data Studio by looking at the table’s columns:<\/p>\n
\"Image<\/a><\/p>\n
A view is also created:<\/p>\n
\"Image<\/a><\/p>\n
Now, let’s insert some data into this table:<\/p>\n
\n
INSERT INTO [dbo].[ArcadeRoomAccess]\r\nVALUES(1,'IN',DATEADD(HOUR, -5,getdate())),\r\n(1,'OUT',DATEADD(HOUR, -3,getdate())),\r\n(2,'IN',DATEADD(HOUR, -15,getdate())),\r\n(2,'OUT',DATEADD(HOUR, -10,getdate()));<\/pre>\n<\/div>\n
And we can see the ledger information with the views and generated columns with the following SQL query:<\/p>\n
\n
SELECT\r\nt.[commit_time] AS [CommitTime],\r\nt.[principal_name] AS [UserName],\r\nh.[AccessID],\r\nh.[PlayerID],\r\nh.[AccessEvent],\r\nh.[Timestamp],\r\nh.[ledger_operation_type_desc] AS Operation\r\nFROM [dbo].[ArcadeRoomAccess_Ledger] h\r\nJOIN sys.database_ledger_transactions t\r\nON t.transaction_id = h.ledger_transaction_id\r\nORDER BY t.commit_time DESC;<\/pre>\n
<\/div>\n<\/div>\n
Update the Un-Updatable<\/h3>\n
Trying an UPDATE or DELETE results in the following:<\/p>\n
\n
UPDATE [dbo].[ArcadeRoomAccess]\r\nSET Timestamp = GETDATE()\r\nWHERE AccessID = 4;<\/pre>\n<\/div>\n
and we see the following error:<\/div>\n
\n
\n
\n
\n
Msg 37359, Level 16, State 1, Line 1\r\n<\/span>Updates are not allowed for the append only Ledger table 'dbo.ArcadeRoomAccess'.<\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
Database Ledger<\/h2>\n
Taking a step back, how does the database use blockchain with Ledger? To start, any rows modified by a transaction (insert, update, or delete) in a table that is ledger enabled are cryptographically SHA-256 hashed using a Merkle tree data structure. This event also creates a root hash representing all rows in the database transaction. At a set interval, transactions are also SHA-256 hashed together through a Merkle tree data structure resulting in a root hash that forms a block. This sets up a process for the block to be SHA-256 hashed through the root hash of the block, along with the root hash of the previous block thus forming a blockchain.<\/p>\n
Putting these two processes together, we have the following flow in the database when a transaction occurs:<\/p>\n