Persistent DataCache with Pub/Sub OnChange
Don't use SqlDependency, is not the right scenario for it. SqlDependency is to monitor data that changes seldom (eg. reference data). With SqlDependency you only get notified that data has changed, w/o knowing what the change consists of, you will have to query yourself.
Service Broker on its own would fit the bill much better, as a means to decouple the notification from the consumption. There are large scale deployments doing this already. With SQL Server 2012 you can do multicast.
But absolutely not by doing a trigger. Notification should be a first class citizen in your application, not an afterthought stiched to the data model in atrigger (ughhh). Have explicit procedures for notifying the subscribers and have your application explicitlty call them. Do not mix your data model and yoru messaging schemas,m you'll just introduce unnecessary coupling.
IF you want to go NoSQL way, that is a whole different discussion. Redis is the usual choice. But don't dismiss true pub/sub messaging products, like ZeroMQ.
BTW, these kind of crusades (pro or against, I don't care...) are best fought with prototypes.