Microsoft SQL Days 2005 - Session d'Aix-en-Provence

Évolution de la plateforme
Les trois piliers de SQL Server 2005
Architecture et disponibilité
     Les causes d'indisponibilité
     Les solutions humaines
     Les solutions technologiques
     Les fondations
     Redémarrage rapide (fast recovery)
     Cliché (Snapshot)
     Les solutions de Haute Disponibilité
          Cluster de basculement
          Cluster distant
          Transfert de Journaux
          Base de données Miroir (Database Mirroring)
          La Réplication
     Conclusion sur les architectures hautement disponibles
Microsoft .NET 2.0 dans SQL Server 2005 (SQLCLR)
     SQL Server et .NET Framework 2.0
     Quelle technologie utiliser ?
     Conclusion sur l'intégration de Microsoft .NET 2.0 dans SQL Server 2005
Productivité
     Les nouveautés de Transact-SQL 2005
          Au niveau des instructions DDL
          Au niveau des instructions DML
     Gestion des identifiants et des utilisateurs
XML avec SQL Server 2005
Support des Web Services en natif avec SQL Server 2005
     Gestion des Web Services
          Configuration
          Sécurité
          WSDL
Conclusion

Le site de SQL Server: http://www.microsoft.com/france/sql/
Présentation de SQL Server 2005: Microsoft France
Le site U.S de SQL Server 2005 http://www.microsoft.com/sql/2005/

SQL Server en exemples:
Mappy
Accenture

Voici enfin le bilan de la session d'Aix-en-Provence des SQL Days 2005 qui s'est déroulée le Mardi 14 Juin 2005. La journée a été riche et surtout très intense. Il fallait vraiment s'accrocher !
Bravo et merci à toute l'équipe des SQL Days pour cette prestation de grande qualité :-)

Cette journée nous a permis de découvrir les nouveautés du SGBDR SQL Server 2005 ou Yukon (nom de code) pour les initiés.
Avant d'évoquer ces nouveautés, nous allons rappeler rapidement l'évolution de la plate-forme et présenter les trois piliers de SQL Server 2005.

Évolution de la plate-forme

Nous pouvons identifier trois générations dans l'évolution de SQL Server: SQL Server 6.0/6.5 pour la première génération, SQL Server 7.0 et SQL Server 2000 pour la seconde génération, SQL Server 2005 pour la troisième génération.

Voici un récapitulatif des objectifs de chaque génération:

Les trois piliers de SQL Server 2005

Les trois piliers de SQL Server 2005 se nomment: Enterprise Management Data, Developer Productivity et Business Intelligence. Le tableau suivant indique ce que prend en charge chaque pilier.

Architecture et disponibilité

Les causes d'indisponibilité

Les causes d'indisponibilité d'un serveur sont de deux natures:

• Les arrêts non programmés (20%)
• Les arrêts programmés (80%)

Il existe des solutions à la fois humaines et technologiques pour pallier le problème d'indisponibilité.

Les solutions humaines

Au niveau du développeur:

• les formations et l'entraînement sont absolument indispensables
• la répétition de tests est fondamentale pour faire face à un désastre, le plus rapidement possible

Au niveau de la stratégie, il est nécessaire de:

• mettre en place des procédures
• prendre en compte le changement
• concevoir un système de Tests et Pré-production

Cela revient donc à déterminer très précisément les personnes qui doivent agir en cas de désastre. Le but est aussi de savoir quand et comment ces personnes doivent agir.

Au niveau de la technologie utilisée:

• elle doit être adaptée au contexte matériel, humain, ...
• elle doit être maîtrisée !

Les solutions technologiques

L'indisponibilité est due, soit à des interruptions non-planifiées, soit à des opérations de maintenance.
On distingue trois cas d'interruptions non planifiées et deux cas d'opérations de maintenance.

Interruptions non-planifiées:

• arrêt du système
• corruption des données
• erreurs humaines

Opérations de maintenance:

• réorganisation des données
• changements liés au système

Voici les solutions proposées par SQL Server 2005 pour pallier les cas d'indisponibilités précédemment cités:

Les fondations

Les fondations doivent être solides. Ces fondations se présentent sous trois composantes majeures.

Redémarrage rapide (fast recovery)

Dans SQL Server 2000, la base était accessible après avoir rejoué les transactions validées et défait les transactions non validées.
Dans SQL Server 2005, la base est accessible dès que les transactions validées ont été rajoutées. Les pages correspondant à des transactions non validées restent verrouillées jusqu'à ce que les transactions soient défaites.

Avantage: le temps de redémarrage (et de basculement) est réduit.

Cliché (Snapshot)

Le Cliché est un nouveau mode d'isolation des transactions proposé au niveau de la base de données. Grâce à cette fonctionnalité, les utilisateurs peuvent accéder à la dernière valeur validée tout au long de la transaction au moyen d'une vue temporaire cohérente. Cette fonctionnalité permet d'augmenter la concurrence des accès tout en réduisant les cas de blocage.

Les solutions de Haute Disponibilité

Il existe deux solutions pour la Haute Disponibilité:

• Le basculement automatique, sans perte de données:
  - Cluster de basculement et Cluster Distant
  - Base de données Miroir
• Le basculement manuel, avec perte de données acceptée:
  - Transfert de journaux
  - Réplication

Cluster de basculement
Un Failover Cluster (cluster de basculement) est un ensemble de serveurs configurés afin qu'un serveur prenne automatiquement le relais d'un serveur défaillant et poursuive le traitement. Tous les serveurs du cluster sont associés à au moins un autre serveur du cluster identifié comme son serveur de secours.
Le cluster de basculement est la solution pour la haute disponibilité.
Le cluster de basculement:

• permet la détection d'erreurs et le basculement automatique
• fournit un redémarrage à chaud après un redémarrage de l'instance. Les journaux sont rejoués.

Cette architecture s'utilise dans tous les environnements où une disponibilité maximale est requise.
Pour en savoir plus sur le cluster de basculement: MSDN

Cluster distant
Une cluster distant propose une solution mixte: haute disponibilité + protection contre les désastres

Cette architecture s'utilise dans tous les environnements où une disponibilité maximale est requise sur des données critiques.

Transfert de Journaux
Un transfert de Journaux propose un mécanisme de récupération en cas de désastre. Le principe consiste à recopier les fichiers de données (initialisation) puis les journaux (régulièrement) sur un ou des serveurs distants.

Cette architecture s'utilise:

• Pour se protéger d'un désastre sur le site principal de production
• Souvent en complément d'un cluster

Base de données Miroir (Database Mirroring)
Dans le cas d'une base de données Miroir, on considère un serveur principal et un serveur distant qui joue le rôle de Miroir.
Chaque serveur possède son propre support de stockage. Si le Principal plante alors les connexions des clients sont redirigés vers le Miroir.
Voici comment cela se passe étape par étape:

• Étape 1: une application sollicite le Principal.
• Étape 2: le Principal envoie les données au Miroir et rejoue les journaux. En asynchrone, il enregistre les données.
• Étape 3: le Miroir rejoue les journaux. En asynchrone, il enregistre les données. Le Miroir est toujours en train de rejouer les journaux.
• Étape 4: le Miroir renvoie un message au Principal.
• Étape 5: le Principal répond à l'application.

Il existe trois modes:

• Synchrone avec témoin: permet le basculement automatique.
• Synchrone sans témoin
• Asynchrone

Le rôle du Témoin est de reconnaître le serveur Principal et de changer le statut de la base.

Une base de données Miroir propose une solution mixte: haute disponibilité + protection contre les désastres.
Le principe est de répliquer les écritures dans les fichiers journaux. Cela permet un redémarrage à chaud après avoir rejoué les dernières écritures journaux.

Cette architecture s'utilise dans tous les environnements où une disponibilité maximale est requise sur des données critiques.

La Réplication
La Réplication est un mécanisme de niveau applicatif, peu adapté à la haute disponibilité. La Réplication consiste à propager des objets ou des transactions de la base de données.

Cette architecture s'utilise pour se protéger d'un désastre si le besoin applicatif était pré-existant.

Conclusion sur les architectures hautement disponibles

Il n'y a pas de solution miracle. Il est impératif de bien étudier les besoins afin de mettre en place une combinaison des techniques précédemment présentées. Par exemple, il sera possible d'utiliser conjointement une base de données Miroir et un Cliché.

Microsoft .NET 2.0 dans SQL Server 2005 (SQLCLR)

SQL Server et .NET Framework 2.0

Voici une grande nouveauté: l'intégration de la CLR dans SQL Server 2005 !
Ce changement permet de modifier considérablement certains aspects de SQL Server. D'une part en terme d'automatisation des tâches d'administration de bases (avec SMO), d'autre part du point de vue programmation (il est par exemple possible d'écrire une procédure dans un langage CLR).
SQL Server et le .NET Framework 2.0 apportent donc une évolution au niveau de l'environnement de programmation mais aussi au niveau de la sécurité.

Environnement de programmation pour:

• Les fonctions
• Les procédures stockées
• Les triggers
• Les types de données utilisateur (UDF)
• Les agrégats

Sécurité:

• Intégration de la sécurité SQL Server et CLR
• Trois niveaux de sécurité: safe, External-Access et Unsafe

Voici l'ensemble des permissions pour chaque niveau de sécurité CLR .

Quelle technologie utiliser ?

Avec SQL Server 2005, nous pouvons donc utiliser conjointement le langage T-SQL et les langages .NET.
Cette union peut sembler déroutante et susciter de nombreuses questions: dois-je utiliser .NET ou T-SQL ? Puis-je utiliser indifféremment un langage CLR ou le langage T-SQL pour réaliser une même tâche ?
Il faut bien comprendre que .NET ne remplace pas T-SQL: l'objectif du code CLR est d'implanter des fonctionnalités difficilement envisageables (voire impossible) en T-SQL. Par exemple, nous pouvons utiliser le langage C# pour définir des types de données complexes. Le langage T-SQL est indispensable dans les tâches de requêtage (c'est d'ailleurs son but).

Le tableau suivant indique les technologies qu'il est conseillé d'utiliser selon le type de tâche à accomplir.

Conclusion sur l'intégration de Microsoft .NET 2.0 dans SQL Server 2005

L'intégration de la plateforme .NET dans le moteur de base de données lui-même ouvre des perspectives très intéressantes pour les développeurs. Cependant, il ne faut pas chercher à utiliser exclusivement cette intégration. Il faut tirer parti de ce qu'il y a de mieux dans le monde T-SQL et SQLCLR (SQLCLR fait référence à l'intégration de la CLR dans SQL).

Productivité

Les nouveautés Transact-SQL 2005

Au niveau des instructions DDL
SQL Server 2005 dispose de nouvelles instructions DDL: varchar(max), nvarchar(max) et varbinary(max).
Alors qu'il était possible d'utiliser des fonctionnalités XML avec SQL Server 2000, SQL Server 2005 propose directement un type de données xml natif.
En ce qui concerne les triggers, le champ d'utilisation de ces derniers s'est considérablement élargi. Avec SQL Server 2000, le déclenchement d'un trigger ne pouvait qu'avoir lieu sur trois instructions DML: INSERT, UPDATE et DELETE.
Désormais, SQL Server 2005 permet l'activation d'un trigger sur d'autres instructions DDL telles que DROP TABLE ou CREATE VIEW

Au niveau des instructions DML
De nombreuses nouveautés sont à présenter au niveau des instructions DML: gestion des exceptions, instructions CTE, l'opérateur Pivot, les fonctions de classement, la clause TOP et la clause Output.

• SQL Server 2005 fait preuve d'une plus grande souplesse en matière de gestion d'exceptions: l'instruction TRY...CATCH permet d'intercepter certaines erreurs telles que les violations de contraintes.
• Common Table Expression (CTE) est une nouvelle fonctionnalité qui permet d'obtenir un jeu de résultats temporaire, qui dérive d'une requête simple de type: SELECT, INSERT, UPDATE ou DELETE. CTE permet par exemple de simplifier considérablement la mise en place de requêtes récursives (point délicat qui exigeait jusqu'ici un code T-SQL très complexe).
• L'opérateur PIVOT permet de récupérer un ensemble de données sommées et croisées pour en faire des résultats croisés. L'opérateur UNPIVOT réalise le travail inverse: il génère des résultats croisés et sommés en partant de données croisées.
• La fonction Row_Number est l'une des nouvelles fonctions de classement. Elle permet de paginer les données en utilisant un enregistrement séquentiel sur chaque enregistrement d'une instruction SELECT.
  SQL Server 2005 propose également Rank et Dense_Rank comme fonctions de classement. Rank autorise un même numéro pour deux enregistrements successifs alors que Dense_Rank ne permet pas les sauts de séquence dans les résultats. Prenons le cas d'une course de F1 avec 6 participants (si si ça existe: voir le GP du dimanche 19 Juin aux U.S.A ;) ). Dans le cas d'une égalité entre les deux premiers concurrents, la fonction Rank renvoie le résultat suivant: 1, 1, 3, 4, 5, 6. Dense_Rank renvoie: 1, 1, 2, 3, 4, 5.
  Il existe aussi la fonction NTile qui permet de séparer les enregistrements en parts égales.
• La clause TOP n permet d'agir sur les n premiers enregistrements. Le programme suivant affiche les n premiers produits dans l'ordre alphabétique:

				SELECT	TOP (@n) NumeroProduit, Nom
FROM	Production
ORDER BY	Nom			

• La clause OUTPUT présente un intérêt majeur en terme de gain de temps et de performance. Nous reviendrons sur cette clause dans une partie technique.

Gestion des identifiants et des utilisateurs

Nous présentons ici deux nouvelles instructions de sécurité qui ont été ajoutées au langage T-SQL: CREATE LOGIN et CREATE USER.

• CREATE LOGIN permet de créer des comptes système. Grâce à cette instruction, nous pouvons gérer des options avancées telles l'expiration ou la stratégie des mots de passe. Le code suivant permet de créer un compte SQL Server pour Machin avec comme mot de passe cestmachin et baseMachin comme base de données par défaut.

	CREATE LOGIN Machin
WITH PASSWORD = 'cestmachin',
DEFAULT_BASE = baseMachin,
CHECK_POLICY = ON,
CHECK_EXPIRATION = ON		

• Les instructions CHECK_POLICY et CHECK_EXPIRATION permettent de définir les règles d'expiration et de stratégies Windows.
• CREATE USER permet de créer des utilisateurs pour les bases de données du serveur. Le code suivant permet de créer un utilisateur nommé Machin pour la base de données baseMachin.

	USE baseMachin
GO
CREATE USER Machin		

XML avec SQL Server 2005

Le type de données xml possède cinq méthodes qui permettent la manipulation des données XML. Les quatre méthodes suivantes utilisent un syntaxe XQuery.
La méthode exist permet de vérifier l'existence de données XML. Cette méthode ne doit pas seulement être employée pour vérifier l'existence de données. Elle doit permettre la vérification de certaines valeurs dans les données XML.
La méthode value permet d'extraire une valeur unitaire du code XML en tant que type de données natif SQL Server (par exemple, un int ou un varchar).
La méthode query permet d'exécuter une instruction XQuery sur des données XML et de retourner une donnée XML non typée. Cette méthode, contrairement à la méthode value, retourne un sous-ensemble d'éléments des noeuds xml interrogés.
La méthode modify permet de modifier des données XML sans avoir à réécrire toute la structure de données.
La méthode nodes, qui utilise une extension de XQuery pour les mises à jour, permet de transformer des données XML en données relationnelles.

La clause FOR XML est utilisée pour transformer des données relationnelles en XML.
La fonction OPENXML permet le parcours de données XML de telle sorte qu'elles puissent être stockées en tant que données relationnelles.

Support des Web Services en natif avec SQL Server 2005

Gestion des Web Services

Configuration
La procédure de création d'un service web sous SQL Server 2005 est assez simple.
La méthode consiste à créer des procédures stockées et des fonctions utilisateur (UDF) qui seront ensuite définies en tant que point de services. Un point de services est un service Web dans la terminologie SQL Server.
La nouvelle instruction Transact-SQL, CREATE ENDPOINT, permet de concrétiser le service Web en créant un point de services.

Sécurité
Lors de la création d'un point de services, nous devons définir une clause HTTP qui permet de dicter les règles d'accès au service web. Cette clause contient, entre autres, un paramètre d'authentification nommé AUTHENTICATION qui permet de définir le mode d'authentification du service web. Le tableau suivant résume les 4 types d'authentification.

PORTS est un paramètre défini dans la clause HTTP. Par exemple, PORTS = (CLEAR) indique l'utilisation du port 80 (port HTTP).
Il est également possible de faire appel à des en-têtes WS-Security pour authentifier la connexion à SQL Server.

WSDL (Web Service Description Langage)
Il est possible d'afficher l'ensemble des services Web disponibles en utilisant le WSDL. Pour cela, nous devons accéder aux dossiers définis au niveau de l'instruction CREATE ENDPOINT et ajouter ?wsdl à la fin du chemin.
Exemple: http://winlocal/wstests?wsdl, où winlocal est le nom de notre serveur.
Il existe aussi une version simplifiée du WSDL qui remplace les types XSD originaux par des types SQL: http://winlocal/wstests?wsdlsimple.
Les procédures stockées permettent de générer son propre WSDL.

Conclusion

C'est déjà fini ?!
Oui pour notre bilan sur les SQL Days 2005.
Absolument pas en ce qui concerne les nouveautés de SQL Server 2005. Nous n'avons pas traité l'intégralité de ces nouveautés pour plusieurs raisons. Premièrement, il est préférable d'étudier certaines notions d'une façon pratique plutôt que théorique (ou au moins les deux en même temps). Deuxièmement, les nouveautés de SQL Server 2005 sont nombreuses. Il est donc préférable de consulter le site MSDN de Microsoft pour obtenir un maximum d'informations. Enfin, il ne s'agit que de la version Beta 2 de SQL Server 2005. Cela signifie donc que d'importantes modifications peuvent être apportées entre aujourd'hui et le mois d'octobre (date prévue pour la sortie de Microsoft SQL Server 2005).
Bien évidemment, nous reviendrons sur SQL Server 2005 mais cette fois-ci, nous plongerons dans le code !

Ce que nous n'avons pas évoqué

Voici les points importants de SQL Server 2005 que je n'ai pas traité:

• La réplication avec SQL Server 2005
  - Réplication transactionnelle
  - Réplication depuis Oracle
• L'optimisation
• Le partitionnement (partitionnement des données)
• Integration Services et Analysis Services

Voir les vidéos et slides des SQL Days 2005.

Samedi 25 mai 2005