— 查看证书状态
SELECT
name,
subject,
start_date,
expiry_date,
thumbprint,
pvt_key_encryption_type_desc
FROM sys.certificates
WHERE name LIKE ‘TDECertificate%’;
GO

— 步骤2：监控TDE性能
— 查看TDE相关的等待统计信息
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE ‘%ENCRYPT%’ OR wait_type LIKE ‘%DECRYPT%’;
GO

— 查看TDE相关的性能计数器
SELECT
object_name,
counter_name,
instance_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE ‘%TDE%’ OR counter_name LIKE ‘%Encryption%’;
GO

— 步骤3：创建TDE监控作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’MonitorTDE’,
@enabled = 1,
@description = N’监控TDE加密状态’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’MonitorTDE’,
@step_name = N’Check TDE Status’,
@subsystem = N’TSQL’,
@command = N’
— 检查TDE加密状态
DECLARE @encrypted_databases INT;
DECLARE @expiring_certificates INT;

— 检查加密状态
SELECT @encrypted_databases = COUNT(*)
FROM sys.databases db
JOIN sys.dm_database_encryption_keys dek ON db.database_id = dek.database_id
WHERE dek.encryption_state != 3; — 3 = ENCRYPTED

IF @encrypted_databases > 0
BEGIN
RAISERROR(‘存在未完全加密的数据库: %d’, 16, 1, @encrypted_databases);
END;

— 检查证书过期情况
SELECT @expiring_certificates = COUNT(*)
FROM sys.certificates
WHERE name LIKE ‘TDECertificate%’ AND expiry_date < DATEADD(MONTH, 3, GETDATE()); IF @expiring_certificates > 0
BEGIN
RAISERROR(‘存在即将过期的TDE证书: %d’, 10, 1, @expiring_certificates);
END;

— 记录TDE状态
INSERT INTO dbo.tde_status_log (
collection_time,
encrypted_databases_count,
expiring_certificates_count
) VALUES (
GETDATE(),
(SELECT COUNT(*) FROM sys.databases WHERE is_encrypted = 1),
@expiring_certificates
);
‘,
@database_name = N’master’;
GO

EXEC dbo.sp_add_jobschedule
@job_name = N’MonitorTDE’,
@name = N’Daily’,
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@active_start_time = 000000,
@active_end_time = 235959;
GO

— 步骤4：创建TDE状态日志表
USE msdb;
GO

CREATE TABLE dbo.tde_status_log (
id INT IDENTITY(1,1) PRIMARY KEY,
collection_time DATETIME,
encrypted_databases_count INT,
expiring_certificates_count INT,
status_message NVARCHAR(MAX)
);
GO

— 步骤5：监控TDE备份
— 创建TDE密钥备份作业
USE msdb;
GO

EXEC dbo.sp_add_job
@job_name = N’BackupTDEKeys’,
@enabled = 1,
@description = N’备份TDE密钥’;
GO

EXEC dbo.sp_add_jobstep
@job_name = N’BackupTDEKeys’,
@step_name = N’Backup TDE Certificates’,
@subsystem = N’TSQL’,
@command = N’
— 备份TDE证书
DECLARE @backup_path NVARCHAR(256);
DECLARE @current_date NVARCHAR(20);

SET @current_date = CONVERT(NVARCHAR(20), GETDATE(), 112) + ‘_’ + REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 108), ‘:’, ”);
SET @backup_path = ‘E:\SQLServer\Keys\Backup_’ + @current_date + ‘\’;

— 创建备份目录
EXEC xp_cmdshell ‘mkdir ‘ + @backup_path;

— 备份所有TDE证书
DECLARE @cert_name NVARCHAR(128);
DECLARE cert_cursor CURSOR FOR
SELECT name FROM sys.certificates WHERE name LIKE ‘TDECertificate%’;

OPEN cert_cursor;
FETCH NEXT FROM cert_cursor INTO @cert_name;

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(‘BACKUP CERTIFICATE ‘ + @cert_name + ‘
TO FILE = ”’ + @backup_path + @cert_name + ‘.cer”
WITH PRIVATE KEY (
FILE = ”’ + @backup_path + @cert_name + ‘.pvk”,
ENCRYPTION BY PASSWORD = ”CertificatePassword123!”
);’);
FETCH NEXT FROM cert_cursor INTO @cert_name;
END;

CLOSE cert_cursor;
DEALLOCATE cert_cursor;

— 备份服务主密钥
BACKUP SERVICE MASTER KEY
TO FILE = @backup_path + ‘ServiceMasterKey.bak’
ENCRYPTION BY PASSWORD = ‘ServiceMasterKeyBackupPassword123!’;

— 记录备份操作
INSERT INTO dbo.tde_backup_log (
backup_time,
backup_path,
status
) VALUES (
GETDATE(),
@backup_path,
‘SUCCESS’
);
‘,
@database_name = N’master’;
GO

EXEC dbo.sp_add_jobschedule
@job_name = N’BackupTDEKeys’,
@name = N’Weekly’,
@freq_type = 8,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@active_start_time = 000000,
@active_end_time = 235959;
GO

— 创建TDE备份日志表
USE msdb;
GO

CREATE TABLE dbo.tde_backup_log (
id INT IDENTITY(1,1) PRIMARY KEY,
backup_time DATETIME,
backup_path NVARCHAR(256),
status NVARCHAR(50)
);
GO

执行结果：