복제 시스템 저장 프로시저 개념

  • 아티클

SQL Server에서 복제 토폴로지의 사용자 구성 가능한 모든 기능에 대한 프로그래밍 방식 액세스는 시스템 저장 프로시저를 통해 제공됩니다. 저장 프로시저는 SQL Server Management Studio나 sqlcmd 명령줄 유틸리티를 사용하여 개별적으로 실행할 수 있지만 복제 태스크의 논리적 시퀀스를 수행하기 위해 실행할 수 있는 Transact-SQL 스크립트 파일을 작성하는 것이 효율적일 수 있습니다.

복제 태스크 스크립트를 작성하면 다음과 같은 이점이 있습니다.

  • 복제 토폴로지 배포에 사용한 단계의 영구 복사본을 유지할 수 있습니다.

  • 스크립트 하나로 여러 구독자를 구성할 수 있습니다.

  • 코드를 평가하고 이해하고 변경하거나 문제를 해결할 수 있게 하여 새로운 데이터베이스 관리자를 신속하게 교육할 수 있습니다.

    보안 정보보안 정보

    스크립트는 보안상 위험할 수 있습니다. 사용자 몰래 또는 사용자 개입 없이 시스템 함수를 호출할 수 있으며 보안 자격 증명을 일반 텍스트로 포함할 수도 있습니다. 스크립트를 사용하기 전에 스크립트에 보안 문제가 있는지 확인하십시오.

복제 스크립트 만들기

복제의 관점에서 스크립트는 각 문이 복제 저장 프로시저를 실행하는 일련의 Transact-SQL 문입니다. 스크립트는 sqlcmd 유틸리티를 사용하여 실행할 수 있는 텍스트 파일로, 주로 .sql 파일 확장명을 사용합니다. 스크립트 파일을 실행하면 sqlcmd 유틸리티는 파일에 저장된 SQL 문을 실행합니다. 마찬가지로 SQL Server Management Studio 프로젝트의 쿼리 개체로 스크립트를 저장할 수도 있습니다.

복제 스크립트는 다음과 같은 방법으로 만들 수 있습니다.

  • 스크립트를 직접 만듭니다.

  • 복제 마법사에 제공되는 스크립트 생성 기능을 사용하거나

  • SQL Server Management Studio. 자세한 내용은 복제 스크립팅을 참조하십시오.

  • RMO(복제 관리 개체)를 사용하여 RMO 개체를 만드는 스크립트를 프로그래밍 방식으로 생성합니다.

복제 스크립트를 직접 만들 경우에는 다음과 같은 사항을 고려해야 합니다.

  • Transact-SQL 스크립트에는 일괄 처리가 하나 이상 있습니다. GO 명령은 일괄 처리의 끝을 나타냅니다. Transact-SQL 스크립트에 GO 명령이 없으면 스크립트가 단일 일괄 처리로 실행됩니다.

  • 일괄 처리 하나에 복제 저장 프로시저를 여러 개 실행할 경우에는 첫 번째 프로시저 이후의 모든 프로시저 앞에 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'AdventureWorks2012'; 

-- 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 AdventureWorks2012 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로 저장하여 필요할 때 실행하거나 다시 실행할 수 있습니다.

위 스크립트에는 SQL Server 온라인 설명서의 여러 복제 코드 예제에 사용된 sqlcmd 스크립팅 변수가 포함되어 있습니다. 스크립팅 변수는 $(MyVariable) 구문을 사용하여 정의되며 변수 값은 명령줄이나 SQL Server Management Studio에서 스크립트에 전달할 수 있습니다. 자세한 내용은 이 항목의 다음 섹션인 "복제 스크립트 실행"을 참조하십시오.

복제 스크립트 실행

복제 스크립트를 만든 후에는 다음 방법 중 하나로 실행할 수 있습니다.

SQL Server Management Studio에서 SQL 쿼리 파일 만들기

SQL Server Management Studio 프로젝트에서 SQL 쿼리 파일로 복제 Transact-SQL 스크립트 파일을 만들 수 있습니다. 스크립트를 작성하면 이 쿼리 파일에 대한 데이터베이스에 연결하여 스크립트를 실행할 수 있습니다. SQL Server Management Studio를 사용하여 Transact-SQL 스크립트를 만드는 방법은 쿼리 및 텍스트 편집기(SQL Server Management Studio)을 참조하십시오.

스크립팅 변수가 포함된 스크립트를 사용하려면 SQL Server Management Studio가 sqlcmd 모드에서 실행 중이어야 합니다. sqlcmd 모드에서는 쿼리 편집기에서 sqlcmd와 관련된 추가적인 구문(예: 변수 값에 사용되는 :setvar)을 사용할 수 있습니다. sqlcmd 모드에 대한 자세한 내용은 쿼리 편집기로 SQLCMD 스크립트 편집을 참조하십시오. 다음 스크립트에서는 :setvar를 사용하여 $(DistPubServer) 변수의 값을 제공합니다.

: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=AdventureWorks2012
SET SubDb=AdventureWorks2012Replica
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\110\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)

개념

복제 프로그래밍 개념

복제 스크립팅