Déplacer les bases systèmes SQL Server 2005

Logo

Introduction

SQL Server 7.0 permettait de déplacer les bases systèmes model et msdb avec les procédures sp_detach_db et sp_attach_db : cette procédure n’est plus autorisée avec SQL Server 2000 et SQL Server 2005. Par ailleurs, avec SQL Server 2005, la nouvelle base de données système cachée appelée mssqlsystemresource fait son apparition.

Voici un guide pratique pour déplacer les bases systèmes SQL Server 2005 à travers un cas concret.

Les bases de données systèmes master, model, tempdb, msdb et mssqlsystemresource sont déplacées du répertoire C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA vers le répertoire E:\MSSQL\Data.

Déplacement des bases systèmes model, msdb et tempdb

Cette procédure s’applique uniquement au déplacement des bases systèmes model, msdb et tempdb, elle ne concerne pas le déplacement des bases systèmes master et mssqlsystemresource (ce cas de figure est présenté dans un paragraphe ultérieur).

La vue sys.master_files

La vue sys.master_files liste les noms logiques et localisations physiques des fichiers de bases de données pour les bases de données systèmes model, msdb et tempdb.

SELECT db_name(database_id) AS DbName, 
     name, 
     physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id IN (DB_ID('model'),DB_ID('tempdb'),DB_ID('msdb'))
ORDER BY  database_idDbName  name      CurrentLocation
------------------------------------------------------------------------
tempdb  tempdev   C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\tempdb.mdf
tempdb  templog   C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\templog.ldf
model   modeldev  C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\model.mdf
model   modellog  C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\modellog.ldf
msdb    MSDBData  C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\MSDBData.mdf
msdb    MSDBLog   C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\MSDBLog.ldf

Génération automatique des commandes ALTER DATABASE pour déplacer les bases model, msdb et tempdb

Les bases de données systèmes model, msdb et tempdb sont déplacées grâce à la commande ALTER DATABASE MODIFY FILE. Les commandes ALTER DATABASE sont exécutées lorsque le serveur SQL Server 2005 est en mode single user et en mode restauration exclusive de la base master (master-only recovery mode). Pour minimiser l’indisponibilité du serveur SQL Server, les commandes ALTER DATABASE peuvent être préparées automatiquement grâce à la requête ci-dessous :

SELECT 'ALTER DATABASE ' + db_name(database_id) +
  ' MODIFY FILE (NAME=''' + name + ''', FILENAME=''' +  
    replace(physical_name,
            'C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\',
            'E:\MSSQL\DATA\') 
   + ''')' + char(10) + 'go' + char(10)
FROM sys.master_files
WHERE database_id IN (DB_ID('model'),DB_ID('tempdb'),DB_ID('msdb'))
ORDER BY database_id

ALTER DATABASE tempdb
MODIFY FILE (NAME='tempdev', FILENAME='E:\MSSQL\DATA\tempdb.mdf')
go
ALTER DATABASE tempdb
MODIFY FILE (NAME='templog', FILENAME='E:\MSSQL\DATA\templog.ldf')
go
ALTER DATABASE model
MODIFY FILE (NAME='modeldev', FILENAME='E:\MSSQL\DATA\model.mdf')
go
ALTER DATABASE model
MODIFY FILE (NAME='modellog', FILENAME='E:\MSSQL\DATA\modellog.ldf')
go
ALTER DATABASE msdb
MODIFY FILE (NAME='MSDBData', FILENAME='E:\MSSQL\DATA\MSDBData.mdf')
go
ALTER DATABASE msdb
MODIFY FILE (NAME='MSDBLog', FILENAME='E:\MSSQL\DATA\MSDBLog.ldf')
go

Le résultat de la requête est conservé précieusement et sera exécuté ultérieurement avec sqlcmd. Dans la suite de cet article, les commandes ALTER DATABASE sont conservées dans le fichier SQL altersysmodelmsdbtempdb.sql.

Procédure de déplacement des bases model, msdb et tempdb

Redémarrage du serveur SQL Server en mode single user et master-only recovery (respectivement -f , -T3608)

Arrêter le serveur SQL avec la commande NET STOP depuis une commande DOS :

Instance par défaut Instance nommée (instancename)
NET STOP MSSQLSERVER
NET STOP MSSQL$instancename

Si le service SQL Server Agent est actif, accepter également l’arrêt de ce service dépendant :

net stop MSSQLSERVER

The following services are dependent on the SQL Server (MSSQLSERVER) service.
Stopping the SQL Server (MSSQLSERVER) service will also stop these services.

   SQL Server Agent (MSSQLSERVER)

Do you want to continue this operation? (Y/N) [N]: Y
The SQL Server Agent (MSSQLSERVER) service is stopping.
The SQL Server Agent (MSSQLSERVER) service was stopped successfully.

The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.

Démarrer ensuite le serveur SQL en mode single user (-f) et master-only recovery (-T3608) avec la commande NET START depuis une commande DOS en spécifiant les paramètres /f et /T3608

Instance par défaut Instance nommée (instancename)
NET START MSSQLSERVER /f /T3608
NET START MSSQL$instancename  /f /T3608
net start MSSQLSERVER /f /T3608

The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.

Pour vérifier le bon démarrage en mode single user et master-only recovery, repérer les lignes ci-dessous dans le fichier de log du serveur SQL :

2009-11-25 18:25:21.34 spid4s  SQL Server started in single-user mode …
2009-11-25 18:25:21.34 spid4s  Recovering only master database because traceflag 3608 was specified …
2009-11-25 18:25:21.34 spid4s  Starting up database 'master'.

Exécution des commandes ALTER DATABASE

Le fichier SQL altersysmodelmsdbtempdb.sql généré précédemment et contenant les commandes ALTER DATABASE pour les bases systèmes model, msdb et tempdb est exécuté avec le binaire sqlcmd.

Le message "The file .... has been modified in the system catalog. The new path will be used the next time the database is started." doit s’afficher pour chaque commande ALTER DATABASE.

Instance par défaut Instance nommée (instancename)
sqlcmd -ialtersysmodelmsdbtempdb.sql -E
sqlcmd -ialtersysmodelmsdbtempdb.sql -E
   -S<ServerName>\instancename
The file "tempdev" has been modified in the system catalog. The new path will be
 used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be
 used the next time the database is started.
…

Déplacement des fichiers et redémarrage en mode normal

Le serveur SQL Server est arrêté avec la commande NET STOP et les fichiers des bases de données model, msdb et tempdb sont déplacés vers leurs nouvelles localisations :

move /Y C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\tempdb.mdf E:\MSSQL\DATA
move /Y C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\templog.ldf E:\MSSQL\DATA
…

Le serveur SQL est redémarré en mode normal avec la commande NET START

Instance par défaut Instance nommée (instancename)
NET START MSSQLSERVER
NET START MSSQL$instancename

L’interrogation de la vue sys.master_files permet de vérifier le déplacement effectif des fichiers pour les bases de données systèmes model, msdb et tempdb.

SELECT db_name(database_id) AS DbName, 
     name, 
     physical_name AS CurrentLocation, 
     state_desc
FROM sys.master_files
WHERE database_id IN (DB_ID('model'),DB_ID('tempdb'),DB_ID('msdb'))
ORDER BY  database_idDbName  name      CurrentLocation             state_desc
-------------------------------------------------------------
tempdb  tempdev   E:\MSSQL\DATA\tempdb.mdf    ONLINE
tempdb  templog   E:\MSSQL\DATA\templog.ldf   ONLINE
…

Déplacement des bases master et mssqlsystemresource

Interdépendance entre la base système Resource et la base master

La base de données Resource mssqlsystemresource est une nouvelle base de données système dans SQL Server 2005 et dépend complètement de la localisation de la base de données master.

Si la base de données master est déplacée, la base de données Resource mssqlsystemresource doit être déplacée également et au même endroit que la base master.

Par ailleurs, la base de données Resource ne doit pas être déplacée dans des systèmes de fichiers NTFS encryptés ou compressés : dans une telle configuration les performances sont dégradées et les migrations impossibles.

Les étapes pour déplacer simultanément les bases master et mssqlsystemresource sont décrites dans les paragraphes qui suivent.

Procédure de déplacement simultané des bases master et mssqlsystemresource

Dans cette procédure, des clés de registres sont modifiées, clés qu’il faut modifier avec de grandes précautions.

Modification des paramètres de démarrage de SQL Server pour la base master dans la base de registres

Les paramètres -d et -l sont toujours donnés au démarrage d’un serveur SQL Server, qu’il s’agisse d’une instance par défaut ou d’une instance nommée. Le paramètre -d donne le chemin du fichier de données de la base master et le paramètre -l le chemin du fichier de journal de transactions de la base master.

Dans le contexte de cet article, SQL Server, qui est une instance par défaut, démarre avec les paramètres de démarrage suivants :

-dC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\master.mdf;
-eC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\LOG\ERRORLOG;
-lC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\mastlog.ldf

Les paramètres de démarrage qui précisent la localisation des fichiers de la base master sont consignés au sein de la base de registres dans la clé HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.<#n>\MSSQLSERVER\Parameters. <#n> identifie l’instance par son numéro #n.

Cas d’une instance par défaut MSSQLSERVER

Pour une instance par défaut MSSQLSERVER, le numéro #n est toujours 1 et la clé est HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER\Parameters.

Cas d’une instance nommée

Pour une instance nommée (par exemple l’instance nommée MOSS_INTERNET), le numéro #n est facilement identifiable grâce à la clé de registre HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL :

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL
Name Type Data
MOSS_INTERNET REG_SZ MSSQL.2
MOSS_INTRANET REG_SZ MSSQL.1

À partir de ces informations, les paramètres de démarrage donnant la localisation de la base master pour l’instance nommée MOSS_INTERNET sont donc stockés dans la clé HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLSERVER\Parameters.

Dans le cas d’une instance par défaut pour exemple, les paramètres de démarrage sont enregistrés dans la base de registres de la façon suivante :

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER\Parameters
Name Type Data
SQLArg0 REG_SZ -dC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\master.mdf
SQLArg1 REG_SZ -eC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\LOG\ERRORLOG
SQLArg2 REG_SZ -lC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\mastlog.ldf

Pour déplacer la base master, les valeurs SQLArg0 et SQLArg2 sont modifiées dans la base de registres afin de refléter la future nouvelle localisation de fichiers de la base master.

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLSERVER\Parameters
Name Type Data
SQLArg0 REG_SZ -dE:\MSSQL\DATA\master.mdf
SQLArg1 REG_SZ -eC:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\LOG\ERRORLOG
SQLArg2 REG_SZ -lE:\MSSQL\DATA\mastlog.ldf

Déplacement de la base master et redémarrage en mode single user

L’instance est arrêtée avec NET STOP et les fichiers de données de la base master master.mdf et mastlog.ldf sont déplacés vers leur future localisation :

move /Y C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\master.mdf E:\MSSQL\DATA
move /Y C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\mastlog.ldf E:\MSSQL\DATA

L’instance ne doit surtout pas être redémarrée en mode normal immédiatement après le déplacement des fichiers de la base master car il reste la base mssqlsystemresource à déplacer également à côté de la base master.

L’instance est redémarrée en mode single user et master-only recovery (-f et -T3608)

Déplacement de la base mssqlsystemresource

Le serveur SQL étant en mode en single user, la localisation des fichiers de la base mssqlsystemresource est modifiée avec les commandes ALTER DATABASE. Les noms logiques du fichier de données et du fichier de journal de la base mssqlsystemresource sont respectivement data et log (non modifiables).

ALTER DATABASE mssqlsystemresource 
    MODIFY FILE (NAME=data, FILENAME= 'E:\MSSQL\Data\mssqlsystemresource.mdf');
go
ALTER DATABASE mssqlsystemresource 
    MODIFY FILE (NAME=log, FILENAME= 'E:\MSSQL\Data\mssqlsystemresource.ldf');
go
The file "data" has been modified in the system catalog. The new path will be
 used the next time the database is started.
The file "log" has been modified in the system catalog. The new path will be
 used the next time the database is started.

Le répertoire de la base mssqlsystemresource est obligatoirement celui de la base master et il est interdit de modifier les noms logiques de la base mssqlsystemresource.

La base de données mssqlsystemresource doit être mise en mode lecture seule :

ALTER DATABASE mssqlsystemresource SET READ_ONLY;
go

L’instance SQL Server est ensuite arrêtée avec NET STOP pour déplacer les fichiers de la base mssqlsystemresource vers leur nouvel emplacement :

move /Y C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\master.mdf E:\MSSQL\DATA
move /Y C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\mastlog.ldf E:\MSSQL\DATA

Vérifications

L’opération de déplacement de la base master et mssqlsystemresource est terminée en redémarrant en mode normal l’instance SQL Server avec NET START. Consulter le fichier de log du serveur pour vérifier le bon redémarrage.