Tunneling SQL queries through central server



  • 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?



  • There's no such "tunneling" feature built-in to SQL server.

    The closest you can come is to setup https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-ver15 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 https://docs.microsoft.com/en-us/sql/relational-databases/synonyms/synonyms-database-engine?view=sql-server-ver15 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.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2