Comment migrer une base de données SQL Server vers une nouvelle version de SQL Server

Tout DBA sait que remettre une base de données SQL Server à une version antérieure de SQL Server n’est pas chose aisée.

En effet, si nous créons un backup (une sauvegarde) d’une base de données depuis une version de SQL Server supérieure, il s’avère impossible de la restaurer, même lorsque le mode de compatibilité de ladite base de données a été ajusté pour coller à la version de destination. Chose plus inattendue, parfois, nous pouvons aussi rencontrer des problèmes dans le sens opposé (d’une version inférieure vers une version supérieure)…

Alors que la restauration d’un backup créé depuis une instance SQL Server 2008 R2 sur une instance SQL Server 2012 fonctionne parfaitement bien, ce n’est pas le cas si la source de ce backup est une ancienne version comme SQL Server 2000. Dans ce dernier cas, nous serions confrontés à l’erreur suivante:

Une des solutions est de:

1. Restaurer le backup de la base de données, pris sur l’instance SQL Server 2000, sur une instance SQL Server 2008
2. Ajuster le niveau de compatibilité à 100
3. Créer un backup de la base sur cette instance SQL Server 2008
4. Restaurer ce backup sur l’instance SQL Server 2012 de votre choix
5. Ajuster le niveau de compatibilité à 110

L’inconvénient de cette solution est que nous devons avoir à notre disposition 3 versions de SQL Server et que nous devons encore nous occuper manuellement de la suppression des fonctionnalités dépréciées du langage Transact SQL.

Si nous avons accès à la base de données depuis laquelle notre sauvegarde a été prise, nous pouvons appliquer la procédure suivante:

1. Démarrer SQL Server Management Studio et se connecter à l’instance qui contient la base de données
2. Dans le menu contextuel de la base de données, naviguez vers Tasks | Generate Scripts…. Ceci appellera l’assistant de génération et publication
3. A l’étape d’introduction de l’assistant, cliquer sur Next (Suivant)
4. A l’étape Choose Objects (Choix des objets) de l’assistant, vérifier que l’option Script entire database and all database objects (Générer pour la base de données et tous les objets) est sélectionnée et cliquer Next (Suivant)
5. A l’étape Set Scripting options Définition des paramètres de génération) de l’assistant:
   1. Sélectionner l’option Save scripts to a specific location (Sauver les scripts dans un emplacement spécifique)
   2. Spécifier si les objets et les données doivent être générées dans un ou plusieurs fichiers ainsi que l’emplacement et l’encodage à utiliser pour ces scripts
   3. Cliquer sur Advanced (Paramètres avancés)
   4. La boite de dialogue Advanced Scripting Options apparait:
      • A l’option Script for Server Version (générer pour une version spécifique), spécifier Schema and data (schéma et données)
      • Définir la valeur True (Vrai) aux options suivantes:
        • Script Logins
        • Script Full-Text Indexes
        • Script Triggers
      • Puis cliquer sur OK

• Cliquer sur Next (Suivant)
• A l’étape de résumé de l’assistant, cliquer sur Next
• A l’étape Save or Publish Scripts (sauver et publier les scripts), cliquer sur Finish (Terminer)
• Exécuter le(s) script(s) généré(s) sur l’instance à version antérieure

Bien que la procédure ci-dessus devrait fonctionner:

1. Les fonctionnalités abandonnées ou dépréciées doivent être supprimées manuellement
2. Une erreur pourrait survenir si la base de données contient des dépendances que SQL Server ne parvient pas à reconnaître (par exemple, des dépendances vers des bases de données accédées au travers serveurs liés)

S’il nous est impossible d’accéder à la base de données originale quelle qu’en soit la raison (e.g. migration d’une base de données vers un site distant sans connexion au réseau), les procédures ci-dessous ne peuvent être appliquées

C’est là que les outils ApexSQL Diff et ApexSQL Data Diff s’avèrent très utiles

ApexSQL Diff iest un outil de comparaison et de synchronisation de bases de données qui détecte les différences entre les objets de base de données et les résout sans erreur. Pour ce faire, il génère des rapports faciles à comprendre sur les différences qu’il a trouvées et permet l’automatisation du processus de synchronisation entre la base de données en cours d’utilisation et une base de données sous gestionnaire de version, des backups et des snapshots de bases de données ou encore un dossier de scripts.

ApexSQL Data Diff est un outil de comparaison et de synchronisation. Il détecte les différences entre deux bases de données au niveau des données et effectue les actions nécessaires pour les synchroniser. Les comparaisons peuvent porter sur des bases de données actives, des fichiers de sauvegarde compressés ou non. Il génère des rapports structurés des différences détectées.

Pour restaurer le backup sur une version plus récente de SQL Server:

1. Sur l’instance “destination”, créer une base vide qui contiendra les données et les objets à restaurer depuis le backup
2. Démarrer ApexSQL Diff

3. Cliquer sur New (Nouveau) dans la boite de dialogue Project management (Gestion de projet)

   

4. Dans la zone relative à la source du traitement:
   • Sélectionner Backup dans la liste de sélection
   • Cliquer sur Add file(s) (Ajouter fichier(s)) et naviguer vers l’emplacement du fichier de sauvegarde
   • Sélectionner le fichier de sauvegarde et cliquer sur Open (Ouvrir)
5. Dans la zone relative à la destination du traitement:
   • Sélectionner Database dans la liste de sélection
   • Spécifier le nom de l’instance où se trouve la base de données vide créée précédemment
   • Définir la méthode d’authentification à utiliser pour se connecter (et des données de connexion valides si l’authentification SQL Server est choisie)
   • Ajouter le nom de la base de données où les objets doivent être restaurés, dans la zone Database
6. Cliquer sur Compare (Comparer)

7. Sélectionner tous les objets listés dans la grille principale (zone centrale)

   

8. Cliquer sur le bouton Synchronize dans la zone Action du ruban Home
9. Cliquer sur Next (Suivant) dans l’étape de spécification de la direction de synchronisation de l’assistant de synchronisation

10. Cliquer sur Next (Suivant) dans l’étape de spécification des dépendances de l’assistant de synchronisation

    

11. Vérifier le résumé des paramètres de synchronisation et les avertissements éventuels puis cliquer sur Next (Suivant)

    

12. Dans la partie où l’on spécifie les options de sortie, sélectionner Synchronize a database (synchroniser une base de données)
13. Cliquer sur Finish (Terminer)
14. Démarrer ApexSQL Data Diff

15. Cliquer sur New (Nouveau) dans la boite de dialogue Project management (Gestion de projet)

    

16. Dans le panneau Source:
    • Sélectionner Backup dans la liste de sélection
    • Cliquer sur Add File(s) (Ajouter fichier(s)) et naviguer vers l’emplacement du fichier de sauvegarde
    • Sélectionner le fichier de sauvegarde et cliquer sur Open (Ouvrir)
17. Dans la zone relative à la destination du traitement:
    • Sélectionner Database dans la liste de sélection
    • Spécifier le nom de l’instance où se trouve la base de données vide créée précédemment
    • Définir la méthode d’authentification à utiliser pour se connecter (et des données de connexion valides si l’authentification SQL Server est choisie)
    • Ajouter le nom de la base de données où les objets doivent être restaurés, dans la zone Database

18. Cliquer sur Compare (Comparer)

    

19. Dans la zone principale (zone centrale), sélectionner les tables dont les données doivent être restaurées
20. Dans la zone relative aux différences de données, sélectionner les enregistrements à restaurer
21. Cliquer sur le bouton Synchronize (Synchroniser) dans la zone action du ruban Home
22. Cliquer sur Next (Suivant) dans l’étape de spécification de la direction de synchronisation de l’assistant de synchronisation
23. Dans la partie où l’on spécifie les options de sortie, sélectionner Synchronize a database (synchroniser une base de données)

24. Cliquer sur Next (Suivant)

    

25. Vérifier les options de synchronisation et les éventuels avertissements puis cliquer sur Finish (Terminer)

Avec la manière développée dans cet article, la migration d’une base de données d’une vieille version de SQL Server vers une version plus récente s’effectue par synchronisation de schéma depuis un backup de la base de données originale vers une base de données initialement vide. Cette synchronisation “de structure” est suivie par une synchronisation des données.

Ainsi ApexSQL Diff et ApexSQL Data Diff se chargeront automatiquement pour vous de toutes les fonctionnalités dépréciées et abandonnées.