Synchronizing SQL Data with or without a VPN

Question

I have written a Windows application that stores data locally in a SQL Server Express database. It is used by multiple people from remote locations who need to synchronize their data occassionally. It requires a fairly simple synchronization process of only a handful of tables and I'm currently accomplishing this by linking each of the SQL Express installations to the "central" SQL Server using a linked server. I then run queries against the remote data and add/update/delete as needed. This works really well.

However, I'm having a difficult time with the VPN connection that is required to make this happen. Before beginning the synchronization process, the first thing the user does is establish a VPN connection with the server (running RRAS on Windows 2003). It works well most of the time, but I run into lots of irritating problems such as the VPN dropping if the connection isn't stable, often the AT&T cellular connection fails to establish a VPN connection (remote users sometimes HAVE to use this method), and one user has two machines at home and only one can have a VPN connection at a given time due to a router limitation. Some run XP, other Vista...it's really a pain and the VPN piece is proving to be very frustrating for me and the users.

Do I have any fairly simple options that I'm overlooking? My first thought is to use a webservice, but that would require passing all of my data to the server, through the webservice, for processing. That's not practical. Another thought is to install a second instance of SQL Server Express on the server, have it listen on a different port, and then connect directly this rather than through the VPN (I'm not even sure this will work with SQL Express. Will it?). I know this has security implications, but how much so? The data is not confidential in nature, nor mission critical. Are there better options that can be done with a minimal amount of work/learning?

Thanks in advance for any suggestions you might have.

Darvis

Answer 1

http://www.stunnel.org/

Provides a wrapper that encapsulates a network connection in SSL encryption.

Answer 2

You could use email with your data as an attachment. You can process emails at your server with an automated agent. Distributing updates the other way might be more difficult - your user would have to save your attachement and then run a local importer. Plus the timing issues of when they decide to do this could be tricky to work out. But it might work better depending on your user base.

Answer 3

How about Service Broker? You stuck your updates into an XML payload, use SEND to upload the changes, the message activates a procedure on your back end, it sherds the XML payload back into table updates. You can put an Express instance on the DMZ to act as gateway and rely on the built-in Message Forwarding. Since Service Broker can use certificates for authentication, no VPN is required. Also you solve the problems of availability, the updates are queued up and delivered whenever there is connectivity.