Configuring a Publication to Allow Subscribers to Retrieve Snapshots Using FTP

SQL Replication


Configuring a Publication to Allow Subscribers to Retrieve Snapshots Using FTP

After you have completed configuring your servers to listen on the TCP/IP or Multiprotocol connection, you are ready to configure your publications for publishing over the Internet. Any publication you create can be enabled for Internet publishing by setting the @enabled_for_internet property on the publication. Setting @enabled_for_internet to TRUE tells the Snapshot Agent to place the files associated with the initial snapshot into the FTP location specified in Publication Properties.

The Distribution Agent or Merge Agent uses FTP to download the snapshot of the schema and data to the Subscriber. The image of the entire publication flows to the destination database where it is re-created as an exact duplicate. After the snapshot files arrive at the Subscriber, the agent applies the files to the appropriate tables at the Subscriber. The agent moves through each table taking out exclusive locks on a set of rows, copying in the new rows, releasing the locks on the rows, and then repeating the process on the next blocks of rows. Because the agent locks only a small number of rows at one time, other users should be able to continue using the tables with minimal disruption.

You can configure a publication through SQL Server Enterprise Manager by selecting Allow snapshots to be downloaded using FTP on the Subscriptions Option tab of the publication Properties dialog box. You can also set the @enabled_for_internet property programmatically through the replication stored procedures that support replication over the Internet:

  • sp_addpublication
  • sp_addmergepublication

  • sp_changemergepublication

  • sp_helpmergepublication

To publish data over the Internet