Study guide
7.2 Change Data Tracking and Dynamic Data Masking
DP-300: Administering Microsoft Azure SQL Solutions Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources
2 Planning and Deploying Azure SQL Solutions
2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching
3 Configuring Resources for Scale and Performance
3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression
4 Planning and Implementing a Migration Strategy
4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting
5 Configuring Database Authentication and Authorization
5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management
6 Implementing Security for Data at Rest and in Transit
6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints
7 Implementing Compliance Controls for Sensitive Data
7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL
8 Monitoring Resource Activity and Performance
8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events
9 Monitoring and Optimizing Query Performance
9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights
10 Configuring Database Solutions for Optimal Performance
10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing
11 Creating and Managing SQL Server Agent Jobs
11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates
12 Automating Deployments and Database Tasks in Azure
12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks
13 Planning an HA/DR Strategy and Backup and Restore
13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations
14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups
14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups
15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting
15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions
7.2 Change Data Tracking and Dynamic Data Masking
Key Takeaways Explain This Topic AI
Change Tracking (CT) is synchronous and lightweight: it records that a row changed and which columns changed, not the values; Change Data Capture (CDC) is asynchronous and records full before/after values via the transaction log
CDC requires enabling at the database (sys.sp_cdc_enable_db) then per table (sys.sp_cdc_enable_table), creates a capture instance and a cdc.<schema>_<table> change table populated by a SQL Agent job (or the managed scheduler on Azure SQL DB)
DDM has four masking functions - default(), email(), random(min, max), partial(prefix, suffix, padding) - and applies to the result set, never to storage; the underlying data is still stored in clear
UNMASK is a database-level permission that bypasses masking; users without it (including guest users) see masked output for any column with a mask defined
DDM is a presentation-layer control and does not stop inference attacks; Always Encrypted is the right control when data must be cryptographically protected at rest and on the wire
Last updated: August 2026
Why Both Controls Appear Together
Both Change Tracking/CDC and Dynamic Data Masking appear under the secure-environment domain because they answer operational compliance questions: what changed in this table? and who is allowed to see the unmasked value? They are easy to confuse with audit (which records events, not values) and with Always Encrypted (which protects data rather than hiding it). The exam will mix scenarios to test whether you pick the lightweight tracking option, the heavy historical capture option, or the masking option.
Change Tracking (CT) vs Change Data Capture (CDC)
Both features detect row changes, but they differ sharply in overhead, scope, and what they record.
Aspect Change Tracking (CT) Change Data Capture (CDC)
What is captured Primary key + whether the row changed; optionally which columns changed Full before-and-after column values for every change
Synchronization Synchronous inside the transaction Asynchronous, via transaction log reader (SQL Agent or managed scheduler)
Overhead Minimal Higher; change tables grow with write volume
History Last change only (no intermediate values) Full history of changes retained for the cleanup retention window
Use case Offline cache sync, idempotent refresh, "did this row change since version X?" ETL, audit history, dimensional slowly-changing data, regulatory change log
Enable ALTER DATABASE ... SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) sys.sp_cdc_enable_db then sys.sp_cdc_enable_table
CT uses the CHANGETABLE table-valued function. CHANGETABLE(CHANGES, ...) returns the rows that changed since a baseline version; CHANGETABLE(VERSION, ...) returns the current version for a specific row. When TRACK_COLUMN_UPDATED = ON is set at the database level, CT also reports which columns changed (via the SYS_CHANGE_COLUMNS column), but it does not record the previous values - only that the column was touched.
CDC is heavier but complete. The capture process reads the transaction log, decodes the operation, and writes a row into a change table named cdc.<schema>_<table> (or cdc.<schema>_<table>_<capture_instance> when a capture instance other than the default is used). Query functions expose the changes:
cdc.fn_cdc_get_all_changes_<capture_instance> returns one row per change (including intermediate values).
cdc.fn_cdc_get_net_changes_<capture_instance> returns the net effect over a range (only the final state).
The LSN range helpers sys.fn_cdc_get_min_lsn and sys.fn_cdc_get_max_lsn bound the query window. A SQL Agent job (the capture job, plus a cleanup job) drives capture on SQL Server and SQL MI; on Azure SQL Database CDC uses a managed background scheduler, so no Agent job is required.
- Enabling and Disabling
- CT: ALTER DATABASE Sales SET CHANGE_TRACKING = ON
- (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
- - Per table
- ALTER TABLE dbo.Customer ENABLE CHANGE_TRACKING
- WITH (TRACK_COLUMNS_UPDATED = ON);
- ALTER TABLE dbo.Customer DISABLE CHANGE_TRACKING;
- ALTER DATABASE Sales SET CHANGE_TRACKING = OFF;
- CDC: EXEC sys.sp_cdc_enable_db;
- EXEC sys.sp_cdc_enable_table
- @source_schema = 'dbo',
- @source_name = 'Customer',
- @role_name = 'cdc_reader',
- @supports_net_changes = 1;
- EXEC sys.sp_cdc_disable_table
- @source_schema = 'dbo',
- @source_name = 'Customer',
- @capture_instance = 'dbo_Customer';
- EXEC sys.sp_cdc_disable_db;
A capture instance scopes the change table. When you re-enable CDC after a schema change, you can create a new capture instance (e.g., dbo_Customer_v2 ) so the old change table keeps serving historical queries while the new one captures the post-change schema. A common exam trap: dropping a column from a CDC-enabled table fails unless you disable the capture instance first or add a new one that excludes the dropped column.
When to Use Which
Use CT when the consumer needs to know that a row changed and which columns changed since a known version, e.g., a mobile client refreshing a cache. Use CDC when the consumer needs the actual old and new values - ETL into a warehouse, an audit log, or building a slowly-changing dimension. CT is the right answer when an exam scenario says "a SaaS application syncs a local copy of reference data and only needs to know which rows changed since the last sync." CDC is the right answer when the scenario requires history of every value change for compliance.
Dynamic Data Masking (DDM)
Dynamic Data Masking limits the exposure of sensitive data to non-privileged users by masking the values in the result set . Crucially, the data is stored unmasked on disk; DDM is a presentation-layer control that rewrites the output based on the caller's permissions. This distinction is one of the most heavily tested facts in the chapter.
Four masking functions cover the common column shapes:
Function Behavior Typical use
default() Strings replaced with XXXX (or fewer X for short values); numerics with 0 ; dates with 01/01/1900 Default for any unspecified sensitive column
email() First letter of email + xxx + domain suffix with the first letter exposed, e.g., aXXX@contoso.com Email columns
random(min, max) Random numeric value within the specified range on each query Numeric columns where any actual value is sensitive
partial(prefix, suffix, padding) Custom masking that exposes a prefix and suffix and replaces the middle with a padding string, e.g., partial(2, 2, 'XXXXXX') on a credit card yields 12XXXXXX90 Phone numbers, credit cards, SSNs
- Adding, Modifying, and Removing Masks
- Masks are added when the column is created or altered: CREATE TABLE dbo.Customer (
- CustomerID int IDENTITY PRIMARY KEY,
- FullName nvarchar(100) MASKED WITH (FUNCTION = 'default()'),
- Email nvarchar(200) MASKED WITH (FUNCTION = 'email()'),
- Phone nvarchar(20) MASKED WITH (FUNCTION = 'partial(2, 2, "XXXXXX")'),
- Age int MASKED WITH (FUNCTION = 'random(18, 99)')
- );
- ALTER TABLE dbo.Customer ALTER COLUMN FullName ADD MASKED WITH (FUNCTION = 'default()');
- ALTER TABLE dbo.Customer ALTER COLUMN FullName DROP MASKED;
A schema modification trap: to change an existing mask, you ALTER the column and ADD MASKED with the new function. To remove the mask, ALTER the column and DROP MASKED. The column itself must be alterable - masks cannot be added to computed columns, FILESTREAM, sparse columns, or columns protected by Always Encrypted.
UNMASK and Guest Users
UNMASK is a database-level permission ( GRANT UNMASK TO NurseRole ). A user without UNMASK sees the masked result for every masked column, with two important consequences the exam tests:
Guest users are masked by default. If a database user has only SELECT on a masked column and no UNMASK, the query returns masked data - DDM does not require an explicit deny.
UNMASK is all-or-nothing at the database scope. You cannot grant UNMASK on a single column or table; the user either sees all masked columns unmasked or none. For finer-grained control, combine DDM with row-level security or column-level permissions.
To inspect masks, query sys.masked_columns : SELECT object_name(object_id) AS tbl, name AS col, is_masked, masking_function
- FROM sys.masked_columns
- WHERE is_masked = 1;
- DDM vs Always Encrypted
Both controls protect sensitive data, but they operate at different layers, and the exam relies on the contrast:
- Property DDM Always Encrypted
- Storage Plaintext on disk Ciphertext on disk
- Wire Plaintext (TLS) to client Ciphertext to client; decrypted in driver
- Where unmasked value lives Server sees plaintext Only client app sees plaintext
Threat model Hides data from casual users, support staff, report writers Protects from DBAs, admins, and server compromise
- Bypass UNMASK permission; inference via aggregates on masked columns Application key custody
- Performance Negligible Deterministic encryption limits equality; randomized limits all comparison
The recurring trap: a scenario describes DBAs and server admins as adversaries. DDM is the wrong answer - it never protects data from a privileged user who can read the underlying storage or query with UNMASK. Always Encrypted is the right answer because the column master key lives in the client application; the server never sees plaintext.
DDM Limitations
DDM has documented limitations worth memorizing for scenario rejection:
Masking does not apply to columns encrypted with Always Encrypted.
Masking does not work for computed or persisted computed columns that depend on a masked column; the output may be undefined or unmasked.
A masked column in a WHERE clause uses the unmasked value at the server, so a user can infer data by querying "WHERE Age > 50" and observing counts.
Aggregate functions over masked columns return masked or partial results and can leak distribution information.
Masking is not a security boundary; the documentation states it is for compliance with privacy exposure rules, not for protecting data from a determined adversary.
BULK INSERT and similar operations still load unmasked values; the mask is enforced on read, not write.
Takeaways
Pick CT for lightweight "did it change?" sync; pick CDC for full-value history with capture instances. Pick DDM for casual visibility control of query results; pick Always Encrypted when even the DBA must not see plaintext. Remember UNMASK is database-scoped, masks are added and dropped through ALTER COLUMN, and DDM never changes what is stored on disk.