| |
 |
|
SQL Server Tips by Burleson |
24 x 7 Monitoring
Ad-hoc monitoring works great when used as intended, but it loses
traction under certain circumstances. Certain critical databases
require special attention, which normally equates, among other
things, to around-the-clock supervision. Often, the sheer number of
databases that require monitoring can present problems and require
that a specialized set of monitoring software be used.
For these scenarios, an enterprise database
monitor or homegrown process is needed. An enterprise monitoring
setup is designed to manage the performance of 24 x 7 applications
as well as handle the unique needs of large SQL Server database
farms. With the features of cursory and intelligent client-side
monitoring components, enterprise 24 x 7 monitoring will also sport
these key capabilities:
-
Provides constant, unattended monitoring
of assigned databases.
-
Offers scalability features for large
database farms.
-
Supplies proactive notification and
correction mechanisms to minimize database downtime.
-
Provides historical analysis of
performance metrics.
To be truly effective for critical
databases, a database monitoring system needs to be on duty at all
times. This means the monitoring system needs to be working even
when the database or operations staff is not. Such a capability
implies functionality far beyond the graphical user interface (GUI)
normally used for assessing the performance of a database.
The 24 x 7 monitoring system polls its assigned database without the
use of any visual interface and enforces predefined limits in terms
of what it will accept as proper activity. To avoid downtime and
large performance dips, the enterprise monitoring system must offer
robust notification and automated correction mechanisms that detect
performance exceptions as far in advance as possible, notify
responsible parties immediately, and take action when it is
instructed to do so.
The sheer size of an SQL Server database installation can be a
challenge in its own right. Traditional manual ad-hoc database
monitoring breaks down when the number of staff members is greatly
outpaced by the number of key databases that need monitoring. Only
an automated scalable approach can be confidently used to guard the
availability and performance of a large database installation.
When scalability is mentioned in the context
of database monitoring, it can mean many different things. However,
the following is a “must have” list of capabilities that have to be
present in the enterprise monitoring system for it to be effective
in terms of scale:
-
Handle Increasing Monitoring Load – The
monitoring system must be able to add, subtract, and handle
assigned databases easily, while maintaining consistent
operation.
-
Offer Intelligent Enterprise Views – The
monitoring system should provide ways to quickly summarize
performance across all monitored databases, and should give an
idea of what is up, what is down, and what needs attention. This
is not an easy thing to do in many circumstances.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
