Datafly.pro

Pourquoi répartir ?

Une répartition des données et index dans plusieurs groupes de fichiers différents vont nous permettre :

• d’avoir un meilleur contrôle sur l’activité disque, fichier par fichier, afin de pouvoir déplacer sur un autre disque la partie de la base particulièrement sollicitée
• d’assurer un meilleur parallélisme des I/O,
• de faciliter certaines tâches de maintenance qui peuvent être rendues difficiles avec une base volumineuse : DBCC CHECKFILEGROUP au lieu de CHECKDB, mise en lecture seule de groupes de fichiers, sauvegardes partielles, etc…

Comment organiser la base de données ?

Premier problème, comment organiser les tables et index en filegroups ? Pour cela, il n’y a pas de recette absolue, mais quelques règles :

• Envisager un nombre raisonnable de groupes de fichiers sauf besoin particulier (une dizaine comme maximum, par exemple)
• Trouver une répartition logique : par schéma ? par domaine fonctionnel ?
• Trouver une répartition équitable du volume et évitez les groupes de fichiers quasi vides ou trop petits,
• Si certaines tables sont beaucoup plus sollicitées que les autres, n’hésitez pas à leur allouer un groupe de fichier.
• Pour les tables extrêmement volumineuses représentant un fort pourcentage du volume de la base, leur allouer un groupe de fichier. Pour aller plus loin si le cas l’exige, envisager de séparer leurs index (nonclustered) dans un autre groupe, voire allez jusqu’à penser au partitionnement de tables et d'index
• Ne plus rien stocker dans le groupe de fichiers PRIMARY, qui restera donc réservé aux objets système. Désigner un autre groupe de fichier en tant que groupe par défaut.
• Stocker les LOB (large object binary, varchar(max) et varbinary(max) dans des groupes de fichiers à part
• Stocker les données archivées dans des groupes de fichiers à part

Un exemple bien réparti :

Comment déplacer les tables vers d’autres groupes de fichiers ?

L’idéal, bien sûr, était d’y penser avant, au moment de la conception… Seulement voilà : maintenant c’est trop tard et des données sont déjà dans les tables ! Le problème va donc être de déplacer nos données vers la nouvelle organisation, avec le moins de soucis possibles.
Pour l’exemple, créons une base de données :

CREATE DATABASE DB_TEST_FG;
GO

Créons deux tables, contenant quelques données

USE DB_TEST_FG;
GO

CREATE TABLE Table1
(ID INT IDENTITY CONSTRAINT PK_Table1 PRIMARY KEY,
DATA1 VARCHAR(30) NOT NULL,
DATA2 VARCHAR(30) NULL)

INSERT Table1 (DATA1, DATA2) VALUES ('Microsoft','Redmond') 

CREATE TABLE Table2
(ID INT  IDENTITY CONSTRAINT PK_Table2 PRIMARY KEY,
DATA VARCHAR(30),
PHOTO VARBINARY(MAX))

INSERT Table2 (DATA, PHOTO) VALUES ('Paysage',0x6543144671AA)

Pour préparer notre répartition, créons 2 groupes de fichiers : FG_DATA (par défaut) et FG_LOB (pour les LOB - Large Object Binary, comme les varchar(max) ou varbinary(max))

ALTER DATABASE DB_TEST_FG ADD FILEGROUP FG_DATA;
GO
ALTER DATABASE DB_TEST_FG ADD FILE
    (NAME = 'F_DATA',
     FILENAME = 'C:\SQL\DB_TEST_FG_DATA.ndf',
     SIZE = 10 MB,
     FILEGROWTH = 10 MB)
    TO FILEGROUP FG_DATA;
GO
ALTER DATABASE [DB_TEST_FG] MODIFY FILEGROUP [FG_DATA] DEFAULT
GO

ALTER DATABASE DB_TEST_FG ADD FILEGROUP FG_LOB;
GO
ALTER DATABASE DB_TEST_FG ADD FILE
    (NAME = 'F_LOB',
     FILENAME = 'C:\SQL\DB_TEST_FG_LOBS.ndf',
     SIZE = 10 MB,
     FILEGROWTH = 10 MB)
    TO FILEGROUP FG_LOB;
GO

Pour déplacer une table vers un autre groupe de fichier, vous pourriez bien sûr créer une nouvelle table et y transférer les données, mais il y a beaucoup plus simple :

• Il suffit de reconstruire son index CLUSTERED sur la nouvelle destination. Comme un index clustered c’est la table elle-même, les données seront déplacées (s’il n’y avait pas d’index CLUSTERED, nous pourrions en créer un temporaire juste pour effectuer le déplacement).
• Pour effectuer l’opération sur un index existant, nous pouvons utiliser la syntaxe CREATE INDEX WITH (DROP_EXISTING = ON) : cela fonctionne aussi pour les contraintes de clé primaire qui s’appuient sur un index clustered (à condition que les caractéristiques de l’index recréé soient rigoureusement identiques). Notez que si vous possédez l’édition Enterprise, les reconstructions d’index peuvent être faites avec l’option ONLINE. Déplaçons donc nos 2 tables :

CREATE UNIQUE CLUSTERED INDEX PK_Table1 ON Table1 (ID) WITH (DROP_EXISTING=ON) ON FG_DATA 
CREATE UNIQUE CLUSTERED INDEX PK_Table2 ON Table2 (ID) WITH (DROP_EXISTING=ON) ON FG_DATA

C’est certes bien pratique, avec il y a une limitation tout de même : si votre table contient des colonnes LOB, leur contenu n’est pas déplacé, comme nous le voyons dans les propriétés de la Table2, où le contenu de la colonne PHOTO est resté sur le groupe de fichiers PRIMARY :

Il existe une astuce pour déplacer les LOB, en utilisant le partitionnement de table. Le partitionnement de tables existe maintenant aussi dans l'édition Standard depuis le SP1 : si vous avez une version plus ancienne en édition standard, vous êtes condamné à créer une nouvelle table et à y réinsérer vos données !
En effet, lorsque l’on déplace une table vers un schéma de partition (donc lorqu’on la partitionne), les LOB sont bien déplacés avec (cela fonctionne aussi dans l’autre sens avec le « dé-partitionnement »). Voyons l'exemple :

-- Créons la fonction et le schéma de partition (pointant sur FG_LOB)
CREATE PARTITION FUNCTION PF_MOVE_HELPER (INT) AS RANGE RIGHT FOR VALUES (0);
CREATE PARTITION SCHEME   PS_MOVE_HELPER AS PARTITION PF_MOVE_HELPER ALL TO (FG_LOB);
GO
-- Déplaçons la Table2 vers le schéma de partition (partitionnons la table)
CREATE UNIQUE CLUSTERED INDEX PK_Table2 ON Table2 (ID) WITH (DROP_EXISTING=ON) ON PS_MOVE_HELPER(ID)
-- Départitionnons la table
CREATE UNIQUE CLUSTERED INDEX PK_Table2 ON Table2 (ID) WITH (DROP_EXISTING=ON) ON FG_LOB

Notre table a bien été déplacée avec les LOB :
 

Un dernier déplacement des données en ligne seulement, et notre table sera conforme à nos spécifications : données en ligne stockées sur FG_DATA, et LOBS stockés sur FG_LOB :

CREATE UNIQUE CLUSTERED INDEX PK_Table2 ON Table2 (ID) WITH (DROP_EXISTING=ON) ON FG_DATA

Voilà le résultat :

Pour finir et vous simplifier la chose, il existe une procédure stockée qui vous permettra de simplifier ces opérations de déplacement (même avec des LOB !) Vous la trouverez sur mon script center à l'adresse suivante : http://scripts.mssql.fr/knowledge-base/deplacer-des-tables-entre-filegroups/

Bonnes manipulation de stockage !