Transcript 1 Chapter Overview Defining Operators Creating Jobs Configuring

Chapter Overview
•
Defining Operators
•
Creating Jobs
• Configuring Alerts
•
Creating a Database Maintenance Plan
•
Creating Multiserver Jobs
1
Notification Methods
•
E-mail
•
Pager
• NET SEND
2
Operators
3
Job Steps
•
Three types
• Operating system commands
• Transact-SQL statements
• ActiveX scripts
•
Each step can contain only one type of job step.
•
Each job can contain multiple job steps.
4
Permissions and Job
Ownership
•
A job is generally owned by the creator, but it can be
assigned to another user.
•
Jobs operate in the security context of the job
owner, not in the security context of the user
executing the job.
•
By default, only sysadmins can execute operating
system and ActiveX job steps.
•
Non-sysadmins can be given permission to run
operating system and ActiveX job steps in the
security context of a defined proxy account.
5
Multiple Job Steps
and Job Responses
6
Job Scheduling
7
Job Creation
•
Using the Create Job wizard
•
Creating directly in SQL Server Enterprise
Manager
•
Using Transact-SQL
8
Proxy Account
•
Click on the General tab for the SQL Server
Agent Properties dialog box.
• Enable non-sysadmins to execute operating
system and ActiveX scripting jobs.
•
Define a local or domain user account.
9
Job Information and History
•
View status of jobs in the details pane for the
Jobs container.
•
View the properties of a job to review or modify
it.
•
View the job history log for a job to review its
execution history.
• Manage the job history log size as a property of
the SQL Server Agent service.
•
Use the SQL Server Agent error log to view error
information.
10
Error Detection Alerts
11
Performance Condition Alerts
12
Alert Creation
•
Using the Create Alert wizard
•
Creating directly in SQL Server Enterprise
Manager
•
Using Transact-SQL
•
Using user-defined error messages
13
Alert Information
and History
•
View status of alerts in the details pane for
Alerts container.
• Refresh Alerts container to view most recent
alert information.
•
When troubleshooting, verify that an alert is
enabled.
•
View the properties of an alert to review or
modify it.
14
Database Maintenance
Plan Wizard
•
Rebuild indexes, and update distributions
statistics.
• Automatically shrink a database.
•
Perform DBCC consistency checks.
•
Back up database and transaction log files.
•
Configure log shipping.
15
Maintenance Plan
Information and History
•
View status of maintenance plans in the
details pane.
•
Refresh the Database Maintenance Plan
container.
•
When troubleshooting, verify that the
maintenance plan is enabled.
•
View the history log of a maintenance plan,
including each component.
•
View the properties of a maintenance plan.
16
Multiserver Job Architecture
17
Master and Target Server
Creation
•
Select a server that can handle the performance
hit for the master.
• Configure it as the master, and designate an
MSXOperator.
•
Enlist target servers–they must be registered
with SQL Server Enterprise Manager.
18
Job Creation for Target
Servers
•
Use same process as for creating local jobs, but
designate target servers.
• Jobs are downloaded by target servers as readonly jobs.
•
Database and file system paths must be valid on
each target server.
19
Target Server Information
and Job History
•
View the status of a target server, including
pending instructions.
• Force download of instructions immediately.
•
Start jobs on target servers, change polling
interval, or synchronize clocks.
• View information by server or by target job.
•
View job history log on any target server.
20
Chapter Summary
•
•
Define users and groups to act as operators.
•
Define event and performance condition alerts, and
specify notifications and jobs.
•
Use Database Maintenance Plan wizard to
automate tasks.
•
Create multiserver jobs to centrally manage jobs.
Create simple or complex jobs to perform tasks,
and specify notifications.
21