secure database distribution to external clients

Question

We want to distribute / synchronize data from our Datawarehouse (MS SQL Server) to external customers (also MS SQL Server). The connection has to be secure, because we are dealing with trusted data. Transmission of data from our system to external client system must be via the http/https

In addition it is possible that the clients still run their systems with an older database schema, so already existing tables and columns should be transmitted and non existing ones should be ignored.

Its most likely that we will have large database updates and the updates have to arrive in almost real-time.

And it is definitely necessary that the data is stored in a client side datawarehouse / SQL database.

The whole process should also include good monitoring possibilities in case something goes wrong.

We started to develop our own .NET solution but I thought it should be almost a common problem to exchange data between different systems.

Does anybody know about an existing solution which we can adapt to our scenario?

Any help is appreciated!

Answer 1

Just use regular SQL connections over a secure VPN or an SSH tunnel. Should be very easy to setup for your networking guys.

For example, you can create a linked server. Then a SQL scheduled job could move the data:

truncate table targetserver.dbname.dbo.tablename

insert into targetserver.dbname.dbo.tablename
select a, b, c
from dbname.dbo.sourcetable

Since the linked server talks to your server over a VPN or SSH tunnel, all data is send encrypted over the internet.

Answer 2

The problem is so common that it has a dedicated component in SQL Server: Service Broker. Rather than start your own .Net thing and take care of the many problems (how are you gonna handle down time? Retries? duplicates? out of order delivery? authentication of non-domain joined computers? routing for machines that change names? service upgrades? transactional consistency, rollbacks? are you gonna use dtc?). You can look at the demo I gave to SQL connections to see how you can easily scale SSB to a throughput of well over 1000 msgs/sec (1k payload) on commodity hardware.

the only requirement is that all partitcipants must be at least SQL Server 2005 (no SSB in 2000).