Commands / Complete-sqmListenerMigration
Complete-sqmListenerMigration
Always On sqmSQLTool v1.8.2+ · Maintenance ✓ -WhatIf supported  ⚠ ConfirmImpact: High
Step 2 of the listener migration workflow — run this function after the cluster team has deleted the old listener cluster resource and created a new one with the same DNS name.

The function: verifies the AG exists, captures database state before, performs a soft DNS resolution check on the new listener, calls ALTER AVAILABILITY GROUP … ADD LISTENER, waits 5 seconds, verifies the listener was registered, rechecks database state after, and writes a TXT completion report.

Returns a [PSCustomObject] with Status = 'SUCCESS' (all DBs online), 'PARTIAL_SUCCESS' (some DBs still in recovery), or 'FAILED' (exception). A 'CANCELLED' result is returned when -WhatIf is used.

Critical: Only run after the cluster team confirms the new listener cluster resource is ONLINE. Prepare phase: Invoke-sqmListenerMigrationPrep.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringOptional$env:COMPUTERNAMESQL Server instance hosting the AG (primary).
-AvailabilityGroupNamestringRequired—Name of the Availability Group to which the listener is re-added.
-ListenerNamestringRequired—DNS name of the new listener cluster resource.
-SqlCredentialPSCredentialOptional—Credential for the SQL Server connection.
-OutputPathstringOptionalC:\System\WinSrvLog\MSSQLDirectory for the completion report. Created if missing.
-EnableExceptionswitchSwitch$falseRe-throw errors instead of returning a FAILED result object.
-WhatIf / -ConfirmswitchOptional—ShouldProcess with ConfirmImpact=High. -WhatIf skips the ALTER statement and returns Status='CANCELLED'.

Execution Flow

START dbatools installed? NO throw: dbatools not found YES -SqlInstance provided? NO → Default: $env:COMPUTERNAME Ensure OutputPath New-Item -ItemType Directory -Force (if not present) AG exists on SqlInstance? NO throw: AG not found YES Get DB status BEFORE listener addition dm_hadr_availability_replica_states — warn if DBs in recovery Soft pre-checks (continue on error) DNS resolve ListenerName (warn if fail) | xp_regread cluster name (ignore fail) ShouldProcess? (ConfirmImpact: High) NO return Status= 'CANCELLED' YES ALTER AVAILABILITY GROUP ADD LISTENER N'ListenerName' (PORT = 1433) | Start-Sleep 5s Verify listener registered + DB status AFTER sys.availability_group_listeners → not found → throw | recheck database_state_desc Write TXT completion report to OutputPath Listener-Migration-Complete-<AG>-<date>.txt Return [PSCustomObject] Status: SUCCESS (all DBs online) | PARTIAL_SUCCESS (DBs still in recovery) DONE

Examples

Complete the listener migration for ProdAG (run after cluster team is done)
Complete-sqmListenerMigration `
    -SqlInstance "SQL02" `
    -AvailabilityGroupName "ProdAG" `
    -ListenerName "PROD-SQL-Listener"
Dry-run: preview without altering the AG
Complete-sqmListenerMigration -SqlInstance "SQL02" -AvailabilityGroupName "ProdAG" -ListenerName "PROD-SQL-Listener" -WhatIf
← Back
Command Index
Related
Add-sqmDatabaseToAG