Post date: Dec 21, 2012 11:1:24 PM
The following section outlines the steps involved with implementing IQueue.ServiceBroker:
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.
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
Navigate to /Application/Queuing.ashx
Add desired Queue. Ensure Type = "ServiceBroker". Ensure no spaces in the Queue Uri.
Start Queue
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