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

Mis à jour : 14 avril 2006

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.

Remarque relative à la sécurité :
For best security, implement a virtual private network (VPN) when using FTP snapshot delivery over the Internet. For more information, see Publication de données sur Internet à l'aide d'un réseau privé virtuel.

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.

     Remarque relative à la sécurité :
     As a security best practice, do not allow anonymous logins to the FTP server.

   Remarque :
   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 Sécurisation du dossier de capture instantanée.

   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.

     Remarque relative à la sécurité :
     As a security best practice, do not allow anonymous logins to the FTP server.

   Remarque :
   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 Sécurisation du dossier de capture instantanée.

   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 Procédure : créer un abonnement par extraction de données (programmation Transact-SQL de réplication).

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 Procédure : créer un abonnement par extraction de données (programmation Transact-SQL de réplication).

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.

   Remarque relative à la sécurité :
   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.

   Remarque relative à la sécurité :
   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.

Exemple

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 Utilisation de sqlcmd avec des variables de script.

-- 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 Utilisation de sqlcmd avec des variables de script.

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

Voir aussi

Concepts

Programming Replication Using System Stored Procedures

Autres ressources

Procédure : remettre une capture instantanée via FTP (SQL Server Management Studio)
Transfert de captures instantanées via FTP

Aide et Informations

Assistance sur SQL Server 2005