Data Transfer Question

[Guy]

I need to transfer on a frequent base (e.g. every 10 minutes or so will be a
parameter that can be set) for reporting purposes a subset (subset of records
of a subset of tables) of data from a MySql db (on a laptop/pc) to a Sql
Server db (on a server) via te Internet. The number of records (or the size
of the data) is not that much (maybe 5-10MB each time). During every data
transfer the new records must be added, modified records chnaged and deleted
records deleted on the server db. I thought using a web service to achieve
this, is this a good solution or are there better alternative solutions
(integration services on the laptop/PC would be no option, since I have to
minimize the the software to be installed on the pc/laptop)? I suppose lost
Internet connections or other errors that might happen can be tackled by
using transaction?

Thanks.

 

[William Vaughn]

Over the Internet? Without a VPN? I assume you're not concerned with data
security. I would concentrate on this in phases. First, I would get the
data transported to the server in a form that's packaged as an atomic unit
(as a single file) that either gets transferred or not. Next, I would focus
on importing the data into the local SQL Server database using SSIS, BCP or
SqlBulkCopy. Next, I would run one (or more) stored procedures to process
the incoming rows. This would include validation as well as posting the data
to appropriate tables.

If you were using a VPN connection, it would be a lot easier. You could use
ADO.NET 3.5 Sync Services to import the data and keep both ends in
sync--even to a MySQL database. This would deal with most (if not all) of
the unstable network issues for you.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Cor Ligthert[MVP]]

Guy,

Based on what I understand what you want to do can a webservice be a perfect
solution for your problem.

However you should have an open Internet Information Server to let the
webservice run.

(As somebody writes this to me, I agree that thus us not really an adonet
question)



Cor

 

[Guy]

thanks for the info.

i understand a VPN connection would be necessary when security is a vital
aspect. but, the solution i'm developing is in the sportssector atmosphere
where the data to be transfered is not really "sensitive" information.
Besides that it would be not possible sending the is data by using a VPN
connection, because the data is hosted with an normal ISP. So, integration
services would be no option either (since an ISP normally doesn't provide
these options with most service-plans). Maybe afterwards when setting up my
own environment (=server, etc.) VPN and integration services would be a
possible option.

regards,

Guy

 

[Guy]

anywhat, although this is not a pure ADO.NET problem but more a web services
question, many thanks.

I suppose the mentioned (5-10MB) data size would be no problem sending as a
DataSet to a webservice regarding performance, ...?

 

[William Vaughn]

So, anyway even without a VPN you'll need to collect the data and that's
still step 1. I might consider storing the data in a local SQL Server
Compact database and simply sync with a remote host. If you can't connect to
the host, then building a structure to send to an XML web service is
probably not a bad idea. I would try to figure out how to serialize the data
in something other than XML to make the performance a bit more tolerable.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Guy]

Thanks.

Indeed SQL Server Compact and syncing on the client pc/laptop side could
eventually also be an option that i will investigate.

Do you mean by "If you can't connect to

the host, then building a structure to send to an XML web service is
probably not a bad idea. I would try to figure out how to serialize the data
in something other than XML to make the performance a bit more tolerable." an alternative solution for the SQL Compact solution?

thanks.

 

[William Vaughn [MVP]]

I'm seeing more and more cases where this makes sense. With SQLCe you get
(another) free DBMS but one that's far lighter and easier to install than
SQL Server Express. See www.hitchhikerguides.net for info on my EBook on the
subject...

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________