Search code examples
sqlsql-serverdatabaseservice-brokerclrstoredprocedure

Sql Service Broker, CLR Intergration, Triggers back end problem

I'm looking for the most efficient way to build a backend for storing/processing data. Basically, data is sent to a server, parsed then saved to a db. Some processing is then done on it based on other data in the db and then alerts are raised via email or sms.

The platform is .NET and the DB is SQL Server 2005 or maybe 2008.

I.e.

  1. A temperature sensor sends 4 bytes of data to a server.
  2. Server converts data to a real value say 20.
  3. The value is then saved to a SQL server db.
  4. The value is then checked against a row in table which has bounds set for that sensor i.e 0-50
  5. If outside the bounds an alert is raised. (Sent via SMS or email.)

This all seems pretty straight forward but I'm looking for the best way to do it, given that the ideal scenario is that it all happens in 'real time' and it could be potentially 100's or 1000's of requests per second. I wanted to leverage some of the 'new' features of SQL 2005/08 such as the Service Broker, CLR integration, triggers etc of which I have little experience.

Steps 1 & 2 have already been completed.

Would it be wise to use the Service broker or MSMQ considering the number of Transactions for queuing? At what point do I process the alert data given that I need to do a look up on the boundary data? I have some ideas how I would like the process the data but am unsure of the best technology / methodology to use.

My idea is (starting at step 3) is to submit the data to the Service broker which in turn calls a CLR procedure to process the 'business logic' on the data. Or do I use a trigger to add the data to the Service broker to process the data then? Can the Service broker call a CLR procedure directly? Is using the service broker even the right idea given that I want to process the data more event driven rather than polling?

From the examples I have seen on the Service Broker it looks as though you need have code to receive the data, where as all I really want to do is add the data to a queue and have the queue emptied automatically (processing the alert data as it does so).

I could do all of this via a standard stored procedure but I would like to use stored procedures minimally and instead use CLR integration as the business logic will be a lot more complex than in the example.

Given that Service broker handles queuing and threading I thought it could be a good candidate for calling a CLR procedure to process the alert data and send the sms or email?

Please show me the light! Thanks!

Solution

  • It sounds like you may want to have a look at Streaminsight http://www.microsoft.com/sqlserver/2008/en/us/r2-complex-event.aspx. I can't say I know too much about it but if you google Allan Mitchell and Streaminsight or have a look on SQLIS.com he has done a number of demo videos using it.