トランザクション レプリケーションにおけるパブリッシング ストアド プロシージャの実行
パブリッシャ側で実行され、パブリッシュされたテーブルに影響を与えるストアド プロシージャがある場合、それらのストアド プロシージャをストアド プロシージャ実行アーティクルとしてパブリケーションに含めることを検討してください。プロシージャの定義 (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
.gif "重要な注意事項") 重要 |
|---|
ストアド プロシージャのレプリケーションは、すべてのアプリケーションに適しているわけではありません。サブスクライバとは異なる行のセットがパブリッシャに存在するようにアーティクルが行方向でフィルタ選択される場合、両方で同じストアド プロシージャを実行すると異なる結果が返ります。同様に、レプリケートされていない別のテーブルのサブクエリに基づいた更新の場合、パブリッシャ側とサブスクライバ側の両方で同じストアド プロシージャを実行しても異なる結果が返ります。 |
ストアド プロシージャの実行をパブリッシュするには
SQL Server Management Studio : トランザクション パブリケーションでストアド プロシージャの実行をパブリッシュする方法 (SQL Server Management Studio)
レプリケーション Transact-SQL プログラミング : sp_addarticle (Transact-SQL) を実行し、パラメータ @type に対して「serializable proc exec」(推奨) または「proc exec」の値を指定します。アーティクルの定義の詳細については、「アーティクルを定義する方法 (レプリケーション Transact-SQL プログラミング)」を参照してください。
サブスクライバでのプロシージャの変更
既定では、パブリッシャ上のストアド プロシージャの定義は各サブスクライバに反映されます。ただし、サブスクライバでストアド プロシージャを変更することもできます。これは、パブリッシャとサブスクライバで異なるロジックを実行する場合に便利です。たとえば、2 つの関数を持つパブリッシャ上のストアド プロシージャ、sp_big_delete を考えてみます。このストアド プロシージャはレプリケートされたテーブル big_table1 から 100 万行を削除し、レプリケートされていないテーブル big_table2 を更新します。ネットワーク リソースの需要を削減するには、sp_big_delete をパブリッシュすることによって、100 万行の削除をストアド プロシージャとして反映する方が効率的です。サブスクライバでは、100 万行だけを削除し、big_table2 への更新を実行しないように sp_big_delete を変更できます。
.gif "注意") 注意 |
|---|
既定では、パブリッシャで ALTER PROCEDURE を使用して行われたすべての変更はサブスクライバに反映されます。これを防ぐには、ALTER PROCEDURE を実行する前に、スキーマの変更の反映を無効にしてください。スキーマの変更の詳細については、「パブリケーション データベースでのスキーマの変更」を参照してください。 |
ストアド プロシージャ実行アーティクルの種類
ストアド プロシージャの実行をパブリッシュするには、シリアル化可能なプロシージャ実行アーティクルとプロシージャ実行アーティクルの 2 種類の方法があります。
シリアル化可能オプションでは、プロシージャがシリアル化可能なトランザクションのコンテキスト内で実行される場合にのみ、プロシージャの実行がレプリケートされるため、このオプションをお勧めします。ストアド プロシージャがシリアル化可能なトランザクションの外から実行される場合、パブリッシュされたテーブルのデータに対する変更は一連の DML ステートメントとしてレプリケートされます。この動作により、サブスクライバ上のデータとパブリッシャ上のデータの一貫性が保たれます。これは、たとえば大規模なクリーンアップ操作などの、バッチ操作に特に便利です。
プロシージャ実行オプションを使用すると、ストアド プロシージャ内の個々のステートメントが成功したかどうかにかかわらず、すべてのサブスクライバに実行がレプリケートされる可能性があります。さらに、ストアド プロシージャによって生じるデータの変更は複数のトランザクションで発生する可能性があるので、サブスクライバ上のデータとパブリッシャ上のデータに一貫性がなくなる可能性があります。このような問題に対処するには、サブスクライバが読み取り専用であることと、READ UNCOMMITTED より高い分離レベルを使用していることが必要です。READ UNCOMMITTED を使用する場合、パブリッシュされたテーブルのデータに対する変更は一連の 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 パラメータを指定してください。これで、エラーが発生した場合でも、エージェントは引き続きサブスクライバに変更を適用できます。このパラメータの詳細については、「トランザクション レプリケーションでのエラーのスキップ」を参照してください。