Обычно приложение имеет постоянное соединение с базой данных, но не всегда это возможно. Примерами таких ситуаций могут быть ненадежное соединение, периодически подключаемые пользователи или мобильные устройства. Обычно в такой ситуации приложение имеет легкий, но функциональный локальный кэш и периодическую одно или двухстороннюю синхронизацию с основной базой данных. Одной из самых главных проблем для разработчиков до выхода SQL Server 2008 была необходимость определить, какие данные были изменены в базе данных, чтобы произвести синхронизацию. Требовалось проектировать и реализовывать свое индивидуальное решение, что обычно означало использование триггеров на отслеживаемых таблицах, которые записывали бы данные об изменениях. Необходимо было написать код, который на основании этих данных отображал изменения, а также не забыть о периодической очистке старых ненужных данных. Спроектировать такое решение простым и в то же время не оказывающим тяжелого влияния на текущую нагрузку в базе данных это довольно тяжелая задача.
С появлением в SQL Server 2008 новой технологии Change Tracking решать такие задачи становится гораздо легче. Как только мы включаем Change Tracking на таблице он начинает отслеживать все DML операции и первичные ключи строк, которые они затронули. Мы можем определить, какая операция была произведена над строкой: вставка, изменение или удаление, а используя соединение с исходной таблицей по первичному ключу – получить актуальные данных в остальных столбцах. Опционально можно также получить информации и о том, какие столбцы были изменены.
Давайте расмотрим все это поподробнее на примерах. Для начала я создам простую базу данных клиентов и заказов. Сразу скажу, что структура базы данных предназначена только для тестирования Change Tracking и ни в коем случае не стоит опирать на нее, проектируя ваши решения, и запускайте все примеры из этой статьи только на тестовом сервере.
-- Создаем БД TrackingChanges_test и набор таблиц в ней для тестирования Change Tracking
USE [master];
GO
-- !!! ВНИМАНИЕ !!! Если уже есть БД с таким же именем, она будет удалена.
IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = N'TrackingChanges_test')
DROP DATABASE [TrackingChanges_test];
GO
CREATE DATABASE [TrackingChanges_test];
GO
USE [TrackingChanges_test];
GO
-- Таблица с клиентами будет содержать синтетический числовой индентификато клиента и его имя в произвольном формате.
CREATE TABLE [dbo].[clients] (
[client_id] INT PRIMARY KEY,
[name] nvarchar(300)
);
GO
-- Таблица с заказами будет содержать числовой IDENTITY ключ заказа, дату, описание, состав заказа в XML формате,
-- стоимость и ссылку на индентификатор клиента, который оформил заказ.
CREATE TABLE [dbo].[orders] (
[order_id] INT IDENTITY(1, 1) PRIMARY KEY,
[order_date] datetime,
[order_description] nvarchar(max),
[order_details] xml,
[order_amount] decimal(9, 2),
[client_id] int
);
GO
ALTER TABLE [dbo].[orders]
ADD CONSTRAINT [FK_orders_clients] FOREIGN KEY
(
[client_id]
) REFERENCES [dbo].[clients]
(
[client_id]
)
ON UPDATE CASCADE
ON DELETE SET NULL;
GO
INSERT INTO [dbo].[clients] ([client_id], [name])
VALUES (1, 'Иванов Сидор'), (2, 'Петров Иван'), (3, 'Сидоров Петр');
GO
INSERT INTO [dbo].[orders] (
[order_date]
,[order_description]
,[order_details]
,[order_amount]
,[client_id]
)
VALUES (
'20120115 15:45:32'
,N'Продукты с доставкой на дом.'
,N'
Хлеб
1
Молоко
5
'
,234.56
,1
), (
'20120623 13:28'
,N'Запчасти к самолету.'
,N'
Фюзеляж
1
Топливный насос
1
'
,67800.00
,3
)
;
GO
Включить Change Tracking можно двумя способами. Первый – используя SQL Server Management Studio, изменить соответствующие параметры в свойствах базы данных на вкладке Change Tracking.
Что означают различные параметры:
-
Change Tracking
Этот параметр используется, чтобы включить или отключить отслеживание изменений для базы данных. Для этого необходимо иметь разрешение db_owner в базе данных или выше. Соответственно именив значение этого параметра на True, мы позволяем в дальнейшем включать отслеживание изменений для отдельных таблиц.
-
Retention Period
Указывает минимальный срок хранения данных отслеживания изменений в базе данных. Данные удаляются только в случае, если параметр Автоматическая очистка имеет значение True. Значение по умолчанию равно 2. Рекомендуется выставлять это значение таким, чтобы в указанный интервал все клиенты гарантированно проводили синхронизацию и получали новый список изменений, иначе им придется делать полную синхронизацию данных. На этом мы остановимся дальше чуть-чуть подробнее.
-
Retention Period Units
Указывает единицы изменения для значения параметра «Retention Period». Может быть выбрано одно из следующих значений: Дней, Часов или Минут. Значение по умолчанию — Дней. Минимальный срок хранения составляет 1 минуту. Максимальный срок хранения не предусмотрен.
-
Auto Clean-Up
Указывает, производится ли автоматическое удаление данных отслеживания изменений по истечении заданного срока хранения. Если параметр Автоматическая очистка включен, то любой ранее заданный срок хранения сбрасывается в значение по умолчанию — 2 дня.
Второй способ включить Change Tracking – использовать DDL инструкию T-SQL ALTER DATABASE, например:
ALTER DATABASE [TrackingChanges_test]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO
После того, как Change Tracking включен на уровне базы данных, необходимо включить его для каждой таблицы, на которой мы хотим отслеживать изменения. Это опять же можно сделать либо с помощью команды ALTER TABLE, либо через SSMS в свойствах таблицы.
Вот так это выглядит в SSMS:
Дополнительный параметр «Track Columns Updated» указывает, будет ли производится отслеживание, какие именно столбцы были изменены.
А вот так это можно сделать с помощью DDL инструкций:
ALTER TABLE [dbo].[clients] ENABLE CHANGE_TRACKING;
GO
ALTER TABLE [dbo].[orders]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
GO
Для таблицы [dbo].[orders] я дополнительно включил отслеживание, какие столбцы изменились. На текущий момент я включил Change Tracking на уровне базы данных и для двух таблиц. Настала пора внести несколько изменений в таблицы и посмотреть на Change Tracking в действии. Для этого я добавлю один заказ, сразу его изменю, также изменю один из существующих заказов и удалю одного из клиентов.
-- Внесем несколько изменений в существующую базу данных
-- Добавляем новый заказ
INSERT INTO [dbo].[orders] (
[order_date]
,[order_description]
,[order_details]
,[order_amount]
,[client_id]
)
VALUES (
'20120903 12:00:00'
,N'Без описания.'
,N' '
,0.0
,2
);
GO
-- Меняем только что введенный заказ
UPDATE [dbo].[orders] SET [order_amount] = 1 WHERE [order_id] = 3;
GO
-- Меняем существующий заказ
UPDATE [dbo].[orders] SET [order_description] = N'Запчасти к самолету ТУ-134.' WHERE [order_id] = 2;
GO
-- Удаляю одного из клиентов
DELETE FROM [dbo].[clients] WHERE [client_id] = 1;
GO
Сразу необходимо ввести понятие как версия изменений. Изначально, как только мы включили Change Tracking текущая версия изменений в базе данных равна 0 и она будет увеличиваться на 1 каждый раз как происходит DML операция. Т.е. после того, как я внес изменения в базу, текущая версия изменений будет равна 4 (было 4 операции INSERT, 2 раза UPDATE и 1 раз DELETE). На удаленном клиенте я должен буду запоминать после синхронизации текущую версию, чтобы в следующий раз, используя это значение, получить список изменений, произошедщий с этой версии. А т.к. на сервере идет очистка данных, то я должен буду сравнить клиентскую версию изменений с серверной, и в случае если она меньше (или клиент никогда не проводил синхронизацию), мне ничего не остается, как заново полностью скачать все данные с сервера.
/* ----- Получаем текущую версию изменений в базе данных и исходные данные. ----- */
-- Получаем текущую и минимально доступную версию изменений. Т.к. мы сделали 4 изменения, то текущая версия у нас 4.
-- Минимально доступная будет равно 0.
DECLARE @next_synchronization_version bigint, @min_valid_synchronization_version bigint;
SET @next_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();
SET @min_valid_synchronization_version = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('[dbo].[clients]'));
SELECT @min_valid_synchronization_version, @next_synchronization_version;
-- Получаем начальные данные.
SELECT [client_id], [name]
FROM [dbo].[clients];
GO
SELECT [order_id],[order_date],[order_description],[order_details],[order_amount],[client_id]
FROM [dbo].[orders];
GO
/* ----- Проверяем версию изменений ----- */
-- Предположим на клиенте версия имеет значение -1, чтобы эмулировать ситуацию, когда клиент долго не делал именения.
DECLARE @last_synchronization_version bigint = -1;
-- Проверяем определенную таблицу.
IF (@last_synchronization_version < CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('[dbo].[clients]')))
BEGIN
PRINT 'Клиент должен быть переинициализирован.'
END
-- Проверяем весь список таблиц
IF EXISTS (
SELECT * FROM sys.change_tracking_tables
WHERE min_valid_version > @last_synchronization_version
)
BEGIN
PRINT 'Клиент должен быть переинициализирован.'
END
GO
Список изменений для определенной таблицы можно получить с помощью функции CHANGETABLE. Давайте получим и посмотрим на список изменений, произошедших в таблице [dbo].[orders] начиная с версии 0.
/* ----- Используем функции для получения списка изменений ----- */
DECLARE @last_synchronization_version bigint = 0;
SELECT
CT.order_id
,CT.SYS_CHANGE_VERSION
,CT.SYS_CHANGE_CREATION_VERSION
,CT.SYS_CHANGE_OPERATION
,CT.SYS_CHANGE_COLUMNS
,CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [dbo].[orders], @last_synchronization_version) AS CT
GO
Мы видими первичный ключ строк, которые были изменены, версию изменений, тип операции, маску измененных столбцов и контекст изменения. Стоит отметить, что для заказа с [order_id] = 3 возвращается лишь одна операция типа INSERT, т.к. мы получаем на выходе так называемые «чистые» изменения: операции вставки и последующего изменения объединяются в одну, но при этом версия создания и версия изменения отличаются. Для операций типа UPDATE отображается битовая маска измененных столбцов. К тому, как лучше работать с маской измененных столбцов и что такое контекст операции, мы вернемся чуть позже.
Итак, клиент получил список изменений. Для того, чтобы получить не только список изменных строк, но и сами данные необходимо сделать соединение результатов функции CHANGETABLE и исходной таблицы.
-- Получаем список измененных строк и актуальные данные.
DECLARE @last_synchronization_version bigint = 0;
SELECT
CT.order_id
,o.[order_date]
,o.[order_description]
,o.[order_details]
,o.[order_amount]
,o.[client_id]
,CT.SYS_CHANGE_VERSION
,CT.SYS_CHANGE_CREATION_VERSION
,CT.SYS_CHANGE_OPERATION
,CT.SYS_CHANGE_COLUMNS
,CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [dbo].[orders], @last_synchronization_version) AS CT
LEFT OUTER JOIN [dbo].[orders] AS o ON o.[order_id] = CT.[order_id]
;
GO
Теперь давайте вернемся к отслеживанию, какие именно столбцы были изменены. Имеет смысл это отслеживать и обрабатывать например для LOB столбцов, когда стоимость изменения гораздо больше, нежели проверить было ли оно вообще. Не рекомендуется интерпретировать битовую маску, возвращаемую функцией CHANGETABLE самостоятельно. Для этого нужно использовать функцию CHANGE_TRACKING_IS_COLUMN_IN_MASK.
/* ----- Пример отслеживания изменений в столбцах ----- */
DECLARE @last_synchronization_version bigint = 0;
DECLARE @ColumnID int = COLUMNPROPERTY(OBJECT_ID('dbo.orders'),'order_description', 'ColumnId');
SELECT
CT.order_id
,o.[order_date]
,CASE WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(@ColumnID, CT.SYS_CHANGE_COLUMNS) = 1
THEN o.[order_description] ELSE NULL END AS [order_description]
,CHANGE_TRACKING_IS_COLUMN_IN_MASK(@ColumnID, CT.SYS_CHANGE_COLUMNS) AS [is_order_description_changed]
,o.[order_details]
,o.[order_amount]
,o.[client_id]
,CT.SYS_CHANGE_VERSION
,CT.SYS_CHANGE_CREATION_VERSION
,CT.SYS_CHANGE_OPERATION
,CT.SYS_CHANGE_COLUMNS
,CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [dbo].[orders], @last_synchronization_version) AS CT
LEFT OUTER JOIN [dbo].[orders] AS o ON o.[order_id] = CT.[order_id]
;
GO
Таким образом, если не было изменений в столбце, то клиенту даже не будет передаваться значение LOB столбца, что к тому может сильно сократить траффик с сервером при синхронизации.
И последний пример, который я хотел показать, это работа с контекстом. При изменении данных, можно указать контекст, например название приложения или идентификатор клиента. Клиент в свою очередь при синхронизации может фильтровать по контексту и получать только те изменения, которые относятся к нему. Например, давайте на сервере, используя в качестве контекста клиента идентификатор приложения внесем изменения в наши данные.
-- Используя в качестве контекста идентификатор приложения внесем изменения.
DECLARE @originator_id varbinary(128);
SET @originator_id = CAST('MyApplicationID_1' AS varbinary(128));
WITH CHANGE_TRACKING_CONTEXT (@originator_id)
UPDATE [dbo].[orders] SET [order_amount] = 2.0 WHERE [order_id] = 3;
GO
Если приложение с другим идентификатом при получении информации об изменениях с сервера будет указывать свой контекст, то оно не получит никаких данных, что позволяет нам сделать изменения доступными только определенным клиентам.
DECLARE @last_synchronization_version bigint = 0;
SELECT
CT.order_id
,o.[order_date]
,o.[order_description]
,o.[order_details]
,o.[order_amount]
,o.[client_id]
,CT.SYS_CHANGE_VERSION
,CT.SYS_CHANGE_CREATION_VERSION
,CT.SYS_CHANGE_OPERATION
,CT.SYS_CHANGE_COLUMNS
,CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [dbo].[orders], @last_synchronization_version) AS CT
LEFT OUTER JOIN [dbo].[orders] AS o ON o.[order_id] = CT.[order_id]
WHERE COALESCE(CT.SYS_CHANGE_CONTEXT, 0) = CAST('MyApplicationID_2' AS varbinary(128))
;
GO
В принципе на этом наш небольшой обзор технологии Change Tracking пора заканчивать. Напоследок я хотел бы еще сказать несколько слов, о том, как это технология работает изнутри. Когда мы включаем отслеживание изменений для определенной таблицы, создается скрытая системная таблица с именем [sys].[change_tracking_<table_id>], в которую будут записываться информация об изменениях. Также Change Tracking является синхронной операцией и влияет на все планы DML запросов к отслеживаемым таблицам: в планы добавляются операции по вставке в скрытые системные таблицы необходимой информации. Поэтому Change Tracking будет влиять на вашу текущую нагрузку, что необходимо учитывать при проектировании вашего решения.
Вот в принципе и все. Это первая, но не единственная статья о технологиях отслеживания изменений, которые в ближайшее время появятся в моем блоге. Можете оставлять ваши комментарии. И напомню, что 17 ноября 2012 г. на мероприятии SQL Saturday #178 я планирую выступить с докладом Change Data Capture, Change Tracking и SSIS 2012.