1. Updated and Stable Components
Use the latest tested and validated software, firmware, and driver versions for NICs, storage arrays, and other components.
2. Deploy in Active Directory
To take advantage of integrated security and centralized management, deploy the SQL Server in an Active Directory domain, and use Windows authentication only if possible. Do NOT deploy the SQL Server on an AD controller.
3. Static IP Address
Configure the SQL Server with static IP address. This practice assures the SQL Server will remain available even the DHCP server failed.
4. RAID Disk
The RAID levels have a big impact on both performance and availability. As you might expect, the more expensive options typically offer the best performance and availability.
General suggestions: put tempdb data files and log file in separate RAID 10 disks (or RAID 1 for cheaper cost), put user database data files in RAID 5 (or RAID 10 for better write performance), and put user database log file in RAID 1 (or RAID 10 for better performance). Isolate log from data at the physical disk level. Windows OS should be put in another RAID 1 disk for fault-tolerance.
5. NTFS Format and Allocation Unit Size
Use NTFS file system format. Put the OS in its own disk with default 4KB allocation unit size. The SQL Server DB data files and transaction log files should be put on separated disks with 64KB (1 extent = 8 x 8KB data pages) allocation unit size. Do NOT use compressed drives.
6. Allocation of tempdb
Separate the tempdb data files and transaction log file onto their own disks to yield better I/O performance. Use one data file per CPU core to reduce latch contention and I/O bottleneck on tempdb. Make all tempdb data files equal size. Set the recovery model of tempdb to SIMPLE.
7. Allow "Lock pages in memory"
For better performance, prevent the OS from paging the SQL Server's memory to disk, by giving the SQL Server service account the right to "Lock pages in memory" in Local Security Policy.
8. Enable Instant File Initialization
The default behavior is to initialize the storage with zeros whenever SQL Server creates a data file or grow a data file, this zero-filling process can be very time-consuming. When you enable Instant File Initialization, the storage will remain uninitialized until SQL Server writes data to it. To enable it, give the SQL Server service account the right to "Perform volume maintenance tasks" in Local Security Policy.
9. File Sizing
Size all data, log, and tempdb files with planned file growth and manually expand files during idle periods. Initialize all data files in a database equal size. Use AUTOGROWTH only as a safety net to prevent files from filling up and forcing the database to read-only. Set the AUTOGROWTH increment using fixed size rather than by percentage for a controllable performance impact during autogrowth.
10. Service Pack
Apply the latest SQL Server service pack (SP) after installation completed.
11. Run SQL Server Best Practices Analyzer
After you finished the SQL Server setup and put all your databases into it, run SQL Server Best Practices Analyzer (BPA) to determine if the configurations are set according to the recommended best practices. You must also download and setup Microsoft Baseline Configuration Analyzer (MBCA) in order to run the SQL Server BPA.
12. Exclude SQL Server Files from Anti-Virus scanning
Antivirus programs can create issues with SQL Server functionality, and it is important to exclude them from their scope, by adding them to the exclusions list. File types to exclude: *.mdf, *.ndf, *.ldf, *.bak
No comments:
Post a Comment