We have a requirement to notify external systems of data changes in various tables in a SQL Server database. The choice of data for monitoring is somewhat under the control of the user (selects from the list of what we support). Recipients of notifications can be on the local network (i.e., in the same data center) or can be remote.
We are currently processing this using an application code at our data access level that detects changes and queues in the Service Broker queue, which is controlled by the Windows service that runs the actual notification. Not really in real time, but close enough.
This has some maintenance issues, so we are considering using one of the change detection mechanisms built into SQL Server. Unfortunately, none of those I watched (I think I looked at them all) seem to fit very well:
Change data tracking and change tracking: The main problem is that they require interrogation of the captured information in order to determine the changes that should be transmitted to the recipients. I suspect this will bring too much overhead.
Notification Services:. Essentially uses SQL Server as a web server, which is a terrible waste of licenses. It also requires access through at least two firewalls on the network, which is unacceptable from a security point of view.
Request notification: Seems to be the most likely candidate, but doesn't seem to have worked very well to dynamically select data items to view. The need to re-register the request after each notification is sent means that we will keep SQL Server busy in managing registrations
Event notification : Designed to notify of events at the database level or instance level that are not really applicable to the detection of data changes.
The best idea that I came up with is to use CDC and include insert triggers in change data tables. Triggers place a queue in the Service Broker queue, which will be processed by some other code to execute notifications. This is essentially what we are doing now, with the exception of using the SQL Server function to detect changes. I'm not even sure that you can add triggers to these tables, but I thought I was getting feedback before spending a lot of time on POC.
It seems like a terrible roundabout to get the job done. Is there something I missed that will make things easier or have I misinterpreted one of these functions?
Thanks, and I apologize for the length of this question.