From the course: Microsoft Azure Database Administrator Associate (DP-300) Cert Prep by Microsoft Press

Implement data change tracking

From the course: Microsoft Azure Database Administrator Associate (DP-300) Cert Prep by Microsoft Press

Start my 1-month free trial Buy for my team

Implement data change tracking

“

- Welcome back. This is lesson 6.3, Implement Data Change Tracking. Data change tracking is exactly as it sounds, tracking changes to data in a database. When data changes are tracked, applications can become aware of the data changes. Data change tracking happens when there are DML changes on the data, insert, update, and delete operations. There are two features that track data changes in a database, the first being change data capture, which provides historical change information on the database, and change tracking, which captures the fact that data in a table have changed, but don't track the changed data. So I want to cover a few key benefits of these data changes, change tracking features, one being no schema changes are required, no adding of columns, no triggers or user tables. Built-in cleanup mechanism, meaning older changed data is removed based on a retention policy. So let's take a look at both change data capture and change tracking in more detail, starting with change data capture. Change data capture provides historical data changes in a database. It does this by capturing the fact that DML changes were made and the fact that actual data that was changed. Changes are captured via an asynchronous process that reads the database transaction log. Those changes are kept in eternal corresponding change data capture system tables. So change tracking information is obtained via built-in functions that allow you to capture the changes. Now because the changes are captured from the transaction log, change data capture has a very low impact on the overall database performance. Let's walk through how to enable change data capture. Enabling change data capture in Azure database is actually quite easy. First, you enable it. It's all done through T-SQL. First, you enable it at the database level, then you enable it at the table level. Now by default, all the columns in the source table are identified as captured columns. If only a subset of columns needs to be tracked, use the at capture column list parameter. Change tracking, this differs from change data capture in that it captures the fact that rows have changed, but doesn't track and store the data that changed. Change tracking helps answer the question about changes that were made to data in a table, such as what rows have changed, meaning the fact that a row has changed, but not how many times or the value of the changes. Has the row changed, meaning just the fact that a row has changed. Because change tracking is in storing the actual change data, there is no historical data information available, and thus, less storage overhead. The way change tracking works is that anytime a DML statement is executed, change tracking information is recorded for each modified row. So just like change data capture, there are a handful of built-in functions that return information about the changes. Let's walk through how to enable change tracking. So first, change tracking needs to be enabled at the database level, and again through T-SQL. And this can be done through the database properties as well as T-SQL, so one through the database properties or through T-SQL. Now next, change tracking needs to be enabled at the table level, and this is done through T-SQL. So it need needs to be enabled first at the database level, and that can be done through T-SQL, or the database properties, but to enable change tracking at the table level, that is done through T-SQL. Now, a comment on the tracked columns updated option. When track columns updated option is set to on, SQL server stores extra information about which columns were updated, not the data, just the columns. Otherwise, when that option is off, it is more of a, hey, a row has changed. Now you can have both of these enabled on the same database. No special considerations needed. Meaning, you can have both change data capture and change tracking enabled on a same database, no special recommendations needed.

Contents