mssql数据变化的实时监测-猿码集

介绍

在使用Microsoft SQL Server进行开发和管理时，监控数据库的变化至关重要。实时监视数据库的数据变化可以检测到潜在的问题，并且在问题出现时，可以立即采取行动。本文将讨论如何使用Mssql进行数据变化的实时监测。

使用触发器实现数据实时监测

什么是触发器？

触发器是Microsoft SQL Server中的一种特殊类型的存储过程。触发器跟踪数据库表中的数据更改，例如insert,update和delete，然后根据需要自动执行一些操作。传统的触发器支持以下两种触发操作：

1、For Insert - 当在表中插入一条新记录时触发。

2、For Update - 当在表中更新一条记录时触发。

这些触发器是事务性的，这意味着当事务未完全提交时，触发器不会尝试执行操作。

如何创建触发器？

下面是一个简单的创建触发器的示例。在这个示例中，我们将跟踪Temp_Monitor表的insert操作，并将相关信息插入到另一张表Temp_Monitor_Log中。


CREATE TRIGGER tr_Temp_Monitor_Insert
ON Temp_Monitor
FOR INSERT
AS
BEGIN
    INSERT INTO Temp_Monitor_Log (Monitored_Date, Monitored_Time, Log_Desc)
    SELECT CAST(GETDATE() AS DATE), CAST(GETDATE() AS TIME), 'New records inserted into Temp_Monitor'
END

在上面的代码中，我们创建了一个名为tr_Temp_Monitor_Insert的触发器。它在Temp_Monitor表的insert操作上执行，并将日志信息插入到Temp_Monitor_Log表中。

使用INSERTED和DELETED临时表进行记录映射

触发器使用INSERTED和DELETED临时表进行记录映射。INSERTED临时表包含新插入或更新的记录，而DELETED临时表包含新记录之前的记录。以下示例演示如何使用INSERTED和DELETED两个临时表进行记录的映射。在此示例中，我们将演示如何跟踪Temp_Monitor表的更新操作，并将所有更改信息插入到Temp_Monitor_Log表中。


CREATE TRIGGER tr_Temp_Monitor_Update
ON Temp_Monitor
FOR UPDATE
AS
BEGIN
    DECLARE @Monitored_Date DATE
    DECLARE @Monitored_Time TIME
    DECLARE @Oper_Desc VARCHAR(MAX)
    
    SELECT @Monitored_Date = CAST(GETDATE() AS DATE), @Monitored_Time = CAST(GETDATE() AS TIME)
    
    SELECT @Oper_Desc = 'Updated records from Temp_Monitor: '
    + CAST(ISNULL(d.survival_rate, i.survival_rate) AS VARCHAR(100))
    + '->' + CAST(ISNULL(i.survival_rate, d.survival_rate) AS VARCHAR(100))
    FROM INSERTED i
    INNER JOIN DELETED d
    ON i.id = d.id
    
    INSERT INTO Temp_Monitor_Log (Monitored_Date, Monitored_Time, Log_Desc)
    SELECT @Monitored_Date, @Monitored_Time, @Oper_Desc
END

在上面的代码中，我们创建了一个名为tr_Temp_Monitor_Update的触发器。它在Temp_Monitor表的update操作上执行，并将更改日志信息插入到Temp_Monitor_Log表中。我们使用INSERTED和DELETED两个临时表进行记录的映射，并使用ISNULL函数来检查相应列是否为空。此操作确保如果一列为空，则使用另一列的值进行更新。

清除触发器

使用以下代码可以清除触发器：


DROP TRIGGER [dbo].[tr_Temp_Monitor_Insert]
DROP TRIGGER [dbo].[tr_Temp_Monitor_Update]

使用Change Data Capture(CDC)实现数据实时监测

Change Data Capture(CDC)是一种SQL Server功能，可在数据库表更改时捕获这些更改。有了CDC，我们可以跟踪更改的细节，例如更新、删除和插入，可以从捕获的更改数据中派生许多应用程序 – 例如审计、复制、数据集成和数据仓库。

CDC工作原理

当启用CDC功能时，SQL Server会为每个表创建一个关联的捕获实例。索引和触发器用于捕获数据更改操作。操作后的SQL Server日志文件用于内部检测更改。当启用CDC时，SQL Server会在挂起交易和提交交易之间的时间捕获所有的更改。 CDC捕获的更改信息存储在系统表中，此数据存储在与生产数据库实例相分离的额外的数据库实例中。在这种情况下，为CDC捕获数据创建了一个独立的微型数据库。

如何开启CDC功能？

可以通过SQL Server 2008 R2或更高版本应用程序实现CDC功能。启用CDC功能的第一步是在数据库级别上启用此功能。

USE ExampleDB GO EXEC sys.sp_cdc_enable_db

可以使用如下的语法为表启用CDC功能。在这个示例中，我们将修改表Temp_Monitor并跟踪更改。


USE ExampleDB
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'Temp_Monitor', 
@role_name     = NULL,
@supports_net_changes = 1

当我们为表启用CDC时，将会自动为目标表创建一个关联的CDC表，以存储捕获的更改数据。我们可以针对此CDC表运行查询以检索有关数据更改的详细信息。

如何查询CDC表中的数据？

可以使用以下代码查询CDC表中的数据：


SELECT * FROM cdc.dbo_Temp_Monitor_CT
GO

此查询将返回一个包含CDC表中所有更改数据的表。每条记录代表一个相应的更改，包括哪个行已插入、更新、删除、何时以及对哪个列进行了更改。

如何清除CDC配置？

下面是一个清除CDC配置的示例:

USE ExampleDB GO EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'Temp_Monitor', @capture_instance = 'dbo_Temp_Monitor' GO

这将停用CDC跟踪表面（dbo.Temp_Monitor）的更改捕获。

如果您想在数据库级别上禁用CDC，则可以按如下方式启用:

USE ExampleDB GO EXEC sys.sp_cdc_disable_db

CDC和触发器的比较

CDC和触发器都可以用于实时监测SQL Server的数据变化。但是，以下是CDC和触发器之间的一些区别:

1、CDC是SQL Server的内置功能，而触发器需要手动创建和配置。

2、CDC使用系统表格来跟踪更改，而触发器使用用户定义的表格。

3、CDC数据可在多个实例之间复制，而触发器的更改不进行复制。

4、CDC捕获变化的性能和速度比触发器更好。

结论

本文讨论了如何使用触发器和CDC实时监测Mssql中的数据变化。无论是使用触发器还是CDC，都可以有效地监控数据变化并解决潜在问题。有了这些方法，可以更容易地处理与数据库表更改以及其他相关问题的情况。无论您选择哪种方法，都需要确保您的数据库安全并且高效运作。

mssql数据变化的实时监测

介绍