powershelldba.de · Uwe Janke
REF: PSDB-AOS-2026 SCOPE: SQL Server 2022+ (tested on 2022) STATUS: v1.3.10
Module

Availability groups configured in ~20 minutes, not an afternoon.

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.

Why this exists

What manual AG setup actually costs.

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 setupAlwaysOnSetup
2–4 hours, depending on the operator's experience~20 minutes
Everyone keeps their own Word/Excel checklistOne GUI, pre-filled from the cluster
No log unless someone writes one by handCluster backup, text log and SPN file written automatically
SPNs missing → call the AD team → waitSetup continues over a temporary SQL login; ready-to-send setspn file for the AD team
Failed attempt → clean up the WSFC by handAutomatic WSFC and test-database cleanup on retry
Logins and Agent jobs drift apart between replicasSync job created on every node
Requirements

What it needs.

Windows Server2022 or later, on every cluster node
SQL Server2022 or later (Enterprise or Standard), default or named instance
ClusterAn existing Windows Server Failover Cluster (WSFC) with 2 or 3 nodes
Backup shareA shared UNC path, writable from every node
NetworkPort 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
RightsLocal 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)
PowerShellWindows PowerShell 5.1 or later
InstallFrom the PowerShell Gallery: Install-Script AlwaysOnSetup (script only; the GitHub repository also holds the .ini, documentation and starter)
ModulesFailoverClusters 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.

Workflow

Ten steps, always in this order.

Every run executes these steps in sequence. Skipped steps are recorded in the log.

  1. SQL service accountOptional: change the account and password via WMI on every node and restart the service.
  2. Enable HADRChecked with 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.
  3. Create the endpointDatabase mirroring endpoint HADR_Endpoint (port 5022) created and started via T-SQL.
  4. Grant CONNECTWindows login for the service account created, 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.
  5. Test databaseFirst, a leftover copy of the test database on any node that belongs to no AG and is not ONLINE (e.g. a secondary stuck in RESTORING after a failed run) is dropped; an ONLINE copy outside an AG on a secondary is only reported. Then the database is created, recovery model set to FULL, initial full backup taken.
  6. Availability groupAG created via plain T-SQL (sqlcmd), replicas joined, automatic seeding enabled. A leftover WSFC group from an earlier attempt is removed first.
  7. ListenerAG listener with IP address, subnet mask and port, via 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.
  8. AG sync jobSQL Agent job AG Sync - Logins, Jobs, LinkedServer created on every node (daily at 02:00, name configurable in 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.
  9. StatusWaits up to 3 minutes for the automatic seeding of the test database and reports the result per secondary, with the failure reason if seeding failed; then AG synchronization status and replica roles via DMVs.
  10. SPN checkMissing MSSQLSvc SPNs detected via 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.
Operation

What you actually do.

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.

PropertyGrid fields

FieldCategoryMeaning
Cluster name1 - ClusterName of the WSFC (read-only, read from the cluster)
Listener name1 - ClusterDNS name of the AG listener (detected, or entered manually when no cluster role exists)
Listener IP1 - ClusterListener IPv4 address (taken from the cluster, or entered manually, e.g. when the cluster role was deleted beforehand)
Listener port1 - ClusterListener TCP port (default: 1433, editable)
Listener subnet mask1 - ClusterSubnet mask of the listener IP (taken from the cluster, default: 255.255.255.0)
Node 1 – 32 - NodesHost name, SQL instance and current HADR status per cluster node, read automatically
AG name3 - Availability GroupName of the availability group (= WSFC role name)
Endpoint port3 - Availability GroupHADR endpoint port (default: 5022)
Failover mode3 - Availability GroupAutomatic = synchronous with automatic failover; Manual = asynchronous
Backup preference3 - Availability GroupPrimary (default) / Secondary; selectable only from SQL Server 2025, fixed to Primary on SQL Server 2022 and older
Test database3 - Availability GroupName of the database to create and add to the AG
Backup share3 - Availability GroupUNC path for the initial full backup
Service account4 - SQL service accountSQL service account (auto-detected, optionally changeable)
Password4 - SQL service accountPassword of the service account, only needed when changing it in step 1
Setup authentication5 - Setup connectionAuto = Windows authentication only if Kerberos works to every remote node, otherwise a temporary SQL login; SQL-Login = always the temporary SQL login
AlwaysOnSetup PropertyGrid on the left with cluster, node, availability group, and service account fields read in automatically, sample values shown, and the live log on the right showing module checks and cluster/SQL information being read in
The PropertyGrid, pre-filled from the cluster, next to the live log, sample values shown here in place of a real environment's names.

After clicking start

  1. Review the PropertyGridCheck every field and adjust if needed.
  2. Choose the primary nodeSelect it from the dropdown list.
  3. Start configurationClick the button, everything from here runs automatically.
  4. Watch the live logColor-coded progress in real time (see below).
  5. If a node can't be reached at allRun the T-SQL block from the clipboard on that node, then click Continue (see SPNs below).
  6. Forward the SPN fileHand the generated request file to the AD team at the end.
Kerberos & SPNs

The one thing worth setting up in advance.

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.

SituationWhat happens
SPNs already setFully 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 authTool 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

SPNs needed (short name and FQDN, per node, plus the listener)

# 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.

When SPNs are missing

  1. Auto-detectionThe tool checks the authentication scheme of every connection (sys.dm_exec_connections.auth_scheme); remote nodes must use Kerberos, NTLM does not count.
  2. Mixed modeA node that allows Windows authentication only is switched to mixed mode: 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.
  3. Temporary SQL loginThe tool disables the PBM password policy (New Login, On Change: Prevent) on the nodes where it is active, because it would roll back the Windows login in step 4, and creates the temporary sysadmin login AGSetup_<random> with CHECK_POLICY = ON.
  4. Setup over SQL authEvery step runs with that login.
  5. Manual fallbackOnly if a node can't be reached with Windows auth at all: the T-SQL block is already on the clipboard (a button copies it again for the next node); run it via SSMS, then click Continue.
  6. Automatic cleanupAt the end the temporary login is dropped on every node and the password policy re-enabled where the tool disabled it. Nodes it switched to mixed mode go back to Windows authentication only, secondaries first and the primary last; if the primary restart triggers a failover, the AG is failed back to the original primary once it is synchronized.
Output & logging

Everything lands in one folder.

All files are written automatically to C:\System\WinSrvLog\MSSQL\.

FileContentsWhen
AlwaysOn_ClusterSettings_<date>.txtCluster configuration, AG parameters, node status, a backup taken before any changeBefore step 1
AlwaysOn_Setup_<date>.logFull text log of every step, timestamped, passwords maskedAfter completion
AlwaysOn_Setup_<date>.rtfColor RTF log, passwords masked (saved via a button in the tool, location of your choice)On demand
AlwaysOn_SPN_ADTeam_<date>.txtReady-to-send setspn commands with explanation and a verification commandStep 10

Live log color coding

Blue, boldSection heading
Green, boldSuccess, action completed
YellowWarning, step skipped, or a note
Red, boldError, action failed
Light greyInformation, general status
Troubleshooting

What each error actually means.

ErrorCauseFix
Cannot generate SSPI contextSPNs for the node are missing in Active DirectoryShort 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 existsLeftover from a failed setup attemptCleaned 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 roleThe 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 resourceAD 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 switchCheck in SSMS: Server Properties → Security → SQL Server and Windows Authentication mode, restart the service, click Continue.
Mixed mode could not be enabledNo 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 triggerA server trigger or PBM policy (On Change: Prevent) besides the known password policyThe 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 serverOnly a warning. Forward the generated file to the AD team; it lists every needed SPN.
Backup directory not reachableInvalid UNC path, or no write permissionFix the path in the PropertyGrid; it must be writable from every node.
Endpoint port 5022 already in useExisting endpoint, or a firewall blocking the portRun SELECT * FROM sys.endpoints WHERE type=4; adjust the port in the PropertyGrid.
AG still not visible after 60sThe AG from step 6 was not created, or not yet visible on the primaryThe 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 secondaryThe log shows failure_state_desc and the error code; check step 4, the paths, and the secondary's error log.
Technical details

Why it's built this way.

Connection strategy

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.

Why sqlcmd for AG creation

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.

WSFC cleanup on retry

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.

Certificate fallback for the endpoint

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.

Security

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.

Next steps

After the AG is up, sqmSQLTool tasks.

AlwaysOnSetup hands off a working availability group. These sqmSQLTool commands are the natural next steps to harden and monitor it.