SE の雑記

SQL Server 2022 CTP 2.0 時点の情報となり、今後の Preview / 一般提供開始時には変更されている可能性もあります。

SQL Server 2022 では、Azure Synapse Link for SQL という機能が追加されました。

Synapse Link には、すでに Cosmos DB / Dataverse が提供されていましたが、今回、SQL Server ファミリー向けに機能が提供され、SQL Server 2022 / Azure SQL Database から、Synapse Analytics の専用 SQL プールに対しての Synapse Link が使用できるようになりました。

以前までは、

• Azure portal を使用して、変更追跡情報を使用して Azure SQL Database から Azure Blob Storage にデータを増分読み込みする
• 変更データ キャプチャ (CDC) を使用して Azure SQL Managed Instance から Azure Storage へのデータの増分読み込みを行う

で記載されているような、方法を使用してテーブルのデータ変更を取得する必要がありました。

Synapse Link for を活用することで、SQL Server のテーブルから専用 SQL プールのテーブルに対してのデータ同期を容易に設定することができるようになります。

Synapse Link for SQLServer 2022 と SQL Database の違い

前述のとり、Synapse Link for SQL は SQL Server 2022 と SQLDatabase の 2 種類が提供されています。

SQL Server 2022

• Azure Synapse Link for SQL Server 2022 (プレビュー)
• Azure Synapse Link for SQL Server 2022 の概要 (プレビュー)

SQL Database

• Azure SQL Database 用 Azure Synapse Link (プレビュー)
• Azure Synapse Link for Azure SQL Database (プレビュー) の概要

どちらも各環境のテーブルを Synapse Analytics の専用 SQL プールに連携する機能となりますが、構成に違いがあります。構成の違いについては Build 2022 の SQL Server 2022 と Azure SQL の新しいイノベーションにより、アプリケーションを最新化する で説明されています。

Synapse Link でデータを同期するためには 3 つのコンポーネントが必要になります。

1. Synapse Analytics と SQL Server の連携 (SHIR)
2. 連携用データ保存先の Landing Zone (ADLS Gen2)
3. Landing Zone から専用 SQL プールへのデータ連携 (Ingestion Service)

Synapse Link for SQL Database では「1.」「2.」は自動的に準備されるため、利用者側で明示的に用意する必要はありません。SQL Server 2022 の場合は、利用者が個別で用意する必要があります。

「1.」については、Synapse Analytics のセルフホスト統合ランタイム (SHIR) がインストールされている環境が必要となり、「2.」については、Synapse Link で使用する ADLS Gen2 を個別に準備する必要があります。

「3.」についてはどちらのパターンでも、自動的に隠蔽されて展開されるため、Ingestion Service については、明示的に何かのリソースを準備する必要はありません。

Synapse Link for SQL Database であれば、事前に連携用のリソースを準備することなく、設定を行うだけでデータの開始することができます。Synapse Link for SQL Server 2022 については、SHIR と ADLS Gen2 を事前に準備する必要があります。

基本的なデータ同期方法についてはどちらも同じため、SQL Server 2022 で設定をしたほうが、取得できる情報が多く、動作を詳細に確認することができます。

Synapse Link for SQL で同期できるテーブルの制限

Synapse Link for SQL で同期できるテーブルについては制約があり、SQL Server / SQL Database ともに、次のドキュメントで記載されています。

• SQL 用 Azure Synapse Link に関する既知の制限事項と問題

同期するテーブルについては「主キーが必要」という制約があり、主キーのないテーブルは同期対象とすることはできません。また、Synapse Link を設定したテーブルは、DROP / TRUNCATE / 列の追加 / 削除の操作ができないという制限が発生します。

SQL Database については、100 DTU 以上が必要になるのですが、SQL Server については、サイズの制限はありません。SQL Database についてはネットワーク関係の設定についての注意がいくつかありますので、使用する際には制限を確認しておくとよいかと思います。

Azure Synapse Link for SQL の FAQ にも制限に関する内容が記載されていますので、上述のドキュメント以外に FAQ も確認しておくとよいかと。

現状、

リンク接続からデータ レプリケーションを一時停止し、一時停止した場所から再開することはできますか?

いいえ、できません。 リンク接続の一時停止と再開は近日公開予定です。

という制限があり、リンク接続の一時停止を行うことができないということも FAQ に記載されています。(専用 SQL プールの一時停止についてはサポートされているようです)

Synapse Link for SQL の設定方法

Synapse Link for SQL の設定方法については、次のドキュメントで解説されています。

• Azure Synapse Link for SQL Server 2022 の概要 (プレビュー)
• Azure Synapse Link for Azure SQL Database (プレビュー) の概要

設定はこれらのドキュメントを参考にして進めれば問題ありません。

SQL Server 2022 であれば、

• 2022/5/24 以降に作成された Synapse Workspace を準備
• Synapse Workspace のセルフホスト統合ランタイムがインストールされた環境を準備
• Landig Zone 用の ADLS Gen2 を準備
• 同期対象のデータベースにマスターキーを作成

を準備していれば、同期の設定ができます。

設定ができていれば、次のように SQL Server 上のテーブルを専用 SQL プールのどのテーブルに同期するかを設定することができます。

Synapse Likn for SQL を開始して、同期を行うと SQL Server に対して「changefeed」というスキーマが作成され、設定の情報が格納されます。

テーブルに対しても複製されているという設定が行われていますね。

SELECT name FROM sys.tables WHERE is_replicated = 1

Synapse Link for SQL のコスト

Synapse Link for SQL の設定時には、同期に使用されるリンクサービスのコア数を指定する必要があり、コア数単位でコストが発生します。

Azure Synapse Analytics の価格 でコストも公開されています。

Synapse Link は一時停止してコストの発生を止めることができますが、現時点では一時停止からの再開は、サポートされておらず、一時停止した場合は、同期を最初から実施しなおす必要があります。

現時点では、同期を継続する場合には、常時起動をしておくサービスとなりますので、同期している間はコストは常に発生すると考えておいたほうが良いです。(検証用途であれば、停止しておくと、同期の再開はできませんが、設定は残しておきながらコストを抑えることができます)

Synapse Link for SQL の仕組み

Synapse Link for SQL の仕組みについては、次のドキュメントが参考になります。

• Azure Synapse Link for SQL 変更フィード
• Azure Synapse Link for SQL Server と Azure SQL Database の管理

Synapse Link for SQL Database では、バックグラウンドタスク / Landing Zone の情報を確認することができませんが、SQL Server 2022 であれば確認することができるので、今回は、SQL Server 2022 で情報を確認しています。

バックグラウンドタスクの常駐

Synapse Link for SQL を設定すると、SQL Server で Synapse Link 用のバックグラウンドタスクが常駐します。

SELECT 
	er.session_id, er.status, er.command , es.program_name,
	es.client_interface_name, er.last_wait_type
FROM 
	sys.dm_exec_requests AS er
	INNER JOIN sys.dm_exec_sessions AS es
		on er.session_id = es.session_id
WHERE command LIKE '%SYNAPSE%'

このバックグラウンドタスクは、「変更フィード」の機能を備えており、トランザクションログから、変更対象のデータを取得し、Landing Zone に書き込みが行われます。

Landing Zone に取得されるデータ

Synapse Link for SQL のデータ連携は、プロセスから直接データを同期するのではなく、Landing Zone に同期用のデータを生成して、そのデータをロードすることで実現されています。

初期の同期については、Landing Zone に Parquet ファイルとして初期スナップショット (FullCopyData) が取得され、初期データの同期が行われます。

以降の増分データについては、CSV で Landing Zone に格納され、このデータが専用 SQL プールに取り込まれることで、データの同期が行われます。

SQL Database に対して設定した場合、Landing Zone は Synapse Analytics で管理されている ADLS Gen2 が使用されるため、実際のファイルは確認することができませんが、SQL Server 2022 に対して設定する場合は、利用者が事前に準備した ADLS Gen2 を使用するため、上記のようにどのようなデータが生成されているか確認することができます。

SQL Server 2022 では、ADLS Gen2 は利用者が用意するため、Landing Zone にどのようなデータが入っているかを容易に確認することができ、ファイルの内容も確認することができますが、このファイルは Synapse Link for SQL で使用されることを前提としたものとなっており、他の用途で使用されることはサポートされていないため、ファイルにアクセスすることができますが、ファイルの二次利用は行うことはできません。

専用 SQL プールのデータ取り込み方法

専用 SQL プールがどのようにしてデータを取り込んでいるかについては、クエリアクティビティから確認することができます。

いくつかを抜き出してみると、次のようなクエリが実行されています。

専用 SQL プールへのデータ取り込みは Landing Zone のデータを COPY INTO で一時テーブルに取り込み、DELETE / INSERT により、データの反映を実施しているようです。

UPDATE についても DELETE -> INSERT で実施されているようでした。

Synapse Link for SQL の情報取得

Synapse Link for SQL の、変更フィードの動作については、確認することができる DMV が公開されており、エラーや、変更フィードの動作状況については、追加された DMV から確認することができます。

• sys.dm_change_feed_log_scan_sessions (Transact-SQL)
• sys.dm_change_feed_errors (Transact-SQL)

sys.dm_change_feed_log_scan_sessions の情報から確認することができるのですが、変更フィードのログスキャンは 5 秒間隔で実行されているようです。

他にも Synapse Link for SQL 向けの拡張イベントも提供されており、詳細な動作については、拡張イベントから確認することができます。

select * from sys.dm_xe_objects where name like '%synapse%' and object_type = 'event'

データ同期で気を付けること

Synapse Link for SQL は、SQL Server または SQL Database のトランザクションストアのデータを、容易に分析ストアとしての専用 SQL プールに同期することができる機能となります。

トランザクションストアの INSERT / UPDATE / DELETE のすべての操作が同期されます。

トランザクションストアと分析ストアを分けたデータ構成としては、Cosmos DB があり、Azure Cosmos DB 分析ストアとは で解説が行われています。

Cosmos DB もトランザクションストアのデータを自動的に分析ストアに同期し、トランザクションと分析で使用するデータストアを分けることができます。

Cosmos DB と Synapse Link のトランザクションストア / 分析ストアのデータ管理の大きな違いとしては、TTL によるデータ管理です。

Cosmos DB は TTTL (トランザクション TTL) と、ATTL (分析 TTL) があり、トランザクションデータストアで削除されたデータについて、ATTL の設定により分析ストア内でのデータ保持期間を個別に制御することができるようになっていました。これにより、トランザクションデータストアで削除したデータは、分析ストアからは削除せずに、分析データにはトランザクションデータストアのデータを蓄積するということが可能となっています。

Synapse Link for SQL については、分析ストアとなる専用 SQL プールでのデータ保持間は設定することができず、トランザクションストアで削除されたデータは分析ストアでも削除され、分析ストアのテーブルはトランザクションストアのコピーとなり同一のデータが保持されるものとなります。

同期されているテーブル間で考えると、同期先の専用 SQL プールのテーブルはトランザクションストアのコピーとなり、同一データが保持された状態となりますので、分析ストアとなる専用 SQL プール (または、Synapse 内の他のデータストア) 内に、トランザクションストアの過去に存在していたデータも保持しておきたいという場合には、独自の仕組みを考える必要が出てくるのではないでしょうか。

Cosmos DB の ATTL のようなもので、分析データを蓄積することができると楽でよいなと思い、フィードバック はしてみました。

今まで SQL Server の HTAP (Hybrid Transaction and Analytical Processing) というと、「行ストアインデックス」(または、インメモリ OLTP) と「列ストアインデックス」を一つのテーブルに設定し、同一テーブル上で二つの形式でデータを格納することで実現していました。

今後の HTAP の選択肢としては、トランザクションストアと分析ストアを異なるデータストアとして分割し、SQL Server / SQL Database と Synapse Analytics を組み合わせることで実現することが可能となります。

Synapse Link for SQL は容易な設定でトランザクションストアと分析ストアを分割したテーブルを作成することができますので、今後のデータストアの設計について、このような形態についても考慮してみたいですね。