Microsoft AzureでAzure SQL DatabaseのLedger(台帳)を使ってみた話

12/24 18:30修正：「このデータベースの将来のテーブルすべてで有効にする」を有効にしなければ、通所のテーブルと Ledger テーブルを混在させられます。誤った記載がございましたことを、お詫びして訂正いたします。

当ブログでは、これまでOracle Databaseを主に扱ってきましたが、今後はSQL Server（Azure SQL Database含む）についても扱っていきたいと思います。

今回はAzure上でSQL ServerをPaaSとして利用できるAzure SQL Databaseに追加されたLedger機能（プレビュー版）を使ってみたいと思います。

以前の記事で、ブロックチェーンの技術、思想をRDB（リレーショナルデータベース）に取り込んだOracle Blockchain TableというOracle Databaseの機能を紹介しました。Oracle Blockchain TableはInsert Onlyの「書き換え・削除ができない中央集権データベース」であると紹介しましが、今回のAzure SQL Database Ledgerはどんな機能なのか見ていきたいと思います。

＊本記事ではわかりやすいように、文脈により台帳とLedgerを使い分けていますが、どちらも同じ意味です。

Azure SQL Databaseとは

本題のLedger機能を触るまえに、Azure上のSQL Databaseとは何かについてみていきます。

Microsoftのドキュメントには以下のようにあります。

Azure SQL Database は、アップグレード、修正プログラムの適用、バックアップ、監視などのほとんどのデータベース管理機能をユーザーの介入なしで処理する、フル マネージドの PaaS (サービスとしてのプラットフォーム) データベース エンジンです。

https://docs.microsoft.com/ja-jp/azure/azure-sql/database/sql-database-paas-overview

Azureで利用できるSQL ServerのPaaSサービスということですね。Oracle Cloud InfrastructureのAutonomous Databaseと同じようなPaaSになります。両者ともOSにアクセスは出来ませんし、バックアップ、監視、パッチ適用の自動化など似ている点が多いです。

Azure SQL Databaeの詳細についてはここでは省きます。

Ledger(台帳)機能とは

それでは本題の「Ledger機能」についてみていきます。

まず、Ledger機能とは何かということですが、ドキュメントには「Azure SQL Database 台帳機能はデータベースに改ざん防止機能を提供します。 監査者や他のビジネス関係者などの他の関係者に対して、データが改ざんされていないことを暗号学的に証明できます。」とあり、RDBに耐改ざん性を高めた機能であることがわかります。

Oracle Blockchain Tableと同じようなBlockchainの技術、思想を取り込んだ機能のようです。LedgerはAzure SQL Databaseの1機能であり、耐改ざん性を高めたテーブルとなるため「中央集権型」となります。いわゆるBlockchainのように分散型の台帳ではありません。

こちらのドキュメントに仕組みの説明があります。仕組みとしては、各トランザクションがハッシュ化され、ハッシュ関数の入力にトランザクションのデータと、以前のトランザクションのハッシュ値が使用されることで、耐改ざん性を実現しています。

さらに、生成されたハッシュをデータベースダイジェストとして外部ストレージに出力できます。ダイジェスト内のハッシュの値をデータベース内の計算されたハッシュと比較することにより、データベースの整合性も検証することができるようです。

また、Microsoftのブログには以下のようにあります。

Azure SQL Database Ledgerは、データベース向けに 2 種類の台帳対応テーブルを提供しています。1 つは更新可能な台帳テーブル、もう 1 つは追加専用の台帳テーブルです。

https://www.microsoft.com/ja-jp/events/azurebase/blog/announcing-azure-sql-database-ledger/

どうやらAzure SQL Database Ledgerには2つ機能があるようですね。

追加専用の台帳テーブルは、Oracle Blockchain Tableと同様の「insert only」でレコードの変更、削除ができないテーブルだろうと予想がつきます。しかし、もう一つのテーブル「更新可能な台帳テーブル」というものは何でしょう。更新ができるLedgerとは、更新履歴などを保持できるAmazon QLDBのような機能を持っているのでしょうか？

それぞれのテーブルについてみていきます。

追加専用の台帳テーブル(Append-only ledger tables)

追加専用の台帳テーブル(Append-only ledger tables)では、「テーブルに対する INSERT 操作のみを許可し、データベース管理者などの特権ユーザーが従来のデータ操作言語操作でデータを変更することができないように保証されています。」とドキュメントには書いてあります。

管理者などの特権ユーザーでもレコードを変更できない、Insert Onlyのテーブルとなります。ユースケースとしては、監査ログやブロックチェーン データの複製先などが想定されるようです。

更新可能な台帳テーブル(Updatable ledger tables)

更新可能な台帳テーブル(Updatable ledger tables)とは、「更新可能な台帳テーブルではデータベース内で変更または削除された行の過去の値を、関連付けられた履歴テーブルに記録します。」とあります。

システムによってバージョン管理されており、削除・更新などの変更履歴を保持するような機能を持つテーブルのようです。

その変更履歴は、更新や削除が行われると、以前のバージョンのすべての行が、履歴テーブルと呼ばれるセカンダリ テーブルに保存されるようです。

なんとなくLedgerの概要はわかったので、実際にこのLedger機能の2つのテーブルを作ってみたいと思います。

Ledger(台帳)を使う下準備・Ledger機能が構成されたSQL Databaseの作成

＊以下日本語ドキュメントに沿って行うためLedgerより台帳という表現が増えます、どちらも同じ意味です。

こちらのドキュメントに作成手順があります。AzureポータルからSQL Databaseを作成する際に、プレビュー版のLedger機能を有効にします。

Azure SQL Databaseは以下の設定で作成しました

• サーバーを西ヨーロッパに作成（現在対応リージョンは西ヨーロッパ、ブラジル南部、および米国中西部）
• サービスレベルはBasicを選択（最も安い構成）
• ストレージ100MB（最小構成）
• パブリックエンドポイントを有効化、クライアントIPをファイアウォールに追加（後にPCからアクセスするため）
• データソースはサンプルを選択

Ledgerはこちらドキュメントの13番～のように「セキュリティ」タブの[台帳]セクションで構成します。*対応しているリージョン以外のサーバーを選択すると台帳セクションが表示されません

「台帳の構成」をクリックすると。構成画面に移り、以下の説明が表示されます。

台帳機能を有効にすると、データベース内のすべてのテーブルが更新可能な台帳テーブルになります。このオプションは、データベースを作成した後では変更できません。現在このオプションを選択していない場合、T-SQL を使用して新しいテーブルを作成する場合に、更新またはアペンドのみが可能な台帳テーブルを作成することができます。テーブルの台帳機能を有効にした後、このオプションを無効にすることはできません。

Azure ポータルの「台帳を構成」より

なるほど、Ledger（台帳）の「このデータベースの将来のテーブルすべてで有効にする」を有効にすると、DB内のすべてのテーブルが「更新可能な台帳テーブル」として作成されるようになるみたいですね。

「このデータベースの将来のテーブルすべてで有効にする」というチェックボックスにチェックを入れて、台帳機能を有効化します。

12/24 修正箇所
※上記チェックを入れない場合は、Ledger テーブルと通常のテーブルを同じデータベース上に作成することが可能です。

つまり、通常のテーブルとLedgerのテーブルを一つのデータベース内に共存させるのは今のところは出来ないようです。更新可能な台帳テーブルは、UPDATE、DELETEなど基本的にDML操作はでき、通常のテーブルのように扱えるので、ひとまず問題なさそうです（オーバーヘッドは気になりますが）。

そして、もう一つダイジェストの設定項目があります。「自動ダイジェスト ストレージを有効にする」というチェックボックスにチェックを入れると、ダイジェストの保存が有効になり、ダイジェストの生成先を指定する項目が出現します。

ダイジェストの生成先は「Azure Storage」もしくは「Azure Confidential Ledger(プレビュー)」に2つがあります。

今回はせっかくなので「Azure Confidential Ledger(プレビュー)」を選択したいと思います。Confidential Ledgerの説明は以下の通りとなります。

専用のストレージは、許可されたブロックチェーン管理サービス内の完全に改ざんされない台帳を通じて、ブロブやファイルなどの非構造化データのストレージを提供します。ユーザーはいつでも改ざんを検証することができます (REST APIを利用可能)。1 つの機密台帳には、複数の SQL データベース (サブ台帳) のダイジェストを格納することができます。

Azure ポータルの「Confidential Ledger」より

Ledgerに特化した非構造化データのストレージということですね。

Confidential Ledgerを「ACLリージョンの選択」という項目が出てきました。選択肢は今のところ「eastus」しか選択できないようです。

また、Confidential Ledgerのストレージ価格レベルですが、「Standard レベルプレビュー期間中は無料」の表示があり、現在特に費用は掛からないようです。

以上でLedgerの設定は完了なので、適用ボタンを押し、SQL Databaseを作成します。作成ボタンを押してからしばらく待ち、デプロイが済んだら準備は完了です。

*この時、通常のSQL DatabaseのデプロイよりもLedgerを構成しているほうが（少々）完了まで時間がかかった気がします。

作成後にLedgerの設定画面を見てみると、Ledger機能のON、OFFは変更できないようになっていますが、ダイジェスト機能や出力先は変更できるようです。

Ledger（台帳）を使ってみる

Ledger機能が構成されたSQL Databaseが作成されたので、さっそくLedger機能を使ってみたいと思います。今回はクライアントPCにインストールした

SQL Server Management Studio (SSMS) を使ってアクセスしていきます（Oracleで言うSQL Deverloperです）。

更新可能な台帳テーブルをつかってみる

まずは、デフォルトで作成するテーブルはすべて「更新可能な台帳テーブル」となるようなので、こっちを先に作成してみます。

その前に、サンプルデータをONにしてSQL Databaseを作成したので、サンプルテーブルは「更新可能な台帳テーブル」になっているのだろうか？と確認したかったのですが、、、なぜかサンプルテーブルが作成されていませんでした（なぜ）。改めてサンプルデータをオンにして作成しましたが、やはりだめでした。

気を取り直して「更新可能な台帳テーブル」を作成していきたいと思います。こちらのドキュメントに作成方法が書いてあります。

ドキュメントと同じスキーマ名とテーブルの定義を使って検証してみます。まずはAccountというスキーマを作成し、Balanceというテーブルを作成します。

Ledgerを有効にしてあるデータベースの場合、すべて「更新可能な台帳テーブル」になるとのことで、あえてドキュメントにあるSYSTEM_VERSIONING = ON, LEDGER = ONの二つを省略してみます（指定しないとデフォルトでONになる模様）。

--schema作成
CREATE SCHEMA [Account]
Commands completed successfully.

--table作成
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以下省略
WITH 
(
 SYSTEM_VERSIONING = ON,
 LEDGER = ON
);
*/
Commands completed successfully.

問題なく作成されました。今度はあえてWith句でLedgerをOFF、SYSTEM_VERSIONINGをOFFにしてみたいと思います。

--LedgerをOFFに指定
CREATE TABLE [Account].[test]
(
    [CustomerID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [LastName] VARCHAR (50) NOT NULL,
    [FirstName] VARCHAR (50) NOT NULL,
    [Balance] DECIMAL (10,2) NOT NULL
)
WITH 
(
 Ledger = OFF
);

Msg 37420, Level 16, State 1, Line 93
LEDGER = OFF cannot be specified for tables in databases that were created with LEDGER = ON.

--SYSTEM_VERSIONINGをOFFに指定
CREATE TABLE [Account].[test]
(
    [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 = OFF
);

Msg 37349, Level 16, State 1, Line 92
LEDGER = ON cannot be specified with SYSTEM_VERSIONING = OFF and APPEND_ONLY = OFF.

Ledgerが有効のデータベースではLedger/SYSTEM_VERSIONINGどちらもOFFにすることは出来ないようです。こちらのドキュメントの制約にも書いてありました。

更新可能な台帳テーブルを作成すると、対応する履歴テーブルと台帳ビューも作成されるようです。確認してみます。

--sysスキーマのカタログビューからJoinして表示
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]);

それぞれ、「Balance」という台帳テーブル、「MSSQL_LedgerHistoryFor_18455581613」という履歴テーブル、「Balance_Ledger」という台帳ビューが作成されたことが確認できます。

では、次にドキュメント通り、Balanceテーブルにデータを入れていきます(SSMSの自動コミットがONになっています)。入ったデータも確認します。

INSERT INTO [Account].[Balance]
 VALUES (1, 'Jones', 'Nick', 50)
;
(1 row affected)

INSERT INTO [Account].[Balance] VALUES (2, 'Smith', 'John', 500),
(3, 'Smith', 'Joe', 30),
 (4, 'Michaels', 'Mary', 200);
(3 rows affected)

SELECT * FROM [Account].[Balance];

ただSELECT * でデータを取得すると、Ledgerに関するカラムは表示されず、ユーザー定義のカラムのみ表示されます。「更新可能な台帳テーブル」では以下の4つのGenerated Always列というカラムが追加されるようです（Oracle Blockchain Tableの隠しカラムのようなものですかね）。

• ledger_start_transaction_id ：行バージョンが作成されたトランザクションのID
• ledger_end_transaction_id：行バージョンが削除されたトランザクションのID
• ledger_start_sequence_number：行バージョンが作成されたトランザクション内でのシーケンス番号
• ledger_end_sequence_number：行バージョンが削除されたトランザクション内でのシーケンス番号

Generated Always列も表示させてみます。

SELECT * 
      ,[ledger_start_transaction_id]
      ,[ledger_end_transaction_id]
      ,[ledger_start_sequence_number]
      ,[ledger_end_sequence_number]
FROM [Account].[Balance] 

ledger_start_transaction_id列とledger_start_sequence_number列に値が入っていますね。

CustomerID =1とCustomerID = 2,3,4は別トランザクションで実行したためledger_start_transaction_idが異なっています。また、Customer ID 2,3,4は同一トランザクションだったので、sequence_numberにて順番がついています。

次に履歴テーブルを見てみます。UPDATE文でレコードを更新しておきます。

UPDATE [Account].[Balance] SET [Balance] = 100
WHERE [CustomerID] = 1
(1 row affected)

次に先ほど確認した、履歴テーブルの名前を使って、SELECTします。

SELECT * 
      ,[ledger_start_transaction_id]
      ,[ledger_end_transaction_id]
      ,[ledger_start_sequence_number]
      ,[ledger_end_sequence_number]
FROM [Account].[Balance] 
GO

SELECT * FROM MSSQL_LedgerHistoryFor_18455581613
GO 

SELECT * FROM Account.Balance_Ledger
ORDER BY ledger_transaction_id
GO

上からBalanceテーブル、履歴テーブル、台帳ビューとなります。

履歴テーブルにはUPDATE文で変更された元の行が表示されています。履歴テーブルのレコードのledger_end_transaction_id列に値が入っており、Balanceテーブル１行目CustomerID=1の行のledger_start_transaction_id列の値と同じになっています。

つまり、新しいトランザクションで行の削除と行の挿入（更新）が行われたことを記録しています。このUPDATE文発行からの一連の内部の動きが台帳ビューに記録されています。UPDATE文発行後、台帳ビューの5行目で元の行が削除され、6行目で新しいレコードが挿入されたことがわかります。

台帳ビューには次のAlways Generatedカラムがあります。

• ledger_transaction_id ：行バージョンを作成または削除したトランザクションの ID
• ledger_sequence_number：テーブルに対するトランザクション内の行レベルの操作のシーケンス番号
• ledger_operation_type：テーブルに対する捜査を表す数値 **値が0=INSERT、値が1=DELETE？
• ledger_operation_type_desc：操作を表す INSERTまたはDELETEが含まれる

**ドキュメントには ledger_operation_typeの説明で「台帳テーブルの行を更新すると、2 つの新しい行が台帳ビューに生成されます。 一方の行のこの列に 1 (DELETE) が含まれ、もう一方の行のこの列に 1 (INSERT) が含まれます。」とあります。英語の原文でも「One row contains 1 (DELETE) and the other row contains 1 (INSERT) in this column.」とあるので、誤植かな？と思いますが、実際のビューには更新の際、DELETEの行に2、INSERTに1となっていました。ドキュメントのスクリーンショットも今回の結果と同様DELETE=2, INSERT=1となっていました。

次にDELETE文も試してみます。

DELETE FROM [Account].[Balance] WHERE CustomerID=1;
(1 row affected)

SELECT * 
      ,[ledger_start_transaction_id]
      ,[ledger_end_transaction_id]
      ,[ledger_start_sequence_number]
      ,[ledger_end_sequence_number]
FROM [Account].[Balance] 
GO

SELECT * FROM Account.MSSQL_LedgerHistoryFor_1845581613
GO 

SELECT * FROM Account.Balance_Ledger
ORDER BY ledger_transaction_id
GO

すべてのテーブルとビューのレコードが更新、追加されました。

最後にテーブル削除（DROP）も試してみます。

DROP Table [Account].[Balance];
Commands completed successfully.

SELECT * 
      ,[ledger_start_transaction_id]
      ,[ledger_end_transaction_id]
      ,[ledger_start_sequence_number]
      ,[ledger_end_sequence_number]
FROM [Account].[Balance] 
Msg 208, Level 16, State 1, Line 66
Invalid object name 'Account.Balance'.

はい、こちらもしっかりDROP出来ました。台帳テーブル（Balanceテーブル）は削除しましたが、「MS_DroppedLedgerTable_Balance_00F00B2DC323452A90BD60AE3AD7F25E」という名前で削除されたテーブルとして残るようです。また、履歴テーブルも残るようです。

この残ったテーブルを削除してみます。

--履歴テーブルを削除
DROP TABLE Account.MSSQL_LedgerHistoryFor_1845581613;
Msg 37386, Level 16, State 1, Line 15
Cannot drop object 'Account.MSSQL_LedgerHistoryFor_1845581613' because it is a ledger history table or a ledger view.

--削除済み台帳テーブルをさらに削除
DROP TABLE Account.MS_DroppedLedgerTable_Balance_00F00B2DC323452A90BD60AE3AD7F25E;
Msg 37427, Level 16, State 1, Line 15
Cannot alter or drop object 'Account.MS_DroppedLedgerTable_Balance_00F00B2DC323452A90BD60AE3AD7F25E' because it is a ledger dropped object.

それぞれ固有のエラーが発生し、履歴テーブルも削除済み台帳テーブルも削除できませんでした。

追加専用の台帳テーブルをつかってみる

次に「追加専用の台帳テーブル」を作成していきたいと思います。こちらのドキュメントに作成方法も含め書いてあります。

さっそくテーブルを作成してみます。追加専用の台帳テーブルを作成するにはWITH句でAPPEND_ONLY = ONが必須となります。

--schemaを作成
CREATE SCHEMA [AccessControl] 

--tableを作成
CREATE TABLE [AccessControl].[KeyCardEvents]
    (
        [EmployeeID] INT NOT NULL,
        [AccessOperationDescription] NVARCHAR (MAX) NOT NULL,
        [Timestamp] Datetime2 NOT NULL
    )
    WITH (
       LEDGER = ON (
                    APPEND_ONLY = ON
                    )
      );
Commands completed successfully.

こちらも同様、ドキュメント通り、AccessControlというスキーマにKeyCardEventsというテーブルを作成します。

次にデータを追加し、SELECTしてみます。

INSERT INTO [AccessControl].[KeyCardEvents]
VALUES ('43869', 'Building42', '2020-05-02T19:58:47.1234567');
(1 row affected)

SELECT * FROM [AccessControl].[KeyCardEvents];

1行挿入されたのが確認できました。また、追加専用の台帳テーブルには以下のGenerated Always列があります。

• ledger_start_transaction_id：行バージョンを作成したトランザクションの ID
• ledger_start_sequence_number：行バージョンを作成したトランザクション内の操作のシーケンス番号

上記カラムも表示してみます。

SELECT *
     ,[ledger_start_transaction_id]
     ,[ledger_start_sequence_number]
FROM [AccessControl].[KeyCardEvents];

追加専用の台帳テーブルでは,レコードの削除、更新ができないので、Generated Always列もstartのみになっています。

次に、UPDATE文、DELETE文、TRUNCATE文を試してみます。

UPDATE [AccessControl].[KeyCardEvents] SET [EmployeeID] = 34184;

Msg 37359, Level 16, State 1, Line 24
Updates are not allowed for the append only Ledger table 'AccessControl.KeyCardEvents'.

DELETE from [AccessControl].[KeyCardEvents];
Msg 37359, Level 16, State 3, Line 26
Updates are not allowed for the append only Ledger table 'AccessControl.KeyCardEvents'.

TRUNCATE TABLE [AccessControl].[KeyCardEvents];
Msg 13545, Level 16, State 1, Line 18
Truncate failed on table 'ledger-db.AccessControl.KeyCardEvents' because it is not a supported operation on system-versioned tables.

UPDATEもDELETEも同じエラーとなりました。また、TRUNCATEもエラーが発生し、削除ができないようになっていることが確認できました。

次にALTER文でテーブルの定義を変更してみます。これも同様エラーが発せ…

--列を追加
ALTER TABLE [AccessControl].[KeyCardEvents] ADD new_column  VARCHAR (10) DEFAULT '';
Commands completed successfully.

SELECT *
     ,[ledger_start_transaction_id]
     ,[ledger_start_sequence_number]
FROM [AccessControl].[KeyCardEvents]

ALTER TABLE文が成功し、列の追加ができました。ほかにもやってみましょう。

--new_column列の型varchar(10)をvarchar(100)に変更
ALTER TABLE [AccessControl].[KeyCardEvents] ALTER COLUMN new_column Varchar (100);
Commands completed successfully.

--new_column列にアルファベット26byte挿入
INSERT INTO [AccessControl].[KeyCardEvents]
VALUES ('999999', 'Building42', '2020-05-02T19:58:47.1234567', 'abcdefghijklmnopqrstuvwxyz')
(1 row affected)

--new_column列の型varchar(100)をvarchar(27)に変更
ALTER TABLE [AccessControl].[KeyCardEvents] ALTER COLUMN new_column Varchar (27);
Commands completed successfully.

--new_column列にアルファベット26bytes + 3bytes 挿入
INSERT INTO [AccessControl].[KeyCardEvents]
VALUES ('0000000', 'Building42', '2020-05-02T19:58:47.1234567', 'abcdefghijklmnopqrstuvwxyz123')
Msg 2628, Level 16, State 1, Line 40
String or binary data would be truncated in table 'ledger-db.AccessControl.KeyCardEvents', column 'new_column'. Truncated value: 'abcdefghijklmnopqrstuvwxyz1'.
The statement has been terminated.

表定義の変更は可能のようです。

では、列を削除してみます。

--値が入っている列を削除
ALTER TABLE [AccessControl].[KeyCardEvents] DROP COLUMN Timestamp;
Msg 37388, Level 16, State 1, Line 77
Column 'Timestamp' in table 'ledger-db.AccessControl.KeyCardEvents' cannot be dropped because the table is a ledger table.

--値が入っていない列を追加
ALTER TABLE [AccessControl].[KeyCardEvents] ADD new_column  VARCHAR (10);
Commands completed successfully.

--値が入っていない列を削除
ALTER TABLE [AccessControl].[KeyCardEvents] DROP COLUMN new_column;
Msg 37388, Level 16, State 1, Line 89
Column 'new_column' in table 'ledger-db.AccessControl.KeyCardEvents' cannot be dropped because the table is a ledger table.

列の削除は、値が既に入っているかにかかわらずできないようです。最後にテーブルを削除してみます。

DROP TABLE [AccessControl].[KeyCardEvents];
Commands completed successfully.

SELECT *
     ,[ledger_start_transaction_id]
     ,[ledger_start_sequence_number]
FROM [AccessControl].[KeyCardEvents];

Msg 208, Level 16, State 1, Line 19
Invalid object name 'AccessControl.KeyCardEvents'.

おっ、テーブルの削除もできてしまうんですね。このあたりはOracle Blockchain Tableと異なる特徴ですね。

削除済みテーブルはどうでしょうか。

DROP TABLE AccessControl.MS_DroppedLedgerTable_KeyCardEvents_6E7B95C3924F4629B3ACB4BBC0AE23C2;
Msg 37427, Level 16, State 1, Line 15
Cannot alter or drop object 'AccessControl.MS_DroppedLedgerTable_KeyCardEvents_6E7B95C3924F4629B3ACB4BBC0AE23C2' because it is a ledger dropped object.

「更新可能な台帳テーブル」もそうでしたが、台帳テーブルはDROP操作を制限していないが、履歴テーブル（更新可能な台帳テーブルの場合）と削除済みのテーブルとして残るということですね。これならDROP可能というのもわかります。

最後にcommit/rollbackも試してみます。SSMSで自動コミットがONになっていたので、OFFにしてから実行します。

ひとまず再度同じ追加専用の台帳テーブルを作成し、1行挿入、コミットします。

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

INSERT INTO [AccessControl].[KeyCardEvents]
VALUES ('43869', 'Building42', '2020-05-02T19:58:47.1234567');

commit transaction;
(1 row affected)

SELECT *
     ,[ledger_start_transaction_id]
     ,[ledger_start_sequence_number]
FROM [AccessControl].[KeyCardEvents]

1行挿入されました。次に、2行目を挿入します。

INSERT INTO [AccessControl].[KeyCardEvents]
VALUES ('99999', 'Building99', '2020-05-02T19:58:47.1234567');

SELECT *
     ,[ledger_start_transaction_id]
     ,[ledger_start_sequence_number]
FROM [AccessControl].[KeyCardEvents]

2行目が挿入されました。この時点2行目のレコードは確定されていないので。rollbackを実行します。

rollback transaction;

SELECT *
     ,[ledger_start_transaction_id]
     ,[ledger_start_sequence_number]
FROM [AccessControl].[KeyCardEvents]

rollbackが実行され、2行目のレコードが取り消されました。

Azure SQL Database Ledgerにおけるハッシュとブロックチェーンについて

いろいろとAzure SQL Database Ledgerを使ってみましたが、ここまで、ブロックチェーンたる所以のハッシュチェーンやブロックに関することは特に出てきませんでした。このLedger(台帳)機能はOracle Blockchain Table同様にブロックチェーンの技術と思想をしっかりと取り込んでいます。

それがデータベース台帳(Database Ledger)にあります。データベース台帳はブロックチェーンとマークル ツリーデータ構造を論理的に使用しており、データベースの状態を把握するため、すべてのトランザクションのエントリを保存しています。もちろん、整合性の検証の際にも利用されます。

ここまで、台帳テーブルや履歴テーブル、台帳ビューなどデータの更新や削除、追加といった操作（Operation）のログや履歴を確認してきました。では、そのトランザクションやブロックのデータはどこに格納されているのでしょうか。それがこのデータベース台帳になります。データベース台帳によってトランザクションとブロックのデータは以下のシステムカタログビューに格納されます。

• sys.database_ledger_transactions：データベース台帳での各トランザクションの情報を含む行を管理します
• sys.database_ledger_blocks：台帳のすべてのブロックに対応する行を管理します（ここにマークル ツリールートやprevious blockのハッシュ値があります）

実際に見てみましょう。

SELECT * FROM sys.database_ledger_transactions
GO

SELECT * FROM sys.database_ledger_blocks
GO

ブロックの番号とマークルルート、前のブロックのハッシュなどが確認できました。

まとめ

Azure SQL Database Ledger（台帳）機能をまとめます。

• Azure SQL Databaseの1機能（テーブル）
• ブロックチェーンの技術を取り入れた改ざん性、検証性を高めたテーブル
• データの削除、更新が可能であり、変更履歴が保存される「更新可能な台帳テーブル」、INSER onlyの「追加専用の台帳テーブル」の二つの機能がある
• データベース台帳にブロックやハッシュの情報が保持される
• データベース ダイジェストを外部ストレージに出力できる

おわりに

今回はAzureのPaaSデータベースであるAzure SQL Databaseの新機能 Ledger (台帳)を使ってみました。

Oracle Blockchain Tableを使っていた私としては、同じ機能もあれば、それぞれ異なる特徴もあって面白いなと感じました。

今回は軽く触ってみた程度なので、ハッシュの検証やダイジェストについて深く触れませんでした。また記事にしたいと思います。ユースケースとしてはOracle Blockchain Tableの記事で書いたものが該当するかなと思っていますが。また別途記事にしてもいいなと思っています。

せっかくなのでOracle Blockchain TableとAzure SQL Database Ledgerの機能比較の記事も作りたいと思います。

最後までご覧いただきありがとうございました。

*本記事は公式の情報ではなく個人の投稿です。正式では無い情報を含む場合があります。