Vue densemble des fonctionnalits dadministration de SQL Server
Vue d’ensemble des fonctionnalités d’administration de SQL Server 2005 patricg@microsoft. com Architecte Infrastructure
Agenda • • Généralités La migration La sécurité L’administration au quotidien La gestion des données La haute disponibilité Les architectures distribuées • Améliorations pour les développeurs • Composants de la partie décisionnelle
Une version majeure 1 ere Génération 2ème Génération SQL Server 6. 0/6. 5 l l l Différentiation de Sybase SQL Server Intégration à Windows Intégration de la Réplication Objectifs long terme : SQL Server 7. 0 l l l Réécriture du moteur relationnel Gestion automatisée des ressources Intégration des services OLAP et ETL 3ème Génération SQL Server 2000 l l l Performance, montée en charge Support du XML Intégration des services de Notification, de Reporting et de Data Mining Performance et Sécurité Intégration du Décisionnel SQL Server 2005 l l l Haute disponibilité Sécurité Intégration CLR XML Natif Nouveau moteur ETL d’entreprise 64 bits IA-32 et x 64 Valeur de l’offre Gestion automatique
Une offre globale
Nouveauté SQL Server 2005 Une offre adaptée à vos besoins Express Workgroup Standard Adaptée aux développeurs pour apprendre, construire et déployer La base de données pour les petites entités et les activités en croissance La plate-forme complète pour la gestion et l’analyse des données destinée aux entités moyennes à grandes 1 Proc 1 Go mémoire 4 Go base 2 Proc 3 Go mémoire 4 Proc versions 32 & 64 -bit Pas de limitation + Partitionnement Outil d’administration simplifié Outil d’administration Management Studio Miroir de BD limité Miroir BD complet & Cluster 8 nœuds Sécurité intégrée SQL Agent Support XML & Améliorations T-SQL Assistant d’optimisation Intégration. Net & CLR Recherche en texte intégral Serveur de rapports Import/Export Client Réplication Gratuit Serveur de Réplication limité Transfert journaux (Log shipping) Cluster 2 noeuds Integration Services (ETL) Analysis Services (Serveur OLAP) Data Mining Réplication complète Notification Services Prix croissant Enterprise La plate-forme intégrée pour la gestion et l’analyse de données des applications critiques de l’entreprise Opérations en ligne et redémarrage rapide Clichés BD Outils avancés (ETL, OLAP et Data Mining) Serveurs de rapports mutiples
Une offre crédible Références 2000 Transactionnel Enregistrement des données de marché Pic de 12 000 Transaction par Seconde Internet Auction Co. , Ltd (Korea ) SGBDR et moteur OLAP Volumétrie : 2, 7 To Transaction par seconde : 3 600 CRM SGBDR pour Siebel Nb d’utilisateurs: 8 000 Siebel e. Service Volumétrie: 1 To SAN ERP Utilisation de SQL comme BDD pour SAP Volumétrie : 1 To+, 5 To Stockage Nb d’utilisateurs : 2000 concurrents ERP Utilisation de SQL Server pour l’appli. de facturation Volumétrie: 5 To & 33 Milliards de lignes & 15 To total Services Web 5 Millions d’images, + 10 Millions de fichiers 99, 99 % de disponibilité 20 Serveurs Décisionnel SQL Server 64 bits comme moteur OLAP Volumétrie: 2, 5 To & Croissance de 30% / an Coût: réduct. 70% / Perf multipliées par 4
1 P 38 K tpm. C 1. 2 M tpm. C 0. 99 $ / tpm. C Unisys: 3. 97 $/tpm. C 16 IBM: 492 K P tpm. C 64 P TPC-C results to the left: Dell Power. Edge 2800, 38, 028 tpm. C, 0. 99 $/tpm. C, available 11/8/05; IBM e. Server x. Series 460 16 P c/s, 492, 307 tpm. C, 6. 37 $/tpm. C, available 5/20/2006; Unisys ES 7000/600 Enterprise Server (16 P), 376, 045 tpm. C, 3. 97 $/tpm. C, available 1/3/2006; HP Integrity Superdome, 1, 231, 434 tpm. C, 4. 91 $/tpm. C, available 5/5/06. Results as of 12/5/05. TPC-C 100 GB 12, 600 Qph. H 9. 43 $/Qph. H 1 TB 16 P 3 TB 32 P HP: 17, 725 Qph. H 30, 956 Qph. H Bull: 25. 48 $/Qph. H TPC-H results above: HP Pro. Liant DL 585 -G 1 12, 600 Qph. H@100 GB, 9. 43 $/Qph. H@100 GB, available 11/7/05; Bull Nova. Scale 5160, 17, 059 Qph. H@1 TB, 25. 48 $/Qph. H@1 TB, available 5/5/06; HP Integrity Superdome, 30, 956 Qph. H@3 TB, 76. 82 $/Qph. H@3 TB, available 5/5/06; NEC Express 5800/1320 Xe, 28, 390 Qph. H@3 TB, 66. 52 $/Qph. H@3 TB, available 5/5/06. Results as of 12/5/05. Benchmarks TPC-H
Administration La migration • Les stratégies possibles : • Montée de niveau • Migration • Les différentes étapes • La préparation • L’outil « Conseiller de mise à niveau » Migration Vers 2005
Montée de niveau d’une instance Upgrade SQL Server 7. 0/2000 Instance: A SQL Server 2005 Instance: A Montée de niveau
Migration SQL Server 2005 Instance: B SQL Server 7. 0/2000 Instance: A Compare & Migration Vérifie Vérifiée!
o m é ! D Rapport du Conseiller de mise à niveau SQL Server Mise à niveau ou Migration Prêt Après la mise à niveau ou la migration Avant la migration ou la mise à niveau
Administration Nouveautés pour la sécurité • SQL Server Surface Area Configuration / • Configuration de la surface d’exposition • Séparation utilisateur – schéma • Encryption dans la base et gestion des clés • Mêmes politiques de mots de passe que Windows Server 2003 Nouveautés 2005
SQL Server Surface Area Configuration • Toutes les fonctionnalités présentant un risque potentiel pour la sécurité sont désactivées par défaut o D m é !
Séparation Utilisateur. Schéma • Chaque schéma est possédé par un rôle ou un utilisateur • Chaque utilisateur possède un schéma par défaut pour la résolution des noms • • Les objets BD appartiennent à un schéma Base A comme défaut possède Schéma 1 possède La création d’un objet dans un schéma nécessite les droits – CREATE TABLE et – La possession du schéma ou ALTER ou CONTROL Rôle 2 Utilisateur 1 Rôle 1 Schéma 2 Schéma 3 SP 1 F 1 Tab 1 possède
Le problème… Table Utilisateur 2 Fonction Objet Vue SGBD Possédé par Utilisateur 1 Procédure Résolution des noms Select * from Foo l Utilisateur. foo l Dbo. foo Supprimer un utilisateur pouvait nécessiter de modifier l’application !
La solution… Table Fonction Objet SGBD Vue r a ép Appartient à Possédé par Schéma d sé s Po Possédé par User 2 User Procédure Supprimer un utilisateur NE nécessite PLUS de modifier l’application
Résolution de nom Table Fonction r Appartient à éd s os P a ép User 2 Schéma Vue Procédure Schéma par défaut Résolution du nom Select * from foo Utilisateur 1 Utilisateur 2 Utilisateur 3 • S 1. foo Schéma par défaut S 1 • Dbo. foo
Encryption des données et gestion des clés • Possibilité d’encrypter et de décrypter les données via les mécanismes suivants : – Certificats – Clés symétriques – Clés asymétriques • Fonctions T-SQL encrypt et decrypt
Administration L’administration au quotidien • Des outils optimisés • SQL Server Management Studio • Plan de maintenance graphiques (utilisation du moteur SSIS) • Améliorations au niveau de la sauvegarde / restauration • Restauration de fichiers et de pages en ligne • Opérations en ligne • Protection contre les erreurs (Cliché de BD ou « Snapshot » ) Mais aussi : • Vues dynamiques (sys. dm_*) • clause ATTACH_REBUILD_LOG • procédure sp_create_plan_guide • paramètre AUTO_UPDATE_STATISTICS_ASYNC • fichiers de paramétrage XML pour l’utilitaire bcp Nouveautés 2005
Des outils optimisés Propriétés « Serveur » o D m é !
Plans de maintenance graphiques
Opérations en ligne
Il n’est plus nécessaire de s’arrêter pour… • La maintenance des indexes « Online » – Create, Rebuild, Reorganize, Drop, Add et drop constraint – Opérations complètement parallèles • La restauration de pages et de fichiers – La base reste accessible – Seule les données en cours de restauration sont inaccessibles. • On peut toujours les faire « Offline »
Exemple de création d’index en ligne Nouveau SS 2005 • Création d’index CREATE CLUSTERED INDEX myidx ON mytab (a, b, c) WITH ( PAD_INDEX = ON, FILLFACTOR = 75, ONLINE = ON, MAXDOP = 6) ON filegroup 1 • Reconstruction d’index (était DBCC DBREINDEX) ALTER INDEX myidx ON mytab REBUILD WITH ( ONLINE = ON, MAXDOP = 4 )
Sauvegardes & Restaurations améliorées • • • Possibilité de faire des sauvegardes sur 2 à 4 périphériques (identiques) en parallèle Possibilité de calculer une somme de contrôle ( « checksum » ) pour garantir la fiabilité de la sauvegarde RESTORE VERIFY_ONLY, – STOP_ON_ERROR et CONTINUE_AFTER_ERROR BD • Svg 1 Svg 2 • Nouveau SS 2005 Copy-Only Backups Restaurations partielles : groupe de fichiers, ou page – la base reste accessible pendant la restauration tant que l’on accède pas au fichier en question Permet de faire une sauvegarde complète sans perturber la chaîne des sauvegardes Idéal pour faire une copie de la base de production sans perturber l’exploitation BACKUP DATABASE <dbname> WITH COPY_ONLY
Cliché BD (“DB Snapshot”) Nouveau SS 2005 Comment ça marche ? CREATE DATABASE Mon. Snap AS SNAPSHOT OF Ma. Base USE Ma. Base UPDATE (pages 4, 9, 10) Ma. Base Page 1 2 3 4’ 4 5 6 7 USE Mon. Snap SELECT (pages 4, 6, 9, 10, 14) Si nécessaire : 8 9’ 9 10’ 10 11 12 13 14 15 16 Mon. Snap – Cliché en lecture simple RESTORE DATABASE Ma. Base FROM SNAPSHOT Mon. Snap
Administration La gestion des données • Partitionnement
Partitionner ? Données Non Partitionnées Données Partitionnées A, B, C, D, E, F, G, H, I, J, K, L A, B, C D, E, F G, H, I J, K, L • Quoi? – Partitionnement de tables, d’indexes, vues indexées • Pourquoi? – Grosses tables • Ajout/suppression facile de grandes quantités de données (Archivage) • Backup/Restore par groupe de fichiers – Mieux utiliser des opérateurs parallèles sur des machines avec beaucoup de CPU (16, 32, …) – Concurrence d’accès (Lock Escalation) • Comment? – Partitionnement d’enregistrement (horizontal)
3 étapes • A chaque ligne correspond une partition grâce à la « partition function » CREATE PARTITION FUNCTION Part_Fon (int) as RANGE RIGHT FOR VALUES (10, 20, 30) • A chaque partition correspond un emplacement physique (Filegroup) grâce au « partition scheme » CREATE PARTITION SCHEME Part_Sche AS PARTITION Part_Fon TO ([Filegroup 1], [Filegroup 2], [Filegroup 3], [Filegroup 4]) • CREATE TABLE Employees (Emp. Id int, Emp. Name varchar(50)) ON Part_Sche (Emp. Id); Un index utilisant une fonction et une clef de partitionnement similaire à la table est dit « aligné » best practice
Tables & Index partitionnés • La clé de partitionnement doit correspondre à une seule colonne • Les tables et les index sont créés sur le schéma de partition (au lieu des Filegroups) • Le partitionnement est transparent pour les requêtes • Des tables et index différents peuvent partager des fonctions et de schémas de partitionnement Partition Fonction Partition Schéma Table ou Index 1 n n
Comment ajouter ou supprimer des partitions? • Permet de traiter le cas typique d’ajout et/ou de suppression d’une partition entière • Réalisé à l’aide de 3 opérations simples sur les partitions : – Split – Merge – Switch sépare 1 partition en 2 réunit 2 partitions en 1 échange 1 table et une partition ALTER PARTITION FUNCTION annual_range() SPLIT RANGE (‘ 2006 -01 -01’) ALTER TABLE B SWITCH TO A PARTITION 2 m é D o
Administration La haute disponibilité • Miroir de base de données • Clustering • Log Shipping • Réplication Nouveautés 2005
La problématique de la disponibilité… Arrêt du Système Interruptions Non-Planifiées Corruption des Données Erreurs Humaines Opérations de Maintenance • Cluster de basculement • Base de Données Miroir 2005 • Transfert de journaux • Réplication • Base de Données Miroir 2005 • Svg/Restauration rapide • Restauration jusqu’à un instant donné 2005 • Cliché Base de données 2005 Réorganisation des données • Opérations de maintenance 2005 des index en ligne • Tables partitionnées 2005 Changements liés au système • Cluster de basculement • Base de Données Miroir 2005
Redémarrage rapide Nouveau SS 2005 ( « fast recovery » ) • SQL Server 2000 – La base est accessible après avoir rejouée les transactions validées et défait les transactions non validées. Refaire (redo) Défaire (undo) Base Accessible temps • SQL Server 2005 – La base est accessible dès que les transactions validées ont été rejouées. Les pages correspondant à des transactions non validées restent verrouillées jusqu’à ce que les transactions soient défaites. Refaire (redo) Défaire (undo) Base Accessible Temps de redémarrage (et de basculement) réduit
Solutions pour la Haute Disponibilité Communication Miroir de BD Granularité Basculement Pertes potentielles TCP endpoints BD Automatique (qq sec) Aucune en synchrone Base unique Disques partagés Instance Automatique (qq dizaine de sec à qq min) Aucune Transfert Copie de fichiers des journaux et restauration Log Shipping Instance Manuel Transactions depuis la dernière copie Réplication Table Manuel Transactions depuis la dernière copie 2005 Cluster Améliorations 2005 Copie de fichiers et chargement
Cluster de basculement ( « Failover Cluster » ) • • • Adresse Serveur Virtuel Stockage partagé • 2005 Un espace disque partagé par plusieurs serveurs, mais un seul serveur y accède à un instant donné Détection d’erreurs & basculement automatique Plusieurs modes : – Une SEULE base, une SEULE instance – Actif / actif possible avec n instances travaillant sur n bases Fournit un redémarrage à chaud dans un délai de l’ordre de la minute Jusqu’à 50 instances Jusqu’à 8 nœuds (Windows Server 2003 x 86) ou 4 nœuds en (Win 2003 - IA-64 ou x 64) Tous les services sont compatibles–cluster: SQL Server Agent, Analysis Services, Full-Text Search Une seule installation globale Attention Un cluster ne protège pas contre les corruptions, la perte de la baie RAID, les désastres sur un site Un cluster ne permet d’augmenter la puissance
Cluster distant (ou « Geo Cluster » ) Adresse Serveur Virtuel • • Solution mixte : Haute Disponibilité + Protection contre les désastres • Bénéfices : Pas de pertes de transactions Pas d’impact sur les performances Une extension naturelle du cluster • Contraintes : – Complexité (système + SGBD + réplication disques) – Distance limitée (qq centaines de km) Utilisation de cette architecture : Tous les environnements où une disponibilité maximale est requise sur des données critique
Base de Données Miroir ( « Nouveau SS 2005 Database Mirroring » ) 1/2 Témoin Application commit Miroir Principal SQL Server Le miroir est toujours en train de rejouer les journaux Journaux Données
Base de Données Miroir ( « Database Mirroring » ) 2/2 Témoin • Bénéfices : Pas de pertes de transactions Rapidité du basculement (qq sec) Pas de contraintes sur le matériel Pas de limite de distance Solution simple SGBD uniquement • Contraintes : Impact à évaluer sur les performances en fonction du débit transactionnel sur le site primaire → Mode asynchrone possible Utilisation de cette architecture : Tous les environnements où une disponibilité maximale est requise sur des données critiques
Administration Applications distribuées • Service Broker • Réplication • Web Services Nouveautés 2005
SQL Service Broker SQL Engine APP 1 APP 2 • Services et environnement de programmation pour construire des applications réparties fiables asynchrones • Apporte une nouvelle infrastructure de communications – – – – Dialogues (conversations) bidirectionnelles Livraison fiable vers des queues locales et distantes Exactement une livraison Pas besoin du commit à 2 phases du DTC Préserve l’ordre des messages, même dans des tx séparées Messages volumineux jusqu’à 2 GB – fragmentés Pas de dépendance sur MSMQ
Cinématique Service Broker Niveau applicatif Application Cible Application Initiatrice On lit On écrit Niveau logique Dialog Conversation Service T ra n Queue smis sion Niveau physique asyn chro ne Transport m é D o Queue
Architecture Service Broker Niveau applicatif Application Cible Application Initiatrice Niveau logique Message Type Contract Dialog Conversation Service Niveau physique Queue Transport Endpoint Queue TCP/IP Transport Endpoint
Réplications • Un schéma à 3 niveaux : – 1 émetteur (publisher) – 1 distributeur (distributor) – N receveurs (subscribers) • • • 1 distributeur peut gérer plusieurs émetteurs 1 receveur peut recevoir des données de plusieurs émetteurs et les republier à son tour Tous les types de réplications permettent les 2 modes : – l’émetteur propage les modifications (push) – Les receveurs demandent les modifications (pull) 2005 Modifications possibles sur le receveur ( « peer to peer » ) Réplication des changements de schémas Nombreuses optimisations de performances Reprise des échanges arrêtés, sans réémission complète des données Réplication Fusion sur HTTPS (idéale pour la réplication vers les mobiles)
Solutions de réplication Quoi ? Quand ? Résolution des conflits Utilisations standards Cliché Snapshot Copie des objets à un instant t Périodique ment N/A Initialisation; Données faiblement modifiées Transactionne lle Les transactions Au fil de l’eau Par transaction Serveur à serveur Peu d’écarts entre les sites Fusion Merge Les données modifiées entre deux instants Périodique ment Ligne par ligne Multiples receveurs Mise à jour asynchrones D’égal à égal Peer to peer Les transactions Au fil de l’eau Par transaction Serveur à serveur Peu d’écarts entre les sites 2005
Réplication Transactionnelle: Replication Peer-to-Peer “Ouest” “Nord” Logreader Agent Dist DB Distribution Agent Logreader Agent “Sud” Logreader Agent Dist DB Distribution Agent 2005 Distribution Agent
Réplication depuis Oracle Réplication Transactionnelle • Une base Oracle en tant qu’émetteur – v 8+, Unix , Linux, Windows • Administration depuis SQL Server – Aucun logiciel ne doit être installé sur la base Oracle • Utilise pleinement les fonctions de Réplication de SQL Server – Réplication Transactionnelle et par Snapshot SQL Server Distributeur • L’inverse est également possible (déjà disponible en 2000) Abonnés
Gestion des Web Services • Gestion des Web Services – Configuration – Sécurité – WSDL • Accès depuis une application tierce WSDL HTTP. sys HTTP Endpoint SQL Server
Nouveautés SQL Server 2005 pour les développeurs ayant un impact sur l’administration
De nouvelles possibilités pour les Développeurs Intégration renforcée avec. Net • Intégration avec Visual Studio • Intégration de la CLR dans le moteur SGBD • Nombreux langages : T-SQL amélioré, VB. NET, C#, J#. . . Extensibilité • Type de données XML natif • Type de données créé par l’utilisateur (en langage. Net) • Nouveaux ordres T-SQL • Déclencheurs sur DDL • Notification Services (requêtes asynchrones) Interopérabilité • Snapshot Isolation Nouveautés 2005
Le choix dans l’accès aux données Accès par calcul et par. Net T-SQL CLR XML Accès relationnel Accès semistructuré
Intégration. NET Microsoft® Visual Studio®. NET Project CLR hébergée dans SQL Server SQL Queries: select sum(tax(sal, state) ) from Emp where county = ‘King’ construire Assembly: “Tax. Lib. dll” déployer SQL Data Definition: create assembly … create function … create procedure … create trigger … create type … Microsoft® SQL Server™
SQL Server et. NET Framework 2. 0 • Environnement de programmation pour – – – Fonctions Procédures stockées Triggers Types définis par l’utilisateur Agrégats • Sécurité – Intégration de la sécurité SQL Server et CLR – Trois niveaux de sécurité • Safe, External-Access (verifiable), Unsafe SQL Engine CLR 2. 0 SQL OS CLR 2. 0 Windows
Nouveautés Transact-SQL 2005 • Instructions DDL (Data Définition Language) – Varchar(max), nvarchar(max), varbinary(max), xml – Déclencheurs sur les instructions DDL (DDL Triggers) • Instructions DML (Data Manipulation Language) – Gestion des Exceptions (Try/Catch) – Instructions CTE (Common Table Expression) – Opérateur Pivot – Fonctions de Classement – TOP – La Clause Output – Mode de Verrouillage (Snapshot Isolation)
Snapshot Isolation CREATE TABLE t 1 (c 1 int unique, c 2 int) INSERT INTO t 1 VALUES (1, 5) Transaction 1 Transaction 2 (Snapshot Isolation) BEGIN TRAN UPDATE t 1 SET c 2 = 9 WHERE c 1 =1 SET TRANSACTION ISOLATION LEVEL SNAPSHOT COMMIT TRAN BEGIN TRAN SELECT c 2 FROM t 1 WHERE c 1 = 1 -- SQL Server returns 5 5 -- SQL Server returns 9 COMMIT TRAN SELECT c 2 FROM t 1 WHERE c 1 = 1 -- SQL Server returns SELECT c 2 FROM t 1 WHERE c 1 = 1 Time Transaction 3 (RCSI) 9 -- SQL Server returns 9
Notifications Services : Evènements, abonnements et notifications Les évènements sont des enregistrements dans une table. Les abonnements sont aussi des enregistrements dans une table. Effectuer une jointure permet de produire des résultats (notifications) - à intervalles réguliers - à des moments précis - de complexité arbitraire Sur 3 canaux : HTTP, SMTP et fichier Subscriptions Subscriber. Id Stock. Symbol billg@msn. com MSFT Trigger 40
Fonctionnement d’une application de SQL NS Abonnés Souscriptions (millions) Sources d’Information Evènements Générateur Notifications (millions) Abonnés: ordinateurs, navigateurs, téléphones, messagers …
Business Intelligence l l Acquisition et intégration de données de système sources Transformation et agrégation de données Restituer Analyser Intégrer l l Enrichissement des données avec la logique d’entreprise Exploration de l’information avec les outils de data mining l l Présentation et distribution des données Accès aux données pour tous
Business Intelligence : Integration Services • ETL d’entreprise – Module entièrement nouveau – Haute performance et évolutivité • Meilleur Design – Environnement de développement – Gestion des versions – Analyse pas à pas visuelle du flux et des données – Nombreuses transformations natives • Extensible – Tâches personnalisées – Transformations personnalisées – Sources de données personnalisées
Ressources SQL Server • Le site français sur SQL Server 2005 (livres blancs, webcasts en français) http: //www. microsoft. com/france/sql 2005 • Le Blog de Patrick Guimonet (actualités, trucs & astuces en français) http: //blogs. technet. com/patricg • Le site Technet français (ressources techniques en français) http: //www. microsoft. com/france/technet/produits/sql/2005 • Le site du Support français (ressources techniques en français) http: //support. microsoft. com/ph/1044 • Le site global sur SQL Server 2005 http: //www. microsoft. com/sql/2005 • Le site français Microsoft SQL Server 2000 http: //www. microsoft. com/france/sql • Le site global Microsoft SQL Server 2000 http: //www. microsoft. com/sql/ • Le site Technet global (SQL Server Tech. Center) http: //www. microsoft. com/technet/prodtechnol/sql/
- Slides: 61