Microsoft SQL Server
Un article de Wikipédia, l'encyclopédie libre.
 |
Cet article est une ébauche à compléter concernant les logiciels, vous pouvez partager vos connaissances en le modifiant. |
SQL Server est un SGBD développé et commercialisé par Microsoft, mais à l'origine par Sybase. le SGBD Microsoft SQL Server fonctionne désormais uniquement sous Windows.
Il était développé à l'origine (version 4.2) par Microsoft et Sybase avant la sortie de sa version 6.0. Suite à un différend entre les deux sociétés, chacune a continué le développement de son côté. Microsoft a choisi de le commercialiser sous le nom de SQL Server, Sybase, pour éviter toute confusion, a renommé Sybase SQL Server en Sybase Adaptive Server Enterprise.
Microsoft SQL Server fait désormais partie de la stratégie technique de Microsoft en matière de base de données. Le moteur MSDE qui est la base de SQL Server doit à terme remplacer le moteur Jet (celui qui gère les bases Access) dans les applications telles que Exchange et Active Directory.
La version 2005 de SQL Server est sortie le 3 novembre 2005 en même temps que Visual Studio 2005.
| SQL Server | |
|---|---|
| Développeur | Microsoft |
| Dernière version | 2005 (le 3 novembre 2005) |
| Environnement | Microsoft Windows |
| Type | SGBD |
| Licence | Par utilisateur ou par processeur |
| Site Web | Microsoft France |
Sommaire |
[modifier] Fonctionnement
[modifier] Langages
Pour les requêtes, SQL Server utilise T-SQL, il s'agit d'une implémentation de SQL qui prend en charge les procédures stockées et les déclencheurs (trigger). Le T-SQL est incompatible avec le PL/SQL d'Oracle.
Pour les transferts de données SQL Server utilise le format TDS (Tabular Data System) qui a été implémenté dans d'autres bases de données (en particulier dans son homologue Sybase) et dont les spécifications sont publiques. Une implémentation Open Source d'un client TDS est disponible et constitue la base du client SQL Server du projet Mono : FreeTDS.
[modifier] Relations
SQL Server est un SGBD relationnel. Il est possible de définir des relations entre les tables de façon à garantir fortement l'intégrité des données qui y sont stockées. Ces relations peuvent être utilisées pour modifier ou supprimer en chaîne des enregistrements liés.
[modifier] Transactions
SQL Server est un SGBD transactionnel. Il est capable de préparer des modifications sur les données d'une base et de les valider ou de les annuler d'un bloc. Ceci garanti l'intégrité des informations stockées dans la base.
Lors d'une transaction, les blocs de données contenant les lignes de données modifiées par cette transaction sont verrouillés et les autres utilisateurs doivent attendre la fin de la transaction pour pouvoir les modifier à nouveau.
Les verrouillages s'effectuent au niveau des lignes, pages, extensions, tables ou base de données. SQL Server ne verrouille que les ressources dont il a besoin (par défaut les enregistrements) et en fonction des besoins peut verrouiller à un niveau plus élevé (pages ou objet). Ceci évite aux utilisateurs d'attendre la fin d'une transaction pour mettre à jour des lignes de données qui n'ont pas été touchéees par une modification et permet de diminuer la quantité de ressources consommées
Les transactions sont enregistrées dans le journal de transaction et sont intégrés à la base de données lors de points de contrôle (check point). Il est possible de forcer un point de contrôle grâce à l'instruction CHECKPOINT
Le journal des transactions peut être conservé de trois manières différentes :
- Mode simple : les transactions terminées sont vidées à chaque point de contrôle
- Mode journalisé en bloc : les instructions et leurs fonctionnements sont enregistrées sauf pour les instructions de chargements massifs (BCP, BULK INSERT et CREATE INDEX) qui sont enregistrées de manière plus simple. Le journal est vidé lors des sauvegardes des fichiers journaux.
- Mode complet : les instructions et leurs fonctionnements sont enregistrés. Le journal est vidé lors des sauvegardes des fichiers journaux.
Dans les 2 derniers modes, il est possible de sauvegarder la base de données et de la restaurer telle qu'elle était à n'importe quel point du temps à la seconde près (avec cependant des limites pour le mode journalisé en bloc). Ce mode permet aussi la sauvegarde de fichiers ou de groupes de fichiers.
[modifier] Fichiers
Les bases de données sont contenues physiquement dans des fichiers. Les fichiers contenant les données portent généralement les extensions :
- MDF (Main Data File) pour le premier fichier - NDF (Next Data File) pour les autres fichiers
Les fichiers contenant le journal de transaction portent généralement l'extension LDF (Log Data File).
Les fichiers sont écrits par bloc de 8196 octets appelés pages. Les données sont stockées sur 8060 octets. Pour calculer le nombre d'enregistrements par bloc on utilise la fonction :
nb_enregistrement_bloc = plancher ( 8060 / taille_enregistrement )
Pour calculer la taille d'une table on utilise la fonction :
taille_table = (nb_enregistrement_table / nb_enregistrement_bloc) * 8196
Les bases de données ne peuvent fonctionner que lorsque tous les fichiers sont présents.
Les fichiers sont regroupés logiquement dans la base de données dans des groupes de fichiers. Ces groupes de fichiers peuvent être sauvegardés de façon indépendante à condition de sauvegarder les journaux de transactions en parallèle.
[modifier] Droits
SQL Server s'appuie par défaut sur le système d'authentification de Windows (Kerberos ou Natif). On peut donner des droits sur les différents éléments de SQL Server à un groupe ou à un utilisateur. Lors de la connexion à la base de données, l'utilisateur est identifié grâce à son login Windows et accède aux ressources de la base de données auxquelles l'administrateur lui a donné droit par l'intermédiaire de son groupe Windows ou directement à son identifiant. Lors de l'accès à une ressource extérieure, le processus SQL Server agit par emprunt d'identité : l'utilisateur ne peut accéder par l'intermédiaire de SQL Server qu'aux ressources auxquelles il aurait droit s'il y accédait directement.
Comme il existe des cas où l'utilisateur ne peut pas être identifié par son login Windows (utilisation de Linux, d'une page Web...) une méthode d'identification directe à SQL Server peut être mise en place. Elle doit l'être explicitement par l'administrateur de l'instance. Lors du mécanisme d'emprunt d'identité quand il accède à une ressource extérieure, il disposera des droits affectés à l'utilisateur défini pour démarrer le service SQL Server. Ce dernier cas peut être très dangereux lorsque SQL Server est exécuté en tant que service Système ou en tant qu'Administrateur. Pour parer à cette faille de sécurité, sous Microsoft Windows Server 2003, Microsoft a prévu l'ajout de deux systèmes d'exécution supplémentaires pour démarrer les services : Service Local et Service réseau. Depuis la version 2005 de SQL Server, il est possible de donner une identité réseau à un utilisateur SQL Server.
Lorsqu'un utilisateur est connecté à une instance, il peut disposer de droits sur l'instance elle-même et/ou sur chacune des bases gérées par l'instance. Les droits sur l'instance sont donnés par l'intermédiaire de rôles d'instance prédéfinis. Les droits sur les bases de données sont donnés par l'intermédiaire de rôle de base de données, de groupes windows ou directement à l'utilisateur. Il existe des rôles de bases de données système qui donnent des droits spécifiques sur la base de données et d'autres définis par l'administrateur qui donnent des droits sur les objets. Lorsque l'accès à une instance est donné à un utilisateur ou à un groupe Windows celui-ci dispose d'un espace utilisateur dans lequel peuvent être enregistrés des objets ayant un nom identique à celui d'un autre espace utilisateur. L'espace utilisateur par défaut des administrateurs d'une base de donnée est dbo (database owner).
Il existe au niveau des bases de données des rôle applicatifs auxquels on peut affecter des droits et accessibles par mot de passe. Lorsqu'ils sont utilisés ils remplacent les droits de l'utilisateur courant par les droits affectés au rôle. Ils sont utilisés pour interdire l'accès aux utilisateurs à une base de données par d'autres moyens que l'application qui leur est fournie.
[modifier] Objets de la base de données
[modifier] Tables
Les tables sont les objets qui contiennent effectivement les données dans la base. Elles sont de deux types :
- Les tables système contiennent les informations permettant le bon fonctionnement de la base de données. Par défaut sous SQL Server 2000 elles ne sont pas modifiables. Sous SQL Server 2005 de nouvelles tables systèmes apparaissent mais sont non accessibles aux utilisateurs. Pour pouvoir toujours acceder à ces informations l'équipe de développment à ajouté un grand nombre de vues systèmes. Celles ci remplacent les anciennes tables systèmes, certaines commandes DBCC. un grand nombre de nouvelles vues systèmes ont aussi été ajoutées. Sous SQL Server tous les objets systèmes font partis du schéma sys et sont stockés dans une base de données appelée SystemRessource.
- Les tables utilisateurs contiennent les données des utilisateurs.
Une table peut contenir jusqu'à 1024 champs dont la somme des tailles n'excède pas 8060 octets. Pour dépasser les 8060 octets les types de données text, binary et image ont été introduits et leurs valeurs sont stockées dans un espace séparé des autres données. Ces champs ne peuvent pas être inclus dans des index classiques.
A partir de la version 2005 de SQL Server, la limite de 8060 octets peut-être dépassée. Une constante MAXSIZE a été introduite pour définir la taille des champs au maximum adressable par le moteur de base de données (actuellement 2 giga octets). Les types text, binary et image ont été déclarés obsolètes.
Les enregistrements d'une table peuvent être caractérisés par une clef primaire qui est indexées automatiquement par SQL Server.
[modifier] Vues
Une vue est une requête nommée de la base de données. Elle est interrogée de la même façon qu'une table.
Les données renvoyées par la vue sont reconstituées à partir des données contenues dans les tables à chaque appel de la vue. SQL Server remplace le nom des vues dans la requête qui va être executée par leurs définitions, puis la requête ainsi obtenue est compilée et executée par le moteur. Le plan de compilation est ensuite stocké pour permettre la réutilisation lors d'un prochain appel.
Dans certaines conditions, il est possible de mettre à jour les valeurs dans une vue. Voici des exemples de ces conditions:
1- Une vue sur plusieurs tables ne peut modifier qu'une seule de ces tables. 2- Si la vue contient des fonctions, utilise "DISTINCT" ou "GROUP BY", la modification ne fonctionnera pas. 3- La vue possède un déclencheur de type INSTEAD-OF.
SQL Server implémente des types particuliers de vues qui sont les vues partitionnées et les vues indexées.
[modifier] Index
Il existe 2 types d'index :
1- Les index clusterisés 2- Les index non-clusterisés
Les index clusterisés sont comme les pages d'un dictionnaire. Ils sont triés logiquement pour trouver rapidement l'enregistrement cherché ! Il ne peut y avoir qu'un seul index clusterisé par le fait même !
Un index non clusterisé est composé de champs triés pour aider la recherche.
Une table peut contenir 249 index. Chaque index peut contenir 16 champs dont la somme des tailles n'excède pas 900 octets. Les index contiennent en plus des données un signet (bookmark) vers les données de la table.
L'optimisateur de requêtes de SQL Serveur choisi quel index utiliser. Il est possible qu'il n'utilise pas l'index que vous avez créé car le coût d'ouvrir l'index et la table peut être plus grand que simplement lire la table au complet ! Pour faire ces choix, l'optimisateur utilise les statistiques de la table. Il est possible mais non recommandé par contre de forcer l'optimisateur à utiliser un index avec la clause WITH(nom_index)
L'index est organisé en arbre-b. Ceci permet de classer plus rapidement les informations que s'il fallait les insérer dans une table séquentielle. Cette disposition étant très gourmande en ressources de stockage, la version 2005 de SQL Server permet de séparer les données de recherche et des données de traitement intégrées à l'index.
[modifier] Procédures stockées
Il est possible de définir des procédures stockées. Une procédure stockée est une suite d'instruction qui vont avoir des effets sur la base de données ou qui renvoient des valeurs.
Les procédures stockées sous SQL Server peuvent prendre en paramètre et/ou retourner des entiers, des chaînes de caractère, des dates, des curseurs, des tables, des tables virtuelles et tout autre type défini dans SQL Server par défaut ou par les utilisateurs.
[modifier] Déclencheurs (trigger)
Le déclencheur est une procédure stockée qui s'exécute lors d'une tentative d'action particulière (insertion, modification, suppression) sur une table ou sur une vue.
Deux tables virtuelles sont créées pour manipuler les données insérées, modifiées ou supprimées : inserted et deleted. Comme il est impossible de modifier le contenu des ces tables virtuelles, les développeurs ont recours à un subterfuge pour modifier les valeurs juste avant l'insertion réelle de celle-ci (ils utilisent le mot clef INSTEAD OF).
[modifier] Fonctions
Depuis SQL Server 2000, il est possible de créer des fonctions.
Ces fonctions sont de 3 types :
- Scalaire : Plusieurs instructions renvoient alors une valeur de type simple
- Table à instruction multiple : Plusieurs instructions renvoient alors une table
- Table en-ligne (vue paramétrée) : Une instruction de type SELECT renvoie une table
Il existe cependant des restrictions quant aux fonctionnalités utilisables dans le corps d'une fonction. Il n'est pas possible d'utiliser de fonctions non déterministes et la fonction ne doit pas modifier des données ou des paramètres système de manière permanente ou durable. Ce qui interdit par exemple l'utilisation d'un INSERT sur une table ou l'utilisation de la fonction GETDATE().
[modifier] Outils
[modifier] SQL Server Management Studio (SSMS)
Cet outil est fourni avec les versions payantes de SQL Server 2005. Il permet de se connecter et d'administrer les différents moteurs SQL Server 2005 (SSRS, SSIS, SSAS et le moteur relationnel). Il permet pour le moteur relationnel de développer des scripts TSQL, avec la possibilité de regrouper l'ensemble de ceux-ci au sein d'une solution (comme sous Visual Studio). On peut aussi enregistrer ceux-ci grace à Visual SourceSafe ou Team Foundation Server.
[modifier] SQL Server Management Studio Express Edition (SSMSE)
Cet outil est téléchargeable gratuitement pour la gestion du moteur relationnel SQL Server 2005 Express Edition. Il permet de se connecter et d'administrer uniquement le moteur relationnel SQL Server 2005. Il permet pour le moteur relationnel de développer des scripts TSQL.
[modifier] SQL Server Business Intelligence Development Studio
Tout comme SSMS, cet outil est fourni avec les versions payantes de SQL Server 2005. Il s'agit de l'outil de développement Microsoft Visual Studio 2005 adapté pour la création de projets Analysis Services, Integration Services ou Reporting Services. Tous ces projets se retrouvent dans le groupe "Projets Business Intelligence".
[modifier] Entreprise manager
Jusqu'à la version 2000, cet outil sert à gérer la base de données de façon entièrement graphique. Il permet en outre d'accéder et de configurer tous les outils annexes de SQL Server.
[modifier] Query Analyzer (Analyseur de requête)
Il s'agit d'un outil qui permet d'exécuter des requêtes sur un serveur SQL. Il permet aussi d'analyser le fonctionnement des requêtes pour optimiser la requête elle-même ou la base de donnée qu'elle interroge.
[modifier] Profiler (générateur de profil)
Cet outil permet de capturer l'activité d'une base de données. Il permet aux administrateurs de vérifier les éléments et les requêtes qui prennent du temps sur la base de données. Éventuellement, il est possible de rejouer la capture sur un serveur de test.
[modifier] Services et moteurs
[modifier] SQL Server 2000
Il s'agit du moteur de bases de données. À chaque instance de SQL Server correspond un service Windows Sql Server. Ce service peut être repéré dans les services Windows sous les noms MSSQL$Nom_instance pour les instances nommées et MSSQLServer pour l'instance par défaut.
Chaque instance créée posséde au départ 4 bases de données systèmes. Le moteur SQL Server s'appuie sur la base de données système master qui contient la définition des autres bases de données.
Les autres bases systèmes sont :
- msdb : utilisée par l'agent SQL Server et la réplication.
- model : utilisée comme modèle pour créer de nouvelles bases de données. Toute nouvelle base de données est une copie de celle-ci.
- tempdb : Base de données temporaire, elle sert à stocker les données temporaires créées par des utlisateurs ou générées par des curseurs, des créations d'index ou lors d'un tri volumineux. Cette base de données système est systématiquement vidée au démarrage du service SQL Server.
[modifier] SQL Server Agent
Il s'agit de l'agent de maintenance de l'instance de SQL Server. À chaque instance de SQL Server correspond un service Windows Sql Server Agent. Ce service peut être repéré dans les services Windows sous les noms SQLAgent$Nom_instance pour les instances nommées et SQLAgent pour l'instance par défaut. Le moteur SQL Server Agent s'appuie sur la base msdb. Ce moteur permet de gérer les sauvegardes, les plans de maintenance, les travaux planifiés, la surveillance de la base, les alertes administratives...!
[modifier] Full Text Search
Moteur d'index / recherche de texte intégral.
[modifier] Distributed Transaction Coordinator
Connu aussi sous le nom de MS DTC, sert a gérer les transactions distribuées. C'est à dire les transactions entre plusieurs serveurs de transactions SQL Server, entre un serveur SQL Server et des serveur de base de données autres, entre plusieurs sources de données différentes qu'ils soient des moteur de base de données ou de simples composants.
[modifier] Notification Services
Service apparu sous SQL Server 2000 permet de requêter régulièrement la base de données et en fonction de ces requêtes de notifier des groupes abonnés à ces évènements.
[modifier] Décisionnel
La plateforme décisionnelle SQL Server se compose d'un ETL, apparu avec SQL 7, d'un moteur multidimentionnel, également apparu sous SQL 7 et d'un moteur de rapports, ajouté en 2004 sous SQL 2000. Avec la version SQL Server 2005, un studio de développement dédié au métier du décisionnel a été intégré dans Visual Studio 2005 : BI development Studio.
[modifier] Data Transformation Services
Il s'agit d'un outil à part entière qui n'est commercialisé qu'avec SQL Server. Il s'agit d'un outil d'ETL complet. Il permet de transférer des données depuis et vers n'importe quelle base de données pour laquelle des drivers OLE-DB ou ODBC ont été installés sur la machine qui héberge le programme.
Il peut s'appuyer sur msdb, mais il est possible de générer avec l'aide de Visual Basic des programmes indépendants qui tourneront sur un ordinateur ne disposant pas de SQL Server. Il possède également son propre mode de stockage structuré.
Au départ certaines sociétés n'ont fait l'acquisition de SQL Server que pour pouvoir utiliser cet outil. Disposant de licences SQL Server elles s'en sont servies pour faire fonctionner leurs bases de données. Ceci a permis à SQL Server de se créer un marché et de concurrencer Oracle
[modifier] Integration Services
Service apparu sous SQL Server 2005, il est le remplaçant de Data Transformation Services. L'ETL a été complètement reconstruit, et se positionne en concurrence des autres outils ETL professionnels du marché.
[modifier] Analysis Services
Service apparu sous SQL Server 7 connu à cette époque sous le nom de OLAP Services. Il permet de gérérer des cubes OLAP, données agrégées et multidimentionelles. Il permet également d'implémenter des algorithmes de Data Mining.
[modifier] Reporting Services
Service apparu sous SQL Server 2000 est un moteur de génération d'états. Deux services web le composent, l'un permettant son administration, l'autre la génération, l'abonnement, le rendu des rapports. Les rendus se font sous Excel, PDF et HTML..
[modifier] Editions
[modifier] Enterprise Edition
Elle supporte un nombre de processeurs et une taille de mémoire vive illimités (limités par le système d'exploitation). Il existe une version 32 bits (x86) et 2 versions 64 bits (ia64 et x64, x64 uniquement pour SQL Server 2005) et n'a pas de limite quant à la taille des bases de données. Elle ne peut s'installer que sur un Windows version serveur. Inclus toutes des fonctionnalités du moteur, dont les fonctions de haute disponibilité. Les fonctionnalités pour le décisionnel sont toutes incluses de la génération d'états avec Reporting Services à l'utilisation de cubes OLAP avec Analysis Services en passant par le transfert de données avec Integration Services (SQL Server 2005) ou Data Transformation Services (SQL Server 7 & 2000). Elle peut en outre fonctionner en cluster jusqu'à 8 nœuds.
[modifier] Developer Edition
Il s'agit d'une édition pour les développeurs qui dispose des mêmes fonctionnalités que l'édition Entreprise. Cependant la licence contient des restrictions quant à son utilisation.
[modifier] Standard Edition
Elle supporte jusqu'à 4 processeurs et une taille de mémoire vive illimitée (limitée par le système d'exploitation). Il existe une version 32 bits (x86) et 2 versions 64 bits (ia64 et x64, les deux uniquement pour SQL Server 2005) et n'a pas de limite quant à la taille des bases de données. Elle ne peut s'installer que sur un Windows version serveur. Cependant SQL Server 2005 permet son installation sur Windows de bureau. Inclus toutes des fonctionnalités du moteur, sauf certaines fonctions de haute disponibilité. Les fonctionnalités pour le décisionnel sont toutes incluses de la génération d'états avec Reporting Services (pour SQL Server 2000, ce composant est à charger séparément) à l'utilisation de cubes OLAP avec Analysis Services en passant par le transfert de données avec Integration Services (SQL Server 2005) ou Data Transformation Services (SQL Server 7 & 2000). Elle peut en outre fonctionner en cluster jusqu'à 2 nœuds depuis sa version 2005.
[modifier] Workgroup Edition
Nouvelle édition apparue avec SQL Server 2005. Elle supporte jusqu'à 2 processeurs et 3 Go de mémoire vive. Pour le moment elle n'existe qu'en version 32 bits et n'a pas de limite quant à la taille des bases de données. Inclus la majeure partie des fonctionnalités du moteur, dont la possibilité de participer à une réplication en tant qu'éditeur. Les fonctionnalités pour le décisionnel incluses se limites à la génération d'états avec Reporting Services.
[modifier] Personal Edition
Edition existant avec SQL Server 7 et SQL Server 2000. Elle supporte jusqu'à 2 processeurs et 2 Go de mémoire vive. N'existe qu'en version 32 bits et n'a pas de limite quant à la taille des bases de données. Inclus la majeure partie des fonctionnalités du moteur, dont la possibilité de participer à une réplication en tant qu'abonné. Elle n'a pas de fonctionnalités pour le décisionnel. En terme de licence, le fait de posseder une licence d'accès client (CAL) SQL Server suffit à utiliser cette édition.
[modifier] MSDE / Express Edition
Voir l’article MSDE.Microsoft a édité une édition gratuite composée uniquement du moteur de base de données relationnel, bridé à 2Go d'espace disque et 5 utilisateurs concurrents en exécution.
[modifier] Compact Edition
Appelée aupravant : Pocket PC / Mobile / Everywhere Edition. Il s'agit d'une édition légère adaptée à l'utilisation sur des PDA munis de Windows for Pocket PC. La version Compact (version 3.1), qui succède aux éditions Mobile s'ouvre elle aux postes de travail classiques à base de Windows. Cette édition peut participer à une réplication en tant qu'abonné.
[modifier] Licences
SQL Server supporte deux systèmes de licences :
- Licence par utilisateur : SQL Server peut utiliser tous les processeurs du serveur mais est limité au nombre d'utilisateurs spécifié. Chaque personne physique se servant d'une application utilisant SQL Server est considéré comme un utilisateur de la base de données
- Licence par processeur : SQL Server utilise le nombre de processeurs spécifiés dans la licence et peut accepter un nombre indéfini d'utilisateurs
