복제 스크립트 업그레이드(복제 Transact-SQL 프로그래밍)Upgrade Replication Scripts (Replication Transact-SQL Programming)

이 항목 적용 대상: 예SQL Server없습니다Azure SQL 데이터베이스없습니다Azure SQL 데이터 웨어하우스 없습니다 병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse Transact-SQLTransact-SQL 스크립트 파일을 사용하여 복제 토폴로지를 프로그래밍 방식으로 구성할 수 있습니다. Transact-SQLTransact-SQL script files can be used to programmatically configure a replication topology. 자세한 내용은 복제 시스템 저장 프로시저 개념을 참조하세요.For more information, see Replication System Stored Procedures Concepts.

중요

sysadmin 역할의 멤버에 의해 실행되는 스크립트는 반드시 업그레이드할 필요는 없지만 이 항목에서 설명하는 대로 기존 스크립트를 수정하는 것이 좋습니다.Although you are not required to upgrade scripts that are executed by members of the sysadmin role, we recommend that you modify existing scripts as described in this topic. Replication Agent Security Model항목의 "에이전트에 필요한 사용 권한" 섹션에서 설명하는 대로 각 복제 에이전트에 대해 최소 사용 권한이 있는 계정을 지정합니다.Specify an account that has minimum permissions for each replication agent as described in the "Permissions Required By Agents" section of the topic Replication Agent Security Model.

이러한 향상된 보안 기능은 복제 에이전트 작업이 실행되는 MicrosoftMicrosoft Windows 계정을 명시적으로 지정할 수 있도록 함으로써 사용 권한을 보다 잘 제어할 수 있게 해 주며, 기존 스크립트의 다음 저장 프로시저에 영향을 줍니다.These security improvements, which enable more control over permissions by allowing you to explicitly specify the MicrosoftMicrosoft Windows accounts under which replication agent jobs are executed, affect the following stored procedures in existing scripts:

  • sp_addpublication_snapshot:sp_addpublication_snapshot:

    이제 sp_addpublication_snapshot(Transact-SQL)을 실행하여 배포자에서 스냅숏 에이전트가 실행되는 작업을 만들 때 Windows 자격 증명을 @job_login@job_password로 제공해야 합니다.You should now supply the Windows credentials as @job_login and @job_password when executing sp_addpublication_snapshot (Transact-SQL) to create the job under which the Snapshot Agent runs at the Distributor.

  • sp_addpushsubscription_agent:sp_addpushsubscription_agent:

    이제 sp_addpushsubscription_agent(Transact-SQL)를 실행하여 명시적으로 작업을 추가하고 배포자에서 배포 에이전트 작업이 실행되는 Windows 자격 증명(@job_login@job_password)을 제공해야 합니다.You should now execute sp_addpushsubscription_agent (Transact-SQL) to explicitly add a job and supply the Windows credentials (@job_login and @job_password) under which the Distribution Agent job runs at the Distributor. SQL ServerSQL Server 이전 버전의 SQL Server 2005SQL Server 2005에서 이 작업은 밀어넣기 구독이 만들어질 때 자동으로 수행되었습니다.In versions of SQL ServerSQL Server before SQL Server 2005SQL Server 2005, this was done automatically when a push subscription was created.

  • sp_addmergepushsubscription_agent:sp_addmergepushsubscription_agent:

    이제 sp_addmergepushsubscription_agent(Transact-SQL)를 실행하여 명시적으로 작업을 추가하고 배포자에서 병합 에이전트 작업이 실행되는 Windows 자격 증명(@job_login@job_password)을 제공해야 합니다.You should now execute sp_addmergepushsubscription_agent (Transact-SQL) to explicitly add a job and supply the Windows credentials (@job_login and @job_password) under which the Merge Agent job runs at the Distributor. SQL ServerSQL Server 이전 버전의 SQL Server 2005SQL Server 2005에서 이 작업은 밀어넣기 구독이 만들어질 때 자동으로 수행되었습니다.In versions of SQL ServerSQL Server before SQL Server 2005SQL Server 2005, this was done automatically when a push subscription was created.

  • sp_addpullsubscription_agent:sp_addpullsubscription_agent:

    이제 sp_addpullsubscription_agent(Transact-SQL)를 실행하여 구독자에서 배포 에이전트가 실행되는 작업을 만들 때 Windows 자격 증명을 @job_login@job_password로 제공해야 합니다.You should now supply the Windows credentials as @job_login and @job_password when executing sp_addpullsubscription_agent (Transact-SQL) to create the job under which the Distribution Agent runs at the Subscriber.

  • sp_addmergepullsubscription_agent:sp_addmergepullsubscription_agent:

    이제 sp_addmergepullsubscription_agent(Transact-SQL)를 실행하여 구독자에서 병합 에이전트가 실행되는 작업을 만들 때 Windows 자격 증명을 @job_login@job_password로 제공해야 합니다.You should now supply the Windows credentials as @job_login and @job_password when executing sp_addmergepullsubscription_agent (Transact-SQL) to create the job under which the Merge Agent runs at the Subscriber.

  • sp_addlogreader_agent:sp_addlogreader_agent:

    이제 sp_addlogreader_agent(Transact-SQL)를 실행하여 수동으로 작업을 추가하고 배포자에서 로그 판독기 에이전트가 실행되는 Windows 자격 증명을 지정해야 합니다.You should now execute sp_addlogreader_agent (Transact-SQL) to manually add the job and supply the Windows credentials under which the Log Reader Agent runs at the Distributor. SQL ServerSQL Server 이전 버전의 SQL Server 2005SQL Server 2005에서 이 작업은 트랜잭션 게시가 만들어질 때 자동으로 수행되었습니다.In versions of SQL ServerSQL Server before SQL Server 2005SQL Server 2005, this was done automatically when a transactional publication was created.

  • sp_addqreader_agent:sp_addqreader_agent:

    이제 sp_addqreader_agent(Transact-SQL)를 실행하여 수동으로 작업을 추가하고 배포자에서 큐 판독기 에이전트가 실행되는 Windows 자격 증명을 지정해야 합니다.You should now execute sp_addqreader_agent (Transact-SQL) to manually add the job and supply the Windows credentials under which the Queue Reader Agent runs at the Distributor. SQL ServerSQL Server 이전 버전의 SQL Server 2005SQL Server 2005에서 이 작업은 지연 업데이트를 지원하는 트랜잭션 게시가 만들어질 때 자동으로 수행되었습니다.In versions of SQL ServerSQL Server before SQL Server 2005SQL Server 2005, this was done automatically when a transactional publication that supported queued updating was created.

    SQL Server 2005SQL Server 2005에 도입된 보안 모델에서 복제 에이전트는 항상 @job_name@job_password에 지정된 자격 증명을 사용하는 Windows 인증을 통해 SQL ServerSQL Server의 로컬 인스턴스에 연결합니다.In the security model introduced in SQL Server 2005SQL Server 2005, replication agents always make connections to the local instance of SQL ServerSQL Server with Windows Authentication using the credentials supplied in @job_name and @job_password. 복제 에이전트 작업을 실행할 때 사용되는 Windows 계정의 요구 사항에 대한 자세한 내용은 Replication Agent Security Model을 참조하십시오.For information about the requirements of Windows accounts used when running replication agent jobs, see Replication Agent Security Model.

중요

가능한 경우 런타임 시 사용자에게 보안 자격 증명을 입력하라는 메시지가 표시됩니다.When possible, prompt users to enter security credentials at runtime. 스크립트 파일에 자격 증명을 저장하는 경우에는 파일 자체에 보안이 설정되도록 합니다.If you store credentials in a script file, ensure that the file itself is secured.

스냅숏 또는 트랜잭션 게시를 구성하는 스크립트를 업그레이드하려면To upgrade scripts that configure a snapshot or transactional publication

  1. 기존 스크립트의 sp_addpublication(Transact-SQL) 전에 게시 데이터베이스의 게시자에서 sp_addlogreader_agent(Transact-SQL)를 실행합니다.In the existing script, before sp_addpublication (Transact-SQL), execute sp_addlogreader_agent (Transact-SQL) at the Publisher on the publication database. @job_name@job_password에 로그 판독기 에이전트가 실행되는 Windows 자격 증명을 지정합니다.Specify the Windows credentials under which the Log Reader Agent runs for @job_name and @job_password. 게시자에 연결할 때 에이전트가 SQL ServerSQL Server 인증을 사용하면 @publisher_security_mode 에 대해 값 @publisher_security_mode 을 지정하고 SQL ServerSQL Server @publisher_login @publisher_login @job_login @publisher_password을 참조하세요.If the agent will use SQL ServerSQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the SQL ServerSQL Server login information for @publisher_login and @publisher_password. 이렇게 하면 게시 데이터베이스에 대한 로그 판독기 에이전트가 만들어집니다.This creates a Log Reader Agent job for the publication database.

    참고

    이 단계는 트랜잭션 게시 전용이며 스냅숏 게시에는 필요하지 않습니다.This step is only for transactional publications and is not required for snapshot publications.

  2. (옵션) sp_addpublication(Transact-SQL) 전에 배포 데이터베이스의 배포자에서 sp_addqreader_agent(Transact-SQL)를 실행합니다.(Optional) Before sp_addpublication (Transact-SQL), execute sp_addqreader_agent (Transact-SQL) at the Distributor on the distribution database. @job_name@job_password에 큐 판독기 에이전트가 실행되는 Windows 자격 증명을 지정합니다.Specify the Windows credentials under which the Queue Reader Agent runs for @job_name and @job_password. 이렇게 하면 배포자에 대한 큐 판독기 에이전트가 만들어집니다.This creates a Queue Reader Agent job for the Distributor.

    참고

    이 단계는 지연 업데이트 구독자를 지원하는 트랜잭션 게시에만 필요합니다.This step is only required for transactional publications that support queued updating subscribers.

  3. (옵션) 새 복제 기능을 구현하는 매개 변수에 대해 기본값이 아닌 값을 설정하려면 sp_addpublication(Transact-SQL) 실행을 업데이트합니다.(Optional) Update the execution of sp_addpublication (Transact-SQL) to set any non-default values for parameters that implement new replication functionalities.

  4. sp_addpublication(Transact-SQL) 다음에 게시 데이터베이스의 게시자에서 sp_addpublication_snapshot(Transact-SQL)을 실행합니다.After sp_addpublication (Transact-SQL), execute sp_addpublication_snapshot (Transact-SQL) at the Publisher on the publication database. 스냅숏 에이전트가 @job_name@job_password에 대해 실행되는 @publication 및 Windows 자격 증명을 지정합니다.Specify @publication and the Windows credentials under which the Snapshot Agent runs for @job_name and @job_password. 게시자에 연결할 때 에이전트가 SQL ServerSQL Server 인증을 사용하면 @publisher_security_mode 에 대해 값 @publisher_security_mode 을 지정하고 SQL ServerSQL Server @publisher_login @publisher_login @job_login @publisher_password을 참조하세요.If the agent will use SQL ServerSQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the SQL ServerSQL Server login information for @publisher_login and @publisher_password. 이렇게 하면 게시에 대해 스냅숏 에이전트 작업이 만들어집니다.This creates a Snapshot Agent job for the publication.

  5. (옵션) 새 복제 기능을 구현하는 매개 변수에 대해 기본값이 아닌 값을 설정하려면 sp_addarticle(Transact-SQL) 실행을 업데이트합니다.(Optional) Update the execution of sp_addarticle (Transact-SQL) to set any non-default values for parameters that implement new replication functionalities.

스냅숏 또는 트랜잭션 게시에 구독을 추가하는 스크립트를 업그레이드하려면To upgrade scripts that add subscriptions to a snapshot or transactional publication

  1. 구독을 만드는 저장 프로시저를 실행한 후 구독을 동기화할 구독 에이전트 작업을 만드는 저장 프로시저를 실행합니다.After executing the stored procedure that creates the subscription, ensure that you execute the stored procedure that creates a Distribution Agent job to synchronize the subscription. 사용하는 저장 프로시저는 구독 유형에 따라 달라집니다.The stored procedure that you use will depend on the type of subscription.

병합 게시를 구성하는 스크립트를 업그레이드하려면To upgrade scripts that configure a merge publication

  1. (옵션) 기존 스크립트에서 새 복제 기능을 구현하는 매개 변수에 대해 기본값이 아닌 값을 설정하려면 sp_addmergepublication(Transact-SQL) 실행을 업데이트합니다.(Optional) In the existing script, update the execution of sp_addmergepublication (Transact-SQL) to set any non-default values for parameters that implement new replication functionalities.

  2. sp_addmergepublication(Transact-SQL) 다음에 게시 데이터베이스의 게시자에서 sp_addpublication_snapshot(Transact-SQL)을 실행합니다.After sp_addmergepublication (Transact-SQL), execute sp_addpublication_snapshot (Transact-SQL) at the Publisher on the publication database. 스냅숏 에이전트가 @job_name@job_password에 대해 실행되는 @publication 및 Windows 자격 증명을 지정합니다.Specify @publication and the Windows credentials under which the Snapshot Agent runs for @job_name and @job_password. 게시자에 연결할 때 에이전트가 SQL ServerSQL Server 인증을 사용하면 @publisher_security_mode 에 대해 값 @publisher_security_mode 을 지정하고 SQL ServerSQL Server @publisher_login @publisher_login @job_login @publisher_password을 참조하세요.If the agent will use SQL ServerSQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the SQL ServerSQL Server login information for @publisher_login and @publisher_password. 이렇게 하면 게시에 대해 스냅숏 에이전트 작업이 만들어집니다.This creates a Snapshot Agent job for the publication.

  3. (옵션) 새 복제 기능을 구현하는 매개 변수에 대해 기본값이 아닌 값을 설정하려면 sp_addmergearticle(Transact-SQL) 실행을 업데이트합니다.(Optional) Update the execution of sp_addmergearticle (Transact-SQL) to set any non-default values for parameters that implement new replication functionalities.

병합 게시에 구독을 추가하는 스크립트를 업그레이드하려면To upgrade scripts that add subscriptions to a merge publication

  1. 구독을 만드는 저장 프로시저를 실행한 후 구독을 동기화할 병합 에이전트를 만드는 저장 프로시저를 실행합니다.After executing the stored procedure that creates the subscription, ensure that you execute the stored procedure that creates a Merge Agent job to synchronize the subscription. 사용하는 저장 프로시저는 구독 유형에 따라 달라집니다.The stored procedure that you use will depend on the type of subscription.

예제Example

다음은 Product 테이블의 트랜잭션 게시를 만드는 SQL Server 2000SQL Server 2000 스크립트의 예제입니다.The following is an example of a SQL Server 2000SQL Server 2000 script that creates a transactional publication for the Product table. 이 게시에서는 지연 업데이트를 장애 조치로 사용하는 즉시 업데이트를 지원합니다.This publication supports immediate updating with queued updating as failover. 읽기 쉽도록 기본 매개 변수가 삭제되었습니다.Default parameters have been removed for readability.

USE [Northwind]
GO

DECLARE @publication AS sysname
DECLARE @publicationDB AS sysname
DECLARE @article AS sysname
SET @publication = N'NwdProductTran'
SET @publicationDB = N'Northwind'
SET @article = N'Products'

-- Enable the replication database.
EXEC sp_replicationdboption 
    @dbname = @publicationDB, 
    @optname = N'publish', 
    @value = N'true'

-- Add the transactional publication.
EXEC sp_addpublication 
    @publication = @publication, 
    @sync_method = N'native', 
    @status = N'active', 
    @repl_freq = N'continuous', 
    @description = N'Transactional publication of Northwind.', 
    @allow_push = N'true', 
    @allow_pull = N'true', 
    @allow_sync_tran = N'true', 
    @autogen_sync_procs = N'true', 
    @allow_queued_tran = N'true'

-- Add a snapshot job.
EXEC sp_addpublication_snapshot 
    @publication = @publication

-- Add the transactional articles.
EXEC sp_addarticle 
    @publication = @publication, 
    @article = @article, 
    @source_owner = N'dbo', 
    @source_object = @article, 
    @destination_table = @article, 
    @type = N'logbased', 
    @schema_option = 0x00000000000080F3, 
    @ins_cmd = N'CALL sp_MSins_Products', 
    @del_cmd = N'XCALL sp_MSdel_Products', 
    @upd_cmd = N'XCALL sp_MSupd_Products', 
    @auto_identity_range = N'false'
GO

예제Example

다음은 트랜잭션 게시를 만드는 앞의 스크립트를 SQL Server 2005SQL Server 2005 이상 버전에서 성공적으로 실행되도록 업그레이드하는 예제입니다.The following is an example of upgrading the previous script, which creates a transactional publication, to run successfully for SQL Server 2005SQL Server 2005 and later versions. 이 게시에서는 지연 업데이트를 장애 조치로 사용하는 즉시 업데이트를 지원합니다.This publication supports immediate updating with queued updating as failover. 새 매개 변수의 기본값이 명시적으로 선언되었습니다.Defaults for new parameters have been explicitly declared.

참고

Windows 자격 증명은 sqlcmd 스크립팅 변수를 사용하여 런타임에 제공됩니다.Windows credentials are supplied at runtime using sqlcmd scripting variables.

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. 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".

-- Execute at the Distributor.
USE [distribution]

DECLARE @login AS sysname;
DECLARE @password AS sysname;
-- Specify the Windows account to run the Queue Reader Agent.
SET @login = $(Login); 
-- Pass the password at runtime.
SET @password = $(Password); 

-- Execute sp_addqreader_agent to create the Queue Reader Agent job. 
EXEC sp_addqreader_agent 
	@job_login = @login, 
	@job_password = @password;
GO

-- Execute at the Publisher.
USE [Northwind]
GO

DECLARE @publication AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @article AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publication = N'NwdProductTran';
SET @publicationDB = N'Northwind';
SET @article = N'Products';
-- Specify the Windows account to run the Log Reader and Snapshot Agents.
SET @login = $(Login); 
-- Pass the password at runtime.
SET @password = $(Password); 

-- Enable the replication database.
EXEC sp_replicationdboption 
	@dbname = @publicationDB, 
	@optname = N'publish', 
	@value = N'true';

-- Execute sp_addlogreader_agent to create the agent job. 
EXEC sp_addlogreader_agent 
	@job_login = @login, 
	@job_password = @password, 
	-- Explicitly specify the use of Windows Integrated Authentication (default) 
	-- when connecting to the Publisher.
	@publisher_security_mode = 1;

-- Add the transactional publication.
EXEC sp_addpublication 
	@publication = @publication, 
	@sync_method = N'native', 
	@repl_freq = N'continuous', 
	@status = N'active',
	@description = N'Transactional publication of Northwind.', 
	@allow_push = N'true', 
	@allow_pull = N'true', 
	@allow_sync_tran = N'true', 
	@autogen_sync_procs = N'true', 
	@allow_queued_tran = N'true',
	@replicate_ddl = 1,
	@enabled_for_p2p = N'false';

-- Create a new snapshot job for the publication, using a default schedule.
EXEC sp_addpublication_snapshot 
	@publication = @publication, 
	@job_login = @login, 
	@job_password = @password,
	-- Explicitly specify the use of Windows Integrated Authentication (default) 
	-- when connecting to the Publisher.
	@publisher_security_mode = 1;

-- Add a transactional article.
EXEC sp_addarticle 
  @publication = @publication, 
  @article = @article, 
  @source_owner = N'dbo', 
  @source_object = @article, 
  @destination_table = @article, 
  @type = N'logbased', 
  @schema_option = 0x00000000000080F3, 
  @ins_cmd = N'CALL sp_MSins_Products', 
  @del_cmd = N'XCALL sp_MSdel_Products', 
  @upd_cmd = N'XCALL sp_MSupd_Products', 
  @auto_identity_range = N'false',
  @identityrangemanagementoption = N'manual',
  @fire_triggers_on_snapshot = N'false';
GO

예제Example

다음은 Customers 테이블의 병합 게시를 만드는 SQL Server 2000SQL Server 2000 스크립트의 예제입니다.The following is an example of a SQL Server 2000SQL Server 2000 script that creates a merge publication for the Customers table. 읽기 쉽도록 기본 매개 변수가 삭제되었습니다.Default parameters have been removed for readability.

-- Enable the replication database.
USE [Northwind]
GO

DECLARE @publicationDB AS sysname
DECLARE @publication AS sysname
DECLARE @article AS sysname
SET @publicationDB = N'Northwind' 
SET @publication = N'NwdCustomersMerge' 
SET @article = N'Customers' 

EXEC sp_replicationdboption 
    @dbname = @publicationDB, 
    @optname = N'merge publish', 
    @value = N'true'

-- Add the merge publication.
EXEC sp_addmergepublication 
    @publication = @publication, 
    @description = N'Merge publication of Northwind.', 
    @retention = 14, 
    @sync_mode = N'native', 
    @centralized_conflicts = N'true', 
    @dynamic_filters = N'false', 
    @keep_partition_changes = N'false'
 
EXEC sp_addpublication_snapshot 
    @publication = @publication

-- Add the merge articles.
EXEC sp_addmergearticle 
    @publication = @publication, 
    @article = @article, 
    @source_owner = N'dbo', 
    @source_object = @article, 
    @type = N'table', 
    @description = null, 
    @column_tracking = N'true', 
    @schema_option = 0x000000000000CFF1
 GO

예제Example

다음은 병합 게시를 만드는 앞의 스크립트를 SQL Server 2005SQL Server 2005 이상 버전에서 성공적으로 실행되도록 업그레이드하는 예제입니다.The following is an example of the previous script, which creates a merge publication, upgraded to run successfully for SQL Server 2005SQL Server 2005 and later versions. 새 매개 변수의 기본값이 명시적으로 선언되었습니다.Defaults for new parameters have been explicitly declared.

참고

Windows 자격 증명은 sqlcmd 스크립팅 변수를 사용하여 런타임에 제공됩니다.Windows credentials are supplied at runtime using sqlcmd scripting variables.

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. 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".

-- Enabling the replication database
-- Enable the replication database.
USE [Northwind]
GO

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @article AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'Northwind';
SET @publication = N'NwdCustomersMerge';
SET @article = N'Customers';
-- Specify the Windows account to run the Snapshot Agent.
SET @login = $(Login); 
-- Supply the password at runtime.
SET @password = $(Password); 

EXEC sp_replicationdboption 
	@dbname = @publicationDB, 
	@optname = N'merge publish', 
	@value = N'true';

-- Add the merge publication.
EXEC sp_addmergepublication 
	@publication = @publication, 
	@description = N'Merge publication of Northwind.', 
	@retention = 14, 
	@sync_mode = N'native', 
	@dynamic_filters = N'false', 
	@keep_partition_changes = N'false',
	-- Only set to '90RTM' if all Subscribers are SQL Server 2005.
	@publication_compatibility_level = N'90RTM',
	@replicate_ddl = 1,
	@allow_subscriber_initiated_snapshot = N'true',
	@allow_web_synchronization = N'false',
	@allow_partition_realignment = N'true',
	@retention_period_unit = N'day',
	@automatic_reinitialization_policy = 0,
	@conflict_logging = N'both';
 
EXEC sp_addpublication_snapshot 
	@publication = @publication, 
	@job_login = @login,
	@job_password = @password;

-- Add the merge article.
EXEC sp_addmergearticle 
	@publication = @publication, 
	@article = @article, 
	@source_owner = N'dbo', 
	@source_object = @article, 
	@type = N'table', 
	@description = null, 
	@column_tracking = N'true', 
	@schema_option = 0x0000000000034FD1,
	@partition_options = 0,
	@subscriber_upload_options = 0,
	@identityrangemanagementoption = N'manual',
	@delete_tracking = N'true',
	@compensate_for_errors = N'false',
	@stream_blob_columns = N'true';
GO

예제Example

다음은 트랜잭션 게시에 밀어넣기 구독을 만드는 SQL Server 2000SQL Server 2000 스크립트의 예제입니다.The following is an example of a SQL Server 2000SQL Server 2000 script that creates a push subscription to a transactional publication. 읽기 쉽도록 기본 매개 변수가 삭제되었습니다.Default parameters have been removed for readability.

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'NwdProductTran' 
SET @subscriber = N'MYSUBSERVER' 
SET @subscriptionDB = N'NorthwindReplica' 

-- Add a Subscriber, using the defaults.
USE [master]
EXEC sp_addsubscriber 
    @subscriber = @subscriber

-- Add a push subscription to a transactional publication.
USE [Northwind]
EXEC sp_addsubscription 
    @publication = @publication, 
    @subscriber = @subscriber, 
    @destination_db = @subscriptionDB, 
    @subscription_type = N'push'
GO

예제Example

다음은 트랜잭션 게시에 밀어넣기 구독을 만드는 앞의 스크립트를 SQL Server 2005SQL Server 2005 이상 버전에서 성공적으로 실행되도록 업그레이드하는 예제입니다.The following is an example of the previous script, which creates a push subscription to a transactional publication, upgraded to run successfully for SQL Server 2005SQL Server 2005 and later versions. 새 매개 변수의 기본값이 명시적으로 선언되었습니다.Defaults for new parameters have been explicitly declared.

참고

Windows 자격 증명은 sqlcmd 스크립팅 변수를 사용하여 런타임에 제공됩니다.Windows credentials are supplied at runtime using sqlcmd scripting variables.

-- 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".

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publication = N'NwdProductTran'; 
SET @subscriber = $(Subscriber); 
SET @subscriptionDB = N'NorthwindReplica'; 
-- Specify the Windows account to run the Distribution Agent.
SET @login = $(Login); 
-- Supply the password at runtime.
SET @password = $(Password); 

-- Add a push subscription to a transactional publication.
USE [Northwind]
EXEC sp_addsubscription 
	@publication = @publication, 
	@subscriber = @subscriber, 
	@destination_db = @subscriptionDB, 
	@subscription_type = N'push';

-- Add an agent job to synchronize the push subscription.
EXEC sp_addpushsubscription_agent 
	@publication = @publication, 
	@subscriber = @subscriber, 
	@subscriber_db = @subscriptionDB, 
	@job_login = @login, 
	@job_password = @password;
GO

예제Example

다음은 병합 게시에 밀어넣기 구독을 만드는 SQL Server 2000SQL Server 2000 스크립트의 예제입니다.The following is an example of a SQL Server 2000SQL Server 2000 script that creates a push subscription to a merge publication. 읽기 쉽도록 기본 매개 변수가 삭제되었습니다.Default parameters have been removed for readability.

DECLARE @publication AS sysname
DECLARE @subscriber AS sysname
DECLARE @subscriptionDB AS sysname
SET @publication = N'NwdCustomersMerge' 
SET @subscriber = N'SUBSERVER' 
SET @subscriptionDB = N'NorthwindReplica' 

-- Add a Subscriber, using the defaults.
USE [master]
EXEC sp_addsubscriber 
  @subscriber = @subscriber

-- Add a push subscription to a merge publication.
USE [Northwind]
EXEC sp_addmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @subscription_type = N'push',
  @subscriber_type = N'local',
  @sync_type = N'automatic'
GO

예제Example

다음은 병합 게시에 밀어넣기 구독을 만드는 앞의 스크립트를 SQL Server 2005SQL Server 2005 이상 버전에서 성공적으로 실행되도록 업그레이드하는 예제입니다.The following is an example of the previous script, which creates a push subscription to a merge publication, upgraded to run successfully for SQL Server 2005SQL Server 2005 and later versions. 새 매개 변수의 기본값이 명시적으로 선언되었습니다.Defaults for new parameters have been explicitly declared.

참고

Windows 자격 증명은 sqlcmd 스크립팅 변수를 사용하여 런타임에 제공됩니다.Windows credentials are supplied at runtime using sqlcmd scripting variables.

-- 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".

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publication = N'NwdCustomersMerge'; 
SET @subscriber = $(Subscriber); 
SET @subscriptionDB = N'NorthwindReplica'; 
-- Specify the Windows account to run the Merge Agent.
SET @login = $(Login); 
-- Supply the password at runtime.
SET @password = $(Password); 

-- Add a push subscription to a merge publication.
USE [Northwind]
EXEC sp_addmergesubscription 
	@publication = @publication, 
	@subscriber = @subscriber, 
	@subscriber_db = @subscriptionDB, 
	@subscription_type = N'push';

-- Add an agent job to synchronize the push subscription.
EXEC sp_addmergepushsubscription_agent 
	@publication = @publication, 
	@subscriber = @subscriber, 
	@subscriber_db = @subscriptionDB, 
	@job_login = @login, 
	@job_password = @password;
GO

예제Example

다음은 트랜잭션 게시에 끌어오기 구독을 만드는 SQL Server 2000SQL Server 2000 스크립트의 예제입니다.The following is an example of a SQL Server 2000SQL Server 2000 script that creates a pull subscription to a transactional publication. 읽기 쉽도록 기본 매개 변수가 삭제되었습니다.Default parameters have been removed for readability.

DECLARE @publication AS sysname
DECLARE @subscriber AS sysname
DECLARE @subscriptionDB AS sysname
SET @publication = N'NwdCustomersMerge' 
SET @subscriber = N'SUBSERVER' 
SET @subscriptionDB = N'NorthwindReplica' 

-- Add a Subscriber, using the defaults.
USE [master]
EXEC sp_addsubscriber 
  @subscriber = @subscriber

-- Add a push subscription to a merge publication.
USE [Northwind]
EXEC sp_addmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @subscription_type = N'push',
  @subscriber_type = N'local',
  @sync_type = N'automatic'
GO

예제Example

다음은 트랜잭션 게시에 끌어오기 구독을 만드는 앞의 스크립트를 SQL Server 2005SQL Server 2005 이상 버전에서 성공적으로 실행되도록 업그레이드하는 예제입니다.The following is an example of the previous script, which creates a pull subscription to a transactional publication, upgraded to run successfully for SQL Server 2005SQL Server 2005 and later versions. 새 매개 변수의 기본값이 명시적으로 선언되었습니다.Defaults for new parameters have been explicitly declared.

참고

Windows 자격 증명은 sqlcmd 스크립팅 변수를 사용하여 런타임에 제공됩니다.Windows credentials are supplied at runtime using sqlcmd scripting variables.

-- 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".

-- Execute at the Subscriber.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publication = N'NwdProductTran'; 
SET @publisher = $(Publisher); 
SET @publicationDB = N'Northwind'; 
-- Specify the Windows account to run the Distribution Agent.
SET @login = $(Login); 
-- Supply the password at runtime.
SET @password = $(Password); 

-- At the subscription database, create a pull subscription 
-- to a transactional publication.
USE [NorthwindReplica]
EXEC sp_addpullsubscription 
	@publisher = @publisher, 
	@publication = @publication, 
	@publisher_db = @publicationDB,
	@subscription_type = N'pull';

-- Add an agent job to synchronize the pull subscription.
EXEC sp_addpullsubscription_agent 
	@publisher = @publisher, 
	@publisher_db = @publicationDB, 
	@publication = @publication, 
	@distributor = @publisher,
	@job_login = @login,
	@job_password = @password;
GO

-- Execute at the Publisher.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'NwdProductTran'; 
SET @subscriber = $(Subscriber); 
SET @subscriptionDB = N'NorthwindReplica'; 

-- Add a pull subscription to a transactional publication.
USE [Northwind]
EXEC sp_addsubscription 
	@publication = @publication, 
	@subscriber = @subscriber, 
	@destination_db = @subscriptionDB, 
	@subscription_type = N'pull';
GO

예제Example

다음은 병합 게시에 끌어오기 구독을 만드는 SQL Server 2000SQL Server 2000 스크립트의 예제입니다.The following is an example of a SQL Server 2000SQL Server 2000 script that creates a pull subscription to a merge publication. 읽기 쉽도록 기본 매개 변수가 삭제되었습니다.Default parameters have been removed for readability.

-- Execute at the Subscriber
DECLARE @publication AS sysname
DECLARE @publisher AS sysname
DECLARE @publicationDB AS sysname
DECLARE @subscriber AS sysname
DECLARE @subscriptionDB AS sysname
SET @publication = N'NwdCustomersMerge' 
SET @publisher = N'PUBSERVER' 
SET @publicationDB = N'Northwind' 
SET @subscriber = N'SUBSERVER' 
SET @subscriptionDB = N'NorthwindReplica'

-- At the subscription database, create a pull subscription 
-- to a merge publication.
USE [NorthwindReplica]
EXEC sp_addmergepullsubscription 
    @publisher = @publisher, 
    @publication = @publication, 
    @publisher_db = @publicationDB

-- Add an agent job to synchronize the pull subscription. 
EXEC sp_addmergepullsubscription_agent 
    @publisher = @publisher, 
    @publisher_db = @publicationDB, 
    @publication = @publication, 
    @subscriber = @subscriber, 
    @subscriber_db = @subscriptionDB, 
    @distributor = @publisher
GO

-- Execute at the Publisher.
DECLARE @publication AS sysname
DECLARE @subscriber AS sysname
DECLARE @subscriptionDB AS sysname
SET @publication = N'NwdCustomersMerge' 
SET @subscriber = N'MYSUBSERVER' 
SET @subscriptionDB = N'NorthwindReplica' 

-- Add a Subscriber, using the defaults.
USE [master]
EXEC sp_addsubscriber 
  @subscriber = @subscriber

-- Add a pull subscription to a merge publication.
USE [Northwind]
EXEC sp_addmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @subscription_type = N'pull',
  @subscriber_type = N'local',
  @sync_type = N'automatic'
GO

예제Example

다음은 병합 게시에 끌어오기 구독을 만드는 앞의 스크립트를 SQL Server 2005SQL Server 2005 이상 버전에서 성공적으로 실행되도록 업그레이드하는 예제입니다.The following is an example of the previous script, which creates a pull subscription to a merge publication, upgraded to run successfully for SQL Server 2005SQL Server 2005 and later versions. 새 매개 변수의 기본값이 명시적으로 선언되었습니다.Defaults for new parameters have been explicitly declared.

참고

Windows 자격 증명은 sqlcmd 스크립팅 변수를 사용하여 런타임에 제공됩니다.Windows credentials are supplied at runtime using sqlcmd scripting variables.

-- 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".

-- Execute at the Subscriber
DECLARE @publication  AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publication = N'NwdCustomersMerge'; 
SET @publisher = $(Publisher); 
SET @publicationDB = N'Northwind'; 
-- Specify the Windows account to run the Merge Agent.
SET @login = $(Login); 
-- Pass the password at runtime.
SET @password = $(Password); 

-- At the subscription database, create a pull subscription 
-- to a merge publication.
USE [NorthwindReplica]
EXEC sp_addmergepullsubscription 
  @publisher = @publisher, 
  @publication = @publication, 
  @publisher_db = @publicationDB;

-- Add an agent job to synchronize the pull subscription. 
EXEC sp_addmergepullsubscription_agent 
  @publisher = @publisher, 
  @publisher_db = @publicationDB, 
  @publication = @publication,
  @distributor = @publisher,
  @job_login = @login,
  @job_password = @password;
GO

-- Execute at the Publisher.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'NwdCustomersMerge';
SET @subscriber = $(Subscriber);
SET @subscriptionDB = N'NorthwindReplica';

-- Add a pull subscription to a merge publication.
USE [Northwind]
EXEC sp_addmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @subscription_type = N'pull',
  @subscriber_type = N'local',
  @sync_type = N'automatic';
GO

관련 항목:See Also

Create a Publication Create a Publication
Create a Push Subscription Create a Push Subscription
Create a Pull Subscription Create a Pull Subscription
복제 보안 설정 보기 및 수정 View and Modify Replication Security Settings
MSSQL_ENG021797 MSSQL_ENG021797
MSSQL_ENG021798 MSSQL_ENG021798
Replication System Stored Procedures Concepts Replication System Stored Procedures Concepts
복제된 데이터베이스 업그레이드Upgrade Replicated Databases