Au programme, le cours "Implementing a Microsoft SQL Server 2005 Database".
Le cours est très interessant, même pour un développeur expérimenté.
Il y a déjà a tellement raconter que je ne prendrais pas le temps de me relire avant la publication (soyez donc indulgents).
Que retenir de cette première journée:
OLAP et OLTP
Les bases de données peuvent être exploitées pour des applications OLTP (Online Transaction Processing) ou OLAP (Online Analytic Processing).
En fonctionnement OLTP les operations sont optimisées pour le traitement sécurisé d'un grand nombre de transactions.
OLTP met en place les systèmes de locking utilisés de façon intensives afin d'assurer l'intégrité des transactions.
C'est typiquement le cas des systèmes marchands ou des données sont fréquements ajoutées et/ou modifiées.
Les opérations sur une base de données OLTP sont généralement très courtes mais nécessite une grande réactivité.
Par contre, il est également possible d'exploiter une base de donnée en OLAP (Online Analytic Processing).
Le but de ce mode d'exploitation des informations est de fournir des réponses rapidement.
OLAP est indiqué pour la génération de rapports ou de longues requêtes brassent beaucoup d'informations.
Une erreur commune est de faire du reporting sur des bases de données OLTP.
En effet, les rapports executent des query qui prennent beaucoup de temps tout en maintenant des locks en lectures très étendus.
Le reporting n'est donc pas une opération à prévoir sur des bases de données de production au risque de bloquer (ou ralentir significativement) le traitement des transactions.
Pour éviter ce problème, il faut considérer:
- Le miroring / DBRestore sur serveur de reporting.
- La réplication
- Le database snapshot.
Le database snapshot est une operation (disponible uniquement sur l'édition enterprise) permettant de faire une copie quasi instantanée d'une DB (même pour une DB de 8 Go).
Ce processus est relativement léger car il ne copie pas "vraiment" la DB de production. Au contraire, lorsque la DB de production est mise-à-jour, seule les pages modifiées sont copiées (avant modification) dans la DB Snapshot (Copy-On-Write).
Une base de donnée Snapshot est read-only et n'a donc pas besoin de gérer des locks. Les DB Snapshots sont donc des victimes idéales pour des processus de reporting.
Modèle de gestion du Log file
Le modèle "Simple" fait une troncation du Log File dès que possible (idéal pour les environnement de développements).
Le modèle "Full" enregistre de nombreuses informations de transactions dans le transaction Log. Cela permet en autre de garantir un rollback ou restauration de DB a presque n'importe quel point dans le temps.
Ce modèle représente un grand désavantage losrque de larges opérations bulk-insert (insertion de records en lot) ou d'effacement de records prennent places.
En effet, dans ce cas, le log file est littéralement congestionné et grandit rapidement.
Le nouveau model "Bulk-Logged" est un équivalent du mode "full" ou l'enregistrement des opérations dans le log sont sommaire lors d'operations bulk. Dans ce cas, il ne sera possible de restorer l'état de la DB que juste avant (ou juste après) de telles opérations.
Checksum et Torn Page Detection
SQL serveur ne fait pas confiance au disque dur lorsque les informations sont écrites. Les erreurs d'écritures ne sont généralement pas détectée durant cette opération. Le disque dur, peut même ne pas savoir que l'information écrite n'est pas correcte si l'information est corrompue en amont par le matériel.
Pour s'assurer de la fiabilité des informations, SQL server stocke un checksum pour chaque data page. Lors de la lecture le checksum est comparé à celui recalculé sur la page chargée, les données sont corrompues s'ils ne correspondent pas.
Lors d'une migration depuis SQL 7, il est important de savoir que la vérification par checksum n'est pas activée. L'ancienne méthode de vérification (TORN_PAGE_DETECTION) est toujours active même si elle est moins fiable.
Type de donnée
Voici quelques précision relatif au nouveaux types de données (et certains types déjà connus).
Table: Les tables (ou RowSet) sont aussi des types de données. Il est possible de faire des query sur le résultat d'un ou plusieurs autres query.
GUID: Le type GUID permet d'assigner une valeur GUID réputée unique (worldwide unique value).
La fonction sql newGuid() permet d'obtenir un nouvel GUID.
Ce qu'il y a de nouveau, c'est la fonction newSequentialGuid() qui permet de générer des GUID sequentielles... franchement utile lorsque des records doivent être ordonnés.
NVarchar: Utilise 2 bytes pour stocker la longueur de la chaine de caractère.
C'est donc une erreur d'écrire Varchar(1) pour espérer gagner de la place.
TimeStamp: Assigne une valeur unique (dans la DB) au champs lorsqu'un record est ajouté ou modifier. Ce type de donnée est utilisé pour s'assurer que le record n'a pas été modifié depuis la dernière lecture (Ado en fait un usage intensif).
Xml: Permet de stocker nativement du contenu XML. Ce type de donnée permet en autre d'exécuter des methods manipulant l'information. Il est également possible de transformer des données XML en rowset (et inversement).
Schema
Une modification très importante depuis SQL2000 sont les schémas. et représente un virement à 180 degré d'une fonctionalité/syntaxe déja existante (dbo.tablename).
Contrairement à ma première idée, les schémas en SQL2005 n'ont rien à avoir avec les schéma graphique de DB.
Les schemas permettent de regrouper les objects SQL (table, stored proc, etc) de façon logique. C'est un peu comme des groupes ou des domaines.
Les schemas sont définis à l'aide d'instruction appropriée et doivent être référencés dans les divers objects aussi bien lors d'operations de création que d'écritures.
Par exemple, dans le schéma "Person", il serait opportun de créer les tables contactInfo, Address, PersonInfo, Affiliation, etc.
L'identification correct de la table se fait en la préfixant avec le schema adéquat.
Par exemple:
select * from Person.PersonInfo
Ce qu'il est important de savoir, c'est que l'utilisation les schémas sont obligatoires!!!L'utilisation des schemas permet en autre de grandement faciliter la gestion de la sécurité... puisque les droits d'accès peuvent être liés au schémas.
Il est aussi utile de noter que chaque utilisateur SQL dispose d'une configuration "default schéma" qui sera automatiquement utilisé pour les requêtes SQL ne précisant pas de schéma.
Attention à la migration des bases de données:
Pour des raisons de compatibilités ascendante, le schéma "dbo" (db owner) existe en SQL 2005 et est maintenu.
Ce dernier, totalement obsolète, ne devrait pas être utilisé pour la création de nouvelles bases de données.
Cependant, le schéma "dbo" existe pour faciliter la migration de DB plus anciennes.
Ainsi, lors d'une migration, les tables sont migrées en utilisant le schéma "dbo".
Lors des requêtes SQL d'anciennes applications (select * from PersonInfo), SQL server essaye d'abord de résoudre le nom de la table dans le "default schema" configuré pour l'utilisateur. Si ce dernier n'existe pas, alors une tentative est faite sur le schéma "dbo".
Pour les anciennes application n'utilisant pas les schémas dans la notation SQL, il est facile de constater qu'une surcharge de traitement est nécessaire pour obtenir les même données.
Note: En raison de l'utilisation de "dbo", Une assignation arbitraire du "default schema" de l'utilisateur SQL peut avoir des conséquences catastrophiques pour des applications migrées.
Partitioning large table
Lorsqu'une large table content des données devenues "read-only" par l'usage ou tellement vieilles, il convient de partitionner la table (et les indexs liés).
Cela permet à SQL server de focaliser l'activité et usage du cache sur la partition active de la table (nb: les indexes sont partitionnés avec la table).
Le but de la partition est de garder une partie active de taille raisonnable... et donc performante.
Les partitions plus anciennes totalement inactive en écriture peuvent être stockées sur des file group read-only (et peuvent par conséquent être stockés sur des disques compressés) limitant l'implication de locks.
Finalement, la partition à l'avantage de stopper le "lock escalation". Ainsi, la lecture de vieille données n'engendrera pas de read-lock sur la partition active.
Il va de soit que l'accès aux différentes partitions est totalement transparente.
File-Groups et Log File
Il est possible de créer differents File-Group pour y stocker une partie des tables (ou des indexes).
Les file-groups permettent de répartir les tables dans différents fichiers et sur différents disques. Cela permet d'optimiser les performances des tables les plus sollicitées en les placants sur des disques rapides (évitant ainsi de pénaliser les applications transactionelles).
Les file-groups peuvent également êtres marqués comme Read-Only. C'est d'ailleurs conseillé pour les tables (ou partitions de tables) n'étant plus modifiées. Cela permet à Sql Server de ne plus avoir à gérer les locks et permet à l'administrateur de placer le file-group sur un lecteur compressé (seul cas d'utilisation toléré).
Note 2: Le stockage de File-Group read-only sur des lecteurs compressés peut devenir interessant lorsque les parties "inactives" contiennent des millions de records.
Par ailleurs, il est conseilé de stocker le Log file sur un disque séparé (et rapide).
Cela limite la surcharge IO qui devrait être partagé entre le stockage des données et celui du Log File lorsqu'un seul disque est utilisé (Disk Head Overheat situation).
Performances
Pour préserver les performances:- Eviter l'usage de curseur si le code peut être écrit en SQL relationnel. L'exécution des curseurs est jusque 30 fois plus lent.
- Eviter l'utilisation des tables temporaires là ou cela est possible. Les tables temporaires ne sont pas optimisée pour la performance et consomment de la mémoire.
- Garder le "Page Hit Ratio" a l'oeil. Il indique le nombre de fois que SQL server va directement retrouver l'information nécessaire depuis sa mémoire cache. Si le hit ratio tombe en dessous de 95%, il faut agir.
