Minhas desculpas se isso já foi respondido. Pesquisei SO e, claro, aqui no DBA, e estou surpreso por não encontrar correspondências próximas. Especificamente, estou procurando uma solução que sobreviva às diferenças de esquema.
Finalmente consegui um servidor de desenvolvimento para sombrear um servidor de data warehouse de produção. Estarei testando mudanças de schema e sproc aqui, eventualmente publicando tais mudanças no servidor prod. Para isso, precisarei de dados razoavelmente atuais, mas não precisam ser atualizados em tempo real. Meu plano é copiar os dados de estado final do servidor principal para o servidor de desenvolvimento como a etapa final do ETL noturno. Qual é a melhor maneira de conseguir isso?
Parâmetros
Todas as cópias automáticas serão de prod para dev. Qualquer cópia de dev para prod será feita à mão e, geralmente, será apenas DDL.
Como o esquema do servidor de desenvolvimento deve mudar independentemente do servidor de produção, não quero que o processo de cópia falhe quando o esquema de destino for diferente (tudo bem se as tabelas específicas em fluxo não puderem ser mantidas em sincronia, é claro) . Caso contrário, eu apenas DROP DATABASErestauraria o backup da noite passada.
Manter o esquema, SPs, exibições e UDFs sincronizados não é necessário, nem mesmo desejável. Eles só devem mudar quando eu os mudar especificamente.
Os registros podem ser alterados retroativamente na maioria das tabelas, portanto atualizações incrementais provavelmente não serão práticas.
O volume de dados é de 14 GB para as tabelas mais críticas, mais cerca de 200 GB de dados menos importantes que podem ser atualizados semanalmente.
Meu objetivo é terminar o processo em duas horas. Os servidores são co-localizados e devem ter alta taxa de transferência. Copiar ( INSERT INTO..SELECT * FROM ProdServer..) uma única tabela com 600 MB de dados e 300 de índices levou 7,5 minutos; nada bom. Preocupante, outra tabela com 11 GB de dados e 8 GB de índices não terminou em 130 minutos, quando a cancelei. Vou testar isso novamente sem índices.
Colocar o banco de dados prod offline é aceitável se puder ser breve - não mais que 30 minutos. O banco de dados de desenvolvimento pode ficar offline por horas, se necessário.
Se eu puder excluir temporariamente uma tabela específica, mantendo-a estática por alguns dias durante o teste, isso seria valioso.
Opções
1: Replicação
Eu poderia criar um instantâneo diário após o término do ETL e publicá-lo no servidor de desenvolvimento. Eu não usei a replicação antes, mas este parece ser o tipo de cenário para o qual ela se destina. Hora de aprender uma nova faceta da tecnologia?
2: Cópia em massa e inserção em massa
Eu poderia escrever um script que percorre todas as tabelas no banco de dados prod e despeja o conteúdo em um arquivo com nome exclusivo. No lado do desenvolvedor, eu percorreria esses arquivos e TRUNCATE/ BULK INSERTna tabela de destino, com um TRY..CATCHbloco caso o esquema fosse alterado. Não tenho certeza se isso teria um desempenho aceitável, mas seria bastante simples de implementar.
3: INSERT do servidor vinculado
Posso executar um TRUNCATE/ INSERT..SELECTpara cada tabela, provavelmente puxando do lado do desenvolvedor. Isso seria simples e rápido, especialmente se alguém eliminar e recriar os índices. Para lidar com as mudanças de esquema, pode-se identificar a interseção das listas de campos para cada par de tabelas e apenas tentar copiar esses campos; isso ajudaria se muitos campos fossem NULLable.
Existem outras opções? Existe uma maneira mais fácil que estou ignorando? Há alguma pegadinha que você encontrou ao trabalhar em projetos semelhantes?
Esta questão descreve o lado da exportação, mas a saída desejada era CSV, portanto as opções são limitadas (e o BCP funciona muito bem, conforme respondido).
Esta questão fala sobre como copiar rapidamente um banco de dados inteiro, incluindo o esquema. Exclui especificamente a replicação.
Esta questão discute a sincronização bidirecional, para a qual a replicação é sugerida.
Eu consideraria o backup/restauração de uma mistura de backups completos e diferenciais de produção para desenvolvimento. Em seguida, sincronize esta cópia restaurada com seus bancos de dados de desenvolvimento reais
O SQL Server 2008 R2+ oferece suporte à compactação de backup na Standard Edition (no SQL Server 2008, era apenas Enterprise Edition), o que torna isso mais fácil.
Razões:
Eu usei isso antes e vou usá-lo novamente por esses motivos
Sugeri isso em minha resposta para O menor backup possível ... com SQL Server
Integração contígua com migrações . A implantação de V. Next significa executar os scripts de atualização para V. Prev para v. Next. Os scripts são fonte, verificados. Nunca considere os arquivos binários do banco de dados (MDF, LDF) como o esquema verdadeiro. considere a fonte (.sql) como o esquema, sempre opere nos arquivos .sql, teste os arquivos .sql até ter certeza da atualização e, em seguida, implante a atualização executando o arquivo .sql.
A implantação baseada em diff está repleta de problemas, pois você está à mercê da ferramenta diff (algumas são melhores que outras, pessoalmente, eu nunca confiaria em minha implantação ao vivo em nenhuma delas). A implantação baseada em cópia nem vale a pena mencionar.
Nenhuma alteração ocorre no servidor de fígado sem passar por esse processo. Sempre .
Que tal copiar o servidor prod para o dev? Por que você precisaria fazer isso, além de comprometer os dados expondo-os a olhos não autorizados? Implante um servidor de teste/desenvolvimento usando o mesmo procedimento, integração contígua e migrações.
Em alguns cenários, é aceitável ter uma réplica de teste de prod ou uma réplica de desenvolvimento de prod. Torne-o parte da queda de compilação de integração contígua: a queda noturna começa com um backup prod, executa as migrações de v. prod para v. dev, o resultado é a variante dev do banco de dados prod, descartado recentemente. BTW, você notou que o drop também apenas testou a migração de v. prod para v. dev? Os dados precisam ser anonimizados ou alguma outra transformação para o ambiente de desenvolvimento? Torne-a uma etapa de implantação específica do desenvolvedor, é claro, controlada por scripts verificados na árvore de origem.
Eu recomendaria usar o SQL Server Integration Services (SSIS) para esse tipo de requisito. O poder do SSIS seria a facilidade de tratamento de erros (conforme exigido pela sua pergunta), a capacidade de agendá-lo e a capacidade de manutenção impecável de um pacote DTSX. Você não terá que se preocupar em vincular servidores explicitamente ou qualquer tempo de inatividade.
O SSIS lida bem com a separação de interesses. É um pacote independente que, quando usado com o SQL Server Agent, pode ser agendado todas as noites ou você pode executá-lo sob demanda.
A replicação de instantâneo seria o único tipo de replicação que poderia funcionar. Mas pode impedir alterações de esquema como as outras versões (transacional, mesclagem/ponto a ponto). Mas você ficaria melhor com um backup e restauração (as ideias de Remus parecem perfeitas), pois você não ganhará nada com a replicação para este cenário.