title: How to notify a windows service(c#) of a DB Table Change(sql 2005)?
body: <p>I have a table with a heavy load(many inserts/updates/deletes) in a SQL2005 database. I'd like to do some post processing for all these changes in as close to real time as possible(asynchronously so as not to lock the table in any way). I've looked a number of possible solutions but just can't seem to find that one neat solution that feels right.</p>
  
  <p>The kind of post processing is fairly heavy as well, so much so that the windows listener service is actually going to pass the processing over to a number of machines. However this part of the application is already up and running, completetly asynchronous, and not what I need help with - I just wanted to mention this simply because it affects the design decision in that we couldn't just load up some CLR object in the DB to complete the processing.</p>
  
  <p>So, The simple problem remains: data changes in a table, I want to do some processing in c# code on a remote server.</p>
  
  <p>At present we've come up with using a sql trigger, which executes "xp_cmdshell" to lauch an exe which raises an event which the windows service is listening for. This just feels bad.</p>
  
  <p>However, other solutions I've looked at online feel rather convoluted too. For instance setting up SQLCacheDependancy also involves having to setup Service broker. Another possible solution is to use a CLR trigger, which can call a webservice, but this has so many warnings online about it being a bad way to go about it, especially when performance is critical.</p>
  
  <p>Idealy we wouldn't depnd on the table changes but would rather intercept the call inside our application and notify the service from there, unfortunately though we have some legacy applications making changes to the data too, and monitoring the table is the only centralised place at the moment.</p>
  
  <p>Any help would be most appreciated.</p>
  
  <p>Summary:</p>
  
  <ul>
  <li>Need to respond to table data changes in real time</li>
  <li>Performance is critical</li>
  <li>High volume of traffic is expected</li>
  <li>Polling and scheduled tasks are not an option(or real time)</li>
  <li>Implementing service broker too big (but might be only solution?)</li>
  <li>CLR code is not yet ruled out, but needs to be perfomant if suggested</li>
  <li>Listener / monitor may be remote machine(likely to be same phyisical network)</li>
  </ul>