Invoke-sqmAlwaysOnSetup Always On

Headless end-to-end AlwaysOn AG setup: auto-discovers the WSFC cluster, nodes, listener, and SQL service accounts; tests connectivity; optionally backs up cluster settings; delegates AG creation to New-sqmAvailabilityGroup; then syncs logins and ensures automatic seeding. Generates an SPN request file for the AD team.

Execution Flow

Synopsis

Acts as a headless replacement for the interactive AlwaysOnSetup GUI. Discovers the WSFC cluster and all SQL instances via Get-Cluster / Get-ClusterNode and WMI Win32_Service. The listener is auto-detected from the cluster network-name and IP-address resources; supply the -Listener* parameters to override. Connectivity is tested with Connect-DbaInstance — if any node fails without -SqlCredential, the setup aborts with an SPN repair hint. After the AG is created via New-sqmAvailabilityGroup, logins are synced to all secondaries and SEEDING_MODE=AUTOMATIC is ensured.

Requires both FailoverClusters and dbatools modules. Must run on a cluster member as a local administrator with sysadmin on all replicas.

When Kerberos SPNs are missing, pass -SqlCredential (SQL auth) or fix the SPNs using the generated SPN report file before running. The function does not create temporary SQL logins like the GUI tool did.

Syntax

Invoke-sqmAlwaysOnSetup
    [-AvailabilityGroupName <String>]       # default: discovered listener name
    [-Database <String[]>]
    [-PrimaryReplica <String>]
    [-ListenerName <String>] [-ListenerIPAddress <String[]>]
    [-ListenerPort <Int>] [-ListenerSubnetMask <String>]
    [-EndpointPort <Int>]                   # default: 5022
    [-FailoverMode <Automatic|Manual>]     # default: Automatic
    [-BackupPreference <Primary|Secondary|PreferSecondary|None>]
    [-ServiceAccount <String>]
    [-SqlCredential <PSCredential>]
    [-BackupClusterSettings <Bool>]         # default: $true
    [-GenerateSpnReport <Bool>]             # default: $true
    [-OutputPath <String>]
    [-SkipLoginSync]
    [-EnableException]
    [-WhatIf] [-Confirm]

Parameters

Parameter	Type	Default	Description
-AvailabilityGroupName	String	Listener name	Name of the AG to create. Defaults to the discovered cluster listener name.
-Database	String[]	—	Databases to seed into the AG (auto-seeded).
-PrimaryReplica	String	First node	Override the primary replica. Defaults to the first cluster node.
-ListenerName	String	Auto-discovered	Override the listener name discovered from the cluster.
-ListenerIPAddress	String[]	Auto-discovered	IP address(es) for the listener.
-ListenerPort	Int	1433	Listener probe port (default if cluster provides none).
-EndpointPort	Int	5022	Database mirroring endpoint port.
-FailoverMode	String	Automatic	`Automatic` (sync) or `Manual` (async).
-BackupPreference	String	Primary	Backup preference for the AG.
-SqlCredential	PSCredential	—	SQL authentication. Omit to use Windows/Kerberos auth.
-BackupClusterSettings	Bool	$true	Write a cluster-settings text file before making changes.
-GenerateSpnReport	Bool	$true	Write an SPN request file (`setspn` commands) for the AD team.
-OutputPath	String	Get-sqmDefaultOutputPath	Directory for backup and SPN report files.
-SkipLoginSync	Switch	$false	Skip post-creation `Sync-sqmLoginsToAlwaysOn`.

Return Value

Property	Description
ClusterName	Discovered WSFC cluster name.
Nodes	Array of cluster node hostnames.
PrimaryReplica	SQL instance used as the primary replica.
SecondaryReplicas	Remaining SQL instances.
AvailabilityGroup	AG name.
Listener	Listener name (if configured).
Status	`Success`, `WhatIf`, or `Failed`.
AgResult	Full result object from `New-sqmAvailabilityGroup`.
LoginSyncResult	Result from `Sync-sqmLoginsToAlwaysOn`.
ClusterSettingsFile	Path to the cluster-settings backup file.
SpnReportFile	Path to the SPN request file for the AD team.

Examples

Example 1 — Full auto-discovery setup — creates AG, syncs logins, writes reports

Invoke-sqmAlwaysOnSetup -AvailabilityGroupName "ProdAG" -Database "AppDb"

Example 2 — Dry-run (shows what would happen without making changes)

Invoke-sqmAlwaysOnSetup -AvailabilityGroupName "ProdAG" -Database "AppDb" -WhatIf

Example 3 — SQL auth when Kerberos SPNs are missing

Invoke-sqmAlwaysOnSetup -AvailabilityGroupName "ProdAG" -Database "AppDb" `
    -SqlCredential (Get-Credential sa)

Example 4 — Manual failover mode, no login sync

Invoke-sqmAlwaysOnSetup -AvailabilityGroupName "DrAG" `
    -FailoverMode "Manual" -SkipLoginSync

⬆ Index

All Commands