SQL Server 2019 cluster: install checklist, Kerberos SPNs and AD permissions
What to check before and after installing a SQL Server 2019 cluster, setspn commands and troubleshooting cluster permissions in Active Directory.
This checklist summarises what to verify before and after installing a high-availability SQL Server 2019 cluster (failover cluster instance and/or Always On availability group), with the setspn commands useful for Kerberos and a classic troubleshooting case for cluster permissions in Active Directory.
Before installation
| Area | Item to check |
|---|---|
| General | SQL Server version (here 2019 Standard), number of replicas (3), replica location (same site, different virtualisation hosts), estimated number/size/activity of databases |
| Active Directory | Delegation to the administrator to create/move computer objects in the dedicated OUs (SQL servers, service accounts, user accounts); deleting a SQL computer object |
| GPO (user rights) | Perform volume maintenance tasks, Impersonate a client after authentication, Lock pages in memory, Log on as a service, SeSecurityPrivilege |
| Network | Public network and private network (cluster heartbeat, dedicated VLAN with fixed IP), one IP dedicated to the cluster, one IP per availability group listener, dedicated listener port (agreed range), endpoint port (5022 by default), listener naming convention, DNS alias to be validated |
| Service accounts | SQL engine, SQL Agent, SSIS and SSRS service accounts, a sysadmin AD account, a failover cluster account |
| Servers | 8 vCPU, 64 GB RAM, 2 virtual SCSI adapters, 2 network adapters, same volumes and folder structure on every node, .NET Framework, required Windows hotfixes, each node added to the cluster, SPNs registered (see below), DTC configured |
| Cluster | Cluster name, quorum (file share), dedicated heartbeat network to improve stability |
| Storage | OS on C: (GPT, 100 GB); data G: (GPT, 64 KB allocation unit, 200 GB); system databases S: (64 KB, 40 GB); tempdb T: (GPT, SSD preferred, 40 GB); logs L: (64 KB, 60 GB); confirm with the system team that volumes can be extended online |
| Backup | Third-party tool (Veeam-type) and native backup to a dedicated local drive |
| Instance | Trace flag 3226 (suppresses successful-backup messages in the error log), named instance, a Latin1_General_CI_AS-type collation to confirm, tempdb data files = number of CPUs, monitoring |
| Naming | Define the naming convention for databases and availability groups (cluster resource name) |
For a separate SSIS server: 4 vCPU, 8 GB RAM, two 80 GB volumes; install only the local database engine for SSISDB.
Other server-side checks:
- configure failsafe/failover behaviour;
- set the Windows power plan to High performance (balanced mode throttles the CPU);
- enable the remote DAC.
Post-installation
- grant permissions on the endpoint (availability group);
- install the Ola Hallengren maintenance scripts;
- enable backup compression;
- create alerts and an operator (see the screenshot: SQL Server Agent, Alert System page, enable the fail-safe operator);
- add monitoring, log forwarding to the syslog/SIEM server and backup;
- configure maintenance jobs, a database inventory, automatic health checks and automatic OS updates.
SPNs for Kerberos
If Kerberos is used, the SQL engine service account must hold the server's SPNs. Remove old SPNs first (-d), then add the new ones (-a, or -S which checks for duplicates). Use the port for a default instance and the instance name for a named instance.
# Remove existing SPNs
setspn -d MSSQLSvc/sqlserver01.contoso.com:1433 CONTOSO\svc_sql
setspn -d MSSQLSvc/sqlserver01.contoso.com:PROD CONTOSO\svc_sql
setspn -d MSSQLSvc/sqlserver01:1433 CONTOSO\svc_sql
setspn -d MSSQLSvc/sqlserver01:PROD CONTOSO\svc_sql
# Add SPNs
setspn -a MSSQLSvc/sqlserver01.contoso.com:1433 CONTOSO\svc_sql
setspn -a MSSQLSvc/sqlserver01.contoso.com:PROD CONTOSO\svc_sql
setspn -a MSSQLSvc/sqlserver01:1433 CONTOSO\svc_sql
setspn -a MSSQLSvc/sqlserver01:PROD CONTOSO\svc_sql
The same pattern applies to a DNS alias or a listener name (MSSQLSvc/alias.contoso.com:1433). For a web service (IIS):
setspn -S HTTP/app.contoso.com CONTOSO\app_pool_user
setspn -S HTTP/app.contoso.com:8091 CONTOSO\app_pool_user
setspn app.contoso.com # list the SPNs of an account or host
To list the aliases (CNAME records) of a DNS zone, to decide which SPNs to register:
Get-WmiObject -Namespace 'root\MicrosoftDNS' -Class MicrosoftDNS_CNAMEType `
-Filter "ContainerName = 'contoso.com'" -ComputerName dnsserver01 |
Select-Object @{N='Alias'; E={$_.Ownername}}, Primaryname
Troubleshooting: the cluster identity cannot update computer objects
Typical message: password reset may lack permissions required to update the object. Please work with your domain administrator to ensure that the cluster identity can update computer objects in the domain. (Microsoft Learn discussion)
- Open Active Directory Users and Computers, then View > Advanced Features.
- Open the properties of the computer object the Cluster service must use, Security tab > Add.
- Add the Cluster service account (or a group it belongs to) and grant: Reset Password, Validated Write to DNS Host Name, Validated Write to Service Principal Name.
Notes
- Original server, domain, account and port names were replaced with generic values.
- Several rows in the original checklist were open questions (
?): fill them in for your environment.