Liste de contrôle de migration de base de données SQL Server – Bien choisir son serveur d impression
Par: Jugal Shah | Dernière mise à jour: 2010-02-05 | Commentaires (10) | Mises à niveau et migrations
Problème
Nous recevons souvent des demandes de migration d'une base de données utilisateur unique ou de plusieurs bases de données utilisateur vers un serveur différent. Au moment de la migration des bases de données, nous risquons de manquer certaines étapes importantes, telles que les propriétés de confiance, les travaux dépendants, le serveur lié, les connexions, etc. Il est également possible que nous manquions d'éléments DBA importants susceptibles de nuire aux performances de SQL Server. Dans cette astuce, je traite des tâches à suivre lors du transfert de bases de données d’un serveur à un autre.
Solution
Afin de réduire le nombre d'erreurs commises pendant le processus de migration, j'ai développé la liste de contrôle de migration de base de données ci-dessous. En outre, certaines mesures doivent être prises pour améliorer les performances de la base de données après la migration.
Liste de contrôle pré-migration
- Analysez l'espace disque du serveur cible pour la nouvelle base de données, si l'espace disque n'est pas suffisant, ajoutez de l'espace supplémentaire sur le serveur cible.
- Confirmez les données et l'emplacement du fichier journal pour le serveur cible
- Collectez les informations sur les propriétés de la base de données (Statistiques automatiques, Propriétaire de la base de données, Modèle de récupération, Niveau de compatibilité, Option de confiance, etc.).
- Collectez les informations des applications dépendantes, assurez-vous que les services d'application seront arrêtés lors de la migration de la base de données
- Collectez les informations de connexion aux bases de données, les utilisateurs et leurs autorisations. (Optionnel)
- Vérifiez la base de données pour les utilisateurs orphelins, le cas échéant
- Recherchez dans le serveur SQL des objets dépendants (travaux d'agent SQL et serveurs liés)
- Vérifiez si la base de données fait partie d'un plan de maintenance
Vous trouverez ci-dessous divers scripts que vous pouvez exécuter pour collecter des données.
Script pour vérifier la taille du disque et de la base de données
- Procédure pour vérifier l'espace disque exec master..xp_fixeddrives - Vérifier la taille de la base de données exec sp_helpdb [dbName] ou utilisation [dbName] select str (sum (convert (dec (17,2), taille)) / 128,10,2) + 'MB' à partir de dbo.sysfiles ALLER
Script pour vérifier les propriétés de la base de données
sélectionner sysDB.database_id, sysDB.Name comme 'Nom de la base de données', syslogin.Name en tant que 'propriétaire de la base de données', sysDB.state_desc, sysDB.recovery_model_desc, sysDB.collation_name, sysDB.user_access_desc, sysDB.compatibility_level, sysDB.is_read_only, sysDB.is_auto_close_on, sysDB.is_auto_shrink_on, sysDB.is_auto_create_stats_on, sysDB.is_auto_update_stats_on, sysDB.is_fulltext_enabled, sysDB.is_trustworthy_on à partir de sys.databases sysDB INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid
Un autre script pour vérifier les propriétés de la base de données
déclarer @dbdesc varchar (max) declare @name varchar (10) set @ name = 'Master' SELECT @dbdesc = 'Status =' + convert (sysname, DatabasePropertyEx (@ name, 'Status')) SELECT @dbdesc = @dbdesc + ', Updateability =' + convert (sysname, DatabasePropertyEx (@ name, 'Updateability')) SELECT @dbdesc = @dbdesc + ', UserAccess =' + convert (sysname, DatabasePropertyEx (@ name, 'UserAccess')) SELECT @dbdesc = @dbdesc + ', Recovery =' + convert (sysname, DatabasePropertyEx (@ name, 'Recovery')) SELECT @dbdesc = @dbdesc + ', Version =' + convert (sysname, DatabasePropertyEx (@ name, 'Version')) - Ces accessoires ne sont disponibles que si la base de données n'est pas arrêtée IF DatabaseProperty (@name, 'IsShutdown') = 0 COMMENCER SELECT @dbdesc = @dbdesc + ', Collation =' + convert (sysname, DatabasePropertyEx (@ name, 'Collation')) SELECT @dbdesc = @dbdesc + ', SQLSortOrder =' + convert (sysname, DatabasePropertyEx (@ name, 'SQLSortOrder')) FIN - Ce sont les propriétés booléennes IF DatabasePropertyEx (@ name, 'IsAutoClose') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsAutoClose' IF DatabasePropertyEx (@ name, 'IsAutoShrink') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsAutoShrink' IF DatabasePropertyEx (@ name, 'IsInStandby') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsInStandby' IF DatabasePropertyEx (@ name, 'IsTornPageDetectionEnabled') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsTornPageDetectionEnabled' IF DatabasePropertyEx (@ name, 'IsAnsiNullDefault') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsAnsiNullDefault' IF DatabasePropertyEx (@ name, 'IsAnsiNullsEnabled') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsAnsiNullsEnabled' IF DatabasePropertyEx (@ name, 'IsAnsiPaddingEnabled') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsAnsiPaddingEnabled' IF DatabasePropertyEx (@ name, 'IsAnsiWarningsEnabled') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsAnsiWarningsEnabled' IF DatabasePropertyEx (@ name, 'IsArithmeticAbortEnabled') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsArithmeticAbortEnabled' IF DatabasePropertyEx (@ name, 'IsAutoCreateStatistics') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsAutoCreateStatistics' IF DatabasePropertyEx (@ name, 'IsAutoUpdateStatistics') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsAutoUpdateStatistics' IF DatabasePropertyEx (@ name, 'IsCloseCursorsOnCommitEnabled') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsCloseCursorsOnCommitEnabled' IF DatabasePropertyEx (@ name, 'IsFullTextEnabled') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsFullTextEnabled' IF DatabasePropertyEx (@ name, 'IsLocalCursorsDefault') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsLocalCursorsDefault' IF DatabasePropertyEx (@ name, 'IsNullConcat') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsNullConcat' IF DatabasePropertyEx (@ name, 'IsNumericRoundAbortEnabled') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsNumericRoundAbortEnabled' IF DatabasePropertyEx (@ name, 'IsQuotedIdentifiersEnabled') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsQuotedIdentifiersEnabled' IF DatabasePropertyEx (@ name, 'IsRecursiveTriggersEnabled') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsRecursiveTriggersEnabled' IF DatabasePropertyEx (@ name, 'IsMergePublished') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsMergePublished' IF DatabasePropertyEx (@ name, 'IsPublished') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsPublished' IF DatabasePropertyEx (@ name, 'IsSubscribe') = 1 SELECT @dbdesc = @dbdesc + ',' + 'est abonné' IF DatabasePropertyEx (@ name, 'IsSyncWithBackup') = 1 SELECT @dbdesc = @dbdesc + ',' + 'IsSyncWithBackup' SELECT @dbdesc
Script pour lister les utilisateurs orphelins
sp_change_users_login 'rapport' ALLER
Script pour lister les serveurs liés
sélectionnez * de sys.sysservers
Script pour répertorier les travaux dépendants de la base de données
sélectionner distinct prénom, nom de la base de données de sysjobs sj INNER JOIN sysjobsteps sjt sur sj.job_id = sjt.job_id
Liste de contrôle de migration de base de données
Voici les étapes à suivre pour effectuer le changement.
1. Arrêtez les services d'application
2. Modifier la base de données en mode lecture seule (facultatif)
- Script pour rendre la base de données en lecture seule UTILISATION [master] ALLER ALTER DATABASE [DBName] SET READ_ONLY AVEC NO_WAIT ALLER ALTER DATABASE [DBName] SET READ_ONLY ALLER
3. Effectuez la dernière sauvegarde de toutes les bases de données impliquées dans la migration.
4. Restaurez les bases de données sur le serveur cible sur les lecteurs appropriés
5. Vérifiez les propriétés de la base de données conformément à la sortie du script de propriétés de la base de données, modifiez les propriétés de la base de données conformément à la liste de contrôle préalable à la migration.
Script pour changer le propriétaire de la base de données
Cela changera le propriétaire de la base de données en "sa". Cela peut être utilisé pour changer de propriétaire.
USE nomDonnées EXEC sp_changedbowner 'sa'
Script pour activer l'option digne de confiance
Si l'option digne de confiance a été définie, cela l'activera pour la base de données.
ALTER DATABASE nom_bdd SET TRUSTWORTHY ON
Script pour changer le niveau de compatibilité de la base de données
Lorsque vous effectuez une mise à niveau vers une nouvelle version, l'ancien niveau de compatibilité reste. Ce script montre comment modifier le niveau de compatibilité pour le rendre compatible avec SQL Server 2005.
ALTER DATABASE DatabaseName SET SINGLE_USER ALLER EXEC sp_dbcmptlevel DatabaseName, 90; ALLER ALTER DATABASE DatabaseName SET MULTI_USER ALLER
6. Exécutez la sortie du script de transfert de connexion sur le serveur cible. Pour créer des connexions sur le serveur cible, vous pouvez obtenir le code à partir de cet article technique: http://support.microsoft.com/kb/246133.
7. Recherchez les utilisateurs orphelins et corrigez les utilisateurs orphelins
Script pour vérifier et réparer les utilisateurs orphelins
- Script pour vérifier l'utilisateur orphelin EXEC sp_change_users_login 'Rapport' --Utilisez le code ci-dessous pour résoudre le problème d'utilisateur orphelin DECLARE @username varchar (25) DÉCLARER les fixateurs CURSEUR POUR SELECT UserName = name FROM sysusers WHERE issqluser = 1 et (id n'est pas nul et id 0x0) et suser_sname (sid) est null ORDRE PAR NOM Fixusers OUVERTS FETCH NEXT FROM fixusers INTO @username WHILE @@ FETCH_STATUS = 0 COMMENCER EXEC sp_change_users_login 'update_one', @nomutilisateur, @nomutilisateur FETCH NEXT FROM fixusers INTO @username FIN FERMER les fixateurs DÉALLOCATEURS
8. Exécutez DBCC UPDATEUSAGE sur la base de données restaurée.
Exécutez la commande DBCC UPDATEUSAGE sur la base de données migrée lors de la mise à niveau vers une version plus récente de SQL Server.
DBCC UPDATEUSAGE ('nom_base_de_données') AVEC COUNT_ROWS
DBCC CHECKDB
OU
DBCC CHECKDB ('nom_base_de_données') WITH ALL_ERRORMSGS
9. Reconstruire les index (facultatif) Selon les conditions et la fenêtre temporelle, vous pouvez exécuter cette option.
Jetez un coup d'œil à cette astuce pour reconstruire tous les index.
Cela reconstruira ou réorganisera tous les index pour une table particulière.
Index Rebuild: – Ce processus supprime l'index existant et recrée l'index.
Index Réorganiser: – Ce processus réorganise physiquement les nœuds d'extrémité de l'index.
- Script pour la reconstruction d'index UTILISATION [DBName]; ALLER ALTER INDEX ALL ON [ObjectName] RECONSTRUIRE ALLER - Script pour Index Reorganize UTILISEZ AdventureWorks; ALLER ALTER INDEX ALL ON [ObjectName] RÉORGANISER ALLER
10. Mise à jour des statistiques d'index
11. Procédures de recompilation
Jetez un coup d'œil à cette astuce pour recompiler tous les objets.
Cela recompilera une procédure stockée particulière.
sp_recompile 'nomProcédé'
12. Démarrez les services d'application, vérifiez la fonctionnalité de l'application et consultez les journaux des événements Windows.
13. Recherchez dans le journal des erreurs SQL Server les échecs de connexion et autres erreurs.
Jetez un coup d’œil à cette astuce pour savoir comment lire les journaux d’erreurs SQL Server.
EXEC xp_readerrorlog 0,1, "Erreur", Null
14. Une fois que l'équipe de l'application a confirmé que l'application fonctionne correctement, mettez les bases de données hors ligne sur le serveur source ou faites-les en lecture seule.
- Script pour rendre la base de données en lecture seule UTILISATION [master] ALLER ALTER DATABASE [DBName] SET READ_ONLY AVEC NO_WAIT ALLER ALTER DATABASE [DBName] SET READ_ONLY ALLER - Script pour mettre la base de données hors ligne EXEC sp_dboption N'DBName ', N'offline', N'true ' OU ALTER DATABASE [DBName] SET OFFLINE AVEC ROLLBACK IMMEDIATE
Prochaines étapes
- Testez le processus pour déterminer le temps et l'espace disque nécessaires à l'aide du processus de sauvegarde et de récupération.
- Rencontrez vos équipes techniques et commerciales pour connaître le temps disponible pour la migration et planifier l'activité.
- Concevoir le plan de restauration si l'application ne fonctionne pas bien
- Ajoutez d'autres cas de migration dans votre liste de contrôle, par exemple, vérifiez si la base de données nécessite une modification au niveau du serveur (par exemple, CLR, XP_Cmdshell, etc.).
- Certains de ces scripts vous donnent la commande de base pour mettre à jour une partie des données, améliorent le processus pour atteindre chaque objet de votre base de données.
Dernière mise à jour: 2010-02-05
A propos de l'auteur
Jugal Shah a plus de 8 ans d'expérience de SQL Server et a travaillé sur SQL Server 2000, 2005, 2008 et 2008 R2.
Voir tous mes conseils
Commentaires
Laisser un commentaire