Enable SQL 2005 broker service for SCOM 2007 - Who is that "Single-user"?
SCOM 2007 uses SQL2005 broker service to perform discoveries, so before you use the Discovery Wizard to install agents, you need to set the Enable_Broker value.
To set ENABLE_BROKER
|
1. Open SQL Server Management Studio.
2. In the Connect to Server dialog box, select the appropriate values in the Server type list, in the Server name list, in the Authentication list, and then click Connect.
3. Click New Query.
4. In the query window, enter the following query:
ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
5. Click Execute.
6. Enter the following query:
ALTER DATABASE OperationsManager SET ENABLE_BROKER
7. Click Execute.
8. Close SQL Server Management Studio.
Note
Closing SQL Server Management Studio closes the connection to the database in single user mode. Depending on your configuration, you may have to manually kill any process that is connected to the database before completing the ALTER query below.
9. Open SQL Server Management Studio.
10. In the Connect to Server dialog box, select the appropriate values in the Server type list, in the Server name list, in the Authentication list, and then click Connect.
11. Click New Query.
12. In the query window, enter the following query:
ALTER DATABASE OperationsManager SET MULTI_USER
13. Click Execute. |
You can verify the setting for ENABLE_BROKER is set to 1 by using this SQL query: SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'.
I was following the above instruction but when I try “ALTER DATABASE OperationsManager SET MULTI_USER”
I got an error contains “the database is in a single-user mode and a user is currently connect to the database” which puzzled me for a while, Tried to close SQL Server Management Studio a couple of times with out success and even tried to stop SQL service. Until I realized that “Root Management Server” is that “single-user”. After I stopped Config Service and the SDK Service, I was able to run the above statement without any issues. This is one of those “Duh” moment!