Qual é a melhor estratégia de extração para aproximadamente 200 bancos de dados de origem do SQL Server 2005 (mesmo esquema) para uma carga diária em uma área de preparação em preparação para limpeza, eliminação de duplicação e transformações do data warehouse?
Até agora, imaginei as seguintes possibilidades:
- Replicação transacional: crie 200 assinantes do SQL Server 2008 R2 extraindo dados de seus respectivos editores em 2005. Habilite o Change Data Capture nas tabelas necessárias no assinante e nas tabelas de sombra para realizar carregamentos incrementais em nosso banco de dados temporário.
- Rowversion: adicione uma coluna rowversion em cada tabela de origem necessária e use-a em conjunto com um processo SSIS para extrair os dados alterados diretamente para o banco de dados de preparação.
- Arquivos BCP: crie uma tarefa automatizada para fazer um despejo noturno de arquivos BCP de todas as tabelas de origem. Use o SSIS para carregar essas tabelas no banco de dados de preparo como parte de um carregamento completo (em vez de incremental).
Pensamentos adicionais:
- Estou nervoso com a sobrecarga administrativa e de hardware necessária para oferecer suporte a uma topologia de replicação transacional totalmente nova em 200 bancos de dados.
- O tamanho total combinado dos bancos de dados é de cerca de 100 GB. Mas a maior parte disso faz parte dos logs de transações e outras tabelas que não serão usadas em nenhum fato ou dimensão. Em outras palavras, os arquivos BCP não serão enormes, e é por isso que estou considerando uma estratégia de extração completa, embora tudo o que li recomende contra ela.
- Estou aberto a sugestões, documentos, etc.
Se você tiver 200 fontes idênticas, poderá parametrizar um pacote SSIS com a fonte de dados e iniciar vários threads. Estes podem ser controlados dentro do pacote por um loop foreach ou de uma fonte externa que inicia os extratores com um parâmetro.
Você pode considerar uma carga completa para fontes dimensionais relativamente pequenas e uma carga incremental para dados transacionais. Isso exigiria que você tivesse dimensões persistentes, mas isso é bastante simples de fazer com operações MERGE ou uma área de pré-carregamento e um manipulador de dimensões se você precisar de dimensões que mudam lentamente.
Você pode considerar dar a cada fonte sua própria área de teste (talvez um esquema para cada fonte no banco de dados de teste). Isso elimina problemas de bloqueio nas tabelas de preparação. Crie um conjunto de exibições sobre as tabelas de preparação (essencialmente apenas um conjunto de uniões que correspondem a cada uma das tabelas de origem) que inclua informações da fonte de dados. Eles podem ser gerados com bastante facilidade, portanto, você não precisa recortar e colar manualmente 200 consultas diferentes na união. Depois de preparar os dados, o processo ETL pode ler todo o lote da exibição.
Isso permite que o ETL seja executado em um hit, embora você tenha que criar uma estratégia para lidar com falhas de extração de sistemas individuais. Para isso, você pode querer olhar para uma arquitetura que lida com dados que chegam atrasados normalmente, para que você possa recuperar feeds individuais que tiveram problemas transitórios.
PCN
Para 200 extrações simples, o BCP é provavelmente um bom caminho a percorrer. As fontes são todas idênticas, então os arquivos BCP serão os mesmos nas fontes. Você pode construir um controlador de carga com SSIS. Fazer vários threads lerem o topo de uma lista comum exigiria que você implementasse o acesso sincronizado à lista. O processo SSIS tem vários loops em execução em paralelo em um contêiner de sequência que abre o próximo item, executa-o e atualiza o status correspondente.
A implementação da função 'próximo' usa um sproc em execução em uma transação serializável que retira a fonte elegível 'próxima' da lista e a marca como 'em andamento' dentro da transação. Este é um problema de 'tabela como fila', mas você não precisa implementar inserções sincronizadas - um lote inteiro pode ser enviado para a tabela no início da execução.
Estruture o processo de extração individual para que ele tente uma ou duas vezes novamente se a primeira tentativa falhar. Isso atenuará muitas falhas causadas por erros transitórios. Falhe a tarefa se ela falhar duas vezes e estruture o ETL para que seja resiliente a falhas de extração individuais.
Cargas incrementais
Provavelmente não vale a pena se preocupar com um carregador incremental para tabelas de dimensão, a menos que você tenha uma dimensão realmente grande que mostre problemas reais de desempenho. Para as fontes de dados da tabela de fatos, provavelmente vale a pena. Se você puder adicionar uma versão de linha à tabela do aplicativo com uma coluna de carimbo de data/hora ou algo semelhante, poderá obter coisas novas. No entanto, você precisará rastrear isso localmente para registrar o último registro de data e hora. Se houver uma data de inserção ou atualização nos dados, você poderá usá-la.
Cargas completas
O que poderia dar errado?
200 processos iniciando para fazer uma carga completa colocam um pico de carga na rede e possivelmente no banco de dados de preparação. Isso pode levar a todos os tipos de problemas transitórios, como tempos limite. Para tabelas de dimensões pequenas, provavelmente não é um problema tão grande. No entanto, para 100 GB, há uma grande variedade de problemas - saturação de WAN, bloqueio (embora a arquitetura de preparação correta atenue isso), disponibilidade de fontes. Quanto mais longo for o processo de extração, maior será a influência dos fatores ambientais na confiabilidade do processo.
Existem muitos imponderáveis aqui, então YMMV. Sugiro uma carga incremental para as tabelas maiores, se possível.
Você pode usar um pacote SSIS que percorre os bancos de dados de origem, exportando os dados necessários para o banco de dados de destino. Com algum trabalho, você pode criar vários segmentos e vários bancos de dados de uma só vez.
O IRI Workbench (GUI do Eclipse) suporta extração em massa (múltiplas tabelas), mapeamento (transformações, se necessário, com regras) e cargas bcp; especificamente, seu assistente de reorganização é mencionado aqui. Eu não começaria com 200 fontes de uma só vez, mas veja como funciona para as 10 primeiras para determinar se sua abordagem multi-script (que eu gosto por portabilidade e fácil modificação) funciona lá. Em seguida, desenvolva essa abordagem e veja como funciona; As ferramentas IRI são para movimentação de dados em massa; a GUI é conveniente para especificar seu uso em situações de várias fontes