SQL Server Local and Public Database

Question

We are running into a situation that need your expertise. Our company is a small consulting firm and we have been using our in-house ERP system (developed by Classic ASP and SQL Server), which is still running great.

Now we want to build external (public) website to communicate with customers on jobs/cases status. Of course the external public site will be using newer programming language like ASP.NET or PHP. The management team does not want to expose our sensitive data on the current SQL server to the world, and they worry about being hacked…etc.

There are two options:

• (1) hosting the new site on the cloud (e.g. Microsoft Azure)
• (2) build our own separate external network for publishing web and database servers (costly route).

IT team is leaning toward about hosting the new public site on the cloud and somehow synchronize data between the cloud and local SQL database. Since cloud database and local database are not in the same network, my worries (as a software developer) is that I will not be able to perform a lot of things. For example:

• When customer login the external site and create case, how can I create a new case record in our current local SQL database instantly?

• When an employee creates/updates the status of a case resides in our local database, how can it trigger to create/update record the public database?

• If I have automate scripts to mass synchronize, export, import, update, create… that in Scheduled Tasks, will there be big problems because cloud and local are separate networks.

Any suggestions or alternative solutions would be appreciated.

Answer 1

You can use Azure SQL Data Sync bi-directional synchronization to keep in synch your cloud database and your local database. This way when a customer creates a case on your external site all related records will be replicated to the local database (member database) with the latency and frequency that you want. The same will happen when a case is updated in your local database, all related records will be replicated to your cloud database (hub database).

You don't need to create your own scripts for the synchronization to happen. SQL Data Sync will take care of that. To get started with SQL Data Sync please visit this documentation.