3

I am using Sql Server 2014, Standard Edition. My objective is to trace the history of operations on the tables (insert, update, delete), and to display on the web so the users are able to tell what happened to the data in the past.

So, I came to know there is a partial support of Audit feature in the Sql Server Standard Edition, and was able to log some operations, as attached.

enter image description here

My question is, how to make those logs stored inside a proper Sql Table so that the records could be accessed, retrieved and displayed on the web page?

Currently, the Audit Destination allowed: File, Security Log, Application Log ... which don't seem to be able to be retrieved directly from an ASP.NET API.

Is it possible to log into an SQL Table?

P.S.:

(Correct me if I am wrong). Many have said that the Standard Edition only allows Server-Level Audit, but NOT Database Level Audit. However, for some unknown reasons, if I added these 7 audit types (yup, 7 or none), I am able to capture and view the database level operations.

enter image description here

5

Capturing DML operations on individual tables is among the auditing features that are not available in Standard Edition.

If you want to trace DML operations, another possible hook is Extended Events. You won't be able to use the auditing package, but you could use lock acquired events to capture read and write operations.

I blogged about it here: https://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/

Other options are:

  • Triggers: this is easy to set up, even automatically by generating the trigger code. Downside is the increased write overhead.
  • App-side logging: If you have control over the application, you can inject the code needed to log accesses to the tables.
  • Hi i have edited my question, basically saying I am able to somehow capture and view database operation with Standard Edition. Here i would like to ask, when you mention "Downside of Triggers" is increased write overhead, so it means it take longer to write an operation into a table. Does this happen to SQL Audit? I mean since Audit does capture those operation details in the external file, would that poses an impact of the write? or is it an independent process that won't affect the performance? – zeroflaw Oct 6 '16 at 3:09
  • 1
    Audit is a low overhead operation and the collection/response happens in a separate thread. Compared to triggers, audit is extremely lightweight. Regarding the mysterious trick to enable auditing in standard edition, if it works as you say it's definitely a bug. A welcome one, but still a bug. I wouldn't rely on that behavior: it could get fixed anytime. – spaghettidba Oct 6 '16 at 4:34
  • Thanks for your info. I feel like level up after reading this. Anyway, allow me to test out for some time before deciding the answer. – zeroflaw Oct 6 '16 at 5:51
  • I personally think that "Extended Events" is a great idea, storage-wise and performance-wise, as it only logs 'statement' (like SQL Audit), instead of column values suggested by other samples in the 'Triggers' approach. However, since the log is also to be displayed to the user, I am thinking that those 'Statements' logged are to be processed before displayed in a better format, periodically. Perhaps that's the tradeoff. – zeroflaw Oct 7 '16 at 2:46
4

I use the following technique to capture audit logs and store them in a table. Perhaps you can adapt it for your needs. We actually use Enterprise edition, so we're probably able to capture more than what is available in Standard.

  • Configure your audit to write to a file system and limit the max size to something like 10mb. This will force new audit files to be created when that size is met. We use a shared folder because we have multiple servers writing audit information and we use ONE capture process that runs on a separate server.

  • Create a stored procedure (an example follows) that will process the audit files using sys.fn_get_audit_file. Create an Agent job to run the capture stored procedure periodically (we run every minute).

  • The stored procedure uses Powershell to 'move' audit files from the main storage folder to a 'staging' folder. We bypass 'locked' files that are still being written to by the audit process and will pick them up on the next capture cycle. The stored procedure reads the audit files using sys.fn_get_audit_file and inserts the information into a history table. You can also include a WHERE clause to limit the information that is selected from the audit file. We then invoke Powershell again to delete the 'staging' audit files.

Here is the stored procedure:

CREATE PROCEDURE [dbo].[SqlAuditCaptureAuditLogs]
AS
BEGIN
    SET XACT_ABORT ON

    EXEC xp_cmdshell 'powershell.exe "Move-Item \\<SharedFolder>\SQLAuditLogs-SC\*.sqlaudit \\<SharedFolder>\SQLAuditLogs-SC\SQLAuditLogs_Staging -ErrorAction SilentlyContinue"'
        ,no_output

    INSERT INTO SQLAUDIT.[dbo].[SQLAUDIT_HISTORY] (
        event_time
        ,sequence_number
        ,action_id
        ,server_principal_name
        ,server_instance_name
        ,database_name
        ,schema_name
        ,object_name
        ,statement
        )
    SELECT event_time
        ,sequence_number
        ,action_id
        ,server_principal_name
        ,server_instance_name
        ,database_name
        ,schema_name
        ,object_name
        ,statement
    FROM sys.fn_get_audit_file('<SharedFolder\SQLAuditLogs-SC\SQLAuditLogs_Staging\*.*', DEFAULT, DEFAULT)

    EXEC xp_cmdshell 'powershell.exe "Remove-Item \\<SharedFolder>\SQLAuditLogs-SC\SQLAuditLogs_Staging\*.* -ErrorAction SilentlyContinue"'
        ,no_output
END
  • Thanks for mentioning that i have to manually extract and process the external file using the Powershell. I would need some time to test your suggestion. Meanwhile, i did edit the question with some extra info. – zeroflaw Oct 6 '16 at 3:11
  • Powershell is not a requirement to manually extract the files or be involved in this process in any way. The files are written by the Sql Server auditing process. I merely used Powershell in my example because it made it easier for me to move files that weren't currently being written to (active) to a staging folder and then my stored procedure could retrieve from there. – Scott Hodgin Oct 6 '16 at 9:12
  • Understand. It is my bad for the wording. At first, i came across an approach using 'Trigger', which kind of effectively processing and saving the logs into the table. However, if using SQL Audit, it is storing 'Statements' into an external file. That seems to require the additional 'Extraction' plus 'Processing' of those statements before being displayed on the Web. – zeroflaw Oct 7 '16 at 1:12
  • Finally, i pick the other answer. Main reason, we couldn't afford Enterprise Edition for more experiments. But i have to thank you for mentioning using 'Store Procedure', 'Agent Job', SQL built-int function to read the file (i found another equivalent to read .xel file), and Powershell to remove the already-read file. – zeroflaw Oct 10 '16 at 5:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.