sql文件中捕获异常_使用更改数据捕获监视SQL Server中的更改

sql文件中捕获异常

背景 (Background)

In multi-user environments, changes may occur frequently to the architecture, data, or overall structure that creates work for other users. In this series, we look at some ways that we can track changes on the data and architecture layer for pin-pointing times, changes, and using the information for alerting, if changes should be kept to a minimum. SQL Server comes with some built-in tools that allow us to monitor changes, and depending on the architecture, we can create tools that allow us also to monitor and identify changes near the time that they occur.

在多用户环境中，为其他用户创建工作的体系结构，数据或整体结构可能会频繁发生更改。 在本系列中，我们研究了一些方法，可以在数据和体系结构层上跟踪更改，以查明时间，更改以及使用信息进行警报（如果更改应保持在最低限度）。 SQL Server附带了一些内置工具，使我们可以监视更改，并且根据体系结构，我们可以创建工具，使我们也可以在更改发生时监视和识别更改。

讨论区 (Discussion)

In this article, we’ll look at adding change data capture to a specific table, as well as how we can track the changes with meaningful queries, along with some useful considerations when enabling this feature.

在本文中，我们将研究将更改数据捕获添加到特定表，以及如何使用有意义的查询来跟踪更改，以及启用此功能时的一些有用注意事项。

First, let’s create a simple table:

首先，让我们创建一个简单的表：

 
CREATE TABLE [dbo].[tblMyNewTable](
       [ID] [smallint] IDENTITY(1,1) NOT NULL,
       [Name] [varchar](50) NULL,
       [OtherID] [tinyint] NULL
)
 

We’re not going to populate data yet, but we will in a second. We want to first create the table and from there check to see if change data capture is enabled on the database level:

我们还不会填充数据，但是我们会在一秒钟内。 我们要首先创建表，然后从那里检查是否在数据库级别启用了更改数据捕获：

 
SELECT is_cdc_enabled
FROM sys.databases
WHERE name = 'OurDatabase'
 

If the above returns a 0, it means that it is not enabled and we want to enable it. Once we enable change data capture, we want to verify that it’s enabled by running the above query again (we should see a 1).

如果上面的代码返回0，则表示它未启用，我们想启用它。 启用更改数据捕获后，我们想通过再次运行上述查询来验证它是否已启用（我们应该看到1）。

 
EXEC sys.sp_cdc_enable_db
 

In the below image, I ran change data capture check before, enabled it, then ran it again and as we see, it changed from 0 to 1.

在下图中，我之前运行过更改数据捕获检查，将其启用，然后再次运行，如我们所见，它从0更改为1。

Now, we will specifically enable tracking data changes on the table level. Because many architecture problems begin with poor naming convention, this is a step where we want to consider what other names we may develop for other objects; I’ve solved numerous problems where the simple problem was poor naming convention. For this reason, my naming convention includes change data capture (cdc) – or what it is – along with its purpose – auditing – and the table name – tblMyNewTable. In essence, I’m answering three questions in this naming convention:

现在，我们将专门在表级别启用跟踪数据更改。 由于许多架构问题都是从不良的命名约定开始的，因此这是我们要考虑为其他对象开发的其他名称的步骤。 我已经解决了许多问题，其中最简单的问题是命名约定不好。 因此，我的命名约定包括变更数据捕获（ cdc ）或它的含义，以及其目的– 审计 –和表名– tblMyNewTable 。 本质上，我在此命名约定中回答了三个问题：

• The name answers what the object is.
  名称回答对象是什么。
• The name answers what the purpose of the object is.
  名称回答了对象的用途。
• The name answers what the target object is. In the case of change data capture in this example, the target is a table.
  名称回答目标对象是什么。 在此示例中，在捕获更改数据的情况下，目标是一个表。

Anytime you hear a colleague or co-worker suggest that, “This is the one time we should make an exception,” recognize that this mentality often leads to more work and often is the cause of problems – often, people forget why or what the exception was!

每当您听到同事或同事的建议时，“这是我们应该例外的时候”，要认识到这种心态通常会导致更多的工作，并且经常是问题的根源–人们常常会忘记为什么或什么例外！

 
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo'
       , @source_name = 'tblMyNewTable'
       , @role_name = NULL
       , @capture_instance = 'cdcauditing_tblMyNewTable'
 

When we enable this, we should see a confirmation message stating that two jobs were started successfully – a capture and cleanup job. We can confirm this by looking through the SQL Server agent jobs and see the two jobs enabled – keep this in mind when disabling it and wanting to make sure that the jobs are removed. Up to this point, we haven’t populated any data in the table, but let’s do that now so that we can see how we’ll track our changes and how our changes will appear in the tracking information:

启用此功能后，我们将看到一条确认消息，指出已成功启动两个作业–捕获和清除作业。 我们可以通过查看SQL Server代理作业并确认已启用两个作业来确认这一点–在禁用它并希望确保删除作业时，请记住这一点。 到现在为止，我们还没有填充表中的任何数据，但是现在就开始进行操作，以便我们可以看到如何跟踪更改以及更改如何显示在跟踪信息中：

 
INSERT INTO tblMyNewTable (Name,OtherID)
VALUES ('John',1)
       , ('Jane',2)
       , ('Sarah',3)
       , ('Sean',4)
 
	SELECT *
FROM tblMyNewTable
 

When we select from the table, we should see the following:

从表中选择时，应看到以下内容：

We must recall that change data capture monitors the change through the log, so we must know the LSNs in order to monitor the changes. In this case, we’ll be retrieving the minimum LSN to identify what’s been changed to the maximum LSN. We have two functions that help us retrieve the minimum and maximum LSN that we can save to variables, so that we can pass in the necessary parameters to monitor changes. Think of this as similar to a where clause when we’re looking from a begin date to an end date:

我们必须记得更改数据捕获通过日志监视更改，因此我们必须知道LSN才能监视更改。 在这种情况下，我们将检索最小LSN，以识别更改为最大LSN的内容。 我们有两个函数可以帮助我们检索可以保存到变量的最小和最大LSN，以便我们可以传入必要的参数以监视更改。 当我们从开始日期到结束日期查找时，可以认为这类似于where子句：

 
DECLARE @begin binary(10), @end binary(10);
SET @begin = sys.fn_cdc_get_min_lsn('cdcauditing_tblMyNewTable');
SET @end   = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_cdcauditing_tblMyNewTable(@begin, @end, N'ALL')
 

When using the function cdc_get_all_changes, we pass in begin, end, and row filter parameters – ALL in this case. In the latter parameter we can specify either ALL, which will return all changes with updates being shown with their new values only, or all update old, where both the previous and new value of an update is shown (later in the article, we see an image comparing these two differences side-by-side). Because we have not updated any data so far, running ‘all update old’ as a parameter won’t show us anything different than our current output:

当使用功能cdc_get_all_changes时，我们传入开始，结束和行过滤器参数-在这种情况下为ALL 。 在后一个参数中，我们可以指定ALL（全部返回，仅显示新值时才显示更新），也可以指定所有旧的更新（其中同时显示更新的前值和新值）（在本文后面，我们将看到并排比较这两个差异的图片）。 因为到目前为止我们还没有更新任何数据，所以将'all update old'作为参数运行不会向我们显示与当前输出不同的内容：

 
DECLARE @begin binary(10), @end binary(10);
SET @begin = sys.fn_cdc_get_min_lsn('cdcauditing_tblMyNewTable');
SET @end   = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_cdcauditing_tblMyNewTable(@begin, @end, N’all update old')
 

Let’s update a record, add a new record, and remove a record:

让我们更新一条记录，添加一条新记录，然后删除一条记录：

 
UPDATE tblMyNewTable
SET Name = 'Jane Doe'
WHERE Name = 'Jane'
 
 
DELETE FROM tblMyNewTable
WHERE Name = 'John'
 
 
INSERT INTO tblMyNewTable (Name,OtherID)
VALUES ('Jason',5)
 

And let’s run our check from the above changes – this time we will be querying both the :

让我们从上述更改中进行检查-这次我们将同时查询：

 
DECLARE @begin binary(10), @end binary(10);
SET @begin = sys.fn_cdc_get_min_lsn('cdcauditing_tblMyNewTable');
SET @end   = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_cdcauditing_tblMyNewTable(@begin, @end, N'ALL')
 
 
DECLARE @begin2 binary(10), @end2 binary(10);
SET @begin2 = sys.fn_cdc_get_min_lsn('cdcauditing_tblMyNewTable');
SET @end2   = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_cdcauditing_tblMyNewTable(@begin2, @end2, N'all update old')
 

Finally, to make the output more meaningful as far as what transactions happened, we can use the below query:

最后，为了使输出对于发生的事务更有意义，我们可以使用以下查询：

 
DECLARE @begin binary(10), @end binary(10);
SET @begin = sys.fn_cdc_get_min_lsn('cdcauditing_tblMyNewTable');
SET @end   = sys.fn_cdc_get_max_lsn();
 
SELECT __$start_lsn
       --, __$seqval
       , CASE
              WHEN __$operation = 1 THEN 'DELETE'
              WHEN __$operation = 2 THEN 'INSERT'
              WHEN __$operation = 3 THEN 'PRE-UPDATE'
              WHEN __$operation = 4 THEN 'POST-UPDATE'
              ELSE 'UNKNOWN'
       END AS Operation
       --, __$update_mask
       , ID
       , Name
       , OtherID
FROM cdc.fn_cdc_get_all_changes_cdcauditing_tblMyNewTable(@begin, @end, N'all update old')
 

We can see the translated information shown, which our inserts, update and delete performed on the table. When looking at the data changes in a table, we’ll may want to filter for specific operations – such as deletes in some cases, so keep in mind that we can apply filtering as well (such as filtering where the operation equals delete). In addition, we might want to use some of the information for time-based monitoring, or column-based monitoring; for an example, in the above image, we may not care what OtherID is added, removed, or changed – the name may be the only value we want to monitor.

我们可以看到显示的翻译信息，我们在表上执行了插入，更新和删除操作。 查看表中的数据更改时，我们可能希望针对特定操作进行过滤-在某些情况下例如删除，因此请记住，我们也可以应用过滤（例如在操作等于删除的情况下进行过滤）。 另外，我们可能希望将某些信息用于基于时间的监视或基于列的监视。 例如，在上图中，我们可能不在乎添加，删除或更改了什么OtherID-名称可能是我们要监视的唯一值。

一些有用的提示和问题 (Some useful tips and questions)

Some cautions about enable change data capture:

有关启用更改数据捕获的一些注意事项：

1. Like all auditing, change data capture increases overhead on a server. We should heavily scrutinize the reason that we’re adding it in the first place.
   像所有审核一样，更改数据捕获会增加服务器的开销。 我们应该仔细检查我们首先添加它的原因。
2. Keep an eye on SQL Server job agent, as it uses this. If this goes offline, or restarts, this will have an effect.
   请密切注意SQL Server作业代理，因为它使用了它。 如果此操作脱机或重新启动，则将生效。
3. Stick to consistent naming conventions so that you can quickly identify if it’s enabled and on what objects; otherwise, when you’re troubleshooting problems, you may miss on identifying the issue related to change data capture.
   遵循一致的命名约定，以便您可以快速确定是否启用了该功能以及启用了哪些对象。 否则，在对问题进行故障排除时，您可能会错过与变更数据捕获相关的问题。
4. Similar to point one, some objects – due to their nature – should not have this feature enabled, such as a fully replicated table (audit the publisher table), or adding it to an automated ETL process (audit the application). With every architecture structure, identify where the best place to audit the data is – and with the exception of many changes – it’s often early in the process.
   与第一点类似，某些对象（由于其性质）不应启用此功能，例如完全复制的表（审核发布者表）或将其添加到自动ETL流程中（审核应用程序）。 对于每种架构结构，确定在哪里审计数据的最佳位置（除了许多更改之外）通常是在过程的早期。

最后的想法 (Final Thoughts)

In this article we looked at enabling the change data capture on a table and experimented with obtaining the changes. This is one way that we can track data changes on the table-level and query what’s been changed. While this feature is useful, we should also consider architecting it in a way that allows us to make sure it doesn’t create disruptions and that we can identify the objects being monitored quickly.

在本文中，我们研究了如何在表上捕获更改数据，并尝试了获取更改。 这是一种我们可以在表级别跟踪数据更改并查询更改内容的方法。 尽管此功能很有用，但我们还应考虑以某种方式对其进行架构设计，以使我们确保它不会造成中断，并可以快速识别要监视的对象。

参考资料 (References)

• Function cdc_get_all_changes函数cdc_get_all_changes
• Log Sequence Numbers日志序号
• Enabling Change Data Capture启用变更数据捕获

翻译自: https://www.sqlshack.com/monitoring-changes-in-sql-server-using-change-data-capture/

sql文件中捕获异常