Sergey Maskalik

Sergey Maskalik's blog

In the pursuit of mastery

What am I trying to solve..

If you are not familiar with Service Broker you can still use this as an example for Knockoutjs and ASP.NET Webmethods.

The biggest pain point with Service Broker (SB) is how long it takes to understand what exactly is happening when you a have a problem. In order to find a problem sometimes you need to execute good amount of queries, read through the results analyze them and eventually find out what the issue is. It becomes a lot more complicated when you have event notifications and queue monitors on your queues. And if you don’t always work with SB you will eventually forget how things work.

In this post we will have some fun building a color coded status page which will show status of our queues, events, messages and queue monitors. It should be as easy as glancing at the screen and if anything is red that means there is a problem. As a bonus you can also add internal activation on your own.

Create a health status query

SELECT * FROM 
(
SELECT Q.Name
   ,CASE
        WHEN EN.name LIKE '%QueueActivation'
            THEN 'Activation'
        WHEN EN.name LIKE '%QueueDisabledEmail'
            THEN 'DisabledEmail'
        WHEN EN.name LIKE '%QueueDisabled'
            THEN 'Resolve'
    END AS EventLetter
   ,QM.state
   ,Q.is_enqueue_enabled AS [Enabled]
   ,po.rows AS MessageCount
   ,Q.is_activation_enabled as InternalActivationEnabled
   ,Q.activation_procedure	as InternalActivationProcedureName   
FROM sys.service_queues AS Q
   JOIN sys.objects AS o ON o.parent_object_id = Q.object_id
   JOIN sys.partitions AS po ON po.object_id = o.object_id
   JOIN sys.objects AS qo ON o.parent_object_id = qo.object_id
   LEFT JOIN sys.event_notifications AS EN ON Q.object_id = EN.parent_id
   LEFT JOIN sys.dm_broker_queue_monitors AS QM ON Q.object_id = QM.queue_id
WHERE Q.is_ms_shipped = 0
   AND po.index_id = 1) DataTable PIVOT 
   ( COUNT(EventLetter) FOR EventLetter IN ( [Activation], [Resolve], [DisabledEmail] ) 
) AS pvt 

To summarize we get all queues in the current database with their message count from partitions view. Btw this query has very low cpu cost on sql and executes in less than 10ms. We also left join event notifications and queue monitors since those are really important when you have external activation. I also pivot the event notifications and give them all similar name since they all represent same function but with different queue name. The result data looks like this:

NameStateEnabledMessageCountInternalActivationActivationResolveDisabledEmail
SampleInitiatorQueueINACTIVE101001
SampleTargetQueueINACTIVE100111
SecondInitiatorQueueINACTIVE101001
SecondTargetQueueINACTIVE100111

This gives us some information, but you still need to know if there is something wrong, that’s where we put knockoutjs to work and color code our results.

Create View Model

var SM = SM || {};
SM.QueueHealthModel = function () {
    var self = this;
    //Data
    self.loadedHealthData = ko.observable();
    self.ajaxErrors = ko.observable();
    //Behaviours
    self.loadHealthData = function () {
        SM.data.getQueueHealthData(self.loadedHealthData);
    };

    self.loadHealthData(); //execute on load
};

And required data function

SM.data = {
    getQueueHealthData: function (callback) {
        $.ajax({
            type: 'POST',
            url: 'Default.aspx/GetAllQueueHealthStatus',
            data: '{}',
            contentType: 'application/json; charset=utf-8',
            dataType: 'json',
            success: function (msg) {
                var data = { healthStatus: msg.d };
                callback(data);
            }
        });
    }
};

Webmethod

Use your favorite data access tool and return an array of data transfer objects

   [WebMethod]
    public static QueueHealthStatus[] GetAllQueueHealthStatus()
    {
        return MyRepository.GetAllQueueHealthStatus();
    }

Html

Finally we’ll add our html markup with knockout bindings

<!-- Errors -->
<div data-bind="visible: ajaxErrors">
<h2 style="margin: 10px 0;">Ajax communication error please check app layer</h2>
<p style="color: red" data-bind="text: ajaxErrors"></p>
</div>

<!-- Health Status -->
<table class="queue-status newspaper-a" data-bind="with: loadedHealthData">
    <thead><tr><th>Queue</th><th>State</th><th>Enabled</th><th>Message Count</th><th>Internal Activation</th><th>Activation</th><th>Resolve</th><th>Disabled Email</th></tr></thead>
    <tbody data-bind="foreach: healthStatus">
        <tr>
            <td data-bind="text: QueueName, click: $root.goToImportLog" class="pointer"></td>
            <td data-bind="text: State, style: { backgroundColor: SM.format.stateColor(State, QueueName) }"></td>
            <td data-bind="text: Enabled, style: { backgroundColor: SM.format.queueEnabledColor(Enabled) }"></td>
            <td data-bind="text: MessageCount, style: { backgroundColor: SM.format.messageCountColor(MessageCount), fontWeight: MessageCount > 0 ? 'bold' : 'normal' }"></td>
            <td data-bind="text: InternalActivationEnabled, style: { backgroundColor: DOM.format.internalActivationColor(QueueName,InternalActivationEnabled) }, attr: { title: InternalActivationProcedureName }"></td>
            <td data-bind="text: ActivationEventEnabled, style: { backgroundColor: SM.format.activationColor(QueueName, ActivationEventEnabled) }"></td>
            <td data-bind="text: ResolveEventEnabled, style: { backgroundColor: SM.format.resolveColor(QueueName, ResolveEventEnabled) }"></td>
            <td data-bind="text: EmailOnDisabledEventEnabled, style: { backgroundColor: SM.format.disableEmailColor(QueueName, EmailOnDisabledEventEnabled) }"></td>
        </tr>
    </tbody>
</table>

Color utility functions

To break down formatting:

  • If the queue manager is INACTIVE, it’s a normal state. NOTIFIED is yellow because it could be stuck in the notified state when activation is not working properly.
  • If queue is disabled that automatic red.
  • If message count is greater than 10, we set color to yellow.
  • For each activation we have individual functions that look at queue name and the state of the activation and figure out if the state is correct. For example disabled email activation is valid on all queues, where activation notification is only valid on target queues.

Format color functions used in knockoutjs bindings. We use immediate function to return an object with functions so the variables used inside don’t pollute global namespace and accessible to the internal functions.

SM.format = (function () {
    var green = '#9AFF9A',
    red = '#FF6347',
    yellow = 'yellow';
    return {
        stateColor: function (state, QueueName) {
            if (QueueName === 'ExternalActivationQueue') {
                return 'white';
            }
            if (state === null) {
                return red;
            }
            if (state === 'NOTIFIED') {
                return yellow;
            }
            if (state === 'INACTIVE') {
                return green;
            }
        },
        queueEnabledColor: function (value) {
            if (value) {
                return green;
            }
            return red;
        },
        messageCountColor: function (numberOfMessages) {
            if (numberOfMessages > 10) {
                return yellow;
            }
            return green;
        },
        activationColor: function (QueueName, exists) {
            if (QueueName.toLowerCase().indexOf("target") !== -1) {
                if (exists) {
                    return green;
                }
                return red;
            }
            if (!exists) {
                return green;
            }
        },
        internalActivationColor : function (queueName, exists) {
            if (queueName.toLowerCase().indexOf("initiator") !== -1 {
                if (exists) {
                    return green;
                }
                return red;
            }
            if (!exists) {
                return green;
            }
        },
        resolveColor: function (QueueName, exists) {
            if (QueueName.toLowerCase().indexOf("target") !== -1) {
                if (exists) {
                    return green;
                }
                return red;
            }
            if (!exists) {
                return green;
            }
        },
        disableEmailColor: function (QueueName, exists) {
            if (exists) {
                return green;
            }
            return red;
        }
    };
} ());

Error handling and loading indicator

There is an awesome jquery ajax global event handler that will catch all ajax exceptions, it’s especially useful when you have many queries running on the page and you don’t want to add error handling logic to each one.

$('body').ajaxError(function (event, request) {
    model.ajaxErrors(request.response);
});

There is also one for loading we can use like this.

$(".loadingIndicator").ajaxStart(function () {
    $(this).fadeIn('fast');
}).ajaxComplete(function () {
    $(this).fadeOut('fast');
});

That’s it. Now when something is wrong with your service broker, it’s very easy to just glance at the screen to understand if something is wrong. You can get more creative and add timeout polling so you can watch how service broker works in real time. And you can also add tool tips on how to resolve issues and maybe even buttons that will fix issues directly from dashboard. Sky is the limit, and this is a good start in the right direction in my opinion.

It took me some time to figure our this part even though it should be trivial. So hopefully this guide will help someone in the future. I think the hardest part was to understand the security context under which internal stored procedures run when activated by service broker.

First thing you need to do is to create a DisabledQueueNotificationQueue. I know I know, it’s a little redundant name but what it means is that you have a queue that receives notifications when other queues get disabled, therefore redundant name.

CREATE QUEUE dbo.DisabledQueueNotificationQueue

Create service endpoint with PostEventNotification message type

CREATE SERVICE DisabledQueueNotificationService
ON QUEUE DisabledQueueNotificationQueue
(
	[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
)
GO

Next is we need to setup a notification procedure which will execute msdb.dbo.sp_send_mail

CREATE PROCEDURE [dbo].[spServiceBroker_SendDisabledQueueEmailNotification]
WITH EXECUTE AS OWNER
AS
	DECLARE @ch UNIQUEIDENTIFIER
	DECLARE @messagetypename NVARCHAR(256)
	DECLARE	@messagebody XML
	DECLARE @queueName varchar(100)
	DECLARE @emailTo VARCHAR(500)
	
	
	SET @emailTo = 'someaddress@yahoo.com'

	WHILE (1=1)
	BEGIN
		BEGIN TRY
			BEGIN TRANSACTION

			WAITFOR (
				RECEIVE TOP(1)
					@ch = conversation_handle,
					@messagetypename = message_type_name,
					@messagebody = CAST(message_body AS XML)
				FROM DisabledQueueNotificationQueue
			), TIMEOUT 60000

			IF (@@ROWCOUNT = 0)
			BEGIN
				ROLLBACK TRANSACTION
				BREAK
			END

			IF (@messagetypename = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
			BEGIN
				SET @queueName = 'Disabled queue: ' + @messagebody.value('/EVENT_INSTANCE[1]/ObjectName[1]', 'VARCHAR(100)');
				
				EXEC msdb.dbo.sp_send_dbmail 
					@profile_name = 'DBA_Notifications',
					@recipients = @emailTo,
					@body = @queueName,
					@subject = @queueName;
				
			END
			
			IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
            BEGIN
				  DECLARE @errorcode INT
				  DECLARE @errormessage NVARCHAR(3000)
                  -- Extract the error information from the sent message
                  SET @errorcode = (SELECT @messagebody.value(
                        N'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"; 
                        (/brokerns:Error/brokerns:Code)[1]', 'int'));
                  SET @errormessage = (SELECT @messagebody.value(
                        'declare namespace brokerns="http://schemas.microsoft.com/SQL/ServiceBroker/Error";
                        (/brokerns:Error/brokerns:Description)[1]', 'nvarchar(3000)'));

                  -- Log the error

                  -- End the conversation on the initiator's side
                  END CONVERSATION @ch;
            END


			IF (@messagetypename = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
			BEGIN
				-- End the conversation
				END CONVERSATION @ch;
			END

			COMMIT TRANSACTION
		END TRY
		BEGIN CATCH
			ROLLBACK TRANSACTION
			DECLARE @ErrorNum INT
			DECLARE @ErrorMsg NVARCHAR(3000)
			SELECT @ErrorNum = ERROR_NUMBER(), @ErrorMsg = ERROR_MESSAGE()
			-- log the error
			BREAK
		END CATCH
	END

The important piece is that this procedure will execute as owner which will be a dbo schema when service broker activates the internal procedure.

Next we add the queue internal activation

ALTER QUEUE DisabledQueueNotificationQueue
WITH ACTIVATION 
(
	PROCEDURE_NAME = dbo.spServiceBroker_SendDisabledQueueEmailNotification,
	STATUS = ON,
	MAX_QUEUE_READERS = 1,
	EXECUTE AS OWNER
)

Finally we create an event notification to send messages when queue gets disabled into the DisabledQueueNotifiactionQueue

CREATE EVENT NOTIFICATION DisabledTargetQueueNotification
    ON QUEUE dbo.TargetQueue
    FOR BROKER_QUEUE_DISABLED
    TO SERVICE 'DisabledQueueNotificationService', 'current database';

If we try to simulate a poison message and rollback receive 5 times the procedure won’t execute and will log an error:

The EXECUTE permission was denied on the object ‘sp_send_dbmail’, database ‘msdb’, schema ‘dbo’.

This is expected since the activated stored procedure is calling an msdb database and has no permissions since it is in the public context. Since I trust other database on my sql server I thought that enabling trustworthy would fix this problem: ALTER DATABASE [msdb] SET TRUSTWORTHY ON. But apparently the setup of my sql server would still not allow a public process to access system database. So the only one thing that was left to do is to create a certificate and sign the procedure so it would be trusted.

CREATE CERTIFICATE spServiceBroker_SendDisabledQueueEmailNotificationCertificate
	ENCRYPTION BY PASSWORD = 'SommePass91'
	WITH SUBJECT = 'spServiceBroker_SendDisabledQueueEmailNotification signing certificate'
GO

ADD SIGNATURE TO OBJECT::[spServiceBroker_SendDisabledQueueEmailNotification]
	BY CERTIFICATE [spServiceBroker_SendDisabledQueueEmailNotificationCertificate]
	WITH PASSWORD = 'SommePass91'
GO


--------------------------------------------------------------------------------
-- We leave the private key so we can resign the procedure later if it changes.
-- If we remove the certificate the whole thing will need to be recreated
--------------------------------------------------------------------------------
--ALTER CERTIFICATE spServiceBroker_SendDisabledQueueEmailNotificationCertificate
--	REMOVE PRIVATE KEY
--GO

BACKUP CERTIFICATE [spServiceBroker_SendDisabledQueueEmailNotificationCertificate]
	TO FILE = 'c:\spServiceBroker_SendDisabledQueueEmailNotificationCertificate.cert'
GO

USE msdb
GO

CREATE CERTIFICATE [spServiceBroker_SendDisabledQueueEmailNotificationCertificate]
	FROM FILE = 'c:\spServiceBroker_SendDisabledQueueEmailNotificationCertificate.cert'
GO


CREATE USER [spServiceBroker_SendDisabledQueueEmailNotificationUser]
  FROM CERTIFICATE [spServiceBroker_SendDisabledQueueEmailNotificationCertificate]
GO

GRANT AUTHENTICATE TO [spServiceBroker_SendDisabledQueueEmailNotificationUser]

GRANT EXECUTE ON [sp_send_dbmail] TO [spServiceBroker_SendDisabledQueueEmailNotificationUser];

Now that the procedure has access to the sp_send_dbmail we have no issues and if you simulate poison message, you should receive an email notification with the name of the queue.

Since I’ve been working with Service Broker I realized how tricky it is to administer and hard to see overall picture. I still come across behaviors I don’t understand, and now sure how exactly to reproduce them, they just happen randomly. However I did make a lot of progress on troubleshooting and can get most things back up and running again.

Dialogs

It’s important to know that dialogs are two way conversations. Before you send a message to the target you open a dialog conversation. And it will stay open until your target picks it up, calls end conversation and then your initiator side will end conversation as well. Only after your conversation will be closed. And closed conversation is a successful conversation. You have to monitor your conversations to make sure they all get closed properly during development and troubleshooting.

--all going conversations
select * from sys.conversation_endpoints

Clean up conversations, DON’T USE THIS IN PRODUCTION AS conversations will get deleted.

Update 7/18/2012 Please note when you remove all conversations it will also disable all EventNotification and so your event notifictions will need to be dropped and created again.

-- clean up all conversations, DEVELOPMENT ONLY 
declare @conversation uniqueidentifier
while exists (select 1 from sys.conversation_endpoints with (nolock))
begin
set @conversation = (select top 1 conversation_handle from sys.conversation_endpoints with (nolock) )
end conversation @conversation with cleanup
end

If you are building some kinda of a tool that will show the status of your messages you will probably want to save conversation handles and map it to the id of your message that way you can trace back to what’s going on with your message.

Event notifications

Event notifications add extra learning of how notifications work, and know how to fix them.

First thing you define your event notification

CREATE EVENT NOTIFICATION EventNotificationTargetQueue
    ON QUEUE dbo.TargetQueue
    FOR QUEUE_ACTIVATION
    TO SERVICE 'ExternalActivatorService', 'E56C42F3-9885-0000-8983-7CA3B5C32362';

and you should see it in

--see event notifications
SELECT * FROM sys.event_notifications

I found that if you clean up your conversations sometimes event notifications will disappear, so it’s a good idea to keep an eye on the sys.event_notifications if you activation is not being triggered.

Now we get to the fun part. After we have turned on activation on a queue and send a message, the queue will go into a NOTIFIED state. When queue goes into a notified state the external activation queue will receive a message saying that the queue has been activated. The question is, does the external activation queue receives an activation message for each message that goes into a queue, the answer is no. Only when a queue is not in NOTIFIED state then when you put a message into a queue it will trigger an activation queue message and put queue into NOTIFIED.

--see queue monitors 
select * from sys.dm_broker_queue_monitors m with (nolock)
join sys.service_queues q with (nolock) on m.queue_id = q.object_id

And there are times when queue can get stuck in NOTIFIED state and you have no message in the queue. If that happens you will have to run this command

RECEIVE TOP(0) * FROM TargetQueue;

If the messages were cleared improperly this will fix the queue and set it back to INACTIVE state.

Event notification troubleshooting

It’s important to understand that every time you create an event notification that creates a queue monitor for your queue, you can view queue monitors:

select * from sys.dm_broker_queue_monitors m with (nolock)
join sys.service_queues q with (nolock) on m.queue_id = q.object_id

So even if you can see your event notifications in SELECT * FROM sys.event_notifications it does not mean that your queue will activate an event. It must have both queue monitor and state of the queue monitor is not in NOTIFIED state.

In addition, conversations on event notifications are not meant to be closed by calling “end dialog”. They need to stay open or in “CONVERSING” to properly dispatch notifications. The conversation on event notification will only get ended when you drop your event notification. If your queue in sys.dm_broker_queue_monitors is in the INACTIVE state and you see your event is in the sys.event_notifications, and your you still don’t receive activation messages, what happened is your conversation on the event notification got ended. To fix you can drop and create notification and it will work again.

That’s it for now, good luck troubleshooting external activation!