locked
SQL 2008 Auditing method least performance overhead? RRS feed

  • Question

  • Hello, with the various methods of auditing in SQL 2008 what would be the least performance overhead method in capturing all successful DB connections (LoginName, DateTimeConnected, DatabaseName in being 3 necessary info/columns but maybe nice to have more info like Client/IP, Application, etc... but least performance as main method) ?  Ultimate goal is to track over a period of time which DBs on the SQL instance isn't being logged into and no longer being used.  Thanks in advance.
    Monday, March 5, 2012 11:37 PM

Answers

  • I agree with Uri. Server side traces are a lightweight method and will answer your needs. I use them to audit the error log, user actions, gather performance figures...

    Use Profiler to create a trace and its filters, then start and stop it. Then click File > Export > Script Trace Definition. You can use that script to create a job to start the trace.

    Refer to http://www.sqlservercentral.com/blogs/sqldbauk/2010/03/14/setting-up-a-server-side-trace/ for more info.

    Remember you will not be able to save the trace direct to a table. You can only use trace files (*.trc).

    Here is a tip if you don't want to overwrite your trace file every time you start the trace. When specifying the file name, use this code:

    SET @fileName = N'C:\MyTraces\MyErrorLogTrace' + CAST(DATEPART(YEAR, @date) AS NVARCHAR(30)) + CAST(DATEPART(MONTH, @date) AS NVARCHAR(30)) + CAST(DATEPART(DAY, @date) AS NVARCHAR(30)) + CAST(DATEPART(HOUR, @date) AS NVARCHAR(30)) + CAST(DATEPART(MINUTE, @date) AS NVARCHAR(30)) + CAST(DATEPART(SECOND, @date) AS nvarchar(30))

    Good luck, and if you have any questions, post them here.


    SQL Server Database Administrator


    Tuesday, March 6, 2012 4:29 PM
  • Thanks to you both, much appreciated.  That's pretty much what I thought also which please correct me if I'm wrong but was also thinking of SQL 2008's SQL Audit but looks like it doesn't capture the intended database name or its database ID in utilizing the 'SUCCESSFUL_LOGIN_GROUP' which rules that out?
    That's right. This event class is limited.

    SQL Server Database Administrator

    • Marked as answer by Iric Wen Thursday, March 15, 2012 2:12 AM
    Tuesday, March 6, 2012 5:46 PM
  • You can use logon triggers to restrict logon hours, track logon information or deny logon from a specific app.

    Logic must be as simple as possible, because the code will be executed for every logon. Be careful not to bloat a logon trigger with lots of code. Just remember will be inserting some delay to every logon.

    I don't believe this is a best practice. I would rather prefer to use a server side trace.


    SQL Server Database Administrator

    • Marked as answer by Iric Wen Thursday, March 15, 2012 2:12 AM
    Tuesday, March 6, 2012 6:18 PM

All replies

  • SQL Server Profiler server site trace

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, March 6, 2012 6:52 AM
  • I agree with Uri. Server side traces are a lightweight method and will answer your needs. I use them to audit the error log, user actions, gather performance figures...

    Use Profiler to create a trace and its filters, then start and stop it. Then click File > Export > Script Trace Definition. You can use that script to create a job to start the trace.

    Refer to http://www.sqlservercentral.com/blogs/sqldbauk/2010/03/14/setting-up-a-server-side-trace/ for more info.

    Remember you will not be able to save the trace direct to a table. You can only use trace files (*.trc).

    Here is a tip if you don't want to overwrite your trace file every time you start the trace. When specifying the file name, use this code:

    SET @fileName = N'C:\MyTraces\MyErrorLogTrace' + CAST(DATEPART(YEAR, @date) AS NVARCHAR(30)) + CAST(DATEPART(MONTH, @date) AS NVARCHAR(30)) + CAST(DATEPART(DAY, @date) AS NVARCHAR(30)) + CAST(DATEPART(HOUR, @date) AS NVARCHAR(30)) + CAST(DATEPART(MINUTE, @date) AS NVARCHAR(30)) + CAST(DATEPART(SECOND, @date) AS nvarchar(30))

    Good luck, and if you have any questions, post them here.


    SQL Server Database Administrator


    Tuesday, March 6, 2012 4:29 PM
  • Thanks to you both, much appreciated.  That's pretty much what I thought also which please correct me if I'm wrong but was also thinking of SQL 2008's SQL Audit but looks like it doesn't capture the intended database name or its database ID in utilizing the 'SUCCESSFUL_LOGIN_GROUP' which rules that out?
    Tuesday, March 6, 2012 5:27 PM
  • Thanks to you both, much appreciated.  That's pretty much what I thought also which please correct me if I'm wrong but was also thinking of SQL 2008's SQL Audit but looks like it doesn't capture the intended database name or its database ID in utilizing the 'SUCCESSFUL_LOGIN_GROUP' which rules that out?
    That's right. This event class is limited.

    SQL Server Database Administrator

    • Marked as answer by Iric Wen Thursday, March 15, 2012 2:12 AM
    Tuesday, March 6, 2012 5:46 PM
  • Thanks Marcelo.  Also please correct me if I'm off in that if I were to create my own customized server level Logon Trigger that tracks successful db logons inserting into my own table this performance overhead is rather costly which server side trace would be better still?
    Tuesday, March 6, 2012 5:56 PM
  • You can use logon triggers to restrict logon hours, track logon information or deny logon from a specific app.

    Logic must be as simple as possible, because the code will be executed for every logon. Be careful not to bloat a logon trigger with lots of code. Just remember will be inserting some delay to every logon.

    I don't believe this is a best practice. I would rather prefer to use a server side trace.


    SQL Server Database Administrator

    • Marked as answer by Iric Wen Thursday, March 15, 2012 2:12 AM
    Tuesday, March 6, 2012 6:18 PM
  • My hunch is that also in that triggers might be a bit more costly.  Is this also accurate in that SQL 2008 SQL Audit uses Extended Events which like we mentioned earlier here doesn't have the capability of capturing DB name or DB ID info which ultimately server side trace would still be my best solution?

    Tuesday, March 6, 2012 6:26 PM
  • Yes, auditing uses the SecAudit package provided by the Extended Events. They are "a bit" more complex, and I don't think you should bother using auditing in your situation. Server side traces will be the easiest and less expensive solution. Though you can read more about extended events here.

    SQL Server Database Administrator


    Tuesday, March 6, 2012 7:24 PM