パブリッシャーで実行され、パブリッシュされたテーブルに影響を与えるストアド プロシージャが 1 つ以上ある場合は、ストアド プロシージャ実行アーティクルとしてそれらのストアド プロシージャをパブリケーションに含めるかどうかを検討してください。 プロシージャの定義 (CREATE PROCEDURE ステートメント) は、サブスクリプションの初期化時にサブスクライバーにレプリケートされます。パブリッシャーでプロシージャが実行されると、レプリケーションはサブスクライバーで対応するプロシージャを実行します。 これにより、プロシージャの実行のみがレプリケートされ、各行の個々の変更をレプリケートする必要がないため、大規模なバッチ操作が実行される場合のパフォーマンスが大幅に向上する可能性があります。 たとえば、パブリケーション データベースに次のストアド プロシージャを作成するとします。
CREATE PROC give_raise AS
UPDATE EMPLOYEES SET salary = salary * 1.10
この手順により、社内の 10,000 人の従業員のそれぞれに 10% の給与が増加します。 パブリッシャーでこのストアド プロシージャを実行すると、各従業員の給与が更新されます。 ストアド プロシージャの実行のレプリケーションがないと、更新は大規模なマルチステップ トランザクションとしてサブスクライバーに送信されます。
BEGIN TRAN
UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = 'emp 1'
UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = 'emp 2'
また、これは 10,000 件の更新プログラムに対して繰り返されます。
ストアド プロシージャの実行のレプリケーションでは、ディストリビューション データベースにすべての更新を書き込んでから、ネットワーク経由でサブスクライバーに送信するのではなく、サブスクライバーでストアド プロシージャを実行するコマンドのみがレプリケーションによって送信されます。
EXEC give_raise
重要
ストアド プロシージャのレプリケーションは、すべてのアプリケーションに適しているわけではありません。 アーティクルが水平方向にフィルター処理され、パブリッシャーの行セットがサブスクライバーと異なる場合、両方で同じストアド プロシージャを実行すると、異なる結果が返されます。 同様に、更新が別のレプリケートされていないテーブルのサブクエリに基づいている場合、パブリッシャーとサブスクライバーの両方で同じストアド プロシージャを実行すると、異なる結果が返されます。
ストアド プロシージャの実行を発行するには
SQL Server Management Studio: トランザクション パブリケーションにストアド プロシージャの実行を発行する (SQL Server Management Studio)
レプリケーション Transact-SQL プログラミング: sp_addarticle (Transact-SQL) を実行し 、パラメーター @typeに 'serializable proc exec' (推奨) または 'proc exec' の値を指定します。 アーティクルの定義の詳細については、「アーティクルの 定義」を参照してください。
サブスクライバーでのプロシージャの変更
既定では、パブリッシャーのストアド プロシージャ定義は各サブスクライバーに反映されます。 ただし、サブスクライバーでストアド プロシージャを変更することもできます。 これは、パブリッシャーとサブスクライバーで異なるロジックを実行する場合に便利です。 たとえば、2 つの関数を持つパブリッシャーのストアド プロシージャsp_big_delete、レプリケートされたテーブル big_table1から 1,000,000 行を削除し、レプリケートされていないテーブル big_table2を更新するとします。 ネットワーク リソースに対する需要を減らすには、sp_big_deleteを発行して、100 万行の 削除をストアド プロシージャとして伝達する必要があります。 サブスクライバーでは、sp_big_delete を変更して100万行のみを削除し、後続のbig_table2への更新を実行しないようにできます。
注
既定では、パブリッシャーで ALTER PROCEDURE を使用して行われた変更はすべてサブスクライバーに反映されます。 これを防ぐには、ALTER PROCEDURE を実行する前にスキーマ変更の反映を無効にします。 スキーマの変更については、「 パブリケーション データベースでのスキーマの変更」を参照してください。
ストアド プロシージャ実行アーティクルの種類
ストアド プロシージャの実行をパブリッシュするには、シリアル化可能なプロシージャ実行アーティクルとプロシージャ実行アーティクルの 2 つの異なる方法があります。
シリアル化可能なオプションは、シリアル化可能なトランザクションのコンテキスト内でプロシージャが実行される場合にのみプロシージャの実行をレプリケートするため、推奨されます。 ストアド プロシージャがシリアル化可能なトランザクションの外部から実行される場合、パブリッシュされたテーブル内のデータへの変更は、一連の DML ステートメントとしてレプリケートされます。 この動作は、サブスクライバーのデータをパブリッシャーのデータと一致させるのに寄与します。 これは、大規模なクリーンアップ操作などのバッチ操作に特に役立ちます。
プロシージャ実行オプションを使用すると、ストアド プロシージャ内の個々のステートメントが成功したかどうかに関係なく、実行をすべてのサブスクライバーにレプリケートできます。 さらに、ストアド プロシージャによってデータに加えられた変更は複数のトランザクション内で発生する可能性があるため、サブスクライバーのデータがパブリッシャーのデータと一致しない可能性があります。 これらの問題に対処するには、サブスクライバーを読み取り専用にし、未コミット読み取りよりも厳しい分離レベルを使用する必要があります。 コミットされていない読み取りを使用する場合、パブリッシュされたテーブル内のデータに対する変更は、一連の DML ステートメントとしてレプリケートされます。
次の例は、シリアル化可能なプロシージャアーティクルとしてプロシージャのレプリケーションを設定することが推奨される理由を示しています。
BEGIN TRANSACTION T1
SELECT @var = max(col1) FROM tableA
UPDATE tableA SET col2 = <value>
WHERE col1 = @var
BEGIN TRANSACTION T2
INSERT tableA VALUES <values>
COMMIT TRANSACTION T2
前の例では、トランザクション T1 の SELECT は、トランザクション T2 の INSERT の前に行われることを前提としています。
プロシージャがシリアル化可能トランザクション内で実行されていない場合 (分離レベルが SERIALIZABLE に設定されている場合)、トランザクション T2 は T1 の SELECT ステートメントの範囲内に新しい行を挿入でき、T1 の前にコミットされます。 これは、T1 より前のサブスクライバーでも適用されることを意味します。 サブスクライバーで T1 が適用されると、SELECT はパブリッシャーとは異なる値を返す可能性があり、UPDATE とは異なる結果が生じる可能性があります。
プロシージャがシリアル化可能なトランザクション内で実行される場合、トランザクション T2 は、T2 の SELECT ステートメントでカバーされる範囲内に挿入することはできません。 T1 がコミットしてサブスクライバーで同じ結果が得られるまでブロックされます。
シリアル化可能なトランザクション内でプロシージャを実行するとロックが長く保持され、コンカレンシーが低下する可能性があります。
XACT_ABORT設定
ストアド プロシージャの実行をレプリケートする場合、ストアド プロシージャを実行するセッションの設定で、XACT_ABORT ON を指定する必要があります。 XACT_ABORTが OFF に設定されていて、パブリッシャーでのプロシージャの実行中にエラーが発生した場合、サブスクライバーでも同じエラーが発生し、ディストリビューション エージェントが失敗します。 XACT_ABORT ON を指定すると、パブリッシャーでの実行中に発生したエラーによって実行全体がロールバックされ、ディストリビューション エージェントのエラーが回避されます。 XACT_ABORTの設定の詳細については、「 SET XACT_ABORT (Transact-SQL)」を参照してください。
XACT_ABORT OFF の設定が必要な場合は、ディストリビューション エージェントの -SkipErrors パラメーターを指定します。 これにより、エラーが発生した場合でも、エージェントはサブスクライバーで変更を適用し続けられます。