One of the biggest trends I'm currently
seeing with my clients is the desire for a consolidated SQL Server environment.
However, just because my clients think they
need to consolidate doesn't mean they're
ready to do so, or even necessarily that they
should. Before you consider implementing a
consolidated SQL Server environment, you
must assess your existing environment to
determine whether planning a consolidation is even possible—or worth your time.
(Because the analysis and assessment can be
time consuming, you
might want to obtain
help; see the sidebar
"Getting Help" for more information.) This article outlines the
steps you need to take to gather the information necessary for making such an assessment. The solution that I discuss involves
workload consolidation; for an alternative
consolidation solution, see the Web-exclusive sidebar "Different Paths to Server
Consolidation: Workload vs. Virtualization,"
http://www.sqlmag.com, InstantDoc ID
95465.
Driving Factors
Consolidation tends to be a top-down
rather than bottom-up business decision.
In most cases, upper management wants
to do more with less and hopes to recover some costs. Thus, consolidating SQL Server
is only part of a much larger transformation
within a company. Another case in which
a company might push for SQL Server
consolidation is if its data center is at or near
capacity and building a new data center isn't
an option. However, the biggest driver for
consolidation that I see on a regular basis is
a situation you might be familiar with: SQL
Server sprawl.
SQL Server has always been a Trojan
horse in many environments—it sneaks in
through the back door and becomes pervasive because it's so easy to install and deploy
whenever an organization needs a database
server. You can install SQL Server on everything from mobile devices through servers.
A side effect of this phenomenon is that
it leads to a mentality of one database per
instance, with that solitary database housed
on a standalone server that's typically underutilized and/or has poor availability. In some
cases, a company's lone SQL Server machine
is a desktop machine that was never meant
for production. But because people started
using the instance and liked the solution
built on top of the database, that desktop
machine became mission critical.
These days, you might be able to gain
more significant performance increases for
your SQL Server installations with a shelf
full of blade servers than you can with a 4U
server you purchased only three years ago.
Because many companies house terabytes of data that require management, you need
to carefully consider everything from hardware purchases to operations. Consolidation
means more than just stacking multiple
SQL Server instances or putting multiple
databases in one instance. You need to take
into account the amount of cooling needed
to ensure that the servers don't overheat, as
well as the overall energy requirements for
the multiple servers in your data center.
To do consolidation right, you'll most
likely need to buy new servers. Assuming
that you can use your existing servers might
be a bad idea. If you reconfigure the servers,
you won't have a true fallback plan after you
consolidate. Instead, consider repurposing
existing hardware to refresh development,
quality assurance (QA), or staging environments. As you assess your consolidation
strategy, you must decide how much and
what type of new hardware you need and
determine the cost.
In addition, ask yourself whether each
instance in your environment was deployed
and is administered in the same manner.
In my onsite experience with both large
and small customers, not a single one has
consistently deployed SQL Server in exactly
the same manner—despite the fact that each
organization has posted standards. Managing
these environments is a nightmare for DBAs.
If each instance has different sort orders, service pack levels, database options, configuration settings, and other differences, you must
remember all the variations or you might do
something on one server that would be detrimental to another. Consolidation can assist
in solving these problems; in fact, it's often
the catalyst to standardize deployments and
other paradigms (e.g., operations, processes,
administration) in an environment.
What You Don't Know Will Hurt You
The first and perhaps most important thing
you need to begin consolidating your environment is information. You need more
than just a day's worth of Performance
Monitor statistics, because a day's worth of
counters won't give you enough information on which to base decisions for a proposed consolidation. In addition, numbers
tell only part of the story.
First, compile a complete list of the
known servers or other machines on
your network with SQL Server installed.
Although most clients think they know their
entire SQL Server inventory, they actually
don't. After you have this list, use a third-party tool (e.g., Quest Software's Reporter)
or a homegrown utility (using Windows
Management Instrumentation—WMI) to
query all of the machines on the network
that have any kind of SQL Server resource
(including products such as Microsoft SQL
Server Desktop Engine—MSDE). Then,
compare the lists to see whether any new
SQL Server installations were discovered. If
the lists don't match, you need to determine
who owns the newly discovered instances,
what they're used for, and why you didn't
know about them.
Next, record all aspects of SQL Server
(e.g., physical machine information such as
processor type and speed, brand, and model;
all OS settings, including driver versions,
service packs, and patches; instance configuration, including all server-level options and
settings, code page, security, jobs, and DTS or
SQL Server Integration Services—SSIS—
packages; database configuration, including
database options, database size, amount of
free space, and size of each file). Record
this information for every discovered SQL
Server machine and instance. This detailed
information will ultimately help you determine which databases you might be able to
combine with others on a specific instance
of SQL Server, as well as which databases
will work well together. The information
you document will influence many aspects
of your consolidation assessment and plan,
including your decision regarding the final
instance standard (e.g., the version of SQL
Server, which forms of high availability you
might deploy). The documentation will also
serve as a reference if you move a database
and someone needs to know where it came
from and whether you moved, reconfigured,
or recreated objects such as jobs, DTS or
SSIS packages, and replication. This might
be the only chance to capture a complete
snapshot of your SQL Server environment.
Although most DBAs don't track historical information about each database, you
need this information (e.g., database usage, growth patterns—including size of database
backups). For example, if a database has
been in production for two years, knowing
its growth pattern can help you predict the
kind and amount of storage space you might
need in the future. Merely making best guess
estimates isn't an effective method for sizing
an environment. If you don't have accurate
information, you might wind up undersizing or grossly oversizing your proposed
environment. Undersizing means you'll run
out of capacity quickly, possibly even before
you finish the consolidation; oversizing
might cost your company money that could
have been better spent elsewhere.
You can't turn back the clock and create
information that doesn't exist, so do your
best to track down the information you
need. If you're lucky, the information might
exist elsewhere, such as in a central monitoring system that has been monitoring disk
counters for two years but that you didn't
have access to. Talk to your systems administrators to see if they have information about
your database servers that you don't have.
In addition to having growth information, you need to understand each database,
instance, and server's performance. Your company most likely has a centralized tool
such as Microsoft Operations Manager
(MOM), NetIQ AppManager, or Computer Associates' (CA's) Unicenter that's
gathering these statistics. If not, you can use
Performance Monitor counters to find this
information. Before you set up your own
monitoring, make sure you aren't duplicating
any efforts. Although you could gather statistics from every available counter, doing so
would be overkill—you'd have a volume of
information that you wouldn't know what
to do with. Instead, gather information from
a select list of counters that gives you the
biggest bang for your buck. The Web-exclusive sidebar "Using Performance Monitor
to Record Counters," http://www.sqlmag.com, InstantDoc ID 95466, explains how to
obtain the information you need. For SQL
Server counters, you can also query SQL
Server 2005's Dynamic Management Views
(DMVs) or SQL Server 2000's sysperfinfo.
Prev. page
[1]
2
next page 
