Troubleshooting PowerFAIDS Integration 9 SQL Service Broker

PFI in PowerCampus 9 replaces the old desktop integration application with NServiceBus and SQL Service Broker. I wasn't previously familiar with SQL Service Brokers, so wanted to share a few things we've learned so far. Please contribute your own tips as well; it's a big topic.

SSB is similar to Microsoft Message Queue (MMQ) used by the older Service Bus behind eTranscripts. It asynchronously passes message from a source to a destination. In this case, the source is a "service" named PowerFaidsServerService in database PowerCampusEvents and the destination is PowerFaidsClientService in database Campus6. (Unless I have source and destination mixed up. Regardless, messages are passing between them.)

Microsoft has bundled a helpful tool with SQL Server Management Studio called ssbdiagnose that can help identify problems with SSB. It returns pretty decent, understandable error messages. Here are some sample commands.

1. The first one checks communication between two databases.
2. The second checks the status of a single conversation.
3. The third checks communication between two services.

cd "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\"
.\ssbdiagnose.exe -E -S sqlserver.school.edu RUNTIME -TIMEOUT 10 CONNECT TO -d Campus6 CONNECT TO -d PowerCampusEvents

# Replace the ID GUID with a current conversation GUID
.\ssbdiagnose.exe -E -S sqlserver.school.edu RUNTIME -SHOWEVENTS -ID 1b221ef6-c51d-4a62-8f82-ad1f01105823 -TIMEOUT 10 CONNECT TO -d Campus6 CONNECT TO -d PowerCampusEvents

.\ssbdiagnose.exe -S sqlserver.school.edu CONFIGURATION FROM SERVICE PowerFaidsClientService -d Campus6 TO SERVICE PowerFaidsServerService -d PowerCampusEvents ON CONTRACT PowerFaidsContract ENCRYPTION OFF

You would replace sqlserver.school.edu with your SQL server name.

Conversation GUID's can be found by right-clicking dbo.PowerFaidsQueue and selecting top 1000 rows; they also appears briefly in [PowerCampusEvents].[dbo].[PowerCampus.Publisher.Queue]. If you stop the PowerCampus.Events.Subscriber service, you can see any new conversation GUID's sitting in the table until you start the subscriber again.

I'm not sure what would happen if you ran ssbdiagnose on the SQL machine itself, but running from my workstation yielded errors about the 'Local' route not being accessible. This solution to this was to modify the routes in both Campus6 and PowerCampusEvents to use the actual address of the server instead of Local. Local does actually work, but I wanted to be able to use ssbdiagnose. See the before and after:

--Your BROKER_INSTANCE will be different; don't copy and paste these
--Before
ALTER ROUTE [PowerFaidsClientRoute]   WITH  SERVICE_NAME  = N'PowerFaidsClientService' ,  BROKER_INSTANCE  = N'400D3247-970A-4580-AC96-A2A374EAD5E8' ,  ADDRESS  = N'Local' 
--After
ALTER ROUTE [PowerFaidsClientRoute]   WITH  SERVICE_NAME  = N'PowerFaidsClientService' ,  BROKER_INSTANCE  = N'400D3247-970A-4580-AC96-A2A374EAD5E8' ,  ADDRESS  = N'tcp://sqlserver.school.edu' 

After setting the routes to have real TCP addresses, I can run the ssbdiagnose commands and get 0 errors and 0 warnings.

Sample Errors and Resolutions

Duplicate Service Broker GUIDs

A problem we've run into several times is that each database has a unique service broker GUID. Restoring Campus6 as Campus6_test or Campus6_YearEnd2020 will cause a duplicated broker GUID. You can fix this by forcing the service broker GUID to be reset.

ALTER DATABASE [campus6_test] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

Wrong Database Owner

Messages on conversation handle c1d298b7-6a5f-ec11-b821-005056b0f0dd (conversation id cbde642c-1010-4b6a-bc5f-cc026ecc5951) from service PowerFaidsClientService to service PowerFaidsServerService cannot be transmitted dues to the following problem: An exception occurred while enqueueing a message in the target queue. Error: 33019, State: 1. Cannot create implicit user for the special login 'sa'.

Resolution: Make sure your PowerCampusEvents database owner is sa, not the IT user who installed it.

USE PowerCampusEvents
GO

EXEC sp_changedbowner sa;

Service Broker Disabled

PowerCampusEvents Messages on conversation handle 95f134c6-37ab-ee11-a74e-005056b4271a (conversation id 2c38c49e-66b3-47e9-b560-5be6b0368d3b) from service http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService to service PowerFaidsServerService cannot be transmitted dues to the following problem: The broker is disabled in the sender's database.

PowerCampusEvents  Service Broker is not enabled in the database

Check that both campus6 and PowerCampusEvents have the broker enabled:

SELECT [name]
	,is_broker_enabled
	,service_broker_guid
FROM [master].sys.databases

Enable the broker. Unfortunately, I usually have to force a rollback of ongoing transactions for this to succeed. Therefore, consider shutting down traffic before forcing this:

ALTER DATABASE campus6 SET ENABLE_BROKER
-- WITH ROLLBACK IMMEDIATE -- Danger!

Duplicate Service Names

There are multiple services named PowerFaidsServerService on the instance available to be picked from by the LOCAL route

Currently, Ellucian does not support having multiple PowerCampusEvents databases on the same server (e.g. PowerCampusEvents and PowerCampusEvents_test). While it's possible to work around this limitation, abiding by it is the easier solution.

Campus6 is not Trustworthy

Could not validate the SEND permission of user dbo on service PowerFaidsServerService due to exception The server principal "sa" is not able to access the database "PowerCampusEvents" under the current security context.

Database Campus6 is required to have the TRUSTWORTHY option set to deliver messages to a different database if no dialog security is configured using a REMOTE SERVICE BINDING

Unfortunately, as far as I know, Ellucian requires/assumes that campus6 have the TRUSTWORTHY option enabled. Enabling this setting has security ramifications beyond the scope of this article.

Check the current setting:

SELECT [name]
	,is_trustworthy_on
FROM [master].sys.databases
WHERE [name] = 'campus6';

Alter the setting:

ALTER DATABASE [campus6] SET TRUSTWORTHY ON;