Passer directement au contenu principal
Migrer une base de données Access vers SQL Server

Migrer une base de données Access vers SQL Server

Nous avons toutes des limites et une base de données Access ne correspond pas à une exception. Par exemple, une base de données Access a une limite de taille de 2 Go et ne peut pas prendre en charge plus de 255 utilisateurs simultanés. Lorsque le moment est écoulé pour que votre base de données Access passe au niveau supérieur, vous pouvez migrer vers SQL Server. SQL Server (en local ou sur le Cloud Azure) prend en charge de grandes quantités de données, des utilisateurs plus concurrents et une plus grande capacité que le moteur de base de données JET/ACE. Ce guide vous permet de démarrer de manière transparente votre serveur SQL Server, de préserver les solutions frontales d’accès que vous avez créées et de vous aider à vous encourager à utiliser Access pour des solutions de base de données futures. L’Assistant Migration SQL Server a été supprimé d’Access dans Access 2013, alors vous pouvez désormais utiliser l’Assistant de migration Microsoft SQL Server (SSMA). Pour effectuer une migration réussie, suivez les étapes ci-dessous.

Étapes de la migration de base de données vers SQL Server

Avant de commencer

Les sections suivantes fournissent des informations d’arrière-plan et d’autres informations pour vous aider à commencer.

À propos des bases de données fractionnées

Tous les objets de base de données Access peuvent se trouver dans un fichier de base de données ou être stockés dans deux fichiers de base de données: une base de données frontale et une base de données principale. Il s’agit d’un fractionnement de la base de données , conçu pour faciliter le partage dans un environnement réseau. Le fichier de base de données principal ne doit contenir que des tables et des relations. Le fichier frontal ne doit contenir que tous les autres objets, tels que des formulaires, des États, des requêtes, des macros, des modules VBA et des tables liées à la base de données principale. Lors de la migration d’une base de données Access, celle-ci est semblable à une base de données fractionnée dans SQL Server, qui fait office de nouveau serveur principal pour les données qui se trouvent actuellement sur un serveur.

Par conséquent, vous pouvez toujours conserver la base de données Access frontale avec des tables liées aux tables SQL Server. En fait, vous pouvez tirer parti du développement d’applications rapide qu’une base de données Access fournit, ainsi que de l’évolutivité de SQL Server.

Avantages de SQL Server

Vous avez encore besoin d’une migration vers SQL Server? Vous trouverez ci-dessous des avantages supplémentaires à prendre en considération:

  • Utilisateurs supplémentaires concurrents    SQL Server peut gérer de nombreux utilisateurs simultanés plutôt que d’accéder au système et réduire la quantité de mémoire requise lors de l’ajout d’utilisateurs.

  • Disponibilité accrue    Avec SQL Server, vous pouvez effectuer une sauvegarde dynamique, incrémentielle ou complète, de la base de données pendant qu’elle est utilisée. Ainsi, vous n’êtes pas obligé de forcer les utilisateurs à quitter la base de données pour sauvegarder les données.

  • Performances et extensibilité élevées    En règle générale, la base de données SQL Server fonctionne mieux qu’une base de données Access, en particulier avec une base de données de 2 téraoctets. Par ailleurs, SQL Server traite les requêtes beaucoup plus rapidement et efficacement en traitant les requêtes en parallèle en utilisant plusieurs threads natifs au sein d’un même processus pour gérer les demandes des utilisateurs.

  • Sécurité améliorée    À l’aide d’une connexion approuvée, SQL Server s’intègre à la sécurité du système Windows pour fournir un accès unique et intégré au réseau et à la base de données, à l’aide des meilleurs systèmes de sécurité. Ainsi, il est beaucoup plus facile d’administrer des schémas de sécurité complexes. SQL Server est le stockage idéal des informations sensibles telles que des numéros de sécurité sociale, des données de cartes de crédit et des adresses confidentielles.

  • Récupérabilité immédiate    Si le système d’exploitation cesse de fonctionner ou se bloque, SQL Server peut récupérer automatiquement la base de données dans un état cohérent en quelques minutes et sans intervention de l’administrateur de la base de données.

  • Utilisation du VPN    L’accès et les réseaux privés virtuels (VPN) ne sont pas accessibles. Mais avec SQL Server, les utilisateurs distants peuvent utiliser la base de données frontale Access sur un ordinateur de bureau et le serveur principal SQL Server situé derrière le pare-feu VPN.

  • Azure SQL Server    Outre les avantages de SQL Server, offre une évolutivité dynamique sans temps d’arrêt, optimisation intelligente, évolutivité globale et disponibilité, élimination des coûts matériels et administration réduite.

Choisir la meilleure option Azure SQL Server

Si vous effectuez une migration vers Azure SQL Server, trois options s’offrent à vous:

  • Une base de données    Cette option dispose de son propre ensemble de ressources gérées par le biais d’un serveur de base de données SQL. Une base de données unique est semblable à une base de données incluse dans SQL Server. Vous pouvez également ajouter une liste élastique, qui est une collection de bases de données avec un ensemble partagé de ressources gérées via le serveur de base de données SQL. Les fonctionnalités SQL Server les plus fréquemment utilisées sont disponibles avec des sauvegardes, les correctifs et la récupération intégrés. Mais il n’y a aucun temps garanti de maintenance exact et la migration à partir de SQL Server peut être difficile.

  • Instance gérée    Cette option est une collection de bases de données système et d’utilisateurs disposant d’un ensemble de ressources partagées. Une instance gérée est comme une instance de la base de données SQL Server qui est hautement compatible avec SQL Server en local. Une instance gérée a des sauvegardes intégrées, des correctifs, la récupération et est facile à migrer à partir de SQL Server. Toutefois, il existe un petit nombre de fonctionnalités SQL Server qui ne sont pas disponibles et ne garantissent pas de durée de maintenance exacte.

  • Machine virtuelle Azure    Cette option vous permet d’exécuter SQL Server à l’intérieur d’une machine virtuelle dans le Cloud Azure. Vous contrôlez totalement le moteur SQL Server et un chemin de migration simple. Néanmoins, vous devez gérer vos sauvegardes, les correctifs et la récupération.

Pour plus d’informations, reportez-vous à la rubrique choix de votre chemin de migration de base de données vers Azure et Sélectionnez l’option SQL Server appropriée dans Azure.

Premiers pas

Quelques problèmes peuvent vous être utiles pour simplifier le processus de migration avant d’exécuter SSMA:

  • Ajouter des index de table et des clés primaires    Vérifiez que chaque table Access comporte un index et une clé primaire. SQL Server nécessite que toutes les tables aient au moins un index et nécessitent une table liée pour avoir une clé primaire si la table peut être mise à jour.

  • Vérifier les relations entre les clés primaire et étrangère    Assurez-vous que ces relations sont basées sur des champs de tailles et de types de données cohérents. SQL Server ne prend pas en charge les colonnes jointes présentant des tailles et des types de données différents dans des contraintes de clé étrangère.

  • Supprimer la colonne de pièces jointes    SSMA ne déplace pas les tables contenant la colonne de pièces jointes.

Avant d’exécuter SSMA, prenez les premières étapes suivantes.

  1. Fermez la base de données Access.

  2. Assurez-vous que les utilisateurs actuels connectés à la base de données ferment également la base de données.

  3. Si la base de données est au format de fichier. mdb, supprimez la sécurité au niveau utilisateur.

  4. Sauvegardez votre base de données. Pour plus d’informations, voir protéger vos données avec les processus de sauvegarde et de restauration.

Conseil    Envisagez d’installer Microsoft SQL Server Express Edition sur votre ordinateur de bureau qui prend en charge jusqu’à 10 Go et est un moyen gratuit et plus facile d’exécuter et de vérifier la migration. Lorsque vous vous connectez, utilisez l’instance de base de données.

Conseil    Dans la mesure du possible, utilisez une version autonome d’Access. Si vous ne pouvez utiliser que Office 365, utilisez le moteur de base de données Access 2010 pour migrer votre base de données Access lors de l’utilisation de SSMA. Pour plus d’informations, voir moteur de base de données Microsoft Access 2010redistribuable.

Exécuter SSMA

Microsoft fournit l' Assistant de migration Microsoft SQL Server (SSMA) pour faciliter la migration. SSMA transfère essentiellement des tables et des requêtes sélection sans paramètres. Les formulaires, États, macros et modules VBA ne sont pas convertis. L’Explorateur de métadonnées SQL Server affiche les objets de base de données Access et les objets SQL Server vous permettant de passer en revue le contenu actuel des deux bases de données. Ces deux connexions sont enregistrées dans votre fichier de migration si vous décidez de transférer des objets supplémentaires à l’avenir.

Remarque    Le processus de migration peut prendre un certain temps en fonction de la taille de vos objets de base de données et de la quantité de données à transférer.

  1. Pour migrer une base de données à l’aide de SSMA, commencez par Télécharger et installer le logiciel en double-cliquant sur le fichier MSI téléchargé. Vérifiez que vous avez installé la version 32 ou 64 appropriée de votre ordinateur.

  2. Après avoir installé SSMA, ouvrez-le sur votre ordinateur de bureau, de préférence à partir de l’ordinateur doté du fichier de base de données Access.

    Vous pouvez aussi l’ouvrir sur un ordinateur qui a accès à la base de données Access à partir du réseau dans un dossier partagé.

  3. Suivez les instructions de début de SSMA pour fournir des informations de base telles que l’emplacement SQL Server, la base de données et les objets Access pour la migration, les informations de connexion et si vous voulez créer des tables liées.

  4. Si vous effectuez une migration vers SQL Server 2016 ou une version ultérieure et que vous voulez mettre à jour une table liée, ajoutez une colonne rowversion en sélectionnant outils de révision > paramètres du projet > général.

    Le champ rowversion permet d’éviter les conflits d’enregistrements. Access utilise ce champ rowversion dans une table liée SQL Server pour déterminer la date de la dernière mise à jour de l’enregistrement. Par ailleurs, si vous ajoutez le champ rowversion à une requête, Access l’utilise pour resélectionner la ligne après une opération de mise à jour. Cela améliore l’efficacité en permettant d’éviter les erreurs de conflits d’écriture et les scénarios de suppression d’enregistrements qui peuvent se produire quand Access détecte des résultats différents de la soumission d’origine, par exemple pour des types de données de nombre à virgule flottante et des déclencheurs qui sont modifiés. celles. Néanmoins, évitez d’utiliser le champ rowversion dans des formulaires, des États ou du code VBA. Pour plus d’informations, voir rowversion.

    Remarque    Évitez d’enbrouiller rowversion avec des horodatages. Même si le mot clé timestamp est un synonyme de rowversion dans SQL Server, vous ne pouvez pas utiliser rowversion comme moyen de horodater une entrée de données.

  5. Pour définir des types de données précis, sélectionnez outils de révision > paramètres du projet > mappage de type. Par exemple, si vous stockez uniquement du texte anglais, vous pouvez utiliser la donnée varchar au lieu du type de données nvarchar .

Convertir des objets

SSMA convertit les objets Access en objets SQL Server, mais ne copie pas les objets immédiatement. SSMA fournit une liste des objets suivants à migrer pour vous permettre de les déplacer vers une base de données SQL Server:

  • Tableaux et colonnes

  • Sélectionner des requêtes sans paramètres

  • Clés primaires et clés étrangères

  • Index et valeurs par défaut

  • Contraintes de vérification (autorisant la propriété de colonne de longueur zéro, la règle de validation de colonne, la validation de table)

Il est recommandé d’utiliser le rapport d’évaluation SSMA, qui affiche les résultats de la conversion, y compris les erreurs, avertissements, messages d’information, estimations de temps pour effectuer la migration, et les étapes de correction d’erreur individuelles à prendre avant de déplacer réellement le objets.

La conversion d’objets de base de données permet d’utiliser les définitions d’objet des métadonnées d’accès, de les convertir en syntaxe Transact-SQL (T-SQL)équivalente, puis de charger ces informations dans le projet. Vous pouvez alors afficher les objets SQL Server ou SQL Azure et leurs propriétés en utilisant SQL Server ou l’Explorateur de métadonnées SQL Azure.

Pour convertir, charger et migrer des objets vers SQL Server, suivez ce guide.

Conseil    Après avoir migré votre base de données Access, enregistrez le fichier de projet pour une utilisation ultérieure afin de pouvoir migrer à nouveau vos données à des fins de test ou de migration finale.

Lier des tables

Envisagez d’installer la dernière version des pilotes SQL Server OLE DB et ODBC au lieu d’utiliser les pilotes natifs SQL Server intégrés à Windows. Les pilotes les plus récents ne sont pas seulement les plus récents, mais ils prennent en charge les nouvelles fonctionnalités d’Azure SQL qui ne sont pas prises en charge par les anciens pilotes. Vous pouvez installer les pilotes sur chaque ordinateur sur lequel est utilisée la base de données convertie. Pour plus d’informations, reportez-vous à la rubrique pilote Microsoft OLE DB 18 pour SQL Server et Microsoft ODBC Driver 17 pour SQL Server.

Après avoir migré les tables Access, vous pouvez créer des liens vers les tables de SQL Server qui héberge désormais vos données. Le fait de créer des liens directement à partir d’Access vous permet également de consulter facilement vos données au lieu d’utiliser des outils de gestion SQL Server plus complexes.  Vous pouvez interroger et modifier des données liées en fonction des autorisations configurées par l’administrateur de base de données SQL Server.

Remarque    Si vous créez une source de données ODBC lorsque vous créez un lien vers votre base de données SQL Server lors du processus de liaison, vous pouvez créer le même DSN sur tous les ordinateurs utilisant la nouvelle application ou par programmation à l’aide de la chaîne de connexion stockée dans le fichier DSN.

Pour plus d’informations, voir lier ou importer des données à partir d’une base de données Azure SQL Server et importer ou attacher des données dans une base de données SQL Server.

Extrem   N’oubliez pas d’utiliser le gestionnaire d’attaches dans Access pour actualiser et relier facilement les tableaux. Pour plus d’informations, voir gérer les tables liées.

Tester et réviser

Les sections suivantes décrivent les problèmes courants que vous pouvez rencontrer lors de la migration et la façon de les gérer.

Requêtes

Seules les requêtes sélection sont converties; Il n’y a pas de requêtes sélection qui prennent des paramètres. Certaines requêtes risquent de ne pas être entièrement converties et SSMA signale des erreurs de requête lors du processus de conversion. Vous pouvez modifier manuellement des objets qui ne sont pas convertis en utilisant la syntaxe T-SQL. Les erreurs de syntaxe risquent également de nécessiter une conversion manuelle des fonctions et des types de données spécifiques à l’accès vers des SQL Server. Pour plus d’informations, reportez-vous à comparaison d’Access SQL avec SQL Server TSQL.

Types de données

Access et SQL Server ont des types de données similaires, mais soyez attentif aux problèmes potentiels suivants.

Grand numéro    Le type de données grand nombre stocke une valeur numérique non monétaire et est compatible avec le type de données bigint SQL. Vous pouvez utiliser ce type de données pour calculer efficacement les grands nombres, mais il est nécessaire d’utiliser le format de fichier de base de données Access 16 (16.0.7812 ou version ultérieure). accdb et fonctionne mieux avec la version 64 bits d’Access. Pour plus d’informations, reportez-vous à utilisation du type de données grand nombre et Choisissez entre la version 64 bits ou 32 bits d’Office.

Oui/Non    Par défaut, une colonne accès oui/non est convertie en champ binaire SQL Server. Pour éviter le verrouillage d’enregistrement, Assurez-vous que le champ bit est défini pour interdire les valeurs NULL. DANS SSMA, vous pouvez sélectionner la colonne bit pour affecter la valeur non à la propriété Null interdit . Dans TSQL, utilisez les instructions Create table ou ALTER TABLE .

Date et heure    Il existe plusieurs éléments à prendre en compte pour la date et l’heure:

  • Si le niveau de compatibilité de la base de données est 130 (SQL Server 2016) ou version ultérieure, et qu’une table liée contient une ou plusieurs colonnes datetime ou datetime2, la table risque de renvoyer le message #deleted dans les résultats. Pour plus d’informations, reportez-vous à la rubrique accès à la table liée à SQL-Server renvoie #deleted.

  • Utilisez le type de données datetime2 dont la plage de dates est supérieure à DateTime.

  • Lors de l’interrogation de dates dans SQL Server, prenez en compte les heures et la date. Par exemple :

    • DateOrdered entre 1/1/19 et 1/31/19 n’inclut pas toutes les commandes.

    • DateOrdered entre 1/1/19 00:00:00 et 1/31/19 11:59:59 PM inclut toutes les commandes.

Pièce jointe   Le type de données pièce jointe enregistre un fichier dans la base de données Access. SQL Server vous permet de prendre en compte plusieurs options. Vous pouvez extraire les fichiers de la base de données Access, puis envisager de stocker les liens vers les fichiers dans votre base de données SQL Server. Vous pouvez également utiliser FILESTREAM, FileTables ou magasin d’objets BLOB distants (RBS) pour conserver les pièces jointes stockées dans la base de données SQL Server.

Lien hypertexte    Les tables Access possèdent des colonnes de liens hypertexte que SQL Server ne prend pas en charge. Par défaut, ces colonnes seront converties en colonnes nvarchar (max) dans SQL Server, mais vous pouvez personnaliser le mappage pour sélectionner un type de données plus petit. Dans votre solution Access, vous pouvez toujours utiliser le comportement du lien hypertexte dans les formulaires et États si vous définissez la propriété LIEN_HYPERTEXTE du contrôle sur la valeur true.

Champ à plusieurs valeurs    Le champ à plusieurs valeurs Access est converti en un champ ntext dans SQL Server qui contient l’ensemble de valeurs délimité. Étant donné que SQL Server ne prend pas en charge un type de données à plusieurs valeurs qui modélise une relation plusieurs-à-plusieurs, supplémentaires de conception et le travail de conversion peuvent être nécessaire.

Pour plus d’informations sur le mappage des types de données Access et SQL Server, voir comparer des types de données.

Remarque    Les champs à plusieurs valeurs ne sont pas convertis et ont été abandonnés dans Access 2010.

Pour plus d’informations, voir types de date et d’heure, types de chaîne et binaireet types numériques.

Visual Basic

Même si VBA n’est pas pris en charge par SQL Server, notez les problèmes possibles suivants:

Fonctions VBA dans les requêtes    Les requêtes Access prennent en charge les fonctions VBA sur des données dans une colonne de requête. Toutefois, les requêtes qui utilisent des fonctions VBA ne peuvent pas être exécutées sur SQL Server, de sorte que toutes les données demandées sont transmises à Microsoft Access pour traitement. Dans la plupart des cas, ces requêtes doivent être converties en requêtes directes.

Fonctions définies par l’utilisateur dans les requêtes    Les requêtes Microsoft Access prennent en charge l’utilisation des fonctions définies dans les modules VBA pour traiter les données qui leur sont transmises. Les requêtes peuvent être des requêtes autonomes, des instructions SQL dans des sources d’enregistrements de formulaire/état, des sources de données de zones de liste déroulante et de zones de liste sur les formulaires, États et champs de table, et les expressions de règle par défaut ou de validation. SQL Server ne peut pas exécuter les fonctions définies par l’utilisateur. Il est possible que vous ayez besoin de recréer manuellement ces fonctions et de les convertir en procédures stockées sur SQL Server.

Optimiser les performances

Pour l’instant, le moyen le plus important d’optimiser les performances de votre nouveau serveur SQL principal consiste à déterminer quand utiliser des requêtes locales ou distantes. Lorsque vous migrez vos données vers SQL Server, vous basculez également d’un serveur de fichiers vers un modèle de base de données client-serveur. Suivez les recommandations générales suivantes:

  • Exécuter des requêtes de petite taille et en lecture seule sur le client pour un accès rapide.

  • Exécutez des requêtes longues et en lecture/écriture sur le serveur pour tirer parti de la puissance de traitement accrue.

  • Réduisez le trafic réseau avec les filtres et l’agrégation pour transférer uniquement les données dont vous avez besoin.

Optimiser les performances dans le modèle de base de données du serveur client

Pour plus d’informations, voir créer une requête directe.

Vous trouverez ci-après des recommandations supplémentaires.

Placer la logique sur le serveur    Votre application peut également utiliser des affichages, des fonctions définies par l’utilisateur, des procédures stockées, des champs calculés et des déclencheurs pour centraliser et partager la logique d’application, les règles et les stratégies d’entreprise, les requêtes complexes, la validation de données et le code d’intégrité référentielle sur le serveur plutôt que sur le client. Posez-vous la question suivante: une requête ou une tâche à effectuer sur le serveur est-elle plus rapide et plus rapide? Enfin, testez chaque requête pour garantir des performances optimales.

Utiliser les affichages dans les formulaires et les États    Dans Access, procédez comme suit:

  • Pour les formulaires, utilisez un mode SQL pour un formulaire en lecture seule et un affichage indexé SQL pour un formulaire en lecture/écriture en tant que source d’enregistrement.

  • Pour les États, utilisez un mode SQL comme source d’enregistrement. Néanmoins, vous pouvez créer une vue séparée pour chaque rapport afin de mettre à jour un rapport spécifique sans avoir à modifier d’autres rapports.

Réduire le chargement des données dans un formulaire ou un État    N’affichez pas de données tant que l’utilisateur ne le demande pas. Par exemple, conservez la valeur de la propriété source, faites en sorte que les utilisateurs sélectionnent un filtre sur votre formulaire, puis remplissez la propriété RecordSource avec votre filtre. Vous pouvez utiliser la clause WHERE de DoCmd. OpenForm et DoCmd. OpenReport pour afficher l’enregistrement exact requis par l’utilisateur. Envisagez de désactiver la navigation dans les enregistrements.

Soyez attentif aux requêtes hétérogènes.   Évitez d’exécuter une requête qui combine une table Access locale et une table liée SQL Server, parfois appelée requête hybride. Ce type de requête nécessite toujours Access pour télécharger toutes les données SQL Server sur l’ordinateur local, puis exécuter la requête, la requête n’est pas exécutée dans SQL Server.

Quand utiliser les tables locales    Envisagez d’utiliser des tables locales pour les données qui changent rarement, par exemple la liste des États ou des provinces dans un pays ou une région. Les tables statiques sont souvent utilisées pour le filtrage et peuvent être plus performantes sur le front-end.

Pour plus d’informations, voir conseiller de l’optimisation du moteur de base de données, utiliser l’analyseur de performance pour optimiser une base de données Accesset optimiser les applications Microsoft Office Access liées à SQL Server.

Voir aussi

Guide de migration de base de données Azure

Blog sur la migration de données Microsoft

Microsoft Access vers leshttps://www.fmsinc.com/consulting/sqlserverupsizing.aspxde migration, de conversion et de migration SQL Server

Méthodes pour partager une base de données de bureau Access

Remarque :  Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez vous nous dire si les informations vous ont été utiles ? Voici l’article en anglais pour référence.

Développez vos compétences dans Office
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×