管理任务自动化
SQL Server代理服务概述: 作业 警报 操作员
使用存储过程定义作业-->sp_add_job(update,delete) sp_add_jobstep(update,delete) sp_add_jobschedule(update,delete) sp_add_jobserver(update,delete)
使用存储过程定义作业-->创建作业 Declare @jobid uniqueidentifier
Exec msdb.dbo.sp_add_job
@job_name=N'Jobname',
@job_id=@jobid output
使用存储过程定义作业-->定义作业步骤 Declare @sql nvarchar(100),@dbname sysname
Select @dbname=db_name(),--当前数据库
@sql=N'job内容' -T-SQL语句
Exec msdb.dbo.sp_add_jobstep
@job_id=@jobid,
@step_name=N'job步骤名称'
@subsystem='TSQL'-步骤类型
@database_name=@dbname
@command=@sql
使用存储过程定义作业-->定义作业调度 Exec msdb.sp_add_jobschedule
@job_id=@jobid
@name=N'调度名'
@freq_tpye=4 --每天
@freq_interval=1 --每天1次
@freq_subday_type=0x8 --重复方式,0x1:指定时间,0x4:多少分钟,0x8多少小时执行一次
@freq_subday_interval=1 --重复周期,每小时执行一次
@freq_start_data=NULL --NULL表示当前日期YYYYMMDD
@freq_end_data=99991231 --默认为99991231
@freq_start_time=0000 --HHMMSS
@freq_end_time=235959
SQL Server 2005代理服务安全性增强: 若要使用SQL Server代理,用户必须是下列一个或多个固定数据库角色的成员: SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole 这些角色存储在msdb数据库中 任何用户都不是这些数据库角色的成员,必须显式授予这些角色中的成员身份 sa可以完全访问SQL Server代理
如果用户不是其中某个角色的成员,连接到SQL Server Management Studio中的SQL Server时,对象资源管理器中的"SQL Server代理"将不可见。用户必须是这些固定数据库角色之一的成员,或者是sysadmin固定服务器角色的成员才能使用SQL Server代理。
SQL Server代理安全性原则: 专门为代理创建专用的用户账户,并且只使用这些代理用户账户来运行作业步骤。只为代理用户账户授予必需的权限。只授予运行分配给给定代理账户的作业步骤实际所需的那些权限。不要作为Windows Administrators组成员的Microsoft Windows账户运行SQL Server代理服务。
较高特权的角色继承较低特权的角色对SQL Server代理对象(包括警报、运算符、作业、计划和代理)的权限
SQLAgentUserRole权限: SQLAgentUserRole是具有最低特权的SQL Server代理固定数据库角色。SQLAgentUserRole的成员只对它们所拥有的本地作业和作业计划拥有权限。它们不能使用多服务器作业(主服务器作业和目标服务器作业),也不能通过更改作业所有权来获得对它们还没有拥有的作业的访问权限。SQLAgentUserRole的成员只能在SQL Server Management Studio的"作业步骤属性"对话框中查看可用的代理列表。在SQL Server Management Studio对象资源管理器中,SQLAgentUserRole的成员只能看到"作业"节点。
SQLAgentUserRole对SQL Server代理对象的权限-->见下列表:
操作 运算符 本地作业(仅限于所拥有的作业) 作业计划(仅限于所拥有的计划) 代理
创建/修改/删除 否 是 是 否
视图列表(枚举) 是 是 是 是
启用/禁用 否 是 是 不适用
视图属性 否 是 是 否
执行/停止/开始 不适用 是 不适用 不适用
查看作业历史记录 不适用 是 不适用 不适用
删除作业历史记录 不适用 否 不适用 不适用
附加/分离 不适用 不适用 是 不适用
SQLAgentReaderRole权限: SQLAgentReaderRole包括所有的SQLAgentUserRole权限,以及查看可用的多服务器作业及其属性和历史记录的列表的权限。此角色的成员还可以查看所有可用作业和作业计划以及它们的属性的列表,而不只是它们所拥有的那些作业和作业计划。SQLAgentReaderRole成员不能通过更改作业所有权来获得对它们还没有拥有的作业的访问权限。在SQL Server Management Studio对象资源管理器中,SQLAgentReaderRole的成员只能看到"作业"节点。
SQLAgentOperatorRole权限: SQLAgentOperatorRole是具有最高特权的SQL Server代理固定数据库角色。可以执行、停止或启动所有本地作业,还可以删除服务器上的任何本地作业的作业历史记录。它们还可以启用或禁用服务器上的所有本地作业和计划。若要启用或禁用本地作业或计划,此角色的成员必须使用存储过程sp_update_job和sp_update_schedule。SQLAgentOperatorRole的成员只能指定那些指定了作业名称、计划名称或标识符的参数和@enabled参数。
SQL Server自动管理应用场景: 每天晚上自动20点停用UserDB数据库,早上7点自动启用该数据库 创建作业-->Declare @jobid uniqueidentifier Exec msdb.dbo.sp_add_job @job_name=N'定时停用UserDB数据库',@job_id=@jobid output
定义作业步骤-->Declare @sqlnvarchar (400),@dbname sysname
Select @dbname=N'master',
@sql=N'alter database UserDB set offline with rollback after 10'
Exec msdb.dbo.sp_add_jobstep
@job_id=@jobid,
@step_name=N'启用UserDB数据处理',
@subsystem='TSQL',
@database_name=@dbname,
@command=@sql
创建调度-->Exec msdb.sp_add_jobschedule
@job_id=@jobid,
@name=N'启用UserDB数据库处理调度',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=0x1,
@freq_subday_interval=1,
@active_start_time=200000 -晚上20点.
添加目标服务器-->Declare @servername sysname
Set
@servername=convert(nvarchar(128),serverproperty(N'serverName'))
Exec msdb.dbo.sp_add_jobserver
@job_id=@jobid,
@server_name=@servername
企业范围的自动化管理: 跨多个SQL Server实例的自动化管理称为"多服务器管理"。使用多服务器管理可以执行的操作: 管理两台或多台服务器。在企业服务器之间安排数据仓库的信息流。
主服务器将作业分发到目标服务器并从它那里接收事件。目标服务器定期连接到主服务器来更新它们的作业计划。如果主服务器上存在新作业,目标服务器将下载该作业。目标服务器在完成作业后,会重新连接到主服务器并报告作业状态。
将该备份作业一次性写入主服务器,然后登记目标服务器。从它们登记时刻起,所有部门服务器将运行相同的备份作业,而只需定义一次作业。
多服务器管理功能用于sysadmin角色成员。然而,目标服务器上的sysadmin角色成员无法编辑目标服务器上由主服务器执行的操作。这项安全措施可防止意外删除作业步骤,并可防止目标服务器上的操作中断。
两个服务都应该在Microsoft Windows域账户下运行。
如果存在大量目标服务器,应避免将生产服务器定义为主服务器。否则,目标服务器的通信量会降低生产服务器的性能。
如果在Microsoft SQL Server Management Studio以外对多服务器作业定义进行了更改,则必须将更改发布到下载列表中,以便目标服务器可以再次下载更新后的作业。为了确保目标服务器具有当前的作业定义,在更新多服务器作业后,需发布一条INSERT指令。
EXECUTE sp_post_max_operation 'INSERT','JOB','<job id>'
实现多服务器管理后,目标服务器将定期联系主服务器以上载有关已执行的作业的信息,并下载新的作业。联系主服务器的过程称为"服务器轮询",该过程每隔"轮询间隔"(定期)发生一次。轮询间隔默认情况下为一分钟。
当目标服务器轮询主服务器时,它从msdb数据库的sysdownloadlist表中读取分配给目标服务器的操作。操作包括删除作业、插入作业、启动作业和更新目标服务器的轮询间隔等。
将操作发布到sysdownloadlist表中有两种方式: 使用sp_post_max_operation存储过程显式发布。使用其他作业存储过程隐式发布。EXECUTE msdb.dbo.sp_post_msx_operation'INSERT','JOB','<job id>'
