Vue densemble des fonctionnalits dadministration de SQL Server 2005

1
Vue densemble des fonctionnalités
dadministrationde SQL Server 2005

• patricg_at_microsoft.com
• Architecte Infrastructure

2
Agenda

• Généralités
• La migration
• La sécurité
• Ladministration 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

3
Une version majeure
1ere Génération
SQL Server 6.0/6.5

• Différentiation de Sybase SQL Server
• Intégration à Windows
• Intégration de la Réplication

4
Une offre globale
5
Nouveauté SQL Server 2005
Une offre adaptée à vos besoins
Enterprise
Express
Workgroup
Standard
La plate-forme intégrée pour la gestion et
lanalyse de données des applications critiques
de lentreprise
La plate-forme complète pour la gestion et
lanalyse des données destinée aux entités
moyennes à grandes
La base de données pour les petites entités et
les activités en croissance
Adaptée aux développeurs pour apprendre,
construire et déployer
4 Proc versions 32
64-bit Miroir de BD limité Cluster 2
noeuds Integration Services (ETL) Analysis
Services (Serveur OLAP) Data Mining Réplication
complète Notification Services
2 Proc 3 Go mémoire Outil
dadministration Management Studio SQL
Agent Assistant doptimisation Recherche en
texte intégral Serveur de Réplication
limité Transfert journaux (Log shipping)
1 Proc1 Go mémoire4 Go base Outil
dadministration simplifié Sécurité
intégrée Support XML Améliorations
T-SQL Intégration .Net CLR Serveur de
rapports Import/Export Client Réplication
Gratuit
Prix croissant
6
Une offre crédible Références 2000
7
Le marché des SGBD en 2004
Non-mainframe DB (6 Bil)By OS Platform
SQL Server domine sur Windows
Windows 51
Unix/Linux49
Windows DatabaseMarket (3.1 Bil)
IBM a été dépassé en 2003
IBM a été dépassé en 2003
Other8
Windows est la plateforme leader pour les SGBD
IBM16
SQL Server51
Oracle25
Les pourcentages reflètent des augmentations de
parts de marché
Source Gartner
8
Benchmarks
9
(No Transcript)
10
Administration

• La migration
• Les stratégies possibles
• Montée de niveau
• Migration
• Les différentes étapes
• La préparation
• Loutil  Conseiller de mise à niveau 

Migration Vers 2005
11
Montée de niveau dune instanceUpgrade
Montée de niveau
12
Migration
SQL Server 2005 Instance B
SQL Server 7.0/2000 Instance A
Migration
Compare Vérifie
Vérifiée!
13
La Planification le secret de la réussite

• Une montée de version ou une migration réussie
  nécessite une planification rigoureuse
• 3 étapes
• Tâches de préparation
• Montée de niveau ou migration proprement dite
• Tâches post-opératoires
• Tester le plan
• Exécuter le plan

14
Les différentes étapes de préparation

• 1) Sauvegarde des données
• 2) Vérification des pré-requis pour SQL Server
  2005
• 3) Lancer le Conseiller de mise à niveau 
• 4) Régler les problèmes bloquants
• 5) Relancer le   Conseiller de mise à niveau
  pour vérifier que les problèmes ont été réglés
• 6) Si possible, enregistrer des mesures de
  performances
• 7) Lancer linstallation SQL Server 2005

15
Avant la migrationou la mise à niveau
Prêt
Mise à niveau ou Migration
Après la mise à niveau ou la migration
Conseiller de mise à niveau SQL Server
16
Nostalgie
http//www.sqldbatips.com/showarticle.asp?ID46
17
Administration

• Nouveautés pour la sécurité
• SQL Server Surface Area Configuration /
• Configuration de la surface dexposition
• 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
18
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

19
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
• La création dun objet dans un schéma nécessite
  les droits
• CREATE TABLE et
• La possession du schéma ou ALTER ou CONTROL

Base
Rôle 2
Utilisateur 1
Rôle1
A comme défaut
possède
possède
possède
Schéma1
Schéma2
Schéma3
SP1
F1
Tab1
20
Le problème
Utilisateur 2
Objet SGBD
Utilisateur 1
Possédé par
Supprimer un utilisateur pouvait nécessiter de
modifier lapplication !
21
La solution
User 2
Possédé par
Possédé par
Objet SGBD
Possédé par
Appartient à
User
Schéma
Supprimer un utilisateur NE nécessite PLUS de
modifier lapplication
22
Résolution de nom
User 2
Possédé par
Appartient à
Schéma
Schéma par défaut
Utilisateur 1
Schéma par défaut S1
Utilisateur 2
Utilisateur 3
23
Encryption des données et gestion des clés

• Possibilité dencrypter 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

24
Administration

• Ladministration 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 lutilitaire bcp

Nouveautés 2005
25
Des outils optimisés
Propriétés  Serveur 
26
Plans de maintenance graphiques
27
Opérations en ligne 
28
Il nest plus nécessaire de sarrê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 

29
Exemple de créationdindex en ligne

• Création dindex
• CREATE CLUSTERED INDEX myidx ON mytab (a, b, c)
• WITH ( PAD_INDEX ON, FILLFACTOR
  75,
• ONLINE ON, MAXDOP 6) ON
  filegroup1
• Reconstruction dindex (était DBCC DBREINDEX)
• ALTER INDEX myidx ON mytab
• REBUILD WITH ( ONLINE ON, MAXDOP 4
  )

30
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
• Restaurations partielles groupe de fichiers,
  fichiers, ou page
• la base reste accessible pendant la restauration
  tant que lon accède pas au fichier en question

BD
Svg 1
Svg 2

• Copy-Only Backups
• 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 lexploitation
• BACKUP DATABASE ltdbnamegt WITH COPY_ONLY

31
Cliché BD (DB Snapshot) Comment ça marche ?
CREATE DATABASE MonSnap AS SNAPSHOT OF MaBase
USE MaBase UPDATE (pages 4, 9, 10)
MaBase
Page
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
4
9
10
USE MonSnap SELECT (pages 4, 6, 9, 10, 14)
MonSnap Cliché en lecture simple
Si nécessaire RESTORE DATABASE MaBase FROM
SNAPSHOT MonSnap
32
Administration

• La gestion des données
• Partitionnement

33
Partitionner ?
A, B, C, D, E, F, G, H, I, J, K, L
Données Non Partitionnées
A, B, C
D, E, F
G, H, I
J, K, L
Données Partitionnées

• Quoi?
• Partitionnement de tables, dindexes, 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 daccès (Lock Escalation)
• Comment?
• Partitionnement denregistrement (horizontal)

34
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 (Filegroup1, Filegroup2,
  Filegroup3, Filegroup4)
• CREATE TABLE Employees (EmpId int, EmpName
  varchar(50)) ON Part_Sche (EmpId)
• Un index utilisant une fonction et une clef de
  partitionnement similaire à la table est dit
   aligné  best practice

35
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

36
Comment ajouter ou supprimer des partitions?

• Permet de traiter le cas typique dajout et/ou de
  suppression dune partition entière
• Réalisé à laide de 3 opérations simples sur les
  partitions
• Split sépare 1 partition en 2
• Merge réunit 2 partitions en 1
• Switch échange 1 table et une partition

ALTER PARTITION FUNCTION annual_range() SPLIT
RANGE (2006-01-01)
ALTER TABLE BSWITCH TO A PARTITION 2
37
Administration

• La haute disponibilité
• Miroir de base de données
• Clustering
• Log Shipping
• Réplication

Nouveautés 2005
38
La problématique de la disponibilité
Arrêt du Système

• Cluster de basculement
• Base de Données Miroir

Corruption des Données

• Transfert de journaux
• Réplication
• Base de Données Miroir

Interruptions Non-Planifiées

• Svg/Restauration rapide
• Restauration jusquà un instant donné
• Cliché Base de données

Erreurs Humaines

• Opérations de maintenance des index en
  ligne
• Tables partitionnées

Réorganisation des données
Opérations de Maintenance

• Cluster de basculement
• Base de Données Miroir

Changements liés au système
39
Redémarrage rapide( 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.
• 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.
• Temps de redémarrage (et de basculement) réduit

Défaire (undo)
Refaire (redo)
Base Accessible
temps
40
Solutions pour la Haute Disponibilité
Améliorations 2005
Améliorations 2005
41
Cluster de basculement ( Failover Cluster )

• Un espace disque partagé par plusieurs serveurs,
  mais un seul serveur y accède à un instant donné
• Détection derreurs 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
  lordre de la minute
• Jusquà 50 instances
• Jusquà 8 nuds (Windows Server 2003 x86) ou 4
  nuds en (Win2003 - IA-64 ou x64)
• Tous les services sont compatiblescluster SQL
  Server Agent, Analysis Services, Full-Text
  Search
• Une seule installation globale

Adresse Serveur Virtuel
2005
Stockage partagé

• 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 daugmenter la puissance

42
Cluster distant (ou  Geo Cluster )

• Solution mixte Haute Disponibilité Protection
  contre les désastres
• Bénéfices
• Pas de pertes de transactions
• Pas dimpact sur les performances
• Une extension naturelle du cluster
• Contraintes
• Complexité (système SGBD réplication
  disques)
• Distance limitée (qq centaines de km)

Adresse Serveur Virtuel

• Utilisation de cette architecture
• Tous les environnements où une disponibilité
  maximale est requise sur des données critiques

43
Base de Données Miroir ( Database Mirroring )
1/2
Témoin
Miroir
Principal
Le miroir est toujours en train de rejouer les
journaux
Données
Journaux
Données
Journaux
44
Base de Données Miroir ( Database Mirroring
) 2/2

• 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

Témoin

• Utilisation de cette architecture
• Tous les environnements où une disponibilité
  maximale est requise sur des données critiques

45
Administration

• Applications distribuées
• Service Broker
• Réplication
• Web Services

Nouveautés 2005
46
SQL Service Broker
SQL Engine
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 lordre des messages, même dans des tx
  séparées
• Messages volumineux jusquà 2 GB fragmentés
• Pas de dépendance sur MSMQ

47
Fragmentation des messages

• SQL Service Broker est conçu pour des messages de
  très grande taille
• SSB assure la création de fragments et les
  réassemble lorsquils arrivent au service
  destinataire
• Le service destinataire reçoit ces gros messages
  comme sil était dun seul tenant
• Cette caractéristique permet de faire passer
  dautres messages sans les bloquer
• La taille maximale est varbinary(MAX) 2 GB

48
Architecture Service Broker
Niveau applicatif
Application Cible
Application Initiatrice
Niveau logique
Dialog Conversation
Service
Service
Niveau physique
Transport
Transport
49
Cinématique Service Broker
Niveau applicatif
Application Cible
Application Initiatrice
On lit
Niveau logique
On écrit
Transmission asynchrone
Dialog Conversation
Service
Service
Niveau physique
Transport
Transport
50
Architecture Service Broker
Niveau applicatif
Application Cible
Application Initiatrice
Message Type
Message Type
Niveau logique
Contract
Contract
Dialog Conversation
Service
Service
Niveau physique
Transport
Transport
Endpoint
Endpoint
51
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) 
• 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)

2005
52
Solutions de réplication
53
Réplication Transactionnelle Replication
Peer-to-Peer
Ouest
Nord
Logreader Agent
Logreader Agent
Dist DB
Dist DB
Sud
Distribution Agent
Distribution Agent
Logreader Agent
2005
Dist DB
Distribution Agent
54
Réplication depuis OracleRéplication
Transactionnelle

• Une base Oracle en tant quémetteur
• v8, 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
• Linverse est également possible (déjà disponible
  en 2000)

SQL Server Distributeur
Abonnés
55
Gestion des Web Services

• Gestion des Web Services
• Configuration
• Sécurité
• WSDL
• Accès depuis une application tierce

HTTP Endpoint
56
Nouveautés SQL Server 2005pour les
développeursayant un impact sur ladministration
57
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 lutilisateur (en
  langage .Net)
• Nouveaux ordres T-SQL
• Déclencheurs sur DDL
• Notification Services (requêtes asynchrones)
• Interopérabilité
• Snapshot Isolation

Nouveautés 2005
58
Le choix dans laccès aux données
Accès par calcul et par .Net
CLR
T-SQL
XML
Accès semi-structuré
Accès relationnel
59
Intégration .NET
construire
Microsoft Visual Studio .NET Project
déployer
CLR hébergée dans SQL Server
MicrosoftSQL Server
60
SQL Server et .NET Framework 2.0

• Environnement de programmation pour
• Fonctions
• Procédures stockées
• Triggers
• Types définis par lutilisateur
• 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
Windows
61
Sécurité CLR Permission Sets

• SAFE
• Aucun accès aux ressources externes à SQL Server
• Aucun appel à du code non managé (Win32, COM)
• Doit-être  vérifiable 
• EXTERNAL_ACCESS
• SAFE accès à certaines ressources externes
  (Fichier, Network)
• Accès aux ressources externes uniquement via des
  classes .NET
• SQL Server 2005 change le contexte dexécution du
  code
• Doit-être vérifiable
• UNSAFE
• Peut appeler du code non managé, peut-être
   un-verifiable 
• LE CODE PEUT TOUT FAIRE DECONSEILLE !!!!

62
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)

63
Snapshot Isolation
CREATE TABLE t1 (c1 int unique, c2 int)INSERT
INTO t1 VALUES (1, 5)
Transaction 1 BEGIN TRANUPDATE t1 SET c2
9 WHERE c1 1 COMMIT TRAN
Transaction 3 (RCSI) BEGIN TRAN SELECT c2
FROM t1 WHERE c1 1 -- SQL Server
returns 5 SELECT c2 FROM t1 WHERE c1 1
-- SQL Server returns 9 COMMIT TRAN SELECT
c2 FROM t1 WHERE c1 1 -- SQL Server
returns 9
Transaction 2 (Snapshot Isolation) SET
TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN
TRAN SELECT c2 FROM t1 WHERE c1 1 -- SQL
Server returns 5 SELECT c2 FROM t1 WHERE c1
1 -- SQL Server returns 5 COMMIT
TRAN SELECT c2 FROM t1 WHERE c1 1 -- SQL
Server returns 9
Time
64
Notifications Services Evènements, abonnements
et notifications
Les évènements sont des enregistrementsdans une
table.
billg_at_msn.com
Les abonnements sont aussi des
enregistrementsdans une table.
Effectuer une jointurepermet de produire des
résultats (notifications)- à intervalles
réguliers- à des moments précis- de complexité
arbitraire Sur 3 canaux HTTP, SMTP et fichier
65
Fonctionnement dune application de SQL NS
Abonnés
Souscriptions(millions)
Sources dInfor-mation
Abonnés ordinateurs, navigateurs, téléphones,
messagers
Générateur
66
En pratique fichiers XML et code à écrire

• Les fichiers XML suivants définissent ce quest
  une application et une instance SQL NS

XML EventFiles
XSLTContentFormatter
XSD
XML EventFiles
XSLTContentFormatter
XSD
Application Definition File
Application Definition File
Instance Configuration File
// Test de SQL NS création de linstance, des
abonnés et des abonnements using
Microsoft.SqlServer.NotificationServices //
Création de lobjet NSInstance string
instanceName Localisation NSInstance
myInstance new NSInstance(instanceName) //
Création de lobjet Subscriber Subscriber
mySubscriber new Subscriber(myInstance) //
Ajout dun abonné mySubscriber.SubscriberId
sqlnsuser_at_sqldays.com" mySubscriber.Add()
Code .Net à écrire
// Managed Code Content Providers using
System using System.Configuration
67
Business Intelligence
68
Business Intelligence Integration Services

• ETL dentreprise
• 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

69
Ressources SQL Server

• Le site français sur SQL Server 2005 (livres
  blancs, webcasts en français)
• http//www.microsoft.com/france/sql/sql2005
• 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/