Near realtime sync of Sql Server and Sql Compact 3.5 in the realworld?

Discussion in 'microsoft.public.sqlserver.replication' started by benseb, Apr 27, 2010.

  1. benseb

    benseb Guest

    Hi

    I'm building a mobile application, whereby 10-15 mobile devices will
    be connected via a local Wifi network to an SQL Server 2008
    installation on a dedicated server.

    Our database contains around 50,000 records in one table (very simple,
    just 10 small varchar columns) with a primary key. These records are
    for entry control (barcode, redeemed=yes/no, date, etc)

    Each of the mobile devices will be checking this table for a barcode
    when scanned, then updating the table once verified (changing one byte
    column from 0 to 1)

    I need to keep all the devices up-to-date so if a barcode is updated
    on one, the others will see this change in data as quickly as
    possible.

    Up until now, I've had each of the mobile devices connecting directly
    to the SQL Server. This works fine in most cases, but should there be
    a network blip, or any other issue causing the database to become
    unavailable, the mobile device is rendered useless and this has caused
    problems when I've needed to restart the sql server or should the
    power fail (its at an event, so generators!). However it does mean all
    the data is 100% realtime as it's all reading from the same
    centralised database

    I've been looking at using a local SQL Compact 3.5 database on each
    device, which would be a local 'cache' of the data (it would contain
    an exact replica). Using Merge Replication I can quite simply run a
    command to sync the database on each local device using
    SqlCeReplication, with the central sql server 2008 device.

    Fantastic in theory!

    However, in the real-world, is this a good solution? I've seen lots of
    case-studies but most of these are for mobile sales reps who may sync
    their local data every hour or so. Ideally I'd need to keep mine
    updated to the minute (at most).

    So far, I've just tested syncing records and it always takes 4 seconds
    to run (init time) - regardless of how many records im updating.

    So, Would it be realistic to sync after every update that's run on the
    device (approx 15 times a minute). Or could I set it to sync when idle
    somehow in the background. Or is there a better solution?

    Cheers

    Ben
     
    benseb, Apr 27, 2010
    #1
    1. Advertisements

  2. benseb

    benseb Guest

    can anyone help?
     
    benseb, May 4, 2010
    #2
    1. Advertisements

  3. benseb

    ErikEJ Guest

    Given your requirements, I would keep the SQL Server direct connection
    solution, but fall back to some local storage (for example SQL Compact, but
    not merge replication), if the central server is not available. Then on the
    next scan attempt to connect and upload the current scanning and any cached
    scannings (removing or invalidating these from the local cache after
    succesful upload).
     
    ErikEJ, May 5, 2010
    #3
  4. benseb

    benseb Guest

    Hi Erik

    Thanks for this. Looking at it, Merge Replication looks like an
    overkill for this. I was therefore thinking of a real-time connection
    to the SQL SErver as you mentioned, with a failover to logging locally
    on the SQL Compact database. We would then need a way of syncing the
    offline changes back to the server. I would need to keep a mirror of
    the database locally so the barcodes can be checked against this when
    offline, but this can be out-of sync (ie an initial copy of the data,
    plus local changes, but minus changes from other devices)

    I would then need to push the changes made whilst offline back up to
    the live DB when online again. Couple of questions - I can flag any
    such transactions and do a batch update with client-side code, or I
    could use one of the replication frameworks such as RDA or Merge.
    Merge Replication seems an overkill as we don't need conflict
    resolution. However will RDA allow the granularity of pushing updated
    records to the server or can I only do total push/pull of all the
    data? I suppose using my code would eliminate to the need to have IIS
    in the loop to manage the replication.

    Ben
     
    benseb, May 17, 2010
    #4
  5. benseb

    ErikEJ Guest

    I would recommend you simply create your own sync mechanism, as your
    requirements are quite simple. RDA is simply "last in wins", so it may not
    be what you need.
     
    ErikEJ, May 17, 2010
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.