When CDC is enabled on a table, it creates two CDC-related jobs that are specific to the database, and executed using SQL Server Agent. Before enabling CDC at the table level, make sure that you have enabled SQL Server Agent. You can run the following stored procedure to enable each table. The above query will return a result that includes a column with the table name, along with a column which displays if CDC is enabled or not. First run following query to show which tables of database have already been enabled for CDC. It has to be enabled for any table which needs to be tracked. The CDC feature can be applied at the table-level to any database for which CDC is enabled. cdc.lsn_time_mapping – This table maps LSN number (for which we will learn later) and time.Įnabling Change Data Capture on one or more Database Tables.cdc.index_columns – This table contains indexes associated with change table.cdc.ddl_history – This table contains history of all the DDL changes since capture data enabled.cdc.change_tables – This table returns list of all the tables which are enabled for capture.cdc.captured_columns – This table returns result for list of captured column.The table which have been created are listed here. Some System Tables will have been created within the AdventureWorks database as part of the cdc schema. You can run following query and check whether it is enabled for any database.Īs soon as CDC is enabled, it will show this result in SSMS.Īdditionally, in the database AdventureWorks, you will see that a schema with the name ‘cdc’ has now been created. Because CDC is a table-level feature, it then has to be enabled for each table to be tracked. Enabling Change Data Capture on a DatabaseĬDC first has to be enabled for the database. The SQL Server DBA can then easily monitor the activity for the logged table using these new audit tables. When you apply Change Data Capture features on a database table, a mirror of the tracked table is created with the same column structure of the original table, but with additional columns that include the metadata used to summarize the nature of the change in the database table row. Because captured data is then stored in relational tables, it can be easily accessed and retrieved subsequently, using regular T-SQL. It only tracks changes in user-created tables. CDC has allowed SQL Server developers to deliver SQL Server data archiving and capturing without any additional programming.ĬDC is one of the new data tracking and capturing features of SQL Server 2008. A better solution was introduced in SQL Server 2008 and is called Change Data Capture (CDC). SQL Server 2005 introduced the new features of ‘after update’, ‘after insert’ and ‘after delete’ triggers that almost solved the problem of tracking changes in data. To implement this, I have seen a variety of solutions from triggers, timestamps and complicated queries (stored procedures) to audit data. This feature is usually implemented for data security purposes. In other words, we are required to save all the history of the changes to the data. Often, you’ll be told that the specification of an application requires that the value of data in the database of an application must be recorded before it is changed.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |