How to: Deliver a Snapshot Through FTP (Replication Transact-SQL Programming)

The option to make snapshot files available on an FTP server can be set and these FTP settings can be modified programmatically using replication stored procedures. The procedure used depends on the type of publication. FTP snapshot delivery is only used with pull subscriptions.

Security Note:
For best security, implement a virtual private network (VPN) when using FTP snapshot delivery over the Internet. For more information, see Publishing Data over the Internet Using VPN.

To enable FTP snapshot delivery for a snapshot or transactional publication

1. At the Publisher on the publication database, execute sp_addpublication. Specify @publication, a value of true for @enabled_for_internet, and appropriate values for the following parameters:

   • @ftp_address - the address of the FTP server used to deliver the snapshot.
   • (Optional) @ftp_port - the port used by the FTP server.
   • (Optional) @ftp_subdirectory - the subdirectory of the default FTP directory assigned to an FTP login. For example, if the FTP server root is \\ftpserver\home and you want snapshots to be stored at \\ftpserver\home\snapshots, specify \snapshots\ftp for @ftp_subdirectory (replication appends 'ftp' to the snapshot folder path when it creates snapshot files).
   • (Optional) @ftp_login - a login account used when connecting to the FTP server.
   • (Optional) @ftp_password - the password for the FTP login.

     Security Note:
     As a security best practice, do not allow anonymous logins to the FTP server.

   Note

   The Snapshot Agent must have write permissions for the directory you specify, and the Distribution Agent or Merge Agent must have read permissions. If pull subscriptions are used, you must specify a shared directory as a universal naming convention (UNC) path, such as \ftpserver\home\snapshots. For more information, see Securing the Snapshot Folder.

   This creates a publication that uses FTP. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

To enable FTP snapshot delivery for a merge publication

1. At the Publisher on the publication database, execute sp_addmergepublication. Specify @publication, a value of true for @enabled_for_internet and appropriate values for the following parameters:

   • @ftp_address - the address of the FTP server used to deliver the snapshot.
   • (Optional) @ftp_port - the port used by the FTP server.
   • (Optional) @ftp_subdirectory - the subdirectory of the default FTP directory assigned to an FTP login. For example, if the FTP server root is \\ftpserver\home and you want snapshots to be stored at \\ftpserver\home\snapshots, specify \snapshots\ftp for @ftp_subdirectory (replication appends 'ftp' to the snapshot folder path when it creates snapshot files).
   • (Optional) @ftp_login - a login account used when connecting to the FTP server.
   • (Optional) @ftp_password - the password for the FTP login.

     Security Note:
     As a security best practice, do not allow anonymous logins to the FTP server.

   Note

   The Snapshot Agent must have write permissions for the directory you specify, and the Distribution Agent or Merge Agent must have read permissions. If pull subscriptions are used, you must specify a shared directory as a universal naming convention (UNC) path, such as \ftpserver\home\snapshots. For more information, see Securing the Snapshot Folder.

   This creates a publication that uses FTP. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

To create a pull subscription to a snapshot or transactional publication that uses FTP snapshot delivery

1. At the Subscriber on the subscription database, execute sp_addpullsubscription. Specify @publisher and @publication.

   • At the Subscriber on the subscription database, execute sp_addpullsubscription_agent. Specify @publisher, @publisher_db, @publication, the Microsoft Windows credentials under which the Distribution Agent at the Subscriber runs for @job_login and @job_password, and a value of true for @use_ftp.

2. At the Publisher on the publication database, execute sp_addsubscription to register the pull subscription. For more information, see How to: Create a Pull Subscription (Replication Transact-SQL Programming).

To create a pull subscription to a merge publication that uses FTP snapshot delivery

1. At the Subscriber on the subscription database, execute sp_addmergepullsubscription. Specify @publisher and @publication.

2. At the Subscriber on the subscription database, execute sp_addmergepullsubscription_agent. Specify @publisher, @publisher_db, @publication, the Windows credentials under which the Distribution Agent at the Subscriber runs for @job_login and @job_password, and a value of true for @use_ftp.

3. At the Publisher on the publication database, execute sp_addmergesubscription to register the pull subscription. For more information, see How to: Create a Pull Subscription (Replication Transact-SQL Programming).

To change one or more FTP snapshot delivery settings for a snapshot or transactional publication

1. At the Publisher on the publication database, execute sp_changepublication. Specify one of the following values for @property and a new value of this setting for @value:

   • ftp_address - the address of the FTP server used to deliver the snapshot.
   • ftp_port - the port used by the FTP server.
   • ftp_subdirectory - the subdirectory of the default FTP directory used for the FTP snapshot.
   • ftp_login - a login used to connect to the FTP server.
   • ftp_password - the password for the FTP login.

   Security Note:
   When possible, prompt users to enter their credentials at runtime. If you store credentials in a script file, you must secure the file.

2. (Optional) Repeat step 1 for each FTP setting being changed.

3. (Optional) To disable FTP snapshot delivery, execute sp_changepublication at the Publisher on the publication database. Specify a value of enabled_for_internet for @property and a value of false for @value.

To change FTP snapshot delivery settings for a merge publication

1. At the Publisher on the publication database, execute sp_changemergepublication. Specify one of the following values for @property and a new value of this setting for @value:

   • ftp_address - the address of the FTP server used to deliver the snapshot.
   • ftp_port - the port used by the FTP server.
   • ftp_subdirectory - the subdirectory of the default FTP directory used for the FTP snapshot.
   • ftp_login - a login used to connect to the FTP server.
   • ftp_password - the password for the FTP login.

   Security Note:
   When possible, prompt users to enter their credentials at runtime. If you store credentials in a script file, you must secure the file.

2. (Optional) Repeat step 1 for each FTP setting being changed.

3. (Optional) To disable FTP snapshot delivery, execute sp_changemergepublication at the Publisher on the publication database. Specify a value of enabled_for_internet for @property and a value of false for @value.

Example

The following example creates a merge publication that allows Subscribers to access the snapshot data using FTP. The Subscriber should use a secure VPN connection when accessing the FTP share. sqlcmd scripting variables are used to supply login and password values. For more information, see Using sqlcmd with 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".

-- Declarations for adding a merge publication.
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @ftp_server AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
DECLARE @ftp_login AS sysname;
DECLARE @ftp_password AS sysname;
DECLARE @ftp_directory AS sysname;
DECLARE @snapshot_folder AS sysname;
DECLARE @article AS sysname;
DECLARE @owner AS sysname;
SET @publicationDB = N'AdventureWorks'; 
SET @publication = N'AdvWorksSalesOfferMergeFtp'; 
SET @ftp_server = $(Server);
SET @login = $(Login);
SET @password = $(Password);
SET @ftp_login = $(FtpLogin);
SET @ftp_password = $(FtpPassword);
SET @ftp_directory = N'\snapshots\ftp';
-- The snapshot folder is the root FTP folder on the server 
-- with the \snapshot subdirectory.
SET @snapshot_folder = $(AlternateFolder);
SET @article = N'SpecialOffer'; 
SET @owner = N'Sales' 

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

-- Create a new merge publication, enabling FTP snapshot delivery. 
-- Specify the publication compatibility level or it will default to 
-- SQL Server 2000.
USE [AdventureWorks]
EXEC sp_addmergepublication 
-- Specify the required parameters.
    @publication = @publication,
    @publication_compatibility_level = N'90RTM',
    @enabled_for_internet = N'true',
    @snapshot_in_defaultfolder = N'true',
    @alt_snapshot_folder = @snapshot_folder,
    @ftp_address = @ftp_server,
    @ftp_subdirectory = @ftp_directory,
    @ftp_login = @ftp_login,
    @ftp_password = @ftp_password;

-- Create the snapshot job for the publication, using the defaults.
EXEC sp_addpublication_snapshot 
    @publication = @publication, 
    @job_login = @login, 
    @job_password = @password;

-- Add an unfiltered article for the Customer table.
EXEC sp_addmergearticle 
    @publication = @publication, 
    @article = @article, 
    @source_object = @article, 
    @type = N'table', 
    @source_owner = @owner, 
    @destination_owner = @owner, 
    @column_tracking = N'true'; 

-- Start the snapshot job for the publication.
EXEC sp_startpublication_snapshot 
    @publication = @publication;
GO

The following example creates a subscription to a merge publication, where the Subscriber obtains the snapshot using FTP. The Subscriber should use a secure VPN connection when accessing the FTP share. sqlcmd scripting variables are used to supply login and password values. For more information, see Using sqlcmd with 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 this batch at the Publisher.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'AdvWorksSalesOfferMergeFtp';
SET @subscriber = $(SubServer);
SET @subscriptionDB = N'AdventureWorksReplica';

-- At the Publisher, register the subscription, using the defaults.
EXEC sp_addmergesubscription 
    @publication = @publication, 
    @subscriber = @subscriber, 
    @subscriber_db = @subscriptionDB, 
    @subscription_type = N'pull', 
    @subscriber_type = N'local', 
    @subscription_priority = 0, 
    @sync_type = N'Automatic';
GO

-- 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 this batch at the Subscriber.
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publicationDB = N'AdventureWorks'; 
SET @publication = N'AdvWorksSalesOfferMergeFtp'; 
SET @publisher = $(PubServer);
SET @login = $(Login);
SET @password = $(Password);
SET @subscriber = $(SubServer);
SET @subscriptionDB = N'AdventureWorksReplica';

EXEC sp_addmergepullsubscription 
    @publisher = @publisher, 
    @publication = @publication, 
    @publisher_db = @publicationDB, 
    @subscriber_type = N'Local', 
    @subscription_priority = 0, 
    @sync_type = N'Automatic';

exec sp_addmergepullsubscription_agent 
    @publisher = @publisher, 
    @publisher_db = @publicationDB, 
    @publication = @publication, 
    @distributor = @publisher, 
    @distributor_security_mode = 1, 
    @use_ftp = N'true', 
    @job_login = @login, 
    @job_password = @password, 
    @publisher_security_mode = 1, 
    @use_web_sync = 0;
GO

See Also

Concepts

Programming Replication Using System Stored Procedures

Other Resources

How to: Deliver a Snapshot Through FTP (SQL Server Management Studio)
Transferring Snapshots Through FTP

Help and Information

Getting SQL Server 2005 Assistance