数据库的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中, 我们可以对我们关心的对象进行不同时间的告警和相应处置.

    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

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

希望以上能够帮到你.

Make work and life simple.

ITPub博客

SQL Server数据库DDL变更监控

首先建立一个触发器Trigger

建立自定义的存储过程Stored Procedure

最新文章