FLASH INFORMATIQUE FI



SQL Server 2008 - le fin du fin pour vos données




Thierry CHARLES


Microsoft nous gracie d’une nouvelle mouture de son célèbre moteur de base de données. Bon, autant le dire tout de suite, cette version n’est pas aussi révolutionnaire que le passage de SQL Server 2000 à SQL Server 2005 ; elle aurait même pu se nommer SQL Server 2005 R2. Mais... mais... il y a quand même plein de petites choses extrêmement sympathiques que nous allons passer en revue dans cet article.

Des nouveautés pour vos données

Date & Time

Il y a quatre nouveaux types de données liées à la gestion du temps (pas la météo bien sûr). Tout d’abord deux types (très) simples, tellement simples qu’on se demande pourquoi ils n’existaient pas avant : DATE qui stocke uniquement une date, sans référence à une heure particulière et TIME qui stocke une heure avec une précision de 100 nS. Le traditionnel DATETIME permet maintenant de lui préciser un fuseau horaire et le nouveau DATETIME2 qui reprend toutes les fonctionnalités de DATETIME, mais avec une résolution de 100 nS.

HierarchyID

Ce type de données a été spécialement conçu pour faciliter la gestion de données hiérarchiques arborescentes (comme dans l’annuaire LDAP de l’école, par exemple) et offre de nombreuses méthodes pour manipuler ces données structurées, comme la recherche d’un ancêtre, la détermination du niveau, la liste des enfants, etc.

Geometry et Geography

De nombreux utilisateurs (clients !!!) ont demandé l’intégration de données spatiales dans SQL Server. C’est maintenant chose faite avec les types : GEOMETRY qui représente une coordonnée dans un espace plan et GEOGRAPHY qui, lui, donne un point sur une sphère. Bien sûr, de nombreuses méthodes sont à disposition pour manipuler ce type de données.
Voici un exemple de requête pour trouver tous les utilisateurs situés dans une région donnée :

DECLARE @MAREGION GEOGRAPHY = GEOGRAPHY::STPolyFromText(‘ POLYGON ((43 5.5), (45 6.1), (44.8 6.05), (43 5.5))' 4326)
SELECT * FROM UTILISATEURS WHERE @MAREGION.STIntersect(UTILISATEURS.COORDONNEES) = 1

L’intégration avec ADO.NET est tout aussi triviale, car elle utilise les types geography et geometry disponibles dans le framework :

DIM g as SqlGeography.Point(Latitude, Longitude, 4326)

Il est possible, pour un rendu visuel, de s’appuyer sur Virtual Earth ou MapPoint (et théoriquement GoogleEarth, mais je ne l’ai pas essayé !).

Table-Valued parameter

Il existe déjà un type TABLE que l’on peut manipuler, mais il est malheureusement impossible de le passer comme paramètre d’une procédure stockée ou d’une fonction. C’est maintenant possible en utilisant les TABLE-VALUED parameter :

CREATE TYPE IDUtilisateurs as TABLE (UserID INT, Username NVARCHAR(50));
CREATE PROCEDURE spEffaceUtilisateurs@UtilAEffacer IDUtilisateurs READONLY AS ...

Des nouveautés dans le langage

Quelques petites nouveautés, qui, si elles n’ont rien d’exceptionnel, permettent d’améliorer et de faciliter l’usage quotidien.

Opérateurs

Il est maintenant possible, comme sous un langage .NET, d’effectuer une opération et de réaffecter le résultat en une seule instruction :

SET @idx += 1

Déclaration et assignation de variable

On peut désormais déclarer une nouvelle variable et lui assigner directement une valeur :

DECLARE @toto AS INT = 1234

Instruction MERGE

Cette instruction permet de fusionner à la volée deux jeux de données. Par exemple, vous pouvez gérer l’insertion d’une nouvelle fiche ou la mise à jour d’une fiche déjà existante en une seule passe :

MERGE UTILISATEURS AS DESTINATION
USING (SELECT * FROM TABLEAINSERER) AS SOURCE
WHEN MATCHED THEN
ON (DESTINATION.IDUTILISATEUR = SOURCE.IDUTILISATEUR) UPDATE SET DESTINATION.NOM = SOURCE.NOM,
    DESTINATION.PRENOM = SOURCE.PRENOM
WHEN NOT MATCHED BY TARGET THEN
INSERT (IDUTILISATEUR, NOM, PRENOM)

  VALUES (SOURCE.IDUTILISATEUR, SOURCE.NOM,
          SOURCE.PRENOM)
;

Cela évite de devoir faire une requête uniquement pour savoir si l’enregistrement existe déjà.

Insertion multiple

L’instruction INSERT s’enrichit avec la possibilité d’ajouter plusieurs enregistrements en une seule opération :

INSERT UTILISATEURS (NOM, PRENOM)
VALUES (‘Zufferey', ‘Christian')
VALUES (‘Charles', ‘Thierry')
VALUES (‘Toto', ‘Tutu')
;

Des nouveautés dans les requêtes

Index filtrés

Il est possible de créer un index soumis à une condition. Cela permet d’optimiser la taille de la table d’index et d’exclure de celle-ci des enregistrements non pertinents. Par exemple, vous avez une table des utilisateurs et désirez créer un index sur le champ NO_CARTE_CREDIT. Seuls 40% de vos utilisateurs ont une carte de crédit. Il est possible de créer un index filtré qui ne contiendra que les utilisateurs où le champ en question est renseigné :

CREATE INDEX IX_CARTECREDIT ON UTILISATEURS (NO_CARTE_CREDIT ASC)
WHERE NO_CARTE_CREDIT IS NOT NULL

IFTS (Integrated Full Text Search)

La fonctionnalité de recherche FullText existe depuis un moment dans SQL Server, mais elle s’appuyait sur un processus externe, à savoir le fameux Windows Search qui n’est pas dépendant de SQL Server. Avec SQL Server 2008, cette fonctionnalité à été intégrée au moteur de base de données. Cette intégration permet un gain de performances absolument fabuleux, car l’optimiseur intégré peut mixer des recherches FullText avec des opérateurs de jointure ou de filtrage. IFTS apporte également son lot de nouveautés dans les méthodes afférentes comme la gestion de mots interdits ou la consultation des mots indexés.

Des nouveautés dans le stockage des données

Colonnes SPARSE

Le concept de colonnes SPARSE représente des colonnes avec une faible densité de données. Par exemple, dans une table PRODUITS, la colonne REMARQUES ne sera remplie que fort peu souvent. Le fait d’indiquer à SQL Server qu’une colonne est de type SPARSE permet de récupérer l’espace occupé normalement par les valeurs NULL. Ce gain permet d’optimiser aussi bien le stockage que la manipulation de données, il y a moins d’octets à lire :

CREATE TABLE PRODUITS (
  ID_PRODUIT INT NOT NULL IDENTITY (1,1),
  DESIGNATION NVARCHAR(50) NOT NULL,
  PRIX FLOAT NOT NULL,
  REMARQUES NVARCHAR(100) SPARSE NULL)
;

Filestream

On peut depuis longtemps stocker des fichiers ou des objets binaires (BLOB). Depuis 2005, on les stocke dans un champ du type VARBINARY(max). Les options de stockage des tables permettent sans problème de stocker de très grosses quantités de données avec un bon niveau de performances, cependant, il y avait un cas où SQL Server n’était pas très à l’aise : celui de fichiers (champs) contenant un stream continu de données (par exemple un fichier vidéo ). En effet dans ce cas il monopolise beaucoup de ressources, car il doit placer la totalité du contenu du champ dans un buffer de transfert. FILESTREAM permet de contourner ce problème en stockant la donnée sous la forme d’un fichier NTFS classique, ce qui permet de récupérer simplement un pointeur sur le fichier et de lire les données sous la forme d’un stream :

CREATE TABLE VIDEODATA (
  ID_VIDEO INT PRIMARY KEY,
  VIDEO VARBINARY(MAX) FILESTREAM NULL);

Des nouveautés dans les performances

Compression des données

SQL Server 2008 supporte la compression à tous les niveaux, le plus évident et le plus utilisé est dans les backups. Mais il est également possible de compresser directement les données d’une table, d’un index ou d’une partition. Il est évident que la compression apporte son lot d’avantages comme une utilisation optimale de l’espace disque et une diminution du volume de données transférées lors d’une requête, mais cela se fait au prix d’une charge CPU plus élevée. Pour une bonne utilisation de la compression, il est indispensable d’effectuer une batterie de tests avec le profiler par exemple.

CREATE TABLE UTILISATEURS (IDUTILISATEUR INT PRIMARY KEY NOT NULL IDENTITY(1,1),
 NOM NVARCHAR(50) NULL,
 PRENOM NVARCHAR(50) NULL)
WITH (DATA_COMPRESSION = PAGE)

Resource Governor

Cette fonctionnalité est l’une des plus attendues de SQL Server. Il permet de gérer exactement les ressources du serveur (CPU, Mémoire). Fini les transactions lourdes qui vampirisent le serveur, ne laissant quasiment rien pour d’autres clients. Il est maintenant possible de définir des limitations et plans de charge par connexion ou par application. Cela permet également de définir des priorités entre différentes applications.

Et encore...

Il serait possible de citer encore beaucoup d’améliorations dans le cadre des processus d’optimisation, des plans d’exécution, du parallélisme, etc.

Des nouveautés dans la sécurité

Chiffrement transparent

Il existe déjà des API permettant de chiffrer des champs et toute une hiérarchie de gestion des clés et des certificats pour gérer la sécurité des données. Cependant, le chiffrement et le décodage devaient se faire au moment de l’utilisation des données en T-SQL, ce qui avait pour conséquence d’alourdir le code. Il est maintenant possible de chiffrer directement toute une base de données, et ce, de manière totalement transparente pour tous les utilisateurs :

ALTER DATABASE DBO.PASSWORD
SET ENCRYPTION ON

Utilisation de clés externes

Il est maintenant possible d’utiliser une gestion centralisée des clés de chiffrement (PKI).

Des nouveautés pour les administrateurs

Policy Framework (Administration par Règles)

Cette fonctionnalité permet - enfin - aux administrateurs d’édicter des règles de gestion et de nommage pour les bases de données et de les faire respecter ! Cela est réalisé en définissant des conditions, par exemple que toutes les tables doivent faire partie d’un schéma spécifique autre que DBO. On définit ensuite une règle de gestion et on peut évaluer le résultat sur les bases déjà existantes. Il est également possible de forcer une règle à tout moment et ainsi interdire l’exécution d’un script ou d’une requête qui ne s’y conformerait pas. Attention cependant au syndrome Je comprends pas, tout fonctionnait parfaitement avant et maintenant y’a tout qui plante !!!

Fonctions d’audit

Cette fonctionnalité ressemble beaucoup au TRACE déjà existant, mais elle s’adresse plus aux administrateurs qu’aux développeurs. Elle permet d’auditer certaines opérations et d’enregistrer des informations dans un fichier de log ou dans le journal des évènements :

CREATE SERVER AUDIT AuditEffacement TO APPLICATION LOG
CREATE DATABASE AUDIT SPECIFICATION AUDIT SUPPRESSION UTILISATEURS
FOR SERVER AUDIT AuditEffacement
ADD (DELETE ON DBO.UTILISATEURS)

Compression des backups

Il était déjà possible de compresser les backups, mais cela se faisait en deux temps : sauvegarde complète, puis compression du backup. Maintenant la compression se fait directement en ligne durant le backup proprement dit. Cela consomme un peu plus de CPU, mais permet de gérer localement de plus gros backups.

Data collector

Ce composant permet de remonter et de stocker les informations sur le fonctionnement de SQL Server d’une manière totalement automatique. Il existe déjà une pléthore de compteurs de performances liés à SQL Server et utilisables dans le moniteur de Windows, mais l’avantage du Data Collector réside dans le mode de collecte qui est particulièrement optimisé.

Les nouveautés du Management Studio

IntelliSense

L’IntelliSense, bien connu des utilisateurs de Visual Studio, s’applique maintenant dans l’édition des procédures T-SQL, dans l’éditeur de requêtes, etc. Un must qui fait qu’il est TRÈS difficile de retourner au Management Studio de SQL 2005.
Note : vous pouvez bien sûr gérer des serveurs SQL 2000 et SQL 2005 depuis la version 2008. (Désolé SQL7 n’est plus supporté).

Gestion de groupes de serveurs

Il est maintenant possible de créer un groupe de serveur SQL et d’appliquer un script ou une requête à l’ensemble des serveurs du groupe. Attention cependant aux catastrophes en série...

Plug-in

Existant - de manière cachée - dans Management Studio 2005, la version 2008 ouvre officiellement la possibilité d’en étendre les fonctionnalités au moyen de composants enfichables ou plug-in. Il y a beaucoup de chances que de nombreux composants créés par la communauté des administrateurs SQL viennent enrichir le Management Studio.

Les versions de SQL Server 2008

Express

Version gratuite, mais totalement fonctionnelle, elle est limitée sur le nombre de processeurs, la mémoire vive et la taille des bases de données. Idéal pour des petits projets, pour apprendre à jouer avec SQL Server ou comme base de données liée à une application Windows.

Compact

Version mobile et embarquée. Entièrement gratuit, fonctionne sous Windows Mobile, Windows XP et Vista. Peut être une alternative intéressante dans le cas d’une base de données liée à une application Windows car très compacte et très facile à mettre en oeuvre. Le moteur possède cependant certaines limitations fonctionnelles.

Web

Version commerciale très bon marché qui vise la haute disponibilité et l’intégration avec .NET (et LinQ ;-)).

Workgroup

Version commerciale à faible coût qui est plutôt dirigée vers le reporting et la synchro avec des serveurs de classe Enterprise.

Standard

Version commerciale la plus utilisée. Elle convient parfaitement pour l’utilisation d’un serveur simple, sans réplication ni snapshot.

Enterprise

La version la plus évoluée de SQL Server (mais aussi la plus chère). Convient pour l’utilisation d’un cluster SQL, de réplication et de mirroring de base de données.

Quelles versions sont disponibles à l’EPFL ?

Les versions Standard et Enterprise sont distribuées par votre Distrilog préféré. À noter que la version Enterprise n’est distribuée que pour un OS 64 bits, de préférence Windows Server 2008 x64. Dans tous les cas, la langue de l’OS et de SQL Server est l’anglais.

Les versions Express et Compact peuvent être téléchargées directement depuis le site de Microsoft.

Et pour conclure

... je suis obligé de dire que j’ai volontairement laissé de côté deux parties importantes de SQL 2008 : Le Reporting Services et le Data Mining (OLAP). Peut-être un prochain article sur ce Flash informatique en parlera...
Dans tous les cas je vous souhaite beaucoup de plaisir avec un outil très professionnel, doté des outils d’administration et de gestion très efficaces et conçu pour faciliter au maximum l’utilisation.



Cherchez ...

- dans tous les Flash informatique
(entre 1986 et 2001: seulement sur les titres et auteurs)
- par mot-clé

Avertissement

Cette page est un article d'une publication de l'EPFL.
Le contenu et certains liens ne sont peut-être plus d'actualité.

Responsabilité

Les articles n'engagent que leurs auteurs, sauf ceux qui concernent de façon évidente des prestations officielles (sous la responsabilité du DIT ou d'autres entités). Toute reproduction, même partielle, n'est autorisée qu'avec l'accord de la rédaction et des auteurs.


Archives sur clé USB

Le Flash informatique ne paraîtra plus. Le dernier numéro est daté de décembre 2013.

Taguage des articles

Depuis 2010, pour aider le lecteur, les articles sont taggués:
  •   tout public
    que vous soyiez utilisateur occasionnel du PC familial, ou bien simplement propriétaire d'un iPhone, lisez l'article marqué tout public, vous y apprendrez plein de choses qui vous permettront de mieux appréhender ces technologies qui envahissent votre quotidien
  •   public averti
    l'article parle de concepts techniques, mais à la portée de toute personne intéressée par les dessous des nouvelles technologies
  •   expert
    le sujet abordé n'intéresse que peu de lecteurs, mais ceux-là seront ravis d'approfondir un thème, d'en savoir plus sur un nouveau langage.