Module 13 Automating SQL Server 2008 R2 Management

1. Module 13 Automating SQL Server 2008 R2 Management

2. Module Overview • Automating SQL Server Management • Working with SQL Server Agent • Managing SQL Server Agent Jobs

3. Lesson 1: Automating SQL Server Management • Benefits of Automating SQL Server Management • Available Options for Automating SQL Server Management • Overview of SQL Server Agent • Demonstration 1A: SQL Server Agent

4. Benefits of Automating SQL Server Management • Reduced administrative workload • Automate and Schedule regular tasks • Proactive management • Monitor Performance • Recognize and respond to potential problems

5. Available Options for Automating SQL Server Management Jobs to perform regular tasks Alerts to respond to Events Operators to send Notificationsfrom Jobs and Alerts

6. Overview of SQL Server Agent • Runs as a Windows service • Must be running to • Execute jobs • Fire alerts • Contact operators • Start Mode should be set to Automatic SQL Server Agent is the component of SQL Server that is responsible for automation

7. Demonstration 1A: SQL Server Agent • In this demonstration, you will see: • How to configure SQL Server Agent • How to review SQL Server Agent jobs using PowerShell

8. Lesson 2: Working with SQL Server Agent • Defining Jobs, Job Step Types and Job Categories • Creating Job Steps • Scheduling Jobs for Execution • Scripting Jobs • Demonstration 2A: Scripting Jobs

9. Defining Jobs, Job Step Types and Job Categories SQL Server Agent Jobs are a specified series of operations performed sequentially by SQL Server Agent • Jobs support many types including: • Transact-SQL • Commandline application • PowerShell script execution • Jobs can be configured to: • Run once or repeatedly • Start at SQL Server Agent start-up • Start manually • Jobs can be assigned to a Category

10. Creating Job Steps Yes Job step 1: Back up Database (Transact-SQL) Fail? Write to Application Log No Yes Job step 2: Transfer Data (CmdExec) Fail? Notify operator No Yes Job step 3: Custom Application (PowerShell) Fail the job Fail? No Job succeeded Notify operator

11. Scheduling Jobs for Execution Schedule: Mon-Sun Shift 1 Schedule: Mon-Sun Shift 2 Every 1 Hours From: 8:00 A.M.To: 5:00 P.M. Every 4Hours From: 5:01 P.M.To: 7:59 A.M. Job: Backup Transaction Log Daily Schedule Daily Schedule • More than one schedule can be defined per job • Schedules can be reused in other jobs

12. Scripting Jobs • Jobs can be scripted for documentation, archiving, and deployment on other instances

13. Demonstration 2A: Scripting Jobs • In this demonstration you will see how to: • Create a job in SSMS • Script a Job • Deploy a job on a second instance of SQL Server

14. Lesson 3: Managing SQL Server Agent Jobs • Viewing Job History • Querying SQL Server Agent-related System Tables and Views • Troubleshooting Failed Jobs • Demonstration 3A: Viewing Job History and Resolving Failed Jobs

15. Viewing Job History • SQL Server Agent keeps history information in msdb • Job history can be queried directly or viewed through SSMS • Job Activity monitor shows current live information • Retention of history can be configured based on time or size

16. Querying SQL Server Agent-related System Tables and Views • SQL Server Agent keeps configuration and history in msdb • System tables for Agent are located in the dbo schema • Tables are documented in Books Online • Use history tables to automate collection of job history over several systems SELECT j.name, jh.run_date,jh.run_time,jh.message FROMmsdb.dbo.sysjobhistory AS jh INNERJOINmsdb.dbo.sysjobs AS j ONjh.job_id = j.job_id WHEREjh.step_id = 0; GO;

17. Troubleshooting Failed Jobs • If SQL Server Agent is not running • Check settings of the service • Check the msdb database • Review job history • Check job outcome to identify the last step that was executed • Check job step outcome to identify why the step failed • If the job did not start, verify: • Job is enabled • Job is scheduled • Schedule is enabled • Verify that all dependent objects such as databases, files, procedures are available • Check that security settings allow access to dependent objects

18. Demonstration 3A: Viewing Job History and Resolving Failed Jobs • In this demonstration, you will see: • A job that has failed to execute correctly • How to review the job history using SSMS • How to review the job history using T-SQL

19. Lab 13: Automating SQL Server Management • Exercise 1: Create a Data Extraction Job • Exercise 2: Schedule the Data Extraction Job • Challenge Exercise 3: Troubleshoot a Failing Job (Only if time permits) Logon information Estimated time: 45minutes

20. Lab Scenario There are a number of routine tasks to be performed on the Proseware instance. Previously these tasks have been performed manually and the lack of consistency in performing these tasks has caused issues for the organization. On the new instance, you need to automate these tasks using SQL Server Agent. There is also a report about an existing SQL Server Agent job that is not performing as expected. If you have time, you need to resolve the issues with the job.

21. Lab Review • How do you configure SQL Server Agent to notify you that a particular job has failed? • Can one job include different types of job steps?

22. Module Review and Takeaways • Review Questions • Best Practices