Автоматическая репликация изменений схемы при синхронизации данных SQL Azure

  • Статья

Область применения:База данных SQL Azure

Функция "Синхронизация данных SQL" предоставляет пользователям возможность синхронизировать данные между базами данных SQL Azure и экземплярами SQL Server в любом направлении. Одно из текущих ограничений синхронизации данных SQL — отсутствие поддержки репликации изменений схемы. Каждый раз при изменении схемы таблицы изменения необходимо применять вручную для всех конечных точек, включая концентратор и все элементы, а затем обновлять схему синхронизации.

В этой статье описывается решение для автоматической репликации изменений схемы во все конечные точки синхронизации данных SQL.

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

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

Важно!

Рекомендуем внимательно прочесть эту статью, особенно разделы о разрешении неполадок и дополнительных рекомендациях, прежде чем начать реализовывать репликацию изменений схемы в среде синхронизации. Также рекомендуется считывать данные синхронизации между несколькими облачными и локальными базами данных с Синхронизация данных SQL. Некоторые операции с базой данных могут привести к нарушению работы решения, описанного в этой статье. Для устранения неполадок Transact-SQL и SQL Server могут потребоваться дополнительные знания о домене.

Automating the replication of schema changes

Настройка автоматической репликации изменений схемы

Создание таблицы для отслеживания изменений схемы

Создайте таблицу для отслеживания изменений схемы во всех базах данных в группе синхронизации:

CREATE TABLE SchemaChanges (
ID bigint IDENTITY(1,1) PRIMARY KEY,
SqlStmt nvarchar(max),
[Description] nvarchar(max)
)

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

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

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

CREATE TABLE SchemaChangeHistory (
LastAppliedId bigint PRIMARY KEY
)
GO

INSERT INTO SchemaChangeHistory VALUES (0)

Создание триггера DDL ALTER TABLE в базе данных, где были сделаны изменения схемы

Создайте триггер DDL для операций ALTER TABLE. Необходимо только создать этот триггер в базе данных, где были сделаны изменения схемы. Чтобы избежать конфликтов, разрешите изменения схемы только в одной базе данных в группе синхронизации.

CREATE TRIGGER AlterTableDDLTrigger
ON DATABASE
FOR ALTER_TABLE
AS

-- You can add your own logic to filter ALTER TABLE commands instead of replicating all of them.

IF NOT (EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(512)') like 'DataSync')

INSERT INTO SchemaChanges (SqlStmt, Description)
    VALUES (EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'), 'From DDL trigger')

Триггер производит вставку записи в таблицу отслеживания изменений схемы для каждой команды ALTER TABLE. В этом примере добавляется фильтр, чтобы избежать репликации изменений схемы, сделанных в схеме DataSync, так как они, скорее всего, внесены службой синхронизации данных. Добавьте дополнительные фильтры, если требуется реплицировать определенные типы изменений схемы.

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

Создание триггера на других конечных точках для применения изменений схемы во время вставки

Этот триггер выполняет команду изменения схемы, если он синхронизирован с другими конечными точками. Необходимо создать этот триггер для всех конечных точек, за исключением тех, где были сделаны изменения схемы (такие как база данных, в которой на предыдущем шаге был создан триггер DDL AlterTableDDLTrigger).

CREATE TRIGGER SchemaChangesTrigger
ON SchemaChanges
AFTER INSERT
AS
DECLARE @lastAppliedId bigint
DECLARE @id bigint
DECLARE @sqlStmt nvarchar(max)
SELECT TOP 1 @lastAppliedId=LastAppliedId FROM SchemaChangeHistory
SELECT TOP 1 @id = id, @SqlStmt = SqlStmt FROM SchemaChanges WHERE id > @lastAppliedId ORDER BY id
IF (@id = @lastAppliedId + 1)
BEGIN
    EXEC sp_executesql @SqlStmt
        UPDATE SchemaChangeHistory SET LastAppliedId = @id
    WHILE (1 = 1)
    BEGIN
        SET @id = @id + 1
        IF exists (SELECT id FROM SchemaChanges WHERE ID = @id)
            BEGIN
                SELECT @sqlStmt = SqlStmt FROM SchemaChanges WHERE ID = @id
                EXEC sp_executesql @SqlStmt
                UPDATE SchemaChangeHistory SET LastAppliedId = @id
            END
        ELSE
            BREAK;
    END
END

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

Синхронизация таблицы отслеживания изменений схемы со всеми конечными точками

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

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

Применение изменений схемы в группе синхронизации

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

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

Добавление новых столбцов

  1. Внесите изменения в схему.

  2. Избегайте любого изменения данных, в котором участвуют новые столбцы, пока не выполнены действия по созданию триггера.

  3. Подождите, пока изменения схемы не применятся ко всем конечным точкам.

  4. Обновите схему базы данных и добавьте новый столбец в схему синхронизации.

  5. Данные в новом столбце синхронизируются по время следующей операции синхронизации.

Удаление столбцов

  1. Удалите столбцы из схемы синхронизации. Синхронизация данных останавливает синхронизацию данных в этих столбцах.

  2. Внесите изменения в схему.

  3. Обновите схему базы данных.

Обновление типов данных

  1. Внесите изменения в схему.

  2. Подождите, пока изменения схемы не применятся ко всем конечным точкам.

  3. Обновите схему базы данных.

  4. Если новые и старые типы данных не полностью совместимы, например при изменении int на bigint, синхронизация может завершиться, прежде чем будет завершен шаг создания триггера. Синхронизация выполнится успешно после повторной попытки.

Переименование столбцов или таблиц

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

Другие типы изменений схемы

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

Устранение неполадок автоматической репликации изменений схемы

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

  1. Отключите триггер DDL и избегайте дальнейших изменений схемы, пока проблема не будет устранена.

  2. В базе данных конечной точки, в которой возникает проблема, отключите триггер AFTER INSERT на конечной точке, где невозможно изменить схему. Это действие позволяет синхронизировать команду изменения схемы.

  3. Синхронизируйте триггер, чтобы синхронизировать таблицу отслеживания изменений схемы.

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

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

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

    b. Примените вручную те команды, которые должны быть применены.

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

  7. Дважды проверьте, актуальна ли схема.

  8. Повторно включите триггер AFTER INSERT, отключенный на втором шаге.

  9. Повторно включите триггер DDL, отключенный на первом шаге.

Если вы хотите очистить записи в таблице отслеживания изменений схемы, используйте команду DELETE вместо TRUNCATE. Никогда не обновляйте столбец идентификатора в таблице отслеживания изменений схемы с помощью DBCC CHECKIDENT. Вы можете создавать таблицы отслеживания изменений схемы и обновить имя таблицы в триггере DDL, если требуется повторное обновление.

Другие вопросы

  • Пользователи базы данных, которые настраивают концентратор и рядовые базы данных, должны иметь достаточные разрешения для выполнения команд изменения схемы.

  • Вы можете добавить дополнительные фильтры в триггер DDL для репликации изменений схемы только в выбранных таблицах или операциях.

  • Изменения схемы можно внести в базу данных только там, где создан триггер DDL.

  • При внесении изменений в базу данных SQL Server убедитесь, что изменение схемы поддерживается в Базе данных SQL Azure.

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

  • Если вам нужно изменить схему таблицы отслеживания изменений схемы, отключите триггер DDL перед внесением изменения, а затем вручную примените это изменение ко всем конечным точкам. Обновление схемы в триггере AFTER INSERT в одной и той же таблице не работает.

  • Не обновляйте столбец идентификаторов с помощью DBCC CHECKIDENT.

  • Не используйте TRUNCATE для очистки данных в таблице отслеживания изменений схемы.

Дальнейшие действия

Дополнительные сведения о синхронизации данных SQL: