SQL Server数据库DDL变更监控

数据库的DDL语言, 就是关系型数据库的三大类语言中的一种, 数据定义语言(Data Definition Language), 主要是数据结构和数据库对象的定义. 有CREATE , ALTER, DROP 等语句组成.

工作中经常需要对数据对象变更进行监控, 可能存在以下的场景:

监控所有的变更, 并保留变更记录.
比如对某些表结构的变更可能需要对其他的数据库的的表进行同步变更.
可能某些View的改变需要同步修改默写Stored Procedure 存储过程.
某些变更需要同步修改相应的应用程序.
经过研究, 可以通过全局触发器和自定义的监控Stored Procedure来实现.

首先建立一个触发器Trigger
触发器是一种特殊类型的存储过程，在数据库服务器中发生事件时自动运行。在DML语言, 即我们常用的INSERT, UPDATE 和 DELETE 操作, 可以触发特点的程序, 检查数据完整性, 同步更新等操作.

这次我们用DDL的Tigger, 建立一个全局的Server.

--============ Create Trigger ============
CREATE TRIGGER TRG_DDL_CHANGES ON ALL SERVER

FOR 
CREATE_TABLE, ALTER_TABLE, DROP_TABLE, 
CREATE_VIEW, DROP_VIEW, ALTER_VIEW

AS
BEGIN
...
END;
GO
记得需要排除对tempdb的监控, 程序处理的临时表都是创建到tempdb中的, 没有必要监控这种临时的对象.

--============ Don't log tempdb DDL ============
SET @_databaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)')
IF @_databaseName IN ('tempdb') RETURN

新建一个表来记录变更的记录.

CREATE TABLE dbo.DBA_DDL_CHANGE_LOG
(

LOG_ID INT IDENTITY PRIMARY KEY,
EVENT_DATA XML NOT NULL,
WARNING_TYPE VARCHAR(20) NULL,
WARNING_COMMENT NVARCHAR(200) NULL,
HANDLE_COMMENT  NVARCHAR(200) NULL,
HANDLE_BY VARCHAR(20) NULL

);
GO
记录变更的记录, 并把logId传入到自定义的存储过程中, 在这个SP中, 我们可以对我们关心的QQ账号拍卖平台对象进行不同时间的告警和相应处置.

INSERT  INTO dbo.DBA_DDL_CHANGE_LOG (event_data)
VALUES  (EVENTDATA());

SET @logId = @@IDENTITY
EXEC dbo.SP_DBA_MONITOR_DDL_CHANGE @logId

建立自定义的存储过程Stored Procedure
我建立了一张表, 用来定义一些存储监控对象的表, 如果对象在我的监控清单中, 就Raise Error, 并把事先定义好的消息提示给变更表结构的DBA, 如有必要, 可以回滚当前的变更操作.

SELECT

    @_eventType = event_data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(max)'),
    @_databaseName = event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)'),
    @_objectName = event_data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)'),
    @_objectType = event_data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(max)')

FROM PerfAnaly.dbo.DBA_DDL_CHANGE_LOG
WHERE log_id = @logId

SELECT @_warnignComment = COMMENT,

    @_warningPurpose = MONITOR_PURPOSE

FROM dbo.DBA_DDL_MONITOR WITH (NOLOCK)
WHERE DATABASE_NAME = @_databaseName

    AND OBJECT_NAME = @_objectName
    AND OBJECT_TYPE = @_objectType

--============ Check table is in Archive or not ============
IF @_eventType IN ('ALTER_TABLE', 'DROP_TABLE', 'DROP_VIEW', 'ALTER_VIEW') AND @_warnignComment IS NOT NULL
BEGIN

UPDATE  dbo.DBA_DDL_CHANGE_LOG
SET     WARNING_TYPE = @_warningPurpose, 
        WARNING_COMMENT = @_warnignComment
WHERE   LOG_ID = @logId

RAISERROR('### %s ###: %s !!!', 11, 1, @_warningPurpose, @_warnignComment)

END
以上, 通过一个触发器, 一个监控表和一个存储过程, 就可以实现记录变更记录, 提醒变更注意事项等功能.

希望以上能够帮到你.