3.6. Best practice considerations: physical server design
Today's off-the-shelf database servers from one of the major system vendors are both powerful and flexible enough for most SQL Server implementations. However, a sound understanding of server design and configuration principles remains an important skill.
The SQLIO and SQLIOSIM tools should be used to validate both the capacity and validity of a storage system before SQL Server is installed. Consider these tools an essential component in the commissioning process of a new production system.
Track-align SQL Server partitions before formatting with a 64K allocation size using the NTFS file system.
Use a switched gigabit network for maximum network performance, and ensure NIC speed and duplex settings are manually configured.
Consider NIC teaming for performance and redundancy, particularly for connections to an iSCSI SAN, but be aware of the implications for use in a cluster (discussed in chapter 5).
Component redundancy is a crucial aspect of a reliable database server. Build fault tolerance into as many server components as possible, including power supplies and fans, and protect servers from power failures with a UPS.
To minimize costs and maximize performance, purchase servers and components within the framework of a server replacement policy. Taking into account falling prices and rapidly increasing component performance, servers should be purchased for a two- to three-year production lifecycle.
Minimize support costs and fault correction time by developing standard database server builds, including standardizing hardware, software, bios and firmware versions, and so forth.
Before consolidating or virtualizing SQL Servers on one host machine, consider the load profiles of each, best obtained through a baseline analysis process. Locating complementary instances together is of crucial importance in avoiding performance problems.
In addition to the load profiles, give consideration to maintenance windows, disk usage, and tempdb usage when consolidating servers.
Before virtualizing or consolidating SQL Server instances, understand the licensing impacts of both methods.
To enable thorough performance analysis and tuning of a virtualized SQL Server, consider the need to access and learn the virtualization toolset. Such tools are required in order to understand the impact one virtual machine may be having on another.
When virtualizing SQL Server instances, all of the other best practices we'll cover throughout this book hold true, such as using separate disks for data and logs, scheduling regular backups, preallocating space to avoid autogrowth operations, and so forth. Virtualization isn't an invitation to ignore the importance of these fundamental tasks.
Most importantly, before virtualizing a critical SQL Server environment, ensure the support implications of doing so are well understood.
Additional information on the best practices covered in this chapter can be found online at http://www.sqlCrunch.com/server.
The last three chapters have been focused on planning and design. In the next chapter, we'll roll up our sleeves and install a SQL Server 2008 instance.