1 / 28

SQL Server Agent

SQL Server Agent. Keith Binford. SQL Server Agent. SQL Server Agent is a Windows service that can execute and schedule tasks and jobs. SQL Server Agent Configuration Manager. SQL Server Agent Configuration Manager. Configuring SQL Server Agent Properties.

anahid
Download Presentation

SQL Server Agent

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. SQL Server Agent Keith Binford

  2. SQL Server Agent SQL Server Agent is a Windows service that can execute and schedule tasks and jobs.

  3. SQL Server Agent Configuration Manager

  4. SQL Server Agent Configuration Manager

  5. Configuring SQL Server Agent Properties • Executing jobs based on resource availability • Idle CPU Condition • When to restart SQL Server Agent • Auto restart SQL Server (Recommended) • Auto restart SQL Server Agent (Recommended) • How much information to persist in the error log • Include Execution Trace Messages(check disk space) • Alerts and notifications

  6. Configuring SQL Server Agent Properties

  7. Configuring SQL Server Agent Properties

  8. Configuring SQL Server Agent Properties

  9. SQL Server Agent • Job • Schedule • Operator • Alert • Proxy

  10. Configuring Jobs A job is a specified series of operations performed sequentially by SQL Server Agent. • A job can perform a wide range of activities, including running Transact-SQL scripts, command-line applications, Microsoft ActiveX scripts, Integration Services packages, Analysis Services commands and queries, or Replication tasks. • Jobs can run repetitive tasks or those that can be scheduled, and they can automatically notify users of job status by generating alerts, thereby greatly simplifying SQL Server administration. *

  11. SQL Server Agent • SQL Server Integration Services (SSIS) packages • T-SQL scripts • PowerShell scripts • ActiveX scripts • Replication tasks • Analysis Services tasks • Operating system task (CmdExec)

  12. Configuring Jobs

  13. Configuring Jobs USE msdb ; GO EXEC dbo.sp_add_job @job_name = N'Weekly Sales Data Backup' ; GO EXEC sp_add_jobstep @job_name = N'Weekly Sales Data Backup', @step_name = N'Set database to read only', @subsystem = N'TSQL', @command = N'ALTER DATABASE SALES SET READ_ONLY', @retry_attempts = 5, @retry_interval = 5 ; GO EXEC dbo.sp_add_schedule @schedule_name = N'RunOnce', @freq_type = 1, @active_start_time = 233000 ; USE msdb ; GO EXEC sp_attach_schedule @job_name = N'Weekly Sales Data Backup', @schedule_name = N'RunOnce'; GO EXEC dbo.sp_add_jobserver @job_name = N'Weekly Sales Data Backup'; GO

  14. Creating Operators An Operator is the person to be notified when a certain action or event occurs on SQL Server or SQL Server Agent.

  15. Creating Operators

  16. Creating Operators • Only members of the sysadmin fixed server role can create operators. • SQLAgentUserRole • SQLAgentReaderRole • SQLAgentOperatorRole • Note that SQL Server Agent must be configured to use Database Mail to send e-mail and pager notifications to operators.  • The Pager and net send options will be removed from SQL Server Agent in a future version of Microsoft SQL Server. Avoid using these features in new development work, and plan to modify applications that currently use these features.

  17. Creating Operators -- sets up the operator information for user 'danwi.' The operator is enabled. -- SQL Server Agent sends notifications by pager from Monday through Friday from 8 A.M. to 5 P.M. USE msdb ; GO EXEC dbo.sp_add_operator @name = N'Dan Wilson', @enabled = 1, @email_address = N'danwi', @pager_address = N'5551290AW @pager.Adventure-Works.com', @weekday_pager_start_time = 080000, @weekday_pager_end_time = 170000, @pager_days = 62 ; GO

  18. Creating Alerts Alerts are automated notifications that are fired when certain events are triggered. • SQL Server event • SQL Server performance conditions • Windows Management Instrumentation(WMI) event Windows Management Instrumentation (WMI) is the infrastructure for management data and operations on Windows-based operating systems. You can write WMI scripts or applications to automate administrative tasks on remote computers but WMI also supplies management data to other parts of the operating system and products, for example System Center Operations Manager, formerly Microsoft Operations Manager (MOM), or Windows Remote Management (WinRM).

  19. Creating Alerts

  20. Creating Alerts USE msdb EXEC sp_add_alert @name = 'Test Alert', @message_id = 55001, @severity = 0, @notification_message = 'Error 55001 has occurred. The database will be backed up...', @job_name = 'Back up the Customer Database'

  21. Creating Alerts -- adds an e-mail notification for the specified alert (Test Alert) -- This example assumes that Test Alert already exists and that François Ajenstat is a valid operator name. USE msdb ; GO EXEC dbo.sp_add_notification @alert_name = N'Test Alert', @operator_name = N'FrançoisAjenstat', @notification_method = 1 ; GO

  22. Creating Proxies A SQL Server Agent proxy defines the security context for a job step. • A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. Each proxy can be associated with one or more subsystems. • A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.

  23. Creating Credentials • Credentials provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server. • Credentials can also be used when a SQL Server Authentication user needs access to a domain resource, such as a file location to store a backup • A credential can be mapped to several SQL Server logins at the same time. A SQL Server login can only be mapped to one credential at a time.

  24. Creating Credentials

  25. Creating Proxies

  26. Creating Proxies sp_add_proxy [ @proxy_name = ] 'proxy_name' , [ @enabled = ] is_enabled , [ @description = ] 'description' , [ @credential_name = ] 'credential_name' , [ @credential_id = ] credential_id , [ @proxy_id = ] id OUTPUT

  27. References Microsoft SQL Server 2012 by Patrick LeBlanc www.TechNet.com http://technet.microsoft.com/en-us/sqlserver/bb265254

  28. END

More Related