I already spent some time thinking for a solution for my problem, but finally
can't decide what would be the most optimal and correct solution. Therefore I
post my question to this forum, to get your opinion.
On a regular basis (eg every 10 minutes or after user initiation) the
content of several MySQL database tables (on a pc/laptop client) need to be
transfered (over the Internet) (synced) with a SQL Server 2005 db (hosted on
a server) with the exact same structure as the MySQL tables. The size
of/number of rows of the different MySQL tables to be transfered is not that
impressive. Since the source MySQL db is the db of a third party application
I prefer not to add/change anything in this db, thus i want to only
reading/querying this db. The data that needs to be transfered is no
sensitive data security wise. My concern is also eventually a lost Internet
connection so that the db's are out of sync.
I see several possibilities to achieve this but to my opinion I think using
a web service, option 1 hereunder, would be the most interesting one:
1. passing a DataSet (of all the tables of the db that everytime needs to be
transfered at once) from the client to a webservice on the server where SQL
Server 2005 resides. Is this realistic, and can this assure the db's stay in
sync (by using transactions on the server side when loading all tables coming
from the DataSet)?
2. creating an (1) xml or plain ascii file containing all data from the
different tables on the client and transfering to the server, via a web
service, and processing this file?
3. querying the MySQL source db and directly inserting in the SQL Server db,
BUT this will probably have an impact when there's a lost Internet connection
since at that time a rollback on the server (due to the lost connection)
isn't possible anymore?
4. Using Synchronization Services for ADO.NET, but this would mean I need to
install an additional SQL Compact on the client, and transfering the MySQL
data to this SQL Compact db. And on its turn this SQL Compact synchronizes
with the SQL Server db on the server? Does my ISP has to take special action
to use Synchronization Services, since I have no impact on my ISP?
5. Other solution(s)?????
Many thanks for your info/feedback.
