Using ASP .NET to Sync 2 databases

[Si]

Hi

I am attempting to devise a web solution for my company:

We need to develop a website that will be hosted by a remote webhost that
will hold small amounts of confidential client data which can be updated by
the client using an SSL login.

Our local SQL server 2000 database holds the same data plus extra client
data. We need to update both databases this data hourly, whilst only
transferring modified data (to save bandwidth).

As both the web database (on the hosts) and the local database can be
updated we need to syncronise the data carefully.

Our webhost can provide either SQL Server 7, MySQL or Access databases for
us. Is there any way we can securely syncronise this data, security is our
main concern. As our website uses SSL for client logins: would it be
possible and secure for our server to somehow connect to the website using
ASP.NET and retreve the fields that need updating on our local server. Then
update the fields that need to be updated on the webserver.

Thanks Si

 

[Dino Chiesa [Microsoft]]

Does your hosting service allow VPN? If so you can stand up a VPN pipe
(which is secure) between your local server and the hosted database, and
perform SQL replication across it.

Also, are you aware of the Data Transformation Services tools shipped in the
various editions of SQL Server 2000? You can define a transformation, save
it, then set it to run periodically. It works with any ODBC source (don't
need SQL on either end!). No code to write. I don't know how it handles the
case where databases at both ends are being updated concurrently with
potentially conflicting data.

As for the possibilities of utilizing SSL and ASP.NET, one way to approach
this is to rely on the ADO.NET Dataset. This of course involves writing
code. You could write a web service (accessible only via SSL) that returns
a dataset; run this service on the hosted server. Then, on your local
server, build an app that is scheduled to run hourly (via Schtasks.exe).
Within that synchronizer app, grab the dataset from the hosted server, then
also grab a datatset from the local db, and merge them.
http://www.c-sharpcorner.com/FAQ/MultiTablesViewData.asp

Then you can update the local DB with the result of the merge; you can also
send back the merged DataSet to the webservice, and have similar logic on
the webservice (remember, it is running on the hosted server) to update the
database with the merged data. The synchronizer does not need to present a
GUI - it could in fact be a "daemon" type app that runs "windowless" and
only logs an event on completion.

The pitfall here: because there is a window between the time you take the
snapshot on the hosted server, and the time you postback the updated
dataset, there's a chance you will get stale data. The DataSet is built
with optimistic concurrency to presume that this problem will not occur, and
when it does occur, you need to take special steps (exception handling). In
other wordsif the database is updated in the "window", the updated data in
the database will not be overwritten when you call Update() on the merged
DS. But you will need to perform exception handling on each row that did
not et the merge.

Depending on your scenario, this may not be a problem. Maybe you just want
an hour, and get the updated data at the next synchronization. On the other
hand, maybe your scenario doesn't permit this sort of looseness.