Here are my personal SQL Server Installation Best Practices. Please note: they’re best practices. Maybe most of them can be hulpfull for most situations, in other situations some options must be configured otherwise. Please let me know if you have any questions or suggestions.
- When using 4 (or preferably more) CPU’s reserve the first core for the operating system. Configure this by opening the server (instance) properties, Processors page.
- Leave Boost priority off. Only use this option when really needed and you know what you’re doing. For example: when using several instances on the same server or when using another application on the same machine. Configure this by opening the server (instance) properties, Processors page.
- Reserve 512 MB – 2 GB of Ram for the Operating System. Configure this by opening the server (instance) properties, Memory page. Maximum server memory = Physical memory – reservation. For example: if your server has 16 GB Ram and you reserve 2 GB Ram then Maximum server memory would be 14336 (MB).
- When you’re not using SQL Server authentication then disable the use of SQL Logins through the server (instance) properties, Security page, Select Windows Authentication.
- Leave SA disabled. If it’s not disabled, log in with a Windows user as a member of the sysadmin role and disable the SA account. If you want to keep the SA account enabled, at least rename it and document the new name.
- Change the default backup directory path. Open Rhe registry editor and navigate to: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer\BackupDirectory and supply a path.
- Place TEMPDB on a separate volume.
- Create an NDF-file for the TEMPDB database for each CPU core available to SQL Server.
- Place your data files (MDF/NDF) on a separate volume. Configure the placement of new file by opening the server (instance) properties, Database settings page.
- Place your transaction log files (LDF) on a separate volume.
- Leave Auto create and auto update statistics on, unless a product reconfigures otherwise (e.g. BizTalk).