SQL Server 2005 实现自动化管理.doc

SQL Server 2005 实现自动化管理作为一个数据库系统管理员，为了系统能安全稳定高效地运行，必须要时常对数据库进行维护，优化管理，在数据库比较多的情况下，这种维护工作会变得负重不堪。还好SQL SERVER 2005提供了十分实用的自动化管理，一些日常的维护优化工作，可以让SQL SERVER2005自己完成任务，大大减低了管理员的负担。SQL SERVER的自动化管理其实我们也不陌生，在前面介绍过的自动备份，自制调度等，都属于SQL SERVER的自动化管理范围。我们今天就围绕着代理服务来看一下相关的问题：1 SQL SERVER代理服务概述2 SQL SERVER2005代理服务安全性 主要是与2000相比安全性增强了。3 SQL SERVER自动管理应用场景 SQL SERVER代理：SQL SERVER2005 允许自动执行管理任务。若要让SQL SERVER 2005自动执行管理任务，就必须要先定义好管理任务的内容，然后指定执行该任务的时间或条件，由SQL SERVER2005自动执行。那么如何自动执行，这里必须要使用的就是SQL SERVER代理SQL SERVER代理简介：是一个WINDOWS的后台服务，可以执行安排的管理任务，这个管理任务又叫做“作业”。每个作业包含了一个或多个作业步骤，每个步骤都可以完成一个任务。SQL SERVER代理可以在指定的时间或在特定的事件条件下执行作业里的步骤，并记录作业的完成情况，一旦执行作业步骤出现错误，SQL SERVER代理还可以设法通知管理员。SQL SERVER代理可以完成的工作一般可以有以下几种：作业警报操作员如果想完成这三个操作的话，那么他们所依托的就是SQL SERVER代理服务。AGENT服务。由于SQL SERVER代理是一个服务，所以如果要让SQL SERVER代理运行作业，处理警报，就必须先要启动SQL SERVER代理服务器，这样作业才会自动运行。这个服务在安装完SQL SERVER时自动安装，但默认情况下没有启动。启动和停止的方法：参考SQL Server 2005系列教学(2) SQL 服务及创建数据库SQL SERVER代理的设置：下面我们对SQL SERVER的代理服务进行设置：我们下面对作业，警报，操作员进行简单的设置和介绍这里与SQL SERVER2000不同。SQL SERVER2000里如果这个服务没有启动，我们也可以编辑里面的内容，但是如果SQL SERVER2005中，此服务没有启动的话则无法编辑里面的东西。这三个功能主要是完成什么工作。这三个东西在我们的SQL SERVER中还是占据了很大的地位的。如我们想定时备份，在每一周的周日的夜里二点进行完全备份。这就是一个作业，当然作业不仅仅说是能进行一个完全备份，只要是能用SQL 语句写出来的，那么都可以实现自动执行。下面我们来从流程上看看作业是如何执行的：我们希望通过作业对数据库进行一个定期的备份操作。我们先来创建一个数据库：创建一个TESTJOB数据库新建一个作业：我们发现这个界面比2000的界面要友好了很多，直观了很多，常规，步骤，计划。一目了然，按照这个流程来就行了。新建作业- 这个界面很简单我们只需要写一个名称：以某个用户身份运行即可！下面的步骤很重要：也就是完成这个作业，这个任务所要经过哪些步骤：那么每一个步骤就是一个SQL语句那么我们开始写一个步骤：这时候我们会看到我们已经成功的创建了第一步。这时候还可以再创建第二步，第三步。我们还可以指定从哪一步开始。还可以从高级中指定如上图再往下就是计划：也就是什么时候执行这个步骤：我们来看一下我们用到最多的一种就是重复执行如果选择CPU空间闲时或启动时是灰色的。常用的就是重复执行，可以定义每天，每几天，几点，几分。包括每周每月。每几个月，星期几。如每个月的第二个星期日。从什么时候到什么时候如三个月以内我先这么定义，三个月以后再说。这是第三个元素计划，再向下就是警报，警报是个可选的，不是必须的，也就是说可以不定义。那如果定义的话，我们来看看应该怎么做？警报与作业不同：作业是由AGENT服务来掌控，在什么时间做什么事情，也就是说我们所要做什么事情都是我们预订好的。我们能意识到将要处理的事情是什么样的结果，但是警报不是，警报是在出现意外的情况下应该怎么去做，我们首先来增加一个警报：类型：SQL SERVER事件警报：这是最常用的一种警报方式性能条件警报：要为其指定性能条件，计数器，实例以及计数器满足什么条件才触发警报。WMI事件警报：是和编程相关的，WMI就是Windows Management Instrumentation（Windows管理规范）。它是Windows中的一个核心管理技术。WMI为访问大量的Windows管理数据和方法的提供了一个统一的机制。WMI通过脚本、C+程序接口、.NET类（系统管理）和命令行工具（WMIC）提供了对这个信息的访问。WMI的功能还包括事件、远程、查询、查看、计划和实施用户扩展及更多内容。警报可以针对所有数据库有效，监控所有数据库什么情况下激活警报：严重性如 15那么还有一个问题就是如果产生了警报应该怎么办？也就是说不可能说警报出来就不管他了，也就是说警报出来，就说明问题出现了，那怎么办，就要定义响应。也就是说警报出来了，我们应该做什么？那么在这里可以执行某个作业或是通知某个操作员。注意这个操作员只是存在于SQL SERVER中，和WINDOWS没有什么关系那么我们来定义一个操作员可以给用户发一封邮件，但是注意现在这么配肯定是不行的，因为它不知道你如何给对方发信，你既然想发信就必须要有一个可以利用的SMTP服务器。现在没有，SQL SERVER本身是没有发邮件的功能的，那么就必须要邮件服务器结合，如EXCHANGE还可以指定个NET SEND 地址或是NETBIOS名称，自动给用户发一个消息，可以结合邮件使用，因为发一个邮件，可能这个数据库管理员不能及时的打开邮箱看到信件，那么可以结合此项，给用户发一个消息。及时提醒用户。但也有一个弊端就是只能应用于局域网，所以如果是外网的话可以结合寻呼程序使用。在一些长假的时候可以定义一些操作员。及时通知。再向下就是通知：就是当作业完成或是失败的时候可以及时通知我们刚刚定义的操作员。我们继续向下看，我们来看一下如何使用T-SQL语句来实现我们刚才的操作：我把几个相关的命令给大家介绍一下：然后我们来看一个综合的例子：创建作业： sp_add_job 常用参数:JOB_NAME 作业名称JOB_ID OUTPUT 该作业的ID号,是 uniqueidentifer 类型的输出变量ENABLE=1或0 是否处于启用状态OWNER_LOGIN_NAME 可登录的用户名称NOTIFY_LEVEL_EVENTLOG 将运行状态放入WINDOWS的应用程序日志.notify_level_email 用于指明作业完成后何时发送电子邮件的值notify_level_netsend 用于指明作业完成后何时发送电子邮件的值notify_level_page 用于指明作业完成后何时发送呼叫的值0 从不 1 成功后 2（默认值） 失败后 3 始终EXEC MSDB.SP_ADD_JOB JOB_NAME=DUFEIBAK,ENABLED=1,OWNER_LOGIN_NAME=PERTHADMINISTRATOR,NOTIFY_LEVEL_NETSEND=3,NOTIFY_NETSEND_OPERATOR_NAME=OP1,NOTIFY_LEVEL_EVENTLOG=3现在会报告没有操作员.所以要先定义一个操作员EXEC MSDB.SP_HELP_OPERATORSP_ADD_OPERATOR 创建操作员常用参数: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 星期六 例如,定义一个操作员为DUFEI,只使用网络地址接收消息EXEC MSDB.SP_ADD_OPERATOR NAME=DUFEI,NETSEND_ADDRESS=00此示例设置 janetl 的操作员信息。启用操作员信息，这样，呼叫程序将从星期一到星期五的上午 8 点到下午 5 点通知 janetl。use msdbexec sp_add_operator name = Janet Leverling, enabled = 1, email_address =janetl, pager_address = 5673219, weekday_pager_start_time = 080000, weekday_pager_end_time = 170000, pager_days = 62 周一到周五sp_add_alert 创建警报:常用参数:NAME 警报名称SEVERITY 用于定义警报的严重级别 (1-25)ENABLED 警报的当前状态NOTIFICATION_MESSAGE 附加消息 JOB_NAME 该警报所执行的作业名称JOB_ID 该警报所执行的作业ID 与作业名称只能有一个例如:MSDB.SP_ADD_ALERT NAME=ALT2,SEVERITY=15MSDB.SP_DELETE_ALERT NAME=AL1:SP_ADD_JOBSCHEDULE作业调度常用参数:JOB_ID 作业IDJOB_NAME 作业名称NAME 调度名称ENABLED 调度的当前状态FREQ_TYPE 用于指明何时将执行作业的值值 描述 1 一次 4 每天 8 每周 16 每月 32 每月，与 freq interval 相关 64 当 SQLServerAgent 服务启动时运行 128 计算机空闲时运行 FREQ_INTERVAL 作业执行的天数FREQ_SUBDAY_TYPE 创建步骤: SP_ADD_JOBSTEP将一个步骤添加到作业中常用参数:JOB_ID 作业IDJOB_NAME 作业名称STEP_NAME 步骤的名称SUBSYSTEM 此计划所适合的类型COMMAND 此计划所使用的命令ON_SUCCESS_ACTION 成功时的操作ON_FAIL_ACTION 失败时的操作值 描述（操作） 1 成功后退出 成功默认值2 失败后退出 失败默认值3 转到下一步 4 转到步骤 on_success_step_id 或是 on_fail_step_id我们看一个综合的例子:定义每天晚上18:00自动对DUFEI数据库进行完全备份,每天的中午12:00差异备份- 定义操作员:EXEC MSDB.SP_ADD_OPERATOR NAME=DUFEI,NETSEND_ADDRESS=01-如果此操作员存在就删除EXEC MSDB.SP_DELETE_OPERATOR NAME=DUFEI-创建作业:declare jobid uniqueidentifierexec msdb.dbo.sp_add_job job_name=NITET数据库备份 ,ENABLED=1,OWNER_LOGIN_NAME=server1ADMINISTRATOR,NOTIFY_LEVEL_NETSEND=3,NOTIFY_NETSEND_OPERATOR_NAME=DUFEI,NOTIFY_LEVEL_EVENTLOG=3,job_id=jobid output-定义作业步骤:DECLARE SQL NVARCHAR(200),DBNAME SYSNAMESELECT DBNAME=DB_NAME() -当前数据库名SELECT SQL=NBACKUP DATABASE ITET TO DISK=C:ITET.BAK -注意双引号EXEC MSDB.DBO.SP_ADD_JOBSTEP JOB_ID=JOBID,STEP_NAME=NSTEP1,SUBSYSTEM=TSQL,DATABASE_NAME=DBNAME,COMMAND=SQL-定义作业调度:EXEC MSDB.SP_ADD_JOBSCHEDULE JOB_ID=JOBID,NAME=NSCH1,FREQ_TYPE=4,FREQ_I