Always On

New-sqmAvailabilityGroup

Creates an Always On Availability Group on an existing Windows Server Failover Cluster (WSFC) in a fully headless, idempotent 7-step process — enabling HADR, creating mirroring endpoints, seeding databases, cleaning up orphaned WSFC groups, creating the AG, joining secondaries, and optionally adding a listener.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes (High)
Output: PSCustomObject

Execution Flow

START dbatools available? throw error STEP 1: Enable HADR + restart SQL per replica STEP 2: Create mirroring endpoint (AES) per replica -ServiceAccount given? No Yes CREATE LOGIN + GRANT CONNECT ON ENDPOINT STEP 3: Optional seed DB creation (RECOVERY FULL) -CleanupOrphanedWsfcGroup? No Stop/Remove-ClusterGroup + registry cleanup (HadrAgNameToldMap) STEP 4: CREATE AVAILABILITY GROUP (primary, FailoverMode/AvailabilityMode/SeedingMode) foreach SecondaryReplica ALTER AG JOIN + GRANT CREATE ANY DATABASE + MODIFY REPLICA -ListenerName given? No STEP 5: ADD LISTENER (poll up to 60s for AG visibility) Optional: Write-sqmSetupEvent (-EventLog) Return PSCustomObject (Status=Success) DONE
High-impact operation (ConfirmImpact='High'): touches cluster state, restarts SQL services for HADR enablement, and creates production AG topology. Always validate with -WhatIf first.
Ported from the legacy AlwaysOnSetup.ps1 script. Assumes the WSFC already exists — this function does not create the Windows cluster, only the SQL Server Availability Group on top of it.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringRequiredPrimary replica SQL instance.
-SecondaryReplicaString[]RequiredOne or more secondary replica instances.
-AvailabilityGroupNameStringRequiredName of the new Availability Group.
-DatabaseString[]OptionalDatabases to add to the AG.
-ReplicaHostMapHashtableOptionalMaps replica name to endpoint hostname/FQDN.
-EndpointNameStringOptionalMirroring endpoint name. Default: HADR_Endpoint.
-EndpointPortIntOptionalEndpoint TCP port. Default: 5022.
-FailoverModeStringOptionalAutomatic / Manual.
-AvailabilityModeStringOptionalOverride sync/async commit mode per replica.
-BackupPreferenceStringOptionalPrimary / Secondary / PreferSecondary / None.
-SeedingModeStringOptionalAutomatic / Manual.
-ListenerNameStringOptionalAG listener network name. Triggers Step 5 when given.
-IPAddressString[]OptionalListener IP address(es).
-SubnetMaskStringOptionalListener subnet mask.
-PortIntOptionalListener port.
-ServiceAccountStringOptionalService account to GRANT CONNECT ON ENDPOINT (with UPN fallback via AD domain lookup).
-RestartServiceSwitchOptionalRestarts SQL Server service after enabling HADR. Default: $true.
-CleanupOrphanedWsfcGroupSwitchOptionalRemoves a leftover WSFC resource group and registry entries from a prior failed attempt.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-EnableExceptionSwitchOptionalThrows terminating errors instead of returning a Failed status object.
-EventLogSwitchOptionalWrites step events via Write-sqmSetupEvent for an animated HTML replay (New-sqmSetupReport).
-WhatIfSwitchOptionalPreview the entire 7-step plan without making changes.
-ConfirmSwitchOptionalPrompt before this high-impact operation.

Return Value

Returns a PSCustomObject with: AvailabilityGroup, PrimaryReplica, SecondaryReplicas, Database, Listener, Status (Pending / WhatIf / CompletedWithErrors / Success / Failed), Steps (List<string>), Error, Timestamp.

Examples

Example 1 — Two-node AG with listener

New-sqmAvailabilityGroup -SqlInstance "SQL01" -SecondaryReplica "SQL02" -AvailabilityGroupName "AG-Prod" -Database "AppDb" -ListenerName "AG-Prod-LST" -IPAddress "10.0.0.50" -SubnetMask "255.255.255.0"

Example 2 — Preview without changes

New-sqmAvailabilityGroup -SqlInstance "SQL01" -SecondaryReplica "SQL02","SQL03" -AvailabilityGroupName "AG-Prod" -WhatIf

Example 3 — Cleanup of a prior failed attempt

New-sqmAvailabilityGroup -SqlInstance "SQL01" -SecondaryReplica "SQL02" -AvailabilityGroupName "AG-Prod" -CleanupOrphanedWsfcGroup -EventLog