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