MicrosoftSQLServerでのデータ同期ガイド

分離されたシステム間で関連情報を共有することは、データの品質と可用性を向上させることができるため、組織にとってますます重要になっています。 複数のディレクトリサーバーで使用可能で一貫性のあるデータセットがあると便利な状況がたくさんあります。 そのため、SQLServerのデータ同期を実行するための一般的な方法を知ることが重要です。

データの可用性と一貫性は、データ複製とデータ同期プロセスによって実現できます。 データレプリケーションは、フォールトトレランスまたはアクセシビリティの向上を目的として、データベースの1つ以上の冗長コピーを作成するプロセスです。 データ同期は、2つ以上のデータベース間でデータの整合性を確立し、その後の継続的な更新によってその整合性を維持するプロセスです。

多くの組織では、さまざまなシステム間でデータ同期を実行することが望ましく、困難です。 データ同期を実行する必要がある多くのユースケースを見つけることができます。

• データベースの移行
• 情報システム間の定期的な同期
• ある情報システムから別の情報システムへのデータのインポート
• 異なるステージまたは環境間でのデータセットの移動
• データベース以外のソースからのデータのインポート

データ同期のための独自の方法や満場一致で合意された方法はありません。 このタスクはケースごとに異なり、データ構造が複雑なため、一見単純なはずのデータ同期でさえ複雑になる可能性があります。 実際のシナリオでは、データ同期は多くの複雑なタスクで構成されており、実行に時間がかかる場合があります。 新しい要件が発生した場合、データベーススペシャリストは通常​​、同期プロセス全体を再実装する必要があります。 これを行う標準的な方法がないため、レプリケーション以外に、データ同期の実装が最適になることはめったにありません。 その結果、メンテナンスが困難になり、費用が高くなります。 データ同期の実装と保守は非常に時間のかかるプロセスであり、それ自体がフルタイムの仕事になる可能性があります。

おそらくMicrosoftSyncFrameworkを使用して、データ同期タスクのアーキテクチャを手動で実装することも、MicrosoftSQLServerを管理するためのツール内で既に作成されたソリューションから利益を得ることができます。 Microsoft SQL Serverデータベースのデータ同期を解決するために使用できる最も一般的な方法とツールについて説明し、いくつかの推奨事項を示します。

ソースと宛先の構造（データベース、テーブルなど）に基づいて、構造が類似または異なる場合のユースケースを区別できます。

ソースと宛先の構造は非常に似ています

これは、ソフトウェア開発ライフサイクルのさまざまな段階でデータを使用する場合によくあることです。 たとえば、テスト環境と本番環境のデータ構造は非常に似ています。 一般的な要件は、テストデータベースと本番データベースの間でデータを比較し、本番データベースからテストデータベースにデータをインポートすることです。

ソースと宛先の構造が異なる

構造が異なる場合、同期はより複雑になります。 これは、より頻繁に繰り返されるタスクでもあります。 一般的なケースは、あるデータベースから別のデータベースにインポートすることです。 最も一般的なケースは、あるソフトウェアが別の会社によって管理されている別のソフトウェアからデータをインポートする必要がある場合です。 通常、インポートはスケジュールに基づいて自動的に実行される必要があります。

使用する方法は、個人の好みと解決する必要のある問題の複雑さによって異なります。

構造がどれほど類似しているかに関係なく、データ同期を解決するために4つの異なる方法を選択できます。

• 手動で作成したSQLスクリプトを使用した同期
• データ比較方式を使用した同期（ソースとターゲットの構造が類似している場合にのみ使用できます）
• 自動生成されたSQLスクリプトを使用した同期-商用製品が必要

ソースと宛先の構造が同じまたは非常に似ている

手動で作成されたSQLスクリプトの使用

最も簡単で面倒な解決策は、同期用のSQLスクリプトを手動で作成することです。

利点

• 無料のオープンソース（FOSS）ツールで実行できます。
• テーブルにインデックスがある場合、それは非常に高速です。
• SQLスクリプトは、ストアドプロシージャに保存することも、SQLServerのジョブとして定期的に実行することもできます。
• 継続的に変更されるデータでも、自動インポートとして使用できます。

短所

• このようなSQLスクリプトの作成は、通常、テーブルごとにINSERT 、 UPDATE 、およびDELETEの3つのスクリプトが必要になるため、非常に面倒です。
• 同期できるのはSQLクエリを介して利用できるデータのみであるため、CSVファイルやXMLファイルなどのソースからインポートすることはできません。
• 保守が困難です。データベース構造が変更された場合、2つまたは3つのスクリプト（ INSERT 、 UPDATE 、場合によってはDELETE ）を変更する必要があります。

例

IDとValueの列を持つSourceテーブルと、同じ列のTargetテーブルの間で同期を行います。

テーブルに同じ主キーがあり、ターゲットテーブルに自動インクリメント（ID）主キーがない場合は、次の同期スクリプトを実行できます。

 -- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

データ比較方法の使用

この方法では、ツールを使用してソースデータとターゲットデータを比較できます。 比較プロセスでは、ソースデータベースとの違いをターゲットデータベースに適用するSQLスクリプトが生成されます。

データの比較と同期のためのプログラムはたくさんあります。 これらのプログラムはほとんど同じアプローチを使用します。 ユーザーはソースデータベースとターゲットデータベースを選択しますが、他の選択肢としては、DBバックアップ、SQLスクリプトを含むフォルダー、またはソース管理システムへの接続があります。

以下は、データ比較アプローチを使用する最も一般的なツールです。

• SQLServerのdbForgeデータ比較
• RedGateSQLデータ比較
• ApexSQLデータ差分

最初のステップでは、データが読み取られるか、ソースとターゲットからのより大きなデータのチェックサムが読み取られます。 次に、比較プロセスが実行されます。

これらのツールは、同期のための追加設定も提供します。

データ同期に必要な次の構成オプションを設定する必要があります。

同期キー

デフォルトでは、主キーまたはUNIQUE制約が使用されます。 主キーがない場合は、列の組み合わせを選択できます。 同期キーは、ソースの行とターゲットの行をペアにするために使用されます。

テーブルペアリング

デフォルトでは、テーブルは名前でペアになっています。 これを変更して、必要に応じてペアリングすることができます。 dbForge Data Compareソフトウェアでは、SQLクエリをソースまたは宛先として選択できます。

同期プロセス

確認後、ツールはソースデータとターゲットデータを比較します。 プロセス全体は、すべてのソースデータとターゲットデータをダウンロードし、指定された基準に基づいてそれらを比較することで構成されます。 デフォルトでは、同じ名前のテーブルと列の値が比較されます。 すべてのツールは、列名とテーブル名のマッピングをサポートしています。 また、 IDENTITY （自動インクリメント）列を除外したり、値を比較する前にいくつかの変換を実行したりする可能性があります（浮動小数点型の丸め、大文字小文字の無視、 NULLを空の文字列として扱うなど）。データのダウンロードが最適化されます。 データ量が多い場合は、チェックサムのみがダウンロードされます。 この最適化はほとんどの場合に役立ちますが、操作を実行するための時間要件はデータの量とともに増加します。

次のステップでは、移行が生成されたSQLスクリプトがあります。 このスクリプトは、保存することも、直接実行することもできます。 安全のために、このスクリプトを実行する前にデータベースのバックアップを作成することもできます。 ApexSQL Data Diffツールは、選択したデータベースでスクリプトを実行する実行可能プログラムを作成できます。 このスクリプトには、変更方法のロジックではなく、変更が必要なデータが含まれています。 これは、定期的なインポートを提供するためにスクリプトを自動的に実行できないことを意味します。 これがこのアプローチの最大の欠点です。

利点

• SQLの高度な知識は必要なく、GUIを介して行うことができます。
• 同期する前に、データベース間の違いを視覚的に確認することができます。

短所

• これは、商用製品の高度な機能です。
• 大量のデータを転送すると、パフォーマンスが低下します。
• 生成されたSQLスクリプトには違いのみが含まれているため、将来のデータを自動的に同期するために再利用することはできません。

以下に、これらのツールの一般的なUIを示します。

自動生成されたSQLと同期する

この方法は、データ比較方法と非常によく似ています。 前の方法との唯一の違いは、データの比較がなく、生成されたSQLスクリプトにデータの違いは含まれていませんが、同期ロジックが含まれていることです。 生成されたスクリプトは、ストアドプロシージャに簡単に保存でき、定期的に（たとえば、毎晩）実行できます。 この方法は、データベース間の自動インポートに役立ちます。 このメソッドのパフォーマンスは、データ比較メソッドよりもはるかに優れています。

自動生成されたSQLによる同期は、SQLDatabaseStudioによってのみ提供されます。

SQL Database Studioは、データ比較メソッドと同様のインターフェースを提供します。 ソースとターゲット（データベースまたはテーブル）を選択する必要があります。 次に、オプション（同期キー、ペアリング、マッピング）を設定する必要があります。 すべてのパラメーターを設定するためのグラフィカルなクエリビルダー機能があります。

利点

• SQLの高度な知識は必要ありません。
• GUIですべてをすばやく設定できます。
• 結果のSQLスクリプトは、ストアドプロシージャに保存できます。
• 自動インポートとして、SQLServerのジョブとして使用できます。

短所

• これは、商用製品の高度な機能です。
• プロセス全体が1つのステップで実行されるため、同期前に手動で差異を確認することはできません。

パフォーマンスベンチマーク

テストケース

2つのデータベース（AとB）。それぞれに2,000,000行のテーブルが1つ含まれています。 テーブルは、同じSQLServer上の2つの異なるデータベースにあります。 このテストは、2つの極端なケースをカバーします。1）ソーステーブルにすべての2,000,000行が含まれ、ターゲットテーブルが空です。 同期では、多くのINSERTSを提供する必要があります。 2）ソーステーブルとターゲットテーブルには2,000,000行が含まれています。 違いは1行だけです。 同期では、 UPDATEを1つだけ提供する必要があります。

RedGateデータ比較には3つのステップが必要です。

• 比較
• スクリプトを生成する
• ターゲットデータベースでスクリプトを実行する

ApexSQLDataDiffには2つのステップが必要です。

• 比較
• スクリプトを生成し、スクリプトを1つのステップで実行します

SQL Database Studioは、同期全体を1つのステップで実行します。 以下は、秒単位の同期時間です。 「個々のステップ」というラベルの付いた列には、上記の同期ステップの期間が表示されます。

低いほど良いです。

同じテストですが、データベースは異なるSQLサーバー上にあり、リンクサーバーを介して接続されていません。

低いほど良いです。

概要

結果から、同期アルゴリズムはSQL Serverに依存しないため、RedGateとApexがデータベースが同じSQLServer上にあるかどうかを気にしないことは明らかです。 SQL Database Studioは、SQLServerのネイティブ機能を使用します。 したがって、データベースが同じサーバー上にある場合、結果はより良くなります。

ソースと宛先の構造が異なります

1つの幅の広いテーブルを多数の小さな関連テーブルに同期する必要がある場合もあります。

この例は、1つの広いテーブルSourceDataで構成されており、小さなテーブルContinent 、 Country 、 Cityに同期する必要があります。 スキームを以下に示します。

SourceDataのデータは、次の画像のようになります。

手動で作成したSQLスクリプトを使用する

大陸テーブルを同期するスクリプト

INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

シティテーブルを同期するスクリプト

INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

このスクリプトはもっと複雑です。 これは、 CountryテーブルとContinentテーブルのレコードを見つける必要があるためです。 このスクリプトは、欠落しているレコードをCityに挿入し、 ContryIdを正しく入力します。

必要に応じて、 UPDATEスクリプトとDELETEスクリプトも同じ方法で作成できます。

利点

• 市販品は必要ありません。
• SQLスクリプトは、ストアドプロシージャに保存することも、SQLServerのジョブとして定期的に実行することもできます。

短所

• このようなSQLスクリプトの作成は困難で複雑です（テーブルごとに、通常、 INSERT 、 UPDATE 、およびDELETEの3つのスクリプトが必要です）。
• 維持するのは非常に難しいです。

外部ツールの使用

この種の同期（多くの関連テーブルへのワイドテーブル）は、さまざまなユースケースに焦点を合わせているため、データ比較メソッドでは実行できません。 データ比較メソッドは、挿入されるデータを含むSQLスクリプトを生成するため、関連するテーブルで参照を検索する簡単な機能はありません。 そのため、この方法を使用するアプリケーションは使用できません（SQLServer用のdbForgeData Compare、RedGate SQL Data Compare、Apex SQL Data Diff）。

ただし、SQL Database Studioを使用すると、同期スクリプトを自動的に作成できます。 次の図には、SQLDatabaseStudioのデータ同期用エディターと呼ばれる要素があります。

Editorは、よく知られているQuery Builderのように見え、非常によく似た方法で機能します。 各テーブルには同期キーが定義されている必要がありますが、テーブル間には関係も定義されています。 上の写真には、同期のためのマッピングもあります。 列リスト（画像の下部）には、 Cityテーブルの列があります（他のテーブルについても同様です）。

列

• Id —この列は主キー（自動生成）であるため、マップされません。
• CountryId —この列は、テーブルの参照として定義されます。
• 名前—この列は、ソーステーブル（ワイドテーブル）の列Cityから入力されます。

列CountryIdとNameが同期キーとして選択されます。 同期キーは、ソーステーブルとターゲットテーブルの行を一意に識別する列のセットです。 主キーIdはソーステーブルにないため、同期キーとして使用することはできません。

同期後、テーブルは次のようになります。

上記の例では、ソースとして1つの幅の広いテーブルがありました。 ソースデータがいくつかの関連するテーブルに格納されている場合の一般的なシナリオもあります。 SQL Database Studioのリレーションは、外部キーではなく列名を使用して定義されます。 このようにして、CSVまたはExcelファイルからインポートすることもできます（ファイルは一時テーブルにロードされ、同期はそのテーブルから実行されます）。 一意の列名を付けることをお勧めします。 これが不可能な場合は、それらの列のエイリアスを定義できます。

利点

• 簡単かつ迅速に作成
• メンテナンスが簡単
• ストアドプロシージャに保存できます（ストアドプロシージャは、後でエディタで同期を開くために必要なデータとともに保存されます）

短所

• 商用ソリューション

ソリューションの比較

データ同期は、一連のINSERT 、 UPDATE 、またはDELETEコマンドで構成されます。 これらのコマンドのシーケンスを作成する方法は複数あります。 この記事では、同期SQLスクリプトを作成するための3つのオプションについて説明しました。 最初のオプションは、すべてを手動で作成することです。 それは実行可能であり（ただし時間がかかりすぎる）、SQLの複雑な理解が必要であり、作成と保守が困難です。 2番目のオプションは、商用ツールを使用することです。 次のツールを調べました。

• SQLServerのdbForgeデータ比較
• RedGateSQLデータ比較
• ApexSQLデータ差分
• SQLデータベーススタジオ

最初の3つのツールは非常によく似ています。 それらはデータを比較し、ユーザーに違いを分析させ、選択された違いを（自動的にまたはコマンドラインからでも）同期させることができます。 これらは、次の使用シナリオに役立ちます。

• さまざまなエラーが原因で、データベースが同期していません。
• 環境間でデータを転送するときは、レプリケーションを回避する必要があります。
• ExcelまたはHTMLのデータ比較レポートが必要です。

各ツールは、何らかの理由で愛されています。dbForgeには優れたUIと多くのオプションがあり、ApexSQLは他のツールよりも優れたパフォーマンスを発揮し、RedGateが最も人気があります。

4番目のツールであるSQLDatabaseStudioは、動作が少し異なります。 変更ではなく、同期ロジックを含むSQLスクリプトを生成します。 すべての作業はデータベースサーバー上で直接行われるため、パフォーマンスも優れています。データベースサーバーと同期ツール間のデータ転送は必要ありません。 このツールは、次のユースケースに役立ちます。

• データベースの構造が異なる場合のデータベースの自動移行
• 複数の関連するテーブルにインポートする
• 外部ソースからのインポートXML、CSV、MS Excel