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

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

Parameter	Type	Required	Description
-SqlInstance	String	Optional	Primary replica SQL instance.
-SecondaryReplica	String[]	Optional	One or more secondary replica instances.
-AvailabilityGroupName	String	Required	Name of the new Availability Group.
-Database	String[]	Optional	Databases to add to the AG.
-ReplicaHostMap	Hashtable	Optional	Maps replica name to endpoint hostname/FQDN.
-EndpointName	String	Optional	Mirroring endpoint name. Default: `HADR_Endpoint`.
-EndpointPort	Int	Optional	Endpoint TCP port. Default: `5022`.
-FailoverMode	String	Optional	`Automatic` / `Manual`.
-AvailabilityMode	String	Optional	Override sync/async commit mode per replica.
-BackupPreference	String	Optional	`Primary` / `Secondary` / `PreferSecondary` / `None`.
-SeedingMode	String	Optional	`Automatic` / `Manual`.
-ListenerName	String	Optional	AG listener network name. Triggers Step 5 when given.
-ListenerIPAddress	String[]	Optional	Listener IP address(es).
-ListenerSubnetMask	String	Optional	Listener subnet mask.
-ListenerPort	Int	Optional	Listener port.
-ServiceAccount	String	Optional	Service account to GRANT CONNECT ON ENDPOINT (with UPN fallback via AD domain lookup).
-RestartService	Switch	Optional	Restarts SQL Server service after enabling HADR. Default: `$true`.
-CleanupOrphanedWsfcGroup	Switch	Optional	Removes a leftover WSFC resource group and registry entries from a prior failed attempt.
-SqlCredential	PSCredential	Optional	SQL authentication credential.
-EnableException	Switch	Optional	Throws terminating errors instead of returning a Failed status object.
-EventLog	Switch	Optional	Writes step events via `Write-sqmSetupEvent` for an animated HTML replay (New-sqmSetupReport).
-WhatIf	Switch	Optional	Preview the entire 7-step plan without making changes.
-Confirm	Switch	Optional	Prompt 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" -ListenerIPAddress "10.0.0.50" -ListenerSubnetMask "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

⬆ Index

All Commands