The need to keep a log for every single modification on the system usually never comes without considerable cost either from a technical or business perspective. Although, the need for such requirement remains crucial. In this post, I am shedding a light specifically on the need to keep track of every single modification of a record in a simple and yet cost effective methodology.
There are several methods to accomplish such requirement; very great and well know third party products can just deliver the need, techniques such as triggers, timestamp columns, join queries … etc could be a time-consuming which sometimes result in an undesirable performance. Meanwhile, there is a great feature out there in SQL management which can give you all these requirements with a low-cost, it is called the “Change Data Capture - CDC”
SQL Server | Change Data Capture – CDC
Change data capture provides information about the DML (Data Manipulation Language) changes on a table or database, it is very useful to know what are the inserted or deleted records, what are the values of the updated records (before and after the update).
The important of CDC will be explained through a simple example of updating a vendor card, which got few fields updated from a specific value to another value. CDC will provide detailed information of the data before and after update as shown below
Configuring Change Data Catalog for Dynamics GP
Once configured, CDC builds new system tables, stored procedures, SQL jobs and functions. Initially, you can run the scripts below to check whether CDC is enabled either on the database level or table level.
To check whether the CDC is enabled on the database level
-- The script below checks whether CDC is enabled for each database
SELECT is_cdc_enabled ,
To check whether the CDC is enabled on the table level
-- The script below checks whether CDC is enabled for each table
In the screen shot above, it is obvious that CDC is enabled on the TWO db, and specifically on the IV00101 table which is the Item Master. In order to enable CDC on the DB and Table, run the following scripts:
To enable the CDC on the database level:
-- The script below enables CDC on the database level
To enable the CDC on the table level:
-- The script below enables CDC on the table level
@source_schema = N'dbo',
@source_name = N'IV00101',
@role_name = NULL
Once CDC is enabled, the following objects are created under TWO db > Tables > System Tables
cdc.change_tables | Storing the tables being tracked
cdc.captured_columns | Storing the column being tracked per table
Real Case Scenario
After enabling the CDC on the TWO db, and the item master table specifically IV00101, I am going to apply the following scenario in order to check the result:
- Adding a new item card
- Changing few fields of the item such as; item class, UOM schedule, item description …etc
- Delete the item
Here is the tracking log as retrieved from [cdc].[dbo_IV00101_CT] on the following path (TWO db > Tables > System Tables), the table shows the different changes applied on the item card since it was inserted, updated then deleted.
Here is the script which is used to retrieve tracking information above, it should be different according to the table for which you enable CDC.
SELECT CASE __$OPERATION
WHEN 1 THEN 'Deleted'
WHEN 2 THEN 'Inserted'
WHEN 3 THEN 'Before being Updated'
WHEN 4 THEN 'After beign Updated'
END AS Record_Status,
In brief, change data catalog feature is a simple and cost-effective method that can be applied to track highly sensitive data such as setup in general. In proceeding posts, I will include a real case example of tracking opening and closing fiscal periods on the financial module and provide alert accordingly.
Mahmoud M. AlSaadi