1 / 18

Streamlining Auditing with Change Tracking and Data Capture in SQL Server

Join Pinal Dave, a Microsoft Technology Evangelist, in an insightful session on auditing best practices with SQL Server. Learn about Change Tracking and Change Data Capture (CDC) techniques to maintain data integrity and audit trails. Discover how to efficiently manage historical data, recover deleted information, and track changes effectively. This session is designed for database professionals looking to enhance their understanding of auditing mechanisms and improve data governance in their SQL Server environments.

licia
Télécharger la présentation

Streamlining Auditing with Change Tracking and Data Capture in SQL Server

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. virtual techdays Auditing Made Easy: Change Tracking and Change Data Capture Pinal Dave│ Technology Evangelist, Microsoft http://blog.sqlauthority.com

  2. INDIA │ 9-11 February2011 virtual techdays About Pinal Dave • Technology Evangelist (DB and BI) – Microsoft India • Founder – http://blog.SQLAuthority.com • MCT, MCTS, MCP, MCDBA, MCAD • Follow me - http://www.twitter.com/pinaldave • LinkedIn - http://in.linkedin.com/in/pinaldave • MVP (2008 – 2011)

  3. INDIA │ 9-11 February2011 virtual techdays S E S S I O N A G E N D A • Introduction to Auditing • Change Tracking • Change Data Capture • Auditing • Demo

  4. INDIA │ 9-11 February2011 virtual techdays A Common Conversation • If I had my original data, I can give you difference between values. • What was the value of this particular field? • How many times this column was updated? • I want all of my deleted data back? • What happened? • Who did it?

  5. INDIA │ 9-11 February2011 virtual techdays Who is he? What does he do?

  6. INDIA │ 9-11 February2011 virtual techdays What is more important? • Data Modifier • Original Data

  7. INDIA │ 9-11 February2011 virtual techdays Story of Audit

  8. INDIA │ 9-11 February2011 virtual techdays Common Practices • SQL Profiler • SQL Trace • Table Trigger • Custom T-SQL Solution

  9. INDIA │ 9-11 February2011 virtual techdays SQL Server 2008 Technologies • Change Tracking (CT) • Change Data Capture (CDC) • SQL Audit

  10. INDIA │ 9-11 February2011 virtual techdays Difference Side By Side Change Data Capture Change Tracking • Which rows are changed and How? • Asynchronous Process • Available in Enterprise Edition • Which rows are changed? • Synchronous Process • Available in All Versions

  11. INDIA │ 9-11 February2011 virtual techdays Similarity Side By Side Change Data Capture Change Tracking • Less Performance Impact • Less Database Size • Usage: Change Tracking, ETL, Rollback data • Least Performance Impact • Least Database Size • Usage: Change Tracking, ETL, Rollback data

  12. virtual techdays Demo Change Tracking - WHAT

  13. virtual techdays Demo Change Data Capture – HOW

  14. INDIA │ 9-11 February2011 virtual techdays SQL Audit • Who did it? and How? • Based on Extended Events • Enterprise Edition Feature • Security Feature

  15. virtual techdays Demo SQL Audit - WHO

  16. INDIA │ 9-11 February2011 virtual techdays Summary • Change Tracking – WHAT has changed? • Change Data Capture – HOW was ‘what’ changed? • SQL Audit – WHO changed ‘what’ ‘how’?

  17. INDIA │ 9-11 February2011 virtual techdays Thank YOU! • Download Code: http://blog.SQLAuthority.com • Try out CT, CDC and SQL Audit • Follow me: http://www.twitter.com/pinaldave • Read: http://blog.SQLAuthority.com • Win! – Quiz at the End

  18. virtual techdays Thank You! http://blog.sqlauthority.com

More Related