Re: Offline scenario with low bandwith

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Darren Shaffer (darrenshaffer_at_discussions.microsoft.com)
Date: 01/26/05 

Date: Tue, 25 Jan 2005 17:02:15 -0700

Steve,

The idea of replicating a 40-60MB database across GPRS is not going to
perform well, regardless of your choice of RDA, Merge, or a custom
web-services-based
replication strategy. GPRS today has about a 40kbps bandwidth and this is
simply
too much data for that bandwidth. Here are some suggestions for you:

1. figure out how to deploy the initial (very large) SQL CE database to
your mobile
users pre-loaded with reference/static data. In most systems, this type of
data is the bulk
of what needs to be on device anyway. You can create your .SDF file once
loaded in this manner, include it as Content in your Visual Studio project,
and deploy it
along with your application to your users via CAB files or on memory cards.

2. create a web service on IIS that exposes methods to update the central
server
with datasets or diffgrams sent from the mobile users. only send what you
must over
GPRS to the central server. you'll have to manage conflicts yourself, but
two tips to
minimize these conflicts - 1) use GUIDs for everything unique across mobile
users and
2) try to architect in such a way that you can do "deletes" and "inserts"
rather than "updates"
on the central server. don't be intimidated by building your own web
services replication
strategy - it's not as big as it sounds and gives you very fine-grained
control over every aspect of
the data replication.

3. delete data that has been successfully sent to the central server from
the local devices
(if it is no longer needed on device). be sure to compact your SQL CE
database at frequent intervals
to reclaim unused space and re-calculate index statistics (improving
performance).

When Microsoft built a mobile client for their CRM product, they used a
hybrid approach
involving RDA and web services to address this issue. A case study
explaining that architecture
is available at: http://www.only4gurus.com/v3/download.asp?ID=5511 and more
detailed
coverage in an Oct 2004 .Net Developer's Journal article that covers it (you
must be
a subscribed to obtain it).

Darren Shaffer
Principal Architect
Connected Innovation

"Steve B." <steve_beauge@com.msn.Swap.dotcom.and.msn> wrote in message
news:41f6585c$0$20324$626a14ce@news.free.fr...
> Hello,
>
> I'm working on an application that will be based on Pocket PC devices for
> clients, and an office server.
>
> The central server will hold all data, and mobile users need to hold their
> own data and product catalog (up to 60 MB).
> When a user type something, it should be stored locally, and periodically,
> the updates need to be applied to the main system, while new products and
> chaged products are grabbed.
>
> I'm prototyping SQL CE to reach my goal, but I have some difficulties to
> understant the architecture. Here it is what I suppose :
>
> The main server runs IIS 6.0, SQL Server 2000 and SQL CE agent (configured
> and working).
> The mobile device run SQL Server CE.
>
> The application (written in C#) works directly in the local SQL Ce DB.
> When the network is available (typically when the user connect its device
> to a GPRS network), the Remote Data Access object uploads changed data
> (push), then drop table and download the entire table (pull).
>
> This process is not acceptable because of the heavy data that require to
> transit on network (actually the 40 MB).
>
> So I wonder if I missed something.
>
> 1. Is it possible to pull only required data instead of pulling the whole
> table (i.e. changed tables, deleted tables and insterted tables since the
> last sync)?
> 2. Where are located conflicted rows ?
> 3. How whould you do to reach my goal ?
>
> Hope that you'll help in getting answers.
>
> Thanks,
> Steve
>

Relevant Pages

  • Re: Offline scenario with low bandwith
    ... SQL replication transmits only changes. ... > with datasets or diffgrams sent from the mobile users. ... > GPRS to the central server. ...
    (microsoft.public.sqlserver.ce)
  • Re: Oyster Card
    ... easy to make bus-based readers connect to the base by GPRS every five ... readers to their central server. ... years to provide their employees and their employees' devices with VPN ... access over GPRS to their secure private networks. ...
    (uk.transport.london)
  • How to set MTU for GPRS Line
    ... Communication is GPRS. ... My merge replication fails after 5-8 minutes with an error: ... The only way to get the merge to work again, is to put the PocketPC in the ...
    (microsoft.public.pocketpc.developer)
  • Re: Oyster Card
    ... GPRS every five minutes to exchange relevant data with the central ... TfL has a secure private network in place to link the fixed Oyster ... readers to their central server. ... years to provide their employees and their employees' devices with VPN ...
    (uk.transport.london)
  • How do I "suspend" merge replication while I extract data from central table?
    ... Is there a standard practice to "hold" Merge replication so that the ... data collected at the central server (in a remote sales order ... SQL 2005 standard central server. ...
    (microsoft.public.sqlserver.replication)