none
SQL Server replcation - delivering a snapshot through FTP time out after 30 seconds on large files

    Question

  • Hi all.

    I'm having a issue when delivering/requesting a snapshot through FTP.

    After studiyng the SQL logs, the problem seems to be that when the subscriber starts the download through FTP, everything seems to be working fine.
    But when hitting a large file (5Mb and up) and the transferperiod exceed 30 seconds, the transfer s aborted and the initial transfer of the snapshot is aborted (starting all over again)

    I have tried to enter a higher value for timeout of remote connections under the server settings (this is SQL 2008) Server properties --> Connections --> Allow remote connections to this server --> Remote query timeout == 0

    This, and several things has been bumped up on both the distributor and the subscriber without any luc.

    Any suggestions? :)
    Monday, September 14, 2009 8:03 AM

All replies

  • This is likely a problem with your link between your publisher and subscriber.

    Can you go to your subscriber and try to ftp the file down from the command line?

    Some other options which may work for you are 1) compressing your snapshot, or 2) using the alternate snapshot folder or dynamic snapshot location (merge only).

    If you use option 2 you will need to write your own code to download the snapshot yourself.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, September 14, 2009 11:03 AM
    Moderator
  • I know that this is not exactly what you want but this is my experience.

    I never transfer the snapshot. This is for me worst practice. My system has 10GB database and just try to imagine what happened when I first tried what u are doing. So many bulk data = computer restart without asking if I want or not, just closing everything and restart. This happens always no matter of the computer or the database bigger that 4-5 GB. And the server is dou processor with total 8 cores and 8gb ram. Excuse me but this SQL server function sucks, what server should I have to handle bigger databases.

    First I suggest that you should copy the database to the subscriber and then init the subscription without initializing the snapshot. After that run the merge agent and you are ready. Currently my database replicates trough FTP configured that way, on a distance of 500kilometers.

    These are my steps:

    =====================

    0. Prepare Db
        0.1. Drop all replication information
            - EXEC sp_removedbreplication @dbname='DatabaseName'
    PS: The prepared database must be attached to all subscribers

    INIT THE PUBLICATION: Some steps are missing because they are specific for me, not related for replication.
    1. Attach DataBase to publication server
        1.1 Execute: dbcc checkdb('dbname')
    6. Check/enable distributor
       6.1.  Create ReplData folder in D:\ReplData
       6.2.  Share ReplData
       6.3.  Right-click Replication (MSSMS) -> Configure Distribution
       6.4.  The server will act as its own Distributor
       6.5.  Snapshot Folder must be the ReplData folder
       6.5.  Finish
    7. Init the publication.
    8. Configure ftp server:
       8.1. Check if FTP server is installed (IIS 6.0 FTP)
          8.1.1. If it's not installed go to Control Panel->Programs and Features->Turn Windows features on or off
          8.1.2. Roles -> Web Server(IIS) -> Add Roles
          8.1.3. Check FTP lines (in the end of the list)
       8.2. Change the root directory of the ftp server to ReplData folder (right-click IIS->FTP Sites->Default FTP Site [Home Directory] (change Local Path))
       8.3. Right click on publserver's publication and select properties.
          8.3.1. In FTP Snapshot and Internet check Allow Subscribers to download snapshot files using FTP
          8.3.2. FTP Server name = publication server name
          8.3.3. Path from the FTP root folder = \ftp
          8.3.4. Login = youruser
          8.3.5. Password = yourpass
          8.3.6. Uncheck Allow Subscribers to synchronize by connecting to the Web server
    9. At subscriber's SQL Server Configuration Manager
       9.1. SQL Native Clien Configuration -> Aliases
       9.2. Create new alias to publication server
          9.2.1. Alias Name = publication servername
          9.2.2. Port No = 1433
          9.2.3. Protocol = TCP/IP
          9.2.4. Server Name = publication servername
       9.3. Edit C:\Windows\System32\Drivers\etc\hosts
          9.3.1. Add publication Server's IP followed by single space and the publication servername
    10. Run snapshot agent at step one
       SQL Server Agent -> Jobs -> publication server SnapshotAgent -> Start Job At Step... (choose step one)

    INIT SUBSCRIBER:
    0. Attach prepared DataBase to subscriber server
    1. Execute dbcc checkdb(database)


    6. On subscriber server create new pull subscription to publication server
      6.1. On subscriber right-click on Local Subscriptions -> New Subscription
      6.2. On First Page Next
      6.3. On Second Page find and choose publicationserver->your already created publication and click Next
      6.4. On Third Page select Run each agent at its Subscriber (pull subscription)
      6.5. On Forth page select Subscriber and subscription database
      6.6. On Fifth page click "...." button
        6.6.1. Merge process should run under SQL Server Agent service account
        6.6.2. Connect to the Publisher Using the following SQL Server login:
           Username: yoursqluser
           Password: sql password
        6.6.3. Click Next
      6.7. On Sixth page Agent Schedule -> <Define schedule...>
        6.7.1. Frequency - Daily
        6.7.2. Occurs every - 30 Minutes
        6.7.3. OK
        6.7.4. Next
      6.8. On Seventh page uncheck Initialize and click Next!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
      6.9. On Eight page Subscription type - Server, Priority for conflict resolution 75.00, Next
      6.10. On the Nineth page Create the subscription(s) must be checked, Next
      6.11. On the Last page Finish
     
    7. On subscriber server run merge agent

    ==================================
    Same sh1t different day
    Monday, September 14, 2009 11:11 AM
  • This is not a good idea. Please refer to http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/f1e8976b-1976-4650-94af-6126741d38c6  for the reasons why.

    It might work if you backup the publisher database and deploy it to the subscriber, but most subscribers do not have the full set of data that the publisher does.


    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, September 14, 2009 1:18 PM
    Moderator
  • this link might be helpful in discussing the varying options available to you.

    http://www.informit.com/articles/article.aspx?p=1392520

    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, September 14, 2009 1:20 PM
    Moderator
  • To keep 'on track' with the current issue I'm having with FTP transmission of the snapshot - the problem (and answer I guess) is pretty simple. The FTP session time out after 30 seconds on 'no activity' other than the filetransfer itself.

    A file with the size 1,5Mb is transferred successfully and the process with the next file is initiated.
    However, if the file is large (aprox. 5Mb and up) the transfer itself use more than 30 seconds, and the transfer is aborted. A second transfer is initiated, but since this also use more than 30 seconds this also is aborted after 30 seconds.

    Have tried to dowload the file throug command line, I'm able to pull the whoe file through without any problems.

    Have also tried to compress the snapshot, but since compressing it creates ONE large file, the issue is the same here, I use more than 30 seconds to transfer the file.

    Monday, September 14, 2009 1:25 PM
  • I'm perplexed by this one.

    What is your setting for querytimeout? I am not sure if this impacts the ftp timeout or not.
    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, September 14, 2009 4:14 PM
    Moderator
  • On serverlevel: 'Remote query timeout' is set to 0 (no tmeout)
    On the linked server object: 'Query timeout' is set to 0

    Any other place I should look? :)
    Tuesday, September 15, 2009 8:10 AM
  • When querytimeout is set to 0, it defaults to the ODBC timeout which IIRC is 20 s. You will need to change it to something like 300 (5 minutes).
    Monday, September 17, 2018 1:26 PM
    Moderator
  • Remote query timeout is only for linked servers.  It has nothing to do with FTP for replication.

    There is no setting in SQL Server which would disconnect your server during FTP transfer of the snapshot file.  I suggest you look at your FTP server logs and try to determine why it is being disconnected.

    Monday, September 17, 2018 3:07 PM