Tables with ledger in sql server 2022

Ledger  in Sql Server 2022

In SQL Server 2022, you can create a ledger using the built-in functionality of the system-versioned temporal tables. A ledger is a record of all transactions that have affected a particular account or set of accounts, and it can be used to keep track of financial data, inventory data, or any other data that involves transactions.

Append-only ledger

Are ideal for application patterns that are insert-only, such as security information and event management (SIEM) applications. Append-only ledger tables block updates and deletions at the API level. This blocking provides more tampering protection from privileged users such as system administrators and DBAs.

To create a table with append-only control use following is the   query

CREATE SCHEMA [AccessControl]; GO CREATE TABLE [AccessControl].[KeyCardEvents] ( [EmployeeID] INT NOT NULL, [AccessOperationDescription] NVARCHAR (1024) NOT NULL, [Timestamp] Datetime2 NOT NULL ) WITH (LEDGER = ON (APPEND_ONLY = ON));

Once done with creating you can insert data into it, right now data is a dummy

INSERT INTO [AccessControl].[KeyCardEvents] 

VALUES ('43869', 'Building42', '2020-05-02T19:58:47.1234567');

Once data is inserted you can query the  table with the following query

There are 2 system columns to which show the transaction  id and start sequence number

SELECT * ,[ledger_start_transaction_id] ,[ledger_start_sequence_number] 

FROM [AccessControl].[KeyCardEvents];

If you want to check who created this table and when it got committed following is the question

SELECT t.[commit_time] AS [CommitTime] , t.[principal_name] AS [UserName] , l.[EmployeeID] , l.[AccessOperationDescription] , l.[Timestamp] , l.[ledger_operation_type_desc] AS Operation FROM [AccessControl].[KeyCardEvents_Ledger] l JOIN sys.database_ledger_transactions t ON t.transaction_id = l.ledger_transaction_id 

ORDER BY t.commit_time DESC;

 When we try to update following error is coming, same error is coming on deleting too 

Updatable ledger

are ideal for application patterns that expect to issue updates and deletions to tables in your database, such as system of record (SOR) applications. Existing data patterns for your application don't need to change to enable ledger functionality.

Create an updateable ledger table as follows.

CREATE SCHEMA [Account]; GO CREATE TABLE [Account].[Balance] ( [CustomerID] INT NOT NULL PRIMARY KEY CLUSTERED, [LastName] VARCHAR (50) NOT NULL, [FirstName] VARCHAR (50) NOT NULL, [Balance] DECIMAL (10,2) NOT NULL ) WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Account].[BalanceHistory]), LEDGER = ON 

);

It will create two tables  Balance and BalanceHistoryTrail

BalanceHistoryTrail is to track the history or change logs of  balancetable

Lets insert data in our balanacetable with following

INSERT INTO [Account].[Balance] 

VALUES (1, 'Jones', 'Nick', 50);

once done you can check with the following query  history table name and ledger names in DB

SELECT

ts.[name] + '.' + t.[name] AS [ledger_table_name] , hs.[name] + '.' + h.[name] AS [history_table_name] , vs.[name] + '.' + v.[name] AS [ledger_view_name] FROM sys.tables AS t JOIN sys.tables AS h ON (h.[object_id] = t.[history_table_id]) JOIN sys.views v ON (v.[object_id] = t.[ledger_view_id]) JOIN sys.schemas ts ON (ts.[schema_id] = t.[schema_id]) JOIN sys.schemas hs ON (hs.[schema_id] = h.[schema_id]) JOIN sys.schemas vs ON (vs.[schema_id] = v.[schema_id]) 

WHERE t.[name] = 'Balance';

Now try update the CashAmt amount to 100

UPDATE [Account].[Balance] SET [Balance] = 100 

WHERE [CustomerID] = 1;

The following query will show you  how the ledger maintains a history of all transaction  

SELECT t.[commit_time] AS [CommitTime] , t.[principal_name] AS [UserName] , l.[CustomerID] , l.[LastName] , l.[FirstName] , l.[Balance] , l.[ledger_operation_type_desc] AS Operation FROM [Account].[Balance_Ledger] l JOIN sys.database_ledger_transactions t ON t.transaction_id = l.ledger_transaction_id 

ORDER BY t.commit_time DESC;

 

Disk Space comparison

Normal db with the same number of tables and records vs DB with ledger with a same number of tables and records.

Normal DB

Ledger DB

Conclusion:

Auditing: Append and update ledgers allow you to track changes to a table, providing an audit trail that can be used to identify who made changes, when they were made, and what changes were made. This can be useful for compliance purposes or for troubleshooting issues in your database.

Rollback: If you need to undo changes made to a table, you can use the data in the append or update ledlger to roll back to a previous version of the table. This can be useful in cases where data is accidentally deleted or modified incorrectly.

Performance: Append and update ledgers can potentially improve performance in some cases, by allowing you to avoid using triggers or other methods to track changes to a table. This can reduce overhead and improve query performance.

Granular control: With append and update ledgers, you can track changes to specific columns in a table, rather than just tracking changes to the entire table. This can give you more granular control over auditing and rollback processes.