Guia para sincronização de dados no Microsoft SQL Server

O compartilhamento de informações relacionadas entre sistemas isolados tem se tornado cada vez mais importante para as organizações, pois permite melhorar a qualidade e a disponibilidade dos dados. Há muitas situações em que é útil ter um conjunto de dados disponível e consistente em mais de um servidor de diretório. É por isso que é importante conhecer os métodos comuns para realizar a sincronização de dados do SQL Server.

A disponibilidade e a consistência dos dados podem ser alcançadas por processos de replicação e sincronização de dados. A replicação de dados é o processo de criação de uma ou mais cópias redundantes de um banco de dados para fins de tolerância a falhas ou melhoria de acessibilidade. A sincronização de dados é o processo de estabelecer consistência de dados entre dois ou mais bancos de dados e as atualizações contínuas subsequentes para manter essa consistência.

Em muitas organizações, realizar a sincronização de dados em diversos sistemas é desejável e desafiador. Podemos encontrar muitos casos de uso em que precisamos realizar a sincronização de dados:

• Migração de banco de dados
• Sincronização regular entre sistemas de informação
• Importação de dados de um sistema de informação para outro
• Movendo conjuntos de dados entre diferentes estágios ou ambientes
• Importando dados de uma fonte que não é de banco de dados

Não existe uma forma única ou um método unanimemente acordado para sincronização de dados. Essa tarefa difere de caso para caso, e mesmo sincronizações de dados que deveriam ser simples à primeira vista podem ser complicadas, devido à complexidade das estruturas de dados. Em cenários reais, a sincronização de dados consiste em muitas tarefas complexas, que podem levar muito tempo para serem executadas. Quando surge um novo requisito, os especialistas em banco de dados geralmente precisam reimplementar todo o processo de sincronização. Como não há maneiras padrão de fazer isso, além da replicação, as implementações de sincronização de dados raramente são ótimas. Isso resulta em manutenção difícil e despesas mais altas. A implementação e manutenção da sincronização de dados é um processo tão demorado que pode ser um trabalho de tempo integral por si só.

Podemos implementar a arquitetura para tarefas de sincronização de dados manualmente, possivelmente usando o Microsoft Sync Framework, ou podemos nos beneficiar de soluções já criadas dentro de ferramentas de gerenciamento do Microsoft SQL Server. Tentaremos descrever os métodos e ferramentas mais comuns que podem ser usados ​​para resolver a sincronização de dados em bancos de dados Microsoft SQL Server e tentaremos dar algumas recomendações.

Com base na estrutura da origem e destino (por exemplo, bancos de dados, tabelas), podemos diferenciar os casos de uso quando as estruturas são semelhantes ou diferentes.

Origem e Destino Têm Estruturas Muito Semelhantes

Este é frequentemente o caso quando usamos dados em vários estágios do ciclo de vida de desenvolvimento de software. Por exemplo, a estrutura de dados nos ambientes de teste e produção é muito semelhante. O requisito comum é comparar dados entre o banco de dados de teste e de produção e importar dados da produção para o banco de dados de teste.

Origem e Destino Têm Estruturas Diferentes

Se as estruturas forem diferentes, a sincronização é mais complicada. Essa também é uma tarefa recorrente com mais frequência. Um caso comum é importar de um banco de dados para outro. O caso mais comum é quando um software precisa importar dados de outro software que é mantido por outra empresa. Normalmente, as importações precisam ser executadas automaticamente de forma programada.

O método usado depende das preferências pessoais e da complexidade do problema que você precisa resolver.

Independentemente de quão semelhantes sejam as estruturas, podemos escolher quatro maneiras diferentes de resolver a sincronização de dados:

• Sincronização usando scripts SQL criados manualmente
• Sincronização usando o método de comparação de dados (pode ser usado apenas quando a origem e o destino têm estrutura semelhante)
• Sincronização usando scripts SQL gerados automaticamente - precisa de produto comercial

Origem e destino têm estruturas iguais ou muito semelhantes

Usando scripts SQL criados manualmente

A solução mais simples e tediosa é escrever manualmente scripts SQL para sincronização.

Vantagens

• Pode ser executado por ferramentas gratuitas e de código aberto (FOSS).
• Se a tabela tiver índices, é muito rápido.
• O script SQL pode ser salvo em um procedimento armazenado ou executado periodicamente como um trabalho para o SQL Server.
• Pode ser usado como uma importação automática, mesmo em dados alterados continuamente.

Desvantagens

• Criar um script SQL desse tipo é bastante tedioso, porque geralmente são necessários três scripts para cada tabela: INSERT , UPDATE e DELETE .
• Você só pode sincronizar dados disponíveis por meio de consultas SQL, portanto, não pode importar de fontes como arquivos CSV e XML.
• É difícil de manter - quando a estrutura do banco de dados é alterada, é necessário modificar dois ou três scripts ( INSERT , UPDATE e às vezes também DELETE ).

Exemplo

Faremos a sincronização entre a tabela Source , com as colunas ID e Value , e a tabela Target , com as mesmas colunas.

Se as tabelas tiverem a mesma chave primária e a tabela de destino não tiver uma chave primária de incremento automático (identidade), você poderá executar o seguinte script de sincronização.

 -- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

Usando o método de comparação de dados

Nesse método, podemos usar uma ferramenta para comparar entre os dados de origem e de destino. O processo de comparação gera scripts SQL que aplicam as diferenças do banco de dados de origem ao banco de dados de destino.

Existem vários programas para comparação e sincronização de dados. Esses programas geralmente usam a mesma abordagem. O usuário seleciona o banco de dados de origem e destino, mas outras alternativas podem ser um backup de banco de dados, uma pasta com scripts SQL ou até mesmo uma conexão com um sistema de controle de origem.

Abaixo estão as ferramentas mais populares que usam a abordagem de comparação de dados:

• Comparação de dados dbForge para SQL Server
• Comparação de dados SQL RedGate
• Diferença de dados SQL do Apex

Na primeira etapa, os dados são lidos ou apenas as somas de verificação de dados maiores da origem e do destino são lidas. Em seguida, o processo de comparação é executado.

Essas ferramentas também oferecem configurações adicionais para sincronização.

Precisamos configurar as seguintes opções de configuração necessárias para sincronização de dados:

Chave de sincronização

Por padrão, a chave primária ou uma restrição UNIQUE é usada. Se não houver chave primária, você poderá escolher uma combinação de colunas. A chave Sync é usada para emparelhar as linhas da origem com as linhas do destino.

Emparelhamento de Mesa

Por padrão, as tabelas são emparelhadas por nome. Você pode alterar isso e emparelhá-los de acordo com suas próprias necessidades. No software dbForge Data Compare, você pode escolher a consulta SQL como origem ou destino.

Processo de Sincronização

Após a confirmação, a ferramenta compara os dados de origem e de destino. Todo o processo consiste em baixar todos os dados de origem e destino e compará-los com base em critérios especificados. Por padrão, os valores de tabelas e colunas com nomes iguais são comparados. Todas as ferramentas suportam o mapeamento de nomes de colunas e tabelas. Além disso, existe a possibilidade de excluir colunas IDENTITY (autoincremento) ou fazer algumas transformações antes de comparar valores (arredondar tipos float, ignorar maiúsculas e minúsculas, tratar NULL como uma string vazia, etc.) O download de dados é otimizado. Se o volume de dados for grande, apenas as somas de verificação serão baixadas. Essa otimização é útil na maioria dos casos, mas os requisitos de tempo para realizar as operações aumentam com o volume de dados.

Na próxima etapa, há um script SQL com as migrações geradas. Este script pode ser salvo ou executado diretamente. Por segurança, podemos até fazer um backup do banco de dados antes de executar este script. A ferramenta ApexSQL Data Diff pode criar um programa executável que executa o script em um banco de dados selecionado. Este script contém dados que precisam ser alterados, não a lógica de como alterá-los. Isso significa que o script não pode ser executado automaticamente para fornecer uma importação recorrente. Essa é a maior desvantagem dessa abordagem.

Vantagens

• Conhecimento avançado de SQL não é necessário e pode ser feito via GUI.
• Você tem a capacidade de verificar visualmente as diferenças entre os bancos de dados antes da sincronização.

Desvantagens

• É um recurso avançado de produtos comerciais.
• O desempenho diminui ao transferir enormes volumes de dados.
• O script SQL gerado contém apenas diferenças e, portanto, não pode ser reutilizado para sincronizar automaticamente dados futuros.

Abaixo você pode ver a UI típica dessas ferramentas.

Sincronize com SQL gerado automaticamente

Este método é muito semelhante ao método de comparação de dados. A única diferença em relação ao método anterior é que não há comparação de dados e o script SQL gerado não contém diferenças de dados, mas lógica de sincronização. O script gerado pode ser facilmente salvo em um procedimento armazenado e pode ser executado periodicamente (por exemplo, todas as noites). Este método é útil para importações automáticas entre bancos de dados. O desempenho deste método é muito melhor do que o método de comparação de dados.

A sincronização por SQL gerado automaticamente é fornecida apenas pelo SQL Database Studio.

O SQL Database Studio fornece uma interface semelhante ao método de comparação de dados. Precisamos selecionar a origem e o destino (bancos de dados ou tabelas). Em seguida, precisamos configurar as opções (chaves de sincronização, emparelhamento e mapeamento). Há um recurso de construtor de consultas gráficas para configurar todos os parâmetros.

Vantagens

• Não é necessário conhecimento avançado de SQL.
• Você pode configurar tudo em uma GUI muito rapidamente.
• O script SQL resultante pode ser salvo em um procedimento armazenado.
• Pode ser usado como importação automática - como um trabalho para SQL Server.

Desvantagens

• É um recurso avançado de produtos comerciais.
• As diferenças não podem ser verificadas manualmente antes da sincronização, pois todo o processo é executado em uma única etapa.

Referências de desempenho

Caso de teste

Dois bancos de dados (A e B), cada um contendo uma tabela com 2.000.000 de linhas. As tabelas estão em dois bancos de dados diferentes no mesmo SQL Server. Este teste abrange dois casos extremos: 1) A tabela de origem contém todas as 2.000.000 linhas e a tabela de destino está vazia. A sincronização precisa fornecer muitos INSERTS . 2) As tabelas de origem e destino contêm 2.000.000 de linhas. A diferença é apenas em uma linha. A sincronização precisa fornecer apenas um UPDATE .

O RedGate Data Compare precisa de 3 etapas:

• Comparar
• Gerar script
• Executar script no banco de dados de destino

ApexSQL Data Diff precisa de 2 etapas:

• Comparar
• Gere script e execute script em uma etapa

O SQL Database Studio executa toda a sincronização em uma única etapa. Abaixo estão os tempos de sincronização, em segundos. Na coluna denominada “etapas individuais” estão as durações das etapas de sincronização listadas acima.

Menor é melhor.

O mesmo teste, mas os bancos de dados estão em servidores SQL diferentes, que não estão conectados em um servidor vinculado.

Menor é melhor.

Resumo

A partir dos resultados, é óbvio que RedGate e Apex não se importam se os bancos de dados estão no mesmo SQL Server, porque o algoritmo de sincronização não depende do SQL Server. SQL Database Studio usa funções nativas do SQL Server; portanto, o resultado é melhor quando os bancos de dados estão no mesmo servidor.

Origem e destino têm uma estrutura diferente

Há também situações em que uma tabela ampla precisa ser sincronizada em muitas tabelas pequenas relacionadas.

Este exemplo consiste em uma ampla tabela SourceData que precisa ser sincronizada em pequenas tabelas Continent , Country e City . O esquema é dado abaixo.

Os dados em SourceData podem ser como os da imagem abaixo.

Usando scripts SQL criados manualmente

Tabela de continente de sincronização de script

 INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

Tabela de cidades de sincronização de script

 INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

Este script é mais complicado. É porque os registros nas tabelas Country e Continent precisam ser encontrados. Este script insere registros ausentes em City e preenche ContryId corretamente.

Os scripts UPDATE e DELETE também podem ser escritos da mesma maneira, se necessário.

Vantagens

• Você não precisa de nenhum produto comercial.
• O script SQL pode ser salvo no procedimento armazenado ou executado periodicamente como um trabalho para o SQL Server.

Desvantagens

• Criar um script SQL desse tipo é difícil e complicado (para cada tabela, três scripts — INSERT , UPDATE e DELETE — geralmente são necessários).
• É muito difícil de manter.

Usando ferramentas externas

Esse tipo de sincronização (tabela ampla em muitas tabelas relacionadas) não pode ser feito com o método de comparação de dados, pois é focado em diferentes casos de uso. Como o método de comparação de dados produz um script SQL com os dados a serem inseridos, ele não tem a capacidade direta de pesquisar referências em tabelas relacionadas. Por esse motivo, os aplicativos que usam esse método não podem ser usados ​​(dbForge Data Compare for SQL Server, RedGate SQL Data Compare, Apex SQL Data Diff).

No entanto, o SQL Database Studio pode ajudá-lo a criar scripts de sincronização automaticamente. Na figura abaixo, há um elemento chamado Editor para Sincronização de Dados no SQL Database Studio.

O Editor se parece com o conhecido construtor de consultas e funciona de maneira muito semelhante. Cada tabela precisa ter uma chave de sincronização definida, mas também existem relações definidas entre as tabelas. Na imagem acima há mapeamento para sincronização também. Na lista de colunas (parte inferior da imagem) estão as colunas da tabela City (para outras tabelas é semelhante).

Colunas

• Id — Esta coluna não é mapeada porque é a chave primária (gerada automaticamente).
• CountryId — Esta coluna é definida como uma referência para a tabela.
• Nome — Esta coluna é preenchida a partir da coluna Cidade na tabela de origem (tabela ampla).

As colunas CountryId e Name são escolhidas como chaves de sincronização. A chave de sincronização é um conjunto de colunas que identifica exclusivamente uma linha na tabela de origem e destino. Você não pode usar a Id da chave primária como uma chave de sincronização porque ela não está na tabela de origem.

Após a sincronização, é assim que as tabelas se parecem:

No exemplo acima, havia uma tabela ampla como fonte. Há também um cenário comum quando os dados de origem são armazenados em várias tabelas relacionadas. As relações no SQL Database Studio não são definidas usando chaves estrangeiras, mas nomes de coluna. Desta forma, também é possível importar de arquivos CSV ou Excel (o arquivo é carregado em uma tabela temporária e a sincronização é executada a partir dessa tabela). É uma boa prática ter nomes de coluna exclusivos. Se isso não for possível, você pode definir aliases para essas colunas.

Vantagens

• Fácil e rápido de criar
• De fácil manutenção
• Pode ser salvo em um procedimento armazenado (o procedimento armazenado é salvo com os dados necessários para abrir a sincronização em um editor posteriormente)

Desvantagens

• Solução comercial

Comparando as soluções

A sincronização de dados consiste em uma sequência de comandos INSERT , UPDATE ou DELETE . Existem várias maneiras de criar sequências desses comandos. Neste artigo, examinamos três opções para criar scripts SQL de sincronização. A primeira opção é criar tudo manualmente. É viável (mas leva muito tempo), requer uma compreensão complexa do SQL e é difícil de criar e manter. A segunda opção é usar ferramentas comerciais. Vimos as seguintes ferramentas:

• Comparação de dados dbForge para SQL Server
• Comparação de dados SQL RedGate
• Diferença de dados SQL do Apex
• Estúdio de Banco de Dados SQL

As três primeiras ferramentas funcionam de forma muito semelhante. Eles comparam dados, permitem que o usuário analise diferenças e podem sincronizar diferenças selecionadas (mesmo automaticamente ou a partir da linha de comando). Eles são benéficos para estes cenários de uso:

• Os bancos de dados estão fora de sincronia devido a vários erros.
• Você precisa evitar a replicação ao transferir dados entre ambientes.
• Relatórios de comparação de dados em Excel ou HTML são necessários.

Cada ferramenta é amada por um motivo ou outro: o dbForge tem uma ótima interface do usuário e muitas opções, o ApexSQL tem um desempenho melhor que o resto e o RedGate é o mais popular.

A quarta ferramenta, SQL Database Studio, funciona de forma um pouco diferente. Ele gera scripts SQL que contêm lógica de sincronização, não alterações. O desempenho também é ótimo, pois todo o trabalho é feito diretamente no servidor de banco de dados, portanto, nenhuma transferência de dados entre o servidor de banco de dados e a ferramenta de sincronização é necessária. Esta ferramenta é útil para os seguintes casos de uso:

• Migrações automáticas de banco de dados onde os bancos de dados têm estrutura diferente
• Importar para várias tabelas relacionadas
• Importar de fontes externas XML, CSV, MS Excel