Merge Replication–Web Synchronization with FTP Snapshot

  • Article

This week I spent a good number of hours to review and assist a Web Synchronization lab setup for a Customer. We tried to look for all possible resources but unfortunately there is almost no Step-by-Step guide available on Web Sync. Thus, thought of making it available on the Web for rest of the world.

Pre-requisites for Web Sync (IIS)

  • Install the Internet Information Services 6.0 (IIS 6.0) management compatibility components (all).
  • Install ISAPI Extensions and CGI
  • FTP Server Components ( In case no separate FTP Server and Web Server need to be used as FTP Server as well) 
    • FTP Server
    • FTP Management Console (Windows Server 2008)
  • Install Replication Components (In case SQL Server and Web Servers are two separate box) - https://msdn.microsoft.com/en-us/library/ms172376(SQL.100).aspx

Steps for Web Sync Setup

  • Create a new FTP Site from IIS Management Console with appropriate Physical Path and expected Alias.
  • Enable Anonymous Read access
  • Give NTFS Write Permissions to Publisher SQL Server Service Account
  • Configure Merge Publication (Follow the GUI wizard)
  • Right Click on Publication and select Configure Web Synchronization
  • Select Subscription Type
  • Select the Name of IIS Server
  • Create a new Virtual Directory
    • Give Alias and Physical Path
    • Replication ISAPI would be copied in the folder.
    • Select Basic Authentication and select Default Domain and Realm from Browse option
    • Select Users/Groups who need access to Virtual Directory (Give the Service Account details)
    • Give the UNC path for FTP physical folder
    • Finish the wizard.

Web Server Configuration Check

  • Check ISAPI and CGI restriction – The Replication ISAPI components should be allowed.
  • Application Pool should be set to Enable 32 bit Application.
  • Disable Anonymous Authentication and enable Basic Authentication in IIS.
  • Make sure that Local Computer/IIS_IUSRS have Read, Read & Execute and List permissions in Virtual Directory created above.
  • Add IIS and SQL Publisher Computer Accounts to IIS_IUSRS group with same permissions as above.
  • SQL Service Account should have full permissions on this folder.
  • Create a Self Signed Certificate in IIS and bind your Web Sync virtual directory to use it.
  • Configure SSL Settings and Select Require SSL and Client Certificate to Ignore.
  • Access https://<iisboxfqdn>/<websyncfolder>/replisapi.dll and that should give you certificate warning. On acceptance, it would prompt for User Account/Password. On proper authentication, page should show SQL Server WebSync ISAPI in message.
  • Install the Certificate in Trusted Root CA.
  • Export the Certificate and install in trusted Root CA of Subscriber box.

Publisher Steps

  • Change the Publisher Snapshot Folder location to FTP sites Physical UNC Path (If not set yet)
  • Select Allow Subscribers to download Snapshot file using FTP and type in proper FTP server name, Path (ftp).
  • Select Allow Subscribers to synchronize by connecting to Web Server and type in Web Server URL with Virtual Directory (https) as above.

Note: Defining alternate Snapshot Folder would always create a subfolder named FTP inside the UNC path. You need to make sure that you mention this in your FTP snapshot location.

Subscriber Steps

  • For Connected subscribers (LAN), create subscription using wizard and change the default snapshot location to FTP.
  • In the wizard make sure to select Use Web Sync and it would pick up Web Sync URL as mentioned in above step automatically.
  • Type in User Name/Password on basic authentication section.
  • For remote subscribers, you can create a script from subscriber created from above step and execute the same on remote subscribers

Please refer to below two articles for more details on security settings: