数据库架构更改时发送 SQL Server 电子邮件警报

引言

我们如何密切关注 SQL Server 中任何数据库的模式变更监控？我们如何跟踪这些变更并将警报邮件发送给相关人员以告知这些变更？

这是一种管理员活动，我们将在本文中学习。为此，您需要将 SQL Mail Profile 配置 在您的 SQL Server 中，以便从 SQL Server 发送邮件警报。

场景

当多个开发人员在一个项目上工作，并且他们在开发初期频繁地更改数据库模式时，这对于与数据归档 ETL 创建相关的 SQL 或 BI 开发人员来说将非常困难。基本表模式的更改可能导致计划作业失败（由于列数据类型或大小的更改），或者如果发布计划不周，可能会丢失新添加列的信息。也有可能有人在没有发布说明的情况下进行更改。因此，为了避免所有这些问题，我们可以在数据库模式发生任何更改时创建警报，将邮件发送给相关人员。

这是问题的解决方案

我们首先创建一个表，该表将用于存储用于更改数据库模式的 DML 脚本的记录。

然后，我们将使用系统级触发器来监视 SQL Server 中任何数据库的模式更改，并将包含用于数据操作操作 (DML) 的脚本命令的记录插入到上述表中，这样我们就可以跟踪一段时间内的模式更改历史记录。

最后，我们在上述表上创建一个触发器，每当插入新记录时，该触发器就会向管理员发送邮件以告知模式更改。

步骤 1： 在您的 SQL Server 中创建数据库（DBAdmin）。

Create Database DBAdmin
Go

步骤 2： 在数据库（DBAdmin）中创建表（DBSchema_Change_Log）。

选择数据库 DBAdmin --> 执行以下脚本创建表（DBSchema_Change_Log）。

USE [DBAdmin] 
GO
/****** Object:  Table [dbo].[DBSchema_Change_Log]    Script Date: 3-Oct-2013 14:38:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DBSchema_Change_Log](
    [RecordId] [int] IDENTITY(1,1) NOT NULL,
    [EventTime] [datetime] NULL,
    [LoginName] [varchar](50) NULL,
    [UserName] [varchar](50) NULL,
    [DatabaseName] [varchar](50) NULL,
    [SchemaName] [varchar](50) NULL,
    [ObjectName] [varchar](50) NULL,
    [ObjectType] [varchar](50) NULL,
    [DDLCommand] [varchar](max) NULL
    
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

步骤 3： 创建服务器级别触发器并启用它。

创建系统级触发器（Schema_LogInTable_DDL），因此每当任何数据库模式发生更改（创建/修改/删除）时，它都会插入事件详细信息以及使用的脚本命令。

EVENTDATA 函数 用于创建事件日志，返回有关服务器或数据库事件的 XML 信息。此函数在事件通知触发时调用，并将结果返回到指定的服务代理。EVENTDATA 也可以在 DDL 或登录触发器的正文中使用。

这里我们使用了 XML 数据类型的变量 (@eventInfo) 来存储 Eventdata 函数返回的结果集，并使用了查询方法从变量中检索结果集的值。

查询	结果
('data(/EVENT_INSTANCE/PostTime)')),'T', ' ')	事件发生时间，命令执行时
('data(/EVENT_INSTANCE/LoginName)')	给出执行脚本时使用的登录名
('data(/EVENT_INSTANCE/DatabaseName)'	发生更改的数据库名称
('data(/EVENT_INSTANCE/SchemaName)')	数据库的架构名称
(data(/EVENT_INSTANCE/ObjectName))	对象名称，例如表名
(data(/EVENT_INSTANCE/ObjectType)')	对象类型，例如表
(data(/EVENT_INSTANCE/TSQLCommand/CommandText)')	用于更改模式的 T-SQL 命令

 
--Create trigger on system level which can keeps note down changes in any database and insert record in specified table
/****** Object:  DdlTrigger [[SchemaChange_LogInTable_DDL]]    Script Date: 03-Oct-2013 14:36:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [SchemaChange_LogInTable_DDL]
 
ON ALL SERVER 
 
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
 
--Fires only for CREATE / ALTER / DROP Table
 
AS 
DECLARE       @eventInfo XML
 
SET           @eventInfo = EVENTDATA()
 
 
INSERT INTO DBAdmin.dbo.DBSchema_Audit_Log  VALUES
 
(

 
       REPLACE(CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
 
       CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),
 
       CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/UserName)')),
 
       CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),
 
       CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),
 
       CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),
       CONVERT(VARCHAR(50),@eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),
 
       CONVERT(VARCHAR(MAX),@eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
 
) 
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--Enable the Trigger on all server so it will run whenever change occur in schema on any database
ENABLE TRIGGER [SchemaChange_LogInTable_DDL] ON ALL SERVER
GO
GO  
  

步骤 4： 在数据库DBAdmin中的表DBSchema_Change_Log上创建触发器

为表（DBSchema_Change_Log）创建插入和更新触发器，每当在此表中插入或更新新记录时，它都会向相关人员发送邮件警报。

这里我们对消息进行了格式化，以创建 HTML 邮件正文。

--create trigger on your above table which will send you email

USE [DBAdmin] 
GO
/****** Object:  Trigger [dbo].[SchmaChangeNotification]    Script Date: 03-Oct-2013 14:37:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create TRIGGER [dbo].[SchmaChangeNotification] 
   ON  [DBAdmin].[dbo].[DBSchema_Change_Log] 
   AFTER INSERT,UPDATE
   AS 
BEGIN

SET NOCOUNT ON;

DECLARE @Body NVARCHAR(MAX)
Declare @mailbody Nvarchar(max)

SET @Body = CAST(( SELECT top 1 X.RecordID AS 'td','',X.EventTime AS 'td','',X.LoginName AS 'td','',x.UserName AS 'td','',
       x.DatabaseName AS 'td','',x.SchemaName AS 'td','', x.ObjectName AS 'td','', x.ObjectType AS 'td','', x.DDLCommand  AS 'td',''
       from  DBSchema_Change_Log X order by X.RecordID desc
       FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))


 
SET @mailbody =  '<html><body>'+@Body +'</body></html>'


--print @mailbody
--Code Start for Sending Email using SQL Email Profile
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile_Mail_Live', -- replace with your SQL Database Mail Profile 
@body = @mailbody,
@body_format ='HTML',

@recipients = 'mubin4all@gmail.com;', -- replace with your email address
@subject = 'Schema Change Notification' ;
--Code End for sending Email
END 

在 SQL Server Management Studio 的对象资源管理器中查看创建的各种对象。

1.您可以看到数据库DBAdmin

2. DBAdmin 内的（DBSchema_Change）表

3. （Schema Change Notification）表（DBSchema_Change）上的触发器

4. （SchemaChange_LoginTable_DDL）服务器对象下的服务器级别触发器

现在对以上所有配置进行测试

如果您尚未配置 SQL 邮件配置文件，则只需注释掉第 4 步中用于发送邮件的 TSQL 查询，它位于开始和结束注释之间。

 
use DBAdmin 
go
--Create sample table to check Trigger on system level fires or not
create table TestTable
(
name varchar(100)
)
go
-- View Record Logged by trigger with SQL Script 
select * from [DBSchema_Change_Log]

输出结果

现在您已准备好监控您的 SQL Server，如果您喜欢这篇文章，请不要忘记为我投票。

致谢

源代码包含适当的引用到以下内容

• Microsoft Technet
• Microsoft MSDN

Copyright

通过将我的代码上传到 codeproject.com，我假设我继承了 codeproject.com 指定的所有开源使用条款、许可证和规定。但是，如果您出于任何目的使用此代码，我非常希望了解。我相信通过引用受信任的人，我展示了有效阅读和重用源代码的能力，而不是重新发明轮子。我希望您也会这样做。

享受 SQL 化。