Share via

SQL Server のデータを Azure Search と同期する方法

  • Article

このポストは、11 月 10 日に投稿された How to Sync SQL Server Data with Azure Search の翻訳です。

SQL Server データベース内に存在するデータを Azure Search に拡張する方法について、ユーザーの皆様からご質問を数多くいただいています。このことから、フルテキストを利用したサービスをオンプレミスの SQL Server データベースからクラウドに移行することや、Azure Search の高度な検索機能を利用することをお望みのお客様が多数いらっしゃるのだと考えられます。

データの変更をほぼリアルタイムに検索エンジンに反映させることが必要なケースは珍しくありませんが、変更された行の追跡は適切に行わないと演算処理の負荷が高くなることがあるため、これは簡単なことではありません。この記事では、SQL Server に組み込まれている変更の追跡機能を使用して、データの変更を効率的に SQL Server から Azure Search に同期する方法について説明します。変更の追跡は SQL Server の内部機能で、ユーザー テーブルに対する変更 (Insert、Update、Delete) を追跡します。また、データベースのパフォーマンスへの影響を非常に小さく抑えながら、変更の追跡や検出を効率よく実行できます。

以降では、筆者が Codeplex (英語) で公開しているサンプルを使用しながら説明を進めます。

 

要件

今回のチュートリアルでは、下記のそれぞれが利用可能であることが前提条件となっています。

  • SQL Server 2008 またはそれ以降
    • 注 : 既定のデータベース構成および SQL Server Management Studio を使用している場合は、(ローカル データベース)\v11.0 としてサーバー インスタンスに接続します。
  • Azure Search サービス (詳細はこちら (英語) を参照)
  • Visual Studio 2012 またはそれ以降
  • サンプルのソース コード (こちら (英語) からダウンロード)

 

SQL Server を Azure Search のサンプル用に構成する

サンプル プロジェクトをダウンロードし、Visual Studio で開いているところから説明を始めます。このサンプルの Visual Studio プロジェクトに、お客様の Azure Search サービスへの接続情報を追加する必要があります。お客様の SQL Server データベースの接続情報は、変更が必要なときにも使用しますのでメモしておいてください。

app.config を開き、SearchServiceName および SearchServiceApiKey の値をそれぞれ Azure Search サービスおよび Azure Search サービスの API キーに合わせて変更します。この値は Azure 管理ポータルで確認できます。

 

変更の追跡を SQL Server データベースに追加する

このアプリケーションを起動すると、“AzureSearchSyncTest” という名前の新しいデータベースがあり、その “Products” という名前のテーブルにはデータが存在しています。このテーブルに対して変更の追跡を有効化します。

オプション: 自社の SQL Server データベースでこのサンプルを試す場合、\sql フォルダーにある 2 つのスクリプトを使用できます。または、app.config 内の接続情報変更することで、お客様の SQL Server インスタンスを参照するようにすることもできます。

特にご確認いただきたいファイルが、\sql フォルダーの add_change_tracking.sql です。下記の 2 つの SQL コマンドを実行するだけで、組み込みの変更追跡機能を上記のテーブルに対して有効化できます。

ALTER DATABASE SyncTest SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

ALTER TABLE Products ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = OFF);

1 つ目のコマンドでデータベース内の変更の追跡を有効化し、SQL Server で変更情報を 2 日間保持するように指定しています。データベース空間の無駄な消費を避けるために、この期間を過ぎた変更情報は削除されます。2 つ目のコマンドでは、どのテーブルを追跡するかを SQL Server に指定します。このサンプルでは、変更された行のみを追跡し、変更された列は追跡しないように指定しています。

列についても変更の追跡を有効化することはできますが、その場合は変更の追跡に使用するストレージ容量が増加するため、変更された行全体よりも変更された列を把握したほうが合理的な場合のみに限定することをお勧めします。データの変更量が多く、また行内の一部の列のみが頻繁に変更される場合には、列の追跡が有効となることもあります。

 

しくみ

ここからは、SQL Server から Azure Search への同期を実行するコンソール アプリケーションのしくみを詳しく説明します。この前の手順で、既に Visual Studio でアプリケーションを開いているかと思います。まだの方は、ここで開いてください。

 

変更の検出

このアプリケーションの要は、SQL Server での変更を効率的に検出する機能です。Program.cs ファイルを開いて、Main(string[] args) 関数に移動してください。最初の行を見ると、下記のようになっています。

_lastVersion = -1;

SQL Server に組み込みの変更の追跡では、「変更バージョン」を使用します。この値は、追跡対象のテーブルのいずれかで変更があるたびに増加します。この変更バージョンを利用することで、特定の変更バージョン以降に行われた変更を戻すように SQL Server に指定することができます。上記の _lastVersion 変数を使用して、正常に完了した直近の同期処理で使われた変更バージョンを追跡します。今回の場合はアプリケーションを初めて実行するので、この値を -1 に設定します。アプリケーションは、この値を読み取り、初回の同期処理としてすべてのデータを同期します。

このアプリケーションの拡張的な利用方法として、_lastVersion の値を適当な場所に保存し、アプリケーションを実行するときにこれを読み込むようにすることもできます。こうすると、アプリケーションを終了しても、終了時点から再開することができます。

この関数の次の行からは、SQL Server データベースでの変更の追跡の初期化、およびデータを受け取る Azure Search のインデックスの作成を行っています。

while (true) ループでは、5 秒ごとに変更を確認しています ([Thread.Sleep(5000)] の部分)。

このループの中で作成される ChangeEnumeratorSql は、最初にすべてのデータを SQL Server から Azure Search にアップロードする際に使用されるクエリです。

その次に changeEnumerator.ComputeChangeSet(_lastVersion) が呼び出されます。この呼び出しでは多くの処理が実行され、その処理は ChangeEnumerator.cs ファイルを開いて ComputeChangeSet(Int64 lastVersion) 関数で確認できます。

この処理では、まず SQL Server に対して直近の変更バージョンを確認する GetChangeSetVersion(con) を呼び出します。この変更バージョンの値を保持しておき、次にこの処理を実行したときに、この時点以降に行われた変更を検出するために使用します。

次に、EnumerateUpdatedDocuments(con, lastVersion) を呼び出します。ここでは、変更されたデータをすべて IEnumerable<Dictionary> に収集します。EnumerateUpdatedDocuments 関数を詳しく見ると、初回 (_lastVersion = -1 の場合) はすべてのデータが選択されることがわかります。_lastVersion > -1 の場合、変更分のみが取得されます。CHANGETABLE というテーブルがありますが、これが変更の追跡で重要な役割を果たしています。このテーブルは SQL Server が保守する内部テーブルです。また、以下の 2 行では、Products テーブルで実行された Insert および Update の処理のみを取得しています。

sqlCmd += ?gand (CT.SYS_CHANGE_OPERATION = ?eU?f ?g;

sqlCmd += ?gor CT.SYS_CHANGE_OPERATION = ?eI?f) ?g;

Delete も同期できるようにするには、このテーブルに対して同様に CT.SYS_CHANGE_OPERATION = ‘D’ と指定します。

Azure SQL Database に関しては、現時点では組み込みの変更追跡はサポートしていません。このため、このアプリケーションの代わりに他の変更追跡手法 (rowversion と tombstone の併用など) を実装する必要があります。

 

Azure Search に変更をプッシュする

ここまでは、変更を取得するメソッドについて説明しました。再び、Program.cs の Main(string[] args) 関数の while (true) ループに戻ります。先ほど説明を中断したところに、ApplyChanges(changes) への呼び出しがあります。この呼び出しでは、Azure Search にアップロードするデータを ChangeSet として渡します。この関数を詳しく見ると、999 件分の変更を一括でアップロードしていることがわかります。また、ここでは “mergeOrUpload” というアクションを使用します。これにより、ドキュメント キーが存在しない場合には受け取ったデータを挿入し、ドキュメント キーが存在する場合は対応するドキュメントの値を更新するように Azure Search に通知します。キー フィールドは、Azure Search でドキュメントを一意に識別するために使用します。このサンプルの場合、productID フィールドをキー フィールドとして使用しています。

拡張的な利用方法として、アップロードする行が新規であることが確実な場合、“merge” や “mergeOrUpload” ではなく “upload” を使用すると処理が迅速に完了します。

 

同期のスケジュール設定

今回のチュートリアルでは while ループを使用して、5 秒ごとに変更を確認して同期する非常にシンプルなコンソール アプリケーションを実行していますが、オンプレミスの運用環境では、これを Windows のサービスとして実行したり、何らかのスケジュール設定されたジョブに実装したりするとよいでしょう。また、(SQL Server VM などではなく) クラウドで実行する場合、この機能の実装には WebJob や Web ロールの利用が最適です。

 

アプリケーションを実行してデータの変更を SQL Server から Azure Search にアップロードする

これでアプリケーションを起動する準備が完了しました。Program.cs の Main 関数にブレークポイントを追加し、このアプリケーションを手順ごとに実行できるようにします。コンソールには次のようなメッセージが表示されます。

Sync Processing Started…

Creating SQL Server database with Products table…

Enabling change tracking for Products table…

Uploading 294 changes…

Sync Complete, waiting 5 seconds…

Sync Complete, waiting 5 seconds…

このアプリケーションを初めて実行するときには、294 行すべてがアップロードされます。その後、5 秒ごとにアプリケーションが変更を確認し、アップロードを行います。

どれか 1 行を変更し、どのように処理されるかを確認しましょう。アプリケーションを実行したまま SQL Server データベースの AzureSearchSyncTest に接続して、次のコマンドを実行します。

UPDATE [Products] set Color = ‘Green’ where ProductID = 680

コンソール画面に戻ると、次のメッセージが表示されます。

Uploading 1 changes…

これは、アプリケーションが変更を 1 つ検出し、Azure Search にアップロードしたことを示しています。行を新たに 1 行挿入しても同様に処理されます。組み込みの変更追跡では行の削除も追跡できますが、このサンプルでは行っていません。

 

Azure Search のインデックスでデータを検証する

データを Azure Search のインデックスに同期することができたので、インデックスに対してクエリを発行し、データが存在することを確認します。この手順では Fiddler (英語) を使用します。

  • Fiddler で [Composer] タブ、[Parsed] タブの順に選択します。
  • コンボ ボックスで [GET] を選択します。
  • [URL] フィールドに次の URL を入力します。“AZURESEARCH_SERVICENAME” の部分はお客様の Azure Search サービス名に変更してください。 https://[AZURESEARCH_SERVICENAME].search.windows.net/indexes/catalog/docs(’680′)?api-version=2014-07-31-Preview
  • 要求ヘッダーのセクションでは次のように入力します。api-key の値には、お客様の有効な API キーを入力してください。
  • [Execute] ボタンをクリックします。

左側のサイドバーに次の行が表示されます。

この行をダブル クリックするとウィンドウが表示され、この行に対する JSON の結果を確認できます。

この行が存在していて、color の値が Green にアップロードされていることがわかります。

これで、SQL Server と Azure Search の同期を実行できました。

 

サンプルの拡張

下記のように、このサンプルを拡張することができます。

  1. Azure Search への削除のアップロードをサポートする。Azure Search で削除を実行する方法の詳細については、このトピック向けの Azure Search API ドキュメント (英語) を参照してください。
  2. アプリケーションを起動するたびに _lastVersion の値を -1 に設定する。これにより、アプリケーション起動時に必ずデータの変更がすべて Azure Search にアップロードされます。正常に完了した直近の変更バージョンをお客様のデータベース内に保存し、アプリケーション起動時にその値を取得するようにすると、アプリケーションの前回終了時点からの変更が追跡されます。
  3. このコンソール アプリケーションを Windows サービスに変更する。

マイクロソフトでは皆様からのフィードバックをお待ちしております。ぜひこちら (英語) までお寄せください。

今回は Liam Cavanagh の記事をご紹介しました。ご意見などがございましたら、ブログ (英語) または Twitter からご連絡ください。