A good SQL Server hardware platform is essential to a well running installation of Insight. Consideration should be given to selecting hardware for your environment that will provide you with the best possible performance and reliability. Also, careful planning should go into the configuration of the chosen hardware, as this can have as much or more impact on performance as the hardware itself.
Overall, extra time and effort spent on selecting and configuring your SQL Server hardware results in better performance, scalability, and reliability.
If you use the Insight system to run your business, you must ensure it is reliable and resilient. To improve these factors, use redundant hardware wherever possible. Always have your SQL Server connected to an uninterruptible power supply (UPS).
SQL Server hardware recommendations and consideration
Keep the following recommendations in mind when purchasing your SQL Server server:
- Purchase a name-brand server from a reputable manufacturer.
- Avoid assembling your own server with components that have not been certified to work together. Purchasing brand servers lowers your risk of hardware failure and provides you with a single point of contact for support if you experience difficulty with the hardware.
- Seek expert advice about hardware and how it relates to SQL Server before you buy your SQL Server server. Also, do not assume that the factory configuration of your brand new server is already optimal. Often, it needs to be further tuned and optimized for use with SQL Server.
CPU
In general, Cyncly recommends that you purchase a server with as many CPUs as you can reasonably justify. Higher speed CPUs perform better for Insight versus slow speed CPUs with more cores.
Memory considerations
Purchase as much memory as possible for your SQL Server because this reduces the amount of input/output (I/O) required to fulfill database reads and writes. Accessing data from memory is much faster than from disk drives; therefore, you experience better overall performance with more memory.
Disk drives and I/O performance
Use RAID for your data protection. Read about RAID levels within the context of SQL Server. Consider using hardware RAID 10 as the best performing RAID. Make sure that you give preference to more drives versus the size of each. SQL Server is random I/O bound. Therefore, having more drives of smaller size (versus fewer of larger size) is beneficial. Consider more than one I/O controller and spreading drives around them.
Consider Write-Back RAID controller caches, but then ensure that this cache is battery backed and that your SQL Server is protected by UPS.
TIP: See the following Microsoft Knowledge Base article for more information about caching controllers: http://support.microsoft.com/kb/86903/en-us
Regarding Windows NT File System (NTFS) Allocation Unit, the best practice for SQL Server is to choose 64 KB because this reduces the likelihood of I/Os that span distinct NTFS allocations, which could result in split I/Os. Please test your backup software for compatibility with this size.
For optimal performance, split SQL Data, SQL Logs, and SQL TempDB onto different hardware drives, RAIDs, and preferably onto different controllers.
You may consider using SSD drives, particularly for the SQL TempDB.
Network considerations
Ensure that your SQL Server is connected to your network with a minimum of 1 GB network connection.
Miscellaneous SQL Server configuration recommendations
The following list concerns general recommendations for optimal SQL Server performance:
- Always use Page Checksum to audit data integrity.
- Use NTFS for security and availability of many SQL Server features.
- Use instant file initialization for performance optimization.
- Use the Windows Network Service Account for security.
- Choose your RAID level carefully. For excellent performance and high reliability of both read and write data patterns, use RAID10. For read-only data patterns, use RAID5.
- For optimized I/O parallelism, use 64 KB or 256 KB stripe size when configuring RAID10 or RAID5.
- For future scalability and ease of maintenance, use volume mount points.
- To increase bus bandwidth reliability, use multipathing software.
- Directly attached I/O is recommended for small- to medium-sized servers.
- SAN systems are recommended for larger servers.
- NAS systems are not recommended. Use iSCSI instead.
- For better recoverability, use an SCSI interface instead of SATA and IDE.
- For larger server loads, use SCSI or SATA with TCQ support.
- Store transaction logs separate from data files. Do not stripe on the same disk as the data files.
- For large bandwidth demands on the I/O bus, use a different bus for the transaction log files.
- The number of data files within a single file group should equal the number of CPU cores.
NEXT TOPIC: Database Tuning and Maintenance