Migrer une base de données Access vers SQL Server

Avant de commencer

Les sections suivantes fournissent des informations générales et d’autres informations pour vous aider à commencer.

À propos du fractionnement des bases de donné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 du fractionnement de la base de données et est conçu pour faciliter le partage dans un environnement réseau. Le fichier de base de données principal doit contenir uniquement des tables et des relations. Le fichier frontal doit contenir uniquement tous les autres objets, y compris les formulaires, les états, les requêtes, les macros, les modules VBA et les tables liées à la base de données principale. Lorsque vous migrez une base de données Access, elle est similaire à une base de données fractionnée dans la mesure où SQL Server agit comme un nouveau serveur principal pour les données qui se trouvent maintenant sur un serveur.

Par conséquent, vous pouvez toujours gérer la base de données Access front-end avec des tables liées aux tables SQL Server. En effet, vous pouvez tirer parti des avantages du développement rapide d’applications qu’offre une base de données Access, ainsi que de la scalabilité de SQL Server.

Avantages de SQL Server

Vous avez encore besoin de convaincre pour migrer vers SQL Server ? Voici quelques avantages supplémentaires à prendre en compte :

Plus d’utilisateurs simultanés SQL Server peut gérer beaucoup plus d’utilisateurs simultanés qu’Access et réduit les besoins en mémoire lorsque d’autres utilisateurs sont ajoutés.
Disponibilité accrue Avec SQL Server, vous pouvez sauvegarder dynamiquement, incrémentielle ou complète, la base de données pendant son utilisation. Ainsi, vous n’êtes pas obligé de forcer les utilisateurs à quitter la base de données pour sauvegarder les données.
Hautes performances et scalabilité La base de données SQL Server fonctionne généralement mieux qu’une base de données Access, en particulier avec une base de données volumineuse de taille téraoctet. En outre, 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 intégré unique au réseau et à la base de données, en utilisant le meilleur des deux systèmes de sécurité. Il est ainsi beaucoup plus facile d’administrer des schémas de sécurité complexes. SQL Server est le stockage idéal pour les informations sensibles telles que les numéros de sécurité sociale, les données de carte de crédit et les adresses confidentielles.
Récupérabilité immédiate En cas de panne du système d’exploitation ou de panne d’alimentation, SQL Server peut automatiquement récupérer la base de données à un état cohérent en quelques minutes et sans intervention de l’administrateur de base de données.
Utilisation du VPN L’accès et les réseaux privés virtuels (VPN) ne s’entendent pas. Mais avec SQL Server, les utilisateurs distants peuvent toujours utiliser la base de données frontale Access sur un bureau et le serveur principal SQL Server situé derrière le pare-feu VPN.
Azure SQL Server En plus des avantages de SQL Server, offre une scalabilité dynamique sans temps d’arrêt, une optimisation intelligente, une scalabilité et une disponibilité globales, l’élimination des coûts matériels et une administration réduite.

Choisir la meilleure option Azure SQL Server

Si vous effectuez une migration vers Azure SQL Server, vous avez le choix entre trois options, chacune présentant des avantages différents :

Base de données unique/pools élastiques Cette option dispose de son propre ensemble de ressources gérées par le biais d’un serveur SQL Database. Une base de données unique est semblable à une base de données autonome dans SQL Server. Vous pouvez également ajouter un pool élastique, qui est une collection de bases de données avec un ensemble partagé de ressources gérées via le serveur SQL Database. Les fonctionnalités SQL Server les plus couramment utilisées sont disponibles avec des sauvegardes intégrées, la mise à jour corrective et la récupération. Toutefois, il n’existe aucune durée de maintenance exacte garantie et la migration à partir de SQL Server peut être difficile.
Managed Instance Cette option est une collection de bases de données système et utilisateur avec un ensemble partagé de ressources. Une instance managée est semblable à une instance de la base de données SQL Server qui est hautement compatible avec SQL Server local. Une instance managée dispose de sauvegardes, de mises à jour correctives et de récupération intégrées 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 aucune durée de maintenance exacte garantie.
Machine virtuelle Azure Cette option vous permet d’exécuter SQL Server à l’intérieur d’une machine virtuelle dans le cloud Azure. Vous disposez d’un contrôle total sur le moteur SQL Server et d’un chemin de migration facile. Toutefois, vous devez gérer vos sauvegardes, correctifs et récupération.

Pour plus d’informations, consultez Choix de votre chemin de migration de base de données vers Azure et Qu’est-ce qu’Azure SQL ?.

Premiers pas

Il existe quelques problèmes que vous pouvez résoudre à l’avance qui peuvent vous aider à 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 dispose d’un index et d’une clé primaire. SQL Server exige que toutes les tables aient au moins un index et qu’une table liée ait une clé primaire si la table peut être mise à jour.
Vérifier les relations de clé primaire/étrangère Assurez-vous que ces relations sont basées sur des champs avec des types de données et des tailles cohérents. SQL Server ne prend pas en charge les colonnes jointes avec différents types de données et tailles dans les contraintes de clé étrangère.
Supprimer la colonne Pièce jointe SSMA ne migre pas les tables qui contiennent la colonne Pièce jointe.

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

Fermez la base de données Access.
Assurez-vous que les utilisateurs actuels connectés à la base de données ferment également la base de données.
Si la base de données est au format de fichier .mdb, supprimez la sécurité au niveau de l’utilisateur.
Sauvegardez votre base de données. Pour plus d’informations, consultez Protéger vos données avec des processus de sauvegarde et de restauration.

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

Conseil Si possible, utilisez une version autonome d’Access.

Exécuter SSMA

Microsoft fournit l’Assistant Migration Microsoft SQL Server (SSMA) pour faciliter la migration. SSMA migre principalement des tables et sélectionne des requêtes sans paramètre. Les formulaires, les rapports, les macros et les modules VBA ne sont pas convertis. L’Explorateur de métadonnées SQL Server affiche vos objets de base de données Access et les objets SQL Server, ce qui vous permet d’examiner 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 qui doivent être transférées.

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é. Veillez à installer la version 32 ou 64 bits appropriée pour votre ordinateur.
Après avoir installé SSMA, ouvrez-le sur votre bureau, de préférence à partir de l’ordinateur avec le fichier de base de données Access.

Vous pouvez également l’ouvrir sur un ordinateur qui a accès à la base de données Access à partir du réseau dans un dossier partagé.
Suivez les instructions de début dans SSMA pour fournir des informations de base telles que l’emplacement sql Server, la base de données Access et les objets à migrer, les informations de connexion et si vous souhaitez créer des tables liées.
Si vous effectuez une migration vers SQL Server 2016 ou version ultérieure et que vous souhaitez 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. En outre, si vous ajoutez le champ rowversion à une requête, Access l’utilise pour sélectionner à nouveau la ligne après une opération de mise à jour. Cela améliore l’efficacité en permettant d’éviter les erreurs de conflit d’écriture et les scénarios de suppression d’enregistrements qui peuvent se produire quand Access détecte différents résultats de la soumission d’origine, comme cela peut se produire avec des types de données à nombre à virgule flottante et des déclencheurs qui modifient des colonnes. Toutefois, évitez d’utiliser le champ rowversion dans les formulaires, les états ou le code VBA. Pour plus d’informations, consultez rowversion.

Remarque Évitez de confondre rowversion avec timestamps. Bien que le mot clé timestamp soit un synonyme de rowversion dans SQL Server, vous ne pouvez pas utiliser rowversion comme moyen d’horodatage d’une entrée de données.
Pour définir des types de données précis, sélectionnez Outils de révision > Paramètres du projet > Mappage des types. Par exemple, si vous stockez uniquement du texte en anglais, vous pouvez utiliser le type de données varchar plutôt que nvarchar .

Convertir des objets

SSMA convertit les objets Access en objets SQL Server, mais il ne copie pas immédiatement les objets. SSMA fournit une liste des objets suivants à migrer afin que vous puissiez décider si vous souhaitez les déplacer vers la base de données SQL Server :

Tables et colonnes
Sélectionnez Requêtes sans paramètres.
Clés primaires et étrangères
Index et valeurs par défaut
Contraintes de vérification (propriété de colonne de longueur nulle, règle de validation de colonne, validation de table)

En guise de meilleure pratique, utilisez le rapport d’évaluation SSMA, qui affiche les résultats de la conversion, y compris les erreurs, les avertissements, les messages d’information, les estimations de temps pour effectuer la migration et les étapes de correction des erreurs individuelles à effectuer avant de déplacer réellement les objets.

La conversion d’objets de base de données prend les définitions d’objet des métadonnées Access, les convertit en syntaxe Transact-SQL (T-SQL) équivalente, puis charge ces informations dans le projet. Vous pouvez ensuite afficher les objets SQL Server ou SQL Azure et leurs propriétés à l’aide de SQL Server ou de l’Explorateur de métadonnées SQL Azure.

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

Conseil Une fois que vous avez correctement migré votre base de données Access, enregistrez le fichier projet pour une utilisation ultérieure afin de pouvoir migrer à nouveau vos données à des fins de test ou de migration finale.

Tables de liaison

Envisagez d’installer la dernière version des pilotes SQL Server OLE DB et ODBC au lieu d’utiliser les pilotes SQL Server natifs fournis avec Windows. Non seulement les pilotes plus récents sont plus rapides, mais ils prennent en charge de nouvelles fonctionnalités dans Azure SQL que les pilotes précédents ne le font pas. Vous pouvez installer les pilotes sur chaque ordinateur sur lequel la base de données convertie est utilisée. Pour plus d’informations, consultez Microsoft OLE DB Driver 18 pour SQL Server et Microsoft ODBC Driver 17 pour SQL Server.

Après avoir migré les tables Access, vous pouvez établir un lien vers les tables dans SQL Server qui héberge désormais vos données. La liaison directe à partir d’Access vous offre également un moyen plus simple d’afficher vos données plutôt que d’utiliser les outils d’administration SQL Server plus complexes. Vous pouvez interroger et modifier des données liées en fonction des autorisations configurées par votre administrateur de base de données SQL Server.

Remarque Si vous créez un DSN ODBC lorsque vous liez à votre base de données SQL Server pendant le processus de liaison, créez le même nom de source de données sur tous les ordinateurs qui utilisent la nouvelle application ou utilisez par programme la chaîne de connexion stockée dans le fichier DSN.

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

Conseil N’oubliez pas d’utiliser le Gestionnaire de tables liées dans Access pour actualiser et lier de nouveau les tables. Pour plus d’informations, consultez Gérer les tables liées.

Tester et réviser

Les sections suivantes décrivent les problèmes courants que vous pouvez rencontrer pendant la migration et comment les traiter.

Requêtes

Seules les requêtes Select sont converties ; d’autres requêtes ne le sont pas, y compris Sélectionner des requêtes qui prennent des paramètres. Certaines requêtes peuvent ne pas être entièrement converties, et SSMA signale des erreurs de requête pendant le processus de conversion. Vous pouvez modifier manuellement des objets qui ne sont pas convertis à l’aide de la syntaxe T-SQL. Les erreurs de syntaxe peuvent également nécessiter la conversion manuelle de fonctions et de types de données spécifiques à Access en fonctions SQL Server. Si vous souhaitez avoir plus d’informations à ce sujet, consultez Comparaison d’Access SQL avec SQL Server TSQL.

Types de données

Access et SQL Server ont des types de données similaires, mais tenez compte des 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 de grands nombres, mais il nécessite l’utilisation du format de fichier de base de données .accdb Access 16 (16.0.7812 ou ultérieur) et fonctionne mieux avec la version 64 bits d’Access. Pour plus d’informations, voir Utilisation du type de données Grand nombre et Choisir entre la version 64 bits ou 32 bits d’Office.

Oui/non Par défaut, une colonne Access Oui/Non est convertie en champ de bits SQL Server. Pour éviter le verrouillage des enregistrements, assurez-vous que le champ de bits est défini pour interdire les valeurs NULL. DANS SSMA, vous pouvez sélectionner la colonne de bits pour définir la propriété Allow Nulls sur NON. Dans TSQL, utilisez les instructions CREATE TABLE ou ALTER TABLE .

Date et heure Il existe plusieurs considérations de date et d’heure :

Si le niveau de compatibilité de la base de données est 130 (SQL Server 2016) ou supérieur et qu’une table liée contient une ou plusieurs colonnes datetime ou datetime2, la table peut renvoyer le message #deleted dans les résultats. Pour plus d’informations, consultez La table liée Access à SQL-Server base de données retourne #deleted.
Utilisez le type de données Date/Heure Access pour mapper au type de données datetime. Utilisez le type de données Access Date/Time Extended pour mapper au type de données datetime2 qui a une plage de dates et d’heures plus grande. Pour plus d’informations, consultez Utilisation du type de données Date/Heure étendue.
Lorsque vous interrogez des dates dans SQL Server, prenez en compte l’heure ainsi que la date. Par exemple :
- DateOrdered Entre le 1/1/19 et le 31/01/19 peut ne pas inclure toutes les commandes.
- DateOrdered Entre le 1/1/19 00 :00 :00 AM et le 31/1/19 11 :59 :59 PM inclut toutes les commandes.

Pièce jointe Le type de données Pièce jointe stocke un fichier dans la base de données Access. Dans SQL Server, vous avez plusieurs options à prendre en compte. Vous pouvez extraire les fichiers de la base de données Access, puis envisager de stocker des liens vers les fichiers dans votre base de données SQL Server. Vous pouvez également utiliser FILESTREAM, FileTables ou le 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 ont des colonnes de lien hypertexte non prises en charge par SQL Server. Par défaut, ces colonnes sont converties en colonnes nvarchar(max) dans SQL Server, mais vous pouvez personnaliser le mappage pour choisir un type de données plus petit. Dans votre solution Access, vous pouvez toujours utiliser le comportement de lien hypertexte dans les formulaires et les états si vous définissez la propriété Hyperlink pour le contrôle sur true.

Champ à valeurs multiples Le champ à valeurs multiples Access est converti en SQL Server en tant que champ ntext qui contient l’ensemble délimité de valeurs. É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, consultez Comparer les types de données.

Remarque Les champs à valeurs multiples ne sont pas convertis.

Pour plus d’informations, consultez Types de date et d’heure, Types string et binaires et Types numériques.

Visual Basic

Bien que VBA ne soit 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 les données d’une colonne de requête. Toutefois, les requêtes Access qui utilisent des fonctions VBA ne peuvent pas être exécutées sur SQL Server. Toutes les données demandées sont donc 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 de 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 modifiable et de zones de liste sur des formulaires, des rapports et des champs de table, ainsi que des expressions de règle par défaut ou de validation. SQL Server ne peut pas exécuter ces fonctions définies par l’utilisateur. Vous devrez peut-être reconcevoir manuellement ces fonctions et les convertir en procédures stockées sur SQL Server.

Optimiser les performances

De loin, le moyen le plus important d’optimiser les performances avec votre nouveau serveur SQL Server principal consiste à décider quand utiliser des requêtes locales ou distantes. Lorsque vous migrez vos données vers SQL Server, vous passez également d’un serveur de fichiers à un modèle de base de données client-serveur de calcul. Suivez ces instructions générales :

Exécutez de petites requêtes en lecture seule sur le client pour un accès le plus rapide.
Exécutez de longues requêtes en lecture/écriture sur le serveur pour tirer parti de la plus grande puissance de traitement.
Réduisez le trafic réseau avec des 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, consultez Créer une requête directe.

Vous trouverez ci-dessous des recommandations supplémentaires.

Placer la logique sur le serveur Votre application peut également utiliser des vues, 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 des données et le code d’intégrité référentielle sur le serveur plutôt que sur le client. Demandez-vous si cette requête ou cette tâche peut-elle être exécutée sur le serveur mieux et plus rapidement ? Enfin, testez chaque requête pour garantir des performances optimales.

Utiliser des vues dans des formulaires et des états Dans Access, procédez comme suit :

Pour les formulaires, utilisez une vue SQL pour un formulaire en lecture seule et une vue indexée SQL pour un formulaire en lecture/écriture comme source d’enregistrement.
Pour les rapports, utilisez une vue SQL comme source d’enregistrement. Toutefois, créez une vue distincte pour chaque rapport, afin de pouvoir mettre à jour plus facilement un rapport spécifique, sans affecter les autres rapports.

Réduire le chargement des données dans un formulaire ou un état N’affichez pas les données tant que l’utilisateur ne les demande pas. Par exemple, laissez la propriété recordsource vide, faites en sorte que les utilisateurs sélectionnent un filtre sur votre formulaire, puis remplissez la propriété recordsource avec votre filtre. Vous pouvez également utiliser la clause where de DoCmd.OpenForm et DoCmd.OpenReport pour afficher le ou les enregistrements exacts nécessaires à l’utilisateur. Envisagez de désactiver la navigation dans les enregistrements.

Soyez prudent avec les 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 qu’Access télécharge toutes les données SQL Server sur l’ordinateur local, puis exécute la requête. Il n’exécute pas la requête dans SQL Server.

Quand utiliser des tables locales Envisagez d’utiliser des tables locales pour les données qui changent rarement, telles que la liste des états ou des provinces d’un pays ou d’une région. Les tables statiques sont souvent utilisées pour le filtrage et peuvent mieux fonctionner sur le front-end Access.

Pour plus d’informations, consultez Assistant Paramétrage du moteur de base de données, Utiliser l’Analyseur de performances pour optimiser une base de données Access et Optimisation des applications Microsoft Office Access liées à SQL Server.