Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL PULL Merge Replication over web - totally unreliable?

Beantwortet SQL PULL Merge Replication over web - totally unreliable?

  • Monday, December 03, 2012 4:46 AM
     
     
    Sign In to Vote
    0

    Hi,

    We have problems performing SQL PULL merge replication over the web.  Most of the time it works fine, but periodically we experience issues as described below.  Our application uses SQL Express for subscribers and we have our own C# RMO code for the agent.  We have eliminated our application as the source of the problem by setting up a full SQL Server as a subscriber and using the built in agent.  It experiences the same problems.

    1. Unable to download a snapshot.

    After reinitializing a subscriber, the snapshot will partially download and will then suddenly stop with the error:

        The connection with the server was terminated abnormally

    There is no obvious timeout issue, the error typically occurs as soon as one of the .bcp files is about to be downloaded.  It's often the same .bcp file that has the problem, but not always so there is no consistency.

    The only solution is to continually retry until the download eventually succeeds.  Recreating the snapshot before retrying seems to improve the chances of a successful sync.

    2. Unable to perform a normal sync (i.e. an update, rather than a full snapshot)

    An attempt to perform a sync results in the following information being logged:

        The upload message to be sent to Publisher 'SERVER2' is being generated
        The merge process is using Exchange ID 'F82F1A56-A81A-4786-A469-7A76259F9ED8' for this web synchronization session.
        No data needed to be merged.
        Request message generated, now making it ready for upload.
        Upload request size is 1895 bytes.

    At this point the sync simply stops doing anything.  There are no errors, the agent still appears to be running, but nothing else happens.

    We typically experience this problem once every few weeks.  Reinitializing a subscriber does not help, the same problem occurs.  The only solution is to recreate the snapshot and then to reinitialize the subscriber.  This then requires the subscriber to perform a full sync which takes around 45 minutes for our dataset.

    I am aware through online searches that others have experienced similar issues with SQL replication over web.  Are there any practical solutions to these issues, or is SQL replication over web simply broken?

     

All Replies

  • Monday, December 03, 2012 6:46 AM
    Moderator
     
     
    Sign In to Vote
    0
    I struggled with the same problem for quite some time.  I would recommend applying the latest service pack and see if that helps.  If not, I recommend contacting Microsoft Support.

    Brandon Williams (blog | linkedin)

     
  • Monday, December 03, 2012 9:37 PM
     
     
    Sign In to Vote
    0

    Hi Brandon, I noticed a post from you back towards the end of last year where you said you were giving up on it.  Did you ever get it resolved?  If not, did you identify a viable alternative solution?

    We need to replicate a SQL database to mobile tablets over 3G.  We've invested a lot of time and effort trying to make this solution work and to find that even using straight SQL server to SQL server has the same issues is disconcerting.

    One possible option is that although we currently use merge replication, that was really just a bad design decision taken early on.  We only perform one way replication, all our articles are flagged as one way only, server down to clients.  I have considered maybe moving towards transactional replication but unless I'm 100% certain I won't hit the same kind of issues, it really isn't viable to invest the time and effort.

     
  • Monday, December 03, 2012 10:01 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    MS ended up fixing a few of the problems we were running into but we were behind schedule for our project and did not have the time or the confidence to follow through with Web Synchronization.

    We ended up going with regular Merge over VPN which we have found to be very reliable.

    You may want to schedule a call with CSS and request to speak with a member of the Replication support team and see what they have to say.

    Brandon Williams (blog | linkedin)

    • Marked As Answer by Steve-Verdant Tuesday, December 04, 2012 11:05 PM
    •  
     
  • Tuesday, December 04, 2012 11:04 PM
     
     
    Sign In to Vote
    0

    Hi Brandon,

    Thanks for the update.  I am in the process of testing the replication over VPN instead of using web, and initial signs look very promising.  I'll update this issue as resolved for now.  Your assistance is much appreciated.