Prerequisites checklist for SQL Server Always On servers
Checklist of points to settle (AD, network, accounts, storage, instance) before building SQL Server 2022 servers for an availability group.
Before building a pair of virtualised SQL Server 2022 servers for an Always On availability group, it helps to agree on a checklist with the infrastructure, Active Directory and storage teams. This article lists the points to settle, as a generic template.
General
| Item | Value / decision |
|---|---|
| Operating system | Windows Server 2022 Standard |
| SQL Server version | SQL Server 2022 Standard |
| Location | Same site, nodes on different hypervisor hosts |
| Databases | Estimate number, size and activity (example: 4 per server) |
Active Directory
- Delegate to the DBA team the right to move computer objects to the target OU and to delete SQL computer objects.
- Use a dedicated OU for the SQL servers.
- Apply server settings through a GPO. The SQL Server service account needs these user rights:
- Perform volume maintenance tasks (instant file initialization)
- Impersonate a client after authentication
- Lock pages in memory
- Log on as a service
- Manage auditing and security log (SeSecurityPrivilege)
- If Kerberos is used, register the Service Principal Names (SPN) for the SQL engine service account (manually or through a GPO).
Network
- Agree on the subnet (public network).
- Agree on a naming convention for the availability group listener (for example a common prefix such as
lsn_). - Reserve a listener port, and an endpoint port for the database mirroring endpoint (default 5022).
- Decide whether a DNS alias is needed.
Accounts
List the accounts to request before installation:
- SQL engine service account
- SQL Agent service account
- A sysadmin AD account
- A failover cluster account
- SSIS service account
- SSRS service account
Server sizing (example)
| Item | Example |
|---|---|
| CPU | 8 |
| RAM | 48 GB |
| Virtual SCSI adapters | 2 (spread the storage across them) |
| Virtual network adapters | 1 |
Other points: identical volumes and folder structure on all replicas, required Windows hotfixes installed, and DTC configured if needed.
Storage
Use separate volumes (GPT) per instance and per file type, with a 64 KB allocation unit size for data, log and system volumes:
| Volume | Allocation | Example size |
|---|---|---|
| System OS (C:) | GPT | 100 GB |
| Data | GPT, 64 KB | 700 GB |
| System databases | GPT, 64 KB | 40 GB |
| tempdb | GPT | 40 GB |
| Log | GPT, 64 KB | 60 GB |
When two instances share a server, repeat the same layout on a second drive letter.
Backup
Decide on a third-party backup tool (Veeam, Avamar, ...) and/or native backups to a local drive.
Instance
- Startup trace flag:
-T3226(suppresses successful backup messages in the SQL Server error log). - Named instance (for example
inst1) and an agreed collation. - tempdb: number of data files equal to the number of CPUs.
- Maintenance tasks and monitoring tool to be defined.
SSAS server (separate VM, tabular mode)
| Item | Example |
|---|---|
| Type | Tabular mode |
| Note | Install the local database engine only for SSIS |
| CPU / RAM | 4 / 16 GB |
| Disks | C: 80 GB, D: 80 GB |