A fast way to check the count of messages in a queue is to run this query is to look at the row count of the underlying the b-tree that stores the messages:
select p.rows
from sys.objects as o
join sys.partitions as p on p.object_id = o.object_id
join sys.objects as q on o.parent_object_id = q.object_id
where q.name = ‘<queuename>’
and p.index_id = 1
Read the rest of this entry »
A frequent question from customers is ‘How can I change the activation timing?’. The short answer is that you cannot, this is hard coded in the SQL Server engine and cannot be customized. But the reason why most people want the timing changed is to start all the configured max_queue_readers at once. I will show you an unorthodox trick that can be used to achieve this result.
It’s been a while since I posted an entry in this blog and this article is long overdue. There were a series of events that prevented me from posting this, not least impacting being the fact that I’ve opened a WoW account…
A question often asked is how to write a typical activated procedure? This article will cover the ways to write a performant T-SQL procedure to process messages. I am not going to cover CLR procedures for now.
Update:: for a way to leverage SqlDependency and Query Notifications from LINQ, see SqlDependency based caching of LINQ Queries.
One of the most mysterious features shipped with SQL Server 2005 seems to be the various flavors of notifications on data change: SqlNotificationRequest, SqlDependency and SqlCacheDependency. I see confusion on how this features work, how to use them and how to troubleshoot problems. Why are there three flavors of apparently the same functionality? And how is Service Broker involved into all of these?
In reality, there is only one feature in the SQL Server 2005 engine that delivers notifications on subscriptions for data changes: Query Notifications. Clients can submit a query requesting to be notified when data was modified in a manner that would change the query result and the server sends a notification when this change occurs. These requests are called ‘query notification subscriptions’. The list of notification subscriptions can be seen in the server level view sys.dm_qn_subscriptions:
select * from sys.dm_qn_subscriptions
Along with the query submitted for the notification, the client submits a service name and a broker instance. Each notification subscription begins a Service Broker dialog with this provided service and broker instance. When data is changed and the change would affect the result of the submitted query result, a message is sent on this dialog. By sending this message, the client is considered notified and the notification subscription is removed. If client desires to be notified on further changes, is supposed to subscribe again.
So we’ve seen that the notification is not delivered back to the client, but a Service Broker message is instead sent to the service the client provided in the subscription request. All normal rules for delivery, routing and dialog security apply to the dialog used to send this message. This means that the notification message can be sent to service hosted in the same database, in a different database or even on a remote machine. Also, there is no need for the client to be connected to receive the notification. It is perfectly acceptable for a client to submit a query for a notification subscription then disconnect and shutdown.
The client consumes the notification message just like any other Service Broker message: by RECEIVE-ing it from the service’s queue. The notification message will be of type [http://schemas.microsoft.com/SQL/Notifications/QueryNotification], an XML message type. This message is part of the [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]contract, which means that the service that receives the notification messages must be bound to this contract. After the client receives the message, is supposed to end the conversation on which the message was received, using END CONVERSATION (make sure you don’t use the CLEANUP clause!).
The restrictions that apply to queries submitted for notifications are explained in BOL in the ‘Creating a Query for Notification’ topic:http://msdn2.microsoft.com/en-us/library/ms181122(SQL.90).aspx. Although the query can be a stored procedure, it must not contain any flow control statements (IF, WHILE, BEGIN TRY etc).
Clients can submit notification subscription request by programming directly against the SQL Native Client, using the HTTP SOAP access to SQL Server or, most commonly, using the ADO.Net client components. AFAIK clients cannot subscribe using the OLEDB, ODBC or JDBC components. Updated: It is possible from OleDB and ODBC as well, see Working with Query Notifications.
Query notification subscriptions have little cost in the SQL Server 2005 engine. Notification subscriptions are only metadata, and the effect of a subscription is to modify the query plans in a manner that allows the relevant data changes to be detected. The picture below shows how a plan for a INSERT INTO TEST … statement is modified when there is a query notification subscription active with the query SELECT * FROM TEST:
This plan shows that, while cheap, query notification subscriptions are not free. The cost associated with them is similar to the cost of having a secondary index on the data, or the cost of having an indexed view.
When the SQL Server 2005 is restarted, all query notification subscriptions are notified and ended.
This Transact-SQL statement can be used to administratively end query notification subscriptions. The BOL describe its usage: http://msdn2.microsoft.com/en-us/library/ms186764.aspx
This is simplest ADO.Net component for subscribing to Query Notifications. This class is used directly to create a query notifications subscription. The usage is straightforward:
– Create a new SqlNotificationRequest object, passing in the appropriate Service Broker service name and broker instance
– Assign the newly created SqlNotificationRequest to the Notification property of a SqlCommand
– Execute the SqlCommand.
MSDN has a sample of this technique here: http://msdn2.microsoft.com/en-us/3ht3391b.aspx
Using the SqlNotificationRequest leaves the task of handling the notification message entirely to the client application. While this is the most flexible way of leveraging the Query Notification functionality, it requires knowledge and understanding of the way Service Broker delivers the messages and how to write a Service Broker application to process the notification messages.
This component tries to make the task of handling the Query Notification subscription messages as straightforward as possible. Using the SqlDependency the application developer gets a CLR callback whenever data has changed. How this is achieved is very simple: the SqlDependency uses SqlNotification to subscribe to query notifications and then the SqlDependency infrastructure uses a just-in-time-created service and queue to receive the notification messages. It starts a background client thread that posts a WAITFOR(RECEIVE…) on this queue and whenever a notification message is delivered by Service Broker into this queue, the background thread will receive this message, inspects it and invoke the appropriate callback. The static methods Start and Stop on the SqlDependency class are starting and stopping this background thread, as well as create and drop the service and queue used by the SqlDependency infrastructure. This background thread is shared by all requests in one appdomain. This is important, because if each SqlDependency request would start its own listener, the back end server would quickly get swamped by all those requests issuing WAITFOR(RECEIVE…) statements, each blocking a server thread.
The MSDN contains a sample of how to use the SqlDependency here: http://msdn2.microsoft.com/en-us/a52dhwx7.aspx. Note how, similarly to the SqlNotification usage, the client is expected to subscribe again if it whishes to be further notified.
If a client disconnects abruptly without stopping the SqlDependency infrastructure, the queue and service created by the SqlDependency.Start are left stranded. This is not a problem, because the SqlDependency infrastructure also deploys a stored proc associated with activation on this queue and sets up a dialog timer. When this timer fires, the procedure is activated and this procedure cleans up the queue, the service and the activated procedure itself. Due to the pending WAITFOR(RECEIVE…) permanently posted by the SqlDependency background thread, the activated procedure will launch only if the client has disconnected w/o cleaning up.
This is a component designed specifically for ASP.Net to cache data, using the SqlDependency whenever possible. I’m not going to dwell into how it works, simply because I don’t really know. But once one understands how the Query Notifications and SqlDependency class work, the SqlCacheDependency is just another use of this infrastructure, with nothing special to it.
While the SqlDependency infrastructure is a great help to developers, is it often used w/o properly understanding its functionality and I often see people totally lost when it comes to troubleshooting a problem. In fact, BOL has a dedicated chapter for this topic here: http://msdn2.microsoft.com/en-us/library/ms177469.aspx. The Profiler can show the Query Notification events that are reported when a new subscription is registered. Once a notification subscription is notified, the notification message is delivered using Service Broker and all of my comments related to troubleshooting dialogs apply to this message delivery as well. If the notification message is no delivered, the first place to look is the transmission_status column in the sys.trasnmission_queue view in the sender’s database.
DECLARE @dh UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @dh
FROM SERVICE [Initiator]
TO SERVICE N‘Target’;
SEND ON CONVERSATION @dh;
END CONVERSATION @dh;
Does the code above look familiar? Well, it shouldn’t, because it contains a very nasty error. This is the dreaded fire-and-forget message exchange pattern. The initiator opens a conversation, sends a message and then ends the conversation. I often seen this pattern deployed in practice, and (just apparently!) it seems to works fine. The target receives the message sent, and it ends its own target endpoint of the conversation, and things look happy, happy, happy, joy, joy, joy. Then suddenly something changes, and messages seem to vanish in thin air. The initiator sends them for sure, but the target doesn’t get them. The sent message is not in sys.transmission_queue, is not in the target queue, there is no error in the initiator queue, and even more, the initiator endpoint disappears! What happened? Well, the target service has responded with an error to the message the initiator has sent. The error could happen for a multitude of reasons, most common being one of these:
– the target may had denied access to the initiator service
– the message XML validation has failed (the target may had added a schema validation to the message)
– the target service may no longer implement the contract used by the initiator
– the target service was just dropped (can happen only if the broker instance was specified in the BEGIN DIALOG)
Well, if the target responded with an error, then why isn’t the error enqueued in the initiator’s queue? Because the initiator has declared that it is no interested in seeing that error! It had ended the conversation! So when the error comes back from the target, the error message is dropped and the initiator endpoint is deleted. This is the expected behavior when the conversation endpoint is in DISCONNECTED_OUTBOUND state.
The solution is let the target end the conversation first. The initiator can simply send the message to the target and then continue. The target will receive the message, end the conversation from the target side and this will send an EndDialog message back to the initiator. All we have to do is attach an activated stored procedure to the initiator’s queue, and in this stored procedure we can end the initiator side of the conversation:
CREATE PROCEDURE inititator_queue_activated_procedure
AS
DECLARE @dh UNIQUEIDENTIFIER;
DECLARE @message_type SYSNAME;
DECLARE @message_body NVARCHAR(4000);
BEGIN TRANSACTION;
WAITFOR (
RECEIVE @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [InitiatorQueue]), TIMEOUT 1000;
WHILE @dh IS NOT NULL
BEGIN
IF @message_type = N‘http://schemas.microsoft.com/SQL/ServiceBroker/Error’
BEGIN
RAISERROR (N‘Received error %s from service [Target]’, 10, 1, @message_body) WITH LOG;
END
END CONVERSATION @dh;
COMMIT;
SELECT @dh = NULL;
BEGIN TRANSACTION;
WAITFOR (
RECEIVE @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [InitiatorQueue]), TIMEOUT 1000;
END
COMMIT;
GO
ALTER QUEUE [InitiatorQueue]
WITH ACTIVATION (
STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = [inititator_queue_activated_procedure],
EXECUTE AS OWNER);
GO
Now when en error is returned by the target service it is logged into the System Event Log:
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17061
Date: 4/6/2006
Time: 10:01:41 PM
User: REDMOND\remusr
Computer: REMUSR10
Description:
Error: 50000 Severity: 10 State: 1 Received error ?<?xml version=”1.0″?><Error xmlns=”http://schemas.microsoft.com/SQL/ServiceBroker/Error”><Code>-8425</Code><Description>The service contract 'DEFAULT' is not found.</Description></Error> from service [Target]
We could have chosen to insert the error message into an audit table, or even send it to an audit service (using Service Broker, of course.J).
One of the most frequent questions about Service Broker is whether it supports any sort of priority for messages. But having priority within a conversation would conflict directly with the exactly-once-in-order guarantee of a conversation. In a good SOA design the two services involved in a conversation are supposed to be independent: separate developers, separate orgs, separate admins etc. If one service can set the priority of an individual message w/o the consent of the other, this action could wreck havoc on how the messages are processed, since the other service may expect the messages in order. A different story though is to have priority for individual conversations. Conversations are supposed to be independent and atomic; the processing order should not matter so having the possibility of setting a priority for a conversation makes sense. Roger has recently addressed the same problem and he already has a couple of posts on the topic: http://blogs.msdn.com/rogerwolterblog/archive/2006/03/11/549730.aspx and http://blogs.msdn.com/rogerwolterblog/archive/2006/03/17/554134.aspx
In my previous post http://rusanu.com/2006/03/01/signing-an-activated-procedure/ I’ve showed how to use code signing to enable a server level privilege (view server state) when running an activated stored procedure. I’ll show now how to solve a very similar issue: call a stored procedure from another database. Why this is a problem is explained in this post: http://rusanu.com/2006/01/12/why-does-feature-not-work-under-activation/
So let’s say the ‘SessionsService’ from my previous post needs some new functionality: it has to audit the requests it’s receive. An audit infrastructure already exists in the [AuditInfrastructure] database, all is needed is to call the stored procedure [audit_record_request] in that database and the request will be audited. First create this ‘audit infrastructure’, which for our example will be very simple:
create database [AuditInfrastructure]
go
use [AuditInfrastructure]
go
-- An audit table, simply stores the time of the request
--
create table audit_table (request_time datetime);
go
-- this is the audit procedure
--
create procedure audit_record_request
as
begin
set nocount on
insert into audit_table (request_time) values (getdate());
end
So now all we have to do is to change the SessionService procedure to call this audit procedure:
USE [DemoActivationSigning];
GO
ALTER PROCEDURE [SessionsServiceProcedure]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @dh UNIQUEIDENTIFIER;
DECLARE @mt SYSNAME;
BEGIN TRANSACTION;
WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,
@mt = message_type_name
FROM [SessionsQueue]), TIMEOUT 1000;
WHILE (@dh IS NOT NULL)
BEGIN
– If the message is a request message,
– send back a response with the list of sessions
–
IF (@mt = N‘RequestSessions’)
BEGIN
– Get the list of current sessions
– and send it back as a response
–
DECLARE @response XML;
SELECT @response = (
SELECT * FROM sys.dm_exec_sessions
FOR XML PATH (’session’), TYPE);
SEND ON CONVERSATION @dh
MESSAGE TYPE [Sessions]
(@response);
-- This is the extra call to the audit procedure
EXECUTE [AuditInfrastructure]..[audit_record_request];
END
– End the conversation and commit
END CONVERSATION @dh;
COMMIT;
– Try to loop once more if there are message
–
SELECT @dh = NULL;
BEGIN TRANSACTION;
WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,
@mt = message_type_name
FROM [SessionsQueue]), TIMEOUT 1000;
END
COMMIT;
END
GO
So now we send a request to the ‘SessionsService’
DECLARE @dh UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @dh
FROM SERVICE [Requests]
TO SERVICE ‘SessionsService’
ON CONTRACT [SessionsContract]
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @dh
MESSAGE TYPE [RequestSessions];
We expect a response back, but nothing happens. The ‘SessionsService’ has gone silent. If we look into the Event Viewer, will find some troublesome entries:
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9724
Date: 3/7/2006
Time: 10:36:25 AM
User: REDMOND\remusr
Computer: REMUSR10
Description:
The activated proc [dbo].[SessionsServiceProcedure] running on queue DemoActivationSigning.dbo.SessionsQueue output the following: ‘The server principal “REDMOND\remusr” is not able to access the database “AuditInfrastructure” under the current security context.’
Data:
0000: fc 25 00 00 0a 00 00 00 ü%……
0008: 09 00 00 00 52 00 45 00 ….R.E.
0010: 4d 00 55 00 53 00 52 00 M.U.S.R.
0018: 31 00 30 00 00 00 14 00 1.0…..
0020: 00 00 41 00 75 00 64 00 ..A.u.d.
0028: 69 00 74 00 49 00 6e 00 i.t.I.n.
0030: 66 00 72 00 61 00 73 00 f.r.a.s.
0038: 74 00 72 00 75 00 63 00 t.r.u.c.
0040: 74 00 75 00 72 00 65 00 t.u.r.e.
0048: 00 00 ..
Note: In case you wonder ‘so what happened to my request?.The activated stored procedure has thrown an error and it rolled back. Because Service Broker is a fully transactional, the dequeued request was rolled back and is again available for dequeue. The activation will kick in again, causing the same error and again the request to be rolled back. Then again activation will kick in and so on and so forth. Eventually (after 5 consecutive rollbacks) the Service Broker Poisson Message support will detect this situation and will disable the queue.
The problem is, as expected, the database impersonation context, as explained here. Same as in the case of server level privileges, the easiest fix is to mark the database trustworthy:
ALTER DATABASE [DemoActivationSigning] SET TRUSTWORTHY ON
If we cannot afford this due to security risks (marking the database trustworthy elevates the database dbo to a de-facto sysadmin), we must reside to code signing. The steps are these:
– alter the procedure to have an EXECUTE AS clause (otherwise the code signing infrastructure does not work)
– create a certificate with a private key in the [DemoActivationSigning] database
– sign the procedure
– drop the private key of the certificate
– copy the certificate into the [AuditInfrastructure] database (backup the certificate to a file and the create from that file)
– derive a user from the certificate in the [AuditInfrastructure]database
– grant the desired privileges to this user
Here is the code for these steps:
USE [DemoActivationSigning];
GO
— Create aprocedure that implements
— the [SessionsService] service
—
ALTER PROCEDURE [SessionsServiceProcedure]
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @dh UNIQUEIDENTIFIER;
DECLARE @mt SYSNAME;
BEGIN TRANSACTION;
WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,
@mt = message_type_name
FROM [SessionsQueue]), TIMEOUT 1000;
WHILE (@dh IS NOT NULL)
BEGIN
— If the message is a request message,
— send back a response with the list of sessions
—
IF (@mt = N‘RequestSessions’)
BEGIN
— Get the list of current sessions
— and send it back as a response
—
DECLARE @response XML;
SELECT @response = (
SELECT * FROM sys.dm_exec_sessions
FOR XML PATH (‘session’), TYPE);
SEND ON CONVERSATION @dh
MESSAGE TYPE [Sessions]
(@response);
EXECUTE [AuditInfrastructure]..[audit_record_request];
END
— End the conversation and commit
END CONVERSATION @dh;
COMMIT;
— Try to loop once more if there are message
—
SELECT @dh = NULL;
BEGIN TRANSACTION;
WAITFOR (RECEIVE TOP (1) @dh = conversation_handle,
@mt = message_type_name
FROM [SessionsQueue]), TIMEOUT 1000;
END
COMMIT;
END
GO
— Create a certificate with a private key
— to sign the procedure with. The password
— used is not important, we’ll drop the
— private key
—
CREATE CERTIFICATE [SessionsServiceProcedureAudit]
ENCRYPTION BY PASSWORD = ‘Password#1234’
WITH SUBJECT = ‘SessionsServiceProcedure Signing for audit certificate’;
GO
— Sign the procedure with the certificate’s private key
—
ADD SIGNATURE TO OBJECT::[SessionsServiceProcedure]
BY CERTIFICATE [SessionsServiceProcedureAudit]
WITH PASSWORD = ‘Password#1234’;
GO
— Drop the private key. This way it cannot be
— used again to sign other procedures.
—
ALTER CERTIFICATE [SessionsServiceProcedureAudit]
REMOVE PRIVATE KEY;
GO
— Copy the certificate in [master]
— We must backup to a file and create
— the certificate in [master] from this file
—
BACKUP CERTIFICATE [SessionsServiceProcedureAudit]
TO FILE = ‘C:\SessionsServiceProcedureAudit.CER’;
GO
USE [AuditInfrastructure]
GO
CREATE CERTIFICATE [SessionsServiceProcedureAudit]
FROM FILE = ‘C:\SessionsServiceProcedureAudit.CER’;
GO
CREATE USER [SessionsServiceProcedureAudit] FROM CERTIFICATE [SessionsServiceProcedureAudit];
G0
— ‘AUTHENTICATE’ permission is required for all other permissions to take effect
—
GRANT AUTHENTICATE TO [SessionsServiceProcedureAudit];
GRANT EXECUTE ON [audit_record_request] TO [SessionsServiceProcedureAudit];
GO
— Enable back the disabled ‘SessionsService’ queue
—
ALTER QUEUE [SessionsQueue] WITH STATUS = ON;
GO
— Check that the response is now sent back
WAITFOR (RECEIVE CAST(message_body AS XML) FROM [RequestsQueue]);
Notice that I did not sent another request. The existing request was still there, waiting for the procedure to be fixed so it can be processed correctly.
In my previous entry http://rusanu.com/2006/01/12/why-does-feature-not-work-under-activation/ I mentioned that one way to enable an activated procedure to be trusted at the server level is to sign the stored procedure with a certificate and grant appropriate permissions to the signing certificate. In this entry I want to show the details how to do this.
I’ve seen this question comming back again and again on forums and discussion groups. The ALTER DATABASE … SET ENABLE_BROKER seems to run forever and doesn’t complete. How long should one wait?
This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.
There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ] | ROLLBACK IMMEDIATE | NO_WAIT
-ROLLBACK will close all existing sessions, rolling back any pending transaction.
– NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.
ALTER DATABASE [<dbname>] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
A number of features behave differently when running under activated stored proc. Selecting from server level views, selecting from dynamic management views, using linked servers and other. Executing the same procedure from a user session yields different results. E.g. if the procedures looks up a dynamic management view like sys.dm_…, it gets fewer rows when running under activation than when running from a user session.
What happens is that you overlook the fact that activation is executing under an EXECUTE AS context. The BOL have a great article explaining the behavior of EXECUTE AS, ‘Extending Database Impersonation by Using EXECUTE AS’, http://msdn2.microsoft.com/en-us/library/ms188304.aspx. It is not activation that causes the different behavior, but the fact that activation ALWAYS uses an EXECUTE AS context. The ‘Troubleshooting Activated Stored Procedures’ topic in BOL (http://msdn2.microsoft.com/en-us/library/ms166102(en-US,SQL.90).aspx) actually explains this and recommends that you also use an EXECUTE AS in the user session when invoking the procedure for debugging purposes, to get the same behavior and results as when activated.
A short explanation of the problem is that the activation execution context is trusted only in the database, not in the whole server. Anything related to the whole server, like a server level view or a dynamic management view or a linked server, acts as if you logged in as [Public].
The recommended way to solve the issue is to sign the procedure with a server level certificate that has the proper rights needed for the operation in question (see http://msdn2.microsoft.com/en-us/library/ms181700.aspx). Another possible way to fix the problem is to mark the database as TRUSTWORTHY, using ALTER DATABASE. But you better read the carefully the ‘Extending Database Impersonation by Using EXECUTE AS’ before doing this step and make sure you understand all the implications. You should do this only if you completely trust the dba of the database in question, as he becomes a de facto sysadmin when the database is marked as trustworthy.
