Serveur d'impression

Liste de contrôle de migration de base de données SQL Server – Bien choisir son serveur d impression

Le 2 mai 2019 - 10 minutes de lecture

Par: Jugal Shah | Dernière mise à jour: 2010-02-05 | Commentaires (10) | Conseils connexes: Plus> 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

  1. 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.
  2. Confirmez les données et l'emplacement du fichier journal pour le serveur cible
  3. 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.).
  4. 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
  5. Collectez les informations de connexion aux bases de données, les utilisateurs et leurs autorisations. (Optionnel)
  6. Vérifiez la base de données pour les utilisateurs orphelins, le cas échéant
  7. Recherchez dans le serveur SQL des objets dépendants (travaux d'agent SQL et serveurs liés)
  8. 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

bouton webcast suivant

bouton suivant

A propos de l'auteur
MSSQLTips auteur Jugal Shah

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

Votre commentaire sera révisé par les administrateurs si besoin.