I have a number of state-full Windows Services that currently poll the database looking for new or changed records in specific Tables and/or Views. For obvious reasons this limits scalability and is adding unnecessary load to my SQL Server 2008 databases. I'd like to implement something akin to a publish & subscribe pattern where the database; through some mechanism; publishes messages to indicate when certain state changes occur.
This brings me to my question... What mechanisms reside within SQL Server 2008 natively; if any; which could support this pattern?
I’m open to other ideas, assuming I can get away from blind polling. As an extra bit of context, the message will need to carry only basic information to describe the nature of the change and what; by id; has changed.
Note: I’ve considered using SSIS packages and triggers but don’t think it’s appropriate, especially during times when the change rate is higher.