Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Audit, Database Snapshots and Extended Events Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2012 9:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
I have a request to capture all SELECT statements issued against a table within a Database Snapshot. SQL Audit would make fullfiliing this request simple in any user-database, however it appears there is a limitation of SQL Audit when it comes to Database Snapshots, namely that SQL thorws an error if issuing CREATE DATABASE AUDIT SPECIFICATION against a Snapshot because it is read-only. Is this completely accurate or am I missing something about SQL Audit that may allow it?

As it stands, unless someone can point out how I can use SQL Audit with a Snapshot, I am considering implementing my own Extended Events session. Initially I thought I might inspect all sql statements that select from a specific table in the Snapshot by filtering on the text to capture only queries that reference the table in question.

This response from Jonathan Kehayias to a similar question talks about possibly using a technique where we would capture the "sqlserver.lock_acquired event with the sqlserver.sql_text and sqlserver.tsql_stack actions" which is very clever but probably will not work on a Database Snapshot because as I understand it no locks are taken in Snapshots because it is a read-only database. I'll be testing that theory out soon enough though.

I do not want to use Trace. Does anyone else have any ideas about how else might this be achieved using SQL Audit, Extended Events or another technique?

The platform is 2008 R2 Enterprise Edition.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1395766
Posted Wednesday, December 12, 2012 4:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
opc.three (12/12/2012)
This response from Jonathan Kehayias to a similar question talks about possibly using a technique where we would capture the "sqlserver.lock_acquired event with the sqlserver.sql_text and sqlserver.tsql_stack actions" which is very clever but probably will not work on a Database Snapshot because as I understand it no locks are taken in Snapshots because it is a read-only database. I'll be testing that theory out soon enough though.

This turned out to be an interesting exercise. Locks are still taken in both the read-only database as well as the snapshot, but less than in a user-database and different locks from each other still. See the code comments for details.

It looks like I am going to implement an XE session like this:

CREATE EVENT SESSION test_session
ON SERVER
ADD EVENT sqlserver.lock_acquired
( ACTION (sqlserver.sql_text)
WHERE
-- my table
resource_0 = 459179422
-- my snapshot
and database_id = 16
)
ADD TARGET package0.asynchronous_file_target
(SET filename = N'X:\test_session.xel',
max_file_size = 1024,
max_rollover_files = 10,
increment = 32,
metadatafile = N'X:\test_session.xem')
WITH (TRACK_CAUSALITY = OFF, MAX_DISPATCH_LATENCY = 5 SECONDS);

Edited predicate and code comments after confirming we do not need a filter on mode. Only one lock is opened on the object, sometimes it is a SCH_S and others it is an IS depending on the isolation level.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1395915
Posted Sunday, December 16, 2012 5:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:19 PM
Points: 7,127, Visits: 12,655
At times, depending on the isolation level or table hints (which the reporting engine sometimes provides), multiple locks will be opened for a single query. Post-processing to roll these multiple lock events up for auditing purposes may become arduous so I am considering standing up a front-end database that will contain one VIEW per table in the snapshot to act as a pass-through. For example if there is a table in the snapshot named dbo.Product there will be a VIEW in the front-end database named dbo.Product that will contain the query SELECT * FROM SnapshotName.dbo.Product. I can then add a Database Audit Specification to the front-end database since it is not read-only. Keeping users away from directly querying the Snapshot (and losing the audit trail) should not be an issue because of the way the rest of the reporting area is setup.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1397032
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse