1 / 20

SQL 710

Week 6: Chapter 6 Agenda. Automation of SQL Server tasks using: SQL Server Agent Scheduling Scripting Technologies. SQL 710. Benefits of Automation. Saves time Reduces chance of human error (forgetting , running wrong task, …)

galena-fletcher
Download Presentation

SQL 710

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. Week 6: Chapter 6 Agenda • Automation of SQL Server tasks using: • SQL Server Agent • Scheduling • Scripting Technologies SQL 710

  2. Benefits of Automation • Saves time • Reduces chance of human error (forgetting , running wrong task, …) • Allows easy execution of tasks in non-peak times or at scheduled times SQL 710

  3. SQL Server Agent • SQL Server’s primary tool for automation and scheduling • A service in the control panel of the SQL Server2000 machine • Configured with Enterprise Manager in Management object • Can automate many tasks using • SQL statements • Operating System Commands • Programming SQL 710

  4. Configuring SQL Server Agent SQL Server Agent must be running at all times. To do this: • Using Service Manager, configure SQL Server Agent to auto start • Using Enterprise Manager, configure SQL Server and SQL Server Agent service to restart automatically if these services stops unexpectedly SQL 710

  5. Configuring SQL Server Agent (ctd) • SQL Server Agent logon Account must be mapped to sysadmin role • Map this account to administrators local group • Use a windows domain user account logon account • Use windows authentication mode for SQL Server Agent to permit greater flexibility SQL 710

  6. SQL Server Agent Objects • SQL Server agent works with following objects: • Jobs – series of tasks • Operators – names and contact info • Alerts – response to conditions (normally for errors) SQL 710

  7. Jobs • A job is a set of instructions, schedules and notifications • Each individual operation is called a step • Step types include Transact-SQL commands, O/S commands, Scripts (ActiveX) • Scripts can use VBScript, JavaScript, etc • Jobs can use conditional logic • Notification methods include email, email pager and Windows NET SEND SQL 710

  8. Create a Job • Ensure that job is enabled (jobs are enabled by default) • Specify the owner who is responsible for performing the job (by default the owner is the logon account creating the job) • Define whether the job is to execute on a local server or on multiple remote servers • Job categories help to organize, filter and manage manyjobs SQL 710

  9. Define job step using T-SQL • Identify database to be used • Include required variable and parameters • Can send a result set of a job step to an output file (output files are often used in troubleshooting to capture an error message that may have occurred while the statement is executing) SQL 710

  10. Define a job step using OS commands • Operating System commands are identified by .exe, .bat, .cmd, .com files extension • Identify a process exit code to indicate that process was successful • Include the full path to the executable application in the command text box. SQL 710

  11. Defining Job Steps • Can use ActiveX Script with languages such as Microsoft VBScript, Microsoft Jscript etc. • Must identify scripting language in which the job step is written • Write or Open Active Script (use the SQL ActiveScriptHost object to print output to the job step history or create object) SQL 710

  12. Job Flow Logic Example JOB 1: Data Transfer Job Step 1: Back Up Database Type: Transact-SQL: Retry attempts: 1 Y SQL 710 Write to Windows Application Log FAIL? N Job Step2: Transfer Data Type: CmdExec; Retry attempts : 2 Y FAIL? Notify Operator N Job Step3: Custom Application Type: Active Scripting: Retry attempts: 0 Y FAIL? N Notify Operator

  13. Job Scheduling • After job has been defined you can schedule the job to be executed automatically • A schedule must be enabled to permit automated execution • Can use Enterprise Manager (GUI) or execute sp_add_jobschedule stored procedure to define job schedule • Job schedules are stored in the msdb.sysjobschedules system tables SQL 710

  14. Job Scheduling (ctd) • Can schedule jobs to start automatically: • When SQL Server Agent started • At a specific time (one time only) • On a recurring basis (daily, weekly, or monthly) • When CPU is idle SQL 710

  15. Notification • Operators are notified of events that take place on SQL Server • Operators don’t require a SQL Server login • Notifications are messages sent to the operators • Notification methods can be: • Email • Email pager • Windows NET SEND command • Operators should be available for contact as required SQL 710

  16. Operators • Use group email alias to notify more than one individual to respond to notification • Test each notification method to ensure that the operator is able to receive messages • Should specify a work schedule for each operator to be notified by pager • Use NET SEND command to send messages to network operators and servers running Windows 2000 or Windows NT. SQL 710

  17. Operator Notification • Ensure operator is available to receive notifications • Ensure messenger service is running on computer of the operator to be notified by NET SEND command • Review most recent notification steps to determine date and time of last notification • Test individual notification methods outside of SQL Server by verifying that you can send e-mail messages, page an operator or successfully execute a NET SEND command. SQL 710

  18. Job History • Job history can be viewed from Enterprise Manager and directly in the msdb..sysjobhistory system table • Sysjobhistory table records: • Date and time each job step occurred • Success or failure of job step • The operator notified and notification method • The duration of job step • Errors or messages from job step SQL 710

  19. Job History Size • By default, msdb database file properties are set to auto growth, and the Truncate log on checkpoint database option is turned on. • By default, maximum job history size is set at 1,000 rows • By default, maximum job history size for each job is set at 100 rows • Rows are removed from sysjobhistory system table in a first-in, first-out (FIFO) manner when size limit is reached SQL 710

  20. Execution Permissions • For T-SQL Jobs: • context of job owner or a specific user • Operating System and ActiveX Script jobs: • Members of Sysadmin role use the SQL Server Agent login account • Job owners that are not members of the sysadmin role use defined domain user account called a proxy account SQL 710

More Related