Déplacer les bases systèmes SQL Server 2005

1- 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.

2- 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é au paragraphe 3).

2-1- La vue sys.master_files

La vue sys.master_files permet d'obtenir rapidement 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, 
     state_desc
FROM sys.master_files
WHERE database_id IN (DB_ID('model'),DB_ID('tempdb'),DB_ID('msdb'))
ORDER BY  database_id

DbName  name      CurrentLocation                                       state_desc
------------------------------------------------------------------------------------
tempdb  tempdev   C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\tempdb.mdf    ONLINE
tempdb  templog   C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\templog.ldf   ONLINE
model   modeldev  C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\model.mdf     ONLINE
model   modellog  C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\modellog.ldf  ONLINE
msdb    MSDBData  C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\MSDBData.mdf  ONLINE
msdb    MSDBLog   C:\MSSQL\MSSQLSERVER\MSSQL.1\MSSQL\DATA\MSDBLog.ldf   ONLINE

2-2- 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.

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

2-3-1- 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 :

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 :

E:\>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

NET START MSSQLSERVER /f /T3608

NET START MSSQL$instancename  /f /T3608

E:\>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. This an informational message only. No user action is required.
2009-11-25 18:25:21.34 spid4s      Recovering only master database because traceflag 3608 was specified. This is an informational message only. No user action is required.
2009-11-25 18:25:21.34 spid4s      Starting up database 'master'.

2-3-2- 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.

E:\>sqlcmd -ialtersysmodelmsdbtempdb.sql -E

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.
 ...

2-3-3- Déplacement des fichiers et redémarrage en mode normal

Le serveur SQL Server est arrêté avec la commande NET STOP (cf § 2-3-1) 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

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_id

DbName  name      CurrentLocation             state_desc
-------------------------------------------------------------
tempdb  tempdev   E:\MSSQL\DATA\tempdb.mdf    ONLINE
tempdb  templog   E:\MSSQL\DATA\templog.ldf   ONLINE
 ...

3- Déplacement des bases master et mssqlsystemresource

3-1- 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.

3-2- 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.

3-2-1- 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 indique le chemin du fichier de données de la base master et le paramètre -l indique le chemin du fichier de journal 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. MSSQL où <#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 :

À 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 :

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.

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

L'instance est arrêtée avec NET STOP (cf § 2-3-1) 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) (cf §2-3-1)

3-2-3- 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

3-2-4- 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.

Annexe