Triggers, Service Broker, CDC or Change Tracking?

In my most recent adventure, I was tasked with creating a real-time push subscription to our companies distributed publication server. This effort kicked off a lot of ideas, foremost being the “Asynchronous trigger” promoted by the service broker. I used the example found in the back of the book Pro SQL Server 2008 Service Broker. While this asynchronous trigger is asynchronous in respect to “writing to an internal SQL Server object” (i.e. a table) it still takes a bit of time to execute (and thus return the initial transaction). I was disappointed to now associate the word asynchronous to “timely”. Service broker has (and will) bring about many advances in the near-term future, however the speed of submission and true asynchrony will hopefully be revisited sometime soon.

The second option for performing the real-time push is to use a synchronous trigger, or an “after update” trigger. The after update trigger would push the record to a common table which would then be polled by a job. Initially I thought there was a way to insert to this common table then execute another trigger on this table to publish the record. However I soon learned that all triggers that call triggers contribute to the same transaction.

When one trigger inserts into a table and that table also contains a trigger, this is a “nested trigger”. The reason that nested triggers are a concern is because the first call that performs the initial insert does not return until the last trigger in the sequence is completed. In trying to circumvent this behaviour, as mentioned before, I tried to implement asynchronous triggers. Asynchronous triggers utilize the service broker in order to send an asynchronous response. Problem is, these “asynchronous triggers” are slower than synchronous triggers. This was illustrated by an ex SQL Server Service Broker developer (Remus Rusanu) on an MSDN forum. I’ll quote:

Synchronous audit has to do one database write (one insert). Asynchronous audit has to do at least an insert and an update (the SEND) plus a delete (the RECEIVE) and an insert (the audit itself), so that is 4 database writes. If the destination audit service is remote, then the sys.transmission_queue operations have to be added (one insert and one delete). So clearly there is no way asynchronous audit can be on par with synchronous audit, there are at least 3 more writes to complete. And that is neglecting all the reads (like looking up the conversation handle etc) and all the marshaling/unmarshaling of the message (usually some fairly expensive XML processing).

Within one database the asynchronous pattern is apealing when the trigger processing is expensive (so that the extra cost of going async is negligible) and reducing the original call response time is important. It could also help if the audit operations create high contention and defering the audit reduces this. Some more esoteric reasons is when asynchronous processing is desired for architecture reasons, like the posibility to add a workflow triggered by the original operation and desire to change this workflow on-the-fly without impact/down time (eg. more consumers of the async message are added, the message is schreded/dispatched to more processing apps and triggers more messages downstream etc etc).

If the audit is between different databases even within same instance then the problem of availabilty arrises (audit table/database may be down for intervals, blocking the orginal operations/application).

If the audit is remote (different SQL Server instances) then using Service Broker solves the most difficult problem (communication) in addition to asynchronicity and availability, in that case the the synchrnous pattern (e.g. using a linked server) is really a bad choice.”

You can find the full discussion here.

You can also find the source code for the asynchronous trigger by downloading the following file and looking in chapter 10.

So what options does that leave? If you want to do asynchronous auditing you are left with Change tracking or Change Data Capture (CDC). Both of which would need to utilize a job and are not the most failsafe way to track changes in case of a job failure. I can say that we have run Change Data Capture running in our production environment for over a year and have had only one failure (due to the transaction log filling up). However, on a failure of CDC, we cannot shut down our production system, thus we would miss some changes. The way to compensate for this would be a nightly reconciliation module.

Change tracking, uses the same framework used for CDC yet is more lightweight, however it is suggested that you enable Snapshot Isolation, which can cause extensive utilization of TempDB.

So, for now, in order to trap our data, we are going to use a synchronous trigger which writes out the changed row to an intermediary table along with the column bitmask of what changed. Then we will run a job against that table polling for changed records.

Overall, I wish I could find a more efficient solution. If anyone has any ideas, please comment below.