Tag Archives: Change Tracking

Видео: Visual Studio 2010, SQL Server и Change Tracking

Пример создания приложения на Visual Studio 2010, которое использует локальный кэш и синхронизацию с основной базой данных MS SQL Server с помощью технологии Change Tracking.

SQL Server, Change Tracking и Visual Studio 2010 – практический пример

В данной статье я хочу показать, как с помощью SQL Server, Change Tracking и Visual Studio 2012 создать небольшое приложение, которое будет иметь свою локальную базу данных и сможет выполнять двухстороннюю синхронизацию с основным сервером. Это как раз основное предназначение технологии. В качестве примера я буду использовать базу данных из своей предыдущей статьи (также скрипт создания базы можно скачать по этой ссылке). В качестве среды разработки буду использовать Visual Studio 2010 и C#, в качестве основного сервера баз данных MS SQL Server установленный локально на моем ноутбуке.

  1. Шаг первый: выполняем скрипт создания базы данных на локальном SQL Server.
  2. Запускаем Visual Studio и создаем новый проект Windows Forms.

  3. В меню Project -> Add New Item выбираем Local Database Cache и добавляем его в текущий проект.

  4. В проект будет добавлен файл .sync и откроется окно Configure Data Synchronization.

  5. Для установки Server connection нажимаем кнопку New и создаем подключение с нашим локальным SQL Server.

  6. Параметр Client Connection можно настроить на уже существующий файл SQL Server Compact Edition. Если нет локальной базы данных, можно оставить настройку TrackingChanges_test.sdf (new) по умолчанию для создания новой базы данных в проекте. Имя новой базы данных будет основываться на имени базы данных на сервере.
  7. Опцию Use SQL Server change tracking оставляем включенной по умолчанию.
  8. В раздел Cached Tables с помощью кнопки OK добавляем таблицы, для которых мы хотим настроить лакольное кэширование. Появляется окно Configure Tables for Offline Use. Значение параметра Data to download оставляем по умолчанию New and incremental changes after first synchronization. Наше приложение будет извлекать из сервера записи, которые были изменены с момента последней синхронизации. Во время первой синхронизации будет загружена вся таблица. Если выбрать значение Entire table each time, то при синхронизации локальная копия таблицы будет заменяться ее версией с сервера баз данных.

  9. Теперь в окне Configure Data Synchronization развернем раздел Advanced. С помощью опции Synchronize tables in a single transaction мы можем задать, будут ли таблицы синхронизированы по отдельности или в пределах одной транзакции. По умолчанию этот флажок не установлен, и все таблицы будут синхронизироваться по отдельности. Если возникнут ошибки, только таблицы с ошибками откатят свои изменения. Если выбран этот параметр, все таблицы синхронизируются в одной транзакции. Если обнаружены ошибки, все модификации для всех таблиц откатываются.
  10. Для параметра Create synchronization components оставляем значение по умолчанию Client and Server, т.к. мы хотим создать приложение, которое будет выполнять двухстороннюю синхронизацию между кэшем и основной базой данных.
  11. Если мы нажмем ссылку Show Code Example, то нам будет приведен пример кода для нашего приложения, который будет делать синхронизацию. Скопируем его в буфер обмена, т.к. он нам еще пригодится.

  12. Нажимаем кнопку OK и закрываем окно Configure Data Synchronization. Теперь перед нами появляется Data Source Configuration Wizard.

  13. Оставляем по умолчанию значение Dataset и жмем Next.
  14. В появившемся окне выбираем таблицы clients и orders, задаем имя нашему DataSet и жмем Finish.

  15. В принципе на этом настройка локального кэша в нашем приложении закончена. Давайте теперь добавим несколько элементов в наше приложение, чтобы визуально посмотреть, как все будет работать. В первую очередь добавим элемент DataGridView и в качестве Data Source я выберу таблицу clients из моего MyDataSet.

    Также я оставлю возможность редактировать записи прямо в моем DataGridView и для удобства переименую его в dataGridView_clients.

  16. Аналогичным образом я добавляю еще один DataGridView для таблицы orders. Также мне потребуются 3 кнопки: Sync, Submit Changes и Refresh Data.
  17. Кнопка Refresh Data будет обновлять наши элементы DataGridView записями из локального кэша. В обработчик на нажатие этой кнопки я добавлю следующий код.
  18. try
    {
        this.ordersTableAdapter.Fill(this.myDataSet.orders);
        this.clientsTableAdapter.Fill(this.myDataSet.clients);
    }
    catch (System.Exception ex)
    {
        System.Windows.Forms.MessageBox.Show(ex.Message);
    }
    
  19. Задача кнопки Submit Changes – передавать и сохранять измененения в элементах DataGridView в наш локальный кэш приложения. На ней соответственно будет исполнятся следующий код.
  20. try
    {
        this.Validate();
    
        this.clientsBindingSource.EndEdit();
        this.ordersBindingSource.EndEdit();
    
        this.clientsTableAdapter.Update(this.myDataSet.clients);
        this.ordersTableAdapter.Update(this.myDataSet.orders);
    
        MessageBox.Show("Update successful");
    }
    catch (System.Exception ex)
    {
        MessageBox.Show("Update failed: " + ex.Message);
    }
    
  21. И наконец с помощью кнопки Sync будет выполняться синхронизация нашего локального кэша с основной базой данных.
  22. try
    {
        MyLocalDataCacheSyncAgent syncAgent = new MyLocalDataCacheSyncAgent();
        Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();
    
        MessageBox.Show("Changes downloaded: " + syncStats.TotalChangesDownloaded.ToString() + Environment.NewLine + "Changes uploaded: " + syncStats.TotalChangesUploaded.ToString());
    }
    catch (System.Exception ex)
    {
        System.Windows.Forms.MessageBox.Show(ex.Message);
    }
    
  23. Также для того, чтобы обеспечить именно двухстороннюю синхронизацию, в окне Solution Explorer на MyLocalDataCache.sync жмем правой клавишей мыши, выбираем View Code и задаем такой код:
  24. namespace ChangeTrackingSyncApp_example {
        
        public partial class MyLocalDataCacheSyncAgent {
            
            partial void OnInitialized(){
                this._clientsSyncTable.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional;
             this._ordersSyncTable.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional;
            }
        }
    }
    

Вот в принципе и все. На этом маленьком и максимально упрощенном примере я хотел показать, как просто можно создать приложение с локальным кэшем и использовать Change Tracking для двухсторонней синхронизации данных. В дополнение к статье я выложу видео, в котором я проделаю те же самые шаги, что и в статье, а также покажу, как будет работать наше приложение. Оставайтесь на связи, я планирую опубликовать еще одну заметку по Change Tracking и затем перейти к технологии Change Data Capture.

Начинаем работу с технологией Change Tracking

Обычно приложение имеет постоянное соединение с базой данных, но не всегда это возможно. Примерами таких ситуаций могут быть ненадежное соединение, периодически подключаемые пользователи или мобильные устройства. Обычно в такой ситуации приложение имеет легкий, но функциональный локальный кэш и периодическую одно или двухстороннюю синхронизацию с основной базой данных. Одной из самых главных проблем для разработчиков до выхода 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.