Skip to main content This browser is no longer supported. Show
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Track data changes (SQL Server)
In this articleApplies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed InstanceSQL 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. Change tracking is supported by Azure SQL Database. Change data capture is only supported in SQL Server and Azure SQL Managed Instance. Benefits of using change data capture or change trackingThe 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:
Feature differences between change data capture and change trackingThe 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.
Change data captureChange 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.
Security modelThis section describes the change data capture security model. Configuration and administrationTo 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 queriesTo 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 ( 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 tablesWhen 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 captureAll base column types are supported by change data capture. The following table lists the behavior and limitations for several column types.
Change data capture and other SQL Server featuresThis section describes how the following features interact with change data capture:
Database mirroringA 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:
For more information about database mirroring, see Database Mirroring (SQL Server). Transactional replicationChange 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 Consider a scenario in which change data capture is enabled on the Note When the Log Reader Agent is used for both change data capture and transactional replication, replicated changes are first written to the Restore or attach a database enabled for change data captureSQL Server uses the following logic to determine if change data capture remains enabled after a database is restored or attached:
You can use sys.sp_cdc_disable_db to remove change data capture from a restored or attached database. Change trackingChange 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 is 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.
Change tracking and Sync Services for ADO.NETSync 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 is easy to configure and provides high performance tracking. For more information about change tracking and Sync Services for ADO.NET, use the following links:
Next steps
See also
FeedbackSubmit and view feedback for What is the process of tracking changes Additions Deletions and error during the data cleaning?Correct. Documentation is the process of tracking changes, additions, deletions, and errors during data cleaning.
What is the process of data cleaning?How to clean data. Step 1: Remove duplicate or irrelevant observations. Remove unwanted observations from your dataset, including duplicate observations or irrelevant observations. ... . Step 2: Fix structural errors. ... . Step 3: Filter unwanted outliers. ... . Step 4: Handle missing data. ... . Step 5: Validate and QA.. What is the first step should a data analyst take to clean their data?Step 1: Remove duplicate or irrelevant observations
Remove unwanted observations from your dataset, including duplicate observations or irrelevant observations. Duplicate observations will happen most often during data collection.
What are the data issues in data cleaning?Data Cleansing: Problems and Solutions. Data is never static. ... . Incorrect data may lead to bad decisions. ... . Incorrect data can affect client records. ... . Develop a data cleansing framework in advance. ... . Big data can bring in bigger problems.. |