Replication System Stored Procedures Concepts

  • [アーティクル]

適用対象:SQL ServerAzure SQL Managed Instance

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. 詳細については、「レプリケーションのスクリプト作成」を参照してください。

  • レプリケーション管理オブジェクト (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'AdventureWorks2022';   
  
-- 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 AdventureWorks2022 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 を使用すると、 スイッチを使用して、実行時にスクリプト変数を Transact-SQL スクリプトに -v 渡すことができます。 この例では、sqlcmd によって、スクリプト中に出現するすべての $(DistPubServer) のインスタンスが、実行前に N'MyDistributorAndPublisher' という値に置き換えられます。

Note

-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\130\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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

参照

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