I’ve been working with customers at Sage for nearly fourteen years. In that time I’ve seen some interesting things. I’ve seen customers accidentally rebuild their RAID array with their production data on it. Rebuilding an array is equivalent to wiping the array and starting over, it means you lost everything. I’ve seen log files grow to nearly a terabyte in size. Heck, I've even seen systems go six months without backing up any of the data. Trust me when I tell you, I’ve seen some crazy things.
One thing I usually don’t see customers do is take advantage of SQL Server to it’s fullest potential. Take for example auditing, it’s a great tool that most people have never even heard of before. This is probably because database auditing (the most interesting part of the auditing feature set for me) is an enterprise edition feature (thanks again Bill). Customers do not seem to be purchasing Enterprise Edition in our market segment these days as much as they used due, I suppose due to the increased to the price point changes implemented in recent years.
For a full list of features by SQL Server edition, see here.
Having said that, here’s where SQL Server Database Auditing suddenly becomes a life saver: Enter the TRUNCATE TABLE command inside SQL Server.
If you look closely at the Table Maintenance function inside Sage ERP X3 (GESATB) you will notice a button titled “Clear.”
If you click on this button you will receive a message that says the following:
If you say yes to this then the table will be reset, translated into SQL jargon that means truncated. The identity column (ROWID) will be reset back to the beginning and all your data is gone. If the table was important to your daily operational needs, you best be dusting off your Business Continuity Plan and figuring out who to call as you have just set forth a series of events, the last of which is likely to restore your database, or at least that one object. For example, if the table was STOJOU then you’ve now orphaned important data to many of the transactions in your system. This is bad.
Now, you might be thinking, but I have a business continuity plan in place, I’ve thought ahead for this kind of problem, so I’m good. A Business Continuity Plan is like a disaster plan on steroids. Whereas a disaster plan tells you how to recover from a disaster, the former explains who to call in what situation, what timelines to follow in escalation protocols, and more importantly, how to keep your business running. It’s a business centric point of view as opposed to a technology disaster point of view.
If you’re lucky, the best thing you could have done was to restore your database immediately, or at least the object truncated such as STOJOU. But here’s the kicker, in order for your Business Continuity Plan to actuate you need a trigger, you need to be alerted that you have a problem in the first place. Every minute that goes by where someone truncated your STOJOU table and you don’t immediately restore said table you are putting your business in quandary. New data is being entered or imported at what seems like a staggering pace when you are under pressure. So how do you deal with this?
Database Audit + Database Mail
If you’ve already enabled Database Mail, and you have SQL Server Enterprise, you can then leverage Database Auditing. With both turned on you can get real time alerts anytime someone runs a TRUNCATE TABLE statement on your server, not just from within Sage ERP X3.
Creating The Audits And Alert
There are lots of walkthroughs on the net on how to build the audits. Brad has one for SQL 2008 here.
I also have posted some sample code for you here:
Essentially you need to create the server audit, then the database audit. And finally, you should create a SQL Agent Job to send an email in the condition you find a TRUNCATE TABLE command logged.
/**********************************************************************************/ /******************************* Audit Creation ***********************************/ /**********************************************************************************/ -- Create server audit IF NOT EXISTS(SELECT name FROM sys.server_audits WHERE name = 'Audit-DeleteCommands') BEGIN PRINT 'Creating new server audit' CREATE SERVER AUDIT [Audit-DeleteCommands] TO FILE ( FILEPATH = N'E:\SQLData\' ,MAXSIZE = 0 MB ,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = '37bc19b5-5d19-4998-9000-9e9800b25c70' ) END -- Enabled the server audit (not enabled by default) ALTER SERVER AUDIT [Audit-DeleteCommands] WITH (STATE = ON) -- Create database audit consuming the server audit resource USE [x3v6] IF NOT EXISTS(SELECT name FROM sys.database_audit_specifications WHERE name = 'DatabaseAudit-DeleteCommands') BEGIN PRINT 'Creating new database audit specification' CREATE DATABASE AUDIT SPECIFICATION [DatabaseAudit-DeleteCommands] FOR SERVER AUDIT [Audit-DeleteCommands] ADD (DELETE ON DATABASE::[x3v6] BY [DEMO]) WITH (STATE = ON) END
/**********************************************************************************/ /************************* Alert: Was TRUNCATE TABLE RUN? *************************/ /**********************************************************************************/ -- THIS CODE WOULD GO INSIDE YOUR SQL AGENT JOB ALERT DEClARE @results INT, @SQL VARCHAR(MAX) SET @SQL = 'SELECT a.event_time, DATEDIFF(MINUTE, ( SELECT DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),CURRENT_TIMESTAMP),event_time) -- Compensating for UTC Time ), GETDATE()) TimeDifferenceInMinutes, LTRIM(RTRIM(a.statement)), a.action_id, a.succeeded, a.object_name, a.server_principal_name, a.database_principal_name, a.server_instance_name, a.database_name, a.schema_name, a.file_name FROM sys.fn_get_audit_file(''E:\SQLData\*.sqlaudit'',default,default) a WHERE LOWER(statement) LIKE ''%truncate%'' AND DATEDIFF(MINUTE, ( SELECT DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),CURRENT_TIMESTAMP),event_time) -- Compensating for UTC Time ), GETDATE()) <= 240 -- 4hours' EXEC(@SQL) SET @results = @@ROWCOUNT IF @results > 0 BEGIN DECLARE @myBody VARCHAR(MAX) SET @myBody = 'The following TRUNCATE TABLE command was run recently. Please take immediate action to determine root cause. A database restore may, or may not be required depending on the specific table that was truncated.' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ADMIN', @recipients = '[email protected]', @body = @myBody, @body_format = 'HTML', @subject = 'ALERT CONDITION: TRUNCATE TABLE COMMAND HAS BEEN RUN - TAKE IMMEDIATE ACTION', @from_address = '[email protected]', @query = @SQL, @attach_query_result_as_file = 1, @query_result_width = 32767 END
So What Does This Mean?
Well, if you've run the code above, you would have created two new objects inside SQL Server. You should now have a server audit and a database audit, which look like this:
If you truncated a table inside the Sage ERP X3 database at this point, and then you queried the audit file we just created, then you would see an output like the following (click on it to expand and make it easier to read). Notice that the actual Truncate Table statement was trapped, along with the time it happened. You could now write scripts using the MinutesAgoWhenCommandWasRun statement that support your Business Continuity Plan.
Conclusion
If you aren't aware of database auditing, or you are aware but haven't thought of a good use case, then I hope this article has given you at least one compelling reason to consider it. The question you should ask yourself at this point is, if someone in your organization accidentally truncated the STOJOU table today, how long until you noticed and took the correct action at the right time? Could you recover quickly enough?