Synchronizing SQL Server CE without replication

[Matt]

Hello,

I currently have a Pocket PC 2000 application and a .Net Windows
application that work together to retrieve data from an SQL 2000
database.

The desktop application connects to the SQL server 2000 and extracts
the data required for the day by serializing the dataset to an XML
file.
This XML file is then sent to the Pocket PC using Active Sync.
The user then runs the Pocket PC application which deserializes the XML
file back to a dataset on startup (this can take a while for larger XML
files).

It works like this for two main reasons.

1) The Pocket PC will always be used offline.
2) Using a single desktop to communicate to the SQL Server means only
one SQL Server license is required rather than one for each Pocket PC.

The problem is that when the Pocket PC application deserializes the XML
file to a dataset on startup it can take a rather long time.

To overcome the need to load all of the data into memory on startup I
have decided to use SQL Server CE.
This way the Pocket PC application would just get the data as it
needed.

My question is how to get the SQL Server 2000 data required into the
SQL Server CE database on the Pocket PC by using the desktop
application.

All the MSDN articles I have seen use replication between the SQL
Server 2000 and SQL Server CE directly which I don't want to do because
of the licensing costs.

Perhaps there is some way to turn a dataset into an SQL Server CE
database and then attach that to SQL Server CE on the Pocket PC?

Perhaps there is some way to connect to the SQL Server CE database on
the Pocket PC and populate it via Active Sync?

Thanks in advance.

Matt

 

[Ginny Caughey [MVP]]

Matt,

Generally people do use the XML intermediate format as you're doing if they
don't want to use merge replicztion or RDA (or aren't able to for some
reason.) You might consider Text Delimited files as a smaller, faster
alternative to XML. There is a TextDataAdapter on www.opennetcf.org that you
could use. There are also third party approaches: RemoteSqlCe from
www.gui-innovations.com and SqlCE Console from www.primeworks.pt. I should
also point out that if the SqlCE data from your mobile devices is used by
SQL 2000, even if you don't connect directly using merge replication, you'd
still need CALs for each mobile device unless your SQL Server uses
per-processor licensing from what I understand.

Ginny Caughey
..Net Compact Framework MVP

 

[Matt]

Hi Ginny,

Thanks for your reply.
For now I am not looking to update the SQL 2000 Server with data from
the SQL CE Server on the Pocket PC.
I am simply looking to populate the SQL CE Server on the Pocket PC with
data from the SQL 2000 Server so it is a one way thing.
It is done via an intermediary process in the desktop application which
gets the data from the SQL 2000 server using the single CAL.
The desktop application then has the data in its own form which has
nothing to do with SQL Server 2000 and it needs to put that data into
an SQL CE database that can be used on the Pocket PC.

This link http://www.microsoft.com/sql/ce/howtobuy/default.asp says
"You can deploy SQL Server CE to an unlimited number of mobile devices
provided that they operate in stand-alone mode. In stand-alone mode,
the device does not connect to or use the resources of any SQL Server
system not present on the device."

My Pocket PC application does not connect to or use the resources of
the SQL Server 2000.
It connects to and uses the resources of the desktop application.

Maybe I have confused things by mentioning SQL Server 2000.
If I simply said I wanted to take some data from my desktop application
and put it into a SQL Server CE database
on the Pocket PC using the Activive Sync link, how would it best be
done?

I shall certainly check out the third party links you have provided.
Of course my company would prefer not to spend on components.

Thanks,

Matt

 

[Ginny Caughey [MVP]]

Matt,

I'm not an expert on SQL Server licensing, so I'll just leave it at that.
And considering what you're doing, perhaps XML is the best choice.

Ginny

Without SQL Server in the discussion, I'd still consider delimited text
files

 

[Ilya Tumanov [MS]]

I'm afraid you're mistaken about SQL 2000 licensing model.

Unless you're on per CPU license, you need as many CALs for SQL 2000 as
many clients are accessing/updating data in SQL 2000 regardless of data
path.
Client in this case is a single PPC device, even though it never connects
to SQL 2000 directly.

Since there are no licensing advantages, I would suggest using
replication/RDA.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

 

[Matt]

Hi IIya,

Logically this licensing thing makes no sense to me.
Effectively you seem to be saying that if I have an application that
prints some data to a peice of paper and
I then take that peice of paper and type the data on it into an
application which places the data into an SQL Server database
I need a CAL for every paper printing application that is running?

Please help before I tear my hair out.

Thanks,

Matt

 

[Ginny Caughey [MVP]]

Matt,

I think the piece you're missing is that your PocketPCs would be running
SqlCe databases that store data that came from SQL Server.

Ginny Caughey
..Net Compact Framework MVP

 

[Ilya Tumanov [MS]]

Legal documents rarely make sense to persons like you and me - without
legal background.
However, you should admit this license is here to prevent exactly what you
were going to do - use many clients with just one CAL via proxy.
Should it be allowed by the license, Microsoft would be having tough times
selling more than one CAL with any copy of SQL 2000.
Indeed, it's pretty easy to come up with some sort of proxy which would
handle many clients with technically just one connection to SQL 2000.

Using paper, pigeon mail, smoke signals and so on is probably also
restricted by the license as long as data is transferred between clients
and SQL Server.
However, since it does not make any sense in practice, it's more of the
theoretical interest.

Also note this is SQL 2000 licensing and it has nothing to do with SQL CE.
You can use Java with mobile version of DB2 on Palm OS and it still will be
considered a client if it fetches data from SQL 2000, directly or
indirectly.

I'm sorry I can't help you with that as I'm a software developer and not an
attorney. I'm pretty much just repeating what I've been told.
If you have concerns about this issue, you should consult an attorney/legal
department.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------

 

[Matt]

Hi Ginny & IIya,

Thanks for your input.
I see your point about Microsoft needing to protect themselves from the
kind of thing I am doing.
There's just something not quite right about it to me.

Can I just clarify then (ignoring SQL CE) that if I take create a
dataset from SQL Server 2000 and serialise it to XML using a desktop
application.
Then copy that XML file to memory card and use it in my Pocket PC, I
will require a client license for the desktop and one for the Pocket
PC?

If the answer is yes then I'll have to tell my techies and project
managers so they make sure our clients will be using the correct number
of CALs.

It would seem then that without some simple way to create an SQL CE
database on the desktop and without any licensing gains I may as well
use replication or RDA as you say.
I'll look into it.

Thanks.

 

[Paul G. Tobey [eMVP]]

You can create SQL CE databases on the desktop, via the emulator. This has
nothing to do with the license situation, of course.

Paul T.

 

[Dick Grier]

Hi,
Then copy that XML file to memory card and use it in my Pocket PC, I
will require a client license for the desktop and one for the Pocket
PC?

If the answer is yes then I'll have to tell my techies and project
managers so they make sure our clients will be using the correct number
of CALs.
<<

We're just "jailhouse lawyers" here. The answer probably is, "Yes." (From a
legal sense; it depends on what your definition of 'is' is.)
It would seem then that without some simple way to create an SQL CE
database on the desktop and without any licensing gains I may as well
use replication or RDA as you say.
I'll look into it.
<<

I think this depends on decisions of cost/scale that go beyond what we can
know. However, lots of folks face these issues. If it were up to many
programmers (read: anarchists), it would be free. Its hard to build a
business model on free, though some try.

Dick

--
Richard Grier (Microsoft Visual Basic MVP)

See www.hardandsoftware.net for contact information.

Author of Visual Basic Programmer's Guide to Serial Communications, 4th
Edition ISBN 1-890422-28-2 (391 pages) published July 2004. See
www.mabry.com/vbpgser4 to order.

 

[Ginny Caughey [MVP]]

Matt,

It sounds to me like you understand the licensing now. I know it's
confusing, because we do get questions about it fairly often. It's easier
with, for example, the Small Business Server. I have one of those, so I've
had to learn how the CALs work to make sure I'm doing it right, but
fortunately SBS offers the option of user CALs so all you have to do is
count people who can log in. SBS also offers machine CALs too (more like SQL
Server) but there you have to count all the machines including any mobile
devices that might ever need to connect, even indirectly.

Be sure to consider per-processor licensing for your SQL Server machine,
especially if you have more than a few devices. It would be much less of an
administrative headache and might end up costing less money too.

Then once you get your licensing plan in place, you can start looking at
merge replication and RDA, which sounds like good options for what you're
doing.

Ginny