SQL Server: new cluster design checklist
Checklist to prepare a SQL Server failover cluster or availability group: AD, network, accounts, storage, instance.
Use this checklist to prepare the design of a new SQL Server cluster (failover cluster instance and/or Always On availability group) before requesting the infrastructure. The values below are examples to adapt; items marked "to define" must be settled with the system, network and security teams.
General
| Item | Example / question to ask |
|---|---|
| SQL Server version | SQL Server 2017 Standard |
| Number of replicas | 2 |
| Location | Same site, on different ESX hosts |
| Estimated number, size and activity of databases | To define |
Active Directory
- Delegate to the SQL administrators the right to create/move/delete computer objects in dedicated OUs: one for SQL servers, one for SQL service accounts and one for SQL user accounts.
- Local rights granted by GPO to the service accounts (server configuration):
- 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 SPNs in Active Directory for the SQL engine service account (done by GPO here).
Network
| Item | Detail |
|---|---|
| Subnets | Public network and private (heartbeat / cluster) network |
| IP addresses | One IP dedicated to the failover cluster; one IP per availability group listener |
| Listener name | Common prefix defined by the naming convention (for example lsn_) |
| Listener port | One port per listener, within a reserved range (for example 15000 to 15010) |
| Endpoint port | One port per endpoint, 5022 by default |
| Heartbeat | Dedicated VLAN with fixed IPs, to improve cluster stability |
| DNS | Decide whether a DNS alias is needed |
Service accounts
To prepare before installation:
- SQL engine service account;
- SQL Agent service account;
- a sysadmin AD account;
- a failover cluster account;
- SSIS and SSRS service accounts if those roles are installed.
Servers
| Item | Example |
|---|---|
| CPU / RAM | 8 vCPU / 64 GB |
| Virtual SCSI adapters | 2 |
| Virtual network adapters | 2 |
On every node:
- same volumes, with the same folder structure;
- .NET Framework installed;
- Failover Clustering feature installed and each node added to the cluster;
- required Windows hotfixes installed;
- DTC configured if the application needs it.
Failover cluster
- Every node is declared in the cluster; the cluster has a dedicated name and IP.
- Quorum: for example a file share witness (to be validated).
- Heartbeat network on a dedicated VLAN.
Storage
| Role | Letter | Format / remark | Size (example) |
|---|---|---|---|
| System (OS) | C: | GPT | 100 GB |
| Databases | G: | GPT, 64 KB allocation unit | 200 GB |
| System databases | S: | GPT, 64 KB allocation unit | 40 GB |
| tempdb | T: | GPT, SSD preferred | 40 GB |
| Transaction logs | L: | GPT, 64 KB allocation unit | 60 GB |
Check with the system administrator that volumes can be extended online (both at OS and hypervisor level).
Backup
- Third-party tool (for example Veeam or Avamar) and/or native backup to a dedicated local drive (for example H:).
Instance
- Trace flag to enable permanently:
-T3226(suppresses successful-backup messages in the SQL Server error log), set as a service startup parameter. - Named instance if needed.
- Collation to be chosen according to application requirements, before installation.
- tempdb: one data file per CPU core (within the recommended limit); plan the maintenance tasks.
- Plan instance monitoring.
SSIS server (if separate)
| Item | Example |
|---|---|
| CPU / RAM | 4 vCPU / 8 GB |
| Storage | C: and D:, 80 GB each |
| Note | Install only the SSIS engine, with a local database |
Items to document
- Database naming convention.
- Availability group naming convention (name of the resource in the cluster).
- GPO strategy, service accounts and user groups, per application.