FAQShop.com : Can MOM Detect Database Schema Changes?

Can MOM Detect Database Schema Changes?

Can we detect database schema changes using MOM. i.e. can I configure MOM such that if a new table has been added to a particular database, MOM will raise an alert? Is this functionality built into the MOM SQL pack ?

Contributed By: Baelson Duque [MSFT]
This functionality is not in the SQL MP, but what you could possibly do is use SQL Triggers to detect this change then use xp_logevent to signify this change then catch that event with MOM. I'm not a SQL Expert, but I believe you can do that with SQL Triggers.

Contributed By: Tom Keane (MSFT)
The MOM SQL Server Management Pack does not alert on schema changes. To track schema changes, you will need to enable SQL Server Auditing. To enable auditing in SQL Server 2000:

Right click on the server name in the SQL Server Enterprise Manager
Select 'Properties | Security'
Then in the 'Audit Level' section, select the level of auditing you require.

Note that auditing will have an effect on the performance of your server.

You can then view the SQL Server audit events by using the SQL Server Profiler tool.