Eu tenho um aplicativo que possui um banco de dados e algumas versões do aplicativo apresentam alterações de esquema. Para facilitar a instalação (?), imaginei que desejaria criar uma metatabela que contém a versão atual do esquema e criar um único script que atualize o esquema de qualquer versão anterior para a versão atual.
Algo assim (pseudocódigo):
if (select v from version) = 1
create table newtable ...
update version set v = 2
end
if (select v from version) = 2
alter table newtable add column newcolumn ...
update newtable set newcolumn = ...
update version set v = 3
end
if (select v from version) = 3
...
end
...
Existem muitos problemas aqui.
A mistura de DDL e DML pode levar a problemas, como adicionar uma nova coluna e tentar atualizá-la no mesmo lote causará um erro de que a coluna não existe. Então pensei que deveria sempre separá-los em diferentes etapas, e cada etapa deveria ser um lote diferente. Obviamente, cada etapa de alteração terminará em um DML para alterar o número da versão, mas tudo bem. Deve haver transações dentro dos lotes DML e não vamos nos preocupar com os lotes DDL.
Também quero ter certeza de que, durante uma execução, apenas uma etapa seja executada, porque o tratamento de erros pode ser um pesadelo. Parar o script após uma etapa também não é trivial, nem no SSMS nem no SQLCMD. No meu exemplo acima, se começarmos na versão 1, ela será atualizada para a 2, depois para a 3, depois para a 4 etc. Então pensei em inverter a ordem das alterações. Primeiro vem a mudança de 3 para 4, depois de 2 para 3, depois de 1 para 2, desta forma, apenas uma etapa é executada por vez.
Isso parece bom? Há outras coisas a considerar?
Este é apenas o meu valor de 2 centavos...
A forma como a empresa para a qual trabalho atualmente faz suas alterações é mantendo cada script separado e tendo cada script executável novamente, portanto, se você notar um problema com um trecho de código, não haverá problema em alterá-lo e executar novamente o roteiro inteiro.
Portanto, envolvemos cada instrução DDL dentro de uma verificação para ver se a alteração já foi feita:
Caso contrário, com a configuração atual, se você cometer um erro na versão 2 e acidentalmente omitir uma instrução DDL chave, não poderá simplesmente adicioná-la a essa versão e executar novamente todo o script da v2. Em primeiro lugar porque o número da versão teria atualizado para 3, então, mesmo se você alterar o número da versão de volta para 2, a seguinte linha lançaria um erro porque a coluna já existe:
Fiz algo muito parecido, mas não como um único script. Eu usei migrações . Também descrevi isso em Controle de versão e seu banco de dados . E, de fato, cada etapa é um script diferente e o aplicativo conduz a atualização (a migração). Cada passo é testado. Cada mudança é uma atualização, seja DDL ou DML, não importa. Existem alterações DML, como alterar algumas entradas de catálogo ou tabelas de pesquisa de aplicativos, essas são atualizações. Claro, não estou falando sobre DML para o conteúdo real do aplicativo.
A mistura de DML e DDL não deve causar problemas. Os scripts podem conter vários lotes. Misturar DML e DDL dentro de uma transação pode levar a problemas e deve ser evitado.
Não tente tornar os scripts idempotentes (seguros para serem executados duas vezes). Isso vai adicionar uma tonelada de problemas, como verificar se a tabela existe, etc. Não tente fazer os scripts ACID (todos os upgrades são bem-sucedidos ou nada); é impossível. Em caso de erro, reverta para um backup. Se o custo da restauração for proibitivo (banco de dados enorme), você deve ter uma boa bateria de testes.
Teste os scripts com bancos de dados de tamanho significativo para não se deparar com surpresas de operação de tamanho de dados na atualização de produção.
Ah, e se essas mensagens do errorrlog parecerem familiares:
isso porque é assim que o próprio SQL Server cuida do versionamento do esquema.