1

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.

Improve this question
  • What about a SSH tunnel? – thorsten müller Mar 15 '16 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? – Ben Cottrell Mar 15 '16 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 '16 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 '16 at 11:13
  • What triggers the generation of the reports? How are the reports defined? Are they ad-hoc? – James Youngman Mar 15 '16 at 20:52
1

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.

Improve this answer
0

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.

Pro:

  • Vpn tunnel not required for each server.

Con:

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

Pro:

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

Con:

  • 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.
Improve this answer
  • 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 '16 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 '16 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 – Deepak Patil Mar 16 '16 at 6:04
  • Check Apache Kafka as an alternative. – Deepak Patil Mar 16 '16 at 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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