This continuation of our SQL Server auditing series is on SQL Server Change Tracking and will cover an overview, enabling, disabling, auditing DML changes including SQL updates and deletes.
这是SQL Server审核系列的延续，是有关SQL Server更改跟踪的，将概述，启用，禁用，审核DML更改，包括SQL更新和删除。
If this is your first reading in the SQL Server Audit series, it is recommended to go through the previous articles of this series (see the TOC at the bottom), to build a solid background about the concept of the SQL Server Audit, the different reasons behind auditing the SQL Server instances and databases, and the different methods that can be used to audit the SQL Server databases. In this article, we will discuss how to perform a SQL Server audit using SQL Server Change Tracking.
如果这是您第一次阅读《 SQL Server审核》系列，建议阅读本系列的前几篇文章（请参阅底部的目录 ），以建立有关SQL Server审核概念的扎实背景，审核SQL Server实例和数据库的原因以及可用于审核SQL Server数据库的不同方法。 在本文中，我们将讨论如何使用SQL Server更改跟踪执行SQL Server审核。
总览 (Overview)
SQL Server Change Tracking, also known as CT, is a lightweight tracking mechanism, introduced the first time in SQL Server , that can be used to track the DML changes performed in SQL Server database tables. SQL Change Tracking can be configured in all SQL Server editions, including the free Express edition.
SQL Server更改跟踪（也称为CT）是一种轻量级跟踪机制，在SQL Server 中首次引入，可用于跟踪SQL Server数据库表中执行的DML更改。 可以在所有SQL Server版本（包括免费的Express版本）中配置SQL更改跟踪。
SQL Server Change Tracking is a synchronous tracking mechanism, in which the changes information will be available directly once the DML change is committed, without the need for a delay while reading the changes from the Transaction Log file, such as the Change Data Capture asynchronous mechanism. This means that, SQL Change Tracking does not require the SQL Server Agent service to be up and running as it has no dependencies on the SQL Agent jobs in capturing or writing the DML changes.
SQL Server更改跟踪是一种同步跟踪机制，其中，一旦提交了DML更改，更改信息将直接可用，而从事务日志文件中读取更改时无需延迟，例如更改数据捕获异步机制。 。 这意味着，SQL更改跟踪不需要启动和运行SQL Server代理服务，因为它在捕获或写入DML更改时不依赖于SQL代理作业。
When SQL Server Change Tracking is enabled on a database table, the SQL Server Engine will create an internal table, under the nameChange_Tracking_<Object_ID>, to track the INSERT, UPDATE and DELETE statements on the tracked user table. What makes SQL Change Tracking lighter than the Change Data Capture feature, is that, it will track the database table changes by mentioning that, there is a DML change performed on this row within the tracked table, providing thePrimary Keycolumn value of the modified row, the changed column and the modification type, without writing detailed information about the changed data, such as writing the inserted or deleted values or the values before and after update process, with the minimum storage requirements and overhead. This is the reason why the database table should have a Primary Key on it, in order to enable SQL Change Tracking on that table, as this key value will be used to identify the modified rows in the tracked tables.
在数据库表上启用SQL Server更改跟踪后，SQL Server引擎将创建一个内部表，名称为Change_Tracking_ <Object_ID>，以跟踪被跟踪用户表上的INSERT，UPDATE和DELETE语句。 使SQL更改跟踪比“ 更改数据捕获”功能更轻松的原因是，它将通过提及在跟踪表中的此行上执行DML更改来跟踪数据库表更改，从而提供已修改的“主键”列值行，更改的列和修改类型，而无需写入有关更改的数据的详细信息，例如，以最小的存储需求和开销来写入插入或删除的值或更新过程之前和之后的值。 这就是为什么在数据库表上应具有主键，以便在该表上启用SQL更改跟踪的原因，因为此键值将用于标识被跟踪表中的已修改行。
SQL Server更改跟踪作为审核解决方案 (SQL Server Change Tracking as an Audit Solution)
SQL Server Change Tracking is considered a legacy SQL Server Audit solution, that can be used to track and audit the database table DML changes by answering simple auditing questions such as, which row is changed, by providing the Primary Key of that row, and what type of change performed on that row.
SQL Server更改跟踪被视为旧版SQL Server审核解决方案，可通过回答简单的审核问题（例如，更改的行，提供该行的主键以及提供的主键）来跟踪和审核数据库表DML更改。在该行上执行的更改的类型。
What makes SQL Change Tracking less desirable as a SQL Server database audit solution is that it records no information about the inserted data, the deleted data or the data before and after the update process. In addition, the tables that have no Primary Key constraints defined on it cannot be audited using SQL Change Tracking, as it is limited only for the database tables with Primary Key constraints.
SQL更改跟踪作为SQL Server数据库审核解决方案的原因不那么理想，是因为它不记录有关插入数据，已删除数据或更新过程之前和之后的数据的信息。 此外，没有定义主键约束的表不能使用SQL Change Tracking进行审计，因为它仅限于具有主键约束的数据库表。
On the other hand, SQL Server Change Tracking will record no history about the changes performed on a database table, where it will record the last change performed on that row, without retaining the version history. For example, if a row is inserted, then updated multiple times and finally deleted, SQL Change Tracking, as a SQL Server Audit solution, will only record the last delete statement, without considering the previous operations performed on that row.
另一方面，SQL Server更改跟踪将不记录有关在数据库表上执行的更改的任何历史记录，而该记录将记录该行上执行的最后更改，而不会保留版本历史记录。 例如，如果插入了一行，然后进行了多次更新并最终被删除，则作为SQL Server审核解决方案SQL更改跟踪将仅记录最后的delete语句，而不考虑对该行执行的先前操作。
To build a useful SQL Server Audit solution using SQL Server Chang Tracking, extra coding effort will be required to join the internal tables of SQL Change Tracking with the tracked source table, based on the Primary Key value of the changed row, that is stored in an internal table, to obtain complete information about the changed data. And due to the fact that the internal tables are not visible and cannot be queried directly, you can take benefits from the SQL Change Tracking functions, that uses the on-disk tables, built based on these internal tables, which we will discuss later in this article.
若要使用SQL Server Chang Tracking构建有用SQL Server Audit解决方案，将需要进行额外的编码工作，才能将SQL Change Tracking的内部表与跟踪的源表（基于更改后的行的Primary Key值）连接在一起，该存储在内部表，以获取有关已更改数据的完整信息。 而且由于内部表不可见并且无法直接查询，您可以从SQL更改跟踪功能中受益，该功能使用基于这些内部表构建的磁盘表，我们将在后面的部分中进行讨论。本文。
Another point to consider here is that the SQL Change Tracking internal tables will grow gradually with time. Although the purging process is controlled by the auto cleanup thread, that is responsible for purging old data from the internal on-disk tables based on a predefined retention period, with a default value of 2 days, you still need to set proper retention period to keep the changes data available for your SQL Server Audit solution. In addition, you can benefit from the new stored procedure added in SQL Server to perform manual cleanup for the internal SQL Server Change Tracking table.
这里要考虑的另一点是，SQL Change Tracking内部表将随着时间的推移逐渐增长。 尽管清除过程是由自动清除线程控制的，但它负责根据预定义的保留期（默认值为2天）从内部磁盘表中清除旧数据，您仍然需要将适当的保留期设置为保留更改数据可用于您SQL Server审核解决方案。 此外，您可以受益于SQL Server 中添加的新存储过程来为内部SQL Server更改跟踪表执行手动清理。
Let us see how we can use the SQL Change Tracking to audit the SQL Server DML changes.
让我们看看如何使用SQL更改跟踪来审核SQL Server DML更改。
启用SQL更改跟踪 (Enabling SQL Change Tracking)
In order to enable SQL Server Change Tracking on a database table for auditing purposes, you should enable it at the database level using the ALTER DATABASE T-SQL statement, by providing the retention period for the internal on-disk tables, and if you will enable the auto clean process, that will delete the internal on-disk table’s data older than the retention period automatically, as below:
为了在数据库表上启用SQL Server更改跟踪以进行审计，您应该使用ALTER DATABASE T-SQL语句在数据库级别启用它，方法是提供内部磁盘表的保留期限，如果要启用自动清除过程，它将自动删除早于保留期限的内部磁盘表数据，如下所示：
USE masterGOALTER DATABASE [CTAudit]SET CHANGE_TRACKING = ON(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
You can also enable it using the SQL Server Management Studio tool, from theChange Trackingtab of the Database Properties window, in which you can specify the retention period of the internal on-disk tables and enable the auto clean process, as shown below:
您还可以使用SQL Server Management Studio工具从“数据库属性”窗口的“更改跟踪”选项卡启用它，在其中可以指定内部磁盘表的保留期限并启用自动清除过程，如下所示：
After enabling SQL Server Change Tracking at the database level, we need to enable it on each table that will track and audit the DML changes on it. This can be achieved using the ALTER TABLE T-SQL statement below:
在数据库级别启用SQL Server更改跟踪后，我们需要在将跟踪和审核DML更改的每个表上启用它。 这可以使用下面的ALTER TABLE T-SQL语句来实现：
USE CTAuditGOALTER TABLE Employee_MainENABLE CHANGE_TRACKINGWITH (TRACK_COLUMNS_UPDATED = ON)
If you try to enable SQL Change Tracking in a database table with no Primary Key defined on it, the ALTER TABLE statement will fail, showing that SQL Change Tracking requires creating a Primary Key on the table before enabling it, as in the error message below:
如果您尝试在未定义主键的数据库表中启用SQL更改跟踪，则ALTER TABLE语句将失败，表明SQL更改跟踪要求在启用表之前创建主键，如下面的错误消息所示：
After adding a Primary Key constraint on the table, the ALTER TABLE statement will be executed successfully. You can also enable SQL Change Tracking using the SQL Server Management Studio, from the SQL Change Tracking tab of the Table Properties window, as shown below:
在表上添加主键约束后，ALTER TABLE语句将成功执行。 您还可以使用SQL Server Management Studio从“表属性”窗口的“ SQL更改跟踪”选项卡中启用SQL更改跟踪，如下所示：
禁用SQL更改跟踪 (Disabling SQL Change Tracking)
Enabling SQL Server Change Tracking on a database table will not prevent you from performing DDL changes at that table, except for the changes on the Primary Key that will fail unless the CT is disabled on that table.
在数据库表上启用SQL Server更改跟踪不会阻止您在该表上执行DDL更改，除非主键上的更改会失败，除非对该表禁用了CT，否则该更改将失败。
Change Tracking can be disabled at the table level using the ALTER TABLE T-SQL statement below:
可以使用以下ALTER TABLE T-SQL语句在表级别禁用更改跟踪：
After disabling it at the table level, Change Tracking can be easily disabled at the database level, using the ALTER DATABASE T-SQL statement below:
在表级别禁用更改跟踪之后，可以使用以下ALTER DATABASE T-SQL语句在数据库级别轻松禁用更改跟踪：
审核DML更改 (Auditing DML Changes)
插入审核 (INSERT Audit)
When SQL Server Change tracking is enabled on the database table for SQL Server database audit purposes, all DML changes that are performed on the table rows will be written to the CT internal tables. Assume that we perform the below INSERT statement into the Employe_Main test table:
当出于SQL Server数据库审计目的而在数据库表上启用SQL Server更改跟踪时，对表行执行的所有DML更改都将写入CT内部表。 假设我们在Employe_Main测试表中执行以下INSERT语句：
To get the Change Tracking data recorded in the internal table after the INSERT statement, you can use the Change Tracking functions, such asCHANGETABLEsystem function. The CHANGETABLE function returns all changes performed on the tracked table after the specified version number. The version number counter is associated with each changed row, that will be increased whenever there is a change on the tracked table is performed. The below T-SQL script can be used to retrieve the change information:
要在INSERT语句后获取记录在内部表中的变更跟踪数据，可以使用变更跟踪功能 ，例如CHANGETABLE系统功能。 CHANGETABLE函数返回指定版本号之后对跟踪表执行的所有更改。 版本号计数器与每个更改的行相关联，只要在跟踪表上进行更改，版本号计数器就会增加。 下面的T-SQL脚本可用于检索更改信息：
SELECT * FROM CHANGETABLE (CHANGES [Employee_Main],0) as CT ORDER BY SYS_CHANGE_VERSION
The data returned from the CT internal tables, after performing the INSERT statement will show, the version of the performed DML change, the type of the DML operation, which is I for INSERT in this case, the changed columns, which is NULL in the case of INSERT and finally the Primary Key value for the inserted rows in the SQL Server audited table, as shown below:
执行完INSERT语句后，从CT内部表返回的数据将显示执行的DML更改的版本，DML操作的类型（在这种情况下，对于INSERT是I，更改后的列，在操作中为NULL）。 INSERT的情况，最后是SQL Server审核表中插入行的主键值，如下所示：
To get the complete inserted record, we can easily join the CHANGETABLE function with the tracked source table, based on the Primary Key value, as in the T-SQL script below:
为了获得完整的插入记录，我们可以根据主键值轻松地将CHANGETABLE函数与跟踪的源表结合起来，如下面的T-SQL脚本所示：
SELECT CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_OPERATION, EM.* FROM CHANGETABLE (CHANGES [Employee_Main],0) as CT JOIN [dbo].[Employee_Main] EMON CT.Emp_ID = EM.Emp_IDORDER BY SYS_CHANGE_VERSION
The returned result from joining the CT function and the tracked source table will show complete information about the inserted data, that can be useful to audit the database table, as below:
通过加入CT函数和跟踪的源表返回的结果将显示有关插入数据的完整信息，这对于审核数据库表很有用，如下所示：
更新审核 (UPDATE Audit)
If the below UPDATE statement is performed on the same tracked table:
如果对同一跟踪表执行以下UPDATE语句：
Then execute the previous query that joins the CHANGETABLE function with the tracked source table, you will see that the change version number for the row with Emp_ID value equal to 2 will be increased. It is clear also that only the last version of the change that is performed on that row will be recorded, missing the previous INSERT statement and keeping the last update statement, which will internally delete the previous record then insert the record with the new value, as shown below:
然后执行将CHANGETABLE函数与跟踪的源表联接在一起的上一个查询，您将看到Emp_ID值等于2的行的更改版本号将增加。 同样清楚的是，将仅记录在该行上执行的更改的最新版本，缺少先前的INSERT语句，并保留最后的update语句，这将在内部删除先前的记录，然后使用新值插入记录，如下所示：
删除审核 (DELETE Audit)
Assume that we execute the below DELETE statement to drop the third row from the tracked table:
假设我们执行下面的DELETE语句从跟踪表中删除第三行：
Then execute the same query that joins the CHANGETABLE function with the tracked source table to check the deleted record information. You will see that the deleted record data will not be shown, as it is not existing in the tracked source table, as shown below:
然后执行将CHANGETABLE函数与跟踪的源表结合在一起的同一查询，以检查已删除的记录信息。 您将看到删除的记录数据将不会显示，因为它在跟踪的源表中不存在，如下所示：
Changing the JOIN type in the previous query to LEFT OUTER JOIN, you will see that the deleted record information from the CHANGETABLE function will be retrieved. This information includes only the change version number and the change type, which is D for DELETE, with no information about the deleted record, as shown below:
将上一个查询中的JOIN类型更改为LEFT OUTER JOIN，您将看到将从CHANGETABLE函数删除的记录信息。 该信息仅包含变更版本号和变更类型，DELETE为D，而没有有关已删除记录的信息，如下所示：
It is clear from the previous results that, SQL Server Change Tracking can be used as a limited SQL Server database audit solution, to track the DML changes on the CT enabled table. This is due to the fact that it will return only the last change that is performed on the modified record with no historical information about the value before the update or delete operations.
从先前的结果可以明显看出，SQL Server更改跟踪可以用作有限SQL Server数据库审核解决方案，以跟踪启用了CT的表上的DML更改。 这是由于这样的事实，它将仅返回对修改后的记录执行的最后更改，而没有关于更新或删除操作之前的值的历史信息。
In the next article of this series, we will discuss how to audit the SQL Server using the SQL Server Audit feature. Stay tuned.
在本系列的下一篇文章中，我们将讨论如何使用SQL Server审核功能来审核SQL Server。 敬请关注。
目录 (Table of contents)
翻译自: /creating-a-sql-server-audit-using-sql-server-change-tracking/
如果觉得《使用SQL Server更改跟踪创建SQL Server审核》对你有帮助，请点赞、收藏，并留下你的观点哦！
