Вы когда-нибудь задумывались, есть ли способ отслеживать и отменять изменения, внесенные в вашу базу данных SQL Server? Что ж, вам повезло! В этой статье мы рассмотрим функцию Change Data Capture (CDC) в SQL Server и то, как ее можно использовать для обеспечения возможностей отмены/повтора.

Функция CDC была введена компанией Microsoft в SQL Server 2008 в составе версии Enterprise. Она позволяет отслеживать и сохранять изменения, внесенные в таблицы базы данных, что упрощает отслеживание и анализ изменений данных со временем.

Давайте рассмотрим сценарий, в котором CDC может быть полезной. Представьте, что у вас есть система, в которой пользователи могут вводить данные, но иногда случаются ошибки. Вы хотите иметь возможность откатить определенные изменения, внесенные пользователем, не затрагивая другие данные. Создание резервной копии и восстановление не являются вариантом, так как вы хотите откатить только определенный набор данных.

Один из подходов к достижению этой цели – это создание системы отслеживания с использованием триггеров. Однако есть и другой вариант – использовать возможности CDC. В этой статье мы сосредоточимся на использовании CDC для чтения данных с предыдущей точки во времени, а затем использования оператора MERGE для отмены изменений.

Сначала давайте включим CDC для нашей базы данных:

USE master
GO
CREATE DATABASE TestCDC
GO
USE TestCDC
GO
EXEC sys.sp_cdc_enable_db
GO

Затем мы создадим таблицу с названием “TimeLog” и включим CDC для нее:

CREATE TABLE TimeLog (
    TimeLogId INT IDENTITY PRIMARY KEY,
    UserId INT NOT NULL,
    TimeStart DATETIME NOT NULL,
    TimeEnd DATETIME NULL,
    Description VARCHAR(200),
    Created DATETIME DEFAULT GETDATE(),
    Modified DATETIME DEFAULT GETDATE()
)
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'TimeLog',
    @role_name = NULL,
    @filegroup_name = N'PRIMARY',
    @supports_net_changes = 1
GO

Теперь, когда CDC включена, мы можем начать вставлять некоторые данные для имитации регистрации пользователей:

INSERT INTO TimeLog (UserId, TimeStart, Description)
VALUES (1, '2012-01-01 10:00:00', '1-й рабочий день')
GO

-- Подождите некоторое время

INSERT INTO TimeLog (UserId, TimeStart, Description)
VALUES (1, '2012-01-02 10:00:00', '2-й рабочий день')
GO

-- Подождите некоторое время

UPDATE TimeLog
SET TimeEnd = '2013-01-01 19:00:00', Modified = GETDATE()
WHERE UserId = 1 AND TimeStart = '2012-01-01 10:00:00'
GO

-- Подождите некоторое время

INSERT INTO TimeLog (UserId, TimeStart, Description)
VALUES (2, '2012-01-01 09:00:00', '1-й рабочий день')
GO

-- Подождите некоторое время

INSERT INTO TimeLog (UserId, TimeStart, Description)
VALUES (2, '2012-01-02 09:00:00', '2-й рабочий день')
GO

-- Подождите некоторое время

INSERT INTO TimeLog (UserId, TimeStart, Description)
VALUES (1, '2012-01-03 10:00:00', '3-й рабочий день')
GO

Теперь, когда у нас есть некоторые данные, давайте рассмотрим, как CDC может помочь нам отслеживать и отменять изменения. CDC предоставляет несколько функций, которые мы можем использовать для выбора изменений, которые произошли. Одна из этих функций – sys.fn_cdc_map_time_to_lsn, позволяет сопоставить временную метку с номером последовательности журнала транзакций (LSN).

Например, если мы хотим получить все изменения, которые произошли в течение последних 24 часов, мы можем использовать следующий код:

DECLARE @begin_time datetime, @end_time datetime, @begin_lsn binary(10), @end_lsn binary(10);

SET @begin_time = GETDATE() - 1;
SET @end_time = GETDATE();

SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

Теперь, когда у нас есть LSN для нужного временного диапазона, мы можем использовать функцию cdc.fn_cdc_get_all_changes_dbo_TimeLog для получения всех изменений:

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_TimeLog(@begin_lsn, @end_lsn, 'all update old')

Эта функция возвращает все изменения, которые произошли, включая обновления, представленные как строки удаления и вставки. Теперь мы можем проанализировать изменения и определить любые некорректные данные, которые необходимо отменить.

Для отмены изменений мы можем использовать оператор MERGE. Оператор MERGE позволяет выполнять операции вставки, обновления и удаления в одном операторе на основе указанного условия. В нашем случае мы будем использовать данные CDC в качестве источника и таблицу TimeLog в качестве назначения.

DECLARE @UserId INT = 1;
DECLARE @begin_time datetime = GETDATE() - 1;
DECLARE @end_time datetime = '2012-03-05 19:38:04.727';
DECLARE @begin_lsn binary(10) = (SELECT sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time));
DECLARE @end_lsn binary(10) = (SELECT sys.fn_cdc_map_time_to_lsn('largest less than', @end_time));

SET IDENTITY_INSERT TimeLog ON;

MERGE INTO TimeLog tgt
USING (
    SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_TimeLog(@begin_lsn, @end_lsn, 'all')
    WHERE UserId = @UserId
) src
ON tgt.TimeLogId = src.TimeLogId
WHEN MATCHED THEN
    UPDATE SET tgt.TimeStart = src.TimeStart, tgt.TimeEnd = src.TimeEnd, tgt.Description = src.Description, tgt.Created = src.Created, tgt.Modified = src.Modified
WHEN NOT MATCHED BY TARGET THEN
    INSERT (TimeLogId, UserId, TimeStart, TimeEnd, Description, Created, Modified)
    VALUES (src.TimeLogId, src.UserId, src.TimeStart, src.TimeEnd, src.Description, src.Created, src.Modified)
WHEN NOT MATCHED BY SOURCE AND tgt.UserId = @UserId THEN
    DELETE;

SET IDENTITY_INSERT TimeLog OFF;
GO

В приведенном выше коде мы сбрасываем данные для определенного пользователя (UserId = 1) до определенного момента времени. Мы используем ту же функцию CDC для получения чистых изменений до момента, когда были введены некорректные данные. Затем мы используем оператор MERGE для обновления таблицы TimeLog данными из источника CDC. Строки, которые существуют в источнике, но не существуют в целевой таблице, вставляются, а строки, которых нет в источнике, но присутствуют в целевой таблице и имеют UserId = 1, удаляются.

После выполнения кода мы можем проверить, что изменения были отменены:

SELECT * FROM TimeLog

Вот и все! Мы успешно использовали CDC и оператор MERGE для отслеживания и отмены изменений, внесенных в нашу базу данных. Важно отметить, что это простой пример, и в реальном сценарии необходимо учитывать дополнительные аспекты, такие как транзакционная целостность.

В заключение, CDC – это мощная функция в SQL Server, которая позволяет отслеживать и анализировать изменения, внесенные в базу данных. Она может быть особенно полезна в ситуациях, когда вам необходимо предоставить возможности отмены/повтора. Используя CDC и оператор MERGE, вы можете легко отменить определенные изменения, не затрагивая другие данные.

Надеюсь, эта статья дала вам хорошее представление о CDC и его потенциальных применениях. Удачного кодирования!