Track Changes in SQL Server 2008
I think every DBMS developer will sooner or later be confronted with the task of tracking database calls and server events in general. And before you choose a tool (or write it yourself), of course, you should pay attention to the solutions offered by the DBMS developers themselves. I want to share the experience of solving this problem for SQL Server 2008.
At the moment there are 4 such solutions for SQL Server 2008 (for SQL Server 2011, no significant changes in this area are foreseen). Some of these tools came from earlier versions, some appeared in 2008. These tools overlap in many ways, therefore, sometimes it’s not easy to choose one (or more) tools for solving a specific task. In this I will try to help, having a brief overview of each tool with an example.
Often confused with CDC (Change Data Capture). But these tools are different in purpose and in implementation. CT is designed to track changes (in which lines, which data has been changed (CR UD)), while the CDC keeps a history of changes (all versions of the lines, including those that have been deleted). As for the implementation, the CDC is based on reading the transaction log (asynchronous), while CT works synchronously.
For each table for which change tracking is enabled, a system table is created in which the ID of the modified row is stored, a bitmask to identify the changed columns, and the type of operation.
To enable CT, you need to activate it at the database level and for a specific table:
More details (description of the parameters, examples of use and detailed information) in an excellent article .
')
Tool for tracking changed data. The main differences from CT are the asynchronous implementation (as stated above) and the storage of all versions of modified (CR UD) data. CDC uses system tables in the cdc schema to store modified data. For each table for which CDC is activated, a table is created with a name like cdc.dbo_Orders_CT (for the dbo.Orders table).
To activate CDC, you need to activate it at the database level for a specific table:
From a purely practical point of view, a significant minus of the CDC is that it is impossible to fix the author of the changes. Of course, no one bothers to add a column to the cdc.dbo_Orders_CT system table with the default value of suser_sname () (in my practice it works), but such manipulations with system tables are not the best way to build a fault-tolerant system.
Examples of queries to stored data and a detailed description in the article .
A powerful tool designed to keep track of all events and requests and the server (including select). The scope of this tool is quite wide - from profiling to issues related to security and identifying user activity in the part of the database that is not intended for them.
SQL Server Audit allows you to flexibly customize the filters of monitored events.
To use auditing, you need to activate it at the server level:
An example of creating a specification of audit (trace) at the server level:
An example of creating an audit specification at the database level:
For auditing infusions, there is a convenient visual interface in SQL Server Management Studio.
Also, it should be noted that the availability of standardized audit tools of the c2 specification (the US state standard, according to MSDN, I did not find the reference to the standard), for activation of which should be done:
Everyone has long been familiar utility, so for now I will not dwell on it.
Thank.
Plans to write in detail about each instrument, unless of course the topic is of interest.
At the moment there are 4 such solutions for SQL Server 2008 (for SQL Server 2011, no significant changes in this area are foreseen). Some of these tools came from earlier versions, some appeared in 2008. These tools overlap in many ways, therefore, sometimes it’s not easy to choose one (or more) tools for solving a specific task. In this I will try to help, having a brief overview of each tool with an example.
1. CT (Change Tracking).
Often confused with CDC (Change Data Capture). But these tools are different in purpose and in implementation. CT is designed to track changes (in which lines, which data has been changed (C
For each table for which change tracking is enabled, a system table is created in which the ID of the modified row is stored, a bitmask to identify the changed columns, and the type of operation.
To enable CT, you need to activate it at the database level and for a specific table:
ALTER DATABASE ChangeTracking SET change_tracking = ON <br/>
( change_retention = 10 minutes, auto_cleanup = ON ) <br/>
<br/>
ALTER TABLE Orders enable change_tracking WITH ( track_columns_updated = ON )
ALTER DATABASE ChangeTracking SET change_tracking = ON <br/>
( change_retention = 10 minutes, auto_cleanup = ON ) <br/>
<br/>
ALTER TABLE Orders enable change_tracking WITH ( track_columns_updated = ON )
More details (description of the parameters, examples of use and detailed information) in an excellent article .
')
2. CDC (Change Data Capture)
Tool for tracking changed data. The main differences from CT are the asynchronous implementation (as stated above) and the storage of all versions of modified (C
- * _lsn - (log sequence number) - a kind of transaction identifiers in the log.
$ operation - type of operation (1 - delete, 2 - insert, 3 - update (version of the line before the update), 4 - update (version of the line after the update). - $ update_mask is a bitmask indicating the changed cells in the row.
- ID and all the others on the right - cells sootv. The structure of the table, changes in which are tracked.
To activate CDC, you need to activate it at the database level for a specific table:
EXEC sys. sp_cdc_enable_db <br/>
<br/>
EXEC sys. sp_cdc_enable_table <br/>
@source_schema = N 'dbo' ,<br/>
@source_name = N 'Orders' ,<br/>
@role_name = N 'cdc' ,<br/>
@capture_instance = N 'dbo_Orders' ,<br/>
@supports_net_changes = 1 ,<br/>
@index_name = 'id_idx' ,<br/>
@captured_column_list = null,<br/>
@ FILEGROUP_NAME = null;
EXEC sys. sp_cdc_enable_db <br/>
<br/>
EXEC sys. sp_cdc_enable_table <br/>
@source_schema = N 'dbo' ,<br/>
@source_name = N 'Orders' ,<br/>
@role_name = N 'cdc' ,<br/>
@capture_instance = N 'dbo_Orders' ,<br/>
@supports_net_changes = 1 ,<br/>
@index_name = 'id_idx' ,<br/>
@captured_column_list = null,<br/>
@ FILEGROUP_NAME = null;
- @source_schema - the schema that owns the table for which we activate the CDC
- @source_name is the name of the table for which we are activating the CDC
- @role_name - the name of the role that will have the right to view the changes (if not, it is created automatically)
- @capture_instance - corresponds to the part of the name that will be issued to the corresponding system table
- @supports_net_changes - support for the ability to display the resulting set of changes (only the latest versions of data). This requires a unique index.
- @index_name - in fact, the name of the unique index
- @captured_column_list - a list of fields for which change tracking will be activated. Default is everything.
- @filegroup_name - file group in which the system table will be placed
From a purely practical point of view, a significant minus of the CDC is that it is impossible to fix the author of the changes. Of course, no one bothers to add a column to the cdc.dbo_Orders_CT system table with the default value of suser_sname () (in my practice it works), but such manipulations with system tables are not the best way to build a fault-tolerant system.
Examples of queries to stored data and a detailed description in the article .
3. SQL Server Audit
A powerful tool designed to keep track of all events and requests and the server (including select). The scope of this tool is quite wide - from profiling to issues related to security and identifying user activity in the part of the database that is not intended for them.
SQL Server Audit allows you to flexibly customize the filters of monitored events.
To use auditing, you need to activate it at the server level:
CREATE server audit ServerAudit<br/>
TO FILE ( filepath = `D:\Audit\`, maxsize = 1GB ) <br/>
WITH ( on_failture = CONTINUE ) <br/>
<br/>
ALTER server audit ServerAudit WITH ( STATE = ON )
CREATE server audit ServerAudit<br/>
TO FILE ( filepath = `D:\Audit\`, maxsize = 1GB ) <br/>
WITH ( on_failture = CONTINUE ) <br/>
<br/>
ALTER server audit ServerAudit WITH ( STATE = ON )
An example of creating a specification of audit (trace) at the server level:
CREATE server audit specification ServerAudit_Permissions<br/>
FOR server audit ServerAudit<br/>
ADD ( server_principal_change_group ) ,<br/>
ADD ( server_permission_change_group ) ,<br/>
ADD ( server_role_member_change_group ) ;<br/>
<br/>
ALTER server audit specification ServerAudit_Permissions<br/>
WITH ( STATE = ON ) ;
CREATE server audit specification ServerAudit_Permissions<br/>
FOR server audit ServerAudit<br/>
ADD ( server_principal_change_group ) ,<br/>
ADD ( server_permission_change_group ) ,<br/>
ADD ( server_role_member_change_group ) ;<br/>
<br/>
ALTER server audit specification ServerAudit_Permissions<br/>
WITH ( STATE = ON ) ;
An example of creating an audit specification at the database level:
USE MyDb<br/>
CREATE DATABASE audit specification SA_MyDb_Orders <br/>
FOR server audit ServerAudit<br/>
ADD ( SELECT , UPDATE , INSERT , DELETE ON dbo. Orders BY PUBLIC ) ,<br/>
ADD ( SELECT , UPDATE , INSERT , DELETE ON dbo. OrderDetails BY PUBLIC )
USE MyDb<br/>
CREATE DATABASE audit specification SA_MyDb_Orders <br/>
FOR server audit ServerAudit<br/>
ADD ( SELECT , UPDATE , INSERT , DELETE ON dbo. Orders BY PUBLIC ) ,<br/>
ADD ( SELECT , UPDATE , INSERT , DELETE ON dbo. OrderDetails BY PUBLIC )
For auditing infusions, there is a convenient visual interface in SQL Server Management Studio.
Also, it should be noted that the availability of standardized audit tools of the c2 specification (the US state standard, according to MSDN, I did not find the reference to the standard), for activation of which should be done:
SP_CONFIGURE 'show advanced options' , 1 ;<br/>
RECONFIGURE ;<br/>
<br/>
SP_CONFIGURE 'c2 audit mode' , 1 ;<br/>
RECONFIGURE ;
SP_CONFIGURE 'show advanced options' , 1 ;<br/>
RECONFIGURE ;<br/>
<br/>
SP_CONFIGURE 'c2 audit mode' , 1 ;<br/>
RECONFIGURE ;
4. SQL Server Profiler
Everyone has long been familiar utility, so for now I will not dwell on it.
Thank.
Plans to write in detail about each instrument, unless of course the topic is of interest.
Source: https://habr.com/ru/post/111207/
All Articles