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='PERTH/ADMINISTRATOR',

@NOTIFY_LEVEL_NETSEND=3,@NOTIFY_NETSEND_OPERATOR_NAME='OP1',@NOTIFY_LEVEL_EVENTLOG=3

 

现在会报告没有操作员.所以要先定义一个操作员

 

EXEC MSDB..SP_HELP_OPERATOR

 

SP_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='192.168.15.200'

 

此示例设置 janetl 的操作员信息。启用操作员信息，这样，呼叫程序将从星期一到星期五的上午 8 点到下午 5 点通知 janetl。

use msdb

exec sp_add_operator @name = 'Janet Leverling',

   @enabled = 1,

   @email_address ='janetl',

   @pager_address = '[e-mail]',

   @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=15

MSDB..SP_DELETE_ALERT @NAME='AL1'

 

:

SP_ADD_JOBSCHEDULE作业调度

常用参数:

@JOB_ID  作业ID

@JOB_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  作业ID

@JOB_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='192.168.1.101'

 

--如果此操作员存在就删除

EXEC MSDB..SP_DELETE_OPERATOR @NAME='DUFEI'

 

--创建作业:

declare @jobid uniqueidentifier

exec msdb.dbo.sp_add_job @job_name=N'ITET数据库备份' ,

@ENABLED=1,@OWNER_LOGIN_NAME='server1/ADMINISTRATOR',@NOTIFY_LEVEL_NETSEND=3,@NOTIFY_NETSEND_OPERATOR_NAME='DUFEI',@NOTIFY_LEVEL_EVENTLOG=3,

@job_id=@jobid output

 

--定义作业步骤:

DECLARE @SQL NVARCHAR(200),@DBNAME SYSNAME

SELECT @DBNAME=DB_NAME() --当前数据库名

SELECT @SQL=N'BACKUP DATABASE ITET TO DISK=''C:/ITET.BAK'''  --注意双引号

EXEC MSDB.DBO.SP_ADD_JOBSTEP @JOB_ID=@JOBID,@STEP_NAME=N'STEP1',

@SUBSYSTEM='TSQL',@DATABASE_NAME=@DBNAME,@COMMAND=@SQL

 

--定义作业调度:

 

EXEC MSDB..SP_ADD_JOBSCHEDULE @JOB_ID=@JOBID,@NAME=N'SCH1',@FREQ_TYPE=4,

@FREQ_INTERVAL=1,@FREQ_SUBDAY_TYPE=0X8,@FREQ_SUBDAY_INTERVAL=1,

@ACTIVE_START_DATE=NULL,@ACTIVE_END_DATE=99991231,@ACTIVE_START_TIME=000000,

@ACTIVE_END_TIME=235959

 

  --   添加目标服务器  

  EXEC   msdb.dbo.sp_add_jobserver @job_id=@jobid,@server_name= N'(local)'    

 

 

--删除作业

EXEC MSDB.DBO.SP_Delete_JOB @JOB_NAME=N'ITET数据库备份'