locked
Syncing SQL Server Express 2008 via a webservice RRS feed

  • Question

  • Hi All,

    We are looking into synchronisation services and have got the SQLExpressProviderSample working which meets 99% of our needs.

    However in the sample the local machine directly connects to the central servers database; this is not going to be possible in the production environment as there are only a few ports open for HTTP(s) internet traffic etc.

    Therefore is it possible to have a web-service on the target server running that the local machine connects to?

    Any help would be appreciated.
    Thanks


    A bit more detail about our scenario:
    • A single server with a single instance of SQL Server Express 2008 running on it, this holds the central database.
    • A website accesses this data and allows users to add/amend the data.
    • Also multiple client applications (C# WinForms) need to connect to the data and also add/amend it, but may be working "offline" for periods of time.
    • These clients will have SQL Server Express 2008 installed.
    • This data then needs to be synced back up (bi-directional syncing).
    • The database uses auto incrementing fields, we plan to set each client to have the auto incrementing field offset so as to make sure we get globally unique ids. (64 bit auto incrementing field, use the top 16 bits to store the "client id").

    Friday, October 16, 2009 12:15 PM

Answers

  • Hi Mark,

    Here are some resources you can look at:

    Documentation: "How to: Configure and Execute Collaborative Synchronization (SQL Server)"
    http://msdn.microsoft.com/en-us/library/dd918848(SQL.105).aspx

    Also a sample that demonstrates n-tier architecture can be found here:
    C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.0\Samples\WebSharingAppDemo-CEProviderEndToEnd

    As to your issue, we scale tested 200 concurrent through IIS so there shouldn't be issues. Without knowing more information, like logging, eventing, profiler, etc... it's hard to diagnose what went wrong on your side. There are couple of things you can try to get more info:

    1) Run trace verbosity level 4 using System.Diagnostics to determine what sync is doing when the connection hangs.
    2) Run sql profiler to determine what is happening on the database server during the hang
    3) Analyze WCF logs to determine what's happening on the mid-tier.

    Thanks,
    Nina

    Monday, November 30, 2009 7:54 PM
    Moderator

All replies

  • Anyone have any suggestions/pointers?

    I have seen samples that do
        SQL Server Express <->Web Service <-> SQL Compact

    But none where it is SQL Server Express at both end points. Thanks.
    Monday, October 19, 2009 7:25 AM
  • Ok, I ditched the above idea and instead gone to the format

    SQL Server Express<->WCF Web Service<-> Client + SQL Server Compact

    All seem to work ok, except that with multiple users concurrently accessing the WCF service I get timeout and hangs and then have to restart IIS on the server.

    My code is based on the following articles:
    http://www.codeproject.com/KB/database/AdoSyncServicesArticalPKg.aspx
    http://www.codeproject.com/KB/smart/sync_services.aspx

    So I was wondering do I have to do explicit releasing and closing/disposing of syncing objects/services to force cleanups?

    Additionally if one client has synchronised then I try and sync another client it does not always get the data.

    Thanks
    Tuesday, October 20, 2009 2:50 PM
  • Your question seems like a very common one.  I'm surprised that the MSF team didn't provide a sample.  At least in the Sync 101 sample.  I'm looking for a simple example myself.
    Friday, October 23, 2009 2:34 AM
  • Hi Mark,

    Here are some resources you can look at:

    Documentation: "How to: Configure and Execute Collaborative Synchronization (SQL Server)"
    http://msdn.microsoft.com/en-us/library/dd918848(SQL.105).aspx

    Also a sample that demonstrates n-tier architecture can be found here:
    C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.0\Samples\WebSharingAppDemo-CEProviderEndToEnd

    As to your issue, we scale tested 200 concurrent through IIS so there shouldn't be issues. Without knowing more information, like logging, eventing, profiler, etc... it's hard to diagnose what went wrong on your side. There are couple of things you can try to get more info:

    1) Run trace verbosity level 4 using System.Diagnostics to determine what sync is doing when the connection hangs.
    2) Run sql profiler to determine what is happening on the database server during the hang
    3) Analyze WCF logs to determine what's happening on the mid-tier.

    Thanks,
    Nina

    Monday, November 30, 2009 7:54 PM
    Moderator