December 12, 2012 at 9:33 am
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
December 12, 2012 at 4:03 pm
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
December 16, 2012 at 5:22 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply