システム ストアド プロシージャを使用したレプリケーションのプログラミング

更新 : 2006 年 7 月 17 日

SQL Server は、レプリケーション トポロジでユーザーが構成可能なすべての機能に、システム ストアド プロシージャを使ってプログラムからアクセスできます。ストアド プロシージャは、SQL Server Management Studio や sqlcmd コマンド ライン ユーティリティを使って個別に実行することもできますが、一連のレプリケーション タスクを実行する Transact-SQL スクリプト ファイルを作成することで、その利点を最大限に活かすことができます。

レプリケーション タスクをスクリプト化することの利点を次に示します。

• レプリケーション トポロジを配置するために必要な手順を 1 つにまとめて再利用できる。
• 単一のスクリプトを使って複数のサブスクライバを構成できる。
• 新人のデータベース管理者がコードを見て処理の内容を理解し、変更やトラブルシューティングに備えることで、迅速に仕事を覚えることができる。

  セキュリティ メモ :
  スクリプトは、セキュリティ脆弱性の原因となる可能性があります。ユーザーが知らないうちに、またはユーザーが操作しなくても、スクリプトによってシステムの機能を実行することができます。また、セキュリティ資格情報が平文で含まれている場合もあります。スクリプトを使用する前に、セキュリティの問題がないかどうかを確認してください。

レプリケーション スクリプトの作成

レプリケーションの観点から言えば、スクリプトは、それぞれがレプリケーションのストアド プロシージャを実行する、1 つまたは複数の Transact-SQL ステートメントの集合です。スクリプトは、sqlcmd ユーティリティを使って実行できるテキスト ファイルで、一般に、.sql という拡張子が使用されます。スクリプト ファイルを実行すると、そこに格納された SQL ステートメントが sqlcmd ユーティリティによって実行されます。同様に、スクリプトをクエリ オブジェクトとして SQL Server Management Studio プロジェクトに格納することもできます。

レプリケーション スクリプトは、以下のような方法で作成できます。

• スクリプトを手動で作成する。
• レプリケーション ウィザードのスクリプト生成機能を使用する。または
• SQL Server Management Studio を使用する。詳細については、「レプリケーション オブジェクトのスクリプトを作成する方法 (SQL Server Management Studio)」を参照してください。
• レプリケーション管理オブジェクト (RMO) を使用し、RMO オブジェクトを作成するためのスクリプトをプログラムから生成する。

レプリケーション スクリプトを手動で作成する場合は、次の点に注意してください。

• Transact-SQL スクリプトは 1 つ以上のバッチを含んでいます。GO コマンドによってバッチの終了を示します。Transact-SQL スクリプトに GO ステートメントがない場合は、1 つのバッチとして実行されます。
• 1 つのバッチで複数のレプリケーション ストアド プロシージャを実行する場合は、そのバッチの中で、最初のプロシージャの後に続くすべてのプロシージャの先頭に EXECUTE キーワードを指定する必要があります。
• バッチに含まれるすべてのストアド プロシージャは、バッチの実行前にコンパイルする必要があります。ただし、バッチがコンパイルされ、実行プランが作成された後で、ランタイム エラーが発生する可能性もあります。
• レプリケーションの構成スクリプトを作成する場合は、セキュリティ資格情報をスクリプト ファイルに格納しなくて済むように、Windows 認証の使用をお勧めします。スクリプト ファイルに資格情報を格納する必要がある場合、不正なアクセスを防ぐために、ファイルをセキュリティ保護する必要があります。

サンプル レプリケーション スクリプト

次のスクリプトを実行すると、サーバー上にパブリッシングとディストリビューションをセットアップできます。

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- Install the Distributor and the distribution database.
DECLARE @distributor AS sysname;
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @directory AS nvarchar(500);
DECLARE @publicationDB AS sysname;
-- Specify the Distributor name.
SET @distributor = $(DistPubServer);
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);
-- Specify the replication working directory.
SET @directory = N'\\' + $(DistPubServer) + '\repldata';
-- Specify the publication database.
SET @publicationDB = N'AdventureWorks'; 

-- Install the server MYDISTPUB as a Distributor using the defaults,
-- including autogenerating the distributor password.
USE master
EXEC sp_adddistributor @distributor = @distributor;

-- Create a new distribution database using the defaults, including
-- using Windows Authentication.
USE master
EXEC sp_adddistributiondb @database = @distributionDB, 
    @security_mode = 1;
GO

-- Create a Publisher and enable AdventureWorks for replication.
-- Add MYDISTPUB as a publisher with MYDISTPUB as a local distributor
-- and use Windows Authentication.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);

USE [distribution]
EXEC sp_adddistpublisher @publisher=@publisher, 
    @distribution_db=@distributionDB, 
    @security_mode = 1;
GO 

その後、このスクリプトを instdistpub.sql としてローカルに保存しておけば必要に応じていつでも再実行できます。

以前のスクリプトには sqlcmd スクリプト変数が含まれており、この変数は SQL Server Books Online のレプリケーション コード サンプルの多くで使用されています。スクリプト変数は $(MyVariable) 構文を使用して定義します。変数の値は、コマンド ラインまたは SQL Server Management Studio でスクリプトに渡すことができます。詳細については、このトピックの次のセクション「レプリケーション スクリプトの実行」を参照してください。

レプリケーション スクリプトの実行

作成したレプリケーション スクリプトは、次のいずれかの方法で実行できます。

SQL Server Management Studio を使った SQL クエリ ファイルの作成

レプリケーション Transact-SQL スクリプト ファイルは、SQL Server Management Studio プロジェクトで SQL クエリ ファイルとして作成できます。スクリプトを作成した後、このクエリ ファイルが格納されたデータベースに接続することによってスクリプトを実行できます。SQL Server Management Studio を使用した Transact-SQL スクリプトの作成方法の詳細については、「SQL Server Management Studio によるスクリプトの作成、分析、編集」を参照してください。

スクリプト変数を含むスクリプトを使用するには、SQL Server Management Studio を sqlcmd モードで実行する必要があります。sqlcmd モードでは、変数の値として使用される :setvar などの sqlcmd に固有の追加の構文を Query Editor で使用できます。sqlcmd モードの詳細については、「クエリ エディタによる SQLCMD スクリプトの編集」を参照してください。次のスクリプトでは、$(DistPubServer) 変数の値の指定に :setvar を使用しています。

:setvar DistPubServer N'MyPublisherAndDistributor';

-- Install the Distributor and the distribution database.
DECLARE @distributor AS sysname;
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @directory AS nvarchar(500);
DECLARE @publicationDB AS sysname;
-- Specify the Distributor name.
SET @distributor = $(DistPubServer);
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);

--
-- Additional code goes here
--

コマンド ラインからの sqlcmd ユーティリティの使用

次の例に、sqlcmd ユーティリティを使用してコマンド ラインから instdistpub.sql スクリプト ファイルを実行する方法を示します。

sqlcmd.exe -E -S sqlserverinstance -i C:\instdistpub.sql -o C:\output.log -v DistPubServer="N'MyDistributorAndPublisher'"

この例の -E スイッチは、SQL Server との接続に Windows 認証を使用することを指定しています。Windows 認証を使用した場合、ユーザー名やパスワードをスクリプト ファイルに格納する必要はありません。スクリプト ファイルの名前とパスは -i スイッチで指定し、出力ファイルの名前は -o スイッチで指定します (このスイッチを使用した場合、SQL Server からの出力はコンソールでなくこのファイルに書き込まれます)。sqlcmd ユーティリティの -v スイッチを使用すると、実行時にスクリプト変数を Transact-SQL スクリプトに渡すことができます。この例では、sqlcmd によって、スクリプト中に出現するすべての $(DistPubServer) のインスタンスが、実行前に N'MyDistributorAndPublisher' という値に置き換えられます。

メモ :
-X スイッチを指定すると、スクリプト変数が無効化されます。

バッチ ファイルを使用したタスクの自動化

バッチ ファイルを使用すると、レプリケーションの管理や同期などのタスクを同じバッチ ファイルにまとめることによって自動化できます。次のバッチ ファイルでは、sqlcmd ユーティリティを使用して、サブスクリプション データベースの削除と再作成を行い、マージ プル サブスクリプションを追加します。その後、マージ エージェントを呼び出して、新しいサブスクリプションを同期しています。

REM ----------------------Script to synchronize merge subscription ----------------------
REM -- Creates subscription database and 
REM -- synchronizes the subscription to MergeSalesPerson.
REM -- Current computer acts as both Publisher and Subscriber.
REM -------------------------------------------------------------------------------------

SET Publisher=%computername%
SET Subscriber=%computername%
SET PubDb=AdventureWorks
SET SubDb=AdventureWorksReplica
SET PubName=AdvWorksSalesOrdersMerge

REM -- Drop and recreate the subscription database at the Subscriber
sqlcmd /S%Subscriber% /E /Q"USE master IF EXISTS (SELECT * FROM sysdatabases WHERE name='%SubDb%' ) DROP DATABASE %SubDb%"
sqlcmd /S%Subscriber% /E /Q"USE master CREATE DATABASE %SubDb%"

REM -- Add a pull subscription at the Subscriber
sqlcmd /S%Subscriber% /E /Q"USE %SubDb% EXEC sp_addmergepullsubscription @publisher = %Publisher%, @publication = %PubName%, @publisher_db = %PubDb%"
sqlcmd /S%Subscriber% /E /Q"USE %SubDb%  EXEC sp_addmergepullsubscription_agent @publisher = %Publisher%, @publisher_db = %PubDb%, @publication = %PubName%, @subscriber = %Subscriber%, @subscriber_db = %SubDb%, @distributor = %Publisher%"

REM -- This batch file starts the merge agent at the Subscriber to 
REM -- synchronize a pull subscription to a merge publication.
REM -- The following must be supplied on one line.
"\Program Files\Microsoft SQL Server\90\COM\REPLMERG.EXE"  -Publisher  %Publisher% -Subscriber  %Subscriber%  -Distributor %Publisher%  -PublisherDB  %PubDb% -SubscriberDB %SubDb% -Publication %PubName% -PublisherSecurityMode 1 -OutputVerboseLevel 1  -Output  -SubscriberSecurityMode 1  -SubscriptionType 1 -DistributorSecurityMode 1 -Validate 3

一般的なレプリケーション タスクのスクリプト化

次に、システム ストアド プロシージャを使ってスクリプト化することのできる、最も一般的なレプリケーション タスクをいくつか紹介します。

• パブリッシングおよびディストリビューションを構成する。
• パブリッシャとディストリビュータのプロパティを変更する。
• パブリッシングおよびディストリビューションを無効化する。
• パブリケーションを作成し、アーティクルを定義する。
• パブリケーションおよびアーティクルを削除する。
• プル サブスクリプションを作成する。
• プル サブスクリプションを変更する。
• プル サブスクリプションを削除する。
• プッシュ サブスクリプションを作成する。
• プッシュ サブスクリプションを変更する。
• プッシュ サブスクリプションを削除する。
• プル サブスクリプションを同期する。

変更履歴

リリース	履歴
2006 年 7 月 17 日	新しい内容 : sqlcmd と sqlcmd モードの SQL Server Management Studio と共にスクリプト変数を使用する方法についての説明を追加しました。

参照

概念

レプリケーションのプログラミング計画

その他の技術情報

レプリケーション ストアド プロシージャ (Transact-SQL)
レプリケーションのスクリプト作成

ヘルプおよび情報

SQL Server 2005 の参考資料の入手