Guia de Migração de Oracle para SQL Server e SQL Server para Oracle

“Dependência de fornecedor” é uma palavra assustadora para muitos executivos de negócios. Por outro lado, já é amplamente entendido na indústria que a completa “independência do fornecedor” não é alcançável. E é especialmente verdade no caso de bancos de dados.

Duas das plataformas RDBMS corporativas mais proliferadas são o Oracle Database e o Microsoft SQL Server (para resumir, no restante deste artigo, vou chamá-los de “Oracle” e “SQL Server”, respectivamente). Claro, o IBM Db2 compete com a Oracle em plataformas de mainframe cada vez menores, mas ainda críticas em muitas áreas. E alternativas de código aberto em rápido progresso, como o PostgreSQL, estão ganhando uma base firme em ambientes dinâmicos em hardware comum de nível baixo a médio e na web.

Mas Oracle vs. SQL Server é a escolha que muitos executivos de negócios enfrentam quando suas organizações precisam de um novo RDBMS. A seleção final é baseada em vários fatores: custo da licença, conhecimento interno disponível e experiência anterior, compatibilidade com ambientes existentes, relações com parceiros, planos de negócios futuros etc. mudar e então a plataforma precisa mudar também. Sei disso porque, no decorrer da minha carreira, implementei essas migrações duas vezes, preparei a avaliação da viabilidade da transição uma vez e estou trabalhando na migração de funcionalidades entre plataformas neste momento.

Tanto o Oracle quanto o SQL Server são implementações de RDBMS “old-school”, parcialmente compatíveis com ANSI. Ao deixar de lado as extensões procedurais – PL/SQL e Transact-SQL têm sintaxes diferentes, mas geralmente são simples de traduzir – e futuros orientados a objetos mais recentes, o código SQL pode parecer enganosamente semelhante. E esta é uma perigosa armadilha de mel.

Dois dos pontos mais críticos para qualquer projeto de migração entre Oracle e SQL Server (em qualquer direção) são as transações e, intimamente relacionadas, as tabelas temporárias , que são uma ferramenta fundamental na resolução do escopo da transação. Também abordaremos as transações aninhadas — aquelas que existem no escopo de outra transação — porque são uma parte essencial da implementação da auditoria de segurança do usuário no Oracle. Mas no SQL Server, a auditoria de segurança do usuário precisa de uma abordagem diferente devido ao seu comportamento COMMIT nesse contexto.

Entendendo a estrutura da transação: observando Oracle vs. SQL Server a partir de dez mil pés

As transações Oracle são implícitas. Isso significa que você não precisa iniciar uma transação—você está sempre em uma transação. E esta transação está aberta até que você emita uma declaração de confirmação ou reversão. Sim, você pode iniciar uma transação explicitamente, definir pontos seguros de reversão e definir transações internas/aninhadas; mas o importante é que você nunca está “fora de uma transação” e deve sempre emitir um commit ou rollback. Observe também que emitir uma instrução de linguagem de definição de dados (DDL) ( CREATE , ALTER , etc.; em uma transação isso pode ser feito através de SQL dinâmico) confirma a transação na qual foi emitida.

Ao contrário do Oracle, o SQL Server possui transações explícitas. Isso significa que, a menos que você inicie explicitamente uma transação, todas as suas alterações serão confirmadas "automaticamente" - imediatamente à medida que sua instrução for processada, pois cada instrução DML ( INSERT , UPDATE , DELETE ) cria uma transação por conta própria e a confirma, a menos que haja erros Fora.

Este é o resultado da diferença nas implementações de armazenamento de dados – como os dados são gravados em um banco de dados e como o mecanismo de banco de dados os lê.

No Oracle, as instruções DML alteram os registros diretamente no arquivo de dados. A cópia antiga do registro (ou substituição de registro vazio, no caso de INSERT ) é gravada no arquivo de rollback atual e a hora exata da alteração é marcada no registro.

Quando uma SELECT é emitida, ela é processada com base nos dados que foram modificados antes de sua emissão. Se algum registro tiver sido modificado após a emissão do SELECT , o Oracle usará a versão mais antiga do arquivo de rollback.

Foi assim que a Oracle implementou a consistência de leitura e leitura/gravação sem bloqueio. É também por isso que consultas de longa duração em bancos de dados transacionais muito ativos às vezes se deparam com o infame erro ORA-01555, snapshot too old: rollback segment ... too small . (Isso significa que o arquivo de rollback necessário para a consulta de uma versão mais antiga do registro já foi reutilizado.) É por isso que a resposta correta para a pergunta “Qual deve ser a duração da minha transação Oracle?” é “O tempo que for necessário e não mais”.

A implementação do SQL Server é diferente: O mecanismo de banco de dados grava e lê diretamente apenas para/de arquivos de dados. Cada instrução SQL ( SELECT / INSERT / UPDATE / DELETE ) é uma transação, a menos que seja parte de uma transação explícita agrupando várias instruções, permitindo que as alterações sejam revertidas.

Cada transação bloqueia os recursos de que precisa. As versões atuais do Microsoft SQL Server são altamente otimizadas para bloquear apenas os recursos necessários, mas o que é necessário é definido pelo código SQL – portanto, otimizar suas consultas é fundamental). Ou seja, diferentemente do Oracle, as transações no SQL Server devem ser as mais curtas possíveis, e é por isso que os commits automáticos são o comportamento padrão.

E qual construção SQL no Oracle e no SQL Server é afetada pela diferença em suas implementações de transação? Tabelas temporárias.

Tabelas temporárias em Oracle e SQL Server

Quando o padrão SQL ANSI define tabelas temporárias locais e globais, ele não declara explicitamente como elas devem ser implementadas. Tanto o Oracle quanto o SQL Server implementam tabelas temporárias globais. O SQL Server também implementa tabelas temporárias locais. O Oracle 18c também implementou tabelas temporárias locais “verdadeiras” (que eles chamam de “tabelas temporárias privadas”.) recursos como colunas de identidade de incremento automático.

Mas de uma perspectiva de análise funcional pura, a introdução de tabelas temporárias privadas pode ser uma bênção mista, pois faz com que os problemas de migração do SQL Server para o Oracle pareçam menores do que são. Esta é outra armadilha do mel, pois pode apresentar alguns novos desafios próprios. Por exemplo, a validação de código em tempo de design não pode ser feita em tabelas temporárias privadas, portanto, qualquer código que as use será invariavelmente mais propenso a erros. Se você usou SQL dinâmico, vamos colocar desta forma: tabelas temporárias privadas são tão complexas para depurar, mas sem o aparente caso de uso único. Daí porque a Oracle adicionou tabelas temporárias locais (privadas) apenas em 18c e não antes.

Resumindo, não vejo um caso de uso para tabelas temporárias privadas no Oracle que não possam ser implementadas usando tabelas temporárias globais iguais ou melhores. Portanto, para qualquer conversão séria, precisamos entender a diferença entre as tabelas temporárias globais do Oracle e do SQL Server.

Tabelas temporárias globais em Oracle e SQL Server

Uma tabela temporária global do Oracle é um objeto de dicionário de dados permanente criado explicitamente em tempo de design por uma instrução DDL. É “global” apenas porque é um objeto de nível de banco de dados e pode ser acessado por qualquer sessão de banco de dados que tenha as permissões necessárias. No entanto, apesar de sua estrutura ser global, todos os dados em uma tabela temporária global têm como escopo apenas a sessão em que ela opera e não são, em nenhuma circunstância, visíveis fora dessa sessão. Em outras palavras, outras sessões podem ter seus próprios dados em sua própria cópia da mesma tabela temporária global. Assim, no Oracle, uma tabela temporária global contém dados locais de sessão — usados ​​principalmente em PL/SQL para simplificação de código e otimização de desempenho.

No SQL Server, uma tabela temporária global é um objeto temporário criado em um bloco de código Transact-SQL. Ele existe desde que sua sessão de criação esteja aberta e visível - tanto em estrutura quanto em dados - para outras sessões no banco de dados. Portanto, é um objeto temporário global para compartilhar dados entre sessões.

Uma tabela temporária local no SQL Server difere das globais por ser acessível apenas na sessão que a cria. E o uso de tabelas temporárias locais no SQL Server é muito mais difundido (e, eu diria, mais crítico para o desempenho do banco de dados) do que o uso de tabelas temporárias globais.

Então, como as tabelas temporárias locais são usadas no SQL Server e como elas devem ser traduzidas para o Oracle?

O uso crítico (e correto) de tabelas temporárias locais no SQL Server é encurtar ou remover o bloqueio de recursos de transação, especialmente:

• Quando um conjunto de registros precisa ser processado por alguma agregação
• Quando o conjunto de dados precisa ser analisado e modificado
• Quando o mesmo conjunto de dados precisa ser usado várias vezes no mesmo escopo

Nesses casos, muitas vezes é uma solução melhor selecionar esse conjunto de registros em uma tabela temporária local para remover o bloqueio da tabela de origem.

Vale a pena notar que as expressões de tabela comuns (CTEs, ou seja, WITH <alias> AS (SELECT...) instruções) no SQL Server são apenas “açúcar sintático”. Eles são convertidos em subconsultas em linha antes da execução do SQL. Oracle CTEs (com uma dica /*+ materialize */ ) são otimizados para desempenho e criam uma versão temporária de uma visualização materializada. No caminho de execução do Oracle, os CTEs acessam os dados de origem apenas uma vez. Com base nessa diferença, o SQL Server pode ter um desempenho melhor usando tabelas temporárias locais em vez de várias referências ao mesmo CTE, como poderia ser feito em uma consulta Oracle.

Devido à diferença entre as implementações de transações, as tabelas temporárias também têm uma função diferente. Como resultado, mover as tabelas temporárias do SQL Server para o Oracle “como está” (mesmo com a implementação de tabelas temporárias privadas do Oracle 18c) pode não apenas prejudicar o desempenho, mas também ser funcionalmente errado.

Por outro lado, ao migrar do Oracle para o SQL Server, é preciso prestar atenção ao comprimento da transação, ao escopo de visibilidade das tabelas temporárias globais e ao desempenho dos blocos CTE com a dica “materializada”.

Em ambos os casos, assim que o código migrado incluir tabelas temporárias, devemos falar não sobre tradução de código, mas sobre reimplementação do sistema.

Digite as variáveis ​​da tabela

Os desenvolvedores provavelmente se perguntarão: E as variáveis ​​de tabela? Precisamos fazer alguma alteração ou podemos mover variáveis ​​de tabela “como estão” em nossas etapas de migração Oracle-to-SQL-Server? Bem, isso depende do porquê e como eles são usados ​​no código.

Vejamos como tabelas temporárias e variáveis ​​de tabela podem ser usadas. Vou começar com o Microsoft SQL Server.

A implementação de variáveis ​​de tabela no Transact-SQL corresponde de certa forma às tabelas temporárias, mas adiciona algumas funcionalidades próprias. A principal diferença é a capacidade de passar variáveis ​​de tabela como parâmetros para funções e procedimentos armazenados.

Esta é a teoria, mas as considerações de uso prático são um pouco mais complexas.

Primeiro encarregado de uma otimização Transact-SQL séria quando eu vinha de um fundo Oracle profundamente enraizado, eu esperava que fosse assim: as variáveis ​​de tabela estão na memória enquanto as tabelas temporárias estão no disco. Mas descobri que as versões do Microsoft SQL Server até 2014 não armazenavam variáveis ​​de tabela na memória. Assim, uma varredura completa da tabela em uma variável temporária é de fato uma varredura completa da tabela no disco. Felizmente, o SQL Server 2017 e versões posteriores oferecem suporte à otimização de memória declarativa para tabelas temporárias e variáveis ​​de tabela.

Então, qual é o caso de uso para variáveis ​​de tabela no Transact-SQL se tudo pode ser feito tão bem ou melhor usando tabelas temporárias? A propriedade chave de uma variável de tabela que é uma variável e, como tal, não é afetada pela reversão da transação e pode ser passada como um parâmetro.

As funções Transact-SQL são muito restritivas: como a tarefa de uma função é retornar algum valor de retorno singular, ela - por design - não pode ter efeitos colaterais . O Transact-SQL vê até SELECT como um efeito colateral, porque no SQL Server qualquer acesso a uma tabela cria uma transação implícita e um bloqueio de transação associado. Isso significa que dentro de uma função, não podemos acessar dados em uma tabela temporária existente, nem criar uma tabela temporária. Como resultado, se precisarmos passar qualquer conjunto de registros para uma função, temos que usar variáveis ​​de tabela.

As considerações da Oracle para usar tabelas temporárias (globais) e variáveis ​​de coleção (o equivalente Oracle PL/SQL das variáveis ​​de tabela Transact-SQL) são diferentes. As variáveis ​​de coleção do Oracle estão na memória, enquanto as tabelas temporárias estão localizadas em tablespaces temporários. As funções do Oracle permitem acesso somente leitura a tabelas, permanentes ou temporárias; um simples SELECT no Oracle nunca bloqueia recursos.

No Oracle, a escolha de usar variáveis ​​de coleção versus tabelas temporárias é baseada na quantidade esperada de dados, a duração pela qual esses dados precisam ser preservados e memória versus alocação e disponibilidade de disco. Além disso, as variáveis ​​de coleção são a maneira padrão de levar um conjunto de linhas como saída de volta a um programa host.

Como a maioria dos elementos de sintaxe SQL são muito semelhantes entre o SQL Server e o Oracle, a conversão de blocos de código com variáveis ​​de tabela do SQL Server Transact-SQL em Oracle PL/SQL é um processo mais simples e sintaticamente mais tolerante. Ele pode passar em um teste de validação básico, mas não será funcionalmente correto a menos que as etapas de reimplementação da tabela temporária sejam executadas, conforme descrito acima. Por outro lado, o código movido do Oracle para o SQL Server envolve mais etapas de modificação apenas para ser sintaticamente válido. Para também ser funcionalmente correto, ele precisará abordar os casos detalhados de uso de tabelas temporárias e CTEs.

Transações Internas (“Transações Aninhadas”)

Em termos de desafios de migração de Oracle para SQL Server, a próxima área importante a ser observada são as transações aninhadas.

Assim como nas tabelas temporárias, se o código Transact-SQL incluir qualquer transação, aninhada ou não, ou o código Oracle incluir transações aninhadas, não estamos falando apenas de uma migração de código simples, mas de reimplementação funcional.

Primeiro, vamos ver como as transações aninhadas do Oracle se comportam e como tendemos a usá-las.

Transações aninhadas no Oracle

As transações aninhadas do Oracle são completamente atômicas e independentes do escopo externo. Não há uso real para transações aninhadas em consultas Oracle SQL simples e interativas. Quando você está trabalhando com o Oracle no modo interativo, você apenas confirma manualmente suas alterações quando vê que chegou a um estado. Se você fez algumas alterações que ainda não pode confirmar até fazer a última etapa - digamos, incerta para você - que pode precisar ser revertida, mas você deseja preservar o trabalho que já foi feito por você, você criará um ponto seguro para reverter para ele sem confirmar ou reverter a transação completa.

Então, onde as transações aninhadas são usadas? Em código PL/SQL. Mais especificamente em procedimentos autônomos—aqueles declarados com PRAGMA AUTONOMOUS_TRANSACTION . Isso significa que quando esse código é chamado (como um procedimento armazenado nomeado ou anonimamente) a transação é confirmada ou revertida independentemente da transação que chamou esse código.

O objetivo de usar transações aninhadas é ter uma unidade de trabalho autocontida confirmada ou revertida, independentemente do que acontecerá com o código de chamada. Quando uma transação interna pode ser confirmada ou revertida, ela seria usada para verificar a disponibilidade de (ou reservar) recursos compartilhados – por exemplo, na implementação de um sistema de reserva de quartos. O principal uso para transações internas somente de confirmação é o monitoramento de atividades, rastreamento de código e auditoria de acesso seguro (ou seja, um usuário não tinha permissão para fazer alterações, mas tentou fazê-lo).

As transações aninhadas no código SQL Server Transact-SQL são completamente diferentes.

Transações aninhadas no SQL Server

No Transact-SQL, se uma transação interna é confirmada depende completamente da transação mais externa. Se uma transação interna tiver sido revertida, ela será revertida. Mas se uma transação interna foi confirmada, ela ainda não está totalmente confirmada, pois pode ser revertida se qualquer nível de sua transação de escopo externo for revertida.

Então, qual é o uso de transações internas se seus commits podem ser desfeitos revertendo sua transação externa? A resposta é a mesma do caso de uso para tabelas temporárias locais: liberar o bloqueio de recursos. A diferença é que não é uma liberação de bloqueio global, mas um bloqueio dentro do escopo da transação externa imediata (direta “pai”). Ele é usado no código Transact-SQL complexo para liberar recursos internos para a transação externa. É uma ferramenta de otimização de desempenho e gerenciamento de recursos.

Como as transações internas/aninhadas do Oracle e do SQL Server têm comportamento diferente (talvez até oposto) e casos de uso completamente diferentes, a migração de uma plataforma para outra requer não apenas uma reescrita, mas uma rearquitetura completa de qualquer escopo que contenha blocos de transação aninhados .

Outros fatores

Essas considerações centradas na tabela temporária e na transação são as únicas coisas que precisam ser abordadas em uma migração do Oracle para o SQL Server? Embora possam ser os mais importantes, definitivamente existem outros, cada um com suas próprias peculiaridades que valem a pena cobrir. Abaixo está o restante do que eu achei os tópicos mais incompreendidos:

1. Colunas de identidade no SQL Server
2. Sequências no Oracle
3. Sinônimos em Oracle
4. Índices filtrados
5. Consistência de leitura (somente Oracle para SQL Server)
6. Uso de ferramentas de migração

A próxima parte desta série continua explorando estes, particularmente os três primeiros.

Tabelas temporárias, variáveis ​​de tabela/coleção e transações aninhadas: os 3 principais pontos problemáticos da migração

Comecei com tabelas temporárias, variáveis/coleções de tabela e transações aninhadas porque esses são os pontos de falha mais comuns e óbvios em projetos de conversão. Qualquer sistema não trivial no Banco de Dados Oracle ou Microsoft SQL Server, sem dúvida, usará alguns deles, e o uso desses elementos está intimamente ligado ao design específico de suporte a transações pelas respectivas implementações de RDBMS.

Leia na Parte 2!