Introduction
How Can We Keep Close eyses on Monitoring Schema Change in Any Database of SQL Server? How Can we keep track of these changes and Send Alert Email to Concern Persons to Inform About this change?
This is a kind of Administrator Activity Which We will learn in this Article. For this You Need to have SQL Mail Profile Configured in your SQL Server to send Email Alert From SQL Server.
Scenario
When Multiple Developer works on a Project and they do changes in database schema very frequently in initial phase of development at that time it will be difficult for the SQL or BI Developer who is associated with creation of Data Archival ETL. Change in base tables Schema can lead to Scheduled Job Failure due to alteration in column data type or size, or will loose information from newly added column if release was not properly planned. It is also possible someone can do change without any release note. so to avoid this all problems we can create Alert to send Email to Concern Person whenever any change in database schema takes place.
Here is Solution of the Problem
We First Create a Table which will be used to store records of DML Script used to change database Schema.
Then We will use System Level Trigger to Observe changes in schema in any database of SQL Server and Insert Record in above table with Script command used for Data Manuplation Operation(DML) , this way we can keep track of history of Schema changes done Over a period of time.
At last We Create Trigger on above Table which will Send Email to Admin to inform about change in Schema whenever new record get inserted.
Step 1 : Create Database (DBAdmin) In Your SQL Server.
Create Database DBAdmin
Go
Step 2 : Create Table(DBSchema_Change_Log) in Database (DBAdmin)
Select Database DBAdmin -->Execute Below Script to Create Table ( DBSchema_Change_Log).
USE [DBAdmin]
GO
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
Step 3 : Create Trigger at Server Level and Enable it.
Create System Level Trigger (Schema_LogInTable_DDL ) , so whenever change (Create / Alter/ Drop) occur in any database Schema so it will insert Event detail with script command used.
EVENTDATA function to create a log of events, Returns xml information about server or database events. this function is called when an event notification fires, and the results are returned to the specified service broker. EVENTDATA can also be used inside the body of a DDL or logon trigger.
Here we have used Variable of XML Datatype (@eventInfo) to store resultset returned by Eventdata function and used Query Method to retrieve Values of Resultset from Variable.
Query
| Result
|
| ('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') | Event Occurrence Time, When Command Executed
|
| ('data(/EVENT_INSTANCE/LoginName)') | Gives you Login Name used while Execution of Script
|
| ('data(/EVENT_INSTANCE/DatabaseName)' | Database Name On Which change Occured
|
| ('data(/EVENT_INSTANCE/SchemaName)') | Schema Name of the Database
|
| (data(/EVENT_INSTANCE/ObjectName)) | Name Of the Object e.g. TableName
|
| (data(/EVENT_INSTANCE/ObjectType)') | Type Of the Object e.g Table
|
| (data(/EVENT_INSTANCE/TSQLCommand/CommandText)') | T-SQL Command used to change the Schema
|
--Create trigger on system level which can keeps note down changes in any database and insert record in specified table
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
Step 4 : Create Trigger On Table DBSchema_Change_Log in database DBAdmin
Create Trigger on Table (DBSchema_Change_Log) for insert and update whenever new record inserted or updated in this table it will send Email Alert to concer person.
Here we have done Formating of Message to create HTML Body of Email.
--create trigger on your above table which will send you email
USE [DBAdmin]
GO
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
Observer different Objects Created in Object Explorer of SQL Server Management Studio.
1. You can See Database DBAdmin
2. (DBSchema_Change)Table Within DBAdmin
3. (Schema Change Notification) Trigger On Table (DBSchema_Change)
4. (SchemaChange_LoginTable_DDL) Server level Trigger under Server Objects
Do the Testing of All Above Configuration Now
If you have not configured SQL mail Profile then simply comment the TSQL Query used for sending Email in Step 4 it is placed betweeb start and end comment.
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]
Output Result
Here you are ready to do monitoring on your SQL Server, If You Like this Article Please Do Not Forget to Vote For Me..
Credits
Source code contains reference to the following where appropriate
Copyright
By uploading my code to codeproject.com I assume I inherit all open source terms of use, licenses and those specified by codeproject.com. However if you use this code for any purpose I would really like to hear about it. It is my belief that by referencing the credited people I demonstrate the ability to effectively read and re-use source code, rather than re-invent the wheel. I expect you would do the same.
Enjoy SQLization.
