Eu gerencio algumas tabelas com mais de 1 bilhão de registros. Diariamente, seus ETLs são executados, lendo dados de arquivos simples e inserindo alguns milhares de registros em cada um deles.
Agora preciso manter essas mesmas tabelas também em outro BD, e claro que precisam ter os mesmos dados. Os dados serão transferidos usando Linked Server.
A maneira mais fácil seria truncar as tabelas de destino, selecionar na fonte inserindo no destino e reconstruir seu índice. Mas não gosto da ideia de copiar os mesmos dados todos os dias.
Outra solução que vejo é consultar as tabelas de origem e destino, distinguindo por dimensão de data e copiando apenas os registros das datas que estão faltando. Eu então criaria um monitor para ver se a quantidade de registros por dia são iguais e destacaria os dias não coincidentes.
Essa segunda solução é boa o suficiente ou existe uma prática recomendada para essa situação?
Se eu tivesse que gerenciar isso, examinaria o que, se houver, modifica os dados nas tabelas fora do processo ETL.
Se nada mais além do seu ETL modificar os dados, eu simplesmente atualizaria o processo ETL para inserir os dados concluídos em ambos os locais (e também executar qualquer manutenção de índice que você esteja fazendo em ambos os locais).
Se algo mais atualizar esses dados, mas apenas um servidor, a replicação transacional é provavelmente a maneira mais leve de obter os dados para o servidor secundário. Mesmo que os dados não estejam sendo modificados fora do ETL, essa não seria uma alternativa terrível para modificar o processo ETL para atualizar dois destinos. Parece que uma porcentagem relativamente pequena de dados está sendo inserida diariamente.
Se os dados estiverem sendo modificados em ambos os servidores, você provavelmente desejará considerar a replicação de mesclagem. A simplicidade disso dependerá em grande parte se a tabela tiver colunas de identidade.