1 / 55

Chapter Ten

Chapter Ten. Automating and Monitoring SQL Server 2000. 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

doyle
Download Presentation

Chapter Ten

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter Ten Automating and Monitoring SQL Server 2000

  2. 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

  3. 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

  4. 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

  5. Jobs • Jobs • Administrative tasks defined once and executed as many times as necessary Figure 10-1: SQL Server Agent architecture

  6. 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

  7. 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

  8. 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

  9. 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

  11. 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

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

  13. 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

  14. 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

  15. Creating and ConfiguringJobs, 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

  16. Creating and ConfiguringJobs, Alerts and Operators Figure 10-8: Notification tab of the New Operator Properties window

  17. 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

  18. 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

  19. 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

  20. 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

  21. Viewing and Deleting Existing Operators in T-SQL Figure 10-9: Results of the sp_help_operator system-stored procedure

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

  23. 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

  24. Managing Job Steps Figure 10-12: General tab of the New Job Step window

  25. 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

  26. Managing Job Steps Figure 10-13: Sample information in the Edit Job Step window

  27. Managing Job Steps Figure 10-14: Advanced tab of the New Job Step window

  28. Scheduling Jobs Figure 10-15: Schedules tab of the New Job Properties window

  29. Scheduling Jobs Figure 10-16: New Job Schedule window

  30. 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

  31. Configuring Job Notifications Figure 10-17: Notification tab of the New Job Properties window

  32. 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

  33. 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

  34. 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

  35. 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

  36. Creating Alerts Figure 10-18: New Alert window

  37. 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

  38. SQL Server Event Alerts Table 10-3: SQL Server error security levels

  39. SQL Server Event Alerts Figure 10-19: Creating a SQL Server event alert

  40. 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

  41. 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

  42. Performance Condition Alerts Figure 10-21: Creating a SQL Server performance condition alert

  43. 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

  44. 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

  45. Windows Performance Monitor and SQL Server 2000 Figure 10-22: Windows System Monitor

  46. Windows Performance Monitor and SQL Server 2000 Figure 10-23: Add Counter window of Windows System Monitor

  47. Windows Performance Monitor and SQL Server 2000 Figure 10-24: Windows System Monitor tracking multiple counters

  48. 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

  49. 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

  50. 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

More Related