This article details how to enable Service Broker. Service Broker is a communication service that is used by the Solver Private Host app. It monitors completion of tasks between multiple applications.
What causes Service Broker to fail and error out?
If you restored the repository database on the SQL server or turned off the SQL Server service and then enabled it, then Service Broker is disabled and would need to be enabled. You may get the following error message on the web portal or something related to service broker:
"The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications."
How is Service Broker used in Private Host?
This cycle explains the process that Private Host goes through when running a report:
How to enable Service Broker?
- Check if Service Broker is enabled first before making any changes. Here is the query to verify if service broker is enabled:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'Solver_Repository';If the result is 1, then Service Broker is enabled and nothing needs to be done.
If it returns 0, then Service Broker is disabled and needs to be enabled.
- To enable Service Broker, put the repository into single user mode, start service broker, and then put the repository back into multi-user mode.
The below query will perform that action on the Repository Database:
declare @DatabaseName nvarchar(1000) ,@SQL nvarchar(max) Set @SQL = '' Set @DatabaseName ='' Select @DatabaseName = name from sys.databases where name in ('Solver_Repository','BI360_Repository') --print @databasename select @SQL = @SQL + 'USE [Master]' +char(13)+ 'Alter Database ['+@databasename+'] SET SINGLE_USER with ROLLBACK IMMEDIATE' +char(13)+ 'Alter Database ['+@databasename+'] SET ENABLE_BROKER WITH NO_WAIT' +char(13)+ 'Alter Database ['+@databasename+'] SET MULTI_USER' +char(13) --print @SQL exec sp_ExecuteSQL @SQL