During the SQL Server or SSAS installation process, you choose only one disk
in the cluster group where the resources will be installed. This disk will contain
the system databases and default files. To use all disks in the cluster group,
after the installation is complete, you must add them as a dependency of the
SQL Server or SSAS resources. This process involves taking the resource offline,
which translates into downtime. Therefore, make sure that you perform this configuration
step before the servers become live in your production environment.
Capacity and Performance
Two other major factors in your disk design are capacity and performance, both
of which are universal concerns with any SQL Server deployment. However, these
two factors can be more challenging to achieve in a clustered configuration
where you might have design restrictions.
Capacity planning. Capacity planning is essential for both sizing
the disks and knowing the performance your environment will require to ensure
that the disks have enough I/O capacity. (For information about capacity planning,
see "SQL Server Consolidation," InstantDoc ID 95461.) Although the act of sizing
isn't straightforward, its outcome will be a number (both I/Os and space) that
lets you meet your current needs while providing enough space for the future.
As you'll see in a moment, you'll need to make some tradeoffs, so determining
this number isn't simple.
Despite what many storage engineers will tell you, having tons of cache on
your disk subsystem isn't the panacea for great performance. If a disk subsystem
is shared among file servers and other applications (along with your SQL Server
implementation), each usage has its own I/O patterns and optimizations and will
take part of that cache. You can't assume your data will remain in the disk
cache. You should get as much cache as you need, but never assume that it will
compensate for all the problems of a poor disk design. It's nearly impossible
on a shared disk subsystem to optimize for only one type of I/O. How many applications
perform only write or read? The reality is that you'll always have a mixture
of I/O types, and you need to factor that into your disk design.
Performance. When it comes to performance, the problem with most
SQL Server deployments is related to I/O. Do you have the right amount of I/O
available for your workload? Back in the good old days of SCSI-based disk subsystems—when
you had more control over how the disks were carved up and could easily dedicate
spindles to SQL Server and isolate LUNs—I/O was "easy" to manage. Let's
assume that a modern spindle gets about 150 I/Os per second. If your workload
needs 2000 I/Os, you would need the equivalent of at least 14 physical disks'
worth of I/O to meet your current need (and that doesn't address growth, for
which you would need to factor a percentage more to allow for future capacity).
With modern disk subsystems, many storage engineers will carve the storage
into one (or a few) big chunks, from which the disks that are presented to Windows
are configured on top of it. The result is that only a percentage of a given
spindle's I/Os are available to a LUN because it's comprised of slices of multiple
spindles instead of dedicated disks. Also, not only does this situation potentially
mix I/O types on a single disk but it might require a larger pool of disks to
get equivalent performance than if dedicated spindles were used. In my experience,
storage engineers only ask how much space you need, not how many I/Os. This
isn't a problem until you have performance problems that wind up being related
to insufficient I/Os (meaning a poor disk implementation). In this case, the
DBA loses because the DBA generally isn't hardware savvy and needs help proving
that the performance problem isn't actually SQL Server.
Determining your I/O requirements isn't exactly straightforward. As I discussed
in "SQL Server Consolidation," you need to know the I/O signatures of individual
databases, especially under load. You need to think about how both your data
and log files are used and how they might grow. You need to know how the database
is used, and you need to understand the types of activity that will occur in
it. Performance Monitor counters tell only part of the story. I always stress
that effective DBAs need to understand not only SQL Server but also Windows
and hardware. What you don't know can hurt you—and that's usually the
reason why I'm called to assist customers: Administrators haven't thought these
concerns through, and now a problem needs to be fixed.
Alphabet Soup
The most challenging design aspect of any failover clustering implementation
is the 26 drive-letter limitation. A SQL Server failover cluster requires that
all drives associated with the clustered instance have a drive letter. You only
have 26 letters available to you. SQL Server 2005 adds support for mount points,
but they don't absolve the need for a drive letter: For SQL Server to use it
in a cluster, a mount point must be mounted under a drive letter. What a mount
point does, however, is let you use an existing clustered drive letter to add
more space without requiring the use of another drive letter. This functionality
has obvious benefits, such as separating out disk I/O since a mount point is
a physically separate disk. Keep in mind that these are still LUNs that you'll
need to manage. You don't want to use 26 drive letters, but you also don't want
a bunch of LUNs overwhelming your servers. You must achieve a delicate balance.
Having only a finite number of drive letters means that you have to be smart
about your disk design. You must design for the here and now while keeping an
eye toward possible future expansion. In a typical environment, no one has 26
drive letters available. A few are already gone (e.g., local system disk, floppy
drive, mapped drives, CD/DVD drive), so you have at most 22 or 23 available
to you. Of those 22 or 23, a few more are going to be taken by the clustered
Microsoft Distributed Transaction Coordinator (MS DTC) and the quorum drive,
so you're down to 20 or 21. After all that, you can think about your disk design
for SQL Server.
Consider an example in which one instance of SQL Server 2005 and one of SSAS
2005 are installed on the same Windows server cluster. If you're planning on
putting 100 databases (all of which have relatively low I/O requirements) under
the SQL Server instance, you won't be able to split out data and log files for
each database onto an individual drive letter. To avoid disk contention, it's
usually recommended to separate data and log files for a database. However,
this advice doesn't necessarily mean putting all data or all log files for your
databases on a single drive. Achieving this is virtually impossible, and in
reality, the recommendation to split data and log files is an impractical "best
practice" nine times out of ten. When you consider that you also don't want
a ton of LUNs in your disk configuration, you can see how you're going to have
to make some tradeoffs.
In the context of this example, a rule is put into place that no single data/log-file
disk for the SQL Server installation will have more than 25 databases. That
decision translates into four disks for the current number of databases, but
you add one more for expansion purposes. One disk is required for the system
databases, and because only the tempdb database is heavily used, it can coexist
with the other system databases on that disk, but it shouldn't be placed with
any of the user databases. To split up I/O and provide for a bit of growth,
you devote two disks for backups. The SSAS implementation is straightforward,
and you split out the system drive from your cubes. Table
1 shows what the disk design would look like.
If you take the same example but utilize mount points in conjunction with the
required drive letters for the data/log-file disks as well as the backup disks
for SQL Server, drive utilization changes. Table
2 is the reconfiguration, including the use of mount points. I'm not advocating
this approach over the previous one, which uses only drive letters; it's just
different. Although you gain only five drive letters by using mount points,
those five letters could make the difference between adding another SQL Server
instance or two to your cluster—as well as possibly simplifying database
management because your DBAs need only remember a few letters. Most of my customers
haven't used mount points with their failover clusters; however, for some, mount
points might be the difference between achieving a certain goal and needing
to buy more servers to obtain the number of required SQL Server instances. Remember
that if you're clustering SQL Server 2000, using mount points isn't an option.
At the end of the Day
Implementing failover clustering isn't a trivial task. More than nearly any
other component of a clustered deployment, the disk subsystem can contribute
to downtime or other availability problems down the road if you fail to design
and implement it properly. Take your time and work toward a disk configuration
that will serve you well for the lifespan of the solution that you're putting
into place. Make sure that it can support your business not only in terms of
space but also in terms of performance. Although it might be a lot of hard work
and compromise, your end state will give you an available, scalable, reliable,
and manageable solution for years to come.
End of Article
Prev. page
1
[2]
next page -->
