How to: Create a Publication (Replication Transact-SQL Programming)

Publications can be created programmatically using replication stored procedures. The stored procedures that are used will depend on the type of publication being created.

Security Note
When possible, use Windows Authentication. When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

To create a snapshot or transactional publication

1. At the Publisher on the publication database, execute sp_replicationdboption (Transact-SQL) to enable publication of the current database using snapshot or transactional replication.

2. For a transactional publication, determine whether a Log Reader Agent job exists for the publication database. (This step is not required for snapshot publications.)

   • If a Log Reader Agent job exists for the publication database, proceed to step 3.

   • If you are unsure whether a Log Reader Agent job exists for a published database, execute sp_helplogreader_agent (Transact-SQL) at the Publisher on the publication database.

   • If the result set is empty, create a Log Reader Agent job. At the Publisher, execute sp_addlogreader_agent (Transact-SQL). Specify the Microsoft Windows credentials under which the agent runs for @job_name and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the Microsoft SQL Server login information for @publisher_login and @publisher_password. Proceed to step 3.

3. At the Publisher, execute sp_addpublication (Transact-SQL). Specify a publication name for @publication, and, for the @repl_freq parameter, specify a value of snapshot for a snapshot publication or a value of continuous for a transactional publication. Specify any other publication options. This defines the publication.

   Note

   Publication names cannot include the following characters:

   % * [ ] | : " ? \ / < >

4. At the Publisher, execute sp_addpublication_snapshot (Transact-SQL). Specify the publication name used in step 3 for @publication and the Windows credentials under which the Snapshot Agent runs for @snapshot_job_name and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the SQL Server login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication.

   Security Note
   When configuring a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Encrypting Connections to SQL Server.

5. Add articles to the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

6. Start the Snapshot Agent job to generate the initial snapshot for this publication. For more information, see How to: Create the Initial Snapshot (Replication Transact-SQL Programming).

To create a merge publication

1. At the Publisher, execute sp_replicationdboption (Transact-SQL) to enable publication of the current database using merge replication.

2. At the Publisher on the publication database, execute sp_addmergepublication (Transact-SQL). Specify a name for the publication for @publication and any other publication options. This defines the publication.

   Note

   Publication names cannot include the following characters:

   % * [ ] | : " ? \ / < >

3. At the Publisher, execute sp_addpublication_snapshot (Transact-SQL). Specify the publication name used in step 2 for @publication and the Windows credentials under which the Snapshot Agent runs for @snapshot_job_name and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the SQL Server login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication.

   Security Note
   When configuring a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Encrypting Connections to SQL Server.

4. Add articles to the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

5. Start the Snapshot Agent job to generate the initial snapshot for this publication. For more information, see How to: Create the Initial Snapshot (Replication Transact-SQL Programming).

Example

This example creates a transactional publication. Scripting variables are used to pass Windows credentials that are needed to create jobs for the Snapshot Agent and Log Reader Agent.

-- To avoid storing the login and password in the script file, the values 
-- are passed into SQLCMD as scripting variables. 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 @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'AdventureWorks2008R2'; 
SET @publication = N'AdvWorksProductTran'; 
-- Windows account used to run the Log Reader and Snapshot Agents.
SET @login = $(Login); 
-- This should be passed at runtime.
SET @password = $(Password); 

-- Enable transactional or snapshot replication on the publication 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;

-- Create a new transactional publication with the required properties. 
EXEC sp_addpublication 
    @publication = @publication, 
    @status = N'active',
    @allow_push = N'true',
    @allow_pull = N'true',
    @independent_agent = N'true';

-- 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;
GO

This example creates a merge publication. Scripting variables are used to pass Windows credentials that are needed to create the job for the Snapshot Agent.

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

--Declarations for adding a merge publication
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'AdventureWorks2008R2'; 
SET @publication = N'AdvWorksSalesOrdersMerge'; 
SET @login = $(Login);
SET @password = $(Password);

-- Enable merge replication on the publication database, using defaults.
USE master
EXEC sp_replicationdboption 
  @dbname=@publicationDB, 
  @optname=N'merge publish',
  @value = N'true' 

-- Create a new merge publication, explicitly setting the defaults. 
USE [AdventureWorks2008R2]
EXEC sp_addmergepublication 
-- These parameters are optional.
  @publication = @publication,
  -- optional parameters 
  @description = N'Merge publication of AdventureWorks2008R2.',
  @publication_compatibility_level  = N'90RTM';

-- Create a new snapshot job for the publication.
EXEC sp_addpublication_snapshot 
  @publication = @publication, 
  @job_login = @login, 
  @job_password = @password;
GO

See Also

Tasks

How to: Create a Publication (RMO Programming)

Concepts

Replication System Stored Procedures Concepts

Publishing Data and Database Objects

Using sqlcmd with Scripting Variables