Transfer data between SQL database servers securely by end to end service

Question

Hi
We have two SQL server databases in two different location that belongs to two different companies.
I want to transfer a few table data (about a million records) from source database server to destination (Master) server. (Cumulative data of 30 companies).
By the way, for the security reasons these SQL servers have no direct access to each others and SQL ports are behind the firewalls and they have not each other SQL logins too.
Actually, I already had deployed this scenario with SQL Server Linked Server but they rejected it for security as mentioned before. So using Bulk Copy, SSIS, … because of direct access to SQL Server ports is not acceptable.
I consider using WCF service (or Web service) on both sides to exchange data between servers but I'm not sure to be the best practice (BTW, WCF is somehow deprecated)
It's necessary to have secure connection like HTTPS or any SSL connection and better to have compression or sending binary data (instead of plain text).
(Other vendor already implemented it with RabbitMQ but with some performance problems.)
I would be grateful to recommend the best practices for this scenario.

Answer 1

This transfer will be repeated once a day. It's better to transfer only new or updated data but it's possible to delete all old data in destination server and retransfer it.

Answer 2

You could make use of Azure Data Sync to enable this. Using this data can be shared both ways if required, but easier if it is a one way move.

You will need to create an additional Azure SQL Database to use this, but the on-premises databases only need to have an agent installed. The movement of traffic between the source databases and the Azure management database is always secure and doesn't need firewall rules other than http.

See https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database

Answer 3

Thanks a lot,
The main question is, what is the best way to copy lots of records from a server to another server daily without knowing the servers about each other logins information.
Consider the below items please!
1-The Databases are SQL Server 2014
2-The servers have not each other logins (so it may be like two service in each sides with their own logins to databases that negotiate with each others).
3-The data should be transferred over the internet so the embedded support of security and compression is a privilege.
For example using WCF Service, end to end Web API to transfer data and bulk copy to save it to destination server, … .
Thanks

Answer 4

Since you don't have access to the SQL Server directly, your simplest approach is probably to simply export all the data to CSV files, compress/encrypt them, and upload them to a FTP or cloud site.