?
Solved

MS SQL change alert.

Posted on 2013-05-21
13
Medium Priority
?
305 Views
Last Modified: 2013-07-05
Dear all,

Any way to make MS SQL server send an email alert (by operators) if any database structure change and what it is ?

Any third party tools needs for this ?

DBA100.
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 39183623
Yes, you can do it via DDL Triggers.
Please go through one of a sample trigger to capture all DDL changes in the database.

http://blog.extreme-advice.com/2012/10/25/capture-schema-change-in-sql-server-to-maintain-history/
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183862
thansk.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183875
For this:

"DDL_DATABASE_LEVEL_EVENTS"

What it includes ?

"[HostName] [varchar](50) NULL"

What is the hostname is about ? the hostname the change initial from ?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:marrowyung
ID: 39183884
Anyway to modify the information the trigger will add?  anyway to add more ?

"
[SystemUser] [varchar](100) NULL,

[CurrentUser] [varchar](100) NULL,

[OriginalUser] [varchar](100) NULL,
"
in what situation the 3 x users different ?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39183909
>> What is the hostname is about ? the hostname the change initial from ?

Yes, it is..

>> in what situation the 3 x users different ?

It will have difference if the login name and user name are different.
Kindly try running this for your SQL user session.

select SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN()
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183918
ok.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183923
do you know if the vesrion for SQL 2012 can use for any server running SQL server 2008 R2?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39183931
I've tried creating this trigger in SQL Server 2008 R2 and it works fine..
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39183963
ok, thansk.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39186672
do you have the SQL server 2005 try from their old article? Any difference in result ? it seems that the version for SQL 2005 is providing much limit information to return ?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39186689
Yes, SQL Server 2005 has limited set of events available for monitoring and the full set of events below for your reference:

SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms189871%28v=sql.90%29.aspx

SQL Server 2012
http://msdn.microsoft.com/en-us/library/bb522542.aspx
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39301559
rrjegan17,

it seems that the email format and email action is not here, right?

how can I customerizes the email message ?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39301758
You can use msdb.dbo.sp_send_dbmail procedure to send emails in the format you require.
More info about the procedure along with examples below:

http://msdn.microsoft.com/en-us/library/ms190307.aspx
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question