snippetsqlMinor
How to PUSH data between a Local DB Server to Cloud DB Server?
Viewed 0 times
localcloudbetweenpushhowserverdata
Problem
I have an implementation doubt regarding replicating data between SQL Server 2014 database servers (Replicating data from Local Premises SQL Server to Cloud DB Server, which are not in same domain or VPN).
May I request you to please advise a solution, to achieve this data transfer push from Local DB Server to Cloud DB Server.
I will elaborate the scenario over here, what we are trying to achieve.
SCENARIO
My client has an ETL application developed in .NET which uses SQL Server as a backend.
They have their Live SQL Server hosted on a cloud provider (not Microsoft Azure)
They are opening up a new branch which does not have a leased line, since the area is on outskirts of the city. This new branch will have almost 1500 users who will make entries to a locally hosted application which will use local DB Server.
They have 2 SQL Servers:
They need to push the data entries inserted in local DB Server Database named as LabProdData to the Cloud DB Server Database LabProdData, so all other branches can refer to this branch data, with a certain delay of 30 minutes or less.
What are the options to implement this scenario?
SOLUTION PROPOSED
I have proposed a solution of ETL creation to open up a SQL Port on the Cloud DB server to facilitate communication to Local DB Server and to push data to the Cloud DB Server
But, this will be quite hectic to develop multiple ETL's since the number of tables are more.
Also, the Deletion and Updation of Data needs to be tracked and Synced, which will add up complexity
My client is bit hesitant about opening the port on Cloud DB Server, since this can be vulnerable to threats.
REQUEST SOLUTION
My doubts are:
May I request you to please advise a solution, to achieve this data transfer push from Local DB Server to Cloud DB Server.
I will elaborate the scenario over here, what we are trying to achieve.
SCENARIO
My client has an ETL application developed in .NET which uses SQL Server as a backend.
They have their Live SQL Server hosted on a cloud provider (not Microsoft Azure)
They are opening up a new branch which does not have a leased line, since the area is on outskirts of the city. This new branch will have almost 1500 users who will make entries to a locally hosted application which will use local DB Server.
They have 2 SQL Servers:
- SQL Server 2014 Web Edition Database on Cloud (not Azure)
- SQL Server 2014 Standard Edition with same Application database on Local Premises
They need to push the data entries inserted in local DB Server Database named as LabProdData to the Cloud DB Server Database LabProdData, so all other branches can refer to this branch data, with a certain delay of 30 minutes or less.
What are the options to implement this scenario?
SOLUTION PROPOSED
I have proposed a solution of ETL creation to open up a SQL Port on the Cloud DB server to facilitate communication to Local DB Server and to push data to the Cloud DB Server
But, this will be quite hectic to develop multiple ETL's since the number of tables are more.
Also, the Deletion and Updation of Data needs to be tracked and Synced, which will add up complexity
My client is bit hesitant about opening the port on Cloud DB Server, since this can be vulnerable to threats.
REQUEST SOLUTION
My doubts are:
- Can we use SQL Server Replication to replicate the data between Local SQL Server and Cloud DB Server, with no Active Directory or no common domain?
- If yes, how do we implement
Solution
I realize this is an old question but may come in handy for others facing similar dilemma.
Your client is correct. Do no open up ports to the world, as this is will create a security risk. You will want a site-to-site VPN between your back offices and "the cloud". Once this is implemented the "the cloud" SQL will be accessible as if it was in your LAN.
Your client is correct. Do no open up ports to the world, as this is will create a security risk. You will want a site-to-site VPN between your back offices and "the cloud". Once this is implemented the "the cloud" SQL will be accessible as if it was in your LAN.
Context
StackExchange Database Administrators Q#127799, answer score: 3
Revisions (0)
No revisions yet.