we have requirement notifying external systems of changes in data in various tables in sql server database. choice of data monitor under control of user (gets choose list of support). recipients of notifications may on locally connected network (i.e., in same data center) or may remote.
we handle application code within our data access layer detects changes , queues notifications on service broker queue monitored windows service performs actual notification. not quite real time close enough.
this has proven have maintenance problems looking @ using 1 of change detection mechanisms built sql server. unfortunately none of ones have looked @ (i think looked @ them all) seem fit well:
change data capture , change tracking: major problem require polling captured information determine changes passed on recipients. suspect introduce overhead.
notification services: uses sql server web server, horrible waste of licenses. requires access through @ least 2 firewalls in network, unacceptable security perspective.
query notification: seems candidate not seem lend particularly dynamically choosing data elements watch. need re-register query after each notification sent means keep sql server busy managing registrations
event notification: designed notify on database or instance level events, not applicable data change detection.
about best idea have come use cdc , put insert triggers on change data tables. triggers queue service broker queue handled other code perform notifications. except using sql server feature change detection. i'm not sure can add triggers tables thought i'd feedback before spending lot of time poc.
that seems awful roundabout way job done. there have missed make job easier or have misinterpreted 1 of these features?
thanks , apologize length of question.
why don't use update , insert triggers? trigger can execute clr code, explained enter link description here
Comments
Post a Comment