แก้ไข

แชร์ผ่าน

Track data changes (SQL Server)

  • บทความ

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

SQL Server provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database. Change data capture and change tracking can be enabled on the same database; no special considerations are required. For the editions of SQL Server that support change data capture and change tracking, see Editions and supported features of SQL Server 2022.

Benefits of using change data capture or change tracking

The ability to query for data that has changed in a database is an important requirement for some applications to be efficient. Typically, to determine data changes, application developers must implement a custom tracking method in their applications by using a combination of triggers, timestamp columns, and additional tables. Creating these applications usually involves a lot of work to implement, leads to schema updates, and often carries a high performance overhead.

Using change data capture or change tracking in applications to track changes in a database, instead of developing a custom solution, has the following benefits:

  • There's reduced development time. Because functionality is available in SQL Server, you don't have to develop a custom solution.

  • Schema changes aren't required. You don't have to add columns, add triggers, or create side table in which to track deleted rows or to store change tracking information if columns can't be added to the user tables.

  • There's a built-in cleanup mechanism. Cleanup for change tracking is performed automatically in the background. Custom cleanup for data that is stored in a side table isn't required.

  • Functions are provided to obtain change information.

  • There's low overhead to DML operations. Synchronous change tracking will always have some overhead. However, using change tracking can help minimize the overhead. The overhead will frequently be less than that of using alternative solutions, especially solutions that require the use of triggers.

  • Change tracking is based on committed transactions. The order of the changes is based on transaction commit time. This allows for reliable results to be obtained when there are long-running and overlapping transactions. Custom solutions that use timestamp values must be designed to handle these scenarios.

  • Standard tools are available that you can use to configure and manage. SQL Server provides standard DDL statements, SQL Server Management Studio, catalog views, and security permissions.

Feature differences between change data capture and change tracking

The following table lists the feature differences between change data capture and change tracking. The tracking mechanism in change data capture involves an asynchronous capture of changes from the transaction log so that changes are available after the DML operation. In change tracking, the tracking mechanism involves synchronous tracking of changes in line with DML operations so that change information is available immediately.

Feature Change data capture Change tracking
Tracked changes
DML changes Yes Yes
Tracked information
Historical data Yes No
Whether column was changed Yes Yes
DML type Yes Yes

Change data capture

Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system.

As shown in the following illustration, the changes that were made to user tables are captured in corresponding change tables. These change tables provide a historical view of the changes over time. The change data capture functions that SQL Server provides enable the change data to be consumed easily and systematically.

Diagram showing the concept of change data capture.

Security model

This section describes the change data capture security model.

Configuration and administration

To either enable or disable change data capture for a database, the caller of sys.sp_cdc_enable_db (Transact-SQL) or sys.sp_cdc_disable_db (Transact-SQL) must be a member of the fixed server sysadmin role. Enabling and disabling change data capture at the table level requires the caller of sys.sp_cdc_enable_table (Transact-SQL) and sys.sp_cdc_disable_table (Transact-SQL) to either be a member of the sysadmin role or a member of the database database db_owner role.

Use of the stored procedures to support the administration of change data capture jobs is restricted to members of the server sysadmin role and members of the database db_owner role.

Change enumeration and metadata queries

To gain access to the change data that is associated with a capture instance, the user must be granted SELECT access to all the captured columns of the associated source table. In addition, if a gating role is specified when the capture instance is created, the caller must also be a member of the specified gating role, and the change data capture schema (cdc) must have SELECT access to the gating role.

Other general change data capture functions for accessing metadata will be accessible to all database users through the public role, although access to the returned metadata will also typically be gated by using SELECT access to the underlying source tables, and by membership in any defined gating roles.

DDL operations to change data capture-enabled source tables

When a table is enabled for change data capture, DDL operations can only be applied to the table by a member of the fixed server role sysadmin, a member of the database role db_owner, or a member of the database role db_ddladmin. Users who have explicit grants to perform DDL operations on the table will receive error 22914 if they try these operations.

Data type considerations for change data capture

All base column types are supported by change data capture. The following table lists the behavior and limitations for several column types.

Type of Column Changes Captured in Change Tables Limitations
Sparse columns Yes Doesn't support capturing changes when using a columnset.
Computed columns No Changes to computed columns aren't tracked. The column appears in the change table with the appropriate type, but will have a value of NULL.
XML Yes Changes to individual XML elements aren't tracked.
Timestamp Yes The data type in the change table is converted to binary.
BLOB data types Yes The previous image of the BLOB column is stored only if the column itself is changed.

SQL Server feature integration

This section describes how the following features interact with change data capture:

  • Database mirroring
  • Transactional replication
  • Database restore or attach

Database mirroring

A database that is enabled for change data capture can be mirrored. To ensure that capture and cleanup happen automatically on the mirror, follow these steps:

  1. Ensure that SQL Server Agent is running on the mirror.

  2. Create the capture job and cleanup job on the mirror after the principal has failed over to the mirror. To create the jobs, use the stored procedure sys.sp_cdc_add_job (Transact-SQL).

For more information about database mirroring, see Database Mirroring (SQL Server).

Transactional replication

Change data capture and transactional replication can coexist in the same database, but population of the change tables is handled differently when both features are enabled. Change data capture and transactional replication always use the same procedure, sp_replcmds, to read changes from the transaction log. When change data capture is enabled on its own, a SQL Server Agent job calls sp_replcmds. When both features are enabled on the same database, the Log Reader Agent calls sp_replcmds. This agent populates both the change tables and the distribution database tables. For more information, see Replication Log Reader Agent.

Consider a scenario in which change data capture is enabled on the AdventureWorks2022 database, and two tables are enabled for capture. To populate the change tables, the capture job calls sp_replcmds. The database is enabled for transactional replication, and a publication is created. Now, the Log Reader Agent is created for the database and the capture job is deleted. The Log Reader Agent continues to scan the log from the last log sequence number that was committed to the change table. This ensures data consistency in the change tables. If transactional replication is disabled in this database, the Log Reader Agent is removed, and the capture job is re-created.

Note

When the Log Reader Agent is used for both change data capture and transactional replication, replicated changes are first written to the distribution database. Then, captured changes are written to the change tables. Both operations are committed together. If there is any latency in writing to the distribution database, there will be a corresponding latency before changes appear in the change tables.

Restore or attach a database enabled for change data capture

SQL Server uses the following logic to determine if change data capture remains enabled after a database is restored or attached:

  • If a database is restored to the same server with the same database name, change data capture remains enabled.

  • If a database is restored to another server, by default change data capture is disabled, and all related metadata is deleted.

    To retain change data capture, use the KEEP_CDC option when restoring the database. For more information about this option, see RESTORE.

  • If a database is detached and attached to the same server or another server, change data capture remains enabled.

  • If a database is attached or restored with the KEEP_CDC option to any edition other than Standard or Enterprise, the operation is blocked because change data capture requires SQL Server Standard or Enterprise editions. Error message 932 is displayed:

    SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

You can use sys.sp_cdc_disable_db to remove change data capture from a restored or attached database.

Change tracking

Change tracking captures the fact that rows in a table were changed, but doesn't capture the data that was changed. This enables applications to determine the rows that have changed with the latest row data being obtained directly from the user tables. Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for those applications that don't require the historical information, there's far less storage overhead because of the changed data not being captured. A synchronous tracking mechanism is used to track the changes. This has been designed to have minimal overhead to the DML operations.

The following illustration shows a synchronization scenario that would benefit by using change tracking. In the scenario, an application requires the following information: all the rows in the table that were changed since the last time that the table was synchronized, and only the current row data. Because a synchronous mechanism is used to track the changes, an application can perform two-way synchronization and reliably detect any conflicts that might have occurred.

Diagram showing the concept of change tracking.

Change tracking and Sync Services for ADO.NET

Sync Services for ADO.NET enables synchronization between databases, providing an intuitive and flexible API that enables you to build applications that target offline and collaboration scenarios. Sync Services for ADO.NET provides an API to synchronize changes, but it doesn't actually track changes in the server or peer database. You can create a custom change tracking system, but this typically introduces significant complexity and performance overhead. To track changes in a server or peer database, we recommend that you use change tracking in SQL Server because it's easy to configure and provides high performance tracking.

For more information about change tracking and Sync Services for ADO.NET, use the following links:

Related content