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

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.

What it needs.

dbatools usage is deliberately minimal — only Invoke-DbaQuery and Connect-DbaInstance. Everything else runs over direct T-SQL (sqlcmd), WMI, or FailoverClusters cmdlets, to avoid version dependencies and internal side effects.

Nine 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 HADRsp_configure 'hadr enabled', 1 on every node; service restart with an active readiness check.
3. Create the endpointDatabase mirroring endpoint (port 5022) created and started via T-SQL.
4. Grant CONNECTWindows login created, GRANT CONNECT ON ENDPOINT applied on every node.
5. Test databaseDatabase created, recovery model set to FULL, initial full backup taken.
6. Availability groupAG created via plain T-SQL (sqlcmd), replicas joined, automatic seeding enabled.
7. ListenerAG listener with IP address and port, via ALTER AVAILABILITY GROUP.
8. StatusAG synchronization status and replica roles read out via DMVs.
9. SPN checkMissing MSSQLSvc SPNs detected; the needed setspn commands exported as a text file for the AD team.

What you actually do.

Start: 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

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 SPNs are missingClick Continue after the manual SQL login step (see SPNs below).
6. Forward the SPN fileHand the generated request file to the AD team at the end.

The one thing worth setting up in advance.

Service Principal Names let SQL Server use Kerberos authentication. If they're missing, Windows authentication fails with Cannot generate SSPI context.

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

When SPNs are missing

1. Auto-detectionThe tool detects which nodes aren't reachable over Kerberos.
2. T-SQL block shownA ready-to-copy block for creating a temporary login is displayed in the log.
3. Run it locallyRun the T-SQL via SSMS, locally on each affected node (over RDP).
4. ContinueClick Continue — the tool verifies the SQL-auth connection on every node.
5. Automatic cleanupThe temporary login is removed and the password policy re-enabled at the end.

Everything lands in one folder.

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

Live log color coding

What each error actually means.

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 (Kerberos) tested on every node first; on 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, and the password policy re-enabled.

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 Invoke-Command.

Security

The temporary SQL login uses a cryptographically random password built only from T-SQL-safe special characters. The password is never written to a log file — it's visible only in the running session's RTF window. The Enforce Password Policy setting is disabled only for the duration of creating the login, and both the login and the policy are guaranteed to be reset at the end.

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.