Sincronizando dois bancos de dados no SQL Server

Bem, eu posso não entender, mas eu tento responder.

Você disse que precisa de uma solução de alto desempenho que seja executada com frequência (mínimo de 2 minutos) e precisa de uma boa abordagem que seja rápida sem travar. Mas você não quer um sistema de caixa preta.

Em vez de um sistema blackbox, que é usado em milhões de instalações com bons resultados, você tenta inventar a roda novamente e construir sua própria solução? Hm, soa um pouco estranho.

Na verdade, essas são minhas sugestões.

1. Replicação mesmo se você disser que não vai usá-la. É a solução mais fácil e melhor que você pode usar para isso. A replicação é fácil de configurar, replicar rápido e você não precisa inventar a roda novamente. Se você apenas estranho sobre o bloqueio, você pode tentar definir o ISOLATION LEVELpara READ_COMMITTED_SNAPSHOT. Você pode ler mais sobre isso aqui . Isso usará uma parte do seu tempdb, mas sua tabela sempre pode ser lida e gravada e a replicação pode funcionar em segundo plano.

Veja o exemplo abaixo:

ALTER DATABASE yourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE yourDatabase SET READ_COMMITTED_SNAPSHOT ON

2. O CDC (Change Data Capture) também pode ser uma solução. Mas desta forma você precisa construir quase tudo por conta própria. E eu fiz a experiência que CDCpode ser uma coisa frágil em algumas circunstâncias. CDCirá capturar todos os dados em uma tabela monitorada (você precisa especificar cada tabela monitorada manualmente). Depois, você obterá o valor antes e o valor depois de um INSERT, UPDATEou DELETE. CDCreterá essas informações por um período de tempo (você pode especificá-las por conta própria). A abordagem pode ser usar CDCem determinadas tabelas que você precisa observar e replicar manualmente essas alterações para o outro banco de dados. By the way, CDCusa o SQL Server Replication sob o capô também. ;-) Você pode ler mais sobre isso aqui .

Aviso: CDCnão estará ciente de DDL-alterações. Isso significa que, se você alterar uma tabela e adicionar uma nova coluna, CDCobservará a tabela, mas ignorará todas as alterações na nova coluna. Na verdade ele só registra NULLcomo valor antes e valor depois. Você precisa reinicializá-lo após DDL-Alterar para uma tabela monitorada.

3. A maneira que você descreveu acima é algo como capturar uma carga de trabalho usando o SQL Server Profiler e executá-la novamente em outro banco de dados para alguns benchmarks. Bem, poderia funcionar. Mas o fato de haver muitos efeitos colaterais é um pouco pesado demais para mim. O que você faz se capturar uma chamada de procedimento em seu cliente. Depois de executar o mesmo comando em seu banco de dados principal, pois está fora de sincronia? O procedimento pode ser executado, mas pode excluir/atualizar/inserir linhas que não estavam presentes em seu cliente. Ou como você lida com vários clientes com um princípio. Eu acho que isso é muito complicado. Na pior das hipóteses, você provavelmente destrói sua integridade.
4. Outra ideia pode ser baseada em aplicativos ou usando um gatilho. Dependendo de quantas tabelas você deseja sincronizar. Você pode gravar todas as alterações em uma tabela de preparo separada e executar um trabalho do SQL Server Agent todos os x minutos para sincronizar essas linhas na tabela de preparo com seu mestre. Mas isso pode ser um pouco pesado se você tentar sincronizar (por exemplo) 150 tabelas. Você teria uma grande sobrecarga.

Bem, esses são meus 2 centavos. Espero que você tenha uma boa visão geral e talvez tenha encontrado uma solução que funcione para você.

Vou tentar enumerar aqui algumas opções com vantagens e desvantagens à medida que as percebo:

1. Replicação do SQL Server - esta é a melhor e mais otimizada ferramenta nativa do SQL Server para esta tarefa. Mas há vários problemas: a. para todos os seus clientes, independentemente de serem bancos de dados SQL Express ou não, você precisará da licença SQL Server CAL. Isso pode ser evitado usando o licenciamento por processador. b. Você não pode sincronizar o cliente SQL CE conforme aqui . c. SQL Express ou LocalDB não podem atuar como um editor ou distribuidor , então você tem menos controle sobre o cliente sobre o processo de replicação.

2. Microsoft Sync Framework - me parece mais adequado para bancos de dados menores de aplicativos móveis. Ele adiciona muitas tabelas ao seu banco de dados e não é tão eficiente quanto a replicação. Como ele é implementado fora do SQL Server como um componente, será mais difícil de configurar. Não tenho experiência com isso, apenas tentei e decidi não usá-lo.

3. Acompanhamento de alterações do banco de dados . É uma função interna do SQL Server que faz o controle de alterações, incluindo inserções, atualizações e exclusões. Todo o resto, como enviar e aplicar alterações, resolver conflitos, etc., você terá que codificar.

4. Colunas de versão de linha (timestamp) Se você não permitir todas as exclusões (sem sincronização de registros excluídos) - você pode implementar sua própria solução com base apenas nas informações de versão de linha. As colunas Rowversion também são usadas pela Replicação do SQL Server, portanto, você precisará adicioná-las de qualquer maneira.

5. CDC conforme mencionado na resposta do Ionic - não tenho experiência com ele, pois está disponível apenas nas edições Enterprise ou Developer.

6. Usar seu próprio truque com o registro de procedimentos armazenados executados - depende muito da natureza do seu aplicativo de banco de dados. Mas quando os procedimentos ficam um pouco diferentes, aí você pode ter uma grande confusão nos dados. E como você lidaria com os conflitos?

Pela sua pergunta, parece que você precisa sincronizar apenas algumas tabelas e não todos os grandes bancos de dados. Para isso, você deve analisar suas necessidades com mais detalhes do que você especificou na pergunta, como:

• As exclusões podem acontecer e o que acontece então?
• Podem ocorrer conflitos, como preveni-los e como resolvê-los?
• Como lidarei com as mudanças na estrutura da tabela?
• ...

Se você eventualmente descobrir que exclusões e conflitos não são seu problema e que sua estrutura não mudará muito, você pode considerar escrever sua própria lógica, mas ela pode facilmente crescer para 1000 linhas de código.

Obrigado a todos por seus comentários.

Resolvi com sucesso o processo de sincronização capturando os procedimentos armazenados executados não como um monte, mas um por um, o que funcionou muito bem no meu caso. Como a integridade e tudo são cuidadosamente considerados, o sistema tem funcionado em tempo real até agora.

Resposta atrasada, mas pode ser útil para os visitantes do tópico

Eu tive um desafio semelhante ao tentar distribuir dados entre diferentes servidores e o resolvi usando ferramentas de terceiros ( Diff para alterações de esquema e DataDiff para sincronização de alterações de dados) e seguindo o script do PowerShell necessário para automatizar o processo:

#check for the existence of the Outputs folder
function CheckAndCreateFolder($rootFolder, [switch]$Outputs)
{
$location = $rootFolder

#setting up location 
if($Outputs -eq $true)
{
    $location += "\Outputs"
}

#if the folder doesn't exist it will be created
if(-not (Test-Path $location))
{ mkdir $location -Force:$true -Confirm:$false | Out-Null }

return $location
}

#root folder for the schema sync process
$rootFolder = "SchemaSync"

#schema output summaries location 
$outsLoc = CheckAndCreateFolder $rootFolder -Outputs

#ApexSQL Diff location, date stamp variable is defined, along with tool’s parameters 
$diffLoc   = "ApexSQLDiff"
$stamp = (Get-Date -Format "MMddyyyy_HHMMss") 
$Params = "/pr:""MyProject.axds""    /out:""$outsLoc\SchemaOutput_$stamp.txt"" /sync /v /f" 
$returnCode = $LASTEXITCODE

#initiate the schema comparison and synchronization process
(Invoke-Expression ("& `"" + $diffLoc +"`" " +$Params))

#write output to file
"$outsLoc\SchemaOutput_$dateStamp.txt"

#schema changes are detected
if($returnCode -eq 0)
{
"`r`n $returnCode - Schema changes were successfully synchronized" >> 

}
else
{
#there are no schema changes
if($returnCode -eq 102)
{
"`r`n $returnCode - There are no schema changes. Job aborted" >> 
}
#an error is encountered
else
{
"`r`n $returnCode - An error is encountered" >> 

#output file is opened when an error is encountered
Invoke-Item "$outsLoc\SchemaOutput_$stamp.txt"
}

}

Este método agenda a comparação entre dois bancos de dados e sincroniza as alterações encontradas em tempo real. Aqui estão alguns artigos que oferecem instruções passo a passo:

https://solutioncenter.apexsql.com/automatically-compare-and-synchronize-sql-server-data/ https://solutioncenter.apexsql.com/how-to-automatically-keep-two-sql-server-database- esquemas em sincronia/

Fiz essa pergunta há vários anos e resolvi finalmente e usei a solução em muitos projetos

1. Se suas tabelas forem exatamente iguais (sejam do mesmo servidor ou não), use a Replicação Transnacional. É muito fácil de configurar e não levará mais de uma hora para a configuração inicial. No entanto, pode precisar de algumas dicas para melhor depurar e entender o mecanismo que você pode descobrir em muitos sites.

2. Se suas tabelas de destino tiverem colunas extras do que suas tabelas de origem, eu definitivamente recomendo Triggers, a menos que você tenha tantas transações em um segundo ou seu servidor seja fraco.

Eu usei o método de trigger em vários projetos de sincronização e funciona muito bem.

CREATE TRIGGER [dbo].[Delete_UNIT] ON [DB2].[dbo].[UNIT]
AFTER DELETE
AS
BEGIN
DELETE S
FROM CRM.dbo.UNIT S
INNER JOIN Deleted D ON S.Unit_Code = D.Unit_Code 
END



CREATE TRIGGER [dbo].[Insert_UNIT] ON [DB2].[dbo].[UNIT]
AFTER INSERT
AS
BEGIN
INSERT INTO CRM.dbo.UNIT(Unit_Code, Unit_Name)
SELECT Unit_Code, Unit_Name
FROM inserted
END


CREATE TRIGGER [dbo].[Update_UNIT] ON [DB2].[dbo].[UNIT]
AFTER UPDATE
AS
BEGIN
UPDATE CRM.dbo.[UNIT] SET
      CRM.dbo.[UNIT].Unit_Code = S.Unit_Code
     ,CRM.dbo.[UNIT].Unit_Name = S.Unit_Name
FROM DB2.dbo.[UNIT] S
INNER JOIN Inserted I ON S.Unit_Code = I.Unit_Code
WHERE CRM.dbo.[UNIT].Unit_Code = S.Unit_Code 
END