パラメータ化された行フィルタ
パラメータ化されたフィルタを使用すると、複数のパブリケーションを作成しなくても、パーティションの異なるデータを各サブスクライバに送信できます (以前のバージョンの SQL Server では、パラメータ化されたフィルタは動的フィルタと呼ばれていました)。パーティションとは、単にテーブル内の行のサブセットのことです。パラメータ化されたフィルタの作成時に選択した設定に基づき、パブリッシュされたテーブルの各行は、1 つのパーティションのみに属するか (重複しないパーティションが作成されます)、または 2 つ以上のパーティションに属します (重複するパーティションが作成されます)。
重複しないパーティションは、サブスクリプション間で共有するか、または 1 つのサブスクリプションのみが特定のパーティションを受け取るように制限することができます。パーティションの動作を制御する設定については、このトピックの「適切なフィルタ選択オプションの使用」で説明します。これらの設定を使用すると、アプリケーションおよびパフォーマンスの要件に応じて、パラメータ化されたフィルタ選択を調整できます。一般に、重複するパーティションを使用すると柔軟性が向上し、重複しないパーティションを単一のサブスクリプションにレプリケートするとパフォーマンスが向上します。
パラメータ化されたフィルタは単一のテーブルで使用し、通常は関連するテーブルに対するフィルタ選択を拡張するために結合フィルタと組み合わせて使用します。詳細については、「結合フィルタ」を参照してください。
パラメータ化された行フィルタを定義または変更するには
SQL Server Management Studio: マージ アーティクルのパラメータ化された行フィルタを定義および変更する方法 (SQL Server Management Studio)
レプリケーション Transact-SQL プログラミング : マージ アーティクルに対してパラメータ化した行フィルタを定義および変更する方法 (レプリケーション Transact-SQL プログラミング)
レプリケーション管理オブジェクト (RMO) プログラミング : アーティクルを定義する方法 (RMO プログラミング)
パラメータ化されたフィルタの動作
パラメータ化された行フィルタでは、WHERE 句を使用して、パブリッシュする適切なデータを選択します。静的行フィルタのようにこの句でリテラル値を指定するのではなく、システム関数 SUSER_SNAME() および HOST_NAME() のいずれかまたは両方を指定します。ユーザー定義関数も使用できますが、その場合はユーザー定義関数の本体に SUSER_SNAME() または HOST_NAME() を含めるか、または MyUDF(SUSER_SNAME() のように、ユーザー定義関数でこれらのシステム関数のいずれかを評価する必要があります。ユーザー定義関数の本体に SUSER_SNAME() または HOST_NAME() を含める場合、その関数にパラメータを渡すことはできません。
システム関数 SUSER_SNAME() および HOST_NAME() は、マージ レプリケーションに固有のものではありませんが、パラメータ化されたフィルタ選択のためにマージ レプリケーションで使用されます。
SUSER_SNAME() は、SQL Server のインスタンスに対する接続のログイン情報を返します。この関数をパラメータ化されたフィルタで使用した場合、マージ エージェントがパブリッシャに接続するために使用するログインが返されます (ログインはサブスクリプションを作成するときに指定します)。
HOST_NAME() は、SQL Server のインスタンスに接続しているコンピュータの名前を返します。この関数をパラメータ化されたフィルタで使用した場合、既定では、マージ エージェントが実行されているコンピュータの名前が返されます。プル サブスクリプションの場合はサブスクライバの名前になり、プッシュ サブスクリプションの場合はディストリビュータの名前になります。
サブスクライバまたはディストリビュータの名前以外の値で、この関数をオーバーライドすることも可能です。通常、アプリケーションでは、販売員の名前や ID など、もっと意味のある値でこの関数をオーバーライドします。詳細については、このトピックの「HOST_NAME() 値のオーバーライド」を参照してください。
システム関数によって返された値は、フィルタ選択しているテーブルで指定した列と比較され、適切なデータがサブスクライバにダウンロードされます。この比較は、サブスクリプションが初期化されたときに実行され (これにより初期スナップショットには適切なデータのみが含まれます)、またサブスクリプションが同期されるたびに実行されます。既定では、パブリッシャでの変更によってパーティションから行が移動すると、その行はサブスクライバで削除されます (この動作は、sp_addmergepublication (Transact-SQL) の @allow_partition_realignment パラメータを使用して制御されます)。
.gif "注意") 注意 |
|---|
パラメータ化されたフィルタの比較が実行されるときは、常にデータベース照合順序が使用されます。たとえば、データベース照合順序で大文字と小文字が区別されず、テーブルまたは列の照合順序で大文字と小文字が区別される場合、比較では大文字と小文字は区別されません。 |
SUSER_SNAME() によるフィルタ選択
Adventure Works サンプル データベースの Employee テーブルについて考えてみます。このテーブルには LoginID 列が含まれており、この列には各従業員のログインが 'domain\login' という形式で格納されています。このテーブルにフィルタを適用して、従業員が各自に関連するデータのみを受け取れるようにするには、フィルタ句を次のように指定します。
LoginID = SUSER_SNAME()
たとえば、ある従業員の値が 'adventure-works\john5' であるとします。マージ エージェントはパブリッシャに接続するときに、サブスクリプションの作成時に指定されたログインを使用します (この場合は 'adventure-works\john5')。次に、マージ エージェントは SUSER_SNAME() によって返された値をこのテーブル内の値と比較し、LoginID 列に 'adventure-works\john5' という値が格納された行のみをダウンロードします。
HOST_NAME() によるフィルタ選択
HumanResources.Employee テーブルについて考えてみます。このテーブルに、ComputerName という列が含まれており、この列に各従業員のコンピュータの名前が 'name_computertype' という形式で格納されているとします。このテーブルにフィルタを適用して、従業員が各自に関連するデータのみを受け取れるようにするには、フィルタ句を次のように指定します。
ComputerName = HOST_NAME()
たとえば、ある従業員の値が 'john5_laptop' であるとします。マージ エージェントはパブリッシャに接続すると、HOST_NAME() によって返された値をこのテーブル内の値と比較し、ComputerName 列に 'john5_laptop' という値が格納された行のみをダウンロードします。
フィルタでは関数を組み合わせて使用することもできます。たとえば、従業員が自分のコンピュータで自分のログインを使用している場合にのみデータを受け取れるようにするには、フィルタ句を次のように指定します。
LoginID = SUSER_SNAME() AND ComputerName = HOST_NAME()
HOST_NAME() の値をオーバーライドしている場合を除き、HOST_NAME() によるフィルタ選択は、通常はプル サブスクリプションでのみ使用します。この関数によって返される値は、マージ エージェントが実行されているコンピュータの名前です。この値は、プル サブスクリプションの場合は各サブスクリプションによって異なりますが、プッシュ サブスクリプションの場合は同じです (プッシュ サブスクリプションの場合、すべてのマージ エージェントがディストリビュータで実行されます)。
.gif "セキュリティに関する注意") セキュリティに関する注意 |
|---|
HOST_NAME() 関数の値はオーバーライドされている場合があります。したがって、HOST_NAME() を含むフィルタを使用してデータのパーティションへのアクセスを制御することはできません。データのパーティションへのアクセスを制御するには、SUSER_SNAME()、SUSER_SNAME() と HOST_NAME() の組み合わせ、または静的行フィルタを使用します。 |
HOST_NAME() 値のオーバーライド
既に述べたとおり、HOST_NAME() は、既定では SQL Server のインスタンスに接続しているコンピュータの名前を返します。パラメータ化されたフィルタを使用する場合、サブスクリプションの作成時に値を指定することによって、この値をオーバーライドすることがよくあります。HOST_NAME() 関数の値をオーバーライドすると、コンピュータの名前ではなく、指定した値が返されます。
| 注意 |
|---|
HOST_NAME() をオーバーライドすると、HOST_NAME() 関数のすべての呼び出しで、指定した値が返されます。HOST_NAME() がコンピュータ名を返す処理に他のアプリケーションが依存していないことを確認してください。 |
HumanResources.Employee テーブルについて考えてみます。このテーブルには EmployeeID という列があります。このテーブルにフィルタを適用して各従業員が関連データのみを受け取れるようにするには、フィルタ句を次のように指定します。
EmployeeID = CONVERT(int,HOST_NAME())
たとえば、従業員の Pamela Ansman-Wolfe には、280 という従業員 ID が割り当てられています。この従業員のサブスクリプションの作成時に、従業員 ID の値 (この場合は 280) を HOST_NAME() 値に指定します。マージ エージェントはパブリッシャに接続すると、HOST_NAME() によって返された値をこのテーブル内の値と比較し、EmployeeID 列に 280 という値が格納された行のみをダウンロードします。
.gif "重要な注意事項") 重要 |
|---|
HOST_NAME() 関数は nchar 型の値を返します。したがって、上記の例のようにフィルタ句の列が数値データ型の場合は、CONVERT を使用する必要があります。CONVERT(nchar,EmployeeID) = HOST_NAME() のように、パラメータ化された行フィルタの句で列名に関数を適用するとパフォーマンスが低下するため、使用しないことをお勧めします。代わりに、この例で示されている EmployeeID = CONVERT(int,HOST_NAME()) という句を使用することをお勧めします。この句は、sp_addmergearticle の @subset_filterclause パラメータに使用できますが、通常はパブリケーションの新規作成ウィザードでは使用できません (このウィザードではフィルタ句を実行して検証を行いますが、コンピュータ名を int 型に変換できないため失敗します)。パブリケーションの新規作成ウィザードを使用する場合は、このウィザードで CONVERT(nchar,EmployeeID) = HOST_NAME() を指定し、次に sp_changemergearticle を使用して句を EmployeeID = CONVERT(int,HOST_NAME()) に変更してから、パブリケーションのスナップショットを作成することをお勧めします。 |
HOST_NAME() 値をオーバーライドするには
以下のいずれかの方法で、HOST_NAME() 値をオーバーライドします。
SQL Server Management Studio: サブスクリプションの新規作成ウィザードの [HOST_NAME 値] ページで値を指定します。サブスクリプションの作成の詳細については、「パブリケーションのサブスクライブ」を参照してください。
レプリケーション Transact-SQL プログラミング : sp_addmergesubscription (Transact-SQL) (プッシュ サブスクリプションの場合) または sp_addmergepullsubscription_agent (Transact-SQL) (プル サブスクリプションの場合) の @hostname パラメータの値を指定します。
マージ エージェント : コマンド ラインまたはエージェント プロファイルで -Hostname パラメータの値を指定します。マージ エージェントの詳細については、「レプリケーション マージ エージェント」を参照してください。エージェント プロファイルの詳細については、「レプリケーション エージェント プロファイル」を参照してください。
パラメータ化されたフィルタを使用したパブリケーションへのサブスクリプションの初期化
パラメータ化された行フィルタがマージ パブリケーションで使用される場合、レプリケーションは 2 つの要素から成るスナップショットを持つ各サブスクリプションを初期化します。詳細については、「パラメータ化されたフィルタを使用したマージ パブリケーションのスナップショット」を参照してください。
適切なフィルタ選択オプションの使用
パラメータ化されたフィルタを使用する場合、制御できる主要な要素が 2 つあります。
マージ レプリケーションによるフィルタの処理方法。"パーティション グループを使用" および "パーティションの変更を保持" の 2 つのパブリケーション設定のいずれかで制御されます。
サブスクライバ間でのデータの共有方法。アーティクル設定の [パーティションのオプション] で指定する必要があります。
フィルタ選択オプションを設定するには
SQL Server Management Studio: パラメータ化された行フィルタを最適化する方法 (SQL Server Management Studio)
レプリケーション Transact-SQL プログラミング : パラメータ化された行フィルタを最適化する方法 (レプリケーション Transact-SQL プログラミング)
"パーティション グループを使用" および "パーティションの変更を保持" の設定
パーティション グループを使用オプションとパーティションの変更を保持オプションでは、いずれもパブリケーション データベースに追加のメタデータを格納することにより、フィルタ選択されたアーティクルを持つパブリケーションの同期パフォーマンスを向上します。パーティション グループを使用オプションでは、事前計算済みパーティション機能を使用することにより、パフォーマンスを向上させることができます。このオプションは、パブリケーションのアーティクルが一連の要件を満たしている場合に、既定で true に設定されています。これらの要件の詳細については、「事前計算済みパーティションによるパラメータ化されたフィルタのパフォーマンス最適化」を参照してください。アーティクルが事前計算済みパーティションを使用するための要件を満たしていない場合は、パーティションの変更を保持オプションが true に設定されます。
[パーティションのオプション] の設定
[パーティションのオプション] プロパティの値は、アーティクルを作成するときに、フィルタ選択されたテーブルのデータをサブスクライバが共有する方法に応じて設定します。このプロパティは、sp_addmergearticle、sp_changemergearticle、および [アーティクルのプロパティ] ダイアログ ボックスを使用して、4 つの値のいずれかに設定できます。このプロパティは、[フィルタの追加] ダイアログ ボックスまたは [フィルタの編集] ダイアログ ボックスを使用して、2 つの値のいずれかに設定できます。これらのダイアログ ボックスは、パブリケーションの新規作成ウィザードおよび [パブリケーションのプロパティ] ダイアログ ボックスから使用できます。次の表は、利用可能な値をまとめたものです。
説明 |
[フィルタの追加] および [フィルタの編集] の値 |
[アーティクルのプロパティ] の値 |
ストアド プロシージャ内の値 |
|---|---|---|---|
パーティション内のデータは重複しています。サブスクライバはパラメータ化されたフィルタで参照されている列を更新できます。 |
[このテーブルの 1 行を複数のサブスクリプションに移動する] |
[重複する] |
0 |
パーティション内のデータは重複しています。サブスクライバはパラメータ化されたフィルタで参照されている列を更新できません。 |
N/A1 |
[重複する (パーティション外のデータ変更を禁止)] |
1 |
パーティション内のデータは重複していません。データはサブスクリプション間で共有されます。サブスクライバはパラメータ化されたフィルタで参照されている列を更新できません。 |
N/A1 |
[重複しない (複数のサブスクリプションで共有)] |
2 |
パーティション内のデータは重複していません。パーティションごとに単一のサブスクリプションが存在します。サブスクライバはパラメータ化されたフィルタで参照されている列を更新できません。2 |
[このテーブルの 1 行を 1 つのサブスクリプションのみに移動する] |
[重複しない (単一のサブスクリプション)] |
3 |
1 基になるフィルタ選択オプションが 0、1、または 2 に設定されている場合、[フィルタの追加] ダイアログ ボックスと [フィルタの編集] ダイアログ ボックスには、[このテーブルの 1 行を複数のサブスクリプションに移動する] が表示されます。
2 このオプションを指定する場合、該当するアーティクル内のデータの各パーティションに対し、単一のサブスクリプションのみを使用できます。2 つ目のサブスクリプションを作成し、その新しいサブスクリプションのフィルタ選択条件が既存のサブスクリプションと同じパーティションに解決される場合、既存のサブスクリプションは削除されます。
| 重要 |
|---|
[パーティションのオプション] の値は、サブスクライバによるデータの共有方法に応じて設定する必要があります。たとえば、パーティションが重複せず、パーティションごとに単一のサブスクリプションが存在するように指定したにもかかわらず、データが別のサブスクライバで更新された場合、マージ エージェントは同期中に失敗し、未集約が発生することがあります。 |
適切なパーティション オプションの選択
重複しないパーティションは事前計算済みパーティションと連動して、一部の機能上の制限が許容される状況でパフォーマンスを向上します。事前計算済みパーティションを使用すると、サブスクライバへのダウンロードの速度は向上しますが、アップロードの速度は低下します。重複しないパーティションを使用すると、事前計算済みパーティションに関連するアップロードの負荷は最小限に抑えられます。重複しないパーティションのパフォーマンス上の利点は、使用するパラメータ化されたフィルタと結合フィルタが複雑になるほど明確になります。
パブリケーションで使用するパーティション オプションを決めるときは、次のシナリオを検討してください。
Adventure Works には移動営業部門があり、郵便番号ごとに顧客の担当が決まっています。アプリケーションでは、顧客がある営業区域から別の営業区域に移動した場合に、郵便番号を更新し、その顧客が別の営業担当者に割り当てられるようにする必要があります。パラメータ化されたフィルタは顧客の郵便番号に基づいています。更新により、該当する郵便番号が、ある営業担当者のパーティションから削除されて別の営業担当者のパーティションに挿入されます。このためには、パラメータ化されたフィルタで参照されている列を更新する機能を持つ、重複するパーティションが必要です。このオプションでは、柔軟性は最大になりますが、重複しないパーティションほどのパフォーマンスは得られない可能性があります。
ある政府機関では、州の各郡にある地域事務所にデータを提供しています。このデータは重複していません。この政府機関の本部にあるテーブルの各行は、1 つのパーティションにのみ含まれていますが、そのパーティションは同じ郡にある複数の事務所に送信されます。この場合、サブスクリプション間で共有されるパーティションに対して重複しないパーティション オプションを使用するのが適切です。これにより、重複するパーティションよりも高いパフォーマンスを実現しつつ、アプリケーションの要件を満たすことができます。
重複しないパーティションを使用し、1 つのサブスクリプションだけが 1 つのパーティション内のデータを受信および更新する場合は、さらにパフォーマンスを向上させることができます。このシナリオは、POS システムや、データが主にサブスクライバで収集されてパブリッシャにアップロードされるフィールド フォース アプリケーションで一般的です。配送アプリケーションの Package テーブルについて考えてみます。各配送物がトラックに積み込まれると、その配送物の状態が Package テーブルで変更され、変更が本部にレプリケートされます。別のトラックの運転手が同じ配送物の状態を更新することはありません。したがって Package テーブルには、パーティションごとに単一のサブスクリプションが存在する重複しないパーティションが適しています。
重複しないパーティションに関する注意点
重複しないパーティションを使用するときは、以下の点に注意してください。
全般的な注意点
パブリケーションでは事前計算済みパーティションを使用する必要があります。
1 つの行は 1 つのパーティションにのみ属している必要があります。
アーティクルを論理レコードの一部にすることはできません。
代替同期パートナーはサポートされません (この機能は推奨されていません)。
サブスクライバはパラメータ化されたフィルタで参照されている列を更新できません。
サブスクライバでの挿入がパーティションに属さない場合、その挿入は削除されません。ただし、他のサブスクライバにはレプリケートされません。
重複するパーティションを使用する一部の状況では、マージ エージェントがデータを挿入するときに、ID 範囲が調整されます。重複しないパーティションでは、サブスクリプション データベースで ID 範囲を調整する権限を持つユーザーのみが、挿入時に範囲を調整できます。ユーザーはテーブルを所有しているか、sysadmin 固定サーバー ロール、db_owner 固定データベース ロール、または db_ddladmin 固定データベース ロールのメンバである必要があります。
パーティションごとに単一のサブスクリプションが存在する重複しないパーティションに関するその他の注意点
アーティクルは 1 つのパブリケーションにのみ存在することができます。アーティクルを再パブリッシュすることはできません。
パブリケーションでは、サブスクライバによるスナップショット処理の開始を許可する必要があります。詳細については、「パラメータ化されたフィルタを使用したマージ パブリケーションのスナップショット」を参照してください。
結合フィルタに関するその他の注意点
結合フィルタ階層では、重複するパーティションのアーティクルを、重複しないパーティションのアーティクルよりも上位にすることはできません。つまり、子アーティクルで重複しないパーティションを使用する場合、親アーティクルでも重複しないパーティションを使用する必要があります。結合フィルタの詳細については、「結合フィルタ」を参照してください。
重複しないパーティションを子に持つ結合フィルタでは、一意キーの結合プロパティを 1 に設定する必要があります。詳細については、「結合フィルタ」を参照してください。
アーティクルは、1 つのパラメータ化されたフィルタまたは結合フィルタのみを持っている必要があります。パラメータ化されたフィルタを持ち、かつ結合フィルタの親になることは可能です。パラメータ化されたフィルタを持ち、かつ結合フィルタの子になることはできません。複数の結合フィルタを持つことはできません。
パブリッシャの 2 つのテーブルに結合フィルタ リレーションシップがあり、子テーブルの行が親テーブルの行と対応していない場合、その行を親テーブルに挿入しても、関連する行はサブスクライバにダウンロードされません (行は重複するパーティションによってダウンロードされます)。たとえば、SalesOrderDetail テーブルの行に対応する行が SalesOrderHeader テーブル内に存在せず、その行を SalesOrderHeader に挿入した場合、その行はサブスクライバにダウンロードされますが、SalesOrderDetail 内の対応する行はダウンロードされません。