O'Reilly logo

Microsoft® SQL Server® 2008 Bible by Uttam Parui, Mike White, Paul Nielsen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Part VIII. Monitoring and Auditing

IN THIS PART

Wow! SQL Server has seen an explosion of monitoring and auditing technologies. SQL Server 2000 and before offered these traditional monitoring technologies:

  • Trace and Profiler

  • System Monitor and Performance Monitor

  • DML triggers and custom audit trails

  • Wait states

SQL Server 2005 added:

  • Dynamic management views

  • DDL triggers (Logon triggers with SP2)

  • Event notification

  • SSMS reports

  • Performance Dashboard (downloadable add-on)

SQL Server 2008 doubles the core monitoring technologies with the following:

  • Extended events

  • SQL Server auditing

  • Change tracking

  • Change Data Capture

  • Management Data Warehouse

  • Policy-Based Management (covered in Chapter 43)

If SQL Server is the box, then this part is about the many ways to be a SQL whisperer and listen to the box. The table on the following page clearly delineates the monitoring and auditing functions available in SQL Server.

Technology

Introduced

Events Tracked

Data Available

Performance

DML Triggers—fires T-SQL code on table events

Beginning of time

Instead Of or After Insert, Update, Delete, Merge

Inserted and deleted tables, all columns, all changes with user context

Synchronous within transaction; depending on width of table and amount ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required