SQL Server standalone install: best practices from the setup screen to the first backup

#1. Before setup

Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel SQLData

• Windows power plan: High performance.
• Service accounts: gMSA or a domain account per service, not LocalSystem or a shared account.

#2. Setup choices

• Features: Database Engine only (plus Full-Text if needed). Don't install SSIS, SSRS or SSAS "just in case".
• Authentication: Windows authentication. Enable mixed mode only if an app needs it, and then disable sa.
• Admins: an AD group, not individual users.
• Grant Perform Volume Maintenance Task (instant file initialisation): tick it.
• Default directories: data on D:, logs on L:, backups on B:.
• tempdb: one data file per core up to 8, equal size, on T:.

Unattended install:

setup.exe /Q /ACTION=Install /FEATURES=SQLENGINE /INSTANCENAME=MSSQLSERVER `
  /SQLSVCACCOUNT="CORP\gmsa-sqlprd01$" /AGTSVCACCOUNT="CORP\gmsa-sqlagt01$" `
  /SQLSYSADMINACCOUNTS="CORP\SQL-Admins" /SQLSVCINSTANTFILEINIT=True `
  /INSTALLSQLDATADIR=D:\ /SQLUSERDBLOGDIR=L:\SQLLogs /SQLBACKUPDIR=B:\Backup `
  /SQLTEMPDBDIR=T:\TempDB /SQLTEMPDBLOGDIR=T:\TempDB /SQLTEMPDBFILECOUNT=8 `
  /SQLTEMPDBFILESIZE=4096 /SQLTEMPDBLOGFILESIZE=4096 /IACCEPTSQLSERVERLICENSETERMS

#3. Post-install configuration

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 57344;   -- 64 GB host: leave ~8 GB for the OS
EXEC sp_configure 'max degree of parallelism', 8;    -- ≤ cores per NUMA node, max 8
EXEC sp_configure 'cost threshold for parallelism', 50;
EXEC sp_configure 'backup compression default', 1;
EXEC sp_configure 'backup checksum default', 1;
EXEC sp_configure 'remote admin connections', 1;     -- DAC for emergencies
RECONFIGURE;

• model database: set sensible growth (data 256 MB, log 128 MB, not percentages).
• Install the latest CU.
• Enable Force Encryption with a CA certificate.
• Enable Query Store on user databases (on by default for new databases in SQL Server 2022+).

#4. Maintenance and backups

Use Ola Hallengren's maintenance solution, or the equivalent:

Copy backups offsite or to immutable storage.

#5. Checks with dbatools

Test-DbaMaxMemory -SqlInstance sql-prd-01
Test-DbaMaxDop -SqlInstance sql-prd-01
Test-DbaTempDbConfig -SqlInstance sql-prd-01
Get-DbaPrivilege -ComputerName sql-prd-01   # check IFI and Lock Pages in Memory

#6. Standalone means…

No automatic failover. Your RTO is the restore time (or VM restart time on a hypervisor with HA). If that isn't good enough, see the FCI vs AG post.

Tip

Put DNS aliases (sql-erp.corp.local) in application connection strings, not the host name. Moving to a new server or an AG listener later then needs no application change.