Deliver a Snapshot Through FTP

Article
06/13/2017

This topic describes how to deliver a snapshot through FTP in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL.

Limitations and Restrictions

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 Secure the Snapshot Folder.

Prerequisites

To transfer snapshot files using File Transfer Protocol (FTP), you must first configure an FTP server. For more information, see the Microsoft Internet Information Services (IIS) documentation.

Security

To help improve security, we recommend that you implement a virtual private network (VPN) when using FTP snapshot delivery over the Internet. For more information, see Publish Data over the Internet Using VPN.

As a security best practice, do not allow anonymous logins to the FTP server. 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 Secure the Snapshot Folder.

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

Using SQL Server Management Studio

After the FTP server is configured, specify directory and security information for this server in the Publication Properties <Publication> dialog box. For more information about accessing this dialog box, see View and Modify Publication Properties.

To specify FTP information

In the Publication Properties - <Publication> dialog box, select Allow Subscribers to download snapshot files using FTP from one of the following pages:
- The FTP Snapshot page, for snapshot and transactional publications, and merge publications for Publishers running versions prior to Microsoft SQL Server 2005.
- The FTP Snapshot and Internet page, for merge publications from Publishers running SQL Server 2005 or later.
Specify values for FTP server name, Port number, Path from the FTP root folder, Login, and Password.
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 the property Path from the FTP root folder (replication appends 'ftp' to the snapshot folder path when it creates snapshot files).
Specify that the Snapshot Agent should write the snapshot files to the directory specified in step 2. For example, to have the Snapshot Agent write the snapshot files to \\ftpserver\home\snapshots\ftp, you must specify the path \\ftpserver\home\snapshots in one of two places:
- The default snapshot location for the Distributor associated with this publication.
  For more information about specifying the default snapshot location, see Specify the Default Snapshot Location.
- An alternate snapshot folder location for this publication. An alternate location is required if the snapshot is compressed.
  Enter the path in the Put files in the following folder textbox on the Snapshot page of the Publication Properties - <Publication> dialog box.
Click OK.

Using Transact-SQL

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.

To enable FTP snapshot delivery for a snapshot or transactional publication

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.
This creates a publication that uses FTP. For more information, see Create a Publication.

To enable FTP snapshot delivery for a merge publication

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.
This creates a publication that uses FTP. For more information, see Create a Publication.

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

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.
At the Publisher on the publication database, execute sp_addsubscription to register the pull subscription. For more information, see Create a Pull Subscription.

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

At the Subscriber on the subscription database, execute sp_addmergepullsubscription. Specify @publisher and @publication.
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.
At the Publisher on the publication database, execute sp_addmergesubscription to register the pull subscription. For more information, see Create a Pull Subscription.

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

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.
(Optional) Repeat step 1 for each FTP setting being changed.
(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

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.
(Optional) Repeat step 1 for each FTP setting being changed.
(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.

Examples (Transact-SQL)

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 Use 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 Use 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

Share via