We consider this scenario and implement undo capability in database.
We have developed a software for managing the data center. In this data center, we have all the information of personal, their finance documents, and all their working hours and so on.
After an elementary survey, we need at least the following Entity Relation Diagram (ERD) for data structure in a database and in this case, we use Microsoft SQL Server.
For implementing the undo in database, it has intended to use the Change Data Capture (CDC) feature of SQL Server.
What is CDC?
The CDC makes it possible to capture the data changing in a row in a table. For using CDC, the following steps must be followed.
- SQL Server Agency must be active
- Active CDC Service for the Database
- Active CDC for each table, which its data must be captured.
SQL Server Agency must be started
To be sure if the SQL SERVER AGENCY is running or not we can use the following code to check the current state of SQL SERVER AGENCY.
EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'
If the service is stopped, we can START it with following Code.
EXEC xp_servicecontrol N'START',N'SQLServerAGENT'
In addition, for more information about the usage of xp_servicecontrol stored procedure the related link is inserted in related links selection.
Active CDC Service for the Database
By default, the CDC is not active in database because it is process and time consuming. However, it is useful for capturing changed data and implementing undo functionality. With following code CDC become active for Database.
USE DatabaseName GO EXEC sys.sp_cdc_enable_db GO
Active CDC for each table
The CDC can become activated for all the columns of a table or for some of them. For starting the CDC the following code is used.
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Table-Name', @role_name = NULL
Only four operations are done in a table or occurs for a data row, each of this operation has a number and is saved in __$operation column in CDC table.
|1||Delete a new data row.|
|2||Insert a data row.|
|3||The old value of updated data row.|
|4||Update a data row.|
After activate the CDC service a CDC table is created for the table under System Tables Folder with following format:
Some points that must be known about CDC:
- The datatype NVARCHAR(MAX) cannot be captured.
- After applying a change in the table, which the CDC active, the CDC service become deactivate and must be activated again.
For more information about CDC, a related link is inserted in related links section.
The scenario for using the CDC
We should have a log file. This log is a table with the following columns.
|Id : BIGINT||ActionName : NVARCHAR(100)||IsRedone : BIT|
In Logs table the actions that are done on the tables are saved. Each action has a row. Some actions can change some tables but it’s not important this action has a KeyWord, which says what have been done and what should have been redone, furthermore this Action has an ActionCode which is used to follow the action in different tables.
In this case we have three tables Person, Finance and WorkingHours. The ActionNames, which can occur are as follows:
|Table: Person||Table: Finance||Table: WorkingHours|
As an example we insert a new person and make a change in person information and then we undo the changes. The above steps are demonstrated in figure 2, 3 and 4.
For each change a log-Record must be inserted in Logs table. The ID of the Logs table is the ActionCode which must be saved in related table.
|It’s important to know the CDC service doesn’t existed in Azure SQL Database. I tried to do the same in Azure SQL database but it wasn’t possible and after I found the Azure SQL Database features Link which is in related links section.|
Monitor, Start and Stop SQL Server services using xp_servicecontrol
Introduction to Change Data Capture (CDC) in SQL Server 2008