次の方法で共有

  • [アーティクル]

レプリケーション システム ストアド プロシージャの概念

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

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

  • レプリケーション トポロジを配置するために必要な手順を 1 つにまとめて再利用できる。

  • 単一のスクリプトを使って複数のサブスクライバを構成できる。

  • 新人のデータベース管理者がコードを見て処理の内容を理解し、変更やトラブルシューティングに備えることで、迅速に仕事を覚えることができる。

    セキュリティに関する注意セキュリティに関する注意

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

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

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

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

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

  • 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 オンライン ブックのレプリケーション コード サンプルの多くで使用されています。スクリプト変数は $(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\100\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

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

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

  • パブリッシングおよびディストリビューションを構成する。

  • パブリッシャとディストリビュータのプロパティを変更する。

  • パブリッシングおよびディストリビューションを無効化する。

  • パブリケーションを作成し、アーティクルを定義する。

  • パブリケーションおよびアーティクルを削除する。

  • プル サブスクリプションを作成する。

  • プル サブスクリプションを変更する。

  • プル サブスクリプションを削除する。

  • プッシュ サブスクリプションを作成する。

  • プッシュ サブスクリプションを変更する。

  • プッシュ サブスクリプションを削除する。

  • プル サブスクリプションを同期する。