Il est possible depuis SQL Server 2005 de créer des captures instantanées de base de données. Cependant je me suis aperçu que cette fonctionnalité était plutôt méconnue. Dans ce billet, j’essaierais d’expliquer au mieux le fonctionnent des captures instantanées et d’exposer les avantages et les inconvénients quant à leur utilisation.

Tout d’abord qu’est ce qu’une capture instantanée  de bases de données ? On peut le définir comme une copie ou image « virtuelle » en lecture seule d’une base de données à un instant donné. Le terme « Lecture seule » signifie donc qu’il est impossible d’effectuer des opérations de mise à jour au travers de cette copie de base de données. De plus, l’espace requis par cette copie virtuelle est en général moins important car seules les pages de données modifiées sont stockées dans une capture instantanée .

 

La création d’un snapshot

Commençons par créer une base de données de test nommée testdb ainsi qu’une table test1 avec un jeu de données :

CREATE DATABASE testdb
ON  PRIMARY
( NAME = N’testdb’, FILENAME = N’E:\MSSQL\DATAS\USERS\testdb.mdf’ , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’testdb_log’, FILENAME = N’F:\MSSQL\LOGS\testdb_log.LDF’ , SIZE = 576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%);
GO

CREATE TABLE test1
(
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
texte VARCHAR(50)
);
GO

DECLARE @i INT = 0;

WHILE @i < 10000
BEGIN
INSERT INTO dbo.test1 (texte) VALUES (‘TEST’ + CAST(@i AS VARCHAR(5)));
SET @i += 1;
END

Une première remarque importante est que la création d’une capture instantanée ne peut pas se faire depuis l’interface graphique de gestion de SQL Server (SSMS). Nous utiliserons donc la syntaxe TSQL de création qui suit le même schéma d’une base de données classique à quelques différences près :

- On ne spécifie pas de taille de fichier. Ceci s’explique par le fait qu’une capture instantanée de bases de données s’appuie sur la technologie des fichiers fragmentés implémentés avec le système de fichier NTFS. Je reviendrais sur ce point un peu plus loin.

- On ne spécifie pas les références aux groupes de fichiers.

- On ne spécifie pas la référence à un journal des transactions pour des raisons qui peuvent paraitre évidentes.

- Le nom physique des fichiers de données et éventuellement l’extension doivent être changés. Il est préférable de les placer sur un axe de disques différent pour éviter les phénomènes de contention. Nous reviendrons également sur ce point.

- Il faut enfin ajouter la clause AS SNAPSHOT OF et la référence à la base de données source.

 

CREATE DATABASE testdb_snapshot
ON
( NAME = N’testdb’, FILENAME = N’F:\MSSQL\testdb_snapshot.ss’)
AS SNAPSHOT OF testdb;
GO

La capture instantanée de la base testdb est maintenant créée. On peut interroger les données de la table test1 depuis cette capture instantanée.

SELECT TOP 1
    id,
    texte
FROM dbo.test1
ORDER BY id DESC;

et son résultat :

 

Insérons maintenant un jeu de données supplémentaire dans la table test1 de la base de données source testdb :

USE testdb;
GO

DECLARE @i INT = 10001;

WHILE @i < 20000
BEGIN
INSERT INTO dbo.test1 (texte) VALUES (‘TEST’ + CAST(@i AS VARCHAR(5)));
SET @i += 1;
END

Nous devons retrouver retrouver la même valeur max pour colonne id (max(id) = 10000) si l’on interroge la table test1 depuis la capture instantanée testdb_snapshot en utilisant la même requête que précédemment :

USE testdb_snapshot;
GO

SELECT TOP 1
    id,
    texte
FROM dbo.test1
ORDER BY id DESC;

 

Fonctionnement des captures instantanées

Chaque fichier de données d’une capture instantanée est créé en tant que fichier fragmenté (sparse file en anglais). Les fichiers fragmentés sont une spécificité du système de fichier NTFS. Le principe est le suivant : à la création, un fichier fragmenté ne contient pas de données utilisateurs et un faible espace disque est requis. Lorsque des données sont insérées dans ce fichier, celui-ci s’accroît graduellement par incrément de 64 Ko. La taille sur disque d’un fichier fragmenté est toujours un multiple de 64Ko.

Regardons les propriétés du fichier testdb_snapshot.ss précédemment créé dans le répertoire F:\MSSQL :

 

Il faut regarder la valeur « Taille sur le disque » qui est de 128Ko (soit 2 X 64Ko). On remarque ici que nous avons 2 valeurs différentes pour la taille. La valeur de la propriété Taille est la taille maximum du fichier qui correspond à la taille du fichier de données de la base d’origine au moment de la création de la capture instantanée. La valeur de la propriété Taille sur disque correspond à la taille réelle du fichier fragmenté. On remarque ici que la taille du fichier est plus grande que la taille sur disque. Ceci peut paraître déroutant mais il existe une explication à cela. En fait lorsqu’un fichier fragmenté est créé, celui-ci est rempli de 0 ce qui signifie que l’espace sur disque n’est pas encore alloué d’où la différence que l’on peut observer ici.

Une autre façon de visualiser ces valeurs de propriétés est d’utiliser la DMF  sys.dm_io_virtual_files et la DMV sys.master_files.

SELECT
    DB_NAME(m.database_id) AS database_name,
    m.physical_name,
    m.size * 8.0 / 1024 AS maximum_size_in_Mo,
    f.size_on_disk_bytes / 1024 AS size_on_disk_KBytes
FROM sys.dm_io_virtual_file_stats(DB_ID(‘testdb_snapshot’), NULL) AS f
INNER JOIN sys.master_files AS m
ON f.file_id = m.file_id
AND f.database_id = m.database_id;

et son résultat :

Comme nous l’avons dit plus haut, un fichier fragmenté possède donc une taille assez faible à sa création mais ce fichier peut avoir une taille au moins égale à celle du fichier d’origine dans le pire des cas si il existe beaucoup de mises à jour la base de données sources. Il faut donc bien prendre en compte ce paramètre et prévoir l’espace disque nécessaire.

Pour chaque fichier de données d’une base de données possédant une capture instantanée, il existe une matrice de gestion stockée en mémoire. Chaque matrice possède un bit pour chaque page de données qui indique si celle-ci a été copiée ou pas dans le fichier de capture instantanée. Quand une mise à jour est effectuée sur la base de données source, cette matrice est consultée en premier par SQL Server afin de déterminer quelle action doit être effectuée. Une page déjà modifiée ne sera pas copiée vers la capture instantanée.

 

Nous avons donc pour une mise à jour d’une page de données sur la base source une et une seule copie vers la capture instantanée. Ce mécanisme est appelé copie à l’écriture et peut engendrer une surcharge d’activité I/O sur le serveur. Ceci est important surtout lors de la création de plusieurs captures instantanées pour une même base. Le surplus d’activité peut donner lieu à une réelle contention au niveau des disques.

De plus quand une requête lit les données depuis une capture instantanée, celle-ci accède premièrement à la matrice située en cache pour connaître la localisation des pages de données concernées pour pouvoir les lire. Selon le cas, la page sera soit lu à partir de la base de données source soit à partir de la capture instantanée.

 

Un gros avantage de cette lecture de données depuis une capture instantanée est qu’aucun verrou n’est posé pendant toute la durée de l’opération indépendamment du mode de verrouillage activé. Ceci est valable quelque soit la localisation de la page de données (base source ou capture instantanée). Pour s’en convaincre faisons un test :

Forçons la pose d’un verrou exclusif lors d’une sélection sur la table test1 depuis la base de données source testdb :

USE testdb;
GO

BEGIN TRAN
SELECT TOP 2 * FROM dbo.test1 WITH (XLOCK)

Regardons à l’aide de la DMV sys.dm_tran_locks les verrous actifs dans la base testdb :

SELECT
    DB_NAME(resource_database_id) AS database_name,
    resource_type,
    request_type,
    request_mode,
    resource_description
FROM sys.dm_tran_locks;

et son résultat :

On constate l’apparition de verrou de type X et IX sur la base de données testdb. (procédez à un ROLLBACK TRAN pour annuler la transaction en cours)

Faisons le même test cette fois-ci en passant par la capture instantanée testdb_snapshot :

Aucun verrou initié par notre requête n’existe ni sur la base de données testdb ni sur la capture instantanée testdb_snapshot.

Revenons rapidement sur la matrice d’état des pages de données pour la notre base de données. Comme nous l’avons dit précédemment, ce cache est stocké en mémoire et est par conséquent accessible tant que le service SQL Server est démarré ou que la base de données est en ligne. Lorsque le serveur est redémarré ou la base est mise en hors ligne, cette matrice est perdue et doit être reconstituée au prochain redémarrage de la base ou du serveur. Pour cela SQL Server détermine au moment de la lecture d’une page donnée si celle-ci se trouve dans le fichier fragmenté ou dans le fichier source de la base de données et reconstitue au fur et à mesure la matrice d’état des pages pour les utilisations futures.

Lorsque le serveur de bases de données redémarre le ou les fichiers de la capture instantanée est vide. Le point de départ d’une capture instantanée est lorsqu’elle est créée. C’est ce point de départ qui sert de référence à SQL Server pour restaurer une capture instantanée. Ensuite le processus de restauration s’effectue comme pour une base de données classique. Les transactions sont rejouées (phase REDO), les transactions qui n’ont pas été validées sont annulées(phase UNDO) et c’est seulement à ce moment que la capture instantanée est mise en ligne. On récupère ici la version originale des pages de données de la base de données source.

A ce stade certaines restrictions sont à prendre en compte :

- Une capture instantanée ne peut pas être créé sur une base de données système.
- Une capture instantanée hérite des droits de la base de données source. Ces droits ne peuvent pas être changés car une capture instantanée est en lecture seule.
- Si un utilisateur est supprimé sur la base source, celui-ci ne l’est pas dans la capture instantanée. Il peut y avoir une désynchronisation des droits utilisateurs.
- FULLTEXT ne fonctionne pas avec les captures instantanées de base de données.
- La nouvelle fonctionnalité FILESTREAM de SQL Server 2008 ne supporte pas les captures instantanées

 

Restauration d’une base à partir d’une capture instantanée

L’utilisation des captures instantanées présente un autre avantage non négligeable à savoir la rapidité de restauration des bases de données. La commande TSQL correspondante est la suivante :

USE master;
GO

RESTORE DATABASE testdb FROM DATABASE_SNAPSHOT = ‘testdb_snapshot';
GO

Si un problème survient durant le processus de restauration, SQL Server tente de finir l’opération de restauration. Dans ce cas la capture instantanée est marquée comme suspecte et doit être régénérée.

D’autres considérations sont à prendre en compte concernant la restauration à partir d’une capture instantanée :

- Elles ne peuvent pas être sauvegardées, restaurées, détachées ou attachées mais ceci n’affecte en rien les processus de sauvegardes des bases de données sources.
- Si une base de données est mode lecture seule ou est compressée elle ne peut pas être restaurée à partir d’une capture instantané
- Dans une topologie de serveurs en miroir, une base de données en miroir ne peut pas être restaurée à partir d’une capture instantanée. En revanche sur la base de données du serveur principal cette opération est tout à fait possible. 

 

Scénarios d’utilisation

L’utilisation des captures instantanées peut être envisagé dans plusieurs scénarios. Je pourrais pas citer tous les scénarios possibles mais ceux qui suivent permettent de couvrir des cas d’utilisations que j’ai pu rencontrer fréquemment.

• Déchargement de la charge pour les besoins en reporting

Vous avez recensé des besoins en reporting sur vos bases de production et vous ne voulez pas perturber le fonctionnement normal de la production. Les captures instantanées peuvent répondre à ce besoin car comme nous l’avons vu un peu plus haut dans le billet aucun verrou n’est initié depuis une requête lancée sur une capture instantanée.

Il existe cependant quelques inconvénients à cette solution surtout en terme de performances. L’activité I/O engendrée peut être considérablement augmenté dans certains cas. En effet, les pages de données non modifiées depuis la création de la capture instantanée sont directement lu à partir du fichier de la base de données source. Il n’est pas impossible de voir apparaître un phénomène de contention disque à ce niveau. De plus, si les mises à jour de données dans la base source sont fréquentes, les opérations de mise à jour risquent de rencontrer des problèmes de performance car le phénomène de copie par écriture, décrit un peu plus haut dans le billet, va multiplier les opérations d’entrées / sorties. Enfin aucun paramétrage de sécurité ne peut être effectué sur une capture instantanée ce qui limite les possibilités de personnalisation d’accès pour des requêtes de reporting par service par exemple.

• Historisation des données

Vous voulez garder pendant une période déterminée un historique de vos données. Les captures instantanées répondent également à ce besoin. Vous avez la possibilité de créer plusieurs captures instantanées sur votre base source à différent moment et il est tout à fait envisageable de planifier leurs création dans un job SQL. (Ce procédé est également utilisé par SSRS). L’avantage est que l’espace disque nécessaire pour stocker les différents versions d’historiques peut être faible.

Les inconvénients majeures ici restent les mêmes que précédemment. En effet plus il existe de captures instantanées de bases de données, plus l’activité I/O risque de devenir importante (Celle-ci est multiplié par le nombre de capture instantanée créée). Dans ce cas il faut bien veiller à créer les fichiers de captures instantanées sur des axes différents que le fichier de données de la production et limiter le nombre de captures instantanées. Enfin n’oublions pas qu’une capture instantanée est liée à la base de données source. Cela signifie que pour diverses raisons, si vous êtes obligés de restaurer votre base de données source, vos captures instantanées sont définitivement perdus.

• Mise à jour d’application

Les scénarios de mise à jour d’application sont idéales pour utiliser des captures instantanées de bases de données. En effet le scénario classique d’une mise à jour consiste à faire une sauvegarde complète de la base, de mettre à jour l’application et la base de données et éventuellement de refaire une autre sauvegarde complète post mise à jour. Dans ce scénario il faut donc compter avec les temps de sauvegardes des bases de données. Dans certains cas, le temps de sauvegarde peut être extrêmement important par rapport à celui de mise à jour de l’application. L’utilisation d’une capture instantanée peut ici paraître judicieux car comme nous l’avons vu sa création est extrêmement rapide d’une part et il est possible de restaurer une base de données depuis une capture instantanée d’autre part. Ceci permettrait de réduire considérablement la fenêtre de maintenance de mise à jour. Cependant en fonction de la quantité de mise à jour effectuée sur la base de données source, il faudra également prendre en compte le surplus d’activité I/O qui risque de ralentir le processus de mise à jour de l’application.

• Intégration dans les architectures de log shipping et mirroring

Les captures instantanées peuvent être intégrés dans des architectures de serveurs utilisant le log shipping ou le mirroring. Les bases de données pouvant être en mode NORECOVERY dans ces architectures, le seul moyen d’accéder à leurs données est de créer une capture instantanée y faisant référence.

Plus particulièrement dans le cas dans une architecture de serveur en miroir la création d’une capture instantanée fait apparaître une étape supplémentaire lors de la phase de validation d’une transaction dans les modes de haute disponibilité et haute sécurité qui se fait de manière synchrone. La transaction doit être validé sur les 2 serveurs pour être considéré comme viable. L’image ci-dessous illustre ce mécanisme.

 

 

1 : La transaction A est initiée sur le serveur principal.

2: La transaction A est ensuite propagée sur le serveur en miroir.

3: Une 3ème étape supplémentaire consiste à copier la ou les pages modifiées vers la capture instantanée. Ce n’est qu’à ce moment précis que la transaction A peut être considérée comme validée.

Dans le cas où les performances représentent un enjeu important, il faudra bien prendre en compte ce point.

Pour conclure sur ce sujet, je dirais que l’implémentation des captures instantanées doit être quelque chose de réfléchi. Il faut bien peser le pour et le contre en fonction des avantages mais aussi des inconvénients d’une telle solution en fonction des besoins métiers et de l’environnement dans lequel on se trouve. Dans tous les cas, et on ne le redira jamais assez, il faut tester et tester à nouveau pour mesurer l’impact réel d’une telle solution avant la mise en production !!!. Personnellement, je ne conseillerais pas l’implémentation des captures instantanées dans les cas de déchargement de reporting ou d’historisation sur des bases de données qui sont extrêmement sollicités. Cependant dans les scénarios de mise à jour d’applications cela peut s’avérer un bon choix. Certes les mises à jour peuvent être ralentis mais le gain de temps perdu dans cette phase est largement compensé par le temps récupéré sur les temps de sauvegardes. Ici encore rien n’empêche de tester cette solution dans la phase de recette d’une release . On peut simplement regretter qu’une cette fonctionnalité ne soit disponible qu’en version entreprise et développeur.

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon