Temos um enorme ambiente de produção do MS SQL Server com milhares de tabelas que precisam urgentemente de ajuste e remoção de índices. Alguma sugestão sobre as melhores maneiras de testar as alterações propostas fora da produção?
Obviamente, podemos (e fazemos) criar um servidor de banco de dados DEV onde podemos fazer alterações, mas essa cópia do DEV não refletirá as demandas do mundo real feitas nas tabelas. Também temos um espelho de produção que usa envio de log de transações, mas os espelhos são sempre somente leitura (a menos que você faça failover deles para o primário), portanto, não podemos testar alterações de índice neles.
Existe alguma maneira de enviar logs de transações para uma instância que não seja um espelho somente leitura? Obviamente, isso pode encontrar problemas, especialmente se forem feitas alterações no esquema no ambiente de teste. Mesmo com alterações apenas no índice, você pode ter tempos limite de consulta e outros comportamentos inconsistentes.
Mas é exatamente isso que pretendemos fazer: dado um conjunto de alterações de índice, como funcionam as consultas do mundo real, sob carga do mundo real? Eles são mais rápidos? Eles falham?
Algumas consultas de teste não serão suficientes. Executamos todos os tipos de trabalhos de processamento noturnos, semanais e mensais que afetam fortemente o banco de dados. Também temos muitos scripts e serviços externos que consultam e atualizam o banco de dados de maneiras que nem sempre prevemos.
Dado o escopo das mudanças necessárias, estou muito hesitante em fazer quaisquer alterações no índice de produção ao vivo sem verificação.
Você pode rastrear o tráfego no servidor de produção e reproduzi-lo em um backup restaurado em um servidor de teste. Se você usar apenas consultas somente leitura, elas não precisarão ser extremamente coordenadas.
Confira o Database Experimentation Assistant ou os utilitários RML mais antigos para ajudá-lo a capturar o rastreamento, transformá-lo em uma repetição e restaurar um backup coordenado do banco de dados de produção no momento correto.
O envio de log não irá ajudá-lo. Assim que você fizer RECOVERY para fazer a leitura/gravação do banco de dados, você não poderá restaurar nenhum novo backup de log.
A menos que ... você possa cronometrar para que, depois de fazer seus testes (independentemente de como você definir esse intervalo de tempo), reinicie o envio de log a partir de um backup completo.
Outra opção seria a replicação transacional. Uma vez que funciona em um nível (basicamente) lógico, permite que você tenha diferentes esquemas de índice. Mas eu realmente não gosto de replicação do ponto de vista de gerenciamento, então esse seria meu último recurso.
IMO, você deve apresentar ao gerenciamento a necessidade de um ambiente de teste de desempenho. Retrabalhe seu envio de log atual para permitir a restauração completa periódica ou adicione um novo ambiente (portanto, presente ao mgmt) para essa finalidade.
Acho que, em vez de enviar logs para o servidor de desenvolvimento, você deve salvar e reproduzir a carga de trabalho de produção na cópia do desenvolvedor e comparar as execuções.