捕获sql异常
This article on Change Data Capture will provide an overview, notes on installation, architecture, enabling and disabling, auditing DML statements and limitations
有关Change Data Capture的文章将提供概述，安装，体系结构，启用和禁用，审核DML语句和限制的注释
Before reading this article, I recommend that you to go through the previous articles of this series (see the TOC at the bottom), to understand the concept of the SQL Server Audit, the 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 particular article, we will discuss how to perform SQL Server database tables audit using Change Data Capture.
在阅读本文之前，建议您通读本系列的前几篇文章（请参阅底部的TOC ），以了解SQL Server审核的概念，审核SQL Server实例和数据库的背后原因以及可用于审核SQL Server数据库的不同方法。 在这篇特别的文章中，我们将讨论如何使用Change Data Capture执行SQL Server数据库表审核。
总览 (Overview)
Change Data Capture, also known as CDC, introduced the first time in SQL Server 2008 version, as a helpful feature to track and capture the changes that are performed on the SQL Server database tables, with no additional programming efforts. Before SQL Server 2016, Change Data Capture could be enabled on a SQL Server database only under the SQL Server Enterprise edition, which is not required starting from SQL Server 2016.
Change Data Capture（也称为CDC）在SQL Server 2008版本中首次引入，它是一项有用的功能，它可以跟踪和捕获对SQL Server数据库表执行的更改，而无需进行其他编程工作。 在SQL Server 2016之前，仅在SQL Server Enterprise版本下才能在SQL Server数据库上启用``更改数据捕获''，从SQL Server 2016开始不需要。
Change Data Capture tracks the INSERT, UPDATE and DELETE operations on the database table, and records detailed information about these changes in a mirrored table, with the same columns structure of the source tables, and additional columns to record the description of these changes. SQL Server writes one record for each INSERT statement showing the inserted values, on record for each DELETE statement showing the deleted data and two records for each UPDATE statement, the first one showing the data before the change and the second one showing the data after performing the change.
更改数据捕获跟踪数据库表上的INSERT，UPDATE和DELETE操作，并在镜像表中记录有关这些更改的详细信息，并具有与源表相同的列结构，而其他列则记录这些更改的描述。 SQL Server为每个显示插入值的INSERT语句写一个记录，在每个显示删除数据的DELETE语句记录上，为每个UPDATE语句记录两个记录，第一个记录显示更改之前的数据，第二个记录执行之后的数据改变。
- Note:注意：Monitoring changes in SQL Server using change data capture for more information about CDC. 使用更改数据捕获监视SQL Server中的更改 。
The additional columns include
其他列包括
- __$start_lsn and __ $ start_lsn和__$end_lsn that show the commit log sequence number (LSN) assigned by the SQL Server Engine to the recorded change __ $ end_lsn ，它们显示SQL Server引擎分配给记录的更改的提交日志序列号（LSN）
- __$seqval that shows the order of that change related to other changes in the same transaction, __ $ seqval显示与同一事务中其他更改相关的更改顺序， __$operation that shows the operation type of the change, where 1 = delete, 2 = insert, 3 = update (before change), and 4 = update (after change) __ $ operation显示更改的操作类型，其中1 =删除，2 =插入，3 =更新（更改前）和4 =更新（更改后）
- __$update_mask that is a bit mask defined for each captured column, identifying the updating columns __ $ update_mask是为每个捕获的列定义的位掩码，用于标识更新列
This detailed information makes it easier to monitor the database changes for security or auditing purposes, or incrementally load these changes from the OLTP source to the target OLAP data warehouse, using T-SQL or ETL methods.
此详细信息可以更轻松地出于安全性或审核目的监视数据库更改，或使用T-SQL或ETL方法将这些更改从OLTP源增量加载到目标OLAP数据仓库。
安装与架构 (Installation and architecture)
Change Data Capture requires that a SQL Server Agent is running on a SQL Server instance. When the feature is enabled on a SQL Server database table, two SQL Server Agent jobs will be created for that database; the first job is responsible for populating database change tables with the changes information and the second job is responsible for the cleaning up the change tables by deleting the records older than the configurable retention period of 3 days.
更改数据捕获要求SQL Server代理在SQL Server实例上运行。 在SQL Server数据库表上启用该功能后，将为该数据库创建两个SQL Server代理作业。 第一项任务是使用更改信息填充数据库更改表，第二项任务是通过删除早于可配置保留期3天的记录来清理更改表。
Change Data Capture depends on the SQL Server Transaction Log as the source of the data changes. When a change is performed, this change will be written to the Transaction Log file.
更改数据捕获取决于SQL Server事务日志作为数据更改的源。 执行更改后，此更改将被写入事务日志文件。
- SQL Server Transaction Log Architecture for more information. SQL Server事务日志体系结构 。
If CDC feature is enabled on that table, the transaction log replication Log reader agent, which acts as the capture process for CDC feature, will read the change logs from the Transaction Log file, adds the metadata information about these changes and write it to the associated CDC change tables, as shown below:
如果在该表上启用了CDC功能，则充当CDC功能捕获过程的事务日志复制日志读取器代理将从事务日志文件中读取更改日志，添加有关这些更改的元数据信息并将其写入到关联的CDC更改表，如下所示：
将数据捕获更改为审核解决方案 (Change Data Capture as an Audit solution)
Change Data Capture can be used as an asynchronous SQL Server Audit solution, to track and audit the table’s DML changes, such as INSERT, UPDATE or DELETE operations, with no option to track the SELECT statements.
Change Data Capture可以用作异步SQL Server审核解决方案，以跟踪和审核表的DML更改（例如INSERT，UPDATE或DELETE操作），而没有选择项来跟踪SELECT语句。
What makes Change Data Capture a good SQL Server Audit solution is that, it can be configured easily using few T-SQL commands, it provides historical information about the values before the modification process and provides detailed information about the data modification process.
使Change Data Capture成为良好SQL Server Audit解决方案的原因是，可以使用少量T-SQL命令轻松配置它，它提供了有关修改过程之前的值的历史信息，并提供了有关数据修改过程的详细信息。
Let us see how we can use the Change Data Capture to audit the SQL Server DML changes.
让我们看看如何使用更改数据捕获来审核SQL Server DML更改。
启用CDC (Enabling CDC)
In order to enable the Change Data capture on a specific table, for auditing purposes, CDC should be enabled first at the database level first, by a member of the SYSADMIN fixed server role, using the sys.sp_cdc_enable_db system stored procedure, as shown below:
为了在特定表上启用“更改数据”捕获，出于审计目的，应该首先由SYSADMIN固定服务器角色的成员使用sys.sp_cdc_enable_db系统存储过程在数据库级别首先启用CDC，如下所示： ：
USE [CDCAudit]
GO
EXEC sys.sp_cdc_enable_db
GO
To make sure that CDC is enabled on that database, we will query the sys.databases DMV for the list of databases, with CDC enabled, as below:
为了确保在该数据库上启用了CDC，我们将在sys.databases DMV中查询启用CDC的数据库列表，如下所示：
After enabling CDC at the database level, we can enable it to track and audit the DML changes on the database tables, by a member of db_owner fixed database role, using the sys.sp_cdc_enable_table system stored procedure, with the ability to enable it to track changes on list of columns specified by the @captured_column_list parameter, and create change tables on a separate filegroup specified by the @filegroup_name parameter, as shown below:
在数据库级别启用CDC之后，我们可以使它能够由db_owner固定数据库角色的成员使用sys.sp_cdc_enable_table系统存储过程来跟踪和审核数据库表上的DML更改，并具有使其能够跟踪的能力更改由@captured_column_list参数指定的列的列表，并在由@filegroup_name参数指定的单独文件组上创建更改表，如下所示：
USE [CDCAudit]
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Employee_Main',
@role_name = NULL,
@filegroup_name = NULL,
@supports_net_changes = 0
GO
To check that CDC is enabled on that table, we will query the sys.tables DMV for all tables under the current database with CDC enabled as below:
要检查该表上是否启用了CDC，我们将在sys.tables DMV中查询当前数据库下启用CDC的所有表，如下所示：
Once CDC is enabled on the table, a number of system tables will be created under the CDC schema of the database to store the CDC related information. These tables include the following
一旦在表上启用CDC，将在数据库的CDC模式下创建许多系统表，以存储CDC相关信息。 这些表包括以下内容
- .captured_columns table that contains the list of captured column .captured_columns表包含捕获列的列表
- .change_tables table that contains the list of tables that are enabled for capture .change_tables表包含已启用捕获的表的列表
- .ddl_history table that records the history of all the DDL changes since capture data enabled .ddl_history表记录自启用捕获数据以来所有DDL更改的历史记录
- .index_columns table that contains all the indexes that are associated with change table .index_columns表包含与变更表关联的所有索引
- .lsn_time_mapping table that is used to map the LSN number with the time and finally one change table for each CDC enabled table that is used to capture the DML changes on the source table, as shown below: .lsn_time_mapping表用于将LSN编号与时间映射，最后是每个CDC启用表的一个更改表，用于捕获源表上的DML更改，如下所示：
… and the SQL Agent jobs associated to CDC enabled table, the capture and cleanup jobs, will be created like below:
…以及与CDC启用表关联SQL Agent作业，捕获和清除作业将如下创建：
禁用CDC (Disabling CDC)
The Change Data Capture can be easily disabled on a specific table using the sys.sp_cdc_disable_table system stored procedure, as shown below:
可以使用sys.sp_cdc_disable_table系统存储过程轻松地在特定表上禁用“更改数据捕获”，如下所示：
… or disabled completely at the database level, without the need to disable it on CDC enabled tables one by one, using the sys.sp_cdc_disable_db system stored procedure, as shown below:
…或完全在数据库级别禁用，而无需使用sys.sp_cdc_disable_db系统存储过程在启用CDC的表上逐一禁用它，如下所示：
审核DML更改 (Auditing DML Changes )
After enabling CDC on the database table, let us perform the below three database DML changes, INSERT, UPDATE then DELETE, on that table and check how these changes will be audited using CDC feature, as below:
在数据库表上启用CDC之后，让我们在该表上执行以下三个数据库DML更改：INSERT，UPDATE然后DELETE，并检查如何使用CDC功能来审核这些更改，如下所示：
As mentioned previously, the data modifications will be written in the change table associated to CDC enabled table, which is the [cdc].[dbo_Employee_Main_CT] table in our case here. To view all records inserted recently to the source table, we can query the change table for all operations with type 2, and complete information about the INSERT operation, including the inserted values, will be displayed as shown below:
如前所述，数据修改将写入与启用CDC的表相关联的更改表中，在本例中为[cdc]。[dbo_Employee_Main_CT]表。 要查看最近插入到源表中的所有记录，我们可以查询更改表中所有类型2的操作，并且将显示有关INSERT操作的完整信息，包括插入的值，如下所示：
Querying the change table for all operations with type 1, will return all records deleted recently from the source table, with the values of the deleted records, will be displayed as shown below:
查询变更表中所有类型为1的操作，将返回源表中最近删除的所有记录，并显示删除的记录的值，如下所示：
And finally, you can query the change table for the operations with types 3 and 4 to track the UPDATE statement, that will display the values before the update, under the operation type 3 and the value after the change, under the operation type 4, as shown below:
最后，您可以查询更改表以查找类型3和4的操作，以跟踪UPDATE语句，该语句将显示操作类型3下更新之前的值，以及操作类型4下更改之后的值。如下所示：
Querying of the change tables is not recommended by Microsoft. Instead, you can query CDC.fn_cdc_get_all_changes system function associated to CDC enabled table as below:
Microsoft不建议查询更改表。 相反，您可以查询与启用CDC的表相关联的CDC.fn_cdc_get_all_changes系统功能，如下所示：
CDC.fn_cdc_get_all_changes function can be queried by providing the @from_lsn, @to_lsn, @row_filter_option parameters, that will retrieve all the DML changes information, as shown below:
可以通过提供@from_lsn ， @to_lsn ， @row_filter_option参数来查询CDC.fn_cdc_get_all_changes函数，如下所示：
局限性 (Limitations)
Change Data Capture can be easily used to audit only the database DML changes, albeit it with no option to monitor SELECT statement, with the negligible configuration effort. On the other hand, to consider CDC as a SQL Server Audit solution, it requires significant maintenance and administration effort. This includes automating an archiving mechanism, as the tracking data will be kept in the change table for a configurable number of days and will be stored in the same or different data file, that should be also monitored and maintained.
更改数据捕获可以轻松地仅用于审计数据库DML更改，尽管它没有选项来监视SELECT语句，而配置工作却可以忽略不计。 另一方面，要将CDC视为SQL Server审核解决方案，则需要大量的维护和管理工作。 这包括自动化归档机制，因为跟踪数据将在变更表中保留可配置的天数，并将存储在相同或不同的数据文件中，还应对其进行监视和维护。
In addition, the change tables will be stored under each database, and a function will be created for each tracked table. This makes it cumbersome and requires significant programming effort to create a consolidated auditing report that reads the DML changes information from all tables under the same database, from all databases under the same instance, or cross multiple instance.
此外，更改表将存储在每个数据库下，并且将为每个跟踪表创建一个功能。 这使其变得麻烦，并且需要大量的编程工作来创建合并的审计报告，该报告从同一数据库下的所有表，同一实例下或跨多个实例的所有数据库读取DML更改信息。
Another limitation for CDC feature as a SQL Server Audit solution is difficult process that is required to handle the DDL changes on CDC enabled table, as having the Change Data Capture enabled on the source table will not prevent performing DDL changes on that table.
CDC功能作为SQL Server审核解决方案的另一个限制是处理在CDC启用的表上进行DDL更改所需的困难过程，因为在源表上启用“更改数据捕获”将不会阻止对该表执行DDL更改。
Also, if the SQL Server Agent service is not running, CDC capture job will not work, and the database log file will grow rapidly, even if the database recovery model is Simple, as the log truncation will not advance, even if a CHECKPOINT is performed, till all the changes that are waiting for capture will be gathered by CDC capture process.
另外，如果SQL Server Agent服务未运行，则CDC捕获作业将无法工作，并且即使数据库恢复模型为Simple，数据库日志文件也会Swift增长，因为即使CHECKPOINT为，日志截断也不会继续进行。执行，直到CDC捕获过程将收集所有等待捕获的更改。
In the next article, we will discuss how to audit the SQL Server instance using the Change Tracking feature. Stay tuned!
在下一篇文章中，我们将讨论如何使用“更改跟踪”功能来审核SQL Server实例。 敬请关注！
目录 (Table of contents)
翻译自: https://www.sqlshack.com/change-data-capture-for-auditing-sql-server/
捕获sql异常
312
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
