7

I am currently working on a project which has to use WebSockets as a way of transferring data to my client. The infrastructure looks like this.
Client --> Web Server --> Microsoft SQL Database

I think that the most ideal situation would be like this: Client opens a socket to the Server. Server opens a socket to the Microsoft SQL Database. Whenever the database is updated (some data has been inserted) the DB writes the data to the socket. The server writes the data back to the client. This might be a bit tedious though, maybe I can somehow open a socket to the DB from the client directly?

I want to know if there is a way to automatically notify the socket to the web server if the MSSQL database is updated, so that it can process that information.

The main question is really; how will I make this work? I've looked into some projects which work with WebSockets like Node.JS and Socket.IO, also Tornado. Although I haven't found any clues as to where to look for this specific feature. I have found some rather unstable drivers for the MSSQL databases for NodeJS but do not understand if there is any way to make a socket to the DB and instantly send the data through the socket when it's pumped into the database.

I also realise that making a socket between client and db wouldn't be smart to say the least security wise, as for now that's not an issue, and that SQL is not the way to go for realtime applications but I'm bound to it for now :)

Edit 1:

Thanks to @tomfanning I now know of a solution to this problem but seriously doubt the improvement in performance. Let me picture the situation for you. In the case that I would use the Trigger on the MSSQL database I imagine this happening.

Situation 1

  1. The database gets updated
  2. The trigger is pulled
  3. The CLR script makes a connection to the Web Server. Either through a socket which it has to open and close for every trigger or through a HTTP(S) request which involves opening and closing a Header (which is useless overhead)
  4. The Web Server receives the trigger and emits the data to the client.
  5. The client updates.

And now imagine the same scenario but then with AJAX

Situation 2:

  1. A timeout of 1000ms is set in the client for AJAX requests
  2. The client time's out and makes an AJAX request
  3. The database executes some query and posts back the result
  4. The client updates.

In situation 1 you need both a request and a socket emit/receive and in situation 2 you would only need one request. If I were to set the timeout of the AJAX request to 10MS would it appear to the user as if it were a realtime application like a websocket? Or would situation 1 still be more efficient and I'm just exaggerating?

Thanks in advance!

Ruben Homs
  • 591
  • 1
  • 7
  • 21
  • 1
    If you can access your db over websockets in an unsecured manner, I can as well. If I can access your db, I will troll it for the lulz. Security is not a joke. – Raynos Dec 20 '11 at 00:39
  • @Raynos I know security is not a joke and I will definitely take it into consideration. But I just have to know whether there is a way to do this for now. – Ruben Homs Dec 20 '11 at 08:22

1 Answers1

4

Possible solution might be T-SQL triggers on INSERT or UPDATE with a CLR procedure which sends some notification to your main application, which then pumps data out to your client via websockets. Would avoid needing to poll the database.

Per your comment - not sure how AJAX would help you specifically in this case - because being a technology driven from the client (browser), your solution would again be polling, which I understand you want to avoid. WebSockets sounds like the right fit here because it gives you the ability to do true "push" from the server to the client without requiring a poll.

Obviously I'm talking in very general and theoretical terms here.

tomfanning
  • 9,552
  • 4
  • 50
  • 78
  • I indeed intend to avoid polling and do want to use WebSockets in my application. But imagine a trigger being pulled and the script being executed, the database would have to establish a connection to the Web server through either a socket or it would open the request as a link or something similar. That would mean it would have to build up a Header which I want to avoid due to the overhead. If I were to do an AJAX request from the client to the server it would take one request instead of 1 request + socket transfer right? – Ruben Homs Dec 20 '11 at 11:08
  • There's no way it's a sensible idea in any scenario I can think of to have any client code speaking to the database server directly. So you're always going to have some form of middleware, and therefore two connections in total (one between client and webserver, one between webserver and database server) instead of one, regardless of technology. And if you initiate your updates *from* the client side, you're always going to be in a polling model, which will be less scalable than a push model. If you want a true push model, you need to initiate the operation somehow from the database end. – tomfanning Dec 20 '11 at 12:56
  • 1
    I've just thought, as well, an INSERT or UPDATE trigger in the database would open only one connection to the web app, which could in turn know about lots (tens, thousands..) of clients that need to receive the push notification via WebSockets. That's tons better than having thousands of clients sending periodic AJAX requests to poll for updates, which would in turn put high pressure on the database. – tomfanning Dec 20 '11 at 13:02
  • I agree fully and understand that, but what about performance? Would the T-SQL Trigger model be more efficient then the AJAX request polling method? – Ruben Homs Dec 20 '11 at 13:05
  • 1
    In my opinion, yes, most probably. It's got to be better to have one message from the database saying "I have new data" sent to the web app, and have the web app say to the clients "here's some new data", than to have multiple clients making periodic requests to the web app asking "have you got new data?", causing the web app (for EVERY client request) to ask the database "have you got new data?", for it to reply "no" most of the time - a message that needs to be sent to every client. Far less traffic would be passed in total. Also your app should end up more responsive. You should try it out! – tomfanning Dec 20 '11 at 13:12
  • Thank you very much. You took away any doubt I had about the possibilities. Especially because mixing these new-age techniques is something new for me. I'm going to give it a whirl! :) – Ruben Homs Dec 20 '11 at 13:22
  • +1 for @tomfanning's solution. MSSQL -> CLR Trigger (I like to jump out of MSSQL CLR as quickly as possible) -> Message Queue (MSMQ?) -> WebSocket server or push to [hosted realtime web services](http://www.leggetter.co.uk/real-time-web-technologies-guide#hosted-services) to distribute the update to connected clients. – leggetter Dec 20 '11 at 16:14
  • @leggetter I'm not that familiar with C# or any other .NET language for that matter, so how does MSQM work? Does the server has to poll for new messages in the queue or does the queue push them through an open socket to the server? – Ruben Homs Dec 21 '11 at 09:40
  • You make a blocking call when you listen on the queue. The call only returns when a new messages is available to be read. You can see one example of this [here](https://github.com/leggetter/Kwwika-Components/blob/master/Kwwika.QueueComponents/MessageQueueReadProcessor.cs#L89) but there will be simpler examples elsewhere like [this](http://www.dotnetfunda.com/articles/article1119-sending-and-receiving-message-in-msmq-using-csharp.aspx). Your CLR code could make a web request to a server which then in turn pushes the message. – leggetter Dec 21 '11 at 15:10
  • @leggetter Just wanted to say thank you, this indeed worked perfectly fast and reliable and was the perfect solution for my problem! – Ruben Homs Jan 29 '12 at 16:23