The following section lists best practices and recommendations for deploying SQL Server on Windows.
- When configuring host-to-storage connectivity, spread connectivity to storage ports of different PowerMax nodes (wide) and do not concentrate on one node before moving to the next (deep). This method allows for better spread of storage CPU resources from each node and higher availability if a component fails.
- Consider port count and speed. For example, a 32 Gb FC port can deliver up to 3 GB/sec bandwidth. If a host uses only two initiators or connects to only two targets (storage front-end ports), bandwidth higher than 6 GB/sec from that host is not expected. In general, a good ratio is for a two-socket server to use two dual-port HBAs connected to four or eight storage ports. Customers have different standards and requirements and will adjust the design to their requirements.
- For performance-critical databases, a minimum of four paths per volume is recommended using host-based multipathing software such as Dell PowerPath or the operating system native multipath software. The multipath software provides both load-balancing and resiliency.
- The connectivity and volume masking configuration affect the number of paths per volume. FC connectivity is determined by using FC zones. If a host has four HBA ports (initiators), and each port is zoned to two different storage front-end ports (targets), the connectivity is referred to as 1:2 and leads to eight paths per volume (four initiators x two targets each). This zoning method enables the masking view, which is configured using Unisphere or Solutions Enabler CLI, to have a single port-group with all the relevant front-end ports. The FC zoning determines the connectivity. This design reduces the number of port groups needed and volume masking complexity.
- Each unique path between the host initiator and storage target creates an I/O queue. When the database issues a high volume of read and write I/O requests to storage, these requests are queued across available paths to the storage volumes. A combination of sufficient volumes and paths ensures that no artificial bottlenecks are created due to too few queues that are becoming full.
- Use VLANs dedicated to iSCSI setup. VLANs allow logical grouping of network endpoints that minimizes network bandwidth contention for iSCSI traffic and eliminates impact on iSCSI traffic due to noisy neighbors.
- If all network devices in the iSCSI communication paths support jumbo frames, using jumbo frames on Ethernet improves iSCSI performance.
- To minimize host CPU impact due to network traffic, ensure that the Transmission Control Protocol (TCP) offloading engine (TOE) is enabled on a host network interface card (NIC), which offloads processing of the TCP stack to the NIC and eases impact on the CPU.
- As with FC connectivity, using PowerPath software or native multipathing helps with load balancing and high availability of iSCSI traffic through the host NICs.
Database files placement
- For performance critical databases, separate the database files and transaction log to different storage volumes. Use at least eight storage volumes for the SQL Server user database files. Double this number when SRDF/S or SRDF/Metro are used to allow better replication concurrency and parallelism.
- Because SQL Server does not stripe the transaction log updates (even when multiple transaction log files are created) on Windows, a single PowerMax volume is often enough for transaction log files with an alternative of using one storage volume for each transaction log file.
- When using local or remote storage replication, separate the system and user databases to different storage volumes. This practice allows replicating only user databases. Configure a target SQL Server instance (with its system databases) ahead of time so that the replicated database can be attached (added) to it without having to spend time during that process to create a new SQL Server instance.
Host considerations
- When formatting SQL Server volumes (for example, in Windows Disk Management), select an Allocation Unit size (AU) of 128 KB. This size matches the PowerMax track size and provides better storage data reduction efficiency and Microsoft Offloaded Data Transfer (ODX) alignment.
- Consider the level of data protection and availability requirements for the SQL Server instance and databases. Without WSFC and FCI, if the SQL Server instance fails, the data is inaccessible. With WSFC and FCI, failover can occur between cluster nodes, but the data is not protected from corruptions and other disasters. A backup solution creates a long-term data protection. Use storage snapshots and clones to create frequent crash-consistent database copies. Set up remote replications using SRDF/S or SRDF/A; use SRDF/Metro to set a stretch cluster. Consider multiple levels of protection such as nightly backups and hourly snapshots with or without remote replications.
- Because each SQL Server instance is active on one cluster node at a time, consider using multiple instances to load-balance workloads across cluster nodes. Each of these instances requires its own system databases and network components (virtual IP) but can use resources on a different cluster node. This practice also provides a level of isolation for user databases.