.NET Thoughts and Ideas

Continuing with my study using AppDev.com course, I feel compelled to mention two crucial steps in setting up “Query Notification” when using database caching techniques. I spent almost two days trying to get this techique to work! Thanks to AppDev it works now.

After adding

<%@ OutputCache SqlDependency="CommandNotification" Duration="9999999" VaryByParam="None" %>

there are two things that we must get right for this setup to work:

1. Establishing the target table and writing a correct query, as outlined in this MSDN article.

2. Configuring Service Broker as listed below

USE YourDatabaseName;

– Ensure compatibility with SQL Server 2005
EXECUTE sp_dbcmptlevel ‘Northwind’,90;

– Enable Service Broker
IF NOT EXISTS (SELECT * FROM sys.databases
WHERE name = ‘YourDatabaseName‘ AND is_broker_enabled = 1)
BEGIN
ALTER DATABASE Northwind SET ENABLE_BROKER
WITH ROLLBACK AFTER 5 SECONDS;
– Set Trustworthy
ALTER DATABASE Northwind SET TRUSTWORTHY ON;
END;
GO

– Create an encryption key for Service Broker
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys
WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N‘t0psecret’;
GO

– Create a Service Broker endpoint
CREATE ENDPOINT BrokerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4037 )
FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS );
GO

select * from sys.dm_qn_subscriptions

It’s easy when you know