I have gathered some notes from SQLSchool.gr for Configuring an SQL Server
1. Set the minimum Page File, 512MB to 2GB, but don’t remove it
2. Set Antivirus Exclusions for all SQL files and services
3. Format the OS drives with the defaults 4k cluster size
4. Format the Drives holding the data & log files with 64k cluster size
5. Format the Drives holding the filestream files with 4k cluster size
6. Set the server’s power option to High Performance
7. Use separate drives for application, database data, database logs, TempDB data & Backup folders.
8. Use a domain user for SQL service account & a different domain user for every service
9. Enable Trace Flags -T1118 & -T2371
10. Enable backup compression
11. Set default Database location for Data, Log & Backup files
12. Do not change the:
a. Maximum number of concurrent connections
b. Max Worker Threads
c. Priority Boost
d. Lightweight Pooling
13. Change the Network Packet Size to 8192
14. Set the Max Server Memory to 1GB for OS and 1GB for each 4GB and 1GB after the first 16GB
15. Change the default size for data & log files of the Model database
16. Change the file growth to fixed units of the Model database
17. Move TempDB to its own drive
18. Change the Cost Threshold for Parallelism to 20-25
19. Configure the MAXDOP:
For more than 8 CPU Cores MAXDOP=8
For 8 or less CPU Cores MAXDOP=N, where N are the CPU Cores
Always use even values
MAXDOP should be no more than the number of cores per NUMA node
20. Enable the Optimize for Ad Hoc Workloads
21. Enable TCP/IP & add firewall exceptions (UTP 1434 SQL Browser)
22. Configure Database Mail, create operators, create Alerts for Severity 16-25 & Errors 823,824,825
23. For new databases:
a. Don’t use default file size
b. Don’t use percentage as file growth
c. Don’t set Auto Close
d. Don’t set Auto Shrink
e. Don’t unset Auto Create/Update Statistics
f. Set ANSI_NULLS to True
g. Set ANSI_PADDING to True
Pantelis Apostolidis is a Sr. Specialist, Azure at Microsoft and a former Microsoft Azure MVP. For the last 20 years, Pantelis has been involved to major cloud projects in Greece and abroad, helping companies to adopt and deploy cloud technologies, driving business value. He is entitled to a lot of Microsoft Expert Certifications, demonstrating his proven experience in delivering high quality solutions. He is an author, blogger and he is acting as a spokesperson for conferences, workshops and webinars. He is also an active member of several communities as a moderator in azureheads.gr and autoexec.gr. Follow him on Twitter @papostolidis.
