Fully automatic configuration of Availability Groups
on Windows Server Failover Clusters
Uwe Janke · Senior SQL Server DBA · dtcSoftware
Manual AlwaysOn configuration - an error-prone marathon.
Time spent on a manual AG setup - depending on the operator's experience
Every DBA keeps their own Word checklist. No consistent standard, no versioning
Forgot a step? Wrong port? Endpoint already exists? - Errors surface late
SPNs missing → Cannot generate SSPI context → contact the AD team → wait → continue
The WSFC group still hangs around, a registry key blocks a retry - hard to find without experience
What was configured? Which failover mode? Which service account? - Nearly impossible to reconstruct afterward
A single script - AlwaysOnSetup.ps1 - handles all 9 configuration steps fully automatically after a one-time parameter check.
Cluster data is read in automatically. No typing needed - just review and start.
Cluster backup, a full log, and an SPN request file are saved automatically.
Can be restarted after a failure. Cleans up WSFC leftovers automatically.
| Before | After |
|---|---|
| 2-4 hours | ~20 minutes |
| Word/Excel checklist | GUI with pre-filled values |
| No log | 3 files automatically |
| SPN: call the AD team | setspn file generated |
| Failed attempt → manual cleanup | Automatic WSFC cleanup |
The tool configures - it doesn't build. What needs to be in place beforehand:
Windows Server Failover Cluster with 2 or 3 nodes
Enterprise or Standard on all nodes, matching version
Between all nodes for the HADR mirroring endpoint
UNC path writable from all nodes
| Requirement | Note |
|---|---|
| Local administrator | On the executing node |
SQL sysadmin | On all nodes |
| Domain read access | For SPN checking (step 9) |
FailoverClusters | Installed automatically |
dbatools ≥ 2.0 | Installed automatically |
Cluster data is read in automatically. PropertyGrid on the left - live log on the right.
All steps run sequentially and are logged. Skipped steps are noted in the log.
AlwaysOn is disabled by default in SQL Server and must be turned on individually on every node.
On all nodes: hadr enabled = 1 via Invoke-DbaQuery
Win32_Service.StopService() / StartService() - no Restart-Computer needed
SELECT 1 every 2 sec., max. 120 sec. - only then does it move on to step 3
Restart-Service MSSQLSERVER doesn't check whether the service is actually ready again.
WMI plus an active connection test ensures SQL Server is fully initialized before step 3 begins.
# On every node: Invoke-DbaQuery -SqlInstance $node ` -Query "EXEC sp_configure 'hadr enabled',1; RECONFIGURE" # WMI service restart $svc = Get-WmiObject Win32_Service ` -ComputerName $node ` -Filter "Name='MSSQLSERVER'" $svc.StopService() # wait until Stopped $svc.StartService() # wait until Running # Readiness test (max. 120s) $ok = $false for ($i=0; $i -lt 60; $i++) { try { Invoke-DbaQuery -Query "SELECT 1" $ok = $true; break } catch { Start-Sleep 2 } } if (-not $ok) { throw "Timeout" }
For CREATE AVAILABILITY GROUP, sqlcmd is used deliberately - not Invoke-DbaQuery.
sys.availability_groups can still appear empty - even though the AG already exists.
sqlcmd opens a fresh TCP connection on every call and sees the current state.
Invoke-DbaQuery and Connect-DbaInstance are used.
Every other operation: direct T-SQL, WMI, or FailoverClusters cmdlets.
# CREATE AVAILABILITY GROUP via sqlcmd # -- fresh connection, no dbatools cache $sql = @" CREATE AVAILABILITY GROUP [$AgName] WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY) FOR DATABASE [$TestDb] REPLICA ON N'$node1' WITH ( ENDPOINT_URL = 'TCP://$node1:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT), N'$node2' WITH ( ENDPOINT_URL = 'TCP://$node2:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT); "@ # Call sqlcmd directly sqlcmd -S $node1 -E -Q $sql
SPNs enable Kerberos auth. If they're missing, Windows auth fails with Cannot generate SSPI context.
| Situation | Flow |
|---|---|
| SPNs set ✓ | Fully automatic - no intervention |
| SPNs missing ✗ | Pause → show T-SQL → manual → continue |
Fallback mechanism:
Kerberos test fails on the affected node
Temporary login - ready to copy into SSMS
Tool checks SQL auth, then continues with all steps
Temporary login is guaranteed to be removed at the end
# Per node - short name and FQDN setspn -S MSSQLSvc/SQLNODE01:1433 DOM\svc-sql setspn -S MSSQLSvc/SQLNODE01.dom.com:1433 DOM\svc-sql setspn -S MSSQLSvc/SQLNODE02:1433 DOM\svc-sql setspn -S MSSQLSvc/SQLNODE02.dom.com:1433 DOM\svc-sql # AG listener setspn -S MSSQLSvc/AG-LISTENER:1433 DOM\svc-sql setspn -S MSSQLSvc/AG-LISTENER.dom.com:1433 DOM\svc-sql # Verification: setspn -L DOM\svc-sql
AlwaysOn_SPN_ADTeam_<date>.txtAll files land automatically in C:\System\WinSrvLog\MSSQL\ - no manual action needed.
| File | Content | Timing |
|---|---|---|
AlwaysOn_ClusterSettings_<date>.txt | Cluster backup before changes | Step 1 |
AlwaysOn_Setup_<date>.log | Full text log | Completion |
AlwaysOn_Setup_<date>.rtf | Colored log (manual) | Manual |
AlwaysOn_SPN_ADTeam_<date>.txt | setspn commands for the AD team | Step 9 |
| ■ Blue | Section header |
| ■ Green | Success |
| ■ Yellow | Warning / note |
| ■ Red | Error |
Failed setup attempts leave WSFC leftovers behind that block a retry.
Tool checks whether a WSFC group for the AG name already exists
-RemoveResources -Force - full cleanup
HadrAgNameToldMap on all nodes via Invoke-Command
No manual intervention needed
# Check and clean up WSFC leftovers $existingGroup = Get-ClusterGroup ` -Name $AgName -ErrorAction SilentlyContinue if ($existingGroup) { Write-Log "Orphaned WSFC group found - cleaning up..." Stop-ClusterGroup -Name $AgName -Force Remove-ClusterGroup -Name $AgName ` -RemoveResources -Force } # Clean up the registry on all nodes $regKey = "HKLM:\Cluster\HadrAgNameToldMap" Invoke-Command -ComputerName $allNodes { if (Test-Path $using:regKey) { Remove-ItemProperty -Path $using:regKey ` -Name $using:AgName -ErrorAction SilentlyContinue } }
The AlwaysOn Setup Tool sets up the AG. sqmSQLTool then takes over day-to-day operations.
Invoke-sqmSplunkConfiguration - AG replication status, sync health, failover events sent to Splunk
Invoke-sqmSaObfuscation - rename the SA account on all nodes, reduce the default attack surface
New-sqmSqlCertificate - secure endpoint encryption with your own SQL certificate
Get-sqmServerSetting - regular check of every AG replica's synchronization status
Invoke-sqmUserDatabaseBackup - detects read-only secondaries automatically and skips them
Get-sqmDeadlockReport - AG-aware, reads only from the primary, hourly capture
What the tool deliberately does not do - and why.
| Task | Responsibility |
|---|---|
| Set up WSFC | Server team / failover cluster setup |
| DNS records for the listener | Network team |
| Firewall port 5022 | Network team |
| Set SPNs | AD team (the tool provides the commands) |
| Install SQL Server | SQLSetupTool |
| Add production databases to the AG | Manual / migration tool |
.\AlwaysOnSetup.ps1). No installer, no registry entries - deliberately simple to deploy.
| Error message | Cause | Solution |
|---|---|---|
Cannot generate SSPI context |
SPNs missing in AD | Perform the manual SQL login step in the tool. Long-term: have the AD team set the SPNs |
WSFC group ... already exists |
Leftover from a failed attempt | Tool cleans up automatically: Remove-ClusterGroup + registry |
Login failed for user AGSetup_... |
Mixed mode disabled or password policy | Server Properties → Security → SQL Server and Windows Authentication mode |
Backup directory not reachable |
UNC path invalid / no write permissions | Correct the path in the PropertyGrid - must be writable from all nodes |
Endpoint port 5022 in use |
Existing endpoint or another service | SELECT * FROM sys.endpoints WHERE type=4 - adjust the port in the PropertyGrid |
AG not visible after 60s |
SQL Server not fully ready | Tool actively waits up to 120s. Check the service manually afterward |
Invalid endpoint owner |
The temporary AGSetup_* login was the owner and was deleted |
AUTHORIZATION is set to the service account - no longer occurs |
AUTHORIZATION clause, SQL Server sets the currently executing login as the endpoint owner.
In the SQL-auth fallback (missing SPNs), that would be the temporary AGSetup_XXXXXXXX login -
which is deleted at the end of step 9. Result: the endpoint still exists, but its owning principal no longer does.
The tool therefore explicitly sets AUTHORIZATION [<ServiceAccount>] - or sa as a fallback.
Fully automatic run after a one-time parameter check
Missing SPNs don't stop the setup - a controlled workaround
Cluster backup, log, SPN file - with no extra effort
WSFC cleanup allows a clean retry after a failure
Together with sqmSQLTool and SQLSetupTool, a complete DBA workflow
| GitHub | github.com/JankeUwe/AlwaysOnSetup |
| Documentation | AlwaysOn_Doku.html |
| Handbook | AlwaysOn_Handbuch.docx |
| Website | www.powershelldba.de |