Safe distribution of the database to external clients
We want to distribute / synchronize data from our Datawarehouse (MS SQL Server) to external users (also MS SQL Server). The connection must be secure as we are dealing with reliable data. Data transfer from our system to an external client system must be done via http / https
In addition, it is possible that clients are still running their systems with the old database schema, so existing tables and columns should be transferred and non-existing ones should be ignored.
We will most likely have big database updates, and updates should come in near real time.
And it is definitely necessary that the data be stored in the datawarehouse / SQL database on the client side.
The whole process should also include good monitoring capabilities in case something goes wrong.
We started to develop our own .NET solution, but I thought it should be almost a common problem for communicating data between different systems.
Does anyone know of an existing solution that we can adapt to our scenario?
Any help is appreciated!
source to share
Just use regular SQL connections over a secure VPN or SSH tunnel. It should be very easy to set up for your networking guys.
For example, you can create a linked server. Then the scheduled SQL 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 sent encrypted over the Internet.
source to share
The problem is so common that SQL Server has a dedicated component: Service Broker . Instead of starting your own .Net and taking care of many issues (how are you going to handle downtime? Repeaters? Duplicates? Non-custom shipping? Authentication of non-domain computers? Routing for machines that change names? Transactional consistency. rollbacks? are you going to use dtc?). You can watch the demo I gave the SQL connection to see how you can easily scale SSB to over 1000 msgs / sec (1k) bandwidth on commodity hardware.
the only requirement is that all partitcipants must be at least SQL Server 2005 (no SSB in 2000).
source to share