A client bought a third party application that needed to share data with two others. Our task was to create an integration layer for the applications to move data back and forth with reliability. All of the others used integration tables as endpoints. This means they consumed data from special tables and perform internal adjustments as needed before finally committing the data to production. The thing to understand is that the data entered is not necessarily that committed to production. Polling the database would work most of the time, however this approached also requires a data caching scheme adding even more overhead a not providing much more data integrity.

What to do? SQL Service Broker to the rescue. It is an abstraction layer in the database that provides kernel level messaging for the SQL server and application. This is done by the application defining a data set and listening for notifications from the Windows messaging system of when that data set changes. In this case the SQLDependency class was used to notify when other applications have fully vetted and consumed specific data without issue. This took a lot of responsibility away from our solution and reduced the overhead tremendously. It allowed us to focus much more on the business logic.

The SQL Service Broker offers a very granular level of application integration with minimal extra coding and less processing overhead. It will take many risky assumptions out of the application design and offer tightly coupled event driven integration opportunities. Not to mention that it brings plenty of SQL love to go around.