Guide de migration Oracle vers SQL Server et SQL Server vers Oracle - Pt. 3

Les première et deuxième parties de cette série ont abordé les différences entre Oracle Database et Microsoft SQL Server dans leur implémentation des transactions, et les pièges de conversion qui en résultent, ainsi que certains éléments de syntaxe couramment utilisés.

Ce dernier volet couvrira la notion de cohérence de lecture Oracle et comment convertir l'architecture, basée sur cette notion, en une version Microsoft SQL Server. Il abordera également l'utilisation des synonymes (et comment ne pas les utiliser) et le rôle du processus de contrôle des modifications dans la gestion de votre environnement de base de données.

Oracle Read Cohérence et son équivalent dans SQL Server

La cohérence de lecture Oracle est une garantie que toutes les données renvoyées par une seule instruction SQL proviennent du même point singulier dans le temps.

Cela signifie que si vous avez émis une SELECT à 12:01:02.345 et qu'elle a fonctionné pendant 5 minutes avant de renvoyer le jeu de résultats, toutes les données (et uniquement les données) qui ont été validées dans la base de données à partir de 12:01:02.345 le feront dans votre jeu de retour. Votre ensemble de retours n'aura aucune nouvelle donnée ajoutée pendant ces 5 minutes qu'il a fallu à la base de données pour traiter votre relevé, ni aucune mise à jour, et aucune suppression ne sera visible.

L'architecture Oracle assure la cohérence de lecture en horodatant en interne chaque modification apportée aux données et en créant un ensemble de résultats à partir de deux sources : des fichiers de données permanents et un segment d'annulation (ou "segment d'annulation", comme on l'appelait jusqu'à la version 10g).

Afin de le prendre en charge, les informations d'annulation doivent être conservées. S'il est écrasé, cela entraîne la tristement célèbre erreur ORA-01555: snapshot too old .

Laissant de côté la gestion des segments annulés - et comment naviguer dans l'erreur ORA-01555: snapshot too old - examinons les implications de la cohérence de lecture sur toute implémentation pratique dans Oracle. De plus, comment devrait-il être mis en miroir dans SQL Server, qui, comme c'est le cas avec d'autres implémentations RDBMS, à l'exception possible et qualifiée de PostgreSQL, ne le prend pas en charge ?

La clé est qu'Oracle lit et écrit ne se bloque pas. Cela signifie également que votre jeu de retour de requête de longue durée peut ne pas contenir les dernières données.

Les lectures et écritures non bloquantes sont un avantage d'Oracle et affectent la portée des transactions.

Mais la cohérence de lecture signifie également que vous ne disposez pas du dernier état des données. Lorsque dans certains scénarios, c'est parfaitement bon (comme la production d'un rapport pour un moment particulier), cela peut créer des problèmes importants dans d'autres.

Ne pas disposer des données les plus récentes, même « sales » ou non validées, peut être critique : le scénario classique est un système de réservation de chambres d'hôtel.

Considérez le cas d'utilisation suivant : vous avez deux agents du service client qui acceptent simultanément les commandes de réservation de salle. Comment pouvez-vous vous assurer que les chambres ne deviennent pas surréservées ?

Dans SQL Server, vous pouvez démarrer une transaction explicite et SELECT un enregistrement dans la liste (qui peut être une table ou une vue) des salles disponibles. Tant que cette transaction n'est pas fermée (soit par COMMIT ou ROLLBACK ), personne ne peut obtenir le même enregistrement de pièce que vous avez sélectionné. Cela évite les doubles réservations, mais oblige également tous les autres agents à attendre les uns les autres pour effectuer les demandes de réservation une par une, de manière séquentielle.

Dans Oracle, vous pouvez obtenir le même résultat en émettant une SELECT ... FOR UPDATE sur les enregistrements correspondant à vos critères de recherche.

Remarque : de meilleures solutions existent, comme la mise en place d'un drapeau temporaire marquant une pièce « à l'étude » au lieu d'en verrouiller aveuglément l'accès. Mais ce sont des solutions architecturales, pas des options linguistiques.

Conclusion : La cohérence de lecture d'Oracle n'est pas "tout bonne" ou "tout mauvaise" mais une propriété importante de la plate-forme qui doit être bien comprise et qui est essentielle à la migration de code multiplateforme.

Synonymes publics (et privés) dans Oracle et Microsoft SQL Server

"Les synonymes publics sont mauvais." Ce n'est pas exactement ma découverte personnelle, mais je l'avais acceptée comme évangile jusqu'à ce que ma journée, ma semaine et mon année soient sauvées par des synonymes publics.

Dans de nombreux environnements de base de données - je dirais tous les environnements Oracle avec lesquels j'ai eu l'occasion de travailler, mais aucun que j'ai conçu -, l'utilisation de CREATE PUBLIC SYNONYM pour chaque objet était une routine car "nous l'avons toujours fait de cette façon".

Dans ces environnements, les synonymes publics n'avaient qu'une seule fonction : permettre de faire référence à un objet sans spécifier son propriétaire. Et c'est une raison mal pensée de rendre les synonymes publics.

Cependant, les synonymes publics Oracle peuvent être extrêmement utiles et offrir des avantages de productivité d'équipe qui l'emportent largement sur tous leurs inconvénients, s'ils sont implémentés et gérés correctement et avec raison. Oui, j'ai dit "productivité de l'équipe". Mais comment? Pour cela, nous devons comprendre comment fonctionne la résolution de noms dans Oracle.

Lorsque l'analyseur Oracle trouve un nom (un mot clé non réservé), il essaie de le faire correspondre à un objet de base de données existant dans l'ordre suivant :

Remarque : L'erreur générée sera ORA-00942: table or view does not exist pour les instructions DML, ou PLS-00201: identifier 'my_object' must be declared pour les procédures stockées ou les appels de fonction.

Dans cet ordre de résolution de noms, il est facile de voir que lorsqu'un développeur travaille dans son propre schéma, tout objet local portant le même nom qu'un synonyme public masquera ce synonyme public. (Remarque : Oracle 18c a implémenté le type de schéma « connexion uniquement », et cette discussion ne s'y applique pas.)

Synonymes publics pour les équipes de mise à l'échelle : Oracle Change Control

Regardons maintenant une équipe hypothétique de 100 développeurs travaillant sur la même base de données (ce que j'ai vécu). De plus, supposons qu'ils travaillent tous localement sur leurs postes de travail personnels et effectuent indépendamment des constructions non basées sur des bases de données, tous liés au même environnement de développement de base de données. La résolution de la fusion de code dans du code non basé sur une base de données (que ce soit C #, Java, C ++, Python ou autre) sera effectuée au moment de l'enregistrement du contrôle des modifications et prendra effet avec la prochaine génération de code. Mais les tables, le code et les données de la base de données doivent être modifiés plusieurs fois au cours du développement en cours. Chaque développeur le fait indépendamment, et cela prend effet immédiatement.

Pour cela, tous les objets de la base de données sont créés dans un schéma d'application commun. Il s'agit du schéma auquel l'application fait référence. Chaque développeur :

• Se connecte à la base de données avec son compte/schéma utilisateur personnel
• Commence toujours par un schéma personnel vide
• Référence le schéma commun uniquement via la résolution de nom à un synonyme public, comme décrit ci-dessus

Lorsqu'un développeur doit apporter des modifications à la base de données (créer ou modifier une table, modifier le code d'une procédure ou même modifier un ensemble de données pour prendre en charge un scénario de test), il crée une copie de l'objet dans son schéma personnel. Pour ce faire, ils récupèrent le code DDL à l'aide de la commande DESCRIBE et l'exécutent localement.

À partir de ce moment, le code de ce développeur verra la version locale de l'objet et des données, qui ne seront pas visibles (ni n'auront d'impact sur) personne d'autre. Une fois le développement terminé, le code de base de données modifié est archivé dans le contrôle de code source et les conflits sont résolus. Ensuite, le code final (et les données, si nécessaire) est implémenté dans le schéma commun.

Après cela, toute l'équipe de développement peut revoir la même base de données. Le développeur qui vient de livrer le code supprime tous les objets de son schéma personnel et est prêt pour une nouvelle mission.

Cette capacité à faciliter le travail parallèle indépendant pour plusieurs développeurs est le principal avantage des synonymes publics, une importance difficile à surestimer. Cependant, dans la pratique, je continue de voir des équipes créer des synonymes publics dans les implémentations Oracle "juste parce que nous le faisons toujours". En revanche, dans les équipes utilisant SQL Server, je ne vois pas la création de synonymes publics établie comme une pratique courante. La fonctionnalité existe mais n'est pas souvent utilisée.

Dans SQL Server, le schéma par défaut actuel d'un utilisateur est défini dans la configuration de l'utilisateur et peut être modifié à tout moment si vous disposez des privilèges « modifier l'utilisateur ». La même méthodologie exacte que celle décrite ci-dessus pour Oracle peut être mise en œuvre. Cependant, si cette méthode n'est pas utilisée, les synonymes publics ne doivent pas être copiés.

Comme Microsoft SQL Server n'associe pas un nouveau compte d'utilisateur à son propre schéma par défaut (comme le fait Oracle), l'association doit faire partie de votre script standard de "création d'utilisateur".

Vous trouverez ci-dessous un exemple de script qui crée des schémas utilisateur dédiés et en attribue un à un utilisateur.

Tout d'abord, créez des schémas pour les nouveaux utilisateurs qui doivent être intégrés à la base de données nommée DevelopmentDatabase (chaque schéma doit être créé dans son propre lot) :

 use DevelopmentDatabase; GO CREATE SCHEMA Dev1; GO CREATE SCHEMA Dev2; GO

Deuxièmement, créez le premier utilisateur avec son schéma par défaut :

 CREATE LOGIN DevLogin123 WITH PASSWORD = 'first_pass123'; CREATE USER Dev1 FOR LOGIN DevLogin123 WITH DEFAULT_SCHEMA = Dev1; GO

À ce stade, le schéma par défaut pour l'utilisateur Dev1 serait Dev1 .

Ensuite, créez l'autre utilisateur sans schéma par défaut :

 CREATE LOGIN DevLogin321 WITH PASSWORD = 'second_pass321'; CREATE USER Dev2 FOR LOGIN DevLogin321; GO

Le schéma par défaut pour l'utilisateur Dev2 est dbo .

Modifiez maintenant l'utilisateur Dev2 pour changer son schéma par défaut en Dev2 :

 ALTER USER Dev2 WITH DEFAULT_SCHEMA = Dev2; GO

Désormais, le schéma par défaut de l'utilisateur Dev2 est Dev2 .

Ce script illustre deux manières d'affecter et de modifier un schéma par défaut pour un utilisateur dans les bases de données Microsoft SQL Server. Étant donné que SQL Server prend en charge plusieurs méthodes d'authentification des utilisateurs (la plus courante est l'authentification Windows) et que l'intégration des utilisateurs peut être gérée par les administrateurs système plutôt que par les DBA, la méthode ALTER USER d'attribution/modification du schéma par défaut sera plus utilisable.

Remarque : J'ai fait en sorte que le nom du schéma soit identique au nom d'un utilisateur. Il n'est pas nécessaire que ce soit ainsi dans SQL Server, mais c'est ma préférence car (1) cela correspond à la façon dont cela se fait dans Oracle et (2) cela simplifie la gestion des utilisateurs (répondant à la plus grande objection de la part d'un DBA à le faire correctement en premier lieu) - vous connaissez le nom d'un utilisateur et vous connaissez automatiquement le schéma par défaut de l'utilisateur.

Conclusion : Les synonymes publics sont un outil important pour construire un environnement de développement multi-utilisateurs stable et bien protégé. Malheureusement, d'après mes observations dans l'industrie, il est plus souvent utilisé pour de mauvaises raisons, laissant les équipes souffrir de la confusion et d'autres inconvénients des synonymes publics sans réaliser leurs avantages. Changer cette pratique pour tirer de réels avantages des synonymes publics peut apporter de réels avantages au flux de travail de développement d'une équipe.

Processus de gestion des accès à la base de données et de gestion des modifications

Comme nous venons de parler de la prise en charge du développement parallèle par de grandes équipes, il convient d'aborder un sujet distinct et souvent mal compris : les processus de contrôle des modifications.

La gestion du changement devient souvent une forme de bureaucratie contrôlée par les chefs d'équipe et les administrateurs de base de données, méprisée par les développeurs rebelles qui veulent tout livrer, sinon « hier », puis « maintenant ».

En tant que DBA, je mets toujours des barrières de protection sur le chemin de "ma" base de données. Et j'ai une très bonne raison à cela : une base de données est une ressource partagée.

Tweeter

Dans un contexte de contrôle de source, la gestion du changement est généralement acceptée car elle permet à une équipe de revenir d'un code nouveau mais cassé à un code ancien mais fonctionnel. Mais dans un contexte de base de données, la gestion du changement peut apparaître comme un ensemble de barrières et de restrictions déraisonnables imposées par les DBA : c'est de la pure folie qui ralentit inutilement le développement !

Laissons de côté ce coup de gueule de développeur : je suis DBA et je ne vais pas me jeter la pierre ! En tant que DBA, je mets toujours des barrières de protection sur le chemin de « ma » base de données. Et j'ai une très bonne raison à cela : une base de données est une ressource partagée.

Chaque équipe de développement - et chacun de leurs développeurs - a un objectif très précisément défini et un livrable très spécifique. Le seul objectif qui est sur le bureau d'un DBA chaque jour est la stabilité de la base de données en tant que ressource partagée. Un DBA a le rôle unique dans une organisation de superviser tous les efforts de développement de toutes les équipes et de contrôler une base de données à laquelle tous les développeurs ont accès. C'est le DBA qui s'assure que tous les projets et tous les processus fonctionnent sans interférer les uns avec les autres et que chacun dispose des ressources nécessaires pour fonctionner.

Le problème est lorsque les équipes de développement et de DBA sont enfermées dans leurs tours d'ivoire respectives.

Les développeurs ne savent pas, n'ont pas accès et ne se soucient même pas de ce qui se passe sur la base de données tant qu'elle fonctionne correctement pour eux. (Ce n'est pas leur livrable et cela n'affectera pas leur évaluation des performances.)

L'équipe DBA garde la base de données à portée de main, la protégeant des développeurs qui "n'y connaissent rien", car l'objectif de leur équipe est la stabilité de la base de données. Et la meilleure façon d'assurer la stabilité est d'empêcher les changements destructeurs, ce qui se traduit souvent par une attitude de protection de la base de données contre tout changement autant que possible.

Ces attitudes conflictuelles envers une base de données peuvent, comme je l'ai vu, conduire à l'animosité entre les équipes de développement et DBA et aboutir à un environnement ingérable. Mais les administrateurs de base de données et l'équipe de développement doivent travailler ensemble pour atteindre un objectif commun : fournir une solution métier, ce qui les a réunis en premier lieu.

Ayant été des deux côtés de la division développeur-DBA, je sais que le problème est facile à résoudre lorsque les DBA comprennent mieux les tâches et les objectifs communs des équipes de développement. De leur côté, les développeurs doivent voir une base de données non pas comme un concept abstrait mais comme une ressource partagée - et là, un DBA devrait assumer le rôle d'un éducateur.

L'erreur la plus courante commise par les administrateurs de bases de données non développeurs consiste à restreindre l'accès des développeurs au dictionnaire de données et aux outils d'optimisation du code. L'accès aux vues de catalogue Oracle DBA_ , aux vues dynamiques V$ et aux tables SYS semble à de nombreux DBA comme "privilégié DBA" alors qu'il s'agit en fait d'outils de développement essentiels.

Il en va de même pour SQL Server, avec une complication : l'accès à certaines vues système ne peut pas être accordé directement, mais ce n'est qu'une partie du rôle de base de données SYSADMIN , et ce rôle ne doit jamais être accordé en dehors de l'équipe DBA. Cela peut être résolu (et devrait être résolu dans le cas de la migration d'un projet d'Oracle vers SQL Server) en créant des vues et des procédures stockées qui s'exécutent sous les privilèges SYSADMIN mais sont accessibles aux utilisateurs non-DBA. C'est le travail du DBA de développement à faire lorsqu'un nouvel environnement de développement SQL Server est configuré.

La protection des données est l'une des principales responsabilités d'un DBA. Malgré cela, il est assez courant que les équipes de développement aient un accès complet aux données de production non filtrées pour permettre le dépannage des tickets liés aux données. Ce sont les mêmes développeurs qui ont un accès limité à la structure de données - structure qui a été créée par eux ou pour eux en premier lieu.

Lorsque de bonnes relations de travail entre les équipes de développement et les équipes DBA sont établies, la création d'un bon processus de contrôle des modifications devient intuitive. Les spécificités et le défi de la gestion des changements côté base de données sont à la fois la rigidité et la fluidité d'une base de données - la structure est rigide, les données sont fluides.

Il arrive souvent que la gestion du changement sur la modification de la structure, c'est-à-dire sur le langage de définition de données, ou DDL, soit bien établie alors que les changements de données n'ont que peu ou pas de gestion du changement. La justification est simple - les données changent tout le temps.

Mais si nous regardons cela de plus près, nous verrons que dans n'importe quel système, toutes les données appartiennent à l'une des deux catégories suivantes : les données d'application et les données d'utilisateur.

Les données d'application sont un dictionnaire de données qui définit le comportement d'une application et sont aussi critiques pour ses processus que n'importe quel code d'application. Les modifications apportées à ces données doivent être soumises à des processus de contrôle des modifications stricts, comme pour toute autre modification d'application. Afin de créer de la transparence dans le processus de contrôle des changements pour les changements de données d'application, les données d'application et les données d'utilisateur doivent être explicitement séparées.

Dans Oracle, cela doit être fait en plaçant les données d'application et d'utilisateur chacune dans son propre schéma. Dans Microsoft SQL Server, cela devrait être fait en plaçant chacun dans un schéma séparé ou - bien mieux - dans une base de données séparée. Faire ces choix devrait faire partie de la planification de la migration : Oracle a une résolution de nom à deux niveaux (schéma/propriétaire - nom d'objet) tandis que SQL Server a une résolution de nom à trois niveaux (base de données - schéma/propriétaire - nom d'objet).

La séparation correcte des données de l'application et de l'utilisateur permet à une équipe DBA de répondre à sa deuxième préoccupation la plus importante : la sécurité des données de l'utilisateur. Comme les données des utilisateurs résident séparément, il sera très simple de mettre en œuvre une procédure de bris de glace pour l'accès aux données des utilisateurs en fonction des besoins.

Conclusion : Les processus de contrôle des changements sont critiques dans tout projet. En génie logiciel, la gestion du changement côté base de données est souvent négligée car les données sont perçues comme « trop fluides ». Mais c'est précisément parce que les données sont à la fois « fluides » et « persistantes » qu'un processus de contrôle des modifications bien conçu doit être la pierre angulaire d'une architecture d'environnement de base de données appropriée.

Sur l'utilisation des outils de migration de code

Les outils propriétaires standard, Oracle Migration Workbench et SQL Server Migration Assistant, peuvent être utiles dans les migrations de code. Mais ce qu'il faut prendre en compte, c'est la règle 80/20 : lorsque le code sera migré à 80 % correctement, la résolution des 20 % restants prendra 80 % de votre effort de migration.

Le plus grand risque dans l'utilisation des outils de migration est de loin la perception de la « solution miracle ». On pourrait être tenté de penser : « Cela fera l'affaire, et j'aurai juste besoin de faire un peu de nettoyage et de rangement. J'ai observé un projet qui a échoué en raison d'une telle attitude de l'équipe de conversion et de sa direction technique.

D'un autre côté, il m'a fallu quatre jours ouvrables pour accomplir la conversion de base d'un système Microsoft SQL Server 2008 de taille moyenne (environ 200 objets) en utilisant la fonctionnalité de remplacement en bloc de Notepad ++ comme outil d'édition principal.

Aucun des éléments de migration critiques que j'ai abordés jusqu'à présent ne peut être résolu par des outils de migration.

Bien sûr, utilisez des outils d'aide à la migration, mais n'oubliez pas qu'ils ne fournissent qu'une aide à la modification. Le texte de sortie résultant doit être révisé, modifié et, dans certains cas, réécrit pour devenir un code digne de la production.

Le développement d'outils d'intelligence artificielle pourrait remédier à ces lacunes des outils de migration à l'avenir, mais je m'attends à ce que les différences entre les bases de données s'estompent d'ici là et que tout processus de migration lui-même devienne inutile. Donc, tant que ces types de projets seront nécessaires, nous devrons le faire à l'ancienne, en utilisant l'intelligence humaine à l'ancienne.

Conclusion : L'utilisation d'outils d'aide à la migration est utile mais ce n'est pas une « solution miracle », et tout projet de conversion nécessite toujours un examen détaillé des points ci-dessus.

Migrations Oracle/SQL Server : regardez toujours de plus près

Oracle et Microsoft SQL Server sont les deux plates-formes RDBMS les plus répandues dans l'environnement de l'entreprise. Les deux sont conformes à la norme ANSI SQL et de petits segments de code peuvent être déplacés avec très peu de modifications, voire tels quels.

Cette similitude donne l'impression trompeuse que la migration entre les deux plates-formes est une tâche simple et directe et que la même application peut facilement être adoptée d'un back-end RDBMS à un autre.

En pratique, de telles migrations de plateformes sont loin d'être anodines et doivent prendre en compte les subtilités du fonctionnement interne de chaque plateforme et, surtout, la manière dont elles implémentent le support de l'élément le plus critique de la gestion des données : les transactions.

Bien que j'ai couvert deux plates-formes RDBMS qui sont au cœur de mon expertise, le même avertissement - "semblable ne signifie pas que cela fonctionne de la même manière" - doit être appliqué au transfert de code entre tout autre système de gestion de base de données compatible SQL. Et dans tous les cas, le premier point d'attention doit être sur la façon dont la mise en œuvre de la gestion des transactions diffère entre les plateformes source et cible.