SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Audit, Database Snapshots and Extended Events


SQL Audit, Database Snapshots and Extended Events

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15085 Visits: 14396
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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15085 Visits: 14396
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
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15085 Visits: 14396
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search