SQL-Server-2005数据库实践教程-自动化管理任务

1第六章.自动化管理任务SQLServer2005数据库实践教程

——管理与维护篇2自动化管理任务概述配置代理效劳器管理作业与警报，设置作业与警报36-1自动化管理任务概述了解自动化管理任务的根本概念，学习自动化管理任务的优点及组件掌握配置代理效劳器技术46-1自动化管理任务概述1、自动化管理任务的概念所谓自动化管理任务是指系统可以根据预先的设置自动地完成某些任务和操作。2、自动化管理任务的分类一般地，把可以自动完成的任务分成两大类：一类是执行正常调度的任务；另一类是识别和回应可能遇到的问题的任务。3、正常调度的任务：所谓的执行正常调度的任务，就如同在MicrosoftSQLServer系统中执行一些日常维护和管理的任务，可以包括备份数据库、传输和转换数据、维护索引、维护数据一致性等。56-1自动化管理任务概述6-1-1自动化管理任务的优点自动化管理任务的主要优点表现在：减少了管理方面的工作负荷，使得DBA将精力集中在其他作业任务上，例如规划数据库的结构或者优化数据库的性能。降低因无视重要维护任务而导致的风险。降低在执行数据库维护任务时人为错误的风险。通过警报进行主动管理，自动化的阻止一些可能问题的发生。66-1自动化管理任务概述6-1-2自动化管理的组件自动化组件包括：WindowsEventLog、MSSQLServer和SQLServer代理。

MSSQLServer效劳是MicrosoftSQLServer系统的数据库引擎，负责把发生的错误作为事件写入Windows的应用程序日志中。如果MicrosoftSQLServer系统或应用程序发生了需要引起用户注意的任何错误或消息，且把这些错误或消息写进了Windows的应用程序日志，那么这些错误或消息就是日志。WindowsEventLog效劳负责处理写入Windows的应用程序日志中的事件，这些事件可以包括：MicrosoftSQLServer系统中严重等级在19~25之间的任何错误；已经定义将要写入Windows的应用程序日志中的错误消息；执行RAISERRORWITHLOG语句。当SQLServer代理效劳启动时，它就在Windows的事件日志中注册并且连接到MicrosoftSQLServer，这样就允许SQLServer代理效劳接受任何MicrosoftSQLServer的事件通知。76-1自动化管理任务概述6-1-3SQLSERVER代理1、作业的概念SQLSERVER代理〔SQLServerAgent〕说到底就是一个Windows的后台效劳和可以执行安排的管理任务，这个管理任务也被称为“作业”。每个作业包含了一个或多个作业步骤，每个步骤都可以完成一个任务。SQLSERVER代理可以在指定的时间或在特定的事件条件下执行作业里的步骤，并记录作业的完成情况，一旦执行作业步骤出现错误，SQLServer代理还可以设法通知管理员。86-1自动化管理任务概述6-1-3SQLSERVER代理实验1：设置SQLServer代理效劳为自动启动实验第一步：翻开SQLServerConfigurationManager，鼠标右键选中其中的“SQLServer2005Agent”效劳，翻开快捷菜单中的“属性”对话框。如下图96-1自动化管理任务概述6-1-3SQLSERVER代理实验1：设置SQLServer代理效劳为自动启动实验第二步：在翻开的“属性”对话框中切换到代理效劳器的“效劳”页面，选择启动模式为“自动”。如下图。当然也可以通过点击开始->运行，然后键入services.msc，运行后找到SQLServer2005Agent，双击设置为自动启动。〔请读者自行尝试〕106-1自动化管理任务概述6-1-3SQLSERVER代理实验2：设置SQLServer代理效劳始终运行实验第一步：在SQLServerManagementStudio中，用鼠标右键选中翻开实例中的“SQLServer代理”，翻开快捷菜单中的“属性”对话框，选择常规选项卡，如下图。116-1自动化管理任务概述6-1-3SQLSERVER代理实验2：设置SQLServer代理效劳始终运行实验第二步：选中复选框“SQLServer意外停止时自动重新启动”和“SQLServer代理意外停止时自动重新启动”，将确保SQLServer和SQLServer代理意外停止后可以自动启动。如下图。126-2管理作业与警报

了解操作员的概念掌握创立作业的根本步骤掌握如何创立警报的步骤学习通过T-SQL创立作业和操作员，以及创立自动化综合任务136-2管理作业与警报

6-2-1操作员操作员是在完成作业或者出现警报时候可以接收消息的人员或者组的别名，通常应当在定义警报之前定义操作员。作业在完成或者失败时候，可以通过电子邮件、网络消息和寻呼机方式通知操作员。146-2管理作业与警报

6-2-1操作员实验1：创立操作员实验第一步：在SQLServerManagementStudio中，用鼠标右键点击翻开实例中“SQLServer代理”里面的“操作员”，翻开快捷菜单中的“新建操作员”对话框。如下图。156-2管理作业与警报

6-2-1操作员实验1：创立操作员实验第二步：分别在翻开的创立操作员用户界面中录入操作员名称和电子邮件的名称，但是这里的电子邮件有效方式是在建立电子邮件效劳器后指定的用户邮件地址，因此首先应当确保电子邮件效劳器是已经配置好并成功启动的，而且应当为具体的用户设置好邮件地址。166-2管理作业与警报

6-2-2创立作业的根本步骤一般我们都是通过SQLServerManagementStudio创立作业，作业步骤的定义被存储在msdb数据库的sysjobsteps系统表中。作业的执行内容可以包括：TSQL脚本，操作系统命令，ActiveX脚本，复制任务，磁盘备份任务等。176-2管理作业与警报

6-2-2创立作业的根本步骤每个具体的作业只能够是以下一种执行类型：1、TSQL脚本TSQL脚本可以包括Transact-SQL语句和存储过程，但必须指定具体的操作数据库，详细的操作参数和变量，以及具体操作的磁盘文件等，而且上述的各项内容必须是真实存在的。2.操作系统命令操作系统命令就是Windows操作系统的可执行命令，包括：.exe、.bat.、d、后缀文件，而且需要制定这些文件的完整磁盘路径，以及执行命令的退出命令，以指示命令成功完成。3.ActiveXActiveX是一个翻开的集成平台，为开发人员、用户和Web生产商提供了一个快速而简便的在Internet和Intranet创立程序集成和内容的方法。使用ActiveX,可轻松方便的在Web页中插入多媒体效果、交互式对象、以及复杂程序。186-2管理作业与警报

6-2-2创立作业的根本步骤实验1：创立简单作业实验第一步：在SQLServerManagementStudio中，用鼠标右键点击翻开实例中“SQLServer代理”里面的“作业”，翻开快捷菜单中的“新建作业”对话框。196-2管理作业与警报

6-2-2创立作业的根本步骤实验1：创立简单作业实验第二步：在作业属性对话框的常规选项页中，分别在名称中命名新作业为“BackUpDatabase1”，该作业说明为“备份school数据库”

206-2管理作业与警报

6-2-2创立作业的根本步骤实验1：创立简单作业实验第三步：选择“步骤”选项页，“新建”作业步骤，在新建作业页面命名步骤名称“backupstep1”，选择数据库为school，类型为T-SQL脚本，并录入命令脚本“backupdatabaseschooltoDISK='c:\schoolbackup.bak'withnoinit”，点击分析后成功建立步骤216-2管理作业与警报

6-2-2创立作业的根本步骤实验1：创立简单作业实验第三步：在作业步骤属性的选择页中点击“高级”项，并在操作界面将“成功时要执行的操作”选项选择为“退出报告成功的作业”，并将输出追加到“输出文件”中，点击确定保存配置信息226-2管理作业与警报

6-2-2创立作业的根本步骤实验1：创立简单作业实验第四步：选择“方案”选项页，“新建”作业方案，在新建作业方案页面命名作业方案名称为“backupschoolplan”，执行频率为每天，每天频率为每分钟〔主要为了观察实验方便〕，点击确定后建立新的方案。最后回到建立方案界面，完成建立方案的工作。236-2管理作业与警报

6-2-3创立警报1、SQLServer允许创立警报以显示系统可能遇到的各种错误，包括SQLServer错误、用户定义错误或者对系统的性能条件等做出必要的响应。2、警报是联系写入Windows事件日志中的MicrosoftSQLServer错误消息和执行作业或发送通知的桥梁3、在MicrosoftSQLServer系统中，错误代号小于或等于50000的错误或消息是系统提供的错误使用的代号，用户定义的错误代号必须大于500004、错误等级也是错误是否触发警报的一种条件。在MicrosoftSQLServer系统中，提供了25个等级的错误。在这些错误等级中，19~25等级的错误自动写入Windows的应用程序日志中，这些错误是致命错误。以上的各种错误警报的具体含义，读者可以通过执行查询语句“select*fromsysmessages”具体进行查看。246-2管理作业与警报

6-2-3创立警报5、警报与作业不同之处在于，作业是由SQLServer代理效劳来掌控的，在什么时间做什么事情都是我们预订好的。我们能意识到将要处理的事情是什么样的结果，但是警报不是，警报是在出现意外的情况下应该怎么去做SQLServer定义警报的方式主要有三种：1.根据SQLServer错误定义警报假设要创立SQLServer错误时候发出的警报，可以通过指定一个错误编号〔如9002：数据库的事务日志已满。〕或特定的严重程度〔如17〕来定义警报。2.根据SQLServer性能条件定义警报除了使用警报响应SQLServer错误以外，还可以使用警报响应SQLServer的性能条件〔如“Windows系统监视器”上查看到的性能条件〕。当超过某个激发条件时候，将激发警报。3.根据WMI事件定义警报WMI是一项核心的Windows管理技术，WMI作为一种标准和根底结构，通过它可以访问、配置、管理和监视几乎所有的Windows资源256-2管理作业与警报

6-2-3创立警报实验1：创立简单警报实验第一步：在SQLServerManagementStudio中，用鼠标右键点击翻开实例中“SQLServer代理”里面的“警报”，翻开快捷菜单中的“新建警报”对话框。如下图。266-2管理作业与警报

6-2-3创立警报实验1：创立简单警报实验第二步：在弹出的“新建警报”对话框的常规选项页中，分别录入警报名，选择警报类型和时间警报定义的数据库，以及根据哪些条件发出警报。276-2管理作业与警报

6-2-3创立警报实验1：创立简单警报实验第三步：在弹出的“新建警报”对话框的响应选项页中，勾选“执行作业”，通过下拉列表选择执行作业〔如果没有那么点击“新建作业”完成〕；勾选“通知操作员”，并选择上节中定义的操作员，通知的方式选择为“电子邮件”。如图6-15所示。最后点击确定，完成对警报的创立工作286-2管理作业与警报

6-2-4通过T-SQL实现自动化管理任务1.创立作业创立作业的系统存储过程是sp_add_job，其根本的语法格式如下：sp_add_job

[@job_name=]'job_name'

[,[@enabled=]enabled]

[,[@owner_login_name=]'login']

[,[@notify_level_eventlog=]eventlog_level]

[,[@notify_level_email=]email_level]

[,[@notify_level_netsend=]netsend_level]

[,[@notify_level_page=]page_level]

[,[@job_id=]job_idOUTPUT]常用参数的含义为：@JOB_NAME作业名称@JOB_IDOUTPUT该作业的ID号,是uniqueidentifer类型的输出变量@ENABLE=1或0是否处于启用状态@OWNER_LOGIN_NAME可登录的用户名称@NOTIFY_LEVEL_EVENTLOG将运行状态放入WINDOWS的应用程序日志.@notify_level_email用于指明作业完成后何时发送电子邮件的值@notify_level_netsend用于指明作业完成后何时发送电子邮件的值，0从不，1成功后，2〔默认值〕失败后，3始终@notify_level_page用于指明作业完成后何时发送呼叫的值，0从不，1成功后，2〔默认值〕失败后，3始终296-2管理作业与警报

6-2-4通过T-SQL实现自动化管理任务实验1：T-SQL创立作业实验EXECMSDB.dbo.SP_ADD_JOB--在MSDB系统数据库中以dbo身份执行SP_ADD_JOB存储过程@JOB_NAME='school_back',--命名作业名称为school_back@ENABLED=1,--是处于启用状态@OWNER_LOGIN_NAME='832dfe13c0ea4b5\ADMINISTRATOR',--可登录的用户名称是本机的ADMINISTRATOR用户@NOTIFY_LEVEL_NETSEND=3,--用于指明作业完成后何时发送电子邮件的值，3表示为始终发送@NOTIFY_NETSEND_OPERATOR_NAME='ADAdmin01',--完成该作业后，接收网络消息的操作员的名称。@NOTIFY_LEVEL_EVENTLOG=3--用于指示何时将该作业的项放入MicrosoftWindowsNT应用程序日志，3表示为始终发送--注意：如果没有操作员.需要预先定义一个操作员ADAdmin01，查询操作员命令如下306-2管理作业与警报

6-2-4通过T-SQL实现自动化管理任务2.创立操作员创立操作员的系统存储过程是SP_ADD_OPERATOR，其根本的语法格式如下：sp_add_operator[@name=]'name'

[,[@enabled=]enabled]

[,[@email_address=]'email_address']

[,[@pager_address=]'pager_address']

[,[@weekday_pager_start_time=]weekday_pager_start_time]

[,[@weekday_pager_end_time=]weekday_pager_end_time]

[,[@pager_days=]pager_days]

[,[@netsend_address=]'netsend_address']常用参数的含义为：@NAME操作员名称@ENABLED是否启用操作员@EMAIL_ADDRESS操作员的邮件地址@PAGER_ADDRESS操作员的寻呼地址@NETSEND_ADDRESS操作员的网络地址@weekday_pager_start_time效劳在工作日〔星期一到星期五〕开始时间@weekday_pager_END_time效劳在工作日〔星期一到星期五〕结束时间@PAGER_DAYS=62表示操作员可以接受呼叫的日期，参数值描述为：1星期日，2星期一，4星期二，8星期三，16星期四，32星期五，64星期六316-2管理作业与警报

6-2-4通过T-SQL实现自动化管理任务实验1：T-SQL创立操作员实验--例如，定义一个操作员为op_qianshao1，只使用网络地址接收消息EXECMSDB..SP_ADD_OPERATOR@NAME='op_qianshao1',@NETSEND_ADDRESS='192.168.1.101'--此例如设置qianshao1的操作员信息。--再创立一个启用操作员信息op_qianshao2，呼叫程序将从星期一到星期五的上午8点到下午5点通知op_qianshao2。usemsdbexecsp_add_operator@name='op_qianshao2',--操作员名称@enabled=1,--操作员名称@email_address='janetl',--启用操作员@pager_address='qianshao@bjjtxy.bj',--操作员的寻呼地址@weekday_pager_start_time=080000,--效劳在工作日〔星期一到星期五〕开始时间@weekday_pager_end_time=170000,--效劳在工作日〔星期一到星期五〕结束时间@pager_days=62--周一到周五326-2管理作业与警报

6-2-4通过T-SQL实现自动化管理任务3.创立警报创立警报的系统存储过程是sp_add_alert，其根本的语法格式如下：sp_add_alert[@name=]'name'

[,[@severity=]severity]

[,[@enabled=]enabled]

[,[@notification_message=]'notification_message']

[,{[@job_id=]job_id|[@job_name=]'job_name'}]常用参数的含义为：@NAME警报名称@SEVERITY用于定义警报的严重级别(1-25)@ENABLED警报的当前状态@NOTIFICATION_MESSAGE附加消息@JOB_NAME该警报所执行的作业名称@JOB_ID该警报所执行的作业ID与作业名称只能有一个336-2管理作业与警报

6-2-4通过T-SQL实现自动化管理任务实验1：T-SQL创立警报实验MSDB..SP_ADD_ALERT@NAME='ALT2',@SEVERITY=15--定义警报名称为ALT2，严重级别为15MSDB..SP_DELETE_ALERT@NAME='ALT2'--删除警报ALT2346-2管理作业与警报

6-2-4通过T-SQL实现自动化管理任务4.创立步骤创立步骤的系统存储过程是SP_ADD_JOBSTEP，其根本的语法格式如下：sp_add_jobstep[@job_id=]job_id|[@job_name=]'job_name'

[,[@step_id=]step_id]

{,[@step_name=]'step_name'}

[,[@subsystem=]'subsystem']

[,[@command=]'command']

[,[@on_success_action=]success_action]

[,[@on_fail_action=]fail_action]常用参数的含义为：@JOB_ID作业ID@JOB_NAME作业名称@STEP_NAME步骤的名称@SUBSYSTEM此方案所适合的类型@COMMAND此方案所使用的命令@ON_SUCCESS_ACTION成功时的操作@ON_FAIL_ACTION失败时的操作，值参数描述〔操作〕：1成功后退出，为成功默认值；2失败后退出，失败默认值；3转到下一步，4转到步骤on_success_step_id或是on_fail_step_id356-2管理作业与警报

6-2-4通过T-SQL实现自动化管理任务实验1：创立作业调度实验第一步：在SQLServerManagementStudio中，展开树形目录中的SQLServer代理项，用鼠标右键点击“作业”项〔如下图〕。并在翻开的“新建作业”窗口中，填写新作业的名称为“BackupDatabase”，所有者为“Administrator”，类别为“未分类”，如