SQLServer教程FG036-SQLServer审计日志配置实战

目录大纲

Part01-基础概念与理论知识
Part02-生产环境规划与建议
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
Part05-风哥经验总结与分享

内容简介

本文档基于SQLServer官方文档的审计日志内容，结合生产环境实际情况，详细讲解SQLServer审计日志的配置、管理、监控等内容。风哥教程参考SQLServer官方文档Auditing、Security等相关章节。

Part01-基础概念与理论知识

1.1 SQLServer审计日志概念

SQLServer审计日志的概念：

审计日志是记录SQLServer实例和数据库活动的重要手段
通过审计日志可以追踪谁在什么时候执行了什么操作
审计日志对于安全合规和故障排查非常重要
支持细粒度的审计策略配置

更多视频教程www.fgedu.net.cn

1.2 SQLServer审计日志类型

SQLServer审计日志类型：

服务器审计：针对SQLServer实例级别的审计
数据库审计：针对特定数据库的审计
登录审计：记录登录尝试和成功/失败情况
操作审计：记录数据库操作，如SELECT、INSERT、UPDATE、DELETE等
权限审计：记录权限变更和使用情况

学习交流加群风哥微信: itpux-com

1.3 SQLServer审计日志架构

SQLServer审计日志架构：

审计对象：定义审计的范围和目标
审计规范：定义要审计的事件和操作
审计目标：定义审计日志的存储位置，如文件、Windows事件日志等
审计会话：控制审计的启动和停止

学习交流加群风哥QQ113257174

Part02-生产环境规划与建议

2.1 SQLServer审计日志规划原则

审计日志规划原则：

根据业务需求和合规要求确定审计范围
选择关键操作和事件进行审计
平衡审计的完整性和系统性能
建立审计日志的存储和管理策略
定期分析审计日志，及时发现异常

风哥提示：生产环境应建立完善的审计体系，确保数据安全和合规要求

2.2 SQLServer审计日志存储策略

审计日志存储策略：

文件存储：将审计日志存储在文件系统中，便于管理和分析
Windows事件日志：将审计日志存储在Windows事件日志中，便于集成到系统监控
Azure Blob存储：对于云环境，可存储在Azure Blob中
存储位置：应选择独立的存储位置，避免与数据文件在同一磁盘
存储大小：根据审计范围和频率估算存储需求

更多学习教程公众号风哥教程itpux_com

2.3 SQLServer审计日志保留策略

审计日志保留策略：

保留期限：根据合规要求确定保留期限，如1年、3年等
备份策略：定期备份审计日志，防止丢失
清理策略：自动清理过期的审计日志，避免存储空间不足
归档策略：将重要的审计日志归档，便于长期保存和查询

from SQLServer视频:www.itpux.com

Part03-生产环境项目实施方案

3.1 SQLServer审计日志配置

审计日志配置：

— 步骤1：创建服务器审计
CREATE SERVER AUDIT [ServerAudit]
TO FILE (
FILEPATH = N’E:\SQLServer\Audit\’,
MAXSIZE = 100 MB,
MAX_ROLLOVER_FILES = 10,
RESERVE_DISK_SPACE = OFF
);
GO

— 步骤2：启用服务器审计
ALTER SERVER AUDIT [ServerAudit] WITH (STATE = ON);
GO

— 步骤3：创建数据库审计规范
USE fgedudb;
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec]
FOR SERVER AUDIT [ServerAudit]
ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public),
ADD (EXECUTE ON SCHEMA::dbo BY public),
ADD (SCHEMA_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP);
GO

— 步骤4：启用数据库审计规范
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec] WITH (STATE = ON);
GO

— 步骤5：创建服务器审计规范
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpec]
FOR SERVER AUDIT [ServerAudit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP);
GO

— 步骤6：启用服务器审计规范
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpec] WITH (STATE = ON);
GO

— 步骤7：查看审计状态
SELECT
name,
status_desc,
audit_file_path,
max_file_size_mb,
max_rollover_files
FROM sys.server_audits;
GO

— 步骤8：查看数据库审计规范
SELECT
name,
database_name,
audit_id,
status_desc
FROM sys.database_audit_specifications;
GO

— 步骤9：查看服务器审计规范
SELECT
name,
audit_id,
status_desc
FROM sys.server_audit_specifications;
GO

执行结果：

Commands completed successfully.

name status_desc audit_file_path max_file_size_mb max_rollover_files
———- ———— ————————- —————- ——————
ServerAudit ON E:\SQLServer\Audit\ 100 10

(1 row affected)

name database_name audit_id status_desc
——————– ————- ——– ————
DatabaseAuditSpec fgedudb 1 ON

(1 row affected)

name audit_id status_desc
—————— ——– ————
ServerAuditSpec 1 ON

(1 row affected)

3.2 SQLServer审计日志管理

审计日志管理：

— 步骤1：查看审计日志
SELECT
event_time,
action_id,
succeeded,
session_id,
server_principal_name,
database_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT);
GO

— 步骤2：筛选特定事件
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘SELECT’, ‘INSERT’, ‘UPDATE’, ‘DELETE’)
AND database_name = ‘fgedudb’
ORDER BY event_time DESC;
GO

— 步骤3：筛选登录事件
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
client_ip,
application_name
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘LGIF’, ‘LGIS’)
ORDER BY event_time DESC;
GO

— 步骤4：修改审计配置
— 修改服务器审计
ALTER SERVER AUDIT [ServerAudit]
WITH (
MAXSIZE = 200 MB,
MAX_ROLLOVER_FILES = 20
);
GO

— 步骤5：禁用和启用审计
— 禁用数据库审计规范
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec] WITH (STATE = OFF);
GO

— 修改数据库审计规范
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec]
ADD (BACKUP_RESTORE_GROUP);
GO

— 启用数据库审计规范
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec] WITH (STATE = ON);
GO

— 步骤6：删除审计
— 禁用服务器审计
ALTER SERVER AUDIT [ServerAudit] WITH (STATE = OFF);
GO

— 删除服务器审计规范
DROP SERVER AUDIT SPECIFICATION [ServerAuditSpec];
GO

— 删除数据库审计规范
USE fgedudb;
GO

DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec];
GO

— 删除服务器审计
DROP SERVER AUDIT [ServerAudit];
GO

执行结果：

event_time action_id succeeded session_id server_principal_name database_name object_name statement
————————- ——— ——— ———- ——————– ————- ———– ——————————————————————————–
2025-04-08 10:00:00.000 SELECT 1 54 fgedu fgedudb large_table SELECT * FROM fgedu.large_table
2025-04-08 09:59:00.000 INSERT 1 54 fgedu fgedudb large_table INSERT INTO fgedu.large_table (col1, col2) VALUES (‘test’, ‘value’)

(2 rows affected)

(1 row affected)

event_time action_id succeeded session_id server_principal_name client_ip application_name
————————- ——— ——— ———- ——————– ———— ——————–
2025-04-08 09:00:00.000 LGIS 1 51 sa 192.168.1.100 SQLServer Management Studio
2025-04-08 08:59:00.000 LGIF 0 50 fgedu 192.168.1.101 .NET SqlClient Data Provider

(2 rows affected)

Commands completed successfully.

3.3 SQLServer审计日志监控

审计日志监控：

— 步骤1：创建审计日志监控表
USE msdb;
GO

CREATE TABLE dbo.audit_log_monitoring (
id INT IDENTITY(1,1) PRIMARY KEY,
collection_time DATETIME,
audit_name VARCHAR(100),
event_count INT,
error_count INT,
file_size_mb DECIMAL(10,2),
file_count INT
);
GO

— 步骤2：创建监控存储过程
CREATE PROCEDURE dbo.sp_collect_audit_log_data
AS
BEGIN
SET NOCOUNT ON;

DECLARE @audit_name VARCHAR(100);
DECLARE @event_count INT;
DECLARE @error_count INT;
DECLARE @file_size_mb DECIMAL(10,2);
DECLARE @file_count INT;

— 遍历所有审计
DECLARE audit_cursor CURSOR FOR
SELECT name FROM sys.server_audits;

OPEN audit_cursor;
FETCH NEXT FROM audit_cursor INTO @audit_name;

WHILE @@FETCH_STATUS = 0
BEGIN
— 统计事件数
SELECT @event_count = COUNT(*)
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
WHERE event_time > DATEADD(HOUR, -1, GETDATE());

— 统计错误数
SELECT @error_count = COUNT(*)
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
WHERE event_time > DATEADD(HOUR, -1, GETDATE())
AND succeeded = 0;

— 统计文件大小和数量
EXEC master.dbo.xp_cmdshell ‘dir /s /b E:\SQLServer\Audit\*.sqlaudit’, NO_OUTPUT;
— 这里可以使用更复杂的方法计算文件大小和数量

— 插入监控数据
INSERT INTO dbo.audit_log_monitoring (
collection_time,
audit_name,
event_count,
error_count,
file_size_mb,
file_count
) VALUES (
GETDATE(),
@audit_name,
@event_count,
@error_count,
0, — 实际环境中计算
0 — 实际环境中计算
);

FETCH NEXT FROM audit_cursor INTO @audit_name;
END;

CLOSE audit_cursor;
DEALLOCATE audit_cursor;

— 清理旧数据（保留30天）
DELETE FROM dbo.audit_log_monitoring
WHERE collection_time < DATEADD(DAY, -30, GETDATE()); END; GO -- 步骤3：创建监控作业 USE msdb; GO EXEC dbo.sp_add_job @job_name = N'CollectAuditLogData', @enabled = 1, @description = N'收集SQLServer审计日志数据'; GO EXEC dbo.sp_add_jobstep @job_name = N'CollectAuditLogData', @step_name = N'Collect Data', @subsystem = N'TSQL', @command = N'EXEC dbo.sp_collect_audit_log_data;', @database_name = N'msdb'; GO EXEC dbo.sp_add_jobschedule @job_name = N'CollectAuditLogData', @name = N'Every 1 Hour', @freq_type = 4, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @active_start_time = 000000, @active_end_time = 235959; GO -- 步骤4：创建审计日志告警 CREATE PROCEDURE dbo.sp_check_audit_log_alerts AS BEGIN SET NOCOUNT ON; DECLARE @error_count INT; -- 检查错误事件 SELECT @error_count = COUNT(*) FROM sys.fn_get_audit_file('E:\SQLServer\Audit\*.sqlaudit', DEFAULT, DEFAULT) WHERE event_time > DATEADD(HOUR, -1, GETDATE())
AND succeeded = 0;

— 如果错误数超过阈值，发送告警
IF @error_count > 5
BEGIN
— 这里可以添加发送邮件告警的代码
PRINT ‘审计日志错误数超过阈值: ‘ + CAST(@error_count AS VARCHAR(10));
END;
END;
GO

— 步骤5：创建告警作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’CheckAuditLogAlerts’,
@enabled = 1,
@description = N’检查SQLServer审计日志告警’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’CheckAuditLogAlerts’,
@step_name = N’Check Alerts’,
@subsystem = N’TSQL’,
@command = N’EXEC dbo.sp_check_audit_log_alerts;’,
@database_name = N’msdb’;
GO

EXEC dbo.sp_add_jobschedule
@job_name = N’CheckAuditLogAlerts’,
@name = N’Every 30 Minutes’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 30,
@active_start_time = 000000,
@active_end_time = 235959;
GO

执行结果：

Commands completed successfully.

(1 row affected)

Commands completed successfully.

(1 row affected)

Commands completed successfully.

(1 row affected)

Part04-生产案例与实战讲解

4.1 SQLServer审计日志配置案例

审计日志配置实战：

— 案例：配置SQLServer审计日志
— 环境准备：
— SQLServer实例：fgedu-server
— 数据库：fgedudb

— 步骤1：创建服务器审计
CREATE SERVER AUDIT [FGEduAudit]
TO FILE (
FILEPATH = N’E:\SQLServer\Audit\’,
MAXSIZE = 500 MB,
MAX_ROLLOVER_FILES = 20,
RESERVE_DISK_SPACE = OFF
);
GO

— 步骤2：启用服务器审计
ALTER SERVER AUDIT [FGEduAudit] WITH (STATE = ON);
GO

— 步骤3：创建数据库审计规范
USE fgedudb;
GO

CREATE DATABASE AUDIT SPECIFICATION [FGEduDatabaseAudit]
FOR SERVER AUDIT [FGEduAudit]
ADD (SELECT ON OBJECT::fgedu.large_table BY public),
ADD (INSERT ON OBJECT::fgedu.large_table BY public),
ADD (UPDATE ON OBJECT::fgedu.large_table BY public),
ADD (DELETE ON OBJECT::fgedu.large_table BY public),
ADD (EXECUTE ON OBJECT::fgedu.sp_test BY public),
ADD (SCHEMA_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP);
GO

— 步骤4：启用数据库审计规范
ALTER DATABASE AUDIT SPECIFICATION [FGEduDatabaseAudit] WITH (STATE = ON);
GO

— 步骤5：创建服务器审计规范
CREATE SERVER AUDIT SPECIFICATION [FGEduServerAudit]
FOR SERVER AUDIT [FGEduAudit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP);
GO

— 步骤6：启用服务器审计规范
ALTER SERVER AUDIT SPECIFICATION [FGEduServerAudit] WITH (STATE = ON);
GO

— 步骤7：验证审计配置
SELECT
a.name AS audit_name,
a.status_desc AS audit_status,
s.name AS spec_name,
s.status_desc AS spec_status
FROM sys.server_audits a
JOIN sys.server_audit_specifications s ON a.audit_id = s.audit_id;
GO

— 步骤8：测试审计
— 执行一些操作，触发审计
USE fgedudb;
GO

— 测试SELECT操作
SELECT TOP 10 * FROM fgedu.large_table;
GO

— 测试INSERT操作
INSERT INTO fgedu.large_table (col1, col2) VALUES (‘test1’, ‘value1’);
GO

— 测试UPDATE操作
UPDATE fgedu.large_table SET col2 = ‘updated’ WHERE col1 = ‘test1’;
GO

— 测试DELETE操作
DELETE FROM fgedu.large_table WHERE col1 = ‘test1’;
GO

执行结果：

Commands completed successfully.

audit_name audit_status spec_name spec_status
———– ———— ——————- ————
FGEduAudit ON FGEduServerAudit ON

(1 row affected)

(10 rows affected)

(1 row affected)

4.2 SQLServer审计日志分析案例

审计日志分析实战：

— 案例：分析SQLServer审计日志
— 环境准备：
— SQLServer实例：fgedu-server
— 审计日志路径：E:\SQLServer\Audit\

— 步骤1：查看审计日志概览
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
ORDER BY event_time DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
GO

— 步骤2：分析登录事件
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
client_ip,
application_name,
session_id
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘LGIF’, ‘LGIS’)
ORDER BY event_time DESC;
GO

— 步骤3：分析数据操作事件
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘SELECT’, ‘INSERT’, ‘UPDATE’, ‘DELETE’)
AND database_name = ‘fgedudb’
ORDER BY event_time DESC;
GO

— 步骤4：分析权限变更事件
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
WHERE action_id IN (‘AL’, ‘ALG’, ‘GRANT’, ‘DENY’, ‘REVOKE’)
ORDER BY event_time DESC;
GO

— 步骤5：分析错误事件
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
WHERE succeeded = 0
ORDER BY event_time DESC;
GO

— 步骤6：统计审计事件
SELECT
action_id,
COUNT(*) AS event_count,
SUM(CASE WHEN succeeded = 1 THEN 1 ELSE 0 END) AS success_count,
SUM(CASE WHEN succeeded = 0 THEN 1 ELSE 0 END) AS failure_count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
GROUP BY action_id
ORDER BY event_count DESC;
GO

— 步骤7：统计用户活动
SELECT
server_principal_name,
COUNT(*) AS event_count,
SUM(CASE WHEN succeeded = 1 THEN 1 ELSE 0 END) AS success_count,
SUM(CASE WHEN succeeded = 0 THEN 1 ELSE 0 END) AS failure_count
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
GROUP BY server_principal_name
ORDER BY event_count DESC;
GO

— 步骤8：分析特定时间段的审计日志
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
WHERE event_time BETWEEN ‘2025-04-08 00:00:00’ AND ‘2025-04-08 23:59:59’
ORDER BY event_time DESC;
GO

执行结果：

event_time action_id succeeded server_principal_name database_name object_name statement
————————- ——— ——— ——————– ————- ———– ——————————————————————————–
2025-04-08 10:00:00.000 DELETE 1 fgedu fgedudb large_table DELETE FROM fgedu.large_table WHERE col1 = ‘test1’
2025-04-08 09:59:00.000 UPDATE 1 fgedu fgedudb large_table UPDATE fgedu.large_table SET col2 = ‘updated’ WHERE col1 = ‘test1’
2025-04-08 09:58:00.000 INSERT 1 fgedu fgedudb large_table INSERT INTO fgedu.large_table (col1, col2) VALUES (‘test1’, ‘value1’)
2025-04-08 09:57:00.000 SELECT 1 fgedu fgedudb large_table SELECT TOP 10 * FROM fgedu.large_table
2025-04-08 09:56:00.000 LGIS 1 fgedu NULL NULL NULL

(5 rows affected)

event_time action_id succeeded server_principal_name client_ip application_name session_id
————————- ——— ——— ——————– ———— ——————– ———-
2025-04-08 09:56:00.000 LGIS 1 fgedu 192.168.1.100 .NET SqlClient Data Provider 54
2025-04-08 09:00:00.000 LGIS 1 sa 192.168.1.101 SQLServer Management Studio 51
2025-04-08 08:59:00.000 LGIF 0 fgedu 192.168.1.102 .NET SqlClient Data Provider 50

(3 rows affected)

(4 rows affected)

— 无权限变更事件

(1 row affected)

action_id event_count success_count failure_count
——— ———– ————- ————–
SELECT 100 100 0
INSERT 50 50 0
UPDATE 30 30 0
DELETE 10 10 0
LGIS 20 20 0
LGIF 5 0 5

(6 rows affected)

server_principal_name event_count success_count failure_count
——————– ———– ————- ————–
fgedu 150 145 5
sa 50 50 0

(2 rows affected)

(7 rows affected)

4.3 SQLServer审计日志故障处理案例

审计日志故障处理实战：

— 案例：SQLServer审计日志故障处理
— 环境准备：
— SQLServer实例：fgedu-server
— 审计日志路径：E:\SQLServer\Audit\

— 场景1：审计日志文件空间不足
— 问题：审计日志文件达到最大大小，无法继续写入
— 解决方案：
— 1. 检查审计状态
SELECT
name,
status_desc,
audit_file_path,
max_file_size_mb,
max_rollover_files
FROM sys.server_audits;
GO

— 2. 增加审计文件大小和滚动文件数
ALTER SERVER AUDIT [FGEduAudit]
WITH (
MAXSIZE = 1000 MB,
MAX_ROLLOVER_FILES = 50
);
GO

— 3. 清理旧的审计文件
— 手动删除过期的审计文件或使用脚本自动清理

— 场景2：审计日志无法写入
— 问题：审计日志写入失败，可能是权限问题或磁盘问题
— 解决方案：
— 1. 检查审计状态
SELECT
name,
status_desc,
audit_file_path
FROM sys.server_audits;
GO

— 2. 检查文件系统权限
— 确保SQLServer服务账户有审计日志目录的写入权限

— 3. 检查磁盘空间
— 确保审计日志所在磁盘有足够的空间

— 4. 重启审计
ALTER SERVER AUDIT [FGEduAudit] WITH (STATE = OFF);
GO

ALTER SERVER AUDIT [FGEduAudit] WITH (STATE = ON);
GO

— 场景3：审计日志过大影响性能
— 问题：审计日志过大，影响SQLServer性能
— 解决方案：
— 1. 调整审计范围
— 禁用不必要的审计事件
ALTER DATABASE AUDIT SPECIFICATION [FGEduDatabaseAudit] WITH (STATE = OFF);
GO

ALTER DATABASE AUDIT SPECIFICATION [FGEduDatabaseAudit]
DROP (SELECT ON OBJECT::fgedu.large_table BY public);
GO

ALTER DATABASE AUDIT SPECIFICATION [FGEduDatabaseAudit] WITH (STATE = ON);
GO

— 2. 优化审计存储
— 考虑使用更快的存储设备

— 3. 定期归档审计日志
— 创建归档脚本，定期将审计日志归档到其他存储

— 场景4：审计日志分析性能问题
— 问题：查询审计日志时性能缓慢
— 解决方案：
— 1. 限制查询范围
— 使用时间范围和其他条件限制查询结果
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT)
WHERE event_time BETWEEN ‘2025-04-08 00:00:00’ AND ‘2025-04-08 23:59:59’
AND database_name = ‘fgedudb’
ORDER BY event_time DESC;
GO

— 2. 使用索引视图
— 创建索引视图加速审计日志查询
USE msdb;
GO

CREATE VIEW dbo.vw_audit_log WITH SCHEMABINDING
AS
SELECT
event_time,
action_id,
succeeded,
server_principal_name,
database_name,
object_name,
statement
FROM sys.fn_get_audit_file(‘E:\SQLServer\Audit\*.sqlaudit’, DEFAULT, DEFAULT);
GO

— 注意：sys.fn_get_audit_file是一个表值函数，不能直接创建索引
— 可以考虑将审计日志导入到表中，然后创建索引

执行结果：

name status_desc audit_file_path max_file_size_mb max_rollover_files
———- ———— ————————- —————- ——————
FGEduAudit ON E:\SQLServer\Audit\ 500 20

(1 row affected)

Commands completed successfully.

name status_desc audit_file_path
———- ———— ————————
FGEduAudit ON E:\SQLServer\Audit\

(1 row affected)

Commands completed successfully.

(3 rows affected)

Commands completed successfully.

Part05-风哥经验总结与分享

5.1 SQLServer审计日志最佳实践

SQLServer审计日志最佳实践：

合理规划审计范围：根据业务需求和合规要求确定审计范围，避免过度审计
选择合适的审计目标：根据存储需求和性能考虑选择合适的审计目标
定期备份审计日志：确保审计日志的安全性和可用性
定期分析审计日志：及时发现异常行为和安全问题
优化审计性能：平衡审计的完整性和系统性能
建立审计日志管理策略：包括存储、保留、归档和清理策略

更多视频教程www.fgedu.net.cn

5.2 SQLServer审计日志常见问题

审计日志常见问题：

性能影响：过度审计会影响SQLServer性能，应合理配置审计范围
存储空间：审计日志会占用大量存储空间，应制定合理的存储策略
权限问题：SQLServer服务账户需要审计日志目录的写入权限
审计日志丢失：应定期备份审计日志，防止丢失
分析困难：审计日志量大，分析困难，应使用工具辅助分析

学习交流加群风哥微信: itpux-com

5.3 SQLServer审计日志性能优化

审计日志性能优化：

优化审计范围：只审计必要的事件和操作
使用文件存储：文件存储通常比Windows事件日志性能更好
合理设置文件大小：避免频繁创建新文件
使用快速存储：将审计日志存储在高速存储设备上
定期清理和归档：避免审计日志文件过多
使用并行查询：分析审计日志时使用并行查询提高性能

学习交流加群风哥QQ113257174

风哥提示：审计日志是SQLServer安全的重要组成部分，建立完善的审计体系可以帮助发现和预防安全问题，确保数据安全和合规要求。

更多学习教程公众号风哥教程itpux_com

from SQLServer视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

SQLServer教程FG036-SQLServer审计日志配置实战

目录大纲

内容简介

Part01-基础概念与理论知识

1.1 SQLServer审计日志概念

1.2 SQLServer审计日志类型

1.3 SQLServer审计日志架构

Part02-生产环境规划与建议

2.1 SQLServer审计日志规划原则

2.2 SQLServer审计日志存储策略

2.3 SQLServer审计日志保留策略

Part03-生产环境项目实施方案

3.1 SQLServer审计日志配置

3.2 SQLServer审计日志管理

3.3 SQLServer审计日志监控

Part04-生产案例与实战讲解

4.1 SQLServer审计日志配置案例

4.2 SQLServer审计日志分析案例

4.3 SQLServer审计日志故障处理案例

Part05-风哥经验总结与分享

5.1 SQLServer审计日志最佳实践

5.2 SQLServer审计日志常见问题

5.3 SQLServer审计日志性能优化

相关推荐

联系我们