When deploying a new SQL Server instance, it is important to consider the best balance of availability, performance, and cost. In this article, we will discuss some recommended hardware and server configuration best practices.
High Availability
The primary goal of high availability (HA) is to ensure that your SQL Server service and database are always available and free from consistency issues. There are four main ways to provide HA:
- Power: Your server should have dual power supplies connected to a UPS for extra protection.
- Redundant Hardware: Use RAID configurations for disk redundancy and always keep backups on a separate device.
- Clustering: Combine multiple servers into a cluster to share or quickly takeover workload in case of failure.
- Multiple Copies of Database: Have standby copies of your database available that can be switched to if needed.
Performance
When it comes to performance, there are several factors to consider, including hardware bottlenecks, database design, and application design. In this article, we will focus on hardware bottlenecks.
The main types of hardware bottlenecks are disk IO, memory, CPU, and network IO. Here are some recommendations for each:
- Disk IO: Separate data files, log files, and TempDB files into different volumes. Use RAID configurations that work well with the type of IO (sequential or random) expected for each file type.
- Memory: SQL Server caches data pages in RAM, so the more RAM you can allocate, the better. Use a 64-bit processor and OS to support as much memory as possible.
- CPU: CPU bottlenecks are rare, but watch out for operations that utilize parallelism, such as index rebuilds, seeks, and joins.
- Network IO: Segment your network traffic by types to avoid interference. Encourage your application team to limit the amount of data passed between the SQL Server and the application server.
By following these best practices, you can ensure a well-performing and highly available SQL Server deployment. Remember to always test your configurations before putting them into production to ensure optimal performance.