Track changes to SQL Server 2000 and 2005 with one simple utility

The solution:
There are a few options to achieve my goal in SQL Server 2005, such as DDL triggers and event notifications. But SQL Server 2000 is more limited. To achieve my goal in both environments, I had to program a small utility that could run once a day or a few times a day, monitor changes in the respective system tables and send a message if changes are, in fact, detected. In order for that to work, SQL Mail (SQL 2000) or Database Mail (SQL 2005) must be configured already. I created a sp_send_dbmail stored procedure on my SQL 2000 instances that simply executes the xp_smtp_sendmail stored procedure.

Here are the example scripts I use in order to rec

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Microsoft SQL Server 2000 Is it possible to find disabled indexes in SQL Server 2000? FAQ: Creating and altering SQL Server tables FAQ: SQL Server comparison features SQL Server DBMS' growing popularity is no coincidence What is a SQL Server instance? Strange error when installing SQL Server 2000 Developer Edition Working with multiple versions of SQL Server Setting up automatic switchover between SQL Server instances How to get started learning about SQL Server All about data purging Microsoft SQL Server 2000 Research Microsoft SQL Server 2005 (Yukon) SQL Server Reporting Services Fast Guide SQL Server Service Broker Tutorial and Reference Guide Tips for tuning SQL Server 2005 to improve reporting performance SQL Server consolidation: Why it's an optimization technique Parent-child dimensions in SQL Server 2005 with Analysis Services MDX Enforcing data integrity in a SQL Server database SSIS error message due to installation problem on SQL Server 2005 Should you upgrade to SQL Server 2005 or SQL Server 2008? Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005 How to configure Database Mail in SQL Server 2005 to send mail Microsoft SQL Server 2005 (Yukon) Research Database Administration Top load balancing methods for SQL Server Performance implications of transaction log autogrowth in SQL Server The keys to database backup protection for SQL Server Understanding transparent data encryption in SQL Server 2008 Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server Implementing SQL Server 2008 FILESTREAM functionality Improving SQL Server full-text search performance Using the OPENROWSET function in SQL Server New replication features in SQL Server 2008 and what they mean to you

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary application server  (SearchSQLServer.com) comma-separated values file  (SearchSQLServer.com) DDBMS  (SearchSQLServer.com) entity-relationship model  (SearchSQLServer.com) full-text database  (SearchSQLServer.com) intelligent database  (SearchSQLServer.com) OLE DB  (SearchSQLServer.com) Quiz: Database Basics  (SearchSQLServer.com) relational database management system  (SearchSQLServer.com) SQL Server  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

eive notification when new objects are created in my SQL Server instance. You can use these same scripts for modifications and deletions of the corresponding server objects:

A more realistic solution:
You can apply the sample code – except for the trigger that will execute anyway – when a new job is added from a central location by executing it as remote stored procedures.

For example, create the following on server_A, server_B and server_C:

[IMAGE]
(Click on image to download script)

And run the following from server D (which is a central server) using Linked Servers:

exec Server_A.master.dbo.spDBA_NewDatabaseNotification
exec Server_B.master.dbo.spDBA_NewDatabaseNotification
exec Server_C.master.dbo.spDBA_NewDatabaseNotification
exec Server_D.master.dbo.spDBA_NewDatabaseNotification

Conclusion:
A simple way to receive notification about changes in your SQL Server objects, such as logins, databases and jobs, when you have both SQL Server 2000 and SQL Server 2005 is to monitor the delta of the system tables that hold the object's information or have a trigger on the object's table when it's possible.

[TABLE]