Commands / Invoke-sqmAlwaysOnSetup
sqmSQLTool › Commands › Invoke-sqmAlwaysOnSetup

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

START FailoverClusters + dbatools available? throw; return Step 1, Discover cluster Get-Cluster → ClusterName | Get-ClusterNode → Nodes[] Listener: Get-ClusterGroup → NetworkName + IPAddress resource → discListenerName / IP / Port Step 2, Discover SQL instances (WMI per node) Get-WmiObject Win32_Service -Filter "MSSQLSERVER or MSSQL$*" → instName + StartName (service account) Step 3, Test connectivity Connect-DbaInstance each node (Windows Auth or -SqlCredential) Any node fails → throw "fix SPNs or pass -SqlCredential" -BackupCluster Settings=$true? Write AlwaysOn_ClusterSettings_*.txt ShouldProcess create AG? AgResult=WhatIf; -WhatIf passed to New-sqm... New-sqmAvailabilityGroup (primary, secondaries[], listener, endpoints, seeding, databases) Step 6, Post-creation Sync-sqmLoginsToAlwaysOn (unless -SkipLoginSync) | Invoke-sqmSqlAlwaysOnAutoseeding -GenerateSpn Report=$true? Write AlwaysOn_SPN_ADTeam_*.txt (setspn commands) Return PSCustomObject { ClusterName, Nodes, PrimaryReplica, AvailabilityGroup, Status, AgResult, LoginSyncResult, SpnReportFile, … } DONE

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

ParameterTypeDefaultDescription
-AvailabilityGroupNameStringListener nameName of the AG to create. Defaults to the discovered cluster listener name.
-DatabaseString[], Databases to seed into the AG (auto-seeded).
-PrimaryReplicaStringFirst nodeOverride the primary replica. Defaults to the first cluster node.
-ListenerNameStringAuto-discoveredOverride the listener name discovered from the cluster.
-ListenerIPAddressString[]Auto-discoveredIP address(es) for the listener.
-ListenerPortInt1433Listener probe port (default if cluster provides none).
-EndpointPortInt5022Database mirroring endpoint port.
-FailoverModeStringAutomaticAutomatic (sync) or Manual (async).
-BackupPreferenceStringPrimaryBackup preference for the AG.
-SqlCredentialPSCredential, SQL authentication. Omit to use Windows/Kerberos auth.
-BackupClusterSettingsBool$trueWrite a cluster-settings text file before making changes.
-GenerateSpnReportBool$trueWrite an SPN request file (setspn commands) for the AD team.
-OutputPathStringGet-sqmDefaultOutputPathDirectory for backup and SPN report files.
-SkipLoginSyncSwitch$falseSkip post-creation Sync-sqmLoginsToAlwaysOn.
-EnableExceptionswitch$falseThrow on error instead of logging and returning a failed result.
-EventLogstring, See Invoke-sqmAlwaysOnSetup -? for details.
-ListenerSubnetMaskstring'255.255.255.0'See Invoke-sqmAlwaysOnSetup -? for details.
-ServiceAccountstring, SQL service account for the endpoint CONNECT grant. Default: discovered from the SQL service.

Return Value

PropertyDescription
ClusterNameDiscovered WSFC cluster name.
NodesArray of cluster node hostnames.
PrimaryReplicaSQL instance used as the primary replica.
SecondaryReplicasRemaining SQL instances.
AvailabilityGroupAG name.
ListenerListener name (if configured).
StatusSuccess, WhatIf, or Failed.
AgResultFull result object from New-sqmAvailabilityGroup.
LoginSyncResultResult from Sync-sqmLoginsToAlwaysOn.
ClusterSettingsFilePath to the cluster-settings backup file.
SpnReportFilePath 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