The question:
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?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
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.
Method 2
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0