33、SQL Server 自动化日常维护指南

SQL Server 自动化日常维护指南

1. 自动化日常维护的重要性

作为数据库管理员（DBA），主动确保环境中所有服务器平稳运行是一项重大责任。不能等到用户抱怨响应时间慢才去重建索引或更新统计信息。若遇到如数据库页面损坏等问题，尽早发现至关重要。若问题长时间未被发现，可能会因缺乏必要备份而无法修复，只能接受数据丢失。维持 SQL Server 实例的最佳性能需要持续维护。幸运的是，可利用 SQL Server 中的工具来自动化维护任务，还能借助 Database Mail 和 SQL Server Agent 自动维护服务器并在出现故障时通知你，这样你就有更多时间处理积压项目。

2. Database Mail 配置

在自动化维护任务时，首先要设置 Database Mail，它能让你收到作业失败和警报等自动通知。配置 Database Mail 有两种方式：使用 Database Mail 配置向导和 T - SQL 脚本。

2.1 使用 Database Mail 配置向导

以下是使用向导配置 Database Mail 的详细步骤：
1. 打开 SQL Server Management Studio，展开“Management”文件夹，右键单击“Database Mail”，从上下文菜单中选择“Configure Database Mail”，进入欢迎界面。
2. 选择“Next”进入“Select Configuration Task”屏幕，由于是首次设置，选择执行所有初始设置任务的选项，然后点击“Next”。若服务器上未启用 Database Mail，系统会提示启用，之后进入“New Profile”屏幕。
3. 在“New Profile”屏幕中，输入新配置文件的名称和可选描述。配置文件是一组数据库邮件账户，可设置优先级以提高在某个邮件服务器不可用时收到邮件的概率。点击“Add”按钮，打开“New Database Mail Account”对话框。
4. 在“New Database Mail Account”对话框中，需为每个要使用的 SMTP 服务器创建一个单独的邮件账户，并输入组织的相关信息，具体选项如下：
- Account Name ：用于标识数据库邮件账户的名称。
- Description ：用于标识数据库邮件账户的可选描述。
- E - mail Address ：邮件发送的电子邮件地址，不一定要是实际的邮件账户，例如可以使用“Severname@company.com”表明邮件来自特定服务器。
- Display Name ：邮件消息中显示的可选名称，用于表明邮件发送者。
- Reply E - mail ：当有人回复此账户发送的邮件时使用的可选电子邮件地址。
- Server Name ：用于发送此账户邮件的 SMTP 服务器的名称或 IP 地址。
- Port Number ：用于连接 SMTP 服务器的端口号。
- This Server Requires a Secure Connection (SSL) ：此选项将加密 SQL Server 与 SMTP 服务器之间的通信，使用此选项需为 SQL Server 安装证书。
- Windows Authentication Using Database Engine Service Credentials ：此选项将使用 MSSQLServer 服务凭据连接到 SMTP 服务器。
- Basic Authentication ：此选项允许指定用户名和密码以连接到 SMTP 服务器。
- Anonymous Authentication ：此选项在不传递任何登录凭据的情况下连接到 SMTP 服务器，用于不需要身份验证的 SMTP 服务器。
5. 完成配置后，点击“OK”返回“New Profile”屏幕。若要添加多个邮件账户，重复上述步骤。添加多个账户后，可通过高亮显示账户并选择“Move Up”或“Move Down”来配置其优先级。完成所有新邮件账户配置后，点击“Next”进入“Manage Profile Security”屏幕。
6. 在“Manage Profile Security”屏幕中，可将 Database Mail 配置文件配置为公共配置文件或私有配置文件。若配置为公共配置文件，任何能访问 msdb 的用户都可使用该配置文件发送邮件；若配置为私有配置文件，则只有特定用户可以使用。还可以设置一个默认公共配置文件和一个默认私有配置文件，当账户同时有权限访问默认私有和公共配置文件时，将使用默认私有配置文件。若要将配置文件设为公共，选中配置文件名称旁边的“Public”复选框；若要设为私有，选择“Private Profiles”选项卡，该选项卡提供用户名下拉列表，可选择特定用户并授予其访问配置文件的权限。配置完配置文件安全性后，点击“Next”进入“Configure System Parameters”屏幕。
7. 在“Configure System Parameters”屏幕中，可定义整个 SQL Server 实例的系统参数，这些更改将应用于所有 Database Mail 配置文件和账户。具体选项如下：
- Account Retry Attempts ：Database Mail 使用配置文件中的每个账户重试发送邮件的次数。例如，若将此参数设置为 3，且配置文件中有两个账户，则每个账户将重试发送邮件 3 次。
- Account Retry Delay (Seconds) ：Database Mail 重试之间等待的秒数，此延迟不是配置文件中每个账户之间的延迟，而是在尝试使用配置文件中的所有账户后，等待指定秒数再重新尝试。
- Maximum File Size (Bytes) ：限制附件大小。
- Prohibited Attachment File Extensions ：发送 Database Mail 时不允许作为附件的文件扩展名，用逗号分隔。
- Database Mail Executable Minimum Lifetime (Seconds) ：即使邮件队列中没有项目，外部邮件进程保持活动的最短时间。若队列中有项目，外部邮件进程将保持活动直到所有项目处理完毕。
- Logging Level ：有三种日志记录级别可将事件发送到 Database Mail 日志：Normal（仅记录错误）、Extended（记录错误、警告和信息性消息，为默认级别）和 Verbose（记录错误、警告、信息性消息、成功消息和内部消息，通常仅用于故障排除）。
8. 完成系统参数配置后，点击“Next”进入“Complete the Wizard”屏幕，查看配置选项，点击“Finish”完成向导。

以下是配置过程的 mermaid 流程图：

graph LR
    A[开始] --> B[打开配置向导]
    B --> C[选择配置任务]
    C --> D{是否启用 Database Mail}
    D -- 否 --> E[启用 Database Mail]
    D -- 是 --> F[创建新配置文件]
    E --> F
    F --> G[添加邮件账户]
    G --> H[配置账户信息]
    H --> I[设置账户优先级]
    I --> J[配置配置文件安全性]
    J --> K[配置系统参数]
    K --> L[完成配置]

2.2 使用 T - SQL 脚本配置

使用 T - SQL 脚本配置 Database Mail 不仅在设置新服务器时更快，还能确保所有服务器的配置一致。以下是具体步骤和代码：
1. 确保启用 Database Mail：

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

2. 确保 msdb 启用了 Service Broker，因为 Database Mail 依赖它来传递邮件消息。可运行以下查询检查是否启用：

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'

若未启用，可运行以下命令启用，但启用前需停止 SQL Server Agent，以便 Service Broker 获取适当的锁：

ALTER DATABASE msdb SET ENABLE_BROKER

3. 添加 Database Mail 配置文件：

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBA Mail Profile',
@description = 'Profile used by the DBA to send email.'

4. 创建邮件账户：

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA Mail Account',
@description = 'Profile used by the DBA to send email.',
@email_address = 'DBA@somecompany.com',
@display_name = 'KEN - PC\SQL11',
@mailserver_name = 'KEN - PC'

5. 将账户与配置文件关联：

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBA Mail Profile',
@account_name = 'DBA Mail Account',
@sequence_number = 1

完整的脚本如下：

--MAKE SURE TO STOP SQL SERVER AGENT BEFORE RUNNING THIS SCRIPT!
USE msdb
GO
--Enable Database Mail
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
--Enable Service Broker
ALTER DATABASE msdb SET ENABLE_BROKER
--Add the profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBA Mail Profile',
@description = 'Profile used by the DBA to send email.'
--Add the account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA Mail Account',
@description = 'Profile used by the DBA to send email.',
@email_address = 'DBA@somecompany.com',
@display_name = (Select @@ServerName),
@mailserver_name = 'KEN - PC'
--Associate the account with the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBA Mail Profile',
@account_name = 'DBA Mail Account',
@sequence_number = 1
Print 'Don''t Forget To Restart SQL Server Agent!'

3. 发送 Database Mail

配置好 Database Mail 后，就可以开始发送邮件。可使用 sp_send_dbmail 存储过程发送邮件，但必须是 msdb 中 DatabaseMailUser 角色的成员才能执行。以下是不同类型邮件发送的示例：

3.1 发送基本邮件

--Basic email
EXEC msdb.dbo.sp_send_dbmail
@recipients='Somebody@SomeCompany.com', --[ ; ...n ]
@subject = 'Basic Database Mail Sample',
@body= 'This is a test email.',
@profile_name = 'DBA Email Profile'

3.2 发送带附件的邮件

--Code to send an email attachment
EXEC msdb.dbo.sp_send_dbmail
@recipients='Somebody@SomeCompany.com', --[ ; ...n ]
@subject ='Database Mail Sample With File Attached',
@body='This is a test email.',
@profile_name ='DBMailProfile',
@file_attachments ='C:\SomeFile.txt'; --[ ; ...n ]

3.3 发送包含查询结果的邮件

--Code to send query results
EXEC msdb.dbo.sp_send_dbmail
@recipients='Somebody@SomeCompany.com', --[ ; ...n ]
@subject ='Query Results As File Sample',
@body='This is a test email.',
@profile_name ='DBA Email Profile',
@query ='SELECT Name FROM sys.sysdatabases',
@execute_query_database = 'master',
@attach_query_result_as_file = 1,
@query_attachment_filename ='Databases.txt'

3.4 发送 HTML 格式的邮件

--Code to send an HTML email message
DECLARE @HTML NVARCHAR(MAX) ;
SET @HTML = '<table border="1">' +
            '<tr><th>Name</th></tr>' +
             Cast((SELECT name as "td"
                   FROM master.sys.sysdatabases
                   FOR XML PATH('tr')) as NVARCHAR(MAX)) +
            '</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@recipients='Somebody@SomeCompany.com', --[ ; ...n ]
@subject ='HTML Sample',
@body= @HTML,
@body_format = 'HTML' ,
@profile_name ='DBA Email Profile'

发送邮件后，可使用 sysmail_allitems 视图查看所有使用 Database Mail 处理的项目，还有其他有用的视图，如 sysmail_faileditems 显示失败消息， sysmail_usentitems 显示未发送消息， sysmail_sentitems 显示已发送消息。

SELECT * FROM msdb.dbo.sysmail_allitems

4. Database Mail 清理程序

Database Mail 会在 msdb 中保留所有发送的邮件副本和事件日志条目，随着时间推移，这些数据的积累会导致 msdb 数据库过度增长。可使用以下两个存储过程清理邮件历史记录，以保持 msdb 数据库大小可控：

4.1 删除邮件消息

sysmail_delete_mailitems_sp
[[ @sent_before =] 'sent_before'] -- '1/1/2009'
[ ,[ @sent_status =] 'sent_status'] -- sent, unsent, retrying, failed

若不提供任何参数，将删除所有邮件消息。

4.2 删除日志条目

sysmail_delete_log_sp
[[@logged_before =] 'logged_before'] --'1/1/2009'
[,[ @event_type =] 'event_type'] --success, warning, error, informational

若不提供任何参数，将删除所有 Database Mail 日志条目。应将这两个清理存储过程纳入日常维护例程，制定合理的保留策略并相应执行清理操作。

5. SQL Server Agent 及操作员配置

SQL Server Agent 是自动化维护任务的核心，它提供了多种功能来协助日常自动化和预防性维护。可创建操作员以接收特定事件通知，定义警报捕获特定事件并执行预定义操作，创建作业按预定计划执行日常预防性维护任务，甚至可以创建主服务器来管理多个目标服务器上的作业。

5.1 定义操作员

定义操作员可通过 SQL Server Agent 接收自动通知。操作员包含两个基本信息：用于标识操作员的名称和用于通知操作员的联系信息。

以下是添加操作员的两种方式：
- 使用 SQL Server Management Studio ：展开对象资源管理器中的“SQL Server Agent”，右键单击“Operators”文件夹，从上下文菜单中选择“New Operator”，打开“New Operator”对话框，输入操作员名称和电子邮件地址，点击“OK”。需注意，“net send”和“pager”信息配置选项将在未来版本中弃用，应避免使用。
- 使用 T - SQL 脚本 ：可使用 msdb 中的 sp_add_operator 过程添加操作员，示例代码如下：

EXEC msdb.dbo.sp_add_operator
@name='DBA Support',
@email_address='DBASupport@somecompany.com'

以下是添加操作员的步骤表格：
|方式|步骤|
| ---- | ---- |
|SQL Server Management Studio|1. 展开 SQL Server Agent
2. 右键单击 Operators 文件夹
3. 选择 New Operator
4. 输入名称和邮箱地址
5. 点击 OK|
|T - SQL 脚本|执行 EXEC msdb.dbo.sp_add_operator @name='DBA Support', @email_address='DBASupport@somecompany.com' |

以下是相关的 mermaid 流程图：

graph LR
    A[开始] --> B{选择方式}
    B -- Management Studio --> C[打开 New Operator 对话框]
    B -- T - SQL 脚本 --> D[编写并执行脚本]
    C --> E[输入信息]
    E --> F[点击 OK]
    D --> G[完成添加]
    F --> G

通过以上对 Database Mail 和 SQL Server Agent 的配置与使用，能有效实现 SQL Server 的自动化日常维护，提高工作效率并保障系统的稳定运行。

SQL Server 自动化日常维护指南

6. 定义警报

在 SQL Server Agent 中，定义警报可以捕获特定事件，并让 SQL Server Agent 执行预定义的操作。以下是定义警报的操作步骤：
1. 打开 SQL Server Management Studio，展开“SQL Server Agent”，右键单击“Alerts”文件夹，选择“New Alert”。
2. 在“New Alert”对话框中，进行如下设置：
- Name ：为警报设置一个有意义的名称，方便识别。
- Type ：选择警报的类型，如“SQL Server event alert”（基于 SQL Server 事件触发）或“Performance condition alert”（基于性能条件触发）。
- Event criteria ：
- 如果是“SQL Server event alert”，需要指定事件的来源、错误号、严重性等条件。例如，当错误号为 823（表示磁盘 I/O 错误）时触发警报。
- 如果是“Performance condition alert”，需要选择性能对象（如“SQLServer:Buffer Manager”）、性能计数器（如“Page reads/sec”）和阈值条件（如大于某个值）。
- Response ：设置警报触发时要执行的操作，可选择通知操作员、执行作业等。例如，选择之前配置好的操作员“DBA Support”，并选择一个用于处理问题的作业。
3. 点击“OK”保存警报设置。

以下是定义警报的步骤列表：
1. 打开 SQL Server Management Studio 并展开“SQL Server Agent”。
2. 右键单击“Alerts”文件夹，选择“New Alert”。
3. 设置警报名称和类型。
4. 根据警报类型设置事件或性能条件。
5. 设置警报触发时的响应操作。
6. 点击“OK”保存设置。

7. 创建作业

创建作业可以让 SQL Server Agent 按预定的时间表执行日常预防性维护任务。以下是创建作业的详细步骤：
1. 打开 SQL Server Management Studio，展开“SQL Server Agent”，右键单击“Jobs”文件夹，选择“New Job”。
2. 在“New Job”对话框的“General”页面：
- Name ：为作业设置一个清晰的名称，如“Weekly Database Backup”。
- Description ：可对作业的功能进行简要描述。
3. 在“Steps”页面，点击“New”添加作业步骤：
- Step name ：为步骤命名，如“Backup Database”。
- Type ：选择步骤的类型，通常为“Transact - SQL script (T - SQL)”。
- Database ：选择要执行脚本的数据库。
- Command ：输入要执行的 T - SQL 脚本，例如：

BACKUP DATABASE YourDatabaseName TO DISK = 'C:\Backup\YourDatabaseName.bak'

4. 在“Schedules”页面，点击“New”设置作业的执行时间表：
   • Name ：为时间表命名，如“Weekly Schedule”。
   • Frequency ：选择执行频率，如“Daily”“Weekly”“Monthly”等。
   • Recurrence pattern ：根据选择的频率设置具体的执行时间，例如每周日晚上 2 点执行。
5. 在“Notifications”页面，设置作业执行成功或失败时的通知方式，可选择通知操作员等。
6. 点击“OK”保存作业设置。

以下是创建作业的步骤表格：
|步骤|操作|
| ---- | ---- |
|1|打开 SQL Server Management Studio 并展开“SQL Server Agent”，右键单击“Jobs”文件夹，选择“New Job”|
|2|在“General”页面设置作业名称和描述|
|3|在“Steps”页面添加作业步骤，设置步骤名称、类型、数据库和执行脚本|
|4|在“Schedules”页面设置作业执行时间表|
|5|在“Notifications”页面设置作业执行结果的通知方式|
|6|点击“OK”保存作业设置|

以下是创建作业的 mermaid 流程图：

graph LR
    A[开始] --> B[打开 New Job 对话框]
    B --> C[设置 General 信息]
    C --> D[添加作业步骤]
    D --> E[设置步骤详细信息]
    E --> F[设置作业时间表]
    F --> G[设置通知方式]
    G --> H[保存作业设置]

8. 创建主服务器管理多目标服务器

如果需要管理多个目标服务器上的作业，可以创建一个主服务器。以下是创建主服务器并管理多目标服务器的步骤：
1. 配置主服务器 ：
- 打开 SQL Server Management Studio，展开“SQL Server Agent”，右键单击“Multi - Server Administration”，选择“Define Master Server”。
- 在“Define Master Server”对话框中，选择当前服务器作为主服务器，并设置相关选项，如“Use Windows Authentication”等。
- 点击“OK”完成主服务器的配置。
2. 注册目标服务器 ：
- 在主服务器上，右键单击“Target Servers”，选择“Register Target Servers”。
- 在“Register Target Servers”对话框中，输入目标服务器的名称或 IP 地址，选择连接方式（如“SQL Server Authentication”或“Windows Authentication”），并输入相应的凭据。
- 点击“OK”完成目标服务器的注册。
3. 分发作业到目标服务器 ：
- 创建作业时，在作业的“Targets”页面，选择要将作业分发到的目标服务器。
- 作业将自动在所选的目标服务器上执行。

以下是创建主服务器并管理多目标服务器的步骤列表：
1. 配置主服务器：打开 SQL Server Management Studio，定义当前服务器为主服务器。
2. 注册目标服务器：在主服务器上注册目标服务器，输入目标服务器信息和凭据。
3. 分发作业：在创建作业时选择要分发作业的目标服务器。

9. 自动化维护任务的综合应用

通过结合 Database Mail、SQL Server Agent、操作员、警报和作业，可以实现全面的自动化维护任务。以下是一个综合应用的示例场景：
1. 配置 Database Mail，确保可以接收作业执行结果和警报通知。
2. 定义操作员，如“DBA Support”，用于接收通知。
3. 创建警报，例如当数据库备份失败时触发警报。
- 警报触发时，通知操作员“DBA Support”。
4. 创建作业，如每周进行一次数据库备份：
- 作业步骤中包含备份数据库的 T - SQL 脚本。
- 作业按每周的时间表执行。
- 作业执行成功或失败时，通过 Database Mail 通知操作员。

以下是综合应用的步骤表格：
|步骤|操作|
| ---- | ---- |
|1|配置 Database Mail|
|2|定义操作员|
|3|创建警报，设置触发条件和通知操作员|
|4|创建作业，设置作业步骤、时间表和通知方式|

10. 总结

通过对 SQL Server 自动化日常维护的各项功能进行配置和使用，包括 Database Mail 的设置与使用、SQL Server Agent 中操作员、警报、作业的配置以及主服务器的创建和管理，可以大大提高数据库管理的效率和可靠性。自动化维护任务可以及时发现和处理问题，减少人工干预，保障 SQL Server 系统的稳定运行。同时，合理的清理程序可以保持数据库的性能和大小在可控范围内。在实际应用中，应根据具体的业务需求和环境，灵活配置和调整这些自动化功能，以实现最佳的维护效果。

在未来的数据库管理工作中，持续关注 SQL Server 的新特性和功能，不断优化自动化维护方案，将有助于更好地应对日益复杂的数据库环境和业务需求。