TL;DR
A solid SQL Server install starts with a clean OS, 64K-formatted data drives, only the features you will use, a default or named instance picked deliberately, and least-privilege domain service accounts.
- Format data, log, backup, and tempdb drives at 64K allocation unit size before install.
- Run the Database Engine and Agent under a domain user account, never a domain admin, and give it full control over data, log, and backup folders.
- Grant Perform Volume Maintenance Tasks to the service account to enable instant file initialization, but mitigate the security tradeoff.
SQL Server installation does not often repeat, especially if you are using on-premises servers.
In this article, you will see a few settings that are recommended to be reviewed after the SQL Server installation checklist.
Preparation
- If possible, start with a freshly installed OS and install the latest drivers.
- Check if BIOS power management is disabled or set to OS control.
- Validate hardware compatibility with SQL Server prerequisites:
- Allocate an adequate amount of CPUs and RAM based on your estimated workload.
- Provision adequate disk space and set up correctly the Storage:
- Ensure that you plan for the right disk capacity for your data, logs, backups, and tempdb files.
- Format drives with allocation unit size set to 64K.
Installation process
Selection of the features is the first step when you begin the SQL Server installation process.
In the windows above, make sure to install only the features you will use.
Most of these are services that automatically start and use system resources.
The C: drive can be used for the above paths (Instance root directory, Shared feature directory, Shared feature directory (x86)).
Instance configuration
The next step is the instance configuration options.
In the dialog above, you will have 2 options:
- Default instance. You connect to SQL Server by only specifying the server name or IP.
- Named instance. You connect to SQL Server by specifying the server name or IP and the instance name (Example: Server01/instance1).
If you use only one instance on the server, you can use the default instance option; otherwise, use named instances.
Validate the software requirements. Some applications that run on SQL Server need to be on the default instance, so the database's connection uses only the machine name.
Service accounts
Use domain users (not admin) accounts for Database Engine and Agent. Some people use a domain admin account as a service account, which can cause severe security problems.
On the other hand, we need to ensure that the SQL Server service account has "full control" permissions on data, logs, and backup directories for read and write activities.
Grant perform volume task privilege
This option allows using the database instant file initialization option, which allows faster database creation, reduces SQL Server start time and restoring.
The more obvious performance improvement can be seen in size growth operations on larger databases.
There is a potential security risk to using this feature, so make sure to take adequate mitigation actions.
Post-installation initial setup
Check the best practices after the SQL Server installation. It should help you know that they were not left as defaults that may hurt performance.
Pushing SQL Server to the limits and taking everything it can give requires more in-depth investigation, and those settings may have different values specifically on your environment.
