IQueue ServiceBroker Configuration

posted Dec 21, 2012, 3:01 PM by Greg Kent   [ updated Jul 25, 2016, 4:52 PM ]

The following section outlines the steps involved with implementing IQueue.ServiceBroker:


Enable Service Broker in Database

Service Broker must be enabled in a SQL 2008 database. The current implementation supports enabling Service Broker on a per database level. The following must be performed after-hours or when the system is offline as it requires single user access to the database.

  • Ensure Queue Service is tempoairly stopped
  • Run the following script:
USE master;
GO
ALTER DATABASE [{DatabaseName}]SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [{DatabaseName}]
SET ENABLE_BROKER;
GO
USE [{DatabaseName}];
GO
ALTER authorization on database::[{DatabaseName}] to [sa];
GO
CREATE MESSAGE TYPE
[//QBO/IQueue/SBMessage]
VALIDATION = WELL_FORMED_XML;
GO
CREATE MESSAGE TYPE
[//QBO/IQueue/SBAcknowledgement]
VALIDATION = WELL_FORMED_XML;
GO
USE master
ALTER DATABASE [{DatabaseName}]
SET MULTI_USER;
GO

Where {DatabaseName} is target database name

  • Ensure the SQL Account in ConnectionStrings.config used to access the database has DBO permissions on the target database

Issues with a restored database where ServiceBroker was enabled

For scenarios where a DEV database was restored to UAT (or UAT to PROD), if you encounter the error:

The Service Broker in database "{Database Name}" cannot be enabled because there is already an enabled Service Broker with the same ID.
You will need to execute the following:
Database must be in Single User Mode. The following script will set Single User Mode. If unable to set Single User, you may need to run sp_who2 and begin killing processes running on database.
ALTER DATABASE [{DatabaseName}]
SET SINGLE_USER
GO
ALTER DATABASE [{DatabaseName}]
SET DISABLE_BROKER;

ALTER DATABASE [{DatabaseName}]
SET NEW_BROKER;
ALTER DATABASE [{DatabaseName}]
SET MULTI_USER;
GO

Ensure database is switched back to multi-user mode. The script set this as well.

Deploy Service Broker Plug-In

  • Obtain latest qbo.Queue.MSSQL from /qbo3/Plugins/Queuing
  • Build and Deploy to {ApplicationRoot}/qbo3/
  • Ensure "ServiceBroker" configuration sections reside in web.config. These can be obtained by viewing qbo.ApplicationWeb web.config Revision 14987 or later
  • Ensure latest qbo.Application.Web /Templates/Configuration/QueueEdit.xslt - Revision 14986 or later

Add a IQueue.ServiceBroker to Queuing.Config

  • Navigate to /Application/Queuing.ashx
  • Add desired Queue. Ensure Type = "ServiceBroker". Ensure no spaces in the Queue Uri.
  • Start Queue

Troubleshooting / Fixes

Resetting the User Account for Service Broker

Sometimes when you restore the database the login@quandis.net user account gets set back to public.  This means that while the website will work the service broker will not because it needs more permissions specifically db_owner.  One of the symptoms of this will be items in the staging and current queues but not processing in the default queue.  If you run the following stored procedure it will reset the login user and then you need to go in and set them to db_owner.  Finally restart the queue and this should resolve the issue.


EXEC sp_change_users_login 'Auto_Fix', 'login@quandis.net'

Purging Transmission_Queue

Sometimes the Transmission Queue will accumulate invalid messages. This can be due to a DB restoration or invalid configuration. This can lead to unwanted ApplicationLog entries. To check the Transmission Queue in the respective database:


SELECT * FROM sys.Transmission_Queue

Note the transmission_status column. You can purge messages by executing:

declare @conversation uniqueidentifier

while exists (select 1 from sys.transmission_queue )

begin

set @conversation = (select top 1 conversation_handle from sys.transmission_queue )

end conversation @conversation with cleanup

end


Comments