sqmSQLTool › Commands › Invoke-sqmServiceBrokerAlwaysOn

Invoke-sqmServiceBrokerAlwaysOn Always On

Orchestrates enabling Service Broker across all nodes of an AlwaysOn AG. Operates in two modes: FAILOVER_MODE — when the AG exists and the broker is disabled, the function cycles through each replica (failover → Enable-sqmServiceBroker → failback) to ensure the broker is enabled at the primary level on each node; ENDPOINT_ONLY_MODE — when the broker is already enabled or the database was removed from the AG, creates the SSBEndpoint on each instance without failovers.

Execution Flow

START dbatools + min 2 instances? throw; return Connect-DbaInstance $SqlInstances[0] → query sys.databases WHERE name='$DatabaseName' → is_broker_enabled BrokerEnabled = (result = 1) sys.availability_groups JOIN sys.availability_replicas JOIN dm_hadr_availability_replica_states AG found → agExists=true, currentPrimary | not found → agExists=false (graceful, OK after DB removed from AG) agExists AND NOT brokerEnabled? -Force or ShouldProcess? User cancelled; return null FAILOVER_MODE AG exists + broker disabled No (broker enabled or no AG) ↓ ENDPOINT_ONLY_MODE foreach $instance in $SqlInstances Invoke-DbaAgFailover → $instance primary Enable-sqmServiceBroker → verify role=PRIMARY Start-Sleep $WaitBetweenFailovers after each Then: Invoke-DbaAgFailover back to $currentPrimary foreach $instance in $SqlInstances sys.service_broker_endpoints WHERE name='SSBEndpoint' Not found → CREATE ENDPOINT SSBEndpoint (TCP 4022) | else SKIPPED Write log file → $OutputPath\ServiceBrokerAlwaysOn_{instance}_{AG}_{timestamp}.txt Return PSCustomObject { AvailabilityGroup, DatabaseName, OriginalPrimary, InstanceResults[], OperatingMode, OverallStatus, LogPath } DONE

Synopsis

The operating mode is determined after checking both the broker status and AG existence: agExists AND NOT brokerEnabled → FAILOVER_MODE; otherwise → ENDPOINT_ONLY_MODE. In FAILOVER_MODE, each instance is made primary in turn, Enable-sqmServiceBroker is called, and a -WaitBetweenFailovers sleep allows health checks between failovers. After processing all nodes, the original primary is restored via a final failover. The SSBEndpoint uses TCP port 4022 with Windows authentication and grants CONNECT to [PUBLIC].

Requires dbatools and sysadmin permissions on all instances. At least 2 instances must be provided.
FAILOVER_MODE performs multiple failovers — plan for brief availability interruptions. Use in a maintenance window. The -WaitBetweenFailovers default of 15 seconds may need to be increased for slower networks or larger databases.

Syntax

Invoke-sqmServiceBrokerAlwaysOn
    -SqlInstances <String[]>              # required; min 2 instances
    -AvailabilityGroupName <String>        # required
    -DatabaseName <String>                 # required
    [-SqlCredential <PSCredential>]
    [-Force]                                # skip confirmation
    [-OutputPath <String>]                 # default: C:\System\WinSrvLog\MSSQL
    [-WaitBetweenFailovers <Int>]          # default: 15 seconds
    [-ContinueOnError] [-EnableException]
    [-WhatIf] [-Confirm]

Parameters

ParameterTypeDefaultDescription
-SqlInstancesString[]—Required. All SQL instances in the AG. Must have at least 2 elements.
-AvailabilityGroupNameString—Required. Name of the Availability Group.
-DatabaseNameString—Required. Name of the database to enable Service Broker on.
-SqlCredentialPSCredential—Optional SQL authentication for all instances.
-ForceSwitch$falseSkip the ShouldProcess confirmation prompt.
-OutputPathStringC:\System\WinSrvLog\MSSQLDirectory for the orchestration log file.
-WaitBetweenFailoversInt15Seconds to wait after each failover before proceeding.
-ContinueOnErrorSwitch$falseContinue processing remaining instances on error.

Return Value

PropertyDescription
AvailabilityGroupAG name.
DatabaseNameDatabase processed.
OriginalPrimaryPrimary replica before any failovers.
InstanceResultsPer-instance results: Instance, Role, BrokerEnabled, Status (SUCCESS/FAILED/SKIPPED), ErrorMessage.
SuccessfulInstancesCount of instances with Status SUCCESS or SKIPPED.
TotalInstancesTotal number of instances processed.
OperatingModeFAILOVER_MODE or ENDPOINT_ONLY_MODE.
OverallStatusSUCCESS (all instances OK) or PARTIAL (some failed).
LogPathPath to the orchestration log file.

Examples

# Enable Service Broker on a 3-node AG
Invoke-sqmServiceBrokerAlwaysOn `
    -SqlInstances @("SQL01", "SQL02", "SQL03") `
    -AvailabilityGroupName "MyAG" `
    -DatabaseName "OperationsManager"

# Longer wait between failovers for slower environments
Invoke-sqmServiceBrokerAlwaysOn `
    -SqlInstances @("SQL01", "SQL02") `
    -AvailabilityGroupName "ProdAG" `
    -DatabaseName "MyDB" -Force -WaitBetweenFailovers 30

# Check result
$r = Invoke-sqmServiceBrokerAlwaysOn `
    -SqlInstances @("SQL01", "SQL02") `
    -AvailabilityGroupName "MyAG" -DatabaseName "AppDB" -Force
Write-Host "Mode: $($r.OperatingMode) | Status: $($r.OverallStatus)"
$r.InstanceResults | Format-Table