All we need is an easy explanation of the problem, so here it is.
The company I work for has multiple branch offices over the globe, but each is only connected through VPN/EVPL to our main office.
Office A ------- Main Office ------ Office B
What is the best way to query data from an MSSQL DB that is located in Office A from Office B?
My first approach was writing stored procedures on the main office SQL, like "QueryDataB" or "QueryDataA", but this is very tedious and I bet there is a better way, like tunneling a connection through or something like this?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
There’s no such "tunneling" feature built-in to SQL server.
The closest you can come is to setup Linked Servers on each branch office’s SQL Server to point to the central SQL Server. You will probably shy away from this after you evaluated it, due to performance, management overhead etc.
If you still want to stick with Linked Servers, then you might want to define Synonyms on each branch server for each relevant table pointing to the table on the central server. I.e., it will look like, from a naming perspective, that the table is local.
Again, after you have evaluated above, you will likely go for something more "performant" and with less management overhead: I.e., talk to your network admin, as suggested by vonPryz.
I would recommend build a data warehouse server in your main office or in the cloud which will contain detailed or aggregated (depending on needs) data from all offices.
Such solution will allow you for easy comparison of data between the offices and minimize amount of data that needs to be transferred over VPNs. Also it will speed up all reports than needs data from more than one office.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂