locked
polcy based management and sql log auditing RRS feed

  • Question

  • what is the advantage of PBM,and what is the difference between normal error log,a trace and sql auditing,all the three them records all the changes and the ocurrances  in the log then what is the advantage of this new feature?
    Wednesday, August 10, 2011 1:08 PM

Answers

  • Hi babji talluri,
    As for the advantage of SQL Trace, it can be started and stopped using code, a job or another program. SQL Trace can be served for performance debugging.  However, SQL Server Audit is meant to provide full auditing capabilities and only auditing capabilities. It is designed with the following primary goals in mind:
    • Security– The audit feature, and its objects, must be truly secure.
    • Performance- Performance impact must be minimized.
    • Management– The audit feature must be easy to manage.
    • Discoverability- Audit-centric questions must be easy to answer.

    Whether to use SQL Trace or SQL Server Audit, it depends on the specific scenario. Please take a look at the following articles for more information:
    SQL Trace
    Auditing in SQL Server 2008
     

    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Friday, August 12, 2011 5:52 AM

All replies

  • Advantage is to create one policy for all user database such as AUTO_CLOSE is OFF or AUTO_SHRINK is  OFF to prevent from performance degradation  but it is small example of many ......  http://technet.microsoft.com/en-us/library/bb510667.aspx

    ERROR LOG, trace files have different goal.. Recording all 'move' in SQL Server to audit  later on


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, August 10, 2011 1:34 PM
    Answerer
  • Advantage is to create one policy for all user database such as AUTO_CLOSE is OFF or AUTO_SHRINK is  OFF to prevent from performance degradation  but it is small example of many ......  http://technet.microsoft.com/en-us/library/bb510667.aspx

    ERROR LOG, trace files have different goal.. Recording all 'move' in SQL Server to audit  later on


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, August 10, 2011 1:34 PM
    Answerer
  • also

    AUDITING
    Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. You can use several methods of auditing for SQL Server, as described in Auditing (Database Engine). Beginning in SQL Server 2008 Enterprise, you can also set up automatic auditing by using SQL Server Audit.

    There are several levels of auditing for SQL Server, depending on government or standards requirements for your installation. SQL Server Audit provides the tools and processes you must have to enable, store, and view audits on various server and database objects.

    You can record server audit action groups per-instance, and either database audit action groups or database audit actions per database. The audit event will occur every time that the auditable action is encountered.

    SQL Trace
    It is not uncommon to experience the occasional slow down of a SQL Server database. A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective actions to fix the problem. This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005.

    Error log
    View the SQL Server error log to ensure that processes have completed successfully (for example, backup and restore operations, batch commands, or other scripts and processes). This can be helpful to detect any current or potential problem areas, including automatic recovery messages (particularly if an instance of SQL Server has been stopped and restarted), kernel messages, or other server-level error messages.


    http://uk.linkedin.com/in/ramjaddu
    Wednesday, August 10, 2011 1:55 PM
  • thanks ram,but all the three are dealing with performancing tuning and and both trace and sql auditing are recoreding at the sytem level ocurrances,i will be thankfull if you can explain once again.

    Thursday, August 11, 2011 8:01 AM
  • thanks uri,can i have some more KT on PBM
    Thursday, August 11, 2011 8:02 AM
  • Hi babji talluri,
    As for the advantage of SQL Trace, it can be started and stopped using code, a job or another program. SQL Trace can be served for performance debugging.  However, SQL Server Audit is meant to provide full auditing capabilities and only auditing capabilities. It is designed with the following primary goals in mind:
    • Security– The audit feature, and its objects, must be truly secure.
    • Performance- Performance impact must be minimized.
    • Management– The audit feature must be easy to manage.
    • Discoverability- Audit-centric questions must be easy to answer.

    Whether to use SQL Trace or SQL Server Audit, it depends on the specific scenario. Please take a look at the following articles for more information:
    SQL Trace
    Auditing in SQL Server 2008
     

    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Friday, August 12, 2011 5:52 AM
  • thanks stephanie..yes sure i will read the articles you have mentioned.
    Wednesday, August 17, 2011 9:10 AM