Partilhar via

Automatizar a replicação de alterações de esquema no Azure SQL Data Sync

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure

A Sincronização de Dados SQL permite que os usuários sincronizem dados entre bancos de dados no Banco de Dados SQL do Azure e instâncias do SQL Server em uma direção ou em ambas as direções. Uma das limitações atuais do SQL Data Sync é a falta de suporte para a replicação de alterações de esquema. Sempre que você alterar o esquema da tabela, precisará aplicar as alterações manualmente em todos os pontos de extremidade, incluindo o hub e todos os membros, e atualizar o esquema de sincronização.

Este artigo apresenta uma solução para replicar automaticamente as alterações de esquema para todos os pontos de extremidade do SQL Data Sync.

  1. Esta solução usa um gatilho DDL para controlar alterações de esquema.
  2. O gatilho insere os comandos de alteração de esquema em uma tabela de controle.
  3. Essa tabela de controle é sincronizada com todos os pontos de extremidade usando o serviço de Sincronização de Dados.
  4. Os gatilhos DML após a inserção são usados para aplicar as alterações de esquema nos outros pontos de extremidade.

Este artigo usa ALTER TABLE como um exemplo de uma alteração de esquema, mas essa solução também funciona para outros tipos de alterações de esquema.

Importante

Recomendamos que você leia este artigo com atenção, especialmente as seções sobre Solução de problemas e Outras considerações, antes de começar a implementar a replicação automatizada de alteração de esquema em seu ambiente de sincronização. Também recomendamos que você leia Sincronizar dados em vários bancos de dados locais e na nuvem com o SQL Data Sync. Algumas operações de banco de dados podem quebrar a solução descrita neste artigo. Conhecimento de domínio adicional do SQL Server e Transact-SQL pode ser necessário para solucionar esses problemas.

Automating the replication of schema changes

Configurar a replicação automatizada de alteração de esquema

Criar uma tabela para controlar alterações de esquema

Crie uma tabela para controlar as alterações de esquema em todos os bancos de dados no grupo de sincronização:

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

Esta tabela tem uma coluna de identidade para controlar a ordem das alterações de esquema. Você pode adicionar mais campos para registrar mais informações, se necessário.

Criar uma tabela para controlar o histórico de alterações de esquema

Em todos os pontos de extremidade, crie uma tabela para controlar a ID do comando de alteração de esquema aplicado mais recentemente.

CREATE TABLE SchemaChangeHistory (
LastAppliedId bigint PRIMARY KEY
)
GO

INSERT INTO SchemaChangeHistory VALUES (0)

Criar um gatilho ALTER TABLE DDL no banco de dados onde as alterações de esquema são feitas

Crie um gatilho DDL para operações ALTER TABLE. Você só precisa criar esse gatilho no banco de dados onde as alterações de esquema são feitas. Para evitar conflitos, permita apenas alterações de esquema em um banco de dados em um grupo de sincronização.

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')

O gatilho insere um registro na tabela de controle de alterações de esquema para cada comando ALTER TABLE. Este exemplo adiciona um filtro para evitar replicar alterações de esquema feitas no esquema DataSync, porque elas provavelmente são feitas pelo serviço Data Sync. Adicione mais filtros se quiser replicar apenas determinados tipos de alterações de esquema.

Você também pode adicionar mais gatilhos para replicar outros tipos de alterações de esquema. Por exemplo, crie CREATE_PROCEDURE, ALTER_PROCEDURE e DROP_PROCEDURE gatilhos para replicar alterações em procedimentos armazenados.

Criar um gatilho em outros pontos de extremidade para aplicar alterações de esquema durante a inserção

Esse gatilho executa o comando de alteração de esquema quando ele é sincronizado com outros pontos de extremidade. Você precisa criar esse gatilho em todos os pontos de extremidade, exceto naquele em que as alterações de esquema são feitas (ou seja, no banco de dados onde o gatilho AlterTableDDLTrigger DDL é criado na etapa anterior).

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

Esse gatilho é executado após a inserção e verifica se o comando atual deve ser executado em seguida. A lógica de código garante que nenhuma instrução de alteração de esquema seja ignorada e todas as alterações sejam aplicadas mesmo se a inserção estiver fora de ordem.

Sincronizar a tabela de controle de alterações de esquema com todos os pontos de extremidade

Você pode sincronizar a tabela de controle de alterações de esquema com todos os pontos de extremidade usando o grupo de sincronização existente ou um novo grupo de sincronização. Certifique-se de que as alterações na tabela de acompanhamento podem ser sincronizadas com todos os pontos de extremidade, especialmente quando estiver a utilizar a sincronização unidirecional.

Não sincronize a tabela do histórico de alterações de esquema, pois essa tabela mantém um estado diferente em pontos de extremidade diferentes.

Aplicar as alterações de esquema em um grupo de sincronização

Somente as alterações de esquema feitas no banco de dados onde o gatilho DDL é criado são replicadas. As alterações de esquema feitas em outros bancos de dados não são replicadas.

Depois que as alterações de esquema forem replicadas para todos os pontos de extremidade, você também precisará executar etapas adicionais para atualizar o esquema de sincronização para iniciar ou parar a sincronização das novas colunas.

Adicionar novas colunas

  1. Faça a alteração do esquema.

  2. Evite qualquer alteração de dados em que as novas colunas estejam envolvidas até concluir a etapa que cria o gatilho.

  3. Aguarde até que as alterações de esquema sejam aplicadas a todos os pontos de extremidade.

  4. Atualize o esquema do banco de dados e adicione a nova coluna ao esquema de sincronização.

  5. Os dados na nova coluna são sincronizados durante a próxima operação de sincronização.

Remover colunas

  1. Remova as colunas do esquema de sincronização. A Sincronização de Dados interrompe a sincronização de dados nessas colunas.

  2. Faça a alteração do esquema.

  3. Atualize o esquema do banco de dados.

Atualizar tipos de dados

  1. Faça a alteração do esquema.

  2. Aguarde até que as alterações de esquema sejam aplicadas a todos os pontos de extremidade.

  3. Atualize o esquema do banco de dados.

  4. Se os tipos de dados novos e antigos não forem totalmente compatíveis - por exemplo, se você mudar de int para bigint - a sincronização poderá falhar antes que as etapas que criam os gatilhos sejam concluídas. A sincronização é bem-sucedida após uma nova tentativa.

Renomear colunas ou tabelas

Renomear colunas ou tabelas faz com que a Sincronização de Dados pare de funcionar. Crie uma nova tabela ou coluna, preencha os dados e exclua a tabela ou coluna antiga em vez de renomear.

Outros tipos de alterações de esquema

Para outros tipos de alterações de esquema - por exemplo, criar procedimentos armazenados ou descartar um índice - não é necessário atualizar o esquema de sincronização.

Solucionar problemas de replicação automatizada de alteração de esquema

A lógica de replicação descrita neste artigo para de funcionar em algumas situações, por exemplo, se você fez uma alteração de esquema em um banco de dados local que não tem suporte no Banco de Dados SQL do Azure. Nesse caso, a sincronização da tabela de controle de alterações de esquema falhará. Você precisa corrigir esse problema manualmente:

  1. Desative o gatilho DDL e evite mais alterações de esquema até que o problema seja corrigido.

  2. No banco de dados de ponto de extremidade onde o problema está acontecendo, desative o gatilho AFTER INSERT no ponto de extremidade onde a alteração de esquema não pode ser feita. Essa ação permite que o comando de alteração de esquema seja sincronizado.

  3. Acione a sincronização para sincronizar a tabela de controle de alterações de esquema.

  4. No banco de dados de ponto de extremidade onde o problema está acontecendo, consulte a tabela de histórico de alterações de esquema para obter a ID do último comando de alteração de esquema aplicado.

  5. Consulte a tabela de controle de alterações de esquema para listar todos os comandos com um ID maior do que o valor de ID recuperado na etapa anterior.

    a. Ignore os comandos que não podem ser executados no banco de dados do ponto de extremidade. Você precisa lidar com a inconsistência do esquema. Reverta as alterações do esquema original se a inconsistência afetar seu aplicativo.

    b. Aplique manualmente os comandos que devem ser aplicados.

  6. Atualize a tabela do histórico de alterações de esquema e defina a última ID aplicada para o valor correto.

  7. Verifique se o esquema está atualizado.

  8. Reative o gatilho AFTER INSERT desativado na segunda etapa.

  9. Reative o gatilho DDL desativado na primeira etapa.

Se você quiser limpar os registros na tabela de controle de alterações de esquema, use DELETE em vez de TRUNCATE. Nunca resenda a coluna de identidade na tabela de controle de alterações de esquema usando DBCC CHECKIDENT. Você pode criar novas tabelas de controle de alterações de esquema e atualizar o nome da tabela no gatilho DDL se a repropagação for necessária.

Outras considerações

  • Os usuários de banco de dados que configuram o hub e os bancos de dados membros precisam ter permissão suficiente para executar os comandos de alteração de esquema.

  • Você pode adicionar mais filtros no gatilho DDL para replicar apenas a alteração de esquema em tabelas ou operações selecionadas.

  • Você só pode fazer alterações de esquema no banco de dados onde o gatilho DDL é criado.

  • Se você estiver fazendo uma alteração em um banco de dados do SQL Server, verifique se a alteração de esquema tem suporte no Banco de Dados SQL do Azure.

  • Se forem feitas alterações de esquema em bancos de dados diferentes do banco de dados onde o gatilho DDL é criado, as alterações não serão replicadas. Para evitar esse problema, você pode criar gatilhos DDL para bloquear alterações em outros pontos de extremidade.

  • Se você precisar alterar o esquema da tabela de controle de alterações de esquema, desative o gatilho DDL antes de fazer a alteração e, em seguida, aplique manualmente a alteração a todos os pontos de extremidade. Atualizar o esquema em um gatilho AFTER INSERT na mesma tabela não funciona.

  • Não reseed a coluna de identidade usando DBCC CHECKIDENT.

  • Não use TRUNCATE para limpar dados na tabela de controle de alterações de esquema.

Próximos passos

Para obter mais informações sobre a Sincronização de Dados SQL, veja: