When integrating with legacy databases you’re often required detect changes by polling. This method have some quirks that can be problematic at times. The first issue is that you need to figure out when rows has been inserted or updated. If your legacy db has timestamp columns for inserts and updates this is quite straight forward, just remember when the time you lasts checked for changes and get all changes that has occurred since then. If no such columns exists you’re in trouble and the pain of writing manual code for keeping track of all that awaits you. The other problem is that it’s hard to figure out which fields that was updated. This might not be a big issue but if you need to make decisions based on that info you need maintain a copy of the data in order to figure out what has changed. This is almost always the case if you want to move a way from CRUD style events like CustomerUpdated and the likes to a more business aligned model with events like CustomerActived, CustomerMoved etc etc.
We faced all the problems above and decided to tackle the problem from another angle. As a nice coincidence I had noticed the Joseph Daigle had created a ITransport implementation for NServiceBus that work with SQL Server Service Broker (SSSB). I had to patch the transport, to support xml serialization in order to be able to send messages direct from within SQL Server. With this in place we could add triggers to the relevant tables to add messages to the SSSB queue.
We added the following stored procedure to help with creating xml in the correct format for the NServiceBus xml parser and finally submitting the message to the queue.
For instructions on how to setup SSSB and create the necessary Services and queues please read more here.
Receiving messages from SSSB
The next step was to enable our regular NServiceBus endpoint to receive messages from SSSB. Our main transport is Msmq so we have to start up the SSSB Transport and forwards all incoming messages to our regular transport using SendLocal. This is easily done by implementing IWantToRunAtStartup causing the NServicebus Host to kick off a separate thread to handle this. Next we subscribe to the TransportMessageReceived event and send the incoming messages to our Msmq transport for regular message processing. This combined with the fact that SSSB supports distributed transactions gives us a nice and robust bridge between SSSB and Msmq.
The code is displayed below.
The image below outlines the moving parts of this solution
This is an example of using the SSSB transport to do event based interop with SQL Server. You can of course use the transport for pure SSSB backed solutions where you use SSSB queues as your main transport for messages between your regular NServiceBus endpoint. This removes the dependency on MSMQ but will make your SQL Server a single point of failure and a potential bottleneck.
Pick your poison!