Guía de migración de Oracle a SQL Server y de SQL Server a Oracle

“Dependencia del proveedor” es una palabra aterradora para muchos ejecutivos de negocios. Por otro lado, ya se entiende ampliamente en la industria que no se puede lograr una "independencia del proveedor" completa. Y es especialmente cierto en el caso de las bases de datos.

Dos de las plataformas RDBMS empresariales más proliferadas son Oracle Database y Microsoft SQL Server (para abreviar, en el resto de este artículo, las llamaré "Oracle" y "SQL Server" respectivamente). Claro, IBM Db2 compite con Oracle en plataformas mainframe cada vez más reducidas, pero aún críticas en muchas áreas. Y las alternativas de código abierto que avanzan rápidamente, como PostgreSQL, están ganando una base firme en entornos dinámicos en hardware básico de bajo a medio nivel y en la web.

Pero Oracle vs. SQL Server es la elección que enfrentan muchos ejecutivos de negocios cuando sus organizaciones necesitan un nuevo RDBMS. La selección final se basa en múltiples factores: costo de la licencia, experiencia interna disponible y experiencia pasada, compatibilidad con los entornos existentes, relaciones con los socios, planes comerciales futuros, etc. cambiar y luego la plataforma también debe cambiar. Lo sé porque en el transcurso de mi carrera, implementé este tipo de migraciones dos veces, preparé la evaluación de la viabilidad de la transición una vez y estoy trabajando en la migración de funcionalidad multiplataforma en este mismo momento.

Tanto Oracle como SQL Server son implementaciones RDBMS "de la vieja escuela", parcialmente compatibles con ANSI. Al dejar de lado las extensiones de procedimiento (PL/SQL y Transact-SQL tienen sintaxis diferentes, pero generalmente son fáciles de traducir) y futuros más nuevos orientados a objetos, el código SQL puede parecer engañosamente similar. Y esta es una peligrosa trampa de miel.

Dos de los puntos más críticos para cualquier proyecto de migración entre Oracle y SQL Server (en cualquier dirección) son las transacciones y, estrechamente relacionadas, las tablas temporales , que son una herramienta clave para resolver el alcance de las transacciones. También cubriremos las transacciones anidadas, aquellas que existen dentro del alcance de otra transacción, porque son una parte clave de la implementación de la auditoría de seguridad del usuario en Oracle. Pero en SQL Server, la auditoría de seguridad del usuario necesita un enfoque diferente debido a su comportamiento COMMIT en ese contexto.

Comprensión de la estructura de la transacción: observación de Oracle frente a SQL Server desde diez mil pies

Las transacciones de Oracle son implícitas. Significa que no necesita iniciar una transacción, siempre está en una transacción. Y esta transacción está abierta hasta que emita una declaración de compromiso o reversión. Sí, puede iniciar una transacción explícitamente, definir puntos seguros de reversión y establecer transacciones internas/anidadas; pero lo importante es que nunca "no está en una transacción" y siempre debe emitir una confirmación o reversión. También tenga en cuenta que emitir una declaración de lenguaje de definición de datos (DDL) ( CREATE , ALTER , etc.; en una transacción se puede hacer a través de SQL dinámico) confirma la transacción en la que se emitió.

A diferencia de Oracle, SQL Server tiene transacciones explícitas. Esto significa que, a menos que inicie explícitamente una transacción, todos sus cambios se confirmarán "automáticamente", inmediatamente después de que se procese su declaración, ya que cada declaración DML ( INSERT , UPDATE , DELETE ) crea una transacción por sí misma y la confirma a menos que tenga errores. fuera.

Este es el resultado de la diferencia en las implementaciones de almacenamiento de datos: cómo se escriben los datos en una base de datos y cómo los lee el motor de la base de datos.

En Oracle, las declaraciones DML cambian los registros directamente en el archivo de datos. La copia anterior del registro (o la sustitución de registros vacíos, en el caso de INSERT ) se escribe en el archivo de reversión actual y la hora exacta del cambio se marca en el registro.

Cuando se emite una instrucción SELECT , se procesa en función de los datos que se han modificado antes de su emisión. Si se ha modificado algún registro después de emitir SELECT , Oracle utiliza la versión anterior del archivo de reversión.

Así es como Oracle implementó la coherencia de lectura y la lectura/escritura sin bloqueo. También es la razón por la que las consultas de ejecución prolongada en bases de datos transaccionales muy activas a veces se encuentran con el infame error ORA-01555, snapshot too old: rollback segment ... too small . (Esto significa que el archivo de reversión que necesita la consulta de una versión anterior del registro ya se ha reutilizado). Esta es la razón por la que la respuesta correcta a la pregunta "¿Cuánto tiempo debe durar mi transacción de Oracle?" es "mientras sea necesario y no más".

La implementación de SQL Server es diferente: el motor de la base de datos escribe y lee directamente a/desde archivos de datos únicamente. Cada declaración SQL ( SELECT / INSERT / UPDATE / DELETE ) es una transacción a menos que sea parte de una transacción explícita que agrupe varias declaraciones, lo que permite revertir los cambios.

Cada transacción bloquea los recursos que necesita. Las versiones actuales de Microsoft SQL Server están altamente optimizadas para bloquear solo los recursos que se necesitan, pero lo que se necesita está definido por el código SQL, por lo que la optimización de sus consultas es fundamental. Es decir, a diferencia de Oracle, las transacciones en SQL Server deben ser lo más cortas posible, y es por eso que las confirmaciones automáticas son el comportamiento predeterminado.

¿Y qué construcción de SQL en Oracle y SQL Server se ve afectada por la diferencia en sus implementaciones de transacciones? tablas temporales.

Tablas Temporales en Oracle y SQL Server

Cuando ANSI SQL Standard define tablas temporales locales y globales, no establece explícitamente cómo deben implementarse. Tanto Oracle como SQL Server implementan tablas temporales globales. SQL Server también implementa tablas temporales locales. Oracle 18c también implementó tablas temporales locales "verdaderas" (a las que llaman "tablas temporales privadas"). Esto hace que la traducción del código de SQL Server a Oracle 18c sea visiblemente más simple que para las versiones anteriores, completando la adición anterior de Oracle de algunas tablas relacionadas. características como columnas de identidad de incremento automático.

Pero desde una perspectiva puramente de análisis funcional, la introducción de tablas temporales privadas puede ser una bendición mixta, ya que hace que los problemas de migración de SQL Server a Oracle parezcan menores de lo que son. Esta es otra trampa de miel, ya que puede presentar algunos nuevos desafíos propios. Por ejemplo, la validación del código en tiempo de diseño no se puede realizar en tablas temporales privadas, por lo que cualquier código que las use será invariablemente más propenso a errores. Si usó SQL dinámico, digámoslo de esta manera: las tablas temporales privadas son igualmente complejas de depurar, pero sin el aparente caso de uso único. Por lo tanto, Oracle agregó tablas temporales locales (privadas) solo en 18c y no antes.

En resumen, no veo un caso de uso para tablas temporales privadas en Oracle que no se pueda implementar utilizando tablas temporales globales iguales o mejores. Entonces, para cualquier conversión seria, debemos comprender la diferencia entre las tablas temporales globales de Oracle y SQL Server.

Tablas temporales globales en Oracle y SQL Server

Una tabla temporal global de Oracle es un objeto de diccionario de datos permanente creado explícitamente en tiempo de diseño por una instrucción DDL. Es "global" solo porque es un objeto a nivel de base de datos y cualquier sesión de base de datos que tenga los permisos necesarios puede acceder a él. Sin embargo, a pesar de que su estructura es global, todos los datos de una tabla temporal global se limitan únicamente a la sesión en la que opera y, en ningún caso, son visibles fuera de esta sesión. En otras palabras, otras sesiones pueden tener sus propios datos en su propia copia de la misma tabla temporal global. Por lo tanto, en Oracle, una tabla temporal global contiene datos locales de la sesión, que se utilizan principalmente en PL/SQL para simplificar el código y optimizar el rendimiento.

En SQL Server, una tabla temporal global es un objeto temporal creado en un bloque de código Transact-SQL. Existe mientras su sesión de creación esté abierta y sea visible, tanto en estructura como en datos, para otras sesiones en la base de datos. Por lo tanto, es un objeto temporal global para compartir datos entre sesiones.

Una tabla temporal local en SQL Server se diferencia de las globales en que solo se puede acceder a ella en la sesión que la crea. Y el uso de tablas temporales locales en SQL Server está mucho más extendido (y yo diría que es más crítico para el rendimiento de la base de datos) que el uso de tablas temporales globales.

Entonces, ¿cómo se usan las tablas temporales locales en SQL Server y cómo deben traducirse a Oracle?

El uso crítico (y correcto) de las tablas temporales locales en SQL Server es acortar o eliminar el bloqueo de recursos de transacciones, especialmente:

• Cuando un conjunto de registros necesita ser procesado por alguna agregación
• Cuando es necesario analizar y modificar un conjunto de datos
• Cuando el mismo conjunto de datos debe utilizarse varias veces en el mismo ámbito

En estos casos, muy a menudo es una mejor solución seleccionar este conjunto de registros en una tabla temporal local para eliminar el bloqueo de la tabla de origen.

Vale la pena señalar que las expresiones de tabla comunes (CTE, es decir, sentencias WITH <alias> AS (SELECT...) ) en SQL Server son simplemente "azúcar sintáctica". Se convierten en subconsultas en línea antes de la ejecución de SQL. Los CTE de Oracle (con una sugerencia /*+ materialize */ ) están optimizados para el rendimiento y crean una versión temporal de una vista materializada. En la ruta de ejecución de Oracle, los CTE solo acceden a los datos de origen una vez. En función de esta diferencia, SQL Server podría funcionar mejor con tablas temporales locales en lugar de varias referencias al mismo CTE, como se podría hacer en una consulta de Oracle.

Debido a la diferencia entre las implementaciones de transacciones, las tablas temporales también cumplen una función diferente. Como resultado, mover tablas temporales de SQL Server a Oracle "tal cual" (incluso con la implementación de tablas temporales privadas de Oracle 18c) no solo puede ser perjudicial para el rendimiento, sino funcionalmente incorrecto.

Por otro lado, al pasar de Oracle a SQL Server, se debe prestar atención a la duración de la transacción, el alcance de la visibilidad de las tablas temporales globales y el rendimiento de los bloques CTE con la sugerencia "materializada".

En ambos casos, tan pronto como el código migrado incluya tablas temporales, no deberíamos hablar de traducción de código, sino de reimplementación del sistema.

Introducir variables de tabla

Los desarrolladores probablemente se preguntarán: ¿Qué pasa con las variables de la tabla? ¿Necesitamos hacer algún cambio o podemos mover las variables de la tabla "tal cual" en nuestros pasos de migración de Oracle a SQL-Server? Bueno, esto depende de por qué y cómo se usan en el código.

Veamos cómo se pueden usar las tablas temporales y las variables de tabla. Comenzaré con Microsoft SQL Server.

La implementación de variables de tabla en Transact-SQL coincide un poco con las tablas temporales, pero agrega algunas funciones propias. La diferencia clave es la capacidad de pasar variables de tabla como parámetros a funciones y procedimientos almacenados.

Esta es la teoría, pero las consideraciones de uso práctico son un poco más complicadas.

Encargado por primera vez con la optimización seria de Transact-SQL cuando provenía de un entorno Oracle profundamente arraigado, esperaba que fuera de esta manera: las variables de la tabla están en la memoria mientras que las tablas temporales están en el disco. Pero descubrí que las versiones de Microsoft SQL Server hasta 2014 no almacenaban variables de tabla en la memoria. Por lo tanto, una exploración de tabla completa en una variable temporal es, de hecho, una exploración de tabla completa en el disco. Afortunadamente, SQL Server 2017 y versiones posteriores admiten la optimización de memoria declarativa para tablas temporales y variables de tabla.

Entonces, ¿cuál es el caso de uso de las variables de tabla en Transact-SQL si todo se puede hacer tan bien o mejor usando tablas temporales? La propiedad clave de una variable de tabla que es una variable y, como tal, no se ve afectada por la reversión de transacciones y se puede pasar como un parámetro.

Las funciones de Transact-SQL son muy restrictivas: como la tarea de una función es devolver un valor de retorno singular, por diseño , no puede tener efectos secundarios . Transact-SQL ve incluso SELECT como un efecto secundario, porque en SQL Server cualquier acceso a una tabla crea una transacción implícita y un bloqueo de transacción asociado. Esto significa que dentro de una función, no podemos acceder a los datos de una tabla temporal existente, ni crear una tabla temporal. Como resultado, si necesitamos pasar cualquier conjunto de registros a una función, debemos usar variables de tabla.

Las consideraciones de Oracle para el uso de tablas temporales (globales) y variables de colección (el equivalente Oracle PL/SQL de las variables de tabla Transact-SQL) son diferentes. Las variables de colección de Oracle están en la memoria, mientras que las tablas temporales se encuentran en espacios de tablas temporales. Las funciones de Oracle permiten el acceso de solo lectura a las tablas, permanentes o temporales; un simple SELECT en Oracle nunca coloca un bloqueo en los recursos.

En Oracle, la elección de usar variables de colección frente a tablas temporales se basa en la cantidad esperada de datos, la duración durante la cual estos datos deben conservarse y la memoria frente a la asignación y disponibilidad del disco. Además, las variables de colección son la forma estándar de llevar un conjunto de filas como salida a un programa host.

Como la mayoría de los elementos de sintaxis de SQL se ven muy similares entre SQL Server y Oracle, la conversión de bloques de código con variables de tabla de SQL Server Transact-SQL a Oracle PL/SQL es un proceso más simple y sintácticamente indulgente. Podría pasar una prueba de validación básica, pero no será funcionalmente correcto a menos que se tomen los pasos de reimplementación de la tabla temporal, como se describe anteriormente. Por otro lado, el código trasladado de Oracle a SQL Server implica más pasos de modificación solo para que sea sintácticamente válido. Para ser también funcionalmente correcto, deberá abordar los casos en profundidad de uso de tablas temporales y CTE.

Transacciones internas ("Transacciones anidadas")

En cuanto a los desafíos de la migración de Oracle a SQL Server, la siguiente área importante a considerar son las transacciones anidadas.

Al igual que con las tablas temporales, si el código Transact-SQL incluye transacciones, anidadas o no , o el código Oracle incluye transacciones anidadas, estamos hablando no solo de una migración de código simple, sino de una reimplementación funcional.

Primero, veamos cómo se comportan las transacciones anidadas de Oracle y cómo tendemos a usarlas.

Transacciones anidadas en Oracle

Las transacciones anidadas de Oracle son completamente atómicas e independientes del ámbito externo. No hay un uso real para las transacciones anidadas en las consultas Oracle SQL sencillas e interactivas. Cuando trabaja con Oracle en modo interactivo, simplemente confirma manualmente sus cambios cuando ve que llegó a un estado. Si realizó algunos cambios que aún no puede confirmar hasta que realice el último paso, digamos, incierto para usted, que es posible que deba revertirse, pero desea conservar el trabajo que ya ha realizado, creará un punto seguro para revertirlo sin comprometer o revertir la transacción completa.

Entonces, ¿dónde se usan las transacciones anidadas? En código PL/SQL. Más específicamente en los procedimientos autónomos, los declarados con PRAGMA AUTONOMOUS_TRANSACTION . Significa que cuando se llama a este código (como un procedimiento almacenado con nombre o de forma anónima), la transacción se confirma o revierte independientemente de la transacción que llamó a este código.

El objetivo de usar transacciones anidadas es tener una unidad de trabajo autónoma confirmada o revertida independientemente de lo que suceda con el código de llamada. Cuando una transacción interna puede comprometerse o revertirse, se usaría para verificar la disponibilidad de (o reservar) recursos compartidos, por ejemplo, al implementar un sistema de reserva de habitaciones. El uso principal de las transacciones internas de solo confirmación es la supervisión de actividades, el seguimiento de códigos y la auditoría de acceso seguro (es decir, no se permitió a un usuario realizar cambios, pero intentó hacerlo).

Las transacciones anidadas en el código Transact-SQL de SQL Server son completamente diferentes.

Transacciones anidadas en SQL Server

En Transact-SQL, si una transacción interna se confirma completamente depende de la transacción más externa. Si una transacción interna se revierte, simplemente se revierte. Pero si se ha confirmado una transacción interna, todavía no está completamente comprometida, ya que se puede revertir si cualquier nivel de su transacción de alcance externo se revierte.

Entonces, ¿cuál es el uso de las transacciones internas si sus compromisos se pueden deshacer revirtiendo su transacción externa? La respuesta es la misma que en el caso de uso de las tablas temporales locales: liberar el bloqueo de los recursos. La diferencia es que no es una liberación de bloqueo global, sino un bloqueo dentro del alcance de la transacción externa inmediata (directa "principal"). Se utiliza en código Transact-SQL complejo para liberar recursos internos para la transacción externa. Es una herramienta de optimización del rendimiento y gestión de recursos.

Como las transacciones internas/anidadas de Oracle y SQL Server tienen un comportamiento diferente (quizás incluso opuesto) y casos de uso completamente diferentes, la migración de una plataforma a otra requiere no solo una reescritura, sino una rediseñación completa de cualquier alcance que contenga bloques de transacciones anidadas. .

Otros factores

¿Son estas consideraciones centradas en tablas temporales y transacciones las únicas cosas que deben abordarse en una migración de Oracle a SQL Server? Si bien pueden ser los más importantes, definitivamente hay otros, cada uno con sus propias peculiaridades que vale la pena cubrir. A continuación se muestra el resto de lo que he encontrado que son los temas más incomprendidos:

1. Columnas de identidad en SQL Server
2. Secuencias en Oracle
3. Sinónimos en Oracle
4. Índices filtrados
5. Coherencia de lectura (solo de Oracle a SQL Server)
6. Uso de herramientas de migración.

La siguiente parte de esta serie continúa explorando estos, particularmente los primeros tres.

Tablas temporales, variables de tabla/colección y transacciones anidadas: los 3 principales puntos débiles de la migración

Comencé con tablas temporales, variables/colecciones de tablas y transacciones anidadas porque estos son los puntos de falla más comunes y obvios en los proyectos de conversión. Cualquier sistema no trivial en Oracle Database o Microsoft SQL Server sin duda usará algunos de ellos, y el uso de estos elementos está estrechamente relacionado con el diseño específico del soporte de transacciones por parte de las respectivas implementaciones de RDBMS.

¡Sigue leyendo en la Parte 2!