notify 3rd Party applications of a table change. As is stands now, 3rd
Party applications access the database via a Data Access Layer (DAL)
dll (C#). I'd like to somehow implement an asychronous event
notification scheme via the same DAL to these clients.
Can someone offer a clever way to implement such events?
Broker Services? I am under the impression the SSBS is typically deployed when databases are to communicate with one another.
Triggers to call some CLR code?
Other?
Thanks in advance,
Loopsludge
In SQL 2005 there is a buil-in solution for 'table change' notifications, namely Query Notifications and the technologies based on it (SqlNotifications, SqlDependency, SqlCacheDependency). See this post for a discussion on them: http://blogs.msdn.com/remusrusanu/archive/2006/06/17/635608.aspx
If your goal is a generic notification platform, then some questions arrise:
What kind of events would you notify (data changes, dirty cache notifications, arbitrary events)?
Where are the subscribers located (same appdomain, same machine, different machines)?
What scale do you need (tens, hundreds or millions of subscribers)?
What kind of reliability is desired for the notifications (what is the cost of loosing one notification, what happens if the subscriber or publisher are recycled)?
Are the subscriptions and/or notifications persistent or transient?
Service Broker offers persisted, reliable communication channel between services hosted on SQL 2005 databases.
HTH,
~ Remus
Sorry it has taken me so long to respond. I have been exploring Query Notifications. I'm still not certain this is the correct way to go.
In answer to your questions:
What kind of events would you notify (data changes, dirty cache notifications, arbitrary events)?
I am interested in notifying clients of data changes, specifically new records.
Where are the subscribers located (same appdomain, same machine, different machines)?
The DAL will be consumed by 3rd Party applications located on a different machine.
What scale do you need (tens, hundreds or millions of subscribers)?
The number of subscribers will be very small. In most cases only 1 but potentially there could be more but not many, five max.
What
kind of reliability is desired for the notifications (what is the cost
of loosing one notification, what happens if the subscriber or
publisher are recycled)?
Now this is a very interesting question. I was reading up on the SqlDependency and noticed that one has to re-submit for each notification from the database. What happens if an event occurs inbetween the time the last notification is being processed and the event subscription? Is it lost? If so then that's a bummer. How might I get around this? Service Broker seems like serious overkill.
Are the subscriptions and/or notifications persistent or transient?
I'm not quite sure I understand completely. Could you please explain? (Sorry)
Best Regards,
Loopsludgesql
No comments:
Post a Comment