DBI 322 Incremental ETL Using CDC for SQL
DBI 322 Incremental ETL Using CDC for SQL and Oracle with SQL Server Integration Services (SSIS) 2012 Matt Masson Senior Program Manager Microsoft Corporation
Change Data Capture
More work to do Less time to do it More people using the system
s e L o D ! k r o s. W Processing Time vs. Data Volume 1 2 3 4
Demo CDC in SQL Server 2012
-- enable CDC on the database exec sys. sp_cdc_enable_db -- enable CDC on a table exec sys. sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'My. Table', @role_name = N'cdc_admin', @supports_net_changes = 1
Using Change Data Capture
Prepare database Get last date Read all from source Read changes Write to destination Process changes Record date Save current date
Scenario 1 Mark Initial Load Start Bulk load from source to destination Mark Initial Load End Scenario 2 SCN LSN
cdc_states
__$reprocessing True special handling
Demo CDC Components for SSIS
All
All vs. Net All Net
All Operation Insert Update Insert Delete Update Delete Key 50 50 60 60 10 20 Name Smith Smyth Jones Williams Martin
All
All with Old Values Operation Insert Update Old Update Insert Delete Update Old Update Delete __$update_mask 0 x. FF 0 x 80 0 x. FF Key 50 50 50 60 60 10 10 20 Name Smith Smyth Jones Williams Martin
All with Old Values
Net Operation Insert Update Delete Key 50 10 20 Name Smyth Williams Martin
Net
Net with Update Mask Operation Insert Update Delete Key 50 10 20 __$Key_Changed True False True Name Smyth Williams Martin __$Name_Changed True
Net with Update Mask
Net with Merge Operation Update Delete Key 50 10 20 Name Smyth Williams Martin
Net with Merge
CDC For Oracle
CDC Designer Oracle Database SQL Server Log. Miner CDC Service Mirror Tables CDC Tables
CDC Instances
FAQ
sys. sp_replincrementlsn 10% overhead db_owner
http: //msdn. microsoft. com/en-us/library/hh 231087. aspx http: //msdn. microsoft. com/en-us/library/dd 266396. aspx http: //msdn. microsoft. com/en-us/library/cc 280519. aspx
Breakout Sessions DBI 310: EIM: Bringing Together SSIS, MDS and DQS Hands-on Labs DBI 24 -HOL: Exploring Microsoft SQL Server Integration Services Product Demo Stations Related Certification Exam 70 -463: Implementing a Data Warehouse with Microsoft SQL Server 2012 Find Me Later At…
mmasson@microsoft. com Matt Masson mattmasson. com @mattmasson
mva
Learning Connect. Share. Discuss. Microsoft Certification & Training Resources http: //northamerica. msteched. com www. microsoft. com/learning Tech. Net Resources for IT Professionals Resources for Developers http: //microsoft. com/technet http: //microsoft. com/msdn
Complete an evaluation on Comm. Net and enter to win!
Scan the Tag to evaluate this session now on my. Tech. Ed Mobile
- Slides: 47