Sauvegarde

Lors d’une migration de SQL server 2000 vers 2005 dans la même langue, il n’y a pas de recommandations particulières, il suffit d’installer la version 2005 par dessus la version 2000. (cf. copie de base de données).La méthode de migration side-by-side est le scénario le plus simple pour passer de SQL server 7.0/2000 à SQL 2005. En revanche lors d’une migration de SQL server en Français vers SQL server en Anglais des problèmes de compatibilité peuvent apparaître. Dans cet article, la méthode de migration consiste à sauvegarder puis à restaurer les bases de données tout en conservant les logins/mots de passes de SQL Server.

SQL Server 2005 Migration Scenario

1.1 Sauvegarde des bases de données

Pour sauvegarder la base de données il suffit d’ouvrir Entreprise Manager puis de sauvegarder la base de données via l’assistant ou alors de simplement exécuter l’ordre SQL vu ci-dessus. Exemple :

BACKUP

database ”

AdventureWorks

” to disk=’

D:\BackupDB_MyServer\AdventureWorks.BAK

InformationsPlus d’informations sur la commande BACKUP (Transac-SQL)

1.2 Sauvegarde des logins/connexions avec mots de passes

Lors de transfert d’une base de données entre deux instances de SQL Server, il faut penser également à recréer les logins et mot de passes afin que les applications qui accèdent à la base puissent continuer à fonctionner normalement. Lorsque qu’une seule base de données est hébergée avec un ou deux logins l’opération peut se faire manuellement. Cependant, avec de nombreux logins l’opération de création des logins/mot de passes devient très fastidieuse et longue. Pour sauvegarder les logins avec leurs mot de passes respectifs nous avons utilisés une version modifiée de la commande sp_help_revlogin qui permet l’utilisation des nouvelles commandes DDL de SQL Server 2005.

Le script sp_help_revlogin doit être exécuté dans SQL Management Studio. Vous pouvez installer SQL Management Studio Express (SSMSE) qui est gratuit. Il vous permet de vous connecter à des serveurs SQL 2000 et SQL Server 2005, toutefois cet outil est en anglais.Télécharger SQL Management Studio Express

Script sp_help_revlogin pour SQL Server 2005

USE

master GO

IF

OBJECT_ID

(

‘sp_hexadecimal’

)

IS

NOT

NULL

DROP

PROCEDURE

sp_hexadecimal GO

CREATE

PROCEDURE

sp_hexadecimal @binvalue

varbinary

(

256

),

@hexvalue

varchar

(

514

)

OUTPUT ASDECLARE

@charvalue

varchar

(

514

)

DECLARE

@i

int DECLARE

@length

int DECLARE

@hexstring

char

(

16

)

SELECT

@charvalue

=

‘0x’

SELECT

@i

=

1

SELECT

@length

=

DATALENGTH

(

@binvalue

)

SELECT

@hexstring

=

‘0123456789ABCDEF’

WHILE

(

@i

<=

@length

)

BEGIN

DECLARE

@tempint

int

DECLARE

@firstint

int

DECLARE

@secondint

int

SELECT

@tempint

=

CONVERT

(

int

,

SUBSTRING

(

@binvalue

,

@i

,

1

))

SELECT

@firstint

=

FLOOR

(

@tempint

/

16

)

SELECT

@secondint

=

@tempint

(

@firstint

*

16

)

SELECT

@charvalue

=

@charvalue

+

SUBSTRING

(

@hexstring

,

@firstint

+

1

,

1

)

+

SUBSTRING

(

@hexstring

,

@secondint

+

1

,

1

)

SELECT

@i

=

@i

+

1

END SELECT

@hexvalue

=

@charvalue GO

IF

OBJECT_ID

(

‘sp_help_revlogin’

)

IS

NOT

NULL

DROP

PROCEDURE

sp_help_revlogin GO

CREATE

PROCEDURE

sp_help_revlogin @login_name

sysname

=

NULL

AS DECLARE

@name

sysname DECLARE

@type

varchar

(

1

)

DECLARE

@hasaccess

int DECLARE

@denylogin

int DECLARE

@is_disabled

int DECLARE

@PWD_varbinary

varbinary

(

256

)

DECLARE

@PWD_string

varchar

(

514

)

DECLARE

@SID_varbinary

varbinary

(

85

)

DECLARE

@SID_string

varchar

(

514

)

DECLARE

@tmpstr

varchar

(

1024

)

DECLARE

@is_policy_checked

varchar

(

3

)

DECLARE

@is_expiration_checked

varchar

(

3

)

IF

(

@login_name

IS

NULL)

DECLARE

login_curs

CURSOR

FOR SELECT

p

.

sid

,

p

.

name

,

p

.

type

,

p

.

is_disabled

,

l

.

hasaccess

,

l

.

denylogin

FROM

sys.server_principals

p

LEFT

JOIN

sys.syslogins

l

ON

(

l

.

name

=

p

.

name

)

WHERE

p

.

type

IN

(

‘S’

,

‘G’

,

‘U’

)

AND

p

.

name

<>

‘sa’

ELSE

DECLARE

login_curs

CURSOR

FOR SELECT

p

.

sid

,

p

.

name

,

p

.

type

,

p

.

is_disabled

,

l

.

hasaccess

,

l

.

denylogin

FROM

sys.server_principals

p

LEFT

JOIN

sys.syslogins

l

ON

(

l

.

name

=

p

.

name

)

WHERE

p

.

type

IN

(

‘S’

,

‘G’

,

‘U’

)

AND

p

.

name

=

@login_name

OPEN

login_curs

FETCH

NEXT

FROM

login_curs

INTO

@SID_varbinary

,

@name

,

@type

,

@is_disabled

,

@hasaccess

,

@denylogin

IF

(

@@fetch_status

=

1

)

BEGIN

PRINT

‘No login(s) found.’

CLOSE

login_curs

DEALLOCATE

login_curs

RETURN

1

END SET

@tmpstr

=

‘/* sp_help_revlogin script ‘

PRINT

@tmpstr

SET

@tmpstr

=

‘** Generated ‘

+

CONVERT

(

varchar

,

GETDATE

())

+

‘ on ‘

+

@@SERVERNAME

+

‘ */’

PRINT

@tmpstr

PRINT

WHILE

(

@@fetch_status

<>

1

)

BEGIN

IF

(

@@fetch_status

<>

2

)

BEGIN

PRINT

SET

@tmpstr

=

‘– Login: ‘

+

@name

PRINT

@tmpstr

IF

(

@type

IN

(

‘G’

,

‘U’

))

BEGIN

— NT authenticated account/group

SET

@tmpstr

=

‘CREATE LOGIN ‘

+

QUOTENAME

(

@name

)

+

‘ FROM WINDOWS’

END

ELSE

BEGIN

— SQL Server authentication

— obtain password and sid

SET

@PWD_varbinary

=

CAST

(

LOGINPROPERTY

(

@name

,

‘PasswordHash’

)

AS

varbinary

(

256

)

)

EXEC

sp_hexadecimal @PWD_varbinary

,

@PWD_string OUT

EXEC

sp_hexadecimal @SID_varbinary

,

@SID_string OUT

— obtain password policy state

SELECT

@is_policy_checked

=

CASE

is_policy_checked

WHEN

1

THEN

‘ON’

WHEN

0

THEN

‘OFF’

ELSE

NULL

END

FROM

sys.sql_logins

WHERE

name

=

@name

SELECT

@is_expiration_checked

=

CASE

is_expiration_checked

WHEN

1

THEN

‘ON’

WHEN

0

THEN

‘OFF’

ELSE

NULL

END

FROM

sys.sql_logins

WHERE

name

=

@name

SET

@tmpstr

=

‘CREATE LOGIN ‘

+

QUOTENAME

(

@name

)

+

‘ WITH PASSWORD = ‘

+

@PWD_string

+

‘ HASHED, SID = ‘

+

@SID_string

IF

(

@is_policy_checked

IS

NOT

NULL

)

BEGIN

SET

@tmpstr

=

@tmpstr

+

‘, CHECK_POLICY = ‘

+

@is_policy_checked

END

IF

(

@is_expiration_checked

IS

NOT

NULL

)

BEGIN

SET

@tmpstr

=

@tmpstr

+

‘, CHECK_EXPIRATION = ‘

+

@is_expiration_checked

END

END

IF

(

@denylogin

=

1

)

BEGIN

— login is denied access

SET

@tmpstr

=

@tmpstr

+

‘; DENY CONNECT SQL TO ‘

+

QUOTENAME

(

@name

)

END

ELSE

IF

(

@hasaccess

=

0

)

BEGIN

— login has exists but does not have access

SET

@tmpstr

=

@tmpstr

+

‘; REVOKE CONNECT SQL TO ‘

+

QUOTENAME

(

@name

)

END

IF

(

@is_disabled

=

1

)

BEGIN

— login is disabled

SET

@tmpstr

=

@tmpstr

+

‘; ALTER LOGIN ‘

+

QUOTENAME

(

@name

)

+

‘ DISABLE’

END

PRINT

@tmpstr

END

FETCH

NEXT

FROM

login_curs

INTO

@SID_varbinary

,

@name

,

@type

,

@is_disabled

,

@hasaccess

,

@denylogin

END CLOSE

login_curs

DEALLOCATE

login_curs

RETURN

0 GO

Vous pouvez télécharger ce script : ScriptSQLLOGIN2K5_sp_help_revlogin.sql

Il suffit ensuite d’exécuter sp_help_revlogin.sql dans SQL Management Studio. L’exécution de ce script va nous fournir en sortie les différents logins, les SID associés et les mot de passes sous forme hashées.

Il faut donc sauvegarder le résultat de ce script pour la restauration et de ne pas le laisser à la portée de tout le monde.

Exemple de script généré par sp_helprevlogin

— Login: SQLUser

CREATE LOGIN

SQLUser

WITH PASSWORD

=0x010058758533930C53711c9537957A06A7D379A12364A1A2B02BF00B672C9534981E8DFC16168DF28E7B2AA844BD

HASHED,SID

=0xEBE4B4FE18C7F94DBF7a7FA1234C1633

Vous avez donc sauvegardé les bases de données ainsi que les logins/mot de passes pour les transférer à la nouvelle instance de SQL Server 2005. Passons maintenant à la restauration !

.style1 { text-align: center; }

restauration

1.1 Installation unattended.

Dans cette partie, nous allons aborder l’installation automatique de SQL server 2005.

Pour cela, il suffit de créer un fichier de réponse dont un exemple ce trouve sur le CDROM de SQL server 2005 dans le dossier Tools\template.ini

Voici un exemple de fichier de réponse que l’on peut créer pour une installation basique, avec un compte de service SQL_SERVEUR utilisé afin de lancer les différents services de SQL server 2005 :

PIDKEY : Il faut rentrer ici le numéro de série sans les “-”

ADDLOCAL : permet de spécifier les composants à installer.

Voici la liste des composants pouvant être installés, suivis de la commande permettant des les sélectionner pour l’installation unattended.

L’installation des composants est case sensitive !Pour installer tous les composants, il suffit de mettre :  “ADDLOCAL=ALL”

INSTANCENAME=<nomdel’instance> : le nom de l’instance que vous voulez créer. Ce paramètre est obligatoire. Pour utiliser le nom d’instance par défaut, il faut mettre : MSSQLSERVER

SQLACCOUNT, AGTACCOUNT, ASACCOUNT,SQLBROWSERACCOUNT : Il faut spécifier un compte qui permettra de démarrer ces 4 services (SQL Server, SQL Server Agent, Analysis Services et SQL Browser)

SQLPASSWORD, AGTPASSWORD, ASPASSWORD, SQLBROWSERPASSWORD : Après avoir défini le ou les comptes qui démarreront les différents services, il faut bien sur rentrer le mot de passe pour chacun des comptes. Vous pouvez remarquer que les mots de passe sont stockés en clair dans le fichier, il faudra donc faire attention à l’endroit ou vous stockerez ce fichier, ainsi qu’aux personnes qui y ont accès.

SECURITYMODE=SQL : Spécifie le mode d’authentification mixte. Si se paramètre n’est pas défini, c’est l’authentification Windows qui sera utilisée. Le mode d’authentification mixte permet la connexion a SQL server grâce à des comptes Windows (de domaine ou locaux) mais également avec des comptes stockés dans la base de données. Cette méthode d’authentification est moins sûre mais permet à des application ne supportant pas l’authentification intégrée à Windows de se connecter à votre base de données.

SAPWD=”[email protected]” : Si le mode d’authentification est mixte, il faut obligatoirement définir le mot de passe pour l’utilisateur SA, il vaut mieux choisir un mot de passe fort.

SQLAUTOSTART=1 : Cette ligne permet de spécifier le mode de démarrage du service. A 1 le service sera démarré automatiquement, à 0 il faudra lancer le service manuellement. Si aucune valeur n’est spécifiée, le démarrage du service sera défini sur manuel. Pour les autre services, il faudra utiliser les commandes suivantes :

  • SQLBROWSERAUTOSTART pour SQL Browser
  • AGTAUTOSTART pour SQL Server Agent
  • ASAUTOSTART pour Analysis Services

Il y a évidement beaucoup d’autres paramètres qui peuvent être configurés via ce fichier, notamment pour la réplication de base de données, ou l’installation sur un cluster. Pour plus d’informations sur ces paramètres, vous pouvez vous référer au fichier template.iniqui contient la liste exhaustive de toutes les options, avec une explication sur chacune d’entre elles.

Une fois le fichier créer, il faut lancer la commande suivante pour commencer l’installation :

CDROM:\servers\setup.exe /settings %chemindufichier.ini% /qn 

Informations Le commutateur /qn permet d’avoir une installation silencieuse, sans afficher d’interface graphique. Néanmoins, à des fin de débogage, vous pouvez utiliser le commutateur /qb qui affichera les informations d’avancement de l’installation.

1.2. la restauration des bases et des logins.

Une fois l’installation de SQL Server 2005 effectuée, le plus gros est fait, il ne reste plus qu’à restaurer les bases et également les logins de connexion.

Pour restaurer les bases, il y a deux possibilités :

  • Via la console Microsoft SQL Server Management Studio :

Un clic droit sur “Databases” puis il faut sélectionner “Restore Database…” et parcourir le disque dur à l’endroit de votre sauvegarde.

  • Via un script :

Un script se révèlera plus efficace que la console de gestion dans le cas ou l’on a plusieurs bases à restaurer ou si l’on souhaite agir sur la base de données lors de la restauration, par exemple pour modifier son emplacement.

RESTORE database

“MaBaseDeDonnées”

from disk

=

‘D:\Bases Sauvegardées\MaBaseDeDonnées.BAK’

RESTORE database

“BDDMalPlacée”

from disk

=’

‘D:\Bases Sauvegardées\BDDMalPlacée.BAK’

WITH MOVE

‘BDDMalPlacée_data’

TO

‘D:\SQL\bases\BDDMalPlacée_data.mdf’

,

MOVE

‘BDDMalPlacée_Log’

TO

‘D:\SQL\bases\BDDMalPlacée_Log.ldf’

Il faudra alors écrire son script à l’aide de Notepad ou équivalent puis de le renommer en.sql. Un double clic sur ce fichier exécutera le script dans la console de management.

Pour restaurer les logins, il suffit d’exécuter le script que vous avez généré à l’aide de sp_help_revlogin.sql dans Management Studio.

Conclusion

Nous avons vu ici comment migrer une base de données sous SQL 2000 vers le dernier SGBDR de la suite Windows Server System de Microsoft : SQL server 2005. La principale difficulté ici était le changement de la langue du programme qui peut provoquer des problèmes de compatibilité notamment au niveau des logins de connexion à la base de données.

Vous pouvez également remarquer que toutes les actions ont la possibilité d’être scriptées, afin d’automatiser au maximum la migration, et par la même occasion éviter des erreurs humaines au moment de cette migration, si vous avez pris soin de tester tous vos scripts avant.