I'm not sure what to title this question or if this is the right place to ask it. After Googling and continually coming up empty, I'm turning here as a last resort.

I've developed a SAAS web application that generates complex reports for our clients. The reports are generated by pulling data from our client's SQL Service instance. Currently we access SQL Server via a VPN.

Having full network access to multiple clients server via the VPN is a security risk that we would like to move away from. My idea is to write an agent that will run as a Windows service that will connect to our servers and wait for requests for data from the SQL server instance to which it will have access via the local network.

My question is how to go about implementing the connection between the Windows service and my servers. The primary requirement is that it be always connected and ready to serve data from SQL Server. One option I've come across is using RabbitMQ in a RPC set up, but this is about the only solution I've come across. What are other viable ways to implement this kind of solution?

I feel like this isn't an altogether unusual requirement but have found very little information online pointing me in the right direction.

  • What about a SSH tunnel? Mar 15, 2016 at 10:27
  • Does RabbitMQ meet all of your requirements? Would it be an acceptable solution or are there things you dislike about it? (if so, what do you dislike?) What are you looking for in addition so far as alternatives go? If it doesn't meet your requirements, what is RabbitMQ lacking that you need? Mar 15, 2016 at 10:57
  • @thorstenmüller With an SSH tunnel, there would still need to be some kind of process to ensure that it stays open. One concern would be scalability. We currently access around 50 different SQL Server instances and that will continue to grow as we gain popularity.
    – Icode4food
    Mar 15, 2016 at 11:11
  • @BenCottrell I've worked with RabbitMQ in the past so I do have a bit of experience. One concern is the security of having RabbitMQ internet accessible. I'm interested in alternatives as this is literally about the only solution I've come across online.
    – Icode4food
    Mar 15, 2016 at 11:13
  • What triggers the generation of the reports? How are the reports defined? Are they ad-hoc? Mar 15, 2016 at 20:52

2 Answers 2


Is there anything preventing you from using a traditional TCP socket connection? You might also be able to get away with a websocket (HTML5 - also built on top of TCP) solution. You didn't mention what language you are developing in but my guess is its either Java or C# and in that case, you will find thousands of tutorials, code samples, and pre-built libraries available on the internet to help get it up and running.

It would be up to you to appropriately encrypt the data you would want to send across the internet but again, I'm 100% positive you would be able to find examples of common encryption algorithms as well.

As for scalability, with constant connections to all clients, you are going to have to eventually put more servers online to handle the extra load from connected clients. You will run into that issue with any solution you end up implementing (including your current VPN solution as well). In fact, you will probably hit the limits of your VPN solution sooner than you would with any sort of traditional client/server architecture.


Approach 1 (Traditional) :

  • All Sql Server instances has publishers demon which responds to the request sent over sftp directory based on file watcher approach.
  • Similar instance then be running at consumer side who is waiting on response from the publisher.
  • Use Regex pattern which precisely defines client along with request resolvers. Refrain from embedding any business logic in it.

Note : Publishers coukd be your Windows service.


  • Vpn tunnel not required for each server.


  • Each Sql server would need a publisher installation which adds up additional maintenance.

Approach 2:

  • Use a queue framework (rabbit/Kafka)
  • Centralised Consumer demon takes the request from services and sends it to q.
  • Publisher demon installed on all the Sql server instances reads the message and responds to data based on request specified to intended server.
  • Consumer demon receives the response and associates it with appropriate service request.


I don't think an. It just looks over engineering.


  • With this approach you still need a tunnel to communicate.
  • Maintenance since publisher is across the clients.
  • You need to spend good amount of time in managing the q request/response architecture since there are lot of corner cases to handle at both publisher/consumer and between consumer and Web service thread.

Approach 3:

  • Use Spring JDBC to create multiple database connections which would be initialized along with service (connection pooling has to be part of this implementation). This is a one Centralised solution which easy to maintain but again requires the Vpn.
  • Approach 1: Since reports can be generated ad-hoc, polling the sftp server every second doesn't seem like a scalable solution. Approach 2: As I mentioned in my question, this is about the only solution I've come across, I'm looking for alternatives. Approach 3: That is more or less what we are currently doing that I'm trying to get away from.
    – Icode4food
    Mar 16, 2016 at 5:57
  • With approach 2 there shouldn't be a need for a tunnel as I'm picturing connecting rabbitmq directly over the internet, not via any other tunnel.
    – Icode4food
    Mar 16, 2016 at 6:03
  • You are trying to connect the private domain from outside. There is no real way apart of either go for vpn or go over Internet. If you can go over the Internet then instead of sftp you can create rest layer as well along Mar 16, 2016 at 6:04
  • Check Apache Kafka as an alternative. Mar 16, 2016 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.