Best Ways to Audit a Production Instance

Question

Best Ways to Audit a Production Instance

CrazyMan

SSChampion

Points: 12560
More actions
September 30, 2008 at 9:33 am

#124831

Hi All
We all were using some sort of auditing on our production servers, there will be a wide range of argument saying Auditing must be custom made as per the requirement.
I need to audit each movement on my production server, what is the best way to do it on 2005?? is there any software that i can use, or is there any other way that i can do this, Please help me on this.
Cheers
😀

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Are you looking to audit all data changes, or all queries run against the server?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

CrazyMan SSChampion Points: 12560 More actions · Answer 2

all Quries that run aganist the SQL server, its run time etc, IO, Process etc....

Its to monitor all the users who are using live servers to get the reports, i know that its not a good idea to use live servers to get reports, since its been used for a long time, it takes some time for me to change the attitude, i just want to look who is the worst performer 🙂

Cheers

🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Then you're going to need some form of trace running. I recommend a server-side trace as it will have the least impact. Set up the trace within profiler, tracing the events that you're interested in, get profiler to generate a script and then put that script into a job.

Make sure that you're tracing to a fast drive, one that doesn't have the data or log files on it, and make sure that the drive has a lot of free space.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luke L SSC-Dedicated Points: 35547 More actions · Answer 4

If you are looking for worst performers, you might want to take a look at setting up a profiler trace and then reviewing it. Just be careful a) where you store the trace in other words don't run your server out of space and b) pay attention to the performance impact of doing it. There are third party utils that basically do the same thing, but allow you to specifiy a threshold or queries that take more than 3 seconds to execute for instance.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Luke L (9/30/2008)
There are third party utils that basically do the same thing, but allow you to specifiy a threshold or queries that take more than 3 seconds to execute for instance.

That can be done with profiler as well. Put a filter on the duration column. 3000000 for 3 sec. (in 2005, 2008 duration is measured in microseconds)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

CrazyMan SSChampion Points: 12560 More actions · Answer 6

Thanks Guys, But iam looking to store this on a database for future use, we can insert trace into database, if iam going to do this on server side, will there be any performance problems for this trace hitting the database,

In other words i want to monitor this 24X7

Thanks

🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Trace to file, set a reasonable size for the files. Once a file is no longer been written to, load that into a database (preferably on a different server) with the fn_trace_getdata function.

Server side traces can only write to file, and using a client-side (profiler) writing to a database is the method of tracing with the highest impact on the traced server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

CrazyMan SSChampion Points: 12560 More actions · Answer 8

Thanks Guys, I will try this and let you know once completed.

Thanks Once again

🙂