SQL Azure Database No CDC No Problem Leveraging

SQL Azure Database – No CDC, No Problem! Leveraging Temporal Tables to Emulate Change Data Capture in SQL Database bo. B Taylor MCA, MCM, MCSE, MCSD, MCT Principal Data Platform Solution Architect

bo. B. Taylor@Microsoft. co m SQL Server Compression Estimator SQL Diag Configuration Tool Connect. To. SQLCommand. Filters aka. ms/SQLbo. BT Linkedin. com/in/SQLbo. BT @SQLbo. BT Magician / Mentalist

Key Objectives / Takeaways 1. Understand why Change Data Capture (CDC) is not present in SQL Azure Database 2. Understand how temporal tables can emulate the functionality of CDC to include which functionality cannot be emulated 3. Implement Temporal Data Capture (TDC) in SQL Azure Database (or SQL Server 2016) by using the TDC Wizard and the TDC database objects.

Change Data Capture – A Brief Review Captures all insert, update, and deletes for a tracked table without triggers. Easily consumed for ETL processes. Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

Change Data Capture – A Brief Review

Why Temporal Data Capture (TDC)? TDC was driven by customer needs – my customer needed the capabilities of CDC on Azure SQL Database However for Azure SQL Database • No SQL Server Agent • No Log Reader Agent • No msdb • So no Change Data Capture!

Temporal Data Capture – A Brief Overview Captures all insert, update, and deletes for a tracked table without triggers or log reader agent. Easily consumed for ETL processes. Temporal data capture is available on all editions of SQL Server 2016 or Azure SQL Database.

Temporal Tables SQL Server 2016 introduces support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI Standard SQL 2011 and is now supported in SQL Server 2016 and Azure SQL Database.

Temporal Table Use Cases Data Audit Point in Time Analysis (Time Travel) Anomaly Detection Slowly-Changing Dimensions Repairing Row-Level Data Corruption And now… Temporal Data Capture (TDC)!

Temporal Table Concepts Temporal tables work by keeping a history of all changes to a given table but does not have the overhead of ‘rewinding’ the transaction log to harvest entries. The downside is that transactional information is not present This makes temporal a possible solution to the missing CDC feature for all editions of SQL Server

Temporal Data Capture – A Brief Overview

Design Goals for Temporal Data Capture (TDC) 1. Easy to implement and consume 2. Leverage existing knowledge of CDC to speed understanding of TDC 3. Be applicable in the cloud and on premise In fact, on premise also has the capability to leverage Stretch. DB for more cost effective storage of TDC historical data if necessary 4. Support modern datatypes and features such as Dynamic Data Masking (DDM) and sparse columns. Note: sparse columns will prevent native PAGE compression of the history table.

Installing TDC Components exec [dbo]. [usp_tdc_enable_db] – this stored procedure installs our schemas, functions and stored procedures. This stored procedure creates the following objects: [dbo]. [usp_tdc_enable_db]; [dbo]. [usp_tdc_create_schema]; [dbo]. [ufn_is_db_tdc_enabled]; [dbo]. [ufn_tdc_objects_exist]; Schema [tdc]. [usp_tdc_enable_table]; [tdc]. [usp_tdc_disable_table]; [tdc]. [usp_Can. Table. Be. TDC_Enabled] ; [tdc]. [usp_Get. Column. Info]; [tdc]. [usp_Get. Join. Info]; [tdc]. [usp_tdc_enable_table_internal]; [tdc]. [ufn_Column. Type]; schema db_name()+'_tdc_history

Enabling TDC for a given table exec [dbo]. [usp_tdc_enable_table] @source_schema, @source_name This stored procedure creates the history table if necessary and creates the two functions used to consume the TDC results. This stored procedure creates the following objects for a table name @source_name [tdc]. ['+ @source_name + '_All. Changes] [tdc]. ['+ @source_name + '_Net. Changes]

Uninstalling TDC components exec [dbo]. [usp_tdc_disable_db] – this stored procedure removes our schemas, functions and stored procedures. It will check to ensure that no tables are currently enabled for TDC. If tables are currently enabled for TDC, this stored procedure will list those tables which must manually be disabled by executing tdc. usp_tdc_disable_table. NOTE: you have the choice to remove the history by executing tdc. usp_tdc_disable_table @source_schema, @source_table, @remove_history =1

Demo TDC Wizard

Where can I get TDC? All of the source code for TDC will be hosted on Code. Plex after SQL PASS Summit I will be releasing The TDC Wizard code and installer The SSMS project for implementing TDC manually All demo code Contact me directly if you would like an early release!

Key Objectives / Takeaways 1. Understand why Change Data Capture (CDC) is not present in SQL Azure Database 2. Understand how temporal tables can emulate the functionality of CDC to include which functionality cannot be emulated 3. Implement Temporal Data Capture (TDC) in SQL Azure Database (or SQL Server 2016) by using the TDC Wizard and the TDC database objects.

Questions?