SQL Server 2008 R2实例添加：作业和代理配置的高级技巧


参考资源链接：[sqlserver2008R2应用添加实例步骤](https://wenku.csdn.net/doc/6412b721be7fbd1778d49334?spm=1055.2635.3001.10343)
# 1. SQL Server作业和代理的概念

## 1.1 SQL Server作业简介
SQL Server作业是数据库管理系统中一种自动化任务的工具，允许管理员或者开发者安排一系列的操作任务，并通过SQL Server Agent服务在指定的时间执行。作业可以定期运行，也可以在满足特定条件时触发，是保证数据库维护、数据备份和报表生成等工作顺利完成的关键组件。

## 1.2 SQL Server代理的作用
代理作为SQL Server中的一个服务，它的职责是管理和执行作业。它负责监控作业的状态，提供日志记录功能，以及在作业执行过程中出现错误时发出警报。代理确保作业的高效、稳定和安全地执行，是作业管理流程中的核心组件。

## 1.3 作业和代理的相互关系
作业与代理之间的关系密不可分。作业定义了要执行的任务列表，而代理则负责调度这些任务的执行。理解作业与代理之间的关系对于高效管理和维护SQL Server环境至关重要，能够帮助数据库管理员更好地控制任务流程，优化数据库性能，确保数据的准确性和一致性。

# 2. 作业管理基础

## 2.1 作业的创建与配置
### 2.1.1 作业的创建步骤
在SQL Server中，作业是一种自动执行的任务，可以包括查询、命令或其他一系列操作。创建作业的步骤如下：

1. **打开作业管理器**: 使用SQL Server Management Studio (SSMS) 连接到SQL Server实例，然后展开“代理”文件夹下的“作业”子文件夹。

2. **右键点击作业文件夹**: 选择“新建作业...”，这将打开创建新作业的向导界面。

3. **设置作业名称和描述**: 输入作业的名称和对作业的简短描述，以便于管理和识别。

4. **选择作业类型**: 可以选择标准作业或复制作业，一般选择标准作业即可。

5. **定义步骤**: 在“步骤”页面中，点击“新建”按钮添加作业步骤。这里可以指定步骤的名称、类型（T-SQL、Windows命令、Active Script等），并输入实际要执行的命令或脚本。

6. **配置调度**: 在“调度”页面中，你可以选择“新建”按钮来创建新的调度计划，用于定义作业执行的频率和时间。

7. **设定目标**: 如果需要，可以为作业设置目标服务器。如果未指定，则作业默认在本地服务器上执行。

8. **完成创建**: 完成所有配置后，点击“确定”保存作业。新的作业现在会出现在作业列表中，并根据定义的调度自动或手动执行。

### 2.1.2 配置作业属性
作业属性允许管理员更细致地控制作业的执行方式和行为：

- **常规**: 包括作业的名称、所有者、类别、描述等基本信息。
- **步骤**: 定义了作业的执行逻辑，可以添加、编辑或删除作业步骤。每个步骤都可以指定不同的命令类型和具体的命令内容。

- **调度**: 可以创建多个调度来控制作业执行的时间和频率。支持按日期、时间、周期性等多种方式设置。

- **通知**: 设置作业完成后的通知方式，如通过电子邮件、警报等方式通知管理员。

- **目标**: 指定作业可以在哪些服务器上执行。这有助于在多个服务器间进行作业的负载均衡和故障转移。

- **历史记录**: 可以设置保存作业执行历史记录的最大数量，以及作业执行失败或成功时记录的详细程度。

## 2.2 作业的执行和监控
### 2.2.1 手动执行作业
手动执行作业对于测试或紧急情况下的快速处理非常有用。要手动执行作业，可以执行以下步骤：

1. 在SSMS中找到要执行的作业。

2. 右键点击该作业并选择“属性”来查看和确认作业的配置信息。

3. 关闭属性窗口后，再次右键点击该作业，选择“启动作业”。

4. 在弹出的“启动作业”对话框中，如果作业有多个步骤，可以指定从哪个步骤开始执行。

5. 点击“确定”按钮，作业即开始执行。

### 2.2.2 监控作业执行状态
SQL Server 提供了多种方式来监控作业的执行状态：

- **作业历史记录**: 通过“作业活动监视器”可以查看作业执行的详细历史记录，包括作业开始时间、结束时间、执行结果和任何错误消息。

- **警报**: 可以配置警报，当作业失败或满足其他特定条件时，通过电子邮件、寻呼机或其他方式通知管理员。

- **系统事件日志**: 作业执行的相关事件会被记录在Windows系统日志中，可以使用事件查看器来监控。

### 2.2.3 使用警报管理作业
警报是用于通知管理员作业执行状态的重要工具。其配置步骤如下：

1. 在SSMS中，右键点击“代理”下的“警报”，选择“新建警报”。

2. 输入警报名称和描述，并定义触发警报的事件。

3. 在“响应”页面中，设置当警报触发时要执行的操作，比如发送通知。

4. 在“选项”页面中，可以进一步定制警报的行为，如确定警报何时被禁用。

5. 点击“确定”保存警报配置。

## 2.3 作业的故障排查与维护
### 2.3.1 诊断作业失败的常见原因
作业失败可能由多种原因造成，常见的包括：

- **权限问题**: 作业执行的数据库或系统对象上没有足够的权限。
- **连接失败**: 作业尝试连接到远程服务器或服务时失败。

- **资源限制**: SQL Server或操作系统资源不足，如内存、CPU、磁盘空间不足。

- **脚本错误**: 在作业步骤中使用的SQL脚本或其他脚本存在语法错误或逻辑错误。

- **依赖关系问题**: 作业依赖的服务或组件未正确启动。

诊断作业失败时，检查SQL Server的错误日志和作业历史记录是关键步骤。

### 2.3.2 维护作业执行历史记录
作业执行历史记录对于监控作业性能和诊断问题至关重要。维护作业历史记录的建议做法包括：

- 定期清理旧的作业历史记录，以避免日志过于庞大，影响查询效率。
- 通过SSMS中的作业属性设置来维护历史记录的最大条目数。

- 当作业执行失败时，确保保存足够的历史记录以供分析。

- 定期审查作业历史记录，识别趋势和潜在的问题。

为了展示具体的SQL Server作业和代理的代码块，示例如下：

-- 创建一个简单的作业来检查数据库的完整性
USE msdb;
GO

EXEC dbo.sp_add_job 
    @job_name = N'Check Database Integrity',
    @enabled = 1,
    @description = N'Job to check database integrity for AdventureWorks2016CTP3';
GO

EXEC dbo.sp_add_jobstep 
    @job_name = N'Check Database Integrity',
    @step_name = N'Run DBCC CHECKDB',
    @step_id = 1,
    @subsystem = N'TSQL',
    @command = N'DBCC CHECKDB AdventureWorks2016CTP3 WITH NO_INFOMSGS, ALL_ERRORMSGS',
    @database_name = N'AdventureWorks2016CTP3',
    @on_success_action = 1,
    @on_success_step_id = 0,
    @on_fail_action = 2,
    @on_fail_step_id = 0;
GO

EXEC dbo.sp_add_schedule 
    @schedule_name = N'Run Daily',
    @freq_type = 4, -- Daily
    @freq_interval = 1,
    @active_start_time = 0; -- midnight
GO

EXEC dbo.sp_add_jobserver 
    @job_name = N'Check Database Integrity',
    @server_name = N'(local)';
GO

-- 立即执行作业，检查其是否正常运行
EXEC dbo.sp_start_job 
    @job_name = N'Check Database Integrity';
GO

在上述代码块中，首先使用 `sp_add_job` 存储过程创建了一个作业，并定义了作业名称和描述。接着使用 `sp_add_jobstep` 添加了一个执行DBCC CHECKDB命令的步骤，来检查指定数据库的完整性。使用 `sp_add_schedule` 创建了一个每日执行的调度计划。最后，使用 `sp_add_jobserver` 指定作业将在本地服务器上执行，并且通过 `sp_start_job` 启动作业来验证作业是否能够正常运行。通过执行这段SQL代码，可以自动检查数据库完整性，大大减少人工干预的需要。

作业的创建和配置是确保SQL Server稳定运行的基础。通过上述步骤和最佳实践，可以确保数据库管理任务被有效地自动化和执行。

# 3. SQL Server代理的深入理解

在SQL Server的管理中，代理（Agent）扮演了至关重要的角色，它是SQL Server的一个组件，可以执行各种自动化任务。深入理解SQL Server代理，可以让我们更加高效地管理和维护SQL Server环境。

## 3.1 SQL Server代理的安全性配置

安全性是数据库管理系统中最关键的方面之一，SQL Server代理的安全性配置是确保作业安全执行的基础。

### 3.1.1 代理的安全角色和权限

SQL Server代理的安全性是通过代理角色和权限来管理的。主要有以下几种角色：

- **SQL Server代理角色**：它为数据库管理员提供了对代理的管理权限。只有拥有此角色的用户才能创建、修改或删除SQL Server代理作业。
- **SQL Server代理操作员角色**：这个角色的成员能够接收作业完成通知和警报。
- **sysadmin固定服务器角色**：作为服务器级别的最高权限角色，sysadmin可以执行所有代理操作。

这些角色的配置确保了适当的权限分离，允许数据库管理员对任务进行分级管理，同时限制操作员的权限，确保系统的安全性。

### 3.1.2 代理操作的加密机制

代理操作经常需要使用凭据，比如登录数据库服务器的用户名和密码。为避免明文存储敏感信息，SQL Server提供了代理操作的加密机制。例如，作业步骤中可以使用代理凭证，这些凭证会加密后存储在系统表中。

为了进一步增强安全性，SQL Server还提供了透明数据加密（TDE）和列级加密。这些