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

1. Required Slide SESSION CODE: DAT318 Auditing, Tracking, and Change Monitoring Technologies in Microsoft SQL Server 2008 Adam Machanic Database Consultant SQLblog

2. 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

3. 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?)

4. 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?)

5. 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.

6. 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

7. 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

8. 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

9. 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?)

10. 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)

11. Change Tracking 101 demo

12. 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

13. 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

14. 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

15. 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

16. 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

17. 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

18. Change Tracking in Depth demo

19. 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?)

20. 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

21. Change Data Capture 101 demo

22. 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

23. 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]

24. 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

25. 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

26. 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

27. Change Data Capture in Depth demo

28. 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

29. 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?)

30. 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

31. 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

32. 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

33. 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

34. 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

35. 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

36. 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)

37. SQL Audit demo

38. 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

39. 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

40. 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

41. Required Slide Complete an evaluation on CommNet and enter to win!

42. 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

43. © 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.

44. Required Slide