nikhil_satam avatar image
nikhil_satam asked

SQL Server 2014 Replication to push data from Local DB to Cloud DB

Hello Sir, Hope you are doing fine. I am writing this email to ask you 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 a 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 need to push the data entries inserted in local DB Server to the Cloud DB Server, so all other branches can refer to this branch data, with a certain delay of 30 minutes or less. SQL Server Versions Cloud DB: SQL Server 2014 Web Edition Local Premises DB: SQL Server 2014 Standard Edition 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 doubt is, 1. 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? 2. If yes, how do we implement this scenario using SQL Server Replication? 3. Is there any other way to implement this scenario, other than Replication? Can you please suggest. 4. Can Replication or Database Mirroring help here and can it be implemented on 2 different SQL Server editions (Web and Standard) Please do let me know if you need any further details to understand the problem statement. Eagerly awaiting for your reply. Thanks in advance!!
replicationsql server 2014etl
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

perrywhittle avatar image
perrywhittle answered
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nikhil_satam avatar image nikhil_satam commented ·
Thanks Perry. I did tried configuring Transactional replication between my Local DB Server (SQL Server 2014 Standard) and Data Center DB Server (SQL Server 2014 Web Edition). My Local DB Server is able to connect to the Data Center/Cloud DB Server through SQL Server Management Studio. But my Cloud DB Server is not able to connect to the Local DB Server (which has a public IP natted on Firewall). The Public IP of the Local DB Server can be pinged successfully from my Cloud DB Server. Can you please help me with how should I configure the Publication from my Local DB Server to allow the Cloud DB Server to subscribe the publication/data. Cloud DB Server must be the Subscriber/Target/Receiver and Local DB Server must be the Publisher/Source. Thanks in advance for your help!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.