auditing tracking and change monitoring technologies in microsoft sql server 2008 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Auditing, Tracking, and Change Monitoring Technologies in Microsoft SQL Server 2008 PowerPoint Presentation
Download Presentation
Auditing, Tracking, and Change Monitoring Technologies in Microsoft SQL Server 2008

Loading in 2 Seconds...

play fullscreen
1 / 44

Auditing, Tracking, and Change Monitoring Technologies in Microsoft SQL Server 2008 - PowerPoint PPT Presentation


  • 161 Views
  • Uploaded on

Required Slide. SESSION CODE: DAT318. Auditing, Tracking, and Change Monitoring Technologies in Microsoft SQL Server 2008. Adam Machanic Database Consultant SQLblog. About Me. Independent SQL Server and .NET Consultant Boston, MA. Author SQL Server 2008 Internals

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Auditing, Tracking, and Change Monitoring Technologies in Microsoft SQL Server 2008' - olinda


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
auditing tracking and change monitoring technologies in microsoft sql server 2008

Required Slide

SESSION CODE: DAT318

Auditing, Tracking, and Change Monitoring Technologies in Microsoft SQL Server 2008

Adam Machanic

Database Consultant

SQLblog

about me
About Me

Independent SQL Server and .NET Consultant

Boston, MA

AuthorSQL Server 2008 Internals

Expert SQL Server 2005 Development

Conference and INETA Speaker

Connections, PASS, TechEd, DevTeach, etc.

Co-Founder: SQLblog.com

The SQL Server Blog Spot on the Web

amachanic@gmail.com

agenda
Agenda

Change Detection: A Primer and Prehistory

Change Tracking: Which Rows Changed?

Change Data Capture: What Changed?

SQL Audit: Who Made a Change?

(or: Who Read My Data?)

agenda1
Agenda

Change Detection: A Primer and Prehistory

Change Tracking: Which Rows Changed?

Change Data Capture: What Changed?

SQL Audit: Who Made a Change?

(or: Who Read My Data?)

who cares what happened
Who Cares What Happened?
  • Your government cares.
    • Can you produce a full disclosure report on who accessed what, and when?
  • Your support team cares.
    • Metadata-driven applications?
    • When did that setting change that caused the app to crash?
  • You should care.
    • Your career just might depend on it.
rolling your own how not to audit
Rolling Your Own: How Not to Audit
  • Security Auditing: SQL Trace
    • No easy way of telling who accessed what
    • Can be expensive from a resource point of view
    • C2 and Common Criteria modes? Close, but not quite there
  • Data Auditing: Triggers
    • Easy to make a mistake, introduce bugs
    • Requires change to database logical model
    • Can create performance nightmares
the timestamp problem
The Timestamp Problem

T3:

App

Requests Changes

T0:

Txn 1 Starts

T1:

Txn 2 Ends

Time (T)

T4:

Txn 1 Ends

T1:

Txn 2 Starts

sql server 2008 major fixes
SQL Server 2008: Major Fixes
  • Three distinct technologies to help us figure out what happened
  • Microsoft v1.0 technologies: They have some pitfalls
  • All in all, they take us a long way towards a solid solution
agenda2
Agenda

Change Detection: A Primer and Prehistory

Change Tracking: Which Rows Changed?

Change Data Capture: What Changed?

SQL Audit: Who Made a Change?

(or: Who Read My Data?)

change tracking overview
Change Tracking: Overview
  • Answers the question: Which rows (keys) changed?
    • Does not store how the data changed
  • Synchronously populated during the transaction
    • Totally integrated into the Storage Engine
  • Available in all SQL Server 2008 editions
  • Use cases:
    • Disconnected cache update
    • ETL update (Type 3 SCD and similar)
ct configuration database level
CT Configuration: Database-Level

ALTER DATABASE AdventureWorks

SET CHANGE_TRACKING = ON;

  • Gets the database ready to track change
  • Recommend also enabling SNAPSHOT ISOLATION
  • Which databases are enabled?
    • sys.change_tracking_databases
ct configuration table level
CT Configuration: Table-Level

ALTER TABLE HumanResources.Employee

ENABLE CHANGE_TRACKING;

  • Signals the query engine to start tracking changes for the table
  • Optionally, can enable tracking of which columns were referenced in an update
change tracking internal architecture
Change Tracking: Internal Architecture
  • Database Commit Table
  • xact_id – transaction identifier
  • commit_ts – the external version #

1: Transaction Starts

xact_id generated

2: Rows Modified

xact_id used in base and change table

3: Transaction Committed

commit_ts generated

  • Base Table
  • xact_id – what transaction last modified the row?
  • (hidden column)
  • Change Table
  • xact_id
  • xact_sequence
  • xact _operation (I,U,D)
  • Base Table PK cols
it s enabled now what
It’s Enabled—Now What?
  • CHANGETABLE function
    • Returns version information on a per-key basis
  • VERSION mode
    • Returns, per key, the most recent version number
  • CHANGES mode
    • Returns, per key and original version, the net changes that have occurred since that version
tracking helper functions
Tracking Helper Functions
  • CHANGE_TRACKING_MIN_VALID_VERSION
    • Per table
    • What is the minimum available change version?
  • CHANGE_TRACKING_CURRENT_VERSION
    • For the database
    • What is the current maximum change version?
  • CHANGE_TRACKING_CONTEXT
    • Applied to a DML operation, allows you to store additional information along with the change
data and change acquisition workflow
Data and Change Acquisition Workflow

SNAPSHOT Transaction

?

Is MIN_VALID_VERSION

> [version from the last collection]

… or …

Does [version from the last collection] == 0

Call CHANGETABLE in VERSION mode

Get the initial snapshot

YES

Call CHANGETABLE in CHANGES mode

Get changes since the last collection

NO

Collect CHANGE_TRACKING_CURRENT_VERSION

Store for the next iteration

agenda3
Agenda

Change Detection: A Primer and Prehistory

Change Tracking: Which Rows Changed?

Change Data Capture: What Changed?

SQL Audit: Who Made a Change?

(or: Who Read My Data?)

change data capture overview
Change Data Capture: Overview
  • Answers the questions:
    • Which rows changed?
    • How did the data change?
  • Asynchronously populated, after the transaction
    • Uses the replication log reader
  • Only available in SQL Server 2008 Enterprise
  • Use cases:
    • All Change Tracking use cases
    • … plus general ETL, data undo scenarios, etc
cdc database level configuration
CDC: Database-Level Configuration

EXEC sys.sp_cdc_enable_db

  • Enables CDC metadata
  • Creates a database schema called “cdc”
  • Creates tables in the schema
  • Creates two SQL Server Agent jobs
    • One to fire up the log reader
    • One to purge history
cdc table level configuration
CDC: Table-Level Configuration

EXEC sys.sp_cdc_enable_table

  • Enables CDC for a given table
  • Creates one table in the cdc schema
    • cdc.[schema]_[tablename]_CT
  • Creates two functions in the cdc schema
    • cdc.fn_get_all_changes_[schema]_[tablename]
    • cdc.fn_get_net_changes_[schema]_[tablename]
cdc job configuration
CDC: Job Configuration

sys.sp_cdc_change_job

  • Change various aspects of collection and cleanup
    • @retention – Number of minutes that a row should be kept in the change table
    • @maxtrans and @pollinginterval – Controls how often polling is done and how many log records are read
  • MSDN: “Tuning the Performance of Change Data Capture in SQL Server 2008” – Steffen Krause
cdc helper functions
CDC: Helper Functions
  • sys.fn_cdc_get_min_lsn
  • sys.fn_cdc_get_max_lsn
    • Find the minimum and maximum LSNs currently in the store
    • To be used during the synchronization process
    • Similar in purpose to CT’s min and max functions
  • sys.fn_cdc_increment_lsn
    • Used to find the next LSN value as compared to the current stored value
    • Used to determine which range to synchronize
cdc row lifecycle workflow
CDC: Row Lifecycle Workflow

Step 1:

Rows Modified, Transaction Committed

Step 2:

Sometime later…

Log Reader kicks in, reads the changes from the log

Step 3:

Change information written to table in CDC schema

net changes cdc vs ct
Net Changes: CDC vs. CT
  • Synchronicity
    • CT: Synchronous, on-time delivery of changes
    • CDC: Potentially late delivery of changes
  • Transactional Impact
    • CT: 64 bytes per transaction + 26 bytes per row
    • CDC: 0 additional bytes per transaction
  • Database Impact
    • CT: None, after transactional impact
    • CDC: Up to 34 bytes per row, plus ALL data
agenda4
Agenda

Change Detection: A Primer and Prehistory

Change Tracking: Which Rows Changed?

Change Data Capture: What Changed?

SQL Audit: Who Made a Change?

(or: Who Read My Data?)

sql audit overview
SQL Audit: Overview
  • Answers the question: Who did what, and when?
    • Can tell you who queried a table
    • Can not tell you what they actually saw
  • Synchronously or asynchronously populated
  • Available only on Enterprise Edition
  • Use cases
    • General security auditing
    • Debugging
background extended events xevent
Background: Extended Events (Xevent)

Advanced tracing infrastructure—new in SQL Server 2008

Designed from the ground up for performance and scalability

Similar scope to SQL Trace, but much bigger surface area

SQL Audit is built on top of XEvent

audit terminology
Audit Terminology
  • Audit
    • A data collection session
    • Defines a target, maximum queue time, maximum file size, and whether a failure should take down the service
  • Audit Specification
    • For which events should the audit collect data?
    • Configured at the database or server level
    • Each Audit can contain many specifications: Up to one per database, one server-level
audit actions and groups
Audit Actions and Groups
  • Each type of event is called an “action”
  • Action Categories
    • Server
    • Database
    • Audit
  • Can work granularly or using predefined “action groups”, e.g.:
    • SCHEMA_OBJECT_ACCESS_GROUP
    • DATABASE_CHANGE_GROUP
audit data collection
Audit Data Collection
  • Audits can collect information in 28 different categories, depending on the action
    • Not all actions populate all columns
    • Maximum of 4000 characters of data for character data in audit records (e.g. T-SQL statement)
    • Some actions produce multiple audit records
  • Multi-row records will produce a sequence_no column so that you can piece together what happened
audit considerations
Audit Considerations
  • Audit is built on top of Xevent
    • Shouldn’t hurt performance too much
  • Sometimes you need to audit something for regulatory purposes
  • Exercise restraint
    • Just because you can doesn’t mean you should
sql audit tooling
SQL Audit: Tooling!
  • The SQL Server Management Studio has a team actually built an interface for this feature
  • Creating and modifying Audits is more or less a point-and-click process
  • Pulling out data can (and usually should) be done with T-SQL
  • Tip: Don’t forget to enable both the Audit and the Audit Specification(s)
summary
Summary
  • SQL Server 2008 provides powerful, flexible technologies to help you answer the toughest database question: What happened?
  • Use these features as needed, but in moderation to ensure that you don’t impact server performance
  • Be prepared for more and more use cases as regulatory bodies continue to work
dat track scratch 2 win

Required Slide

Track PMs will supply the content for this slide, which will be inserted during the final scrub.

DAT Track Scratch 2 Win
  • Find the DAT Track Surface Table in the Yellow Section of the TLC
  • Try your luck to win a Zune HD
  • Simply scratch the game pieces on the DAT Track Surface Table and Match 3 Zune HDs to win
resources

Required Slide

Resources

Learning

  • Sessions On-Demand & Community
  • Microsoft Certification & Training Resources

www.microsoft.com/teched

www.microsoft.com/learning

  • Resources for IT Professionals
  • Resources for Developers
  • http://microsoft.com/technet
  • http://microsoft.com/msdn
slide41

Required Slide

Complete an evaluation on CommNet and enter to win!

slide42

Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st

http://northamerica.msteched.com/registration

You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year

slide43

© 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.