AlwaysOnSetup is a PowerShell WinForms tool that fully automates configuring a SQL Server AlwaysOn availability group on an existing Windows Server Failover Cluster, up to 3 nodes. It reads the cluster automatically, falls back to SQL authentication when Kerberos SPNs are missing, cleans up failed attempts on its own, and logs everything.
Setting up an availability group by hand is slow, inconsistent between DBAs, and the Kerberos/SPN trap alone can stall a rollout for a day waiting on the AD team.
| Manual setup | AlwaysOnSetup |
|---|---|
| 2–4 hours, depending on the operator's experience | ~20 minutes |
| Everyone keeps their own Word/Excel checklist | One GUI, pre-filled from the cluster |
| No log unless someone writes one by hand | Cluster backup, text log and SPN file written automatically |
| SPNs missing → call the AD team → wait | Setup continues over a temporary SQL login; ready-to-send setspn file for the AD team |
| Failed attempt → clean up the WSFC by hand | Automatic WSFC and test-database cleanup on retry |
| Logins and Agent jobs drift apart between replicas | Sync job created on every node |
| Windows Server | 2022 or later, on every cluster node |
| SQL Server | 2022 or later (Enterprise or Standard), default or named instance |
| Cluster | An existing Windows Server Failover Cluster (WSFC) with 2 or 3 nodes |
| Backup share | A shared UNC path, writable from every node |
| Network | Port 5022 open between all nodes (HADR endpoint), Windows admin rights on the other nodes for service control and WMI (RPC); PowerShell remoting (WinRM) only as a fallback |
| Rights | Local administrator on the node it runs from, sysadmin on every node; domain read access only for the SPN check (without it the tool warns and lists every needed SPN) |
| PowerShell | Windows PowerShell 5.1 or later |
| Install | From the PowerShell Gallery: Install-Script AlwaysOnSetup (script only; the GitHub repository also holds the .ini, documentation and starter) |
| Modules | FailoverClusters and dbatools ≥ 2.0, both installed automatically if missing |
The configuration itself uses only two dbatools commands, Invoke-DbaQuery and Connect-DbaInstance. AG creation and joins run over direct T-SQL (sqlcmd), service work over WMI, cluster work over FailoverClusters cmdlets, to avoid version dependencies and internal side effects. Only the AG sync job (step 8) relies on dbatools copy commands, because that is exactly what they are built for.
Every run executes these steps in sequence. Skipped steps are recorded in the log.
SERVERPROPERTY('IsHadrEnabled'). Only if it is off: HADR_Enabled in the instance registry (via xp_instance_regwrite), service restart via the Service Control Manager, readiness check and a second HADR check.HADR_Endpoint (port 5022) created and started via T-SQL.GRANT CONNECT ON ENDPOINT applied on every node. If the Windows login cannot be used (e.g. across domains), the endpoints switch to certificate authentication.sqlcmd), replicas joined, automatic seeding enabled. A leftover WSFC group from an earlier attempt is removed first.ALTER AVAILABILITY GROUP. Network name and IP resources left by a failed attempt are removed first; the listener's AD computer object, which the cluster disables when it removes them, is reported for the AD team.AlwaysOnSetup.ini). It runs on all nodes but only acts on the current primary, copying logins, Agent jobs and linked servers to the secondaries. If the job already exists on a node, the log shows a note and the existing job is left unchanged.setspn -L; the needed setspn commands exported as a text file for the AD team. If the SPNs can't be queried (no domain read access), that is a warning, not an error: every needed SPN is listed and goes into the file.Start: double-click Start-AlwaysOnSetup.cmd (also works straight from a share: it copies the tool to C:\ProgramData\AlwaysOnSetup and starts it elevated), or open PowerShell as Administrator on a cluster node and run AlwaysOnSetup.ps1. It checks modules and reads cluster information automatically, everything it finds appears in a PropertyGrid for review.
| Field | Category | Meaning |
|---|---|---|
| Cluster name | 1 - Cluster | Name of the WSFC (read-only, read from the cluster) |
| Listener name | 1 - Cluster | DNS name of the AG listener (detected, or entered manually when no cluster role exists) |
| Listener IP | 1 - Cluster | Listener IPv4 address (taken from the cluster, or entered manually, e.g. when the cluster role was deleted beforehand) |
| Listener port | 1 - Cluster | Listener TCP port (default: 1433, editable) |
| Listener subnet mask | 1 - Cluster | Subnet mask of the listener IP (taken from the cluster, default: 255.255.255.0) |
| Node 1 – 3 | 2 - Nodes | Host name, SQL instance and current HADR status per cluster node, read automatically |
| AG name | 3 - Availability Group | Name of the availability group (= WSFC role name) |
| Endpoint port | 3 - Availability Group | HADR endpoint port (default: 5022) |
| Failover mode | 3 - Availability Group | Automatic = synchronous with automatic failover; Manual = asynchronous |
| Backup preference | 3 - Availability Group | Primary (default) / Secondary; selectable only from SQL Server 2025, fixed to Primary on SQL Server 2022 and older |
| Test database | 3 - Availability Group | Name of the database to create and add to the AG |
| Backup share | 3 - Availability Group | UNC path for the initial full backup |
| Service account | 4 - SQL service account | SQL service account (auto-detected, optionally changeable) |
| Password | 4 - SQL service account | Password of the service account, only needed when changing it in step 1 |
| Setup authentication | 5 - Setup connection | Auto = Windows authentication only if Kerberos works to every remote node, otherwise a temporary SQL login; SQL-Login = always the temporary SQL login |
Service Principal Names let SQL Server use Kerberos authentication. If they're missing, remote Windows connections fall back to NTLM or fail with Cannot generate SSPI context.
| Situation | What happens |
|---|---|
| SPNs already set | Fully automatic run, Kerberos on every node, no manual step |
| SPNs missing (NTLM only) | The tool creates a temporary SQL login itself and runs the whole setup over SQL authentication, still without a manual step |
| Node not reachable with Windows auth | Tool pauses → T-SQL block is on the clipboard → you run it on the node → click Continue → the rest of the run continues over SQL authentication |
# Per SQL node, short name and FQDN setspn -S MSSQLSvc/SQLNODE01:1433 DOMAIN\SQLServiceAccount setspn -S MSSQLSvc/SQLNODE01.domain.com:1433 DOMAIN\SQLServiceAccount setspn -S MSSQLSvc/SQLNODE02:1433 DOMAIN\SQLServiceAccount setspn -S MSSQLSvc/SQLNODE02.domain.com:1433 DOMAIN\SQLServiceAccount # AG listener setspn -S MSSQLSvc/AG-LISTENER:1433 DOMAIN\SQLServiceAccount setspn -S MSSQLSvc/AG-LISTENER.domain.com:1433 DOMAIN\SQLServiceAccount # Verify after setting: setspn -L DOMAIN\SQLServiceAccount
Step 10 generates the exact commands for your environment and saves them to C:\System\WinSrvLog\MSSQL\AlwaysOn_SPN_ADTeam_<date>.txt, ready to forward as-is. Without domain read access the tool can't see which SPNs already exist, so the file then lists all of them; setspn -S only adds the missing ones and reports existing ones as duplicates.
sys.dm_exec_connections.auth_scheme); remote nodes must use Kerberos, NTLM does not count.LoginMode via T-SQL (xp_instance_regwrite), service restart via the Service Control Manager, so no WinRM is needed. PowerShell remoting is only the fallback. If a node cannot be switched, the log names it for a manual switch and Continue checks the SQL login.AGSetup_<random> with CHECK_POLICY = ON.All files are written automatically to C:\System\WinSrvLog\MSSQL\.
| File | Contents | When |
|---|---|---|
| AlwaysOn_ClusterSettings_<date>.txt | Cluster configuration, AG parameters, node status, a backup taken before any change | Before step 1 |
| AlwaysOn_Setup_<date>.log | Full text log of every step, timestamped, passwords masked | After completion |
| AlwaysOn_Setup_<date>.rtf | Color RTF log, passwords masked (saved via a button in the tool, location of your choice) | On demand |
| AlwaysOn_SPN_ADTeam_<date>.txt | Ready-to-send setspn commands with explanation and a verification command | Step 10 |
| Error | Cause | Fix |
|---|---|---|
| Cannot generate SSPI context | SPNs for the node are missing in Active Directory | Short term: the tool switches to the temporary SQL login on its own. Long term: have the AD team set the SPNs from the generated file. |
| WSFC group ... already exists | Leftover from a failed setup attempt | Cleaned up automatically: Remove-ClusterGroup plus the HadrAgNameToldMap registry key on every node. |
| Msg 41057 ... WSFC resource ... already exists (listener) | Network name and IP of a failed ADD LISTENER are still in the AG's cluster role | The tool asks first (default No), removes them only on Yes, and does not create the listener in the same run (see next row). |
| Cluster error 0x533 "account is currently disabled" (resource AG_Listener) | The listener's AD computer object is disabled; the cluster does that when it removes a network name resource | AD team: enable or delete the computer object and give the cluster account (CNO) rights on it. Then run the tool again or the ADD LISTENER statement from the log. |
| Login failed for user AGSetup_... | The node is not in mixed mode, typically after the manual T-SQL step on a node the tool could not switch | Check in SSMS: Server Properties → Security → SQL Server and Windows Authentication mode, restart the service, click Continue. |
| Mixed mode could not be enabled | No Windows-auth connection with sysadmin to the node, no admin rights for its service, and no PowerShell remoting as fallback (e.g. "the requested HTTP URL was not available": no WinRM listener for that IP) | Switch the node to mixed mode in SSMS, restart the service and click Continue (the tool does not set it back), or have the SPNs set so the run stays on Kerberos. |
| CREATE LOGIN rolled back by a trigger | A server trigger or PBM policy (On Change: Prevent) besides the known password policy | The log lists the active server triggers and policies; disable the blocking one for the run. |
| SPNs could not be queried (step 10) | setspn -L failed: no domain read access, or the account can't be resolved from this server | Only a warning. Forward the generated file to the AD team; it lists every needed SPN. |
| Backup directory not reachable | Invalid UNC path, or no write permission | Fix the path in the PropertyGrid; it must be writable from every node. |
| Endpoint port 5022 already in use | Existing endpoint, or a firewall blocking the port | Run SELECT * FROM sys.endpoints WHERE type=4; adjust the port in the PropertyGrid. |
| AG still not visible after 60s | The AG from step 6 was not created, or not yet visible on the primary | The listener step is skipped. Check the step 6 messages in the log, then add the listener manually or run the tool again. |
| Seeding failed / no seeding started (step 9) | No endpoint connection between the replicas, or different data/log paths on the secondary | The log shows failure_state_desc and the error code; check step 4, the paths, and the secondary's error log. |
Reading state: Windows auth via WMI for service information, T-SQL for HADR status. Configuring: Windows auth tested on every node first, with Kerberos required for remote nodes; with NTLM only or an SSPI error, a temporary SQL login with a cryptographically random 28-character password (RNGCryptoServiceProvider). Cleanup: the temporary login is deleted on every node automatically, the password policy re-enabled, and the original authentication mode restored.
CREATE AVAILABILITY GROUP and every AG-join command runs through sqlcmd directly rather than Invoke-DbaQuery. Reason: after the service restart in step 2, dbatools can hold a cached connection where sys.availability_groups still appears empty. sqlcmd opens a fresh TCP connection on every call.
Before creating the AG, the tool checks for a leftover WSFC group from a previous attempt and cleans it up automatically: Stop-ClusterGroup + Remove-ClusterGroup -RemoveResources -Force, then clears the HKLM:\Cluster\HadrAgNameToldMap registry key on every node via the remote registry (PowerShell remoting only as fallback). If the group held a listener, the cluster disables its AD computer object on removal, and a new listener with that name fails with 0x533 until the AD team enables it. The tool therefore never removes a network name without asking: a warning dialog names the AD objects affected, default No.
When the service account's Windows login can't be created on a partner node (typically across domains), each node gets its own certificate, the public keys are exchanged through <instance default backup path>\HADR_Certs, and the endpoints switch to AUTHENTICATION = CERTIFICATE.
The temporary SQL login uses a cryptographically random password built only from T-SQL-safe special characters. On the manual path the password is visible in the live log window and on the clipboard while the session runs; every saved log, the automatic text log and the RTF/text file from the save button, has it replaced by ********. The PBM password policy stays disabled from the creation of the login until the cleanup at the end, only on nodes where the tool disabled it, and both the login and the policy are reset at the end.
AlwaysOnSetup hands off a working availability group. These sqmSQLTool commands are the natural next steps to harden and monitor it.
Invoke-sqmSplunkConfiguration
Points the Splunk forwarder at every instance's SQL Server error log, where AG role changes and failovers are recorded.
Invoke-sqmSaObfuscation
Renames the SA login and adds a disabled decoy SA. Run it on every replica to reduce the default attack surface.
New-sqmSqlCertificate
Renews a SQL Server certificate with backups of old and new, and can switch the AG endpoint to it.
Every release, what changed, and why.