I've been back working with SQL Server Query Notifications again lately. This blog post stems from a problem where the query notification "appears to register correctly" but does not fire. Or registers but "fires immediately". In this case "register correctly" meant fires a QN:Subscription event to SQL Profiler.

The firing of a QN:Subscription event to SQL Profiler means that you did attempt to register a query notification. The query notification can either register correctly or return an additional field in the QN:Subscription event, an XML attribute named "info". This can be the reason why the Query Notification was NOT registered.

If your OnChange handler gets hit, you may either be getting a valid notification (some row has changed) or a notification that something has gone wrong, either with the registration or after the registration. Be sure and look at the SqlNotificationInfo for each request to see what happened.

The most common reason for a query notification to fail registration is "Query", that is the query is not supported by Query Notifications. But the one that I ran into recently is "Isolation". Query Notifications aren't supported if the statement that performs the SELECT is running under isolation level Read Uncommitted or Snapshot. Another interesting one that can happen at registration time is "Options", the SET options aren't set properly (yes, Query Notifications require certain SET options).

After the notification is registered, you can get not only Insert/Update/Delete notifications but also "Alter, a table/index/view definition was altered. And, if the SQL Server is recycled while you have a pending Query Notification, you'll get a "restart" notification reason when it starts back up. Bear in mind that you won't get that restart notification if Mirroring-Based failover happens (see http://support.microsoft.com/kb/930048).

Most times you just want to invalidate your cache and re-register the query notification, but note the reason (SqlNotificationInfo class) carefully; reregistering a query that fails for "Options" or "Isolation" just causes a register-fire-reregister spin behavior. Or, if you only act on Insert/Update/Delete, the perception that the notification registers correctly but never fires.

The companion whitepaper to my "Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005" whitepaper (see yesterday's post) is available. This whitepaper is called "Internals, Troubleshooting, and Best Practices for use of Scaleout Technologies in SQL Server 2005", with as much about internals as I could cram in 50 pages. Again, I don't have the "main" link; the direct link is here. Enjoy.

I've been working on some whitepapers on scaleout technologies in SQL Server 2005. The first whitepaper is now available; I don't have the main link, but here is the direct link to the doc file on the Microsoft download site. The whitepaper is about the implementation steps when using scaleout technologies like Service Broker, Scalable Shared Database, Query Notiifcations, and Peer-to-Peer Replication and how to choose which technology or combination of technologies is the best fit.

It will be followed by a companion whitepaper about internals and troubleshooting of these same scaleout technologies. I'll let you know when that one's available. Hope you find them useful.

Last week at TechEd I was showing off Query Notifications. When I showed using the preprovisioned queue (overloads on SqlDependency.Start and SqlDependency constructor), a delegate asked about using the same queue with more than one subscriber. He repeated the question when I showed the low-level SqlNotificationRequest.

I did some tests over the weekend and the answer is no. You need a separate queue for each subscriber, otherwise the SqlDependency listener gets "confused". If you use the dynamically created queues like most people do (SqlDependency.Start with only a connection string), you get a new queue and service for each subscriber. So you won't notice this. I thought I'd have better luck with the SqlNotificationRequest because I'm reading the queue myself, but without going to the system tables (and perhaps introducing race conditions) its not possible there either.

Queue are cheap to create, so there is really no reason not to create a separate pre-provisioned queue and service for each notification subscriber. But, at least for the time being, its a requirement.

In helping out with a project involving ASP.NET 2.0's cache sync feature, which has best performance when built on SQL Server 2005's Query Notifications, I was asked if there was a way to "pre-provision" the queue, service, and stored procedure that cache sync needs. The concern was that the connection string to start up the listener (in Application.OnStart()) used the same security principal as the remainder of the application. That is, the app data access and listener start connection strings were both

"server=myserver;integrated security=sspi;database=mydb".

This meant that the entire application had create queue, create service, and create procedure privilege. That's too much privilege. Using a separate SQL login for SqlDependency.Start() was not an option.

ADO.NET's SqlDependency has an override for the static Start method that takes the name of a pre-provisioned queue. This can be combined with use of the constructor of SqlDependency that uses an Options parameter. In my test, the queue name had to be a one-part SQL identifier; a two-part name (schema.object) didn't seem to work. So the queue, service, and procedure must live in the SQL principal's default schema. The options parameter is a string that names the Service and Database (Broker Instance) that the depdendency should use. The options string would look like this:

"service=myservice;local database=mydb" or
"service=myservice;broker instance={GUID}" //where GUID is the Service Broker GUID.

As far as a stored procedure to do the same type of processing that SqlDepedency's listener does, you can base your procedure on the one SqlDependency dynamically generates, changing the name of the queue and service, of course.

Unfortunately, ASP.NET's SqlCacheDependency doesn't allow the options string to be specified. And the override of SqlDepedency.Start() doesn't work without the corresponding options on SqlDependency. So the only way to use a preconfigured service, queue, and is to build you own cache, using the ASP.NET Cache class as a "template". It's not as simple as being able to use the OutputCache directive on the Page class, made things OK as far as the security folks concerns.

Perhaps ASP.NET will support this in future, or there's a workaround that I hadn't thought of (and I did go as far as to read the code for SqlCacheDependency). I'll have more to say about query notifications at the upcoming SQLskills scale-out events. It's quite an interesting area. If you have lots of read-mostly lookup tables, its well worth the time.

I've answered a few questions lately on setting up SqlDependency or ASP.NET SQL Server dependency with SQL Server 2005. Folks have gone by the instructions on the DataWorks Weblog posting and still receive the error "either schema dbo does not exist or you do not have permission to access it". This is caused by the separation or users and schemas in SQL Server 2005.

The instructions don't show creating 'startUser' (the principal that creates procedures, queues, and services), so folks create it, using the new DDL, like this:

CREATE LOGIN startUser WITH PASSWORD = 'SomeStrongPW1'
CREATE USER startUser FOR LOGIN startUser

Problem is, CREATE USER doesn't assign a default database schema (its not supposed to) and when startUser attempts to create database objects, it creates them in the "default default_schema" which is dbo. The quick fix is to create a schema for (owned by) the user and make that schema its default schema.

CREATE SCHEMA startUserSchema AUTHORIZATION startUser
ALTER USER startUser WITH DEFAULT_SCHEMA = startUserSchema

A better alternative might be to create a database role for this function and create the default schema owned by the role. Then add startUser to the role. You still have to alter the user's default_schema in this case, because database roles cannot have default schemas. Roles cannot have default_schemas themselves because if one user was a member of 3 different roles and each role had a different default_schemas which one would "win"?

A less attractive (actually unattractive) alternative is to give startUser CREATE (actually ALTER) privilege on the DBO schema.

GRANT ALTER ON SCHEMA::dbo to startUser

DON'T do this, you've just given startUser much more privilege than it really needs.

Three more comments:
1. If you used sp_adduser instead of CREATE USER, you "got lucky". For backward compatibility sp_adduser actually does:

CREATE USER startUser WITH DEFAULT_SCHEMA = startUser
GO
-- create schema must be first statement in the batch
CREATE SCHEMA startUser AUTHORIZATION startUser

When/if that backward compatibility mode is removed, your luck runs out.

2. With the new separation of users and schemas granting CREATE TABLE permission doesn't give the user enough to create a TABLE, nowadays. The user also needs a *container* to create tables (or other database objects) in. The user needs a database schema. Resist the temptation to make a schema for the user; rather make a schema for a role the user is a member of. The fact that schemas can be owned by a role is one of their best features.

3. Technically, Service Broker SERVICEs (as well as MESSAGE_TYPEs and CONTACTs) don't live at schema scope. They live at database scope, so you don't need a schema for them. CREATE privilege is enough. But QUEUEs (and most database objects) do live at schema scope. QUEUEs are just tables with special semantics, after all.

I'm headed out on the road. For quite a while. Wanted to settle this before I went.

Looks like the implementation details of SqlDependency have changed a bit. Even since the September CTP (where they added SqlDependency.Start and Stop). Stop and Start control the (new) internal listener. Start also sets up a stored procedure and a Service Broker Dialog Timer. Every 120 seconds, the timer fires, which activates the procedure, which sets the timer again. The first time I saw this, I thought the procedure was polling. Actually its more like its trauling. But its not polling (whew).

The way SqlDependency works now is that requests for query notifications set up their own broker services to catch the notification message. The activation proc (also set up for the notifications) relays the notification message back to the client. Which calls your event handler, or in the case of ASP.NET's SqlNotificationRequest, invalidates the cache.

So what's the timer for? The timer is trauling to find services and procedures (they have GUIDs in their names) set up by SqlDependency instances and will clean them up if it finds any. This is NOT on an individual SqlDependency instance basis, but on a *per-listner* basis. That is, once for every client that calls Start.

Take home message is, now more than ever, you need to limit the number of clients that call Start. Maybe only ASP.NET (or other) caches???

I'm glad that's settled. See you on the road I'm teaching/talking about this more in person...c'mon over and say hi.

I've been wondering what happened to the QueryNotification dispatcher proc that's used by SqlDependency in ADO.NET (and in ASP.NET with SQL Server 2005). The one that I wrote about in one of my MSDN articles. Lately, although the dispatcher proc and assembly didn't show up in MSDB, the function kept working. I wondered why, how, what was happening.

This morning I installed the July CTP (which hasn't been reported to work with SQL Server 2005, so I didn't try) and found why. It's been "eased out".

There is now a static method called Start on SqlDependency (and a matching Stop method) that starts off ADO.NET's dependency listener. This creates a Service Broker queue and service (by default) and starts listening on it with a WAITFOR. So the functionality is no longer a passive listener (server pushes notification) but an active listener (strange as that sounds, means client listens and pulls notification). You pass a connection string into Start, but it looks like it will multiplex listeners on the same (1) connection.

Some nice repercussions of this (offhand, there may be more) are:
 No dependency of this feature on having SQLCLR enabled on server
 No possibility of DOS attacks on client
 No firewall issues since the listener uses one "normal" connection
 
More later...back to the revising/editing table for me.

In the April CTP of .NET 2.0, I chanced upon some changes to SqlNotificationRequest, using my favorite tool, .NET Reflector and my one my favorite investigative techniques, called “follow the error message”.

It turns out that two properties in SqlNotificationRequest the id and Service properties are about to be replaced. They still work but are marked “do not use, to be removed”. They'll but replaced by the UserData and Options properties. UserData appears to be a straight replacement for id, probably mandated by the .NET naming police. The naming police are possibly the same folks who replaced SqlContext.GetPipe and GetWindowsIdentity methods with the Pipe and WindowsIdentity properties. Making things consistent is nice, but its hard on folks who write things like demos, labs, slides, books, and articles.

SqlNotificationRequest.Options is more interesting. While the Service property only let you specify the Service Broker Service name, options gives you...you guessed it, more options. You can also specify which database the broker service lives in or even the Broker indentifier.

Suppose you wanted to listen for query notifications on a query on a table in the pubs database, using a service named “MyService” that also lives in the pubs database in your local SQL Server instance. Using the soon-obsolete Service and Id properties it would look like this:

SqlNotificationRequest not = new SqlNotificationRequest();
not.Id = Guid.NewGuid().ToString();
not.Service = "MyService";
not.Timeout = 0;
// now hook it up to the right SqlCommand

Using the new syntax would look like this:

SqlNotificationRequest not = new SqlNotificationRequest();
not.UserData = Guid.NewGuid().ToString();
not.Options = "service=MyService;local database=pubs";
not.Timeout = 0;
// now hook it up to the right SqlCommand

You can even use the Service Broker identifier GUID (look it up by “select name, service_broker_guid from sys.databases”) in the Options like this:

//NB: Service Broker service names are case sensistive!
//not.Options = "service=MyService;local database=pubs";
not.Options = "service=MyService;broker instance=CE086F11-C691-47F1-A8B6-1B7BD59EA6AE";

This property gives you the option of pointing at a service in a different database in the same instance, or even a different instance, subject to sercurity, of course. Happy query notifying. I gotta go fix a paper. And a book chapter. And a slide. And a lab. And... geez.

Last week I promised some of my students that I had an article in the works on the intricacies of SQL Server 2005/.NET 2.0 Query Notifications, both from a server implementation and client consumer point of view. Yesterday I found out that the article “shipped” to (was officially posted on) the website. You can find it here. This article is part 4 of a 6-part series on ADO.NET 2.0/SQL Server 2005 I'm writing for MSDN Data Access and Storage Developer Center. The other articles might be useful to ya' too...

There have been a number of questions recently (well OK, three) on the beta newsgroups about SqlDependency problems. It's been suggested that SqlDependency doesn't work in Whidbey Beta 1.

The reason for this is that SqlDependency (and, of course, its lower-level cousin SqlNotificationRequest) requires a “valid” notify-able SQL query to work. What's a “valid”  notify-able query for a NotificationRequest? Subscriptions for query notification in SQL Server 2005 use the same underlying mechanism to be notified of resultset changes as indexed views do. Therefore the rules are the same as the rules for indexed views.

Since the example in our “First Look at SQL Server 2005 for Developers” book also uses an invalid query (it doesn't use a two-part table name), I posted the rules for indexed views (and therefore for SqlNotificationRequest/SqlDependency on the book website, under “changes since the book shipped/chapter 12”. It seemed that you could “get away with” not always following the rules until SQL Server 2005 Beta2. The list of rules is from the SQL Server 2005 Beta 2 Books Online.

By the way, if you submit an invalid SELECT statement with a SqlDependency, you'll get an immediate notification with the reason (in SqlNotificationEventArgs) “invalid”.

Just when I thought I'd found all the new cool features. An Ascend Phase 1 participant once asked me “can the DBA get rid of unwanted query notification subscriptions”? In Beta2 you can.

SELECT * FROM sys.dm_qn_subscriptions

-- pick the ID of the subscription that you want, then
-- say its ID = 42
KILL QUERY NOTIFICATION SUBSCRIPTION 42

Cool. Your subscriber app doesn't appear to get notified its been killed, though, (as it does when almost anything else affecting the subscription happens) either when using SqlDependency or SqlNotificationRequest . That's why this should only be used for “pesky” subscriptions.