THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Simple Monitoring for Data Changes

One task I've had to tackle repeatedly goes like this: "whenever the data in table_X gets modified accidentally by the folks in <pick a department> the application breaks. And we can't lock that table, because the vendor-supplied app has no security feature to keep people out of there."

It's no less common for being sort of a hacky problem. The knee-jerk answer might be, "fix the application" - but many businesses buy their software, so that's not always an option. There are a few different approaches one could take, with pros and cons:

A trigger seems obvious, but when I've used that method in the past, I have run into some issues. First, in theory one should be able to add a trigger to any application -- but in practice it can be a little dicey, especially if the app has a pile of complicated trigger logic already. Second, for better or worse, many applications issue SQL updates to tables as part of a "click save" operation that post all the same values back to a table, so an update occurs, but no values are modified. So, implied in the problem statement is a subtle issue: update statements have to be permitted, while update statements that alter values are not. Third, it's not clear what a trigger like this should do: if it halts processing and prevents changes to the table, again the application might break. If, instead, it has some hard-wired notification method (e-mailing from triggers - yikes!) then the app might continue to work, but the notification will be brittle and prone to failure in the future. Finally, if values are changed, it might not be trivial to restore the correct values if there's no record of what those correct values should be.

The last time through this issue, I decided to solve it with a more generic method, if only not to have to touch it again. In order to be rigorous I really needed these features:

  • The solution should report errors through the company's standard operations channel, which is a service that monitors the OS event logs for errors, instead of some custom SQL Mail or Agent job, etc. that would need to be separately maintained.
  • In the event of a problem, the correct values need to be close at hand, to put them back in place.
  • Legitimate changes should be allowed without undue effort (which implies that the monitoring solution should never have values hard-coded into it, because it would then need to be updated when the underlying data legitimately needs modification).
  • The solution should generalize to any table, so that I can stop writing specific triggers for each case.

Solution

Step 1

Once we've identified the tables that are the object of our affection, make a copy of the data in each table to a corresponding "template" table. The template preserves the desired state. Note that this whole conversation is relevant only to relatively small sets of data, such as application config tables; if you have a huge table to preserve, this won't be the best method:

SELECT * INTO dbo.template_vitalAppConfig FROM dbo.vitalAppConfig

When you have legitimate changes to the data, this template table will need to be updated or just paved over. 

Step 2

Create a stored procedure that uses dynamic SQL to compare the content of any pair of tables. For this I started with a neat method for table comparison published by Jeff over at SQL Team, and extended it to allow the use of any table. Using a method that works for any table makes it much simpler to solve this issue the next time around:


CREATE PROCEDURE [dbo].[validateConfigTable](
   @schemaname NVARCHAR(128), @tablename NVARCHAR(128)
) AS 


/* This is a monitoring procedure intended to compare config 
tables against known data in template tables and return any discrepancies

Given a table name and schema name, this proc will look for 
that table and a partner table called "template_<table_name>".

Example: dbo.myconfig => dbo.template_myconfig

It will then return any differences between the two.

*/

BEGIN

   -- Compose dynamic statement that can compare any set of columns
   -- from Information_Schema views
   -- See http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

   DECLARE @columnlist NVARCHAR(MAX)
   DECLARE @sql NVARCHAR(MAX)
   DECLARE @schema NVARCHAR(128)
   DECLARE @table NVARCHAR(128)
   DECLARE @templatetable NVARCHAR(128)

   SET @columnlist = ''
   
   SELECT @columnlist = @columnlist
       + CASE WHEN LEN(@columnlist) = 0 THEN '' ELSE ', ' END
       + QUOTENAME( column_name ),
       @schema = QUOTENAME( table_schema ),
       @table = QUOTENAME( table_name ),
       @templatetable = QUOTENAME( 'template_' + table_name )
   FROM information_schema.columns
   WHERE table_schema = @schemaname
       AND table_name = @tablename 
   
   SET @sql = N' SELECT MIN(table_name) as table_name, ' + @columnlist + CHAR(13)
       +   N' FROM ( ' + CHAR(13) 
       +   N'   SELECT ''' + REPLACE( @table, '''', '''''' ) + N''' as table_name, ' 
       +      @columnlist + CHAR(13)
       +   N'   FROM ' + @schema + '.' + @table + CHAR(13)
       
       +   N'   UNION ALL ' + CHAR(13)
       +   N'   SELECT ''template_' + REPLACE( @table, '''', '''''' ) + N''' as table_name, ' 
       +      @columnlist + CHAR(13)
       +   N'   FROM ' + @schema + '.' + @templatetable + CHAR(13)
           
       +   N' ) allRows ' + CHAR(13)
       
       +   N' GROUP BY ' + @columnlist
       + CHAR(13) + N' HAVING COUNT(*) = 1; '
   
   PRINT @sql
   
   EXEC( @sql )
END

GO

Step 3

Use some scheduler or other to execute the procedure at intervals, which will compare the pairs of tables. If the tables are identical, the proc will not return any results. If any results come back, they are the differences between the two tables. Log an error if there are any differences. I am using a PowerShell script, but it's just as feasible to use T-SQL.

T-SQL Example:

DECLARE @numberDiscrepancies INT

EXEC validateConfigTable 'dbo', 'vitalAppConfig'

SELECT @numberDiscrepancies = @@rowcount

IF @numberDiscrepancies > 0
BEGIN 
   RAISERROR( 'Config table emergency!', 12, 10 )
END

GO

PowerShell Example (more verbose, but provides more control over how the logging happens):

# **************************************************************************
# This PowerShell script will check the content of a config table in 
# a SQL database for required rows/values
# If the values have been changed, an error is recorded in the application 
# event log.
#
# To run:
# Put the SQL Server connection string in a text file DBConnection.cfg, 
# on the first line of the file, then:
#
# powershell.exe .\thisScript.ps1
#
# **************************************************************************

$conn = new-object System.Data.SqlClient.SqlConnection
$sqlCmd = new-object System.Data.SqlClient.SqlCommand

# The connection string is expected in the first line of settings file 
# DBConnection.cfg:

$conn.ConnectionString = Get-Content .\DBConnection.cfg | Select-Object -First 1
$sqlCmd.Connection = $conn

Write-Host "Checking on connection: " $conn.ConnectionString

# Settings for what to put in the event log:

$logSource = "Your Custom Alerts"
$errorMsg = "Values in a configuration table have been modified."
$errorType = [System.Diagnostics.EventLogEntryType]::Error
$infoType = [System.Diagnostics.EventLogEntryType]::Information
$anyErrors = $false

# Connect to the database and validate content of config tables 
# against template config tables:

trap [Exception] {
	 write-host "Error: $_.Exception.Message"
	 $conn.Close()
	 throw "An Error Occurred."
}

$conn.open()

# List tables to validate here:
@(	"myTable", `
	"vitalConfigTable", `
	"anotherVitalConfigTable" ) | foreach {
	
	# Check each table in the list above:
	
	Write-host "Validating table" $_	
	
	$query = ( "EXECUTE dbo.validateConfigTable 'dbo', '" + $_ + "'" )
	$sqlCmd.CommandText = $query
	$results = $sqlCmd.ExecuteReader()
	
	if ( $results.HasRows ) {
		if ( !$log ) {
			$log = new-object System.Diagnostics.EventLog("Application")
			$log.Source = $logSource
		}
		$log.WriteEntry($errorMsg + " Table: " + $_, $errorType, 65000)
		Write-Host $errorMsg "Table:" $_
		$anyErrors = $true
	} 
	
	$results.Close()
}

$conn.Close()

if( !$anyErrors ) {
	$log = new-object System.Diagnostics.EventLog("Application")
	$log.Source = $logSource
	$log.WriteEntry( "Config tables were validated successfully.", $infoType, 65000)
}

Write-Host "Check complete. Errors, if any, will be in the application event log."

With one or the other monitor script running at intervals, you can trigger a notification through whatever standard channel your team has in place for server monitoring. If anyone out there has a cleaner method than this, I'd love to hear what you're doing. Cheers.

Published Monday, October 19, 2009 11:22 AM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

sqlity said:

This is a great method to compare tables. I extended a little bit on it, to make it work for a couple of cases that your solution does not cover, here: http://www2.sqlity.net/content/working-table-compare-approach

October 30, 2009 7:41 AM
 

merrillaldrich said:

sqlity - Nice! Excellent enhancements.

November 13, 2009 4:33 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement