Voir le flux RSS

thtioxine

Passer de MS Access à Sql Server , quelles phases ?

Noter ce billet
par , 04/08/2020 à 14h41 (226 Affichages)
NB : L'idée de base est d'utiliser MS ACCESS avec une base SQL et non de migrer TOUTE l'application en un autre language.

But
Le but de ce billet n’est pas de donner une recette infaillible quant à la migration d’une application Access vers une application Sql. En effet, chaque cas est différent et nécessite une analyse particulière.
Le but en est de donner des pistes, libre à vous de les suivre ou non.

Un petit test pour asseoir la réflexion
J’ai calculé le tps de réponse d’un ordre sql en fct de la façon d’y accéder

La base se trouve sur le réseau et le frontal sur mon poste (table liée)
1er test : select * from xxxxx et affichage des 100eres REF (Uniquement la colonne REF)
Acces => 31 sec
Odbc => 23 Sec
OleDB (Sql Server) => 10 Sec

2eme Test : update xxxx set type = ‘UDT’ where type is null (Maj attendue de 360000 enreg)
Access => Au bout d’un quart d’heure, toujours pas de réponse (Break)
Odbc => Idem (Break)
OleDb => 3 Sec

De ce test, on en déduit que le passage par les tables liées n’est jamais concluant.
D’autre part, si ODBC s’avère une solution possible pour des tables de petites tailles, ce système ne convient pas aux gros traitements.

La conclusion est évidente, soit on évite Access totalement, et donc les tables liées, soit on ne touche à rien. [Ceci dit, comme tout ce qui suivra ci-dessous, je n’ai pas la science infuse et il est possible de voir des avantages à passer par des tables liées, ne serait-ce que pour préparer le futur].

Les solutions évidentes
Sans analyse particulière, on pense immédiatement à 2 solutions.
A) Les tables liées dont on a vu que cette solution est à éviter, sauf, peut-être, dans l’idée de préparer le futur (on verra plus tard, que même dans cet optique, ce n’est pas une bonne idée)
B) Reprise sans modification du modèle (On se contente de réécrire les requêtes)
C’est un pari que l’on fait. Si ça réussit, on aura économisé une phase d’analyse qui peut être longue et une phase d’optimisation qui, elle aussi, peut être coûteuse.
Par contre, si ça rate, tout le travail fait ne pourra être repris et le temps passé sera donc perdu.
Enfin, dans les 2 cas, on est incapable de fournir un chiffrage réaliste et l’on risque de se retrouver avec une appli bancale au temps de réponse décevant compte tenu du travail engagé.

Il est donc important de ne pas se précipiter sur ces solutions « évidentes » qui se transforment, in fine, en piège.

On ne va donc pas parler plus avant de ces solutions mais développer les étapes à suivre pour obtenir une idée claire du travail à fournir, des gains espérés et du coût final pour l’utilisateur.

Le phasage
Une migration bien conduite passe par de nombreuses phases, certaine facultative, d’autre recommandée.
La première de celle-ci est la pertinence. De la réponse à cette question dépend le reste.

La pertinence.
Dans tout projet, on ne consomme pas les ressources dans le vide sans espoir de retour. Il est donc impératif de savoir si « cela vaut le coup ». Pour ce faire, il faut d’abord savoir quel est le retour attendu (Financier, Humain, Politique, …). En effet, suivant le type de retour, la façon d’aborder le sujet sera différente. Si le retour est politique (on cherche à obliger un client, obtenir une promotion, montrer des capacités de faire, …), le coût n’est pas une variable impérative, juste un indicateur. Inversement, si il s’agit de rajeunir une appli vieillissante, d’en assurer la maintenance,… , le coût devra être couvert par les gains liés à la migration en question.
Cette phase est complexe et l’expérience de la maintenance est un prérequis.
Comme on n’entre pas dans le détail, il faut « sentir le truc ». On s’appuiera sur des éléments objectifs (Nombre de formulaires, de rapports, de requêtes, de tables (liées et non liées),…) et des éléments subjectifs (des tables ont beaucoup de colonnes, le nombre d’index semble faible, on trouve pas mal de liste déroulante, il semble y avoir pas mal d’update, ….)
On fera préciser aux utilisateurs les formulaires et rapports qui sont utilisés et ceux qui pourront être abandonnés. Dans une vieille appli, il y a souvent de nombreuses fonctions, qui, en leur temps ont eu une utilité, utilité qui a depuis disparue. Il est important de ne pas prendre en compte celles-ci dans le pré chiffrage.
Ensuite c’est l’expérience qui va parler et permettre d’obtenir non pas un chiffrage mais une estimation.
Cette phase se terminera alors par une présentation des résultats permettant au client de mettre un GO/NO GO.

Rappel : Normalement, ce n’est pas à l’IT de dire si le projet est pertinent en termes de ROI. C’est au client qui, au vu des évos probables, du nombre de personnes concernés, du départ ou non du responsable de l’appli, du temps perdu à l’utilisation, … de dire, si oui ou non, cela vaut le coup.
Pour l’IT, une migration bien menée amènera toujours un plus important pour une reprise éventuelle, ne serait-ce que par la connaissance de l’application.

La 1ere phase passée et le GO obtenu, il s’agit maintenant de chiffrer au plus près le travail à fournir. A la fin de cette phase, le client devra de nouveau émettre un GO/NO GO. Une fois la décision prise, celle-ci est irrévocable compte tenu que l’IT va alors engager les moyens nécessaires.
J’insiste ici sur la nécessité que la 1ere phase soit accomplie par un agent expérimenté. Il serait dommageable que de quelques jours, l’on passe à quelques mois ou inversement.

De Macro à Micro, la phase 2
Cette phase va permettre d’ asseoir un chiffrage réaliste, de déterminer les points durs et ceux qui nécessiteront, de la part de l’utilisateur, des sacrifices ou des adaptations. (SQL n’est pas Access et certaines fonctions pourraient, dans la migration, disparaîtrent).
On va d’abord considérer les tables liées. En effet, sauf exception, vous n’aurez pas la main sur ces tables et donc aucune possibilité d’optimisation. Si les problèmes les plus graves se situent à ce niveau et que ces tables sont des tables ACCESS, les chances d’obtenir une application « optimisée » sont quasi nulles. (Dans le cas où nous serions en SQL, il sera possible d’en obtenir l’url)
Ensuite, on examinera les différents objets qui composent l’appli (formulaire, rapport, requête, code, ..) afin d’en déduire le modèle de donnée « idéal » et recenser ceux qui nécessiteront un examen détaillé en vue d’une adaptation.
Attention : On parle bien ici d’obtenir un MDD cohérent avec les requêtes utilisées dans les fonctions et non d’obtenir le MDD existant. L’idée étant d’évaluer la divergence existante. Plus celle-ci sera importante, plus la migration coûtera chere, pour finir, à l’extrême, à se transformer en une refonte complète.
Nous allons donc chiffrer
A) L’adaptation du MDD (Refonte des tables, copie des données)
B) L’adaptation des objets à ce nouveau modèle
Dans un second temps, on va chiffrer l’adaptation de l’application aux bonnes pratiques SQL
A) Pas de liste déroulante non bornée (Ceci peut amener à des modifications du formulaire pour permettre à l’utilisateur de choisir les 1ers caractères recherchés, par exemple)
B) Pas de mise à jour automatique. L’utilisateur devra valider le formulaire. En effet, ACCESS met à jour les champs au fur et à mesure que ceux-ci sont renseignés. Un tel comportement est très coûteux et très dangereux (rollback transaction). Il est donc à proscrire totalement.
C) Ajout des « on error » en cas d’erreur de base de données afin de pouvoir gérer les transactions (Commit et Rollback)
D) Remplacement des boucles insert ou update par des bulk si possible
En final, l’IT doit obtenir un plan de migration et le coût de celui-ci en charge (Attention à ajouter 15% afin de faire face aux impondérables). Le délai devra être estimé en fonction des ressources que l’on compte y mettre (une ou plusieurs, prise de congé, …).Si il semble nécessaire de mettre plusieurs ressources, il faut absolument prévoir, dans les charges et les délais, des mises à niveau de celui qui ne sera pas le dev principal. Ceci est très important car un second non à niveau ne sert à rien et peut même être source d’erreurs.

La fin de cette phase génère une documentation (plan de migration) qui devra être fourni au client.
Celui-ci devra alors donner son accord au plan présenté.

La migration des tables, la phase 3 :
Cette phase est la suite normale de la phase 2. Elle est généralement présente dans toutes migrations car il est très improbable que le MDD « idéal » et le MDD physique coïncide.
Durant cette phase, nous allons
A) Créer la base
B) Créer les tables
C) Créer les index
D) Créer les views
E) Migrer les données
La création de la base est une opération triviale (Il suffit de suivre l’assistant) mais dans laquelle il est nécessaire de ne pas oublier de donner un nom au schéma (Sous espace de la base, celui-ci isole les tables. Une base peut ainsi contenir plusieurs schémas). De plus, lors de la création, il sera demandé la collation. Cette donnée est importante car elle influe sur le type de caractères que pourra contenir, par défaut, une table, et surtout sur le tri. Ainsi, une collation CI (Case Insensitive) ne tiendra pas compte, lors du tri des majuscules ou des minuscules, à l’inverse d’une collation CS (Case Sensitive).

La création des tables devra être faite en tenant compte, pour les champs, de certains impératifs.
Normalement, on ne créé pas de nom de champ contenant des espaces ou des accents.
Le type de champ devra toujours être du varchar lorsque l’on a des champs texte.
Les dates sont de type Date/heure et le type TimeStamp ne doit pas être utilisé sauf pour noter le timestamp de maj.
Les champs numériques sont de type int ou float.
Enfin les booléens sont de type int.
Le null doit être géré et ne doit pas être remplacé par des espaces.
Les clés primaires ne sont jamais obligatoires sauf dans 2 cas
A) Il est impératif de dissocier les individus (pour des maj, par exemple)
B) Il y a un tri par défaut

Un index est nécessaire quand les champs sur lequel il devra porter sont des données d’entrée des recherches de sorte que celle-ci ne se fasse pas « full table scan » (ce qui dégrade fortement les temps de réponse).

On établira des views au plus proche de la réponse attendue car celles-ci sont maintenues par le moteur SQL et permettent un développement plus simple (la complexité est remonté à SQL et non à l’application) et des temps de réponse plus rapide (SQL n’a pas à parser l’ordre puisque celui-ci est intégré aux tables). Enfin les view permettent de tester, in vivo, la rapidité de la réponse et d’intervenir, donc, sur les tables, l’index, l’ordre ….

Enfin on prépare la migration des données en utilisant une de ces techniques.
A) On descend, dans un fichier, les tables ACCESS que l’on recharge, par BULK dans SQL (C’est la meilleure technique)
B) On utilise un utilitaire en JAVA qui va lire les tables ACCESS et charger les tables SQL (C’est la pire des solutions car cela nécessite un composant JDBC comprenant ACCESS)
C) On utilise ACCESS pour charger les tables SQL (Cela peut être une bonne solution pour préparer ACCESS à OLEDB, rendre apparent, dès la migration, des problèmes de conversion et donner de l’expérience aux dev)

Il est courant que, lors de la migration, des problèmes sur le MDD apparaissent. Ceux-ci sont de deux natures.
A) Le type de donnée ne correspond pas (des dates qui en fait sont du texte, des numériques pas toujours numérique, …)
B) Des champs qui sont dans le MDD d’origine et ne sont pas retrouvés en final.
Pour le 1er point, il est plus sage de se rapprocher du MDD d’origine et donc, de passer le type SQL en varchar
Pour le second point, deux positions possibles
A) On vérifie si le champ en question est réellement absent ou si c’est un oubli lors de l’analyse.
B) Lors de la phase 1, nous avons éliminé certaines fonctions, ce qui peut provoquer l’absence du champ. Du coup, on verra si il manque réellement plus tard et on migre sans en attendant.

Cette phase est extrêmement importante et c’est sur celle-ci que va s’appuyer le reste du développement. Si la rigueur ou/et la connaissance n’est pas au rendez-vous, le final sera une appli lente, instable, et décevante pour les utilisateurs.
Il faut donc que la personne qui sera en charge de cette partie ait une bonne expérience des bases de données et de ce qui se fait ou non en SQL.
Un point d’avancement sera fait avec le client sinon déjà prévu.

La transformation de l’application, la PHASE 4 (Remarquez la majuscule !!) :
Nous avons mis l’échafaudage en place, refait les plans, reconstruit les murs et la charpente. Il est temps de sortir le placo, le carrelage, l’enduit et la peinture.

PHASE 4 – Sous Phase 1 – Les formulaires
Ben oui, faut bien commencer par quelque chose. Si on choisit les formulaires pour démarrer, c’est qu’il y a 2 avantages.
A) On prépare les entrées / sorties, et donc la possibilité de tester simplement par comparaison avec l’appli originale.
B) C’est bien plus simple pour suivre un cheminement logique dans la migration.

Et dans les formulaires, je commence par ceux qui affichent des données (donc output). Pourquoi ?
C’est en général le plus simple (on ne se heurte pas au pb de maj)
Il y a le plus de travail en ACCESS (Bornage des listes, ajout de bouton, tri,….), mais c’est ce qui posera le moins de pb car ACCESS est très puissant dans tout ce qui est ergonomie.
La migration SQL est la plus simple à mettre en œuvre (encore que …).
C’est simple à tester (il suffit de vérifier que le retour dans l’appli originale est le même et que les gains en temps de réponse sont bien la).

On s’attaquera, ensuite aux formulaires de MAJ.
De même que pour les affichages, on commencera par ajouter les objets nécessaires, en particulier les boutons SAVE et CANCEL.
Sur ces deux boutons, on codera la maj qui devra IMPÉRATIVEMENT être de type transactionnelle.
(C’est-à-dire supporter le Rollback). C’est cet impératif qui sera le plus compliqué à résoudre. En effet, il faut déterminer quand commiter la transaction, et donc ou ouvrir et fermer les connections de la base. A la différence d’une fonction de recherche ou l’on ouvre au début et fermons à la fin, une transaction peut couvrir plusieurs modules et la fermeture, en plein milieu, pourrait provoquer des bugs complexes à comprendre et à corriger.
Les tests consisteront à vérifier, par les formulaires d’affichage MAIS aussi, dans les tables, que les données sont bien stockées.

PHASE 4 – Sous Phase 2 – Le code non relié (Utilitaires, fonctions transverses, …)
Là, pas de chichi, il va falloir descendre le code et corriger au fur et à mesure.

Travail commun (IMPORTANT).
Dans tous les cas, le NULL devra être géré, le END (qui provoque une perte des infos globales) évité et les erreurs interceptées (Sous peine de voir commité une transaction en erreur) et gérées (On remonte les infos nécessaires à la maintenance comme le nom du module, celui de la fonction, le type et le message d’erreur).

En final de cette phase, on doit obtenir une appli testable par le client.
Il est temps de provoquer une réunion pour parler de la recette.

Envoyer le billet « Passer de MS Access à Sql Server , quelles phases ? » dans le blog Viadeo Envoyer le billet « Passer de MS Access à Sql Server , quelles phases ? » dans le blog Twitter Envoyer le billet « Passer de MS Access à Sql Server , quelles phases ? » dans le blog Google Envoyer le billet « Passer de MS Access à Sql Server , quelles phases ? » dans le blog Facebook Envoyer le billet « Passer de MS Access à Sql Server , quelles phases ? » dans le blog Digg Envoyer le billet « Passer de MS Access à Sql Server , quelles phases ? » dans le blog Delicious Envoyer le billet « Passer de MS Access à Sql Server , quelles phases ? » dans le blog MySpace Envoyer le billet « Passer de MS Access à Sql Server , quelles phases ? » dans le blog Yahoo

Commentaires

  1. Avatar de harpyopsis
    • |
    • permalink
    Bonjour Tioxine,

    Intéressant billet sur un sujet qui me donne mal à la tête depuis des semaines, alors que je suis complètement isolé pour tout faire. J'ai énormément de questions...

    Soit une BD Access 2007 de 320 tables, qui tourne depuis 5 ans.

    Quand je migre ces tables vers MS SQL Serveur avec Migration Assistant, je les retrouve affublées d'un préfixe "dbo."

    Comme ces tables sur Server vont être réimportées (liées) dans la même front-end Access d'origine, ce changement de noms impromptu ("Jarnicoton") va tout foutre en l'air !

    Je ne comprends pas si c'est Migr. Assist ou bien SSMS qui génère ce préfixe dbo.

    Est-il justifié, ou serait-ce une erreur, de s'en débarrasser immédiatement avant de s'occuper des PK-FK ???

    (OK, je viens de voir sur un forum anglais que dbo c'est "Data Base Owner", que c'est généré par SQL Server pour faciliter les recherches dans de très grosses DB, et que la grande majorité des programmeurs s'en débarrassent sans complexe)

    Comment faire ? Je suis complètement novice en SQL sur des objets avec MSSQL.

    Je viens de voir

    EXEC sp_rename

    et

    EXEC sp_MSforeachtable

    Comment créer et exécuter un loop avec ça dans SSMS ???

    Je suis au désespoir....

    Je viend de trouver ceci sur Stackoverflow, qu'en pensez-vous ???

    declare @oq nvarchar(max) = N'prefix_'
    declare @cmd nvarchar(max)
    select @cmd = a from (
    select 'EXEC sp_rename ''' + TABLE_NAME + ''', ''' + REPLACE(TABLE_NAME, @oq, N'') + ''' '
    from INFORMATION_SCHEMA.TABLES
    for xml path('')
    ) t(a)
    exec sp_executesql @cmd
    ............ Non, décidément aucun code SQL ne marche pour supprimer le préfixe dbo.... Je viens d'en essayer une demi-douzaine, alors que le temps presse tellement...

    .............Mais je viens de voir qu'il serait possible de le maintenir dans les tables su SQL Server, mais de renommer ces mêmes tables dans Access une fois reliées...

    Je ne sais que penser.....

    Merci pour votre éventuelle réponse !

    phil
    Mis à jour 11/09/2020 à 12h11 par harpyopsis
  2. Avatar de thtioxine
    • |
    • permalink
    Bonjour
    Comme dit dans mon billet, quitte à passer à sql autant le faire vraiment, la notion de table liée n'étant généralement pas une bonne idée car c'est toujours ACCESS qui gère.
    Ça ne coûte pas plus cher de faire de l'OLEDB et les gains en temps de réponse, quand c'est bien fait, sont faramineux.

    Ceci dit.
    Une table SQL se trouve systématiquement dans un schéma. Par défaut, le schéma est dbo, d'ou votre préfix.
    Quand vous reliez vos tables, celles-ci sont en effet préfixé par dbo, d'ou un nom du type dbo_xxxxx. Ne cherchez pas à vous en débarrasser du coté sql, ce n'est pas possible. Il faut le faire du coté MS ACCESS en renommant les tables. Voici un lien expliquant comment faire.

    https://www.microsoftaccessexpert.co...DBOPrefix.aspx

    Mais une fois de plus, prenez le temps de faire un rapide chiffrage d'un passage à SQL par OLEDB.

    A+



    Citation Envoyé par harpyopsis
    Bonjour Tioxine,

    Intéressant billet sur un sujet qui me donne mal à la tête depuis des semaines, alors que je suis complètement isolé pour tout faire. J'ai énormément de questions...

    Soit une BD Access 2007 de 320 tables, qui tourne depuis 5 ans.

    Quand je migre ces tables vers MS SQL Serveur avec Migration Assistant, je les retrouve affublées d'un préfixe "dbo."

    Comme ces tables sur Server vont être réimportées (liées) dans la même front-end Access d'origine, ce changement de noms impromptu ("Jarnicoton") va tout foutre en l'air !

    Je ne comprends pas si c'est Migr. Assist ou bien SSMS qui génère ce préfixe dbo.

    Est-il justifié, ou serait-ce une erreur, de s'en débarrasser immédiatement avant de s'occuper des PK-FK ???

    (OK, je viens de voir sur un forum anglais que dbo c'est "Data Base Owner", que c'est généré par SQL Server pour faciliter les recherches dans de très grosses DB, et que la grande majorité des programmeurs s'en débarrassent sans complexe)

    Comment faire ? Je suis complètement novice en SQL sur des objets avec MSSQL.

    Je viens de voir

    EXEC sp_rename

    et

    EXEC sp_MSforeachtable

    Comment créer et exécuter un loop avec ça dans SSMS ???

    Je suis au désespoir....

    Je viend de trouver ceci sur Stackoverflow, qu'en pensez-vous ???



    ............ Non, décidément aucun code SQL ne marche pour supprimer le préfixe dbo.... Je viens d'en essayer une demi-douzaine, alors que le temps presse tellement...

    .............Mais je viens de voir qu'il serait possible de le maintenir dans les tables su SQL Server, mais de renommer ces mêmes tables dans Access une fois reliées...

    Je ne sais que penser.....

    Merci pour votre éventuelle réponse !

    phil