Introduction
Sortie cet été, la nouvelle version de SQL Server arrive avec une myriade de nouvelles fonctionnalités, sans bouleverser pas les avancées apportées par la version 2005. Microsoft souhaite d’ailleurs adopter une nouvelle approche orientée « features » pour faire évoluer ses produits et faciliter les mises à jour.
Ces nouveautés ont pour vocation d’améliorer la base de données de Microsoft sur les axes de
- la fiabilité, pour accroitre la capacité à faire tourner des applications critiques d’entreprise
- la productivité, pour offrir aux développeurs et aux administrateurs plus de simplicité, pour en faire plus en moins de temps
- l’intelligence, pour permettre à chaque utilisateur d’exploiter l’information la plus pertinente
Dans cet article nous allons passer en revue une grande partie de ces nouvelles fonctionnalités.
Nouveautés du langage
On commence par des petites nouveautés, celles du langage T-SQL. Ce ne sont pas les plus grosses ni les plus attendues mais elles améliorent l’utilisation au quotidien et va réduire sensiblement la taille de nos scripts.
Déclaration et assignation de variable : on peut maintenant en une seule instruction déclarer et attribuer une valeur à une variable.
Opérateurs : on peut maintenant faire une opération et réaffecter le résultat en une seule instruction
Instruction MERGE : cette nouvelle instruction permet de fusionner 2 jeux de données. Très concrètement, elle permet par exemple de traiter l’insertion et la mise à jour en une seule fois.
MERGE dbo.Contact AS Destination
USING (SELECT * FROM dbo.Donnees_a_Inserer) AS Source
ON (Destination.ContactId = Source.ContactId)
WHEN MATCHED THEN
UPDATE SET Destination.Adresse = Source. Adresse, Destination.Date = GetDate()
WHEN NOT MATCHED BY TARGET THEN
INSERT (ContactId, Adresse, Nom)
VALUES (Source. ContactId, Source. Adresse, Source. Nom) ;
Insertion multiple : l’instruction INSERT s’enrichit avec la possibilité d’ajouter plusieurs enregistrements en même temps
INSERT MaTable(Nom, Prenom)
VALUES (‘Riehl’, ‘Jean-Pierre’)
VALUES (‘Pertus’, ‘Sébastien’) ;
Nouveautés pour les données
Nouveaux types
HierarchyID : ce type permet de modéliser plus facilement des structures arborescentes comme le très célèbre Employé-Manager. Ce type offre aussi de nombreuses méthodes pour manipuler les données efficacement (recherche d’un ancêtre, détermination du niveau, etc.)
Date & Time : il y a 4 nouveaux types liés aux données temporelles qui voient le jour. Les plus simples sont le type Date qui ne stocke que la date du jour mais pas l’heure et le type Time qui lui stocke uniquement l’heure avec une précision de 100 nanosecondes. Apparaissent aussi 2 nouveaux type DateTime. Le premier, datetime2, qui a une précision beaucoup plus importante (100ns) et le second qui prend en compte les fuseaux horaires.
Geography et Geometry : attendu depuis longtemps, SQL Server s’enrichit du support des données spatiales. Ces 2 types permettent respectivement de gérer des coordonnées géographiques, c'est-à-dire relative à la Terre, et des données géométriques à savoir un point sur un plan. Ces deux types sont livrés avec de nombreuses méthodes de manipulation et un système d’indexation.
Table-Valued Parameter : il existait déjà un type Table que l’on pouvait manipuler mais on ne pouvait pas le passer comme paramètre d’une procédure stockée ou d’une fonction. C’est possible dans cette nouvelle version avec les TVP.
CREATE TYPE UserIds AS TABLE (
UserId VARCHAR(50));
CREATE PROCEDURE spDeleteUsers
@UsersToDelete UserIds READONLY
AS
[…]
Le « spatial »
Les fonctionnalités spatiales étaient très attendues dans une base de données de la taille de SQL Server. Pour cette nouvelle fonctionnalité, les équipes de Microsoft s’appuient sur les standards de l’Open Geospatial Consortium et fournissent une solution complète, clef en main et intégrée.
Les types spatiaux sont facilement manipulables et rendent presque naturel l’ajout d’informations géographiques ou vectorielles dans une application. La requête ci-dessous montre comment retrouver des points dans une zone donnée.
Declare @zone geography = geography::STPolyFromText('POLYGON((43 3.2, 43 3.5, 43.2 3.5, 43.2 3.2, 43 3.2))', 4326)
Select *
From CustomerPlaces
Where @zone.STIntersects(CustomerPlaces.Localization) = 1
L’intégration avec ADO.NET et les applications .NET est tout aussi triviale puisque les mêmes types geography et geometry (ce sont des types CLR) existent dans le Framework.
SqlGeography g = SqlGeography.Point(Latitude, Longitude, 4326);
Pour un rendu plus visuel, on pourra s’appuyer sur des technologies comme Virtual Earth ou MapPoint.
Ces nouveaux types ouvrent la voie à des applications localisées voire à un système d’information localisé.
Nouvelles façons de stocker les données
FILESTREAM : on peut stocker depuis longtemps des fichiers ou de larges objets binaires. Depuis 2005, on stocke ces BLOB dans un champ de type varbinary(max). Les options de stockage des tables permettent de gérer des gros volumes de données sans difficulté avec un bon niveau de performance.
Cependant, il reste un point sur lequel SQL Server n’est pas très à l’aise, c’est une lecture de BLOB en mode stream. En effet, dans le cas de très gros fichiers (de la vidéo par exemple) SQL Server monopolise beaucoup de ressources car il doit placer tout le champ dans ses buffers de transfert.
FILESTREAM permet de stocker ce type de données sous la forme de fichiers sur un système NTFS classique. Ce mode de stockage permet de pouvoir récupérer un pointeur sur le système de fichier et de lire les données de façon streamée, comme un fichier classique.
CREATE TABLE dbo.VideosData (
Id INT PRIMARY KEY,
Mpeg4Data VARBINARY(MAX) FILESTREAM NULL)
SPARSE columns : le concept de Sparse columns représente les colonnes à faible densité comme par exemple les attributs d’un contact dans une base de CRM qui peuvent être très nombreux et pourtant très peu renseignés. Le fait d’indiquer à SQL Server que ces attributs doivent être stockés dans des colonnes de type SPARSE permet de gagner l’espace normalement occupé par les valeurs nulles. Ce gain n’est pas temps pour le stockage mais pour la manipulation des données qui s’en trouve améliorée du fait d’avoir moins d’octets à lire.
CREATE TABLE dbo.ClientCRM (
ClientID int NOT NULL IDENTITY (1, 1),
Nom varchar(50) NOT NULL,
Poids int SPARSE NULL,
Yeux varchar(50) SPARSE NULL,
Taille numeric(3, 2) SPARSE NULL)
Les sparses columns s’utilisent aussi avec la notion de Columns Set qui permet de récupérer ou d’insérer plus facilement des jeux de colonnes disposant d’une valeur sans avoir à les manipuler toutes explicitement.
Nouvelles façons de requêter des données
iFTS : iFTS est l’abbréviation de Integrated Full-Text Search. La recherche Full-Text existe depuis un moment dans SQL Server mais se base sur une architecture inter-processus. En effet, la recherche s’appuyait sur le service Windows Search, qui réside en dehors du giron de SQL Server.
Avec la version 2008, la recherche a été intégrée dans SQL Server. Cette intégration permet des gains de performances énormes car l’optimiseur peut mixer les opérations de recherche full-text avec des opérateurs de jointures ou de filtrage classique dans le plan d’exécution.
Cette nouvelle version se voit aussi enrichie de petites fonctionnalités qui facilitent la gestion d’une solution de recherche comme la gestion plus simple des mots interdits ou la consultation des mots de l’index.
Index filtrés : Il est désormais possible de poser des index sur une partie seulement des données d’une table. On rajoute juste une clause WHERE dans la création de l’index et celui-ci ne s’appliquera qu’à un sous-ensemble des données. Ici on n’indexe que les clients qui ont un numéro de téléphone renseigné.
CREATE INDEX IX_Phonenumber ON dbo.Client
(Phonenumber ASC)
WHERE Phonenumber is not null
Nouveautés pour les administrateurs
Nouveautés pour l’administration
Compression des backups : cela peut paraître trivial de compresser un backup de base de données mais cette opération devait être faite à postériori par l’opérateur de sauvegarde. Maintenant, c’est SQL Server lui-même qui peut compresser la sauvegarde au fil de l’eau. Certes plus de CPU est consommée mais cela permet de manipuler localement de gros backup.
Fonctions d’audit : cette fonctionnalité est similaire à la TRACE mais avec une vocation plus administrative. Elle permet de d’auditer certaines opérations sur le serveur et de les loguer dans un fichier ou dans le journal d’événements.
CREATE SERVER AUDIT SuppresionAudit TO APPLICATION_LOG
CREATE DATABASE AUDIT SPECIFICATION Audit_Suppression_Poduits
FOR SERVER AUDIT SuppresionAudit
ADD (DELETE ON dbo.Product)
Administration par règles : plus connu sous le nom de Policy Framework, cette fonctionnalité permet enfin aux administrateurs de base de données d’édicter des règles de gestion ou de nommage pour leurs bases et de les faire respecter.
On écrit d’abord une condition, comme par exemple que toutes les tables doivent appartenir à un schéma autre que dbo. On définit ensuite une règle de gestion de cette condition.
Enfin, on peut soit l’évaluer sur demande pour avoir un état des lieux, comme ci-dessous. Mais on peut aussi forcer la validité de la règle à tout moment, et ainsi interdire l’exécution d’un script ne la respectant pas.
Data collector : ce nouveau composant permet de récupérer et de stocker des informations précieuses sur le fonctionnement de SQL Server comme l’utilisation des disques ou de la mémoire. La nouveauté n’est pas dans les compteurs remontés, car ils sont déjà auditables depuis le moniteur de performance de Windows ou depuis les vues systèmes, mais dans le mode de collecte, complètement automatisé et dans la consolidation des données puisqu’un DataWarehouse dédié sert de stockage.
Nouveautés pour la sécurité
Chiffrement transparent : il existait déjà des API de chiffrement et toute une hiérarchie de clés et certificats intégrées à la version 2005 pour gérer la sécurité des données. Cependant, il fallait manipuler ces APIs en T-SQL au moment de l’utilisation des données. Avec la version 2008, il est maintenant possible de chiffrer toute une base de données de façon transparente pour les utilisateurs.
ALTER DATABASE SalairesDB
SET ENCRYPTION ON
Utilisation de clés externes : comme indiqué ci-dessus, SQL Server possède toute une hiérarchie de sécurité pour gérer les clés et les certificats. Mais dans un système d’information, on peut disposer d’une gestion centrale des clés de chiffrement (le PKI) et vouloir en tirer parti dans la base de données. SQL Server 2008 peut utiliser un gestionnaire de clés externe pour gérer les clés qu’il utilise.
Nouveautés pour les performances
Compression des données : SQL Server 2008 supporte la compression des données à tous les niveaux. Nous l’avons vu avec les backups mais il est aussi possible de compresser directement les données d’une table, d’une partition ou d’un index.
On a du mal à classer cette fonctionnalité dans la catégorie performance car compression est synonyme de consommation processeur. On attend plus souvent de ce genre de fonctionnalité de gagner de l’espace disque. On en gagne certes mais là où la compression apporte est dans l’exécution des requêtes car on doit lire moins de données sur le disque, ce qui est un des principaux nerfs de la guerre de la performance. A utiliser néanmoins avec discernement…
CREATE TABLE Contact
(id int, fullname nvarchar(50) )
WITH (DATA_COMPRESSION = PAGE);
Resource Governor : cette fonctionnalité est une des plus attendues dans SQL Server. Le Resource Governor permet de gérer finement l’utilisation du serveur (CPU, mémoire). Fini les requêtes qui monopolisent toutes les ressources. On peut assigner des limitations et des plans de charge aux différentes connexions ou applications. Ainsi, on peut faire en sorte qu’une application critique dispose d’un maximum de puissance de traitement et que des petites applications soient moins prioritaires.
CREATE FUNCTION fnPriorisation () RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @group AS sysname
IF (SUSER_NAME() = 'Directeur ')
SET @Group = 'PRIORITAIRE'
ELSE IF (APP_NAME() LIKE '%REPORT SERVER%')
SET @Group = 'Reporting Services'
ELSE
SET @Group = 'NON PRIORITAIRE'
RETURN @Group
END
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=fnPriorisation);
Mais encore… : on peut citer de nombreuses autres améliorations comme l’optimisation et la meilleure gestion des plans d’exécution, ajouts de Hints pour mieux maîtriser les requêtes, améliorations du parallélisme sur les partitions, l’ajout de CPU à chaud, l’amélioration du mirroring, etc.
Nouveautés décisionnelles
Nouveautés de Integration Services
Change Data Capture : cette fonctionnalité sert à optimiser le chargement de DataWarehouse. Elle récupère toutes les modifications faites sur une table et les stocke de façon à pouvoir être exploitées sans impact sur les tables de production.
Data Profiling : un nouveau composant voit le jour pour profiler vos données et récupérer des informations précieuses pour mesurer la qualité de vos données. On peut ainsi récupérer le ratio de valeurs nulles, la distribution des valeurs, etc.
Gestion du cache : on peut maintenant dans un package SSIS utiliser un cache de données quand on doit manipuler les mêmes données à différents endroits. De nouveaux composants permettent de le créer et de l’utiliser dans des flux de données.
Composant Lookup : le composant Lookup est quelque peu amélioré grâce à la gestion du cache. En effet, on peut maintenant maîtriser finement le chargement de la table de référence utilisée pour l’opération de lookup.
Compteurs de performance : une nouvelle source de données apparaît pour récupérer les données des compteurs de performance de Windows.
Scripting en C# : les composants de script supportent maintenant le langage C# et s’appuient sur la brique VSTA (Visual Studio Tools for Application) pour supporter la totalité du Framework .NET.
Mais encore… : Microsoft profite de cette nouvelle version pour apporter une amélioration des performances générales notamment grâce à une optimisation du multithreading.
Nouveautés de Analysis Services
Améliorations des performances globales : dans cette version 2008 de SSAS, Microsoft a cherché à beaucoup optimiser les performances de son moteur multidimensionnel. L’objectif est ambitieux : supporter des DataWarehouse de 100To de données en lieu et place des 25 supportés avec la version 2005. De nombreuses optimisations dans le calcul des agrégats et la gestion des cubes abondent en ce sens.
Notion de bonnes pratiques : une nouvelle fonctionnalité aussi appelée « alertes AMO » apparaît dans Business Intelligence Studio. De la même façon que le correcteur orthographique de Word, elles soulignent les éléments qui ne respectent pas les bonnes pratiques de modélisation et méritent réflexion. Ces avertissements sont débrayables ponctuellement ou définitivement.
Concepteur de dimensions : le concepteur de dimensions est enrichi d’une représentation graphique des relations entre attributs. Cela apporte une aide visuelle pour la conception de vos dimensions.
Concepteur d’agrégations : ce concepteur est grandement amélioré puisqu’il permet maintenant de donner des directives précises au moteur OLAP quant à la granularité des agrégations qu’il fait. On peut ainsi décider par exemple de ne pas agréger au niveau jour de la dimension temps car ce niveau est très peu utilisé mais préférer l’attribut couleur des produits.
Mais encore… : d’autres nouveautés sont disponibles dans cette version 2008 d’Analysis Services comme le writeback (écriture dans un cube) dans une partition de type MOLAP ou encore l’amélioration de la pertinence de certains des algorithmes de Data Mining.
Nouveautés de Reporting Services
C’est sans aucun doute dans Reporting Services que les nouveautés sont le plus visibles, découvrons les plus en détail.
Nouvelle architecture : pas de panique, l’architecture actuelle n’est pas remise en cause. Néanmoins, on a le choix avec cette version 2008 d’installer Reporting Services sans avoir de serveur IIS sur la machine. Ce pré-requis, souvent en contradiction avec les bonnes pratiques de Microsoft, n’est plus obligatoire. La nouvelle architecture permet à SSRS d’écouter lui-même les connexions HTTP en se branchant directement sur le driver HTTP.SYS de Windows. Cela lui permet de mieux prendre en charge la gestion de la mémoire et les longs rapports.
Nouvel outil de création de rapport : un des défauts dans la version 2005 était les outils de création de rapports. Visual Studio était trop « développeur » et Report Builder n’a pas convaincu du fait de son manque de richesse fonctionnelle. Microsoft s’est fixé comme objectif d’avoir un outil à destination de l’utilisateur final iso-fonctionnel avec Visual Studio, et bien évidemment, le tout avec une ergonomie nettement améliorée.
Il semblerait que le pari soit gagné car Report Designer, qui a le look de la gamme Office 2007, est quasiment le même outil que Visual Studio. Et pour les deux outils, après quelques minutes d’adaptation, on ne peut plus se passer de leur ergonomie intuitive et efficace. Fini les soucis de ne pas savoir à quel niveau d’agrégation on est, quel filtre est mis, quel champ est affiché, etc.
Export vers Word : il est maintenant possible d’exporter un rapport sous le format Word
Nouveaux composants graphiques : Reporting Services 2005 ne brillait pas par la beauté de ces graphiques, très vieillots avec leur look Office 97. Microsoft a acquis la technologie Dundas, célèbre éditeur de composants pour SSRS. Il intègre donc nativement dans sa version 2008 les composants graphs et jauges de cet éditeur. Non seulement on peut maintenant faire de jolis graphiques mais la palette fonctionnelle s’enrichit énormément : graphe à double échelle, nouveaux types, échelle discontinue, etc.
Tablix : avec 2005, nous avions le tableau et la matrice, le premier s’étendant de haut en bas, le second de gauche à droite. Avec 2008, il n’y a plus qu’un composant hybride, le tablix. L’idée est de fusionner le meilleur de la table et de la matrice tout en gommant les limitations des 2 composants. Ce nouveau composant corrige en effet une grande partie des problèmes de 2005 qui nous contraignaient à faire de la gymnastique dans nos rapports comme les groupes adjacents, les lignes statiques, les totaux, etc.
Nouveautés de Management Studio
Plug-in : Management Studio 2005 contenait un moteur de plug-in non documenté et jamais dévoilé. La version 2008 le révèle au grand public. En espérant que la communauté va en faire bon usage et enrichir l’outil de nombreux Power Toys.
IntelliSense : longtemps attendue, c’est une des nouveautés les plus agréables car elle nous sert au quotidien. Une fois goutée, il est difficile de revenir à 2005 tellement la recherche de tables, de champs, de mots-clés est précieuse.
Gestion de groupes de serveurs : on peut depuis Management Studio travailler sur des groupes de serveurs et passer des scripts en parallèle sur les différents serveurs.
Conclusion
Sans remettre en cause l’architecture ou les briques posées par la version 2005, la version 2008 apporte tout un wagon de nouveautés et d’améliorations qui sont loin d’être futiles. Cet article vous en a présenté une grande partie mais il manque encore des chapitres sur SQL CE 3.5, les frameworks de mapping O/R que sont Entity Framework et Linq to SQL, des modules comme le Change Tracking, les améliorations du XML, etc.
Plus que jamais SQL Server mérite sa place dans le cercle fermé des grandes bases de données d’entreprise, capables de gérer de la petite application au DataWarehouse de 100To en passant par des applications critiques ou déconnectées.