Mark Tremaine

There exists a new feature in Microsoft SQL Server 2005 called Service Broker.  With Service Broker, internal or external processes can send and receive guaranteed, asynchronous messaging by using extensions to Transact-SQL.

Read More

One way to utilize this feature is by implementing SQLCacheDependency within a SqlSiteMapProvider.  Thus allowing the SqlSiteMapProvider to update as changes occur to the SiteMap table.

Here’s a quick snippet to enable this feature within the database:

   1:  ALTER DATABASE YOUR_DATABASE_NAME SET NEW_BROKER WITH ROLLBACK IMMEDIATE

   2:  GO

   3:  ALTER DATABASE YOUR_DATABASE_NAME SET ENABLE_BROKER

   4:  GO

If you plan to use Service Broker for your own notification queries be sure to follow the rules.

Additionally, the SQL user must either be a member of the db_owner fixed database role for the intended database or the sysadmin fixed server role.  I read that the user could alternately belong to the db_ddladmin fixed database role for the intended database, however; I was not able to get this configuration to work.

Lastly, if you are working within a schema other then dbo, the SQL user used to query the Service Broker must be the schema owner, not a SQL role.  This has to do with the fact that a role cannot be assigned a default schema, and thus defaults to the dbo schema.