Offline scenario with low bandwith

[Steve B.]

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

 

[Enrico Bizzarri]

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)?

I think you can't, one solution is to store data in the max number of tables
you can (e.g. ordersHead, ordersBody, clients, newClients, oldClients, areas
etc. etc.) So you can transmit only the tables you need.

2. Where are located conflicted rows ?

When you pull/push a table you can set another table where conflicted rows
are stored (e.g. err_myTable), and then you have to manage them.

3. How whould you do to reach my goal ?

Limiting the amount of data to transmit to the bone. Creating a 2 way sync,
one via GPRS for small data and the second via USB(activesync) for the main
stream to do once a week.

HTH

Enrico

 

[Steve B.]

Thanks for the answer, I'll take a look.
However I'm not sure it will be possible to donwload only some table, since
many tables changes often, even for a single row. That makes the full pull
required...

Steve

 

[Steve B.]

And what about replication?
Does replication send all datas ?
Is it possible to replicate only a subset of rows (i.e. only user-specific
datas)

Thanks,
Steve

 

[Enrico Bizzarri]

And what about replication?

Replication is out of my knowledge

Sorry.

 

[Guest]

Take a look at the following samples for Smart Client (off-line scenarious)

http://msdn.microsoft.com/smartclient/codesamples/werock/default.aspx

http://www.microsoft.com/downloads/...20-6e92-4f90-87dd-425094637c49&displaylang=en

 

[Steve B.]

Thanks all for your answers.

Here are results of my investigation, FYI:

SQL replication transmits only changes.
SQL replication allows filtering data horizontally, using some builtin
functions, like SUSER_SNAME to work with a subset of data.

So the first time the application is launched, user-specific data and common
data are downloaded. Next times, the replication mechanisms detect changes
in subscribers and publisher to exchange only the required data.

That answers all of my questions.

Steve