SQL Server Installation Best Practices: Before and After Setup Checklist

Today, I’m sharing a comprehensive checklist of SQL Server installation best practices that can be followed during a new deployment. Although this guide does not cover every possible configuration, following these steps will help ensure a secure, high-performing, and production-ready SQL Server environment. This checklist will be updated regularly as best practices evolve.

1.      First, verify that provided Host environment matches with the request sheet provided to Infra Team such as

1.1.  Host Operating system version

1.2.  Number and size of Disk Drives.

1.3.  RAM.

1.4.  Firewall settings, etc

2.      Patch the host server system OS to latest stable available patch to have best performance and security in place.

3.      Patch the SQL Server OS with latest stable CU and GDR. Use below link to check the latest SQL server patches. In general, industry wide (N-1) is used in production where N => latest released patch.

https://www.catalog.update.microsoft.com/

4.      When you start installing SQL server, always use separate drives for Data (.mdf & .ndf), Log(.ldf) and Temp DB. Prefer using faster drive i.e. High I/O throughput drives for SQL Server Data and Log files hosting.

5.      If database backups are taken locally, it can be placed on a comparatively slower drive to save cost.

6.      Data disks should be enabled with Read/Write Caching for better performance.

7.      Configure multiple TempDB files to prevent TempDB contention. Now most of the SQL server setup provides TempDB multiple file creation at the time of installation.

8.      TempDB files must have same size and auto growth setting for best performance.

9.      All Database files size auto growth must be set to grow in MB instead of Percentage (%). Auto growth size should neither be too small nor too big to improve faster response and prevent disk fragmentation.

10. Set Min and Max Server Memory for better memory handling at SQL server instance level. As a standard 20% of total memory can be left for OS usages to prevent Host OS memory starving. This can even be made to 15% if Host OS is only using SQL server and no other application is running on the server.

11. Always enable Lock Pages In Memory (LPIM) and Instant File Initialization (IFI) for better memory management and Disk performance respectively.

12. Use Mixed mode authentication and give a strong password for admin user. After installation is completed and admin users are setup correctly, don’t forget to change and disable ‘sa’ account to enhance server security.

13. Use a dedicated service account preferably a domain account, for all SQL services with password expiration as “never” or if your environment allows, there must a password rotation policy in place.

14. Ensure “Principle of Lease Privilege” for Server and Database level permissions.

15. Configure firewall rules for SQL Server ports and alter all default settings to enable better security.

16. Configure DB maintenance jobs such as:

16.1.                   Database Backups. (Full, Diff & Tlog)

16.2.                    Index Rebuild / Reorganize.

16.3.                   Stats Update

16.4.                   Database integrity  

16.5            Enable DBMail

17. Configure all Alerts as required for:

17.1.                   Disk Space.

17.2.                   Memory Usage.

17.3.                   High CPU.

17.4.                   DB Offline.

18. Create Server/Database level triggers (In production):

18.1.                   Prevent Drop Database

18.2.                   Prevent Drop Table (For critical tables)

18.3.                   New user Creation/ Drop User.

19. Enable Logging and Auditing as much as possible.

20. Backup and Restore validation process.

21. Enable Database security in Transit- by implementing TDE if required.

22. Enable Dynamic Data Masking to prevent unauthorized data view within the system.

23. Stop and disable all unwanted SQL Services to prevent unnecessary resource consumption. For this you must be aware of all SQL Services and their purpose.

24. Recycle error log as required.

25. Finally don’t forget to update your Server/Database inventory sheet and prepare a proper runbook for your newly created Database environment.