Near realtime sync of Sql Server and Sql Compact 3.5 in the real world?
benseb
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
can anyone help?
ErikEJ
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).
--
Erik Ejlskov Jensen, Mobile App Dev MCTS
Check out my SQL Compact blog at
http://erikej.blogspot.com
"benseb" <ben...@gmail.com> wrote in message
news:5dedbef3-c66d-430d...@k41g2000yqf.googlegroups.com...
benseb
> 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).
>
> --
> Erik Ejlskov Jensen, Mobile App Dev MCTS
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
ErikEJ
requirements are quite simple. RDA is simply "last in wins", so it may not
be what you need.
--
Erik Ejlskov Jensen, Mobile App Dev MCTS
Check out my SQL Compact blog at
http://erikej.blogspot.com
"benseb" <b...@skiddle.com> wrote in message
news:804f7b53-2e4c-4160...@q8g2000vbm.googlegroups.com...