SQL Server Performance

SQL Server Auditing or monitoring DBA activitys

Discussion in 'ALL SQL SERVER QUESTIONS' started by sachinonnet, Jul 23, 2012.

  1. sachinonnet New Member


    we want to implement solution to track DBA activity on SQL server 2008R2, activity's like:

    2. Generating BACKUP
    or anything they are doing on Database i.e creating users

    we started sql traces/profiles is there other better and efficient way to monitor/ log these activity, which will not kill server performance.

  2. Shehap MVP, MCTS, MCITP SQL Server

    You can use the new 2008 auditing techniques as follows:

    1- Server Auditing for such activities below :

    · Backup/Restore
    · Users creation
    · Users attributes change
    · Roles creations/changes
    · Schema objects change
    · Server objects change
    · Failed logins
    · …..etc

    To create a server auditing , You might have check the follow MSDN blog : http://msdn.microsoft.com/en-us/library/cc280386.aspx

    2- Data change tracking for such Insert /Update /Delete actions on each table,

    For how to you use it , might have check the following MSDN blog: http://msdn.microsoft.com/en-us/library/cc280462(v=sql.105).aspx

    For SQL profiler , I don't recommend to use it unless it is highly needed and for a certain time interval not all of the time due to its potential impacts on DB Server particularly if it capture lots of events that are repeated frequently

    However if it still insist to use SQL profiler , you can use SQL tracer better than SQL profiler to save network bandwidth by more than 70 %.......

    For how to use SQL tracer ,you can check the following MSDN blog: http://msdn.microsoft.com/en-us/library/ms190362(v=sql.105).aspx
  3. RichardJenson New Member

    For tracking the activities regarding who, what, when and where for the things like:

    1) Database Backup creation whether from one SQL server to another server or from one Server to another drive.
    2) Database Restore creation.
    3) Deletion of Tables.
    4) Failed and Successful Logins.
    5) Create, Alter, Delete for the Database Replication and many more...

    These activities can be tracked from many third party utility tool also and I can suggest you to try in Lepide's tool which can perform all of these tasks quite easily and with better clarity to audit such tasks.

Share This Page