Guide de migration Oracle vers SQL Server et SQL Server vers Oracle

La « dépendance vis-à-vis des fournisseurs » est un mot qui fait peur à de nombreux dirigeants d'entreprise. D'un autre côté, il est déjà largement admis dans l'industrie qu'une « indépendance complète vis-à-vis du fournisseur » n'est pas réalisable. Et c'est particulièrement vrai dans le cas des bases de données.

Deux des plates-formes RDBMS d'entreprise les plus proliférées sont Oracle Database et Microsoft SQL Server (par souci de brièveté, pour le reste de cet article, je les appellerai respectivement "Oracle" et "SQL Server"). Bien sûr, IBM Db2 est en concurrence avec Oracle sur des plates-formes mainframe de plus en plus réduites, mais toujours essentielles dans de nombreux domaines. Et les alternatives open source qui progressent rapidement, telles que PostgreSQL, s'imposent solidement dans les environnements dynamiques sur du matériel standard de bas à moyen niveau et sur le Web.

Mais Oracle contre SQL Server est le choix auquel de nombreux dirigeants d'entreprise sont confrontés lorsque leurs organisations ont besoin d'un nouveau SGBDR. La sélection finale est basée sur de multiples facteurs : coût de la licence, expertise interne disponible et expérience passée, compatibilité avec les environnements existants, relations avec les partenaires, futurs plans d'affaires, etc. changer et la plate-forme doit également changer. Je le sais car au cours de ma carrière, j'ai implémenté de telles migrations à deux reprises, préparé une fois l'évaluation de la faisabilité de la transition et je travaille actuellement sur la migration de fonctionnalités multiplateformes.

Oracle et SQL Server sont tous deux des implémentations RDBMS «à l'ancienne», partiellement conformes à la norme ANSI. En laissant de côté les extensions procédurales (PL/SQL et Transact-SQL ont des syntaxes différentes, mais sont généralement simples à traduire entre elles) et les nouveaux futurs orientés objet, le code SQL peut sembler trompeusement similaire. Et c'est un dangereux piège à miel.

Deux des points les plus critiques de tout projet de migration entre Oracle et SQL Server (dans les deux sens) sont les transactions et, étroitement liées, les tables temporaires , qui sont un outil clé pour résoudre la portée des transactions. Nous couvrirons également les transactions imbriquées, celles qui existent dans le cadre d'une autre transaction, car elles constituent un élément clé de la mise en œuvre de l'audit de sécurité des utilisateurs dans Oracle. Mais dans SQL Server, l'audit de la sécurité des utilisateurs nécessite une approche différente en raison de son comportement COMMIT dans ce contexte.

Comprendre la structure des transactions : observer Oracle par rapport à SQL Server à dix mille pieds

Les transactions Oracle sont implicites. Cela signifie que vous n'avez pas besoin de démarrer une transaction, vous êtes toujours dans une transaction. Et cette transaction est ouverte jusqu'à ce que vous émettiez une instruction commit ou rollback. Oui, vous pouvez démarrer une transaction explicitement, définir des points de sécurité de restauration et définir des transactions internes/imbriquées ; mais ce qui est important, c'est que vous n'êtes jamais "pas dans une transaction" et que vous devez toujours émettre un commit ou un rollback. Notez également que l'émission d'une instruction en langage de définition de données (DDL) ( CREATE , ALTER , etc. ; dans une transaction, cela peut être fait via SQL dynamique) valide la transaction dans laquelle elle a été émise.

Contrairement à Oracle, SQL Server a des transactions explicites. Cela signifie qu'à moins que vous ne démarriez explicitement une transaction, toutes vos modifications seront validées "automatiquement" - immédiatement au fur et à mesure que votre instruction est traitée, car chaque instruction DML ( INSERT , UPDATE , DELETE ) crée une transaction par elle-même et la valide à moins qu'elle n'erreur en dehors.

C'est le résultat de la différence dans les implémentations de stockage de données - comment les données sont écrites dans une base de données et comment le moteur de base de données les lit.

Dans Oracle, les instructions DML modifient les enregistrements directement dans le fichier de données. L'ancienne copie de l'enregistrement (ou la substitution d'enregistrement vide, dans le cas de INSERT ) est écrite dans le fichier de restauration actuel et l'heure exacte de la modification est indiquée sur l'enregistrement.

Lorsqu'une instruction SELECT est émise, elle est traitée en fonction des données qui ont été modifiées avant son émission. Si des enregistrements ont été modifiés après l'émission de SELECT , Oracle utilise l'ancienne version du fichier de restauration.

C'est ainsi qu'Oracle a implémenté la cohérence de lecture et la lecture/écriture non bloquante. C'est aussi pourquoi les requêtes de longue durée sur des bases de données transactionnelles très actives se heurtaient parfois à la tristement célèbre erreur ORA-01555, snapshot too old: rollback segment ... too small . (Cela signifie que le fichier de restauration nécessaire à la requête d'une ancienne version de l'enregistrement a déjà été réutilisé.) C'est pourquoi la bonne réponse à la question "Combien de temps doit durer ma transaction Oracle ?" est "Aussi longtemps que nécessaire et pas plus".

L'implémentation de SQL Server est différente : le moteur de base de données écrit et lit directement vers/depuis les fichiers de données uniquement. Chaque instruction SQL ( SELECT / INSERT / UPDATE / DELETE ) est une transaction à moins qu'elle ne fasse partie d'une transaction explicite regroupant plusieurs instructions, permettant d'annuler les modifications.

Chaque transaction verrouille les ressources dont elle a besoin. Les versions actuelles de Microsoft SQL Server sont hautement optimisées pour ne verrouiller que les ressources nécessaires, mais ce qui est nécessaire est défini par le code SQL. L'optimisation de vos requêtes est donc essentielle). C'est-à-dire que, contrairement à Oracle, les transactions dans SQL Server doivent être aussi courtes que possible, et c'est pourquoi les validations automatiques sont le comportement par défaut.

Et quelle construction SQL dans Oracle et SQL Server est affectée par la différence dans leurs implémentations de transaction ? Tableaux temporaires.

Tables temporaires dans Oracle et SQL Server

Lorsque la norme ANSI SQL définit des tables temporaires locales et globales, elle n'indique pas explicitement comment elles doivent être implémentées. Oracle et SQL Server implémentent des tables temporaires globales. SQL Server implémente également des tables temporaires locales. Oracle 18c a également implémenté de «vraies» tables temporaires locales (qu'ils appellent «tables temporaires privées».) Cela rend la traduction du code SQL Server vers Oracle 18c visiblement plus simple que pour les versions plus anciennes, complétant l'ajout antérieur d'Oracle de quelques des fonctionnalités telles que les colonnes d'identité à incrémentation automatique.

Mais du point de vue de l'analyse fonctionnelle pure, l'introduction de tables temporaires privées peut être un bienfait mitigé car elle rend les problèmes de migration de SQL Server vers Oracle moins importants qu'ils ne le sont. C'est un autre piège à miel, car il peut introduire de nouveaux défis qui lui sont propres. Par exemple, la validation du code au moment de la conception ne peut pas être effectuée sur des tables temporaires privées, de sorte que tout code les utilisant sera invariablement plus sujet aux erreurs. Si vous avez utilisé SQL dynamique, disons-le de cette façon : les tables temporaires privées sont tout aussi complexes à déboguer, mais sans le cas d'utilisation unique apparent. C'est pourquoi Oracle a ajouté des tables temporaires locales (privées) uniquement en 18c et pas avant.

En bref, je ne vois pas de cas d'utilisation pour les tables temporaires privées dans Oracle qui ne peuvent pas être implémentées en utilisant des tables temporaires globales identiques ou meilleures. Donc, pour toute conversion sérieuse, nous devons comprendre la différence entre les tables temporaires globales Oracle et SQL Server.

Tables temporaires globales dans Oracle et SQL Server

Une table temporaire globale Oracle est un objet de dictionnaire de données permanent créé explicitement au moment de la conception par une instruction DDL. Il est "global" uniquement car il s'agit d'un objet au niveau de la base de données et est accessible par toute session de base de données disposant des autorisations requises. Cependant, bien que sa structure soit globale, toutes les données d'une table temporaire globale ne sont limitées qu'à la session dans laquelle elle opère et ne sont en aucun cas visibles en dehors de cette session. En d'autres termes, d'autres sessions peuvent avoir leurs propres données dans leur propre copie de la même table temporaire globale. Ainsi, dans Oracle, une table temporaire globale contient des données locales de session, utilisées principalement en PL/SQL pour la simplification du code et l'optimisation des performances.

Dans SQL Server, une table temporaire globale est un objet temporaire créé dans un bloc de code Transact-SQL. Il existe tant que sa session de création est ouverte et il est visible (à la fois dans la structure et les données) pour les autres sessions de la base de données. Il s'agit donc d'un objet temporaire global pour le partage de données entre les sessions.

Une table temporaire locale dans SQL Server diffère des tables globales en n'étant accessible que dans la session qui la crée. Et l'utilisation de tables temporaires locales dans SQL Server est beaucoup plus répandue (et, je dirais, plus critique pour les performances de la base de données) que l'utilisation de tables temporaires globales.

Alors, comment les tables temporaires locales sont-elles utilisées dans SQL Server et comment doivent-elles être traduites dans Oracle ?

L'utilisation critique (et correcte) des tables temporaires locales dans SQL Server consiste à raccourcir ou à supprimer le verrouillage des ressources de transaction, en particulier :

• Lorsqu'un ensemble d'enregistrements doit être traité par une agrégation
• Lorsqu'un ensemble de données doit être analysé et modifié
• Lorsque le même ensemble de données doit être utilisé plusieurs fois dans le même périmètre

Dans ces cas, il est très souvent préférable de sélectionner cet ensemble d'enregistrements dans une table temporaire locale pour supprimer le verrou de la table source.

Il convient de noter que les expressions de table courantes (CTE, c'est-à-dire les instructions WITH <alias> AS (SELECT...) ) dans SQL Server ne sont que du « sucre syntaxique ». Elles sont converties en sous-requêtes en ligne avant l'exécution SQL. Les CTE Oracle (avec un indice /*+ materialize */ ) sont optimisés en termes de performances et créent une version temporaire d'une vue matérialisée. Dans le chemin d'exécution d'Oracle, les CTE n'accèdent qu'une seule fois aux données source. Sur la base de cette différence, SQL Server peut être plus performant en utilisant des tables temporaires locales au lieu de plusieurs références au même CTE, comme cela pourrait être fait dans une requête Oracle.

En raison de la différence entre les implémentations de transaction, les tables temporaires remplissent également une fonction différente. Par conséquent, le déplacement des tables temporaires SQL Server vers Oracle "tel quel" (même avec l'implémentation d'Oracle 18c de tables temporaires privées) peut non seulement nuire aux performances, mais aussi fonctionner mal.

D'autre part, lors du passage d'Oracle à SQL Server, il convient de prêter attention à la longueur de la transaction, à la portée de la visibilité des tables temporaires globales et aux performances des blocs CTE avec l'indice « matérialisé ».

Dans les deux cas, dès que le code migré inclut des tables temporaires, il ne faut pas parler de traduction de code, mais de ré-implémentation du système.

Saisir des variables de table

Les développeurs se demanderont probablement : qu'en est-il des variables de table ? Avons-nous besoin d'apporter des modifications ou pouvons-nous déplacer les variables de table « telles quelles » dans nos étapes de migration Oracle vers SQL Server ? Eh bien, cela dépend de pourquoi et comment ils sont utilisés dans le code.

Voyons comment les tables temporaires et les variables de table peuvent être utilisées. Je vais commencer par Microsoft SQL Server.

L'implémentation des variables de table dans Transact-SQL correspond quelque peu aux tables temporaires, mais ajoute certaines fonctionnalités qui lui sont propres. La principale différence est la possibilité de transmettre des variables de table en tant que paramètres aux fonctions et aux procédures stockées.

C'est la théorie, mais les considérations d'utilisation pratique sont un peu plus impliquées.

D'abord chargé de l'optimisation sérieuse de Transact-SQL alors que je venais d'un milieu Oracle profondément ancré, je m'attendais à ce qu'il en soit ainsi : les variables de table sont en mémoire tandis que les tables temporaires sont sur disque. Mais j'ai constaté que les versions de Microsoft SQL Server jusqu'en 2014 ne stockaient pas les variables de table en mémoire. Ainsi un parcours complet de table sur une variable temporaire est bien un parcours complet de table sur le disque. Heureusement, SQL Server 2017 et les versions ultérieures prennent en charge l'optimisation déclarative de la mémoire pour les tables temporaires et les variables de table.

Alors, quel est le cas d'utilisation des variables de table dans Transact-SQL si tout peut être fait aussi bien ou mieux en utilisant des tables temporaires ? La propriété clé d'une variable de table selon laquelle il s'agit d'une variable et en tant que telle n'est pas affectée par l'annulation de la transaction et peut être transmise en tant que paramètre.

Les fonctions Transact-SQL sont très restrictives : comme la tâche d'une fonction est de renvoyer une valeur de retour singulière, elle ne peut pas, par conception, avoir d'effets secondaires . Transact-SQL considère même SELECT comme un effet secondaire, car dans SQL Server, tout accès à une table crée une transaction implicite et un verrou de transaction associé. Cela signifie qu'à l'intérieur d'une fonction, nous ne pouvons pas accéder aux données d'une table temporaire existante, ni créer une table temporaire. Par conséquent, si nous devons passer un ensemble d'enregistrements dans une fonction, nous devons utiliser des variables de table.

Les considérations d'Oracle pour l'utilisation de tables temporaires (globales) et de variables de collection (l'équivalent Oracle PL/SQL des variables de table Transact-SQL) sont différentes. Les variables de collection Oracle sont en mémoire, tandis que les tables temporaires sont situées dans des tablespaces temporaires. Les fonctions Oracle permettent un accès en lecture seule aux tables, permanent ou temporaire ; un simple SELECT dans Oracle ne place jamais un verrou sur les ressources.

Dans Oracle, le choix d'utiliser des variables de collection plutôt que des tables temporaires est basé sur la quantité de données attendue, la durée pendant laquelle ces données doivent être conservées et l'allocation et la disponibilité de la mémoire par rapport au disque. De plus, les variables de collection sont le moyen standard de prendre un jeu de lignes en sortie vers un programme hôte.

Comme la plupart des éléments de syntaxe SQL se ressemblent beaucoup entre SQL Server et Oracle, la conversion de blocs de code avec des variables de table de SQL Server Transact-SQL vers Oracle PL/SQL est un processus plus simple et plus indulgent sur le plan syntaxique. Il pourrait passer un test de validation de base, mais ne sera pas fonctionnellement correct à moins que les étapes de réimplémentation de la table temporaire ne soient prises, comme indiqué ci-dessus. D'un autre côté, le code déplacé d'Oracle vers SQL Server implique plus d'étapes de modification juste pour être syntaxiquement valide. Pour être également fonctionnellement correct, il devra traiter les cas approfondis d'utilisation de tables temporaires et de CTE.

Transactions internes ("Transactions imbriquées")

En termes de défis de migration d'Oracle vers SQL Server, le prochain domaine majeur à examiner est celui des transactions imbriquées.

Tout comme pour les tables temporaires, si le code Transact-SQL inclut une transaction, imbriquée ou non, ou si le code Oracle inclut des transactions imbriquées, nous ne parlons pas simplement d'une migration de code simple, mais d'une réimplémentation fonctionnelle.

Voyons d'abord comment se comportent les transactions imbriquées d'Oracle et comment nous avons tendance à les utiliser.

Transactions imbriquées dans Oracle

Les transactions Oracle imbriquées sont complètement atomiques et indépendantes de la portée externe. Il n'y a pas d'utilisation réelle pour les transactions imbriquées dans les requêtes Oracle SQL interactives simples. Lorsque vous travaillez avec Oracle en mode interactif, vous validez manuellement vos modifications lorsque vous voyez que vous êtes arrivé à un état. Si vous avez effectué des modifications que vous ne pouvez pas encore valider tant que vous n'aurez pas effectué la dernière étape, disons, incertaine pour vous, qui devra peut-être être annulée, mais que vous souhaitez conserver le travail que vous avez déjà effectué, vous allez créer un point de sécurité pour y revenir sans valider ni annuler la transaction complète.

Alors, où sont utilisées les transactions imbriquées ? En code PL/SQL. Plus précisément dans les procédures autonomes, celles déclarées avec PRAGMA AUTONOMOUS_TRANSACTION . Cela signifie que lorsque ce code est appelé (en tant que procédure stockée nommée ou de manière anonyme), la transaction est validée ou annulée indépendamment de la transaction qui a appelé ce code.

L'objectif de l'utilisation de transactions imbriquées est d'avoir une unité de travail autonome validée ou annulée, indépendamment de ce qui arrivera au code appelant. Lorsqu'une transaction interne peut être validée ou annulée, elle serait utilisée pour vérifier la disponibilité (ou réserver) des ressources partagées, par exemple lors de la mise en œuvre d'un système de réservation de chambres. L'utilisation principale des transactions internes à validation uniquement est la surveillance de l'activité, le suivi du code et l'audit des accès sécurisés (c'est-à-dire qu'un utilisateur n'était pas autorisé à apporter des modifications, mais a tenté de le faire.)

Les transactions imbriquées dans le code SQL Server Transact-SQL sont complètement différentes.

Transactions imbriquées dans SQL Server

Dans Transact-SQL, la validation ou non d'une transaction interne dépend entièrement de la transaction la plus externe. Si une transaction interne a été annulée, elle est simplement annulée. Mais si une transaction interne a été validée, elle n'est toujours pas entièrement validée, car elle peut être annulée si n'importe quel niveau de sa transaction de portée externe est annulé.

Alors, à quoi servent les transactions internes si ses commits peuvent être annulés en annulant sa transaction externe ? La réponse est la même que dans le cas d'utilisation des tables temporaires locales : déverrouiller les ressources. La différence est qu'il ne s'agit pas d'une libération de verrou globale, mais d'un verrou dans le cadre de la transaction externe immédiate (transaction "parente" directe). Il est utilisé dans le code Transact-SQL complexe pour libérer des ressources internes pour la transaction externe. C'est un outil d'optimisation des performances et de gestion des ressources.

Comme les transactions internes/imbriquées d'Oracle et de SQL Server ont un comportement différent (peut-être même opposé) et des cas d'utilisation complètement différents, la migration d'une plate-forme à une autre nécessite non seulement une réécriture, mais une réarchitecture complète de toute portée contenant des blocs de transaction imbriqués. .

Autres facteurs

Ces considérations centrées sur les tables temporaires et les transactions sont-elles les seules choses qui doivent être traitées dans une migration d'Oracle vers SQL Server ? Bien qu'ils soient peut-être les plus importants, il y en a certainement d'autres, chacun avec ses propres bizarreries qui valent la peine d'être couvertes. Vous trouverez ci-dessous le reste de ce que j'ai trouvé être les sujets les plus mal compris :

1. Colonnes d'identité dans SQL Server
2. Séquences dans Oracle
3. Synonymes dans Oracle
4. Index filtrés
5. Cohérence de lecture (Oracle vers SQL Server uniquement)
6. Utilisation des outils de migration

La prochaine partie de cette série continue en les explorant, en particulier les trois premiers.

Tables temporaires, variables de table/collection et transactions imbriquées : les 3 principaux problèmes de migration

J'ai commencé avec les tables temporaires, les variables/collections de table et les transactions imbriquées, car ce sont les points d'échec les plus courants et les plus évidents dans les projets de conversion. Tout système non trivial dans Oracle Database ou Microsoft SQL Server en utilisera sans aucun doute certains, et l'utilisation de ces éléments est très étroitement liée à la conception spécifique de la prise en charge des transactions par les implémentations RDBMS respectives.

Lisez la suite dans la partie 2 !