Sql-server – Tools or built-in functionality to alert upon table data changes

monitoringsql serversql-server-2012t-sql

We have the following (unfortunate) scenario: we have a database where on-the-fly changes are being made by developers as quick remedies to dictionary tables (order types, document types, fee distributions, and so forth). This wouldn't be an issue if these particular developers didn't forget to check their changes into TFS, but alas, we face it. This causes an issue where the database project(s) in TFS do not properly reflect what is in the database.

Essentially, I want receive an e-mail or other type of notification (table insert with necessary information) when a change is made to 15-20 tables, what the change was, and who committed the transaction.

What is the best method to monitor this type of behavior? We are willing to invest in tools that allow for this ability. I've checked out RedGate SQL Monitor, but it doesn't seem to have this feature set (or I am missing it).

I've also seen usage of triggers, but we have a large amount of tables that would require monitoring and I don't know the performance degradation that might incur and if we can collect the appropriate data. I do like the trigger approach where it would insert the information into a table ([dbo].[TABLE_UPDATES]) because it would allow us to build an SSRS report to run against and pull data as needed, but I'm not sure I 100% trust this as a solution.

We currently use RedGate DLM Dashboard for monitoring schema changes, and it has proven quite invaluable to receive those updates. We just want to expand it to the data itself.

Disclaimer: I know – terrible practice by developers to apply things directly to a database without checking in their changes, but we are currently having to deal with this hurdle and reign these individuals from wildly shooting at the hip. We are wanting a long-term solution for accountability purposes as our database and company grows.

Thanks in advance.

Best Answer

If you need the data changes themselves then you will probably need to use Triggers. You could convert all values to string, build an HTML table of those changes, and then send that via sp_send_dbmail. Please note that while sp_send_dbmail does accept a query to include the results of in the email (either in the body or as an attached file), it runs in a separate session so it won't have access to the INSERTED or DELETED pseudo-tables, nor even local temporary tables (and global temporary tables either might not exist when the query is run or it would at least interfere with multiple DML operations happening at the same time, even if those are unlikely). You could also include the login information from SESSION_USER and ORIGINAL_LOGIN().

Regarding performance and Triggers, there doesn't seem to be much reason for concern:

  • These are lookup tables and there aren't that many changes coming through.
  • The sp_send_dbmail command runs asynchronously via Service Broker.

Another option is to track the data automatically in your version control system (e.g. TFS). This does not mean that the changes are introduced automatically into release scripts, but the changes are tracked so that you can investigate what changed and then figure out how to best get it scripted into your release process. I wrote an article on SQL Server Central several years ago about doing just this: Exporting and Versioning Lookup Data: A Real-World Use of the CLR (free registration is required to read content on that site). That article discusses using a SQLCLR Stored Procedure to do a daily/nightly export of the lookup tables into delimited files. Please note that the SQLCLR Stored Procedure, DB_BulkExport, is found in the SQL# library (that I created), and was available in the Free version when the article was written, but is now only in the Full version and no longer available for free. However, there is always the option of writing ones own SQLCLR Stored Procedure to do the same thing.

The system that I developed and discussed in that article is based upon running via a SQL Server Agent job once per night. But we had hundreds of lookup tables. The effects of running nightly are that you miss incremental changes made during the day, and you aren't able to capture who made those changes. If you need each individual change and who did it, then you can rethink that approach to get kicked off from a Trigger that exports the data and calls whatever external program handles the commit into the versioning system. A SQLCLR Trigger might be able to accomplish the export and calling the "commit" program all at the same time since it does have access to the INSERTED and DELETED tables.

Related Question