email article printer friendly

Configure Disks for High Availability
Configure SQL Server properly to ensure high availability and optimal performance.
by Allan Hirt with Cathan Cook, Kimberly L. Tripp, and Frank McBath

August 2003 Issue

About this Article:
This article is excerpted from Chapter 4 of Microsoft SQL Server 2000 High Availability
ISBN: 0-7356-1920-4
Author: Allan Hirt with Cathan Cook, Kimberly L. Tripp, and Frank McBath
Price: $49.99
Pages: 784
URL: http://www.microsoft.com/mspress/books/6515.asp

Whenever you design a system for availability, growth, and performance, there is some form of trade-off involved. This article guides you step by step through the decision-making, planning, and implementation of disk subsystems used with SQL Server.

Capacity Planning
Numerous factors go into deciding how your disks will be configured. The first part of that decision process must be capacity planning, the art of determining exactly how much space you need. For new systems, this can range from very simple to very complex, depending on how much information you have up front. For extending existing systems, upgrading, or migrating to a new hardware or software platform, capacity planning should be easier because there should be documented history on the prior growth of that system, database, and application.

Two kinds of disk space usage must be known: raw space, and the physical number of disk drives needed for storage and to achieve the desired level of performance. Remember that figuring out how much raw space, which will then dictate how many drives you need, is based on your application's requirements of how it will be using SQL Server. The information and equations are based on these basic tenets of disk capacity planning.

Raw Disk Space Needed. Conceptually, the amount of raw disk space is represented by the following equation:

Minimum Disk Space Needed = 
Size of Data (per database, including system databases) + 
Size of All Indexes (per database, including system databases)

There is a flaw in this equation, however. Calculating your disk space based on the minimum amount needed would be a mistake. You need to take into account the growth of the database over the time that the system will be in service. That would transform the equation into this:

Minimum Disk Space Needed = Size of Data
(per database, including system databases) + Size of All 
Indexes (per database, including system databases, 
full-text indexes, and so on) + Planned Growth + 
Microsoft Distributed Transaction Coordinator (MS DTC) 
Logging Space + Amount of Operating Reserve + 
Amount Reserved for Hardware Optimization

The revised equation is much more realistic. The amount of operating reserve is the total drive space needed to handle an emergency situation.

For example, if you need to add a new column to a table, you need enough transaction log space for the entire table (possibly two times the table) because all changes are logged row by row and the table is affected in its entirety within one transaction. The amount reserved for hardware optimization is based on the disk drive performance of the inner tracks of the physical drive, which might be slower than the outer tracks.

In this case, the amount can range from 10 to 40 percent, depending on whom you ask, but the performance characteristics might be different on more modern disk drives (up to 25 percent faster on the outer tracks than the inner tracks). You can combine this reserve space with the operating system reserve in most cases. With MS DTC usage, if you have high transactional volume, the logging of MS DTC might become a bottleneck.

Note: How do you actually determine the size of each database? You need to know how the applications will be using the databases—not only the user databases created specifically for the application, but system databases msdb and tempdb as well.

Application Database Usage. Each application using SQL Server has its own signature that is its distinct usage of SQL Server. Assess each application that will be utilizing one or more databases. What kind of work is it doing? Is it mainly reads? Mainly writes? A mixture? Only reads? Only writes? If the workload is a combination of reads and writes, what is the ratio of reads to writes? (Some hardware solutions can assist you in this matter to report on real read versus write statistics, as well as storage caching statistics.) If this is an existing database system, how has the usage changed over time? If this is a new system, what is the projected usage of the system? Do you anticipate changes in usage patterns? You might need to ask more questions. For a packaged application that you are not developing, ensure that your vendor can reasonably answer the questions so you can plan your hardware appropriately. If possible, get the input of other customers who have used that software as well.