none
Receive "DML Events" in applications without polling RRS feed

  • Question

  • I posted this in the TSQL forum as well, but have not gotten an answer and upon further reflection think this is a more general sql server question as opposed to TSQL specific.

     

    We have a .NET application which needs to receive an event every time the data in a particular set of tables changes. This data typically changes somewhere between 1 and 50 times per minute.

    Our .NET application is basically caching the contents of several (related) tables in our database and serving clients.

    The pattern we have been using to keep the data in our cache server up to date is what I would call "optimized polling", but the design does not quite sit right with us and we would prefer to go to an events driven pattern.

    We tried using SqlDependency which seemed promising initially (though after reading the docs thoroughly we became skeptical but tested it anyway to be sure) but the performance was not sufficient (deadlocks, etc).  We would have to re-hook the event after every time the SqlDependency's event (don't remember what it was called) was fired.  Also, when the event would fire the actual modified data did not come with the Event Args so we would immediately have to go back and query the base tables anyway.  We eventually abandoned this design which of course we didn't expect to work very well since the docs for the SqlDependency class clearly stated that it was not designed for tables that changed frequently.

    We also thought about putting a CLR Trigger on our tables and inside the trigger sending a message to our .NET layer.  This sounded like a workable solution except for the fact that we couldn't really maintain any static data in our CLR Trigger and were therefore not able to maintain any type of connection to our .NET layer.  We thought about sending a udp message but didn't like the idea.

    I was hoping this would be addressed in 2008 but I don't really see anything so far that would help with this.
    Isn't this a very common business requirement for people who store data in an RDBMS and use a data access layer to serve clients data and update them when the data changes?
    It is certainly the case in financial services where I work.  Yet many of my colleagues that work with SQL Server end up doing some type of polling.

    So my questions are:
    1. Is there anything in 2008 (and / or ADO.new) that can help us with this?
    2. If not are there any suggestions that anyone has that we can use to get data updates to our .NET applications without polling?

    Thanks in advance,
    Mike

    Tuesday, April 22, 2008 6:30 PM

All replies

  •  mike iz wrote:

    I posted this in the TSQL forum as well, but have not gotten an answer and upon further reflection think this is a more general sql server question as opposed to TSQL specific.



    Nevertheless, I first saw your post in the T-SQL forum, and I answered there. So lets' keep the discussion
    there, although I can agree that this forum fits better.

    Saturday, April 26, 2008 10:16 PM