Migration de bases de données avec le Log Shipping
Les scripts SQL présentés dans cet article sont destinés à vous permettre de migrer tout ou partie des bases de données d’une instance SQL Server vers une autre.
Il vous permet d’effectuer la migration autant de fois que nécessaire.
Par exemple, il n’est pas rare de faire une première migration à des fins de tests/validations, puis quand tout est validé, de procéder à la migration finale.
J’ai personnellement créé et utilisé ces scripts pour plusieurs migrations.
Je les mets à disposition gratuitement, sans aucune garantie et décline toute responsabilité sur l’usage que vous en ferez !
Merci de bien lire la documentation ci-dessous dans son ensemble pour comprendre ce qui est effectué et ce qui ne l’est pas.
J’ajoute qu’il est toujours préférable de tester avec des instances SQL Developer avant de l’utiliser en production, histoire de bien comprendre comment cela fonctionne.
En cas de doutes, vous pouvez toujours faire appel à moi pour prendre en charge votre migration 😉
PS: si vous utilisez ce script !
Laissez-moi un commentaire et/ou une recommandation sur LinkedIn, ce serait très gentil.
Et, si vous avez un site, un Backlink serait très très gentil 😉
je sais, ça fait beaucoup…
Table des matières
Introduction à lire absolument !
L’objectif est de permettre d’effectuer la migration des bases de données SQL Server vers une version égale ou supérieure avec une automatisation maximale tout en rendant celle-ci la plus simple possible.
Attention aux points suivants :
Pour les bases de données cryptées (TDE) ou soumises à certificat, il faudra faire un backup restore du certificat vers le serveur cible avant de lancer le Log Shipping.
Ce script ne fonctionne qu’en mode d’authentification intégrée et le compte utilisé doit être SYSADMIN sur les deux serveurs !
Historique des modifications
2023/03/21 Correction du code pour les instances CASE SENSITIVE
2023/03/21 Modification du mode de propagation des comptes
2023/03/19 Ajout de fonctionnalités et correction de bug
Il est désormais possible d’ajouter ou d’enlever des bases de données du Log Shipping unitairement avec l’ajout de paramètres (voir vidéos).
Exec [msdb].[LogShip].[up_Set_LogShipping_OFF]
@SetSourceDbOffLine bit = 0,
@LSDatabaseName sysname = null
Exec [msdb].[LogShip].[up_Set_LogShipping_ON]
@LSDatabaseName sysname = null
Ajout de la possibilité de mettre la base de données source OFFLINE.
Gestion des connexions ouvertes sur les bases de données de la cible, le REWRITE est désormais assuré.
Correction d’un bug sur la suppression du job quand la dernière base en Log Shipping est remise ONLINE.
2022/09/07 Split des Backups sur N fichiers
J’ai ajouté la gestion du split des backups full sur plusieurs fichiers [1-8] car pour des bases de données de plusieurs centaines de Go ou de plus d’un To cela réduit de beaucoup les temps de Backup.
2022/09/14 STRING_SPLIT
Ajout de la fonction STRING_SPLIT pour les version antérieures à SQL 2016
2022/11/15 Ajout d’une base avant le lancement du Log Shipping
Ajout d’un script SQL qui permet d’ajouter une nouvelle base de données au Log Shipping existant.
2022/11/15 Ajout du paramètre @RestoreMode
Ajout du paramètre @LogShipRestoreMode qui permet de spécifier le mode de restauration [NORECOVERY 0 ou STANDBY 1].
La restauration en mode STANDBY permet un accès en lecture seule sur le secondaire mais ne fonctionne qu’entre des instances de même version.
Le script SQL "1-Create migration schema and objects in msdb.sql"
Ce script contrôle l’ensemble du déploiement des objets requis dans la base MSDB du serveur source et génère le script de déploiement pour le serveur cible qui comprend aussi les LOGIN et ROLE du serveur source qu’il faut déployer sur le serveur cible.
Une fois exécuté, il fournit la commande à exécuter depuis le serveur source en mode CMD pour le déploiement sur le serveur cible via SQLCMD.
Tous les Scripts fournis s’exécutent toujours depuis le serveur source (Sauf le script de DROP).
Ce script ne lance pas le Log Shipping !
A la fin de son exécution, ce script retourne la ligne de commande à exécuter pour le déploiement sur le serveur cible comme dans l’exemple ci-dessous:
Les objets déployés
Les objets déployés dans msdb (tables, vues, procédures et fonctions), sont inscrits dans un schéma spécifique LogShip et sont utilisés pour procéder à la mise en place et à l’arrêt du LOG SHIPPING, ainsi que pour la génération du script SQL dynamique de déploiement sur le serveur cible.
Il déploie aussi les jobs requis par le log shipping.
Prérequis et paramétrage
Les prérequis :
Le compte AD utilisé pour exécuter le script doit être SYSADMIN sur les instances SQL source et cible.
Il faut un partage réseau sur lequel sera généré le script de déploiement dynamique (cible) et où seront effectués les Backup FULL ainsi que les Backups de LOG du LOG SHIPPING.
Les instances source et cible doivent avoir un accès complet sur ce partage.
Le paramétrage :
Le paramétrage doit bien sûr être fait avant d’exécuter le script SQL de déploiement et toujours sur le serveur source.
Si certains comptes obsolètes ne doivent pas être migrés, il faut les supprimer avant d’exécuter ce script.
Un script de DROP [Drop msdb objects.sql] est fourni.
Il ne faut utiliser que si et seulement si le Log Shipping n’a pas été activé !!!
Il permet de tout supprimer en fin de migration (sur les deux serveurs) ou pour repartir de zéro.
--Pour exclure une base de données du Log Shipping, il faut la supprimer de la table suivante :
Delete [msdb].[LogShip].[DatabasesDetails]
Where DatabaseName = 'DB_NAME'
Le paramétrage de base est réduit au strict minimum par souci de simplification mais il est possible de paramétrer encore plus finement certaines propriétés :
– le chemin de destination des fichiers des bases de données sur le serveur cible,
– le niveau de compatibilité de chaque base de données ainsi que la conservation ou non des Backups FULL effectués au démarrage du log shipping après restauration.
Le paramétrage avancé
Le paramétrage avancé doit être effectué après l’exécution du script de déploiement et avant de lancer le LOG SHIPPING, il faut aller modifier les données de trois tables en fonctions des besoins.
Par exemple, pour activer la rétention des Backups FULL utilisés pour la mise en Log Shipping, il faut modifier le paramètre à ‘N’ dans la table ci-dessous.
Par défaut, les Backups FULL sont supprimés juste après la restauration sur le serveur cible pour minimiser l’espace utilisé.
--Modification de la rétention des Backups FULL supprimés par défaut après le RESTORE
Update [msdb].[LogShip].[LogShipParams]
Set DeleteRestoredBackup = 'Y' --Default ou 'N' pour conserver les Backups FULL après le RESTORE
--Modification des chemins des fichiers DATA et LOG sur le serveur cible
--Si NewPath = NULL, c’est le paramétrage par défaut de l’instance qui est utilisé !
Update [msdb].[LogShip].[DbFilesLocation]
Set NewPath = 'Disque:\Nouveau_chemin'
Where DatabaseName = 'DB_NAME'
--Par défaut, c’est le niveau de compatibilité spécifié dans le paramétrage du script qui est appliqué.
--Set @TargetCompatibilityLevel = '150'—SQL 2019;
--Toutefois, si certaines bases de données doivent conserver leur niveau de compatibilité actuel, il faut le modifier dans la table :
--[msdb].[LogShip].[DatabasesDetails]
--Paramétrage du niveau de compatibilité d'origine sur le serveur cible
Update [msdb].[LogShip].[DatabasesDetails]
Set [NewVersionNum] = [VersionNum]
Where DatabaseName = 'DB_NAME'
Toute modification du paramétrage avancé doit être faite avant de lancer la mise en Log Shipping !
Avant de lancer le Log Shipping, il faut s’assurer qu’aucune tâche de Backup de LOG ne soit activée sur le serveur source sur l’une des bases de données concernées, sinon le Log Shipping sera cassé et il faudra tout recommencer !!!
Une fois le paramétrage fait et le script dynamique déployé sur l’instance cible via la commande CMD fournie automatiquement, le Log Shipping peut être lancé.
Les vidéos de démonstration
Déploiement du script sur le serveur source et le serveur cible, puis lancement du Log Shipping
Sortir une base de données du Log Shipping
Finaliser la migration d'une base de données
Finaliser la migration de toutes les bases de données
Lancement du Log Shipping (depuis le serveur source)
--Lancement du Log Shipping depuis le serveur source
Exec [msdb].[LogShip].[up_Set_LogShipping_ON];
Voici la liste des tâches effectuées par la procédure :
– Vérification de la source d’exécution, si ce n’est pas le serveur source, la procédure se termine.
– Création d’un serveur lié spécifique entre la source et la destination [LogShipLinkedServer].
– Création des synonymes requis (tables et procédures)*.
*Les synonymes sont des pointeurs d’objets sur la cible
– Déploiement du paramétrage sur la cible.
Les étapes suivantes sont effectuées séquentiellement sur chaque base de données inscrite dans le Log Shipping :
– Vérification du Recovery Model et passage en mode FULL si besoin.
– Lancement du Backup FULL.
– Restauration du Backup FULL sur la cible et selon le paramétrage suppression de celui-ci.
– Mise en place de la base de données dans le Log Shipping.
– Activation des jobs de Log Shipping sur la source et la cible (si inactifs).
En cas d’erreur, un Rollback complet est effectué :
– Suppression des bases du Log Shipping.
– Restauration du Recovery Model d’origine sur la source.
– Désactivation des jobs de Log Shipping.
En fin de traitement (error ou success), les synonymes et le serveur lié sont supprimés.
Toutes les étapes sont loguées en temps réel dans la fenêtre MESSAGE d’SSMS.
En fin de traitement, vous pouvez vérifier l’état du Log Shipping sur les deux serveurs via :
Rapports standard / Etat de l’envoi des journaux.
Désactivation du Log Shipping pour les tests ou la mise en prod finale
Pour désactiver le Log Shipping et restaurer les bases de données dans leur état original (Recovery Model) sur la source et la cible, il faut exécuter la procédure suivante toujours depuis le serveur source.
--Arrêt du Log Shipping depuis le serveur source
Exec [msdb].[LogShip].[up_Set_LogShipping_OFF];
Voici la liste des tâches effectuées par la procédure :
– Vérification de la source d’exécution, si ce n’est pas le serveur source la procédure se termine.
– Création d’un serveur lié spécifique entre la source et la destination [LogShipLinkedServer].
– Création des synonymes requis (tables et procédures)*.
*Les synonymes sont des pointeurs d’objets sur la cible
– Désactivation des jobs du Log Shipping sur la source et la cible.
– Un dernier Backup et Restore des LOGS pour avoir des bases de données ISO.
Les étapes suivantes sont effectuées séquentiellement sur chaque base de données :
– Suppression de la base de données du Log Shipping sur la source et la cible.
– Mise ONLINE de la base de données sur la cible.
– Restauration de son Recovery Model d’origine sur la source et la cible.
– Mise en place du niveau de compatibilité paramétré pour la cible.
– Vérification et association des comptes orphelins dans les bases de données sur la cible.
En fin de traitement, les synonymes et le serveur lié sont supprimés.
Toutes les étapes sont loguées en temps réel dans la fenêtre MESSAGE d’SSMS.
Une fois la migration terminée, vous pourrez utiliser le script de DROP pour nettoyer les deux serveurs.
Migration SSRS, SSIS
Il n’y a pas de méthode 100% fiable automatisable en pur SQL pour la migration SSRS et SSIS, certains problèmes liés à la master key et aux rôles ne fonctionnent pas contrairement à ce qui est documenté.
De plus, cette migration n’est pas à refaire, une seule fois suffit, donc il est inutile de perdre trop de temps à automatiser cette partie sachant qu’il y aura de toute façon des interventions manuelles à faire à postériori.
Pour la migration du serveur de rapports SSRS, il faut suivre les étapes décrites dans le lien ci-dessous :
https://docs.microsoft.com/fr-fr/sql/reporting-services/install-windows/migrate-a-reporting-services-installation-native-mode?view=sql-server-ver15
Pour la migration des Packages SSIS, il faut créer le nouveau catalogue sur la nouvelle instance, mettre à jours les Packages après avoir fait une sauvegarde, puis les déployer dans le nouvel environnement depuis SSDT (Visual Studio) :
https://docs.microsoft.com/fr-fr/sql/integration-services/install-windows/upgrade-integration-services-packages-using-the-ssis-package-upgrade-wizard?view=sql-server-ver15
Pour la migration SSAS, il faut suivre le lien ci-dessous :
https://docs.microsoft.com/fr-fr/sql/database-engine/install-windows/upgrade-analysis-services?view=sql-server-ver15