Как использовать пользовательскую систему отслеживания изменений

Статья
10/17/2009

Для многих приложений необходимо отслеживать изменения в базе данных сервера, чтобы эти изменения можно было передавать на клиенты в ходе последующего сеанса синхронизации. В данном разделе описаны требования к системе отслеживания изменений, а также демонстрируется, как создать нестандартную систему, которую смогут использовать службы Sync Framework. В ряде случаев приемлемо нестандартное отслеживание изменений. Однако следует иметь в виду, что это усложняет процесс и может повлиять на производительность базы данных сервера. При работе с SQL Server 2008 рекомендуется использовать функцию отслеживания изменений SQL Server. Дополнительные сведения см. в разделе Как использовать отслеживание изменений SQL Server.

Требования к серверу для синхронизации

Службы Sync Framework спроектированы таким образом, чтобы их влияние на базу данных сервера было минимальным. Поэтому изменения, необходимые для отслеживания изменений в базе данных сервера, пропорциональны уровню функциональности, заложенному в приложении. Следует учитывать следующие моменты.

На одном конце диапазона функциональности — моментальный снимок данных, предназначенный только для загрузки. Он не требует никаких изменений.
На другом конце — двусторонняя синхронизация с полным отслеживанием изменений и обнаружением конфликтов.

В следующей таблице обобщаются способы применения служб Sync Framework и приводятся соответствующие требования, предъявляемые к базе данных сервера.

Сценарий	Первичный ключ или уникальный столбец1	Отслеживание времени обновления	Отслеживание времени вставки	Отслеживание времени удаления	Отслеживание идентификатора клиента для обновлений	Отслеживание идентификатора клиента для вставок	Отслеживание идентификатора клиента для удалений
Загрузка моментального снимка данных на клиент	Нет	Нет	Нет	Нет	Нет	Нет	Нет
Загрузка добавочных операций вставки и обновления на клиент	Да	Да	Да2	Нет	Нет	Нет	Нет
Загрузка добавочных операций вставки, обновления и удаления на клиент	Да	Да	Да2	Да	Нет	Нет	Нет
Передача операций вставки на сервер	Да	Нет	Нет	Нет	Нет	Нет3	Нет
Передача операций вставки и обновления на сервер	Да	Нет	Нет	Нет	Нет3	Нет3	Нет
Передача операций вставки, обновления и удаления на сервер	Да	Нет	Нет	Нет	Нет3	Нет3	Нет3
Двунаправленные операции вставки и обновления с обнаружением конфликтов	Да	Да	Да2	Нет	Да4	Да4	Нет
Двунаправленные операции вставки, обновления и удаления с обнаружением конфликтов	Да	Да	Да2	Да	Да4	Да4	Да4

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

2 Обязательно, если необходимо отличать операции вставки и обновления. Дополнительные сведения см. в подразделе «Определение изменений данных, которые должны быть загружены на клиент» далее в этом разделе.

3 Обязательно, если строка может быть изменена несколькими клиентами и при этом имеет место необходимость определить, какой клиент изменил строку. Дополнительные сведения см. в подразделе «Определение клиента, выполнившего изменение данных» далее в этом разделе.

4 Обязательно, если не нужно возвращать изменения клиенту, который их выполнил. Дополнительные сведения см. в подразделе «Определение клиента, выполнившего изменение данных» далее в этом разделе.

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

Скорее всего, помимо описанных выше изменений, придется создать хранимые процедуры для доступа к данным. Большинство примеров в этой документации приведено на встроенном SQL, поскольку он позволяет проще продемонстрировать назначение кода. В производственных приложениях следует пользоваться хранимыми процедурами, поскольку они хорошо инкапсулируют код, как правило, обладают более высоким быстродействием и могут обеспечивать лучшую защищенность по сравнению со встроенным SQL (если правильно написаны).

Определение изменений данных, которые должны загружаться на клиент

При синхронизации только с загрузкой или при двунаправленной синхронизации все изменения отслеживаются на сервере, что позволяет службам Sync Framework определить, какие изменения должны быть переданы клиентам. Службы Sync Framework не определяют точный порядок поддержки отслеживания изменений, хотя для этого существует несколько разных подходов. Он применяется для обеспечения синхронизация всех таблиц.

Добавить столбец, отслеживающий вставку строки в базу данных сервера.
Добавить столбец, а в некоторых случаях триггер, отслеживающий последнее обновление строки в базе данных сервера.
Добавить таблицу захоронения и триггер, отслеживающий удаление строки в базе данных сервера. Если удалять данные с сервера нежелательно, но удаления нужно отправить клиенту, можно отслеживать логические удаления в базовой таблице. Для этого используется столбец, обычно типа bit, указывающий, что строка удалена. В другом столбце сохраняется время удаления.

Эти столбцы и таблицы захоронения совместно с привязками используются для указания операций вставки, обновления и удаления, которые требуют загрузки. Привязка определяет момент времени, который используется при синхронизации изменений. Рассмотрим следующие запросы.

Запрос, заданный в свойстве SelectIncrementalInsertsCommand. Этот запрос загружает добавочные операции вставки из таблицы Sales.Customer в образце базы данных Sync Framework следующим образом:
```
SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM
Sales.Customer WHERE InsertTimestamp > @sync_last_received_anchor
AND InsertTimestamp <= @sync_new_received_anchor
```
Дополнительные сведения об этом и других свойствах, относящихся к командам синхронизации, см. в разделе Как задать синхронизацию моментальными снимками, с загрузкой, с передачей и двунаправленную.
Запрос, заданный в свойстве SelectNewAnchorCommand. Этот запрос получает значение момента времени. В столбце InsertTimestamp хранятся значения timestamp. Поэтому он при помощи функции Transact-SQL MIN_ACTIVE_ROWVERSION, которая появилась в SQL Server 2005 с пакетом обновления 2 (SP2), получает значение timestamp из базы данных сервера:
```
SELECT @sync_new_received_anchor = MIN_ACTIVE_ROWVERSION - 1
```
Функция MIN_ACTIVE_ROWVERSION возвращает наименьшее активное значение timestamp (которое также называется rowversion) в текущей базе данных. Значение timestamp является активным, если оно используется в незафиксированной транзакции. Если в базе данных не существует активных значений, то функция MIN_ACTIVE_ROWVERSION возвращает то же значение, что и @@DBTS + 1. Функция MIN_ACTIVE_ROWVERSION полезна, например, когда при синхронизации данных значения timestamp используются для группирования наборов изменений. Если приложение пользуется в командах привязки функцией @@DBTS, а не MIN_ACTIVE_ROWVERSION, то при синхронизации могут быть пропущены изменения, которые станут активными.

При первой синхронизации таблицы Sales.Customer выполняются следующие действия.

Выполняется новая команда привязки. Она возвращает значение 0x0000000000000D49. Это значение сохраняется в клиентской базе данных. Синхронизация таблицы еще не выполнялась, поэтому значение привязки, хранящееся в клиентской базе данных со времени последней синхронизации, отсутствует. В этом случае платформы Sync Framework используют наименьшее значение, доступное для типа данных SQL Server timestamp: 0x0000000000000000. Запрос, который выполняют службы Sync Framework, выглядит следующим образом. Этот запрос загружает схему и все строки таблицы.
```
exec sp_executesql N'SELECT CustomerId, CustomerName, SalesPerson,
CustomerType FROM Sales.Customer WHERE (InsertTimestamp >
@sync_last_received_anchor AND InsertTimestamp <=
@sync_new_received_anchor)',N'@sync_last_received_anchor timestamp,
@sync_new_received_anchor timestamp',
@sync_last_received_anchor=0x0000000000000000,
@sync_new_received_anchor=0x0000000000000D49
```
Во время второй синхронизации выполняется новая команда привязки. С момента последней синхронизации производилась вставка строк, Поэтому команда возвращает значение 0x0000000000000D4C. Синхронизация таблицы выполнялась ранее, Поэтому платформы Sync Framework могут получить значение привязки: 0x0000000000000D49. Это значение сохранено в клиентской базе данных во время предыдущей синхронизации. Выполняется следующий запрос. Он загружает только те строки из таблицы, которые были вставлены за период, заданный двумя значениями привязки.
```
exec sp_executesql N'SELECT CustomerId, CustomerName, SalesPerson,
CustomerType FROM Sales.Customer WHERE (InsertTimestamp >
@sync_last_received_anchor AND InsertTimestamp <=
@sync_new_received_anchor)', N'@sync_last_received_anchor timestamp,
@sync_new_received_anchor timestamp',
@sync_last_received_anchor=0x0000000000000D49,
@sync_new_received_anchor=0x0000000000000D4C
```

Примеры команд обновления и удаления см. в разделах Как загружать на клиент добавочные изменения данных и Как установить двунаправленный обмен добавочными изменениями данных между клиентом и сервером.

Как отмечалось выше, команды, используемые для получения значений привязки, зависят от типа данных столбцов в базе данных сервера. В этой документации во всех примерах используется тип данных SQL Server timestamp, который также называется rowversion. Для столбца с типом данных SQL Server datetime запрос на новую команду привязки будет примерно таким:

SELECT @sync_new_received_anchor = GETUTCDATE()

Чтобы определить, какой тип данных следует использовать для привязки, необходимо взвесить требования приложения и степень гибкости, необходимую для изменения схемы базы данных сервера. Если база данных еще разрабатывается, можно точно указать, какие столбцы и триггеры нужно добавить. Если это производственная база данных, возможности разработчика могут быть ограничены. Необходимо учитывать следующие правила.

Все таблицы в группе синхронизации должны иметь одинаковый тип данных и новую команду привязки. По возможности указывайте один и тот же тип данных и одинаковую команду для всех групп.
Тип данных datetime прост для понимания, а в таблице часто уже имеется столбец, отслеживающий изменение строки. Однако использование этого типа данных может оказаться проблематичным, если клиенты находятся в разных часовых поясах. При использовании этого типа данных при добавочных изменениях могут быть пропущены некоторые транзакции.
Тип данных timestamp является точным и не зависит от часовых поясов. Однако каждая таблица в базе данных SQL Server может содержать только один столбец этого типа. Поэтому, если необходимо отличать вставки и обновления, можно добавить столбец с другим типом данных, например binary(8), и хранить значения timestamp в этом столбце. См. пример в разделе Инструкции по сценариям установки для поставщика базы данных. Тип данных timestamp может вызвать проблемы при восстановлении базы данных сервера из резервной копии. Дополнительные сведения см. в разделе Объекты базы данных, поддерживаемые платформой Sync Framework. Как отмечалось выше, в командах выборки новой привязки рекомендуется пользоваться функцией MIN_ACTIVE_ROWVERSION.

Определение клиента, выполнившего изменение данных

Определение клиента, выполнившего изменение данных, может оказаться необходимым по двум основным причинам.

Для поддержки обнаружения и разрешения конфликтов при синхронизации только с передачей и двусторонней синхронизации.

Если строку может изменить сервер и один или несколько клиентов, то могут потребоваться сведения о том, кем именно произведены изменения. Это позволит, например, написать код, определяющий приоритеты изменений. Если эти сведения недоступны, то большим приоритетом обладает последнее изменение строки.
Предотвращение отправки изменений обратно на клиент во время двунаправленной синхронизации.

Вначале службы Sync Framework передают изменения на сервер, а затем загружают их на клиент. Если идентификатор клиента, выполнившего изменение, не отслеживается, то оно будет передано на сервер, а затем загружено обратно на клиент во время того же сеанса синхронизации. В ряде случаев это приемлемо, а в других — нет.

Как и при отслеживании изменений, службы Sync Framework не определяют порядок поддержки отслеживания идентификаторов, однако имеется один общий подход. Он применяется для обеспечения синхронизация всех таблиц.

Добавить к базовой таблице столбец, в котором производится отслеживание клиента, который выполняет вставку.
Добавить к базовой таблице столбец, в котором производится отслеживание клиента, который выполняет обновление.
Добавить в таблицу захоронения столбец, в котором производится отслеживание клиента, который выполняет удаление.

Эти столбцы и таблицы используются вместе со свойством ClientId для определения клиента, который выполняет каждую операцию вставки, обновления и удаления. В момент первой синхронизации таблицы (любым методом, кроме синхронизации при помощи моментальных снимков) платформы Sync Framework сохраняют на клиенте значение идентификатора GUID, идентифицирующее этого клиента. Этот идентификатор передается DbServerSyncProvider для использования в запросах выборки и обновления в каждом SyncAdapter. Значение идентификатора доступно через свойство ClientId. Рассмотрим следующий запрос на языке Transact-SQL.

SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM
Sales.Customer WHERE InsertTimestamp > @sync_last_received_anchor AND
InsertTimestamp <= @sync_new_received_anchor AND InsertId <>
@sync_client_id

Он похож на приведенный выше запрос для отслеживания операций вставки на сервере. Инструкция в предложении WHERE гарантирует загрузку только тех операций вставки, которые выполнены на сервере или любом клиенте, который синхронизируется в настоящий момент.

Платформы Sync Framework также позволяют приложениям идентифицировать клиентов на сервере с помощью целого числа вместо значения GUID. Дополнительные сведения см. в разделе Как использовать переменные сеанса.

Примеры подготовки сервера

В следующем примере показано, как настроить в таблице Sales.Customer из образца базы данных Sync Framework инфраструктуру отслеживания для обработки наиболее сложного сценария приложения: двунаправленной синхронизации операций вставки, обновления и удаления с обнаружением конфликтов. Менее сложным сценариям требуется лишь часть этой инфраструктуры. Дополнительные сведения см. в подразделе «Требования к серверу для синхронизации» выше в этом разделе. Полный сценарий, создающий объекты для этого примера и дополнительные объекты, см. в разделе Инструкции по сценариям установки для поставщика базы данных. Дополнительные сведения об использовании этих объектов см. в разделе Как задать синхронизацию моментальными снимками, с загрузкой, с передачей и двунаправленную.

Примеры в этом разделе в процессе подготовки сервера выполняют следующие шаги.

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

Проверка схемы Sales.Customer

В следующем примере кода показана схема таблицы Sales.Customer. Таблица имеет первичный ключ на столбце CustomerId и не содержит столбцов, которые можно было бы использовать для отслеживания изменений.

CREATE TABLE SyncSamplesDb.Sales.Customer(
    CustomerId uniqueidentifier NOT NULL PRIMARY KEY DEFAULT NEWID(), 
    CustomerName nvarchar(100) NOT NULL,
    SalesPerson nvarchar(100) NOT NULL,
    CustomerType nvarchar(100) NOT NULL)

Добавление столбцов к отслеживанию операций вставки и обновления

В следующем примере кода добавляется четыре столбца: UpdateTimestamp, InsertTimestamp, UpdateId и InsertId. Столбец UpdateTimestamp имеет тип SQL Server timestamp. Он автоматически обновляется при обновлении строки. Как уже было отмечено, таблица может содержать только один столбец timestamp. Поэтому столбец InsertTimestamp имеет тип binary(8) и значение по умолчанию @@DBTS + 1. Пример добавляет единицу к значению, возвращаемому функцией @@DBTS, чтобы столбцы UpdateTimestamp и InsertTimestamp имели одинаковое значение после выполнения вставки. Если этого не сделать, то строка после вставки будет выглядеть как обновление.

Идентификатор, создаваемый платформами Sync Framework для каждого клиента, является идентификатором GUID, и поэтому два столбца идентификаторов представляют собой столбцы uniqueidentifier. По умолчанию они имеют значение 00000000-0000-0000-0000-000000000000. Это значение указывает, что сервер выполнил обновление или вставку. Следующий пример включает столбец DeleteId в таблице захоронения.

ALTER TABLE SyncSamplesDb.Sales.Customer 
    ADD UpdateTimestamp timestamp
ALTER TABLE SyncSamplesDb.Sales.Customer 
    ADD InsertTimestamp binary(8) DEFAULT @@DBTS + 1
ALTER TABLE SyncSamplesDb.Sales.Customer 
    ADD UpdateId uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
ALTER TABLE SyncSamplesDb.Sales.Customer 
    ADD InsertId uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'

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

CREATE NONCLUSTERED INDEX IX_Customer_UpdateTimestamp
ON Sales.Customer(UpdateTimestamp)

CREATE NONCLUSTERED INDEX IX_Customer_InsertTimestamp
ON Sales.Customer(InsertTimestamp)

CREATE NONCLUSTERED INDEX IX_Customer_UpdateId
ON Sales.Customer(UpdateId)

CREATE NONCLUSTERED INDEX IX_Customer_InsertId
ON Sales.Customer(InsertId)

Добавление таблицы захоронения к операции отслеживания операций удаления

В следующем примере кода создается таблица захоронения с кластеризованным индексом и триггер для ее заполнения. Когда в таблице Sales.Customer выполняется операция удаления, триггер вставляет строку в таблицу Sales.Customer_Tombstone. Перед вставкой триггер проверяет, содержит ли таблица Sales.Customer_Tombstone строку, имеющую первичный ключ удаленной строки. Это происходит в том случае, если строка была удалена из таблицы Sales.Customer, снова вставлена и опять удалена. Если такая строка найдена в таблице Sales.Customer_Tombstone, триггер удаляет ее и снова вставляет. Можно также обновить столбец DeleteTimestamp в таблице Sales.Customer_Tombstone.

CREATE TABLE SyncSamplesDb.Sales.Customer_Tombstone(
    CustomerId uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED, 
    CustomerName nvarchar(100) NOT NULL,
    SalesPerson nvarchar(100) NOT NULL,
    CustomerType nvarchar(100) NOT NULL,
    DeleteId uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
    DeleteTimestamp timestamp)

CREATE TRIGGER Customer_DeleteTrigger 
ON SyncSamplesDb.Sales.Customer FOR DELETE 
AS 
BEGIN 
    SET NOCOUNT ON
    DELETE FROM SyncSamplesDb.Sales.Customer_Tombstone 
        WHERE CustomerId IN (SELECT CustomerId FROM deleted)
    INSERT INTO SyncSamplesDb.Sales.Customer_Tombstone (CustomerId, CustomerName, SalesPerson, CustomerType) 
    SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM deleted
    SET NOCOUNT OFF
END

CREATE CLUSTERED INDEX IX_Customer_Tombstone_DeleteTimestamp
ON Sales.Customer_Tombstone(DeleteTimestamp)

CREATE NONCLUSTERED INDEX IX_Customer_Tombstone_DeleteId
ON Sales.Customer_Tombstone(DeleteId)

См. также

Основные положения

Отслеживание изменений в базе данных сервера

Поделиться через