38、SQL Server 监控与审计全解析

职场萌新987

于 2025-08-13 15:36:15 发布

阅读量53

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Server 2012管理精华文章标签： SQL Server 监控审计

本文链接：https://blog.youkuaiyun.com/go5gopher/article/details/150568926

SQL Server 2012管理精华专栏收录该内容

46 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

SQL Server 监控与审计全解析

1. 使用活动监视器监控服务器

在 SQL Server 2008 中，微软重新设计了活动监视器（Activity Monitor），使其成为监控 SQL Server 的重要工具，SQL Server 2012 沿用了与 2008 版本相同的活动监视器。

启动活动监视器 ：在 SQL Server Management Studio 中，右键单击实例名称，然后选择“活动监视器”。
界面介绍 ：启动后，屏幕顶部会显示四个图表，分别为“处理器时间百分比”“等待任务”“数据库 I/O”和“批处理请求/秒”，这些图表按预定间隔收集并展示服务器数据。图表下方有四个类别：“进程”“资源等待”“数据文件 I/O”和“最近的昂贵查询”，这些类别提供了关于 SQL Server 实例运行情况的额外信息。
功能特点 ：活动监视器以图形用户界面（GUI）的形式展示了一些针对动态管理视图（DMV）的常见查询，并且可以使用标准的 GUI 操作对数据进行过滤和排序。顶部的图表提供了实例性能的简要历史记录，底部的类别则提供了可过滤的各种主题信息。
局限性 ：活动监视器按预定义的间隔查询系统，因此在处理生产问题时，通过 Management Studio 运行查询可能比等待活动监视器刷新能更快得到结果。通常在没有积极处理特定问题时，可将活动监视器用作早期问题识别的工具。

2. 使用 DMV 捕获 SQL Server 性能监视器计数器

在 SQL Server 2012 中，DMV sys.dm_os_performancecounters 包含了与 SQL Server 中存储的相同性能计数器。因此，无需连接服务器并运行性能监视器，只需查询该 DMV 即可在数据库中跟踪监视器的结果。

3. 管理数据仓库（Management Data Warehouse）

为了帮助用户收集、审查和存储性能数据，微软在 SQL Server 2008 中引入了管理数据仓库（MDW）。MDW 提供了一个向导，可帮助用户设置用于存储性能数据的存储库，决定要收集的数据以及收集频率，并提供一些有助于分析数据的报告。虽然使用该工具会失去一些控制权，但它是在开发自定义流程的同时快速开始监控系统的绝佳选择。相关详细设置和使用说明可参考 Kalen Delaney 的白皮书： http://msdn.microsoft.com/en-us/library/dd939169(v=sql.100).aspx 。

4. 利用 SQL Server Profiler

SQL Server Profiler 是一个图形化工具，用于跟踪 SQL Server 中发生的事件。它使用大量的事件和过滤器来捕获用户感兴趣的数据和信息，能帮助快速识别系统中正在运行的内容。

启动方式 ：
- 在 SQL Server Management Studio 中，点击“工具”，然后选择“SQL Server Profiler”。
- 通过“开始”菜单，依次选择“所有程序”>“Microsoft SQL Server 2012”>“性能工具”>“SQL Server Profiler”。
操作步骤 ：
1. 启动 SQL Server Profiler 后，连接到要捕获事件的服务器和实例，会进入“跟踪属性”对话框。
2. 在“常规”选项卡中，需要做出三个决策：选择要使用的模板、指定文件保存位置以及是否启用跟踪停止时间。可以选择将数据存储在表或文件中，在繁忙的服务器上进行跟踪时，建议不要写入表，以免增加数据库的写入操作。
3. 选择模板后，点击“事件选择”选项卡。可以选中“显示所有事件”和“显示所有列”复选框，以显示所有可用的可监控事件和列。
4. 如果要监控特定应用程序、用户或其他可用列的事件，可以点击“列筛选器”按钮，在“编辑筛选器”对话框中选择要应用筛选器的列，并在对话框右侧添加相应的文本。
5. 添加完所有筛选器后，回到“事件选择”选项卡，点击“运行”按钮开始跟踪。
注意事项 ：在服务器上运行 SQL Server Profiler 捕获大量事件时可能会导致 CPU 使用率增加，为避免资源利用率上升，可以使用服务器端跟踪。

5. 使用服务器端跟踪

服务器端跟踪是一种无需 GUI 界面即可跟踪系统中发生事件的方法。可以使用以下存储过程指定要跟踪的事件和过滤器：
- sp_trace_create ：创建跟踪定义。
- sp_trace_event ：向跟踪定义中添加事件和事件列。
- sp_trace_setFilter ：为跟踪定义事件过滤器。

可以使用以下跟踪函数查看跟踪信息：
- fn_trace_getinfo(trace_id) ：检索特定跟踪 ID 的跟踪定义，如果参数为 NULL，则检索所有跟踪的定义，也可以使用 sys.traces 检索类似数据。
- fn_trace_geteventinfo(trace_id) ：识别为跟踪指定的事件和列。
- fn_trace_getfilterinfo(trace_id) ：显示跟踪的过滤器。

创建服务器端跟踪的步骤如下：
1. 在 SQL Server Profiler 中使用 GUI 定义跟踪，勾选所有适当的选项。
2. 启动并立即停止跟踪。
3. 导航到“文件”选项，选择“导出”>“脚本跟踪定义”>“适用于 SQL Server 2005 - 2012”。
4. 指定文件名并点击“确定”，将得到一个跟踪定义的文件，例如以下脚本：

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5  

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'c:\tracefile.trc', @maxfilesize, NULL  
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 8ace1c88-612a-4f99-b771-5e429ccaafa6'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:  
select ErrorCode=@rc

finish:  
go

6. 自动化监控

有时在问题报告后再启动 SQL Server Profiler、审查 DMV 或向性能监视器添加计数器可能为时已晚，无法确定问题的原因。因此，定期出现问题时，自动化监控服务器是个不错的选择。可以通过以下方法实现自动化监控：
- DMV ：创建作业以自动运行用于捕获数据的脚本，注意仔细选择捕获时间，确保脚本不会对生产造成影响。
- 跟踪：设置作业或其他自动化流程，在 SQL Server 启动时执行使用服务器端跟踪方法创建的跟踪脚本。在创建新跟踪时，要注意设置最小化文件大小的选项，清理跟踪文件，避免磁盘空间被占满，并确保指定的文件名不会覆盖已有的重要文件，通常可以在文件名后附加日期和时间。
- 性能监视器 ：确定要监控的计数器，在性能监视器的“数据收集器集”部分创建数据收集器。设置文件滚动大小以防止文件占用服务器的所有空间，并为新创建的数据收集器创建一个计划，以控制数据的捕获时间。

自动化捕获系统性能信息后，可以将其与基线进行比较。一旦发现性能与基线相比发生变化（如 CPU、内存或磁盘 I/O），就需要确定导致变化的查询。

7. SQL Server 审计的重要性

作为数据库管理员，需要了解登录到系统或尝试登录系统的用户以及他们获得访问权限后所进行的操作。在 SQL Server 中，如果能够完全控制所有用户的访问和权限，那么对用户的审计就不是那么关键；但大多数情况下，DBA 无法完全控制，因此跟踪系统中谁在做什么非常重要。

历史上，DBA 可以审计登录成功或失败情况、启用 C2 审计跟踪，也可以使用 SQL Server Profiler 或跟踪来确定哪些用户已登录以及他们在系统中进行的操作。但除了 SQL Server Profiler 和跟踪外，DBA 缺乏对捕获事件的控制权，即使默认跟踪运行，由于没有添加额外的过滤器，也常常缺乏所需的准确信息。幸运的是，SQL Server 2008 增加了 SQL Server Audit 功能，允许用户确定重要的事件并相应地进行日志记录。

8. 选择审计类型

SQL Server Audit 允许从服务器级别和数据库级别两个方面监控服务器。数据库级别的审计仅在 SQL Server 2012 的企业版、数据中心版和开发人员版中可用。选择审计类型时，可以参考以下标准：
|审计类型|适用情况|
| ---- | ---- |
|服务器审计|监控影响整个服务器的操作、跨所有数据库的更改操作以及所有数据库架构的更改操作|
|数据库审计规范|监控特定数据库、对象或架构的操作、数据库中主体的特定操作以及数据库中的特定数据操作语言（DML）语句（如 SELECT、DELETE、UPDATE 等）|

综上所述，通过合理运用各种监控和审计工具，可以更好地管理 SQL Server 服务器，确保系统的性能和数据安全。

SQL Server 监控与审计全解析

9. 服务器审计的具体操作

服务器审计主要用于监控影响整个服务器的操作、跨所有数据库的更改操作以及所有数据库架构的更改操作。以下是创建服务器审计的一般步骤：
1. 创建审计对象 ：使用 CREATE SERVER AUDIT 语句创建一个审计对象，指定审计数据的存储位置（如文件或表）、最大文件大小等参数。例如：

CREATE SERVER AUDIT MyServerAudit
TO FILE (
    FILEPATH = 'C:\AuditLogs\',
    MAXSIZE = 10 MB,
    MAX_ROLLOVER_FILES = 5
);

启用审计对象 ：使用 ALTER SERVER AUDIT 语句启用创建的审计对象。

ALTER SERVER AUDIT MyServerAudit
WITH (STATE = ON);

创建服务器审计规范 ：使用 CREATE SERVER AUDIT SPECIFICATION 语句指定要审计的服务器级别的事件。例如，审计登录事件：

CREATE SERVER AUDIT SPECIFICATION MyServerAuditSpec
FOR SERVER AUDIT MyServerAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP);

启用服务器审计规范 ：使用 ALTER SERVER AUDIT SPECIFICATION 语句启用创建的服务器审计规范。

ALTER SERVER AUDIT SPECIFICATION MyServerAuditSpec
WITH (STATE = ON);

10. 数据库审计规范的具体操作

数据库审计规范用于监控特定数据库、对象或架构的操作、数据库中主体的特定操作以及数据库中的特定数据操作语言（DML）语句。以下是创建数据库审计规范的步骤：
1. 确保服务器审计已启用 ：在创建数据库审计规范之前，需要确保相应的服务器审计对象已经创建并启用。
2. 创建数据库审计规范 ：使用 CREATE DATABASE AUDIT SPECIFICATION 语句指定要审计的数据库级别的事件。例如，审计某个表的插入、更新和删除操作：

CREATE DATABASE AUDIT SPECIFICATION MyDatabaseAuditSpec
FOR SERVER AUDIT MyServerAudit
ADD (INSERT, UPDATE, DELETE ON dbo.MyTable BY public);

启用数据库审计规范 ：使用 ALTER DATABASE AUDIT SPECIFICATION 语句启用创建的数据库审计规范。

ALTER DATABASE AUDIT SPECIFICATION MyDatabaseAuditSpec
WITH (STATE = ON);

11. 审计数据的查看与分析

创建并启用审计后，需要查看和分析审计数据。可以通过以下方式进行：
- 查看文件审计数据 ：如果审计数据存储在文件中，可以使用 SQL Server Profiler 或其他工具打开审计文件进行查看。
- 查看表审计数据 ：如果审计数据存储在表中，可以使用 SQL 查询语句直接从表中检索数据。例如：

SELECT * FROM fn_get_audit_file ('C:\AuditLogs\*.sqlaudit', NULL, NULL);

12. 监控与审计的流程总结

为了更好地理解和应用上述监控与审计的方法，下面使用 mermaid 流程图展示整个流程：

graph LR
    classDef startend fill:#F5EBFF,stroke:#BE8FED,stroke-width:2px;
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px;
    classDef decision fill:#FFF6CC,stroke:#FFBC52,stroke-width:2px;

    A([开始监控与审计]):::startend --> B{选择监控工具}:::decision
    B -->|活动监视器| C(使用活动监视器监控服务器):::process
    B -->|DMV| D(使用 DMV 捕获性能计数器):::process
    B -->|MDW| E(使用管理数据仓库):::process
    B -->|SQL Server Profiler| F(利用 SQL Server Profiler 跟踪事件):::process
    B -->|服务器端跟踪| G(使用服务器端跟踪):::process
    C --> H(自动化监控设置):::process
    D --> H
    E --> H
    F --> H
    G --> H
    H --> I{是否需要审计}:::decision
    I -->|是| J{选择审计类型}:::decision
    J -->|服务器审计| K(创建并启用服务器审计):::process
    J -->|数据库审计规范| L(创建并启用数据库审计规范):::process
    K --> M(查看和分析审计数据):::process
    L --> M
    I -->|否| N([结束]):::startend
    M --> N

13. 监控与审计的最佳实践

为了有效地监控和审计 SQL Server，以下是一些最佳实践：
- 定期审查监控数据 ：定期查看活动监视器、性能监视器和审计数据，及时发现潜在的问题。
- 设置合理的阈值 ：在性能监视器和自动化监控中，设置合理的阈值，当性能指标超过阈值时及时发出警报。
- 备份审计数据 ：定期备份审计数据，防止数据丢失。
- 最小化审计影响 ：在设置审计时，只审计必要的事件，避免对服务器性能造成过大影响。

14. 总结

通过本文介绍的各种监控和审计方法，数据库管理员可以全面了解 SQL Server 的运行状态，及时发现并解决性能问题和安全隐患。在实际应用中，应根据具体需求选择合适的监控和审计工具，并遵循最佳实践，以确保 SQL Server 系统的稳定运行和数据安全。同时，持续关注 SQL Server 的更新和新功能，不断优化监控和审计策略，以适应不断变化的业务需求。