Presentation 10

possehastyMechanics

Nov 5, 2013 (4 years and 2 days ago)

58 views

10

Chapter Ten

Automating and Monitoring
SQL Server 2000

10

Objectives


Configure SQL Server Agent to automate the
administration of SQL Server 2000


Create and configure jobs, alerts and
operators by using SQL Server Agent


Monitor hardware resource usage and SQL
Server activity by using the Windows System
Monitor

10

SQL Server 2000
Automation


Automated administration


Refers to a programmed response to a
predictable administrative task or event on the
server


Leveraging this functionality in SQL Server
2000 frees database administrators to focus on
tasks that cannot be predicted

10

SQL Server Agent


SQL Server Agent


Separate program that executes administrative tasks and
generates alerts defined by database administrators


SQL Server Agent can be used to:


Execute administrative tasks


Detect system conditions and automatically instigate
measures to resolve


Alert people in a variety of ways

10

Jobs


Jobs


Administrative
tasks defined
once and
executed as
many times as
necessary

Figure 10
-
1: SQL Server Agent architecture

10

Alerts


Alerts


Actions on an instance of SQL Server 2000 in
response to a particular event or performance
condition


Commonly used to trigger notification of a
problem to administrative users of a database
known as operators

10

Operators


Operators


Users who are often configured within an instance
of SQL Server 2000 to receive notification of
particular jobs and alerts


Operators can receive notification in one of three
ways:


E
-
mail


Pager


The NET SEND command

10

Operators


E
-
mail notifications


Provided through the SQL Mail service


Pager notification


Actually an extension of the standard e
-
mail
notification in that only those pagers whose
service providers handle e
-
mails for paging can
be used

10

Operators


NET send


Command
-
line application in Windows 2000 and Windows NT that
automatically forces a message box to pop up on a computer
screen when the user is connected to the network

Figure 10
-
2: NET SEND message box

10

Multiserver Automation

Figure 10
-
3: Multiserver automation architecture

10

Multiserver Automation


Master server


Houses a complete list of the various operations
(jobs) that must be performed on the various target
servers


Target server


An instance of SQL Server 2000 that connects to
the master server and receives jobs scheduled to
be run locally when using multiserver automation

10

Configuring SQL Server
Agent

Figure 10
-
4:
General tab of
the Properties
window SQL
Server Agent

10

Configuring SQL Server
Agent


Advanced tab


Allows you
configure SQL
Server Agent to
monitor and restart
SQL Server and
SQL Server Agent
services if they
unexpectedly stop

Figure 10
-
5: Advanced tab of the SQL Server Agent Properties window

10

Configuring SQL Server
Agent


Connection tab


Where
authentication
credentials are
specified for
SQL Server
Agent

Figure 10
-
6: Connection tab of the SQL Server Agent Properties window

10

Creating and Configuring

Jobs, Alerts and Operators


Creating operators


To create operators in
Enterprise Manager,
expand Management
folder, expand SQL
Server Agent node,
right
-
click on operators
node and click New
Operators option from
context
-
sensitive menu

Figure 10
-
7: General tab of the New Operators window

10

Creating and Configuring

Jobs, Alerts and Operators

Figure 10
-
8:
Notification tab of
the New Operator
Properties window

10

Creating Operators with

T
-
SQL Statements


SQL Server 2000 provides several system
-
stored procedures for managing operators
in the SQL Server Agent notification system


Since the msdb database stores all of the
information about operators, alerts and jobs, all
of these procedures must be run from the msdb
database

10

Creating Operators with

T
-
SQL Statements


The sp_operator system
-
stored procedure is used to add
new operators


The pagers_days parameter specifies the days on which
the operators can receive pager notification

Table 10
-
1: Day Values for the Pager_days Parameter

10

Updating Operators in

T
-
SQL


The sp_update_operator system
-
stored
procedure is used to modify the properties
of existing operators


Accepts the same parameters as the
sp_add_operator procedure but requires that
the name parameter be a valid existing
operator

10

Viewing and Deleting Existing
Operators in T
-
SQL


The sp_help_operator system
-
stored procedure is
used to return information about all of one of the
operators defined in the msdb database for an
instance of SQL Server 2000


When called without any parameters, the procedure returns
a result set containing the configurations for all operators in
the system


If the operator_name parameter is specified, only the
information of that particular operator is returned


To delete and existing operator, you would use the
sp_delete_operator system
-
stored procedure

10

Viewing and Deleting Existing
Operators in T
-
SQL

Figure 10
-
9: Results of the sp_help_operator system
-
stored procedure

10

Creating Jobs

Figure 10
-
10: General tab of the New Job Properties window
(Figure 10
-
10 contains sample job information)

10

Creating Jobs


Steps tab will show a list of all steps in a particular job

Figure 10
-
11: Steps tab of the New Job Properties window

10

Managing Job Steps

Figure 10
-
12: General tab of the New Job Step window

10

Managing Job Steps


There are three general types of steps:


T
-
SQL statements


Windows executable programs (.exe files)


ActiveX scripts


In addition to these, there are also some
pre
-
defined step types associated with
replication

10

Managing Job Steps

Figure 10
-
13: Sample information in the Edit Job Step window

10

Managing Job Steps

Figure 10
-
14: Advanced tab of the New Job Step window

10

Scheduling Jobs

Figure 10
-
15: Schedules tab of the New Job Properties window

10

Scheduling Jobs

Figure 10
-
16: New Job Schedule window

10

Configuring Job
Notifications


The automation system powered by SQL
Server Agent service keeps a log of all job and
step history


This information can be helpful when debugging
jobs and when verifying that jobs have executed
successfully


In addition to this, various operators can be
configured to receive notifications of jobs as they
execute

10

Configuring Job
Notifications

Figure 10
-
17: Notification tab of the New Job Properties window

10

Configuring Job
Notifications


From screen in Figure 10
-
17, you would typically
assign various operators to receive information
via e
-
mail, pager and NET SEND command


To send an e
-
mail notification to an operators, check
the E
-
mail operator option and then select an operator
from the list box to the right


The notification can be configured in three ways:


When the job fails


When the job succeeds


Every time the job executes regardless of success or failure

10

Creating Jobs with T
-
SQL
Statements


There is a set of system
-
stored procedures that can be
used to create and manage jobs


They each address the various options available through the
Enterprise Manager user interface when creating new jobs

Table 10
-
2: Notify level values for sp_add_job system stored procedure

10

Creating Jobs with T
-
SQL
Statements


Adding Job Steps with T
-
SQL


The sp_add_jobstep system
-
stored procedure
is used to add steps to a job


Adding job schedules in T
-
SQL


To add a job schedule to a job in T
-
SQL, use
the sp_add_jobschedule system
-
stored
procedure

10

Creating Alerts


Alerts are used to respond to events as well
as performance conditions


You can define various alerts in the SQL
Server Agent system to notify operators
when particular events are written to the
Windows application log

10

Creating Alerts

Figure 10
-
18:
New Alert
window

10

SQL Server Event Alerts


SQL Server 2000 traps and reports certain
events to the Windows application log


Each error that SQL Server 2000 reports is
stored in the sysmessages table of the master
database


By default there are over 3,700 different
messages stored in this table


Each of these has a unique error number and a
severity level, as well as descriptive information

10

SQL Server Event Alerts

Table 10
-
3:
SQL Server
error security
levels

10

SQL Server Event Alerts

Figure 10
-
19:
Creating a
SQL Server
event alert

10

SQL Server Event Alerts


Each alert can be
configured to
notify operators of
the event using
the Response tab
of the New Alert
Properties window

Figure 10
-
20: Notifications tab of the New Alert Properties window

10

Performance Condition
Alerts


Alerts can be configured to fire when a
threshold on some performance metric is
breached


Performance counters


Individual metrics that are measured by SQL
Server 2000


Performance object


Grouping mechanism used to categorize related
performance counters

10

Performance Condition
Alerts

Figure 10
-
21:
Creating a SQL
Server performance
condition alert

10

Creating Alerts with T
-
SQL


Alerts can also be created using the
sp_add_alert system
-
stored procedure


As you would expect, only the parameters required
for a single type of alert are required when using
the procedure


Notifications are added to alerts using the
sp_add_notification system
-
stored procedure

10

Windows Performance
Monitor and SQL Server 2000


When SQL Server 2000 is installed on Windows 2000
or a Windows NT server computer, several
performance objects and their associate performance
counters are installed


These performance counters, as well as some native
Windows 2000 performance objects and counters,
are invaluable when determining where system
bottlenecks occur


The Windows System Monitor program is used to
create traces and monitor specific counters

10

Windows Performance
Monitor and SQL Server 2000

Figure 10
-
22:
Windows
System
Monitor

10

Windows Performance
Monitor and SQL Server 2000

Figure 10
-
23:
Add Counter
window of
Windows
System Monitor

10

Windows Performance
Monitor and SQL Server 2000

Figure 10
-
24:
Windows
System
Monitor
tracking
multiple
counters

10

Monitoring for Bottlenecks
with Windows System Monitor


Windows System Monitor can be used to evaluate
hardware conditions that affect the overall
performance of SQL Server 2000


There are three main categories of hardware
resources that can commonly cause bottlenecks
in server performance:


CPUs


Memory


I/O subsystem

10

Monitoring for Bottlenecks
with Windows System Monitor


CPU


In large multi
-
user environments, CPU processing
power can be a limiting factor in overall performance


When determining if the CPU is causing a bottleneck
on a server, use the % Process Time performance
counter from the Processor performance object in
Windows System Monitor


If the counter is consistently at 75% or higher, then the
CPU is probably part of the cause in a poorly
performing system

10

Monitoring for Bottlenecks
with Windows System Monitor


Memory and Cache Hits


Amount of memory available to SQL Server 2000 is a
very important value when optimizing performance


To determine if there is a sufficient amount of memory
available and being used by a SQL Server 2000
instance, check the following counters:


Total Server Memory (KB) of the SQL Server:Memory Manager
performance object


Buffer Cache Hit Ration of the SQL Server:Buffer Management
performance object

10

Monitoring for Bottlenecks
with Windows System Monitor


I/O subsystem


I/O subsystem performance bottlenecks are
directly related to capabilities of the hard drives on
the server


The important performance counters to watch
when monitoring for a possible I/O subsystem
bottleneck are found in the Logical Disk and
Physical Disk performance objects

10

Monitoring for Bottlenecks
with Windows System Monitor


The following performance counters of the
Physical Disk object monitor disk latency:


Avg. Disk Sec/Transfer


Avg. Disk Sec/Read


Avg. Disk Sec/Write

10

Monitoring for Bottlenecks
with Windows System Monitor

Figure 10
-
25:
Getting
counter
information
using the
Explain button

10

Chapter Summary


SQL Server Agent service provides a robust and
flexible facility for automating and monitoring SQL
Server 2000 instances


Using jobs, recurring operations can be configured
and scheduled to run automatically by the SQL
Server Agent service


Alerts are a powerful component of the SQL Server
Agent architecture that allow different events and
performance conditions to trigger notifications to
administrative personnel

10

Chapter Summary


Alerts can be based on events that SQL Server 2000
writes to Windows application log or they can be
defined for certain SQL Server 2000 performance
counters


Performance counters are added to the server when
you install SQL Server 2000


These counters can be used in conjunction with the
Windows System Monitor to identify performance
bottlenecks involving the CPU, memory and I/O
subsystems