Commands / Enable-sqmServiceBroker
Enable-sqmServiceBroker
Configuration sqmSQLTool v1.8.2+ · Enable ✓ -WhatIf / -Force  ⚠ ConfirmImpact: High
Enables Service Broker on a specified database by executing the following sequence:
  1. Set database to SINGLE_USER WITH ROLLBACK IMMEDIATE — disconnects all active sessions
  2. Execute ALTER DATABASE … SET ENABLE_BROKER
  3. Restore MULTI_USER (rollback to MULTI_USER automatically if step 2 fails)
  4. Create SSBEndpoint on TCP port 4022 with WINDOWS authentication (skipped if already exists; failure is a warning, not fatal)
  5. Grant CONNECT ON ENDPOINT::SSBEndpoint TO PUBLIC (warning on failure)

A TXT log file is written for every run. Returns a [PSCustomObject] with Status = 'SUCCESS' (broker confirmed enabled) or 'PARTIAL' (endpoint step had issues). Suitable for both standalone and Always On configurations.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMESQL Server instance. Accepts pipeline input.
-DatabaseNamestringRequired—Database on which Service Broker is enabled.
-SqlCredentialPSCredentialOptional—Credential for the SQL Server connection.
-ForceswitchSwitch$falseSkip the ShouldProcess confirmation prompt.
-OutputPathstringOptionalC:\System\WinSrvLog\MSSQLDirectory for the log file. Created if missing.
-ContinueOnErrorswitchSwitch$falseSwallow the outer catch and return nothing instead of re-throwing.
-EnableExceptionswitchSwitch$falseAlways re-throw on error (overrides -ContinueOnError).
-WhatIf / -ConfirmswitchOptional—ShouldProcess (ConfirmImpact: High) guards the entire operation. Returns $null when cancelled.

Execution Flow

START dbatools installed? NO throw: dbatools not found YES -SqlInstance provided? NO → Default: $env:COMPUTERNAME Connect-DbaInstance + ensure OutputPath init log file: ServiceBrokerEnable_<server>_<timestamp>.txt ShouldProcess OR -Force? NO log "Abgebrochen" return $null YES Step 1 — SET SINGLE_USER WITH ROLLBACK IMMEDIATE disconnects all active sessions | fail → throw Step 2 — ALTER DATABASE SET ENABLE_BROKER fail → try SET MULTI_USER (rollback) → throw Step 3 — SET MULTI_USER Start-Sleep 500ms (let DB come back online) | fail → throw Step 4 — Create SSBEndpoint (IF NOT EXISTS) TCP LISTENER_PORT=4022, WINDOWS auth | already exists → skip | fail → warn (non-fatal) Step 5 — GRANT CONNECT ON ENDPOINT::SSBEndpoint TO PUBLIC fail → warn (non-fatal) Verify + write log SELECT is_broker_enabled FROM sys.databases | write .txt log to OutputPath Return [PSCustomObject] Status: SUCCESS (is_broker_enabled=1) | PARTIAL (endpoint issues) DONE

Examples

Enable Service Broker on the local default instance
Enable-sqmServiceBroker -DatabaseName "OperationsManager"
Enable on a remote instance, skip confirmation prompt
Enable-sqmServiceBroker -SqlInstance "SQL01" -DatabaseName "OperationsManager" -Force
← Back
Command Index
Related
Enable-sqmMonitoringAccess