Exploitation SQL Server : Retour d'expérience

Exploitation SQL Server : Retour d'expérience 08/02/2010 Serge DURU – Consultant Senior – WAISSO Nadia BEN EL KADI – TechnologySpecialist - MICROSOFTBruno SAILLE – Consultant Avant-vente - MICROSOFT

Objectifs • Retour d’expérience sur l’administration quotidienne • Présentation des outils et des nouveautés SQL Server • Rappel des bonnes pratiques • Le monitoring avec System Center

Présentation de WAISSO • La conduite et la réalisation de projets CRM (MS CRM et Neolane) • La conduite et la réalisation de projet avec la suite Microsoft BI • L’administration et l’expertise autour de SQL Server : • L’administration courante des bases de données • Délégation de personnel à temps partiel ou à temps plein • Le soutien aux développements • Conception et/ou validation de modèles • Aide à l’écriture et validation du code applicatif T-SQL • Les audits et l’expertise • Optimisation et Tuning, Opérations ponctuelles « pompiers » • Consolidation, rationnalisation • Capacity Planning, mise en œuvre de PRA, haute disponibilité info@waisso.com www.waisso.com recrut@waisso.com

Retours du terrain • Lors de nos missions, nous constatons : • Plans de maintenance incomplets • Méconnaissance des outils et techniques mis à disposition par SQL Server • Performances insuffisantes • Sécurité et fiabilité non garanties

Agenda • Présentation de WAISSO • Opérations quotidiennes • Compression • Gestion par règles • Ressource Governor • Opérations en ligne • Outils de diagnostic • Vues dynamiques • DatabaseTuningEngine • Performance Studio (dashboards) • Multi-Server Management • Bonnes pratiques • Rappels • Plan de maintenance • SQLRAP • PBM • Monitoring avec SCOM • Présentation • Administration de SQL Server avec SCOM • Exemples de rapports

Opérations quotidiennes • Compression des backups • Avantages • Occupation disque • Gain de temps • Diminution du temps de reprise d’activité • Fonctionnement • Option du backup classique • Intégré nativement à SQL Server • Exemple de gain Sur des bases de productions « réelles » • 75 % de volume en backup • 40 % de temps en backup • 27 % de temps en restore

Opérations quotidiennes • Compression des données • Transparent pour les applications • Longueur variable généralisée • Stockage identique sur disque et en mémoire • Pas d’explosion mémoire • Gain important I/O disque • Stockage : gain de 1,5 à 7 2007060 4911-403C-9 6431-4D57-8 1 2 3 2 1 1 2 1 3 4 1 3 1 3 1 3 1

Opérations quotidiennes • Transparent pour les applications • Longueur variable généralisée • Compression des données • Stockage identique sur disque et en mémoire • Pas d’explosion mémoire • Gain important I/O disque • De x1.5 à x7 attendu 2007060 4911-403C-9 6431-4D57-8 1 2 3 1 2 10 0.00 1 2 3 4 4 2 3 1 1 1 1 2 3 4 • CREATE TABLE myTable (..) • WITH DATA_COMPRESSION = {NONE|ROW|PAGE} • sp_estimate_data_compression_savings 2 1 3 4 3 4 2 1 3 4 3 4 3 1 2 3 3 2 1 3 1 3 4

Démo Compression des données

Opérations quotidiennes • Compression des données • Retour sur investissement ? • Gain de stockage sur disque, de 20 à 80 % sur certaines tables • Gain en performances, grâce à la réduction des I/O disques et de la pression sur la mémoire • Limites ? • Efficace sur les tables ayant beaucoup de données redondantes

Opérations quotidiennes • Gestion par les règles • Facilite l’administration des serveurs SQL • Permet de déployer les règles sur différents serveurs (groupes de serveurs) • Règles concernant l’installation, la création d’objets, l’activation de services…

Démo Gestion par les règles

Opérations quotidiennes • Gestion par règles • Retour sur investissement Exemple de gain réalisé : • Diminution de 20 % du temps d’administration (moins d’incidents, délégation de certaines tâches…) • Baisse de 30 % du temps de mise en production (moins de validation « humaine », respect des normes…) • Mise en place en 10 hj

Opérations quotidiennes • Ressource Governor • Permet de gérer les priorités entre différents groupes d’applications (saisie des commandes VS reporting…) • Limite les ressources disponibles pour chaque type de charge de travail

Opérations quotidiennes SQL Server • Workload group • Resource Pool • Limitation des ressources • Garantie de ressource • Gestiondynamique • Avec SQL Server 2008, contrôle min/max du CPU, de la mémoire, DOP. Backup OLTP Activity Executive Reports Admin Tasks Ad-hoc Reports High Admin Workload OLTP Workload Report Workload Min Memory 10% Max Memory 20% Max CPU 20% Max CPU 90% Admin Pool Application Pool

Démo Ressource Governor

Opérations quotidiennes • Ressource Governor • Retour sur investissement • Utile pour consolider plusieurs instances sur un seul serveur • Permet d’écrêter la courbe CPU • Utile pour partager les ressources entre plusieurs applications • Garantit un niveau de service aux utilisateurs • Limites • Allonge la durée d’exécution des traitements • Risque de poses de verrous pendant plus de temps

Opérations quotidiennes • Opérations en ligne • Modification d’index • Reconstruction d’index • Retour sur investissement : • Disponibilité des données et des applications • Possibilité d’effectuer ces opérations plus souvent, sans perte de qualité de service de production

Démo On Line Index Rebuild

Opérations quotidiennes • Powershell • Extensions des possibilités de scripting • Orienté objet : s’appuie sur les objets .NET SMO • Facilite l’administration multi-serveurs

PowerShell (exemple de code) #get instance information $svrs = Get-Content 'serversProd.txt' #liste des serveurs ForEach ($svr in $svrs) { #Test ping $resultping = Get-WMIObject -query "select StatusCodefrom Win32_PingStatus whereAddress = '$svr'" if ($resultping.statuscode -eq 0) { #********récupère les instances du serveur courant par la liste des services sqlserver running $Instances='' $mc = get-SMO_ManagedComputer $svr trap { write-host ("ERROR: " + $_) -ForegroundcolorRed; Continue} $Instances=$mc.Services | where-object{$_.Type -eq 'SqlServer' -and $_.ServiceState -eq 'Running'} foreach ($instance in $instances) { #**********requête chaque instance pour récupérer la date des backup

Outils de diagnostic • Vues de gestion dynamiques • Informations de configuration Système et Instance • Informations sur l’état et les performances de l’instance • Processus, index…

Démo Quelques exemples de Vues de gestion dynamiques

Outils de diagnostic • DatabaseEngineTuningAdvisor • Evalue et analyse la mise en place d’index • Options de prises en compte lors de l’analyse. • Index ,Partitions, Structures existantes • Analyse des préconisations • Limites • Risque de surcharge du serveur analysé • Risque de sur-indexation • Retour sur investissement • Implication des développeurs • Gain de temps pour les DBA

Présentation DatabaseEngineTuningAdvisor

DatabaseEngineTuningAdvisor

Outils de diagnostic • Performance Studio • Performance Dashboards • Vue des performances de l’instance locale • Possibilité de « drillthrough » pour identifier la requête qui pose problème • Historisation des mesures et des événements • Gestion Multi-serveurs • Permet de donner l’état de santé de plusieurs instances en une seule vue • Monitoring sur l’espace disque et l’utilisation CPU • Possibilité de gérer les seuils d’alerte, et la durée de rétention des informations

Démo Performance Studio Multi-Server Management

Outils de diagnostic • Multi-Server Management • Permet de donner l’état de santé de plusieurs instances en une seule vue • Monitoring sur l’espace disque et l’utilisation CPU • Possibilité de gérer les seuils d’alerte, et la durée de rétention des informations

Agenda • Présentation de WAISSO • Opérations quotidiennes • Compression • Gestion par règles • Ressource Governor • Opérations en ligne • Outils de diagnostic • Vues dynamiques • DatabaseTuningEngine • Performance Studio (dashboards) • Multi-Server Management • Bonnes pratiques • Rappels • Plan de maintenance • BPA/PBM • SQLRAP • Monitoring avec SCOM • Présentation • Administration de SQL Server avec SCOM • Exemples de rapports

Checks avant mise en Prod • Coté OS : Options de performance • Processor Scheduling • Memory • Si 32 bit • /3GB si nécessaire • /PAE (ne pas combiner avec /3gb) si besoin • Coté Carte réseau • File & Printer sharing si activé le positionner à « Maximize for client/server App » • Coté Antivirus • Best Practice SQL & Antivirus : http://support.microsoft.com/kb/309422/en-us

Coté Serveur SQL • Versions à ce jour (@@version) • SQL 2005 SP3 (le sp2 n’tant plus supporté depuis le 12 janvier 2010) • SQL 2008 SP1 (fin du sport de la RTM(sp0) au 12 avril 2010) • Coté mémoire • Positionner « Max server memory » de telle façon à laisser assez de mémoire à OS et aux autres processus • En multi-instance sur un cluster : bien régler les « max serveur memory » (leur somme ne doit pas dépasser Ram-mémoire pour OS) • Si 32 bit • Awe + Lock page in memory • « Max degreeparallelism » ne jamais dépasser 8 • Nombre Errorlogsà configuré si besoin (defaut = 6) • Historique des tâches à configuré en fonction du nombre de travaux planifiés

Coté Bases de données Pour les bases de production • Modèle de Récupération : fonction du DRP • Ajuster le « compatibility Level » : surtout si upgrade (80/90/100) • Auto Shrink à OFF • Auto Close à OFF • Bien choisir les tailles initiales • Autogrow : préférez en Mb plutôt qu’en % • Page Verifyoption: à positionner sur Checksum. • Séparer les fichiers de data (MDF/NDF) des fichiers de log (LDF)

Rappel du fonctionnement de TEMPDB Créer autant de fichiers que de #CPU physique (sans dépasser 8 fichiers) Voir KBA : http://msdn.microsoft.com/en-us/library/ms175527.aspx Configurez la taille initiale des fichiers de la TEMPDB a une taille réaliste Paramétrez le pourcentage d'incrémentation de la croissance des fichiers à une valeur suffisante. Placez la base de données sur un sous-système d'E/S rapide pour obtenir de bonnes performances. Recommandations : TEMPDB

BPA et PBM • BPA : Best Practice Analyzer (SQL 2005 et antérieur) • Outil gratuit en download • Outil de collecte et d’analyse afin de vérifier le bon usage des best practices • PBM : Policy Based Management (SQL 2008) – Best Practice Policies • Importer les Policy dans SSMS puis lancer la vérification • Vous pouvez créer vos propres règles

Plan de maintenance : DO IT ! • Pourquoi ? Indispensable pour vos DBs et les DBs systèmes • Le scripter ou via SSMS maintenance plan tool • Il devrait comporter au minimum • Vérification de l’intégrité • Sauvegarde des Bases de données • Sauvegarde des journaux de transactions • Suppression de la fragmentation : pour cela utiliser la commande « ALTER INDEX » • Rebuild ou Reorganize ? • Recherche de la fragmentation : « sys.dm_db_index_physical_stats » • Mise à jour des statistiques si besoin • Purge de l’historique des plans stockés dans « msdb » Ces taches sont indépendante et peuvent être planifier à différents moment en fonction de vos fenêtres de traitements

Plan de maintenance…suite • Ce qu’il ne faut pas faire • Ne pas inclure une opération de Shrinka chaque opération de maintenance. Le shrink doit rester une opération ponctuelle à un instant T pour diminuer une enveloppe de fichier de DB que vous auriez sur-taillée • Inutile de faire un « Update stat » après un « Rebuild » : le Rebuild inclus déjà cette opération.

Votre boite à outils  Coté configuration • MBSA : MS Baseline Security • BPA pour SQL 2005 et antérieur • PBP – Policies • SQLDIAG Coté Troubleshooting & Performance • LES DMV depuis SQL Serveur 2005 : via T-sql (sys.dm_ xxx) ou Rapports prédéfinis (Dashbord) • Fragmentation, indexes manquants, requêtes consommatrice en Cpu, en io, les blocages • SQL Profiler • SQL DTA • SQLDIAG : PerfMon/EventLogs/Profiler/BlockingInfo/SQLConfig • PSSDIAG • RML utilities • Readtrace , Reporter, et outil de stress • Site Codeplex • PAL • SQLNEXUS Pensez à vous créer des « baselines » afin de les exploiter en cas de problèmes

SQLRAP • Audit SQL Server qui couvre OS, SQL et le cluster pour les instances en cluster • Vérifie à la configuration, les best practices, les performances • Délivre un rapport avec les points à risques • Mise en place d’un plan de « remediation » et de suivi de l’application des recommandations • Doit passer par le support • Formation sur le fonctionnement des outils utilisés

System Center Operations Manager (SCOM) 2007 R2 • Solution spécialisée sur les plateformes Windows, avec fourniture de management packs (règles et reporting) • Matériel • OS et applications Microsoft • Management packs tierce-partie • Supervision des services de bout en bout • Windows / Unix / Linux, ou intégrable dans les outils de supervision existants • Physique / Virtuel • De l’expérience client à l’infrastructure • Reporting associé, avec possibilité d’abonnement par mail et publication SharePoint (performance, disponibilité, SLA) • Rapidité et souplesse de déploiement (délégations, scalabilité)

Management pack SQL Server Availability Monitoring Component availability DB availability Replication monitoring SQL events (~400) Agent jobs Backup Reporting Services Performance Monitoring User Connections Process utilization per instance DB and log size (% and absolute) DB and log growth Client response time (template) Configuration Monitoring Best practice monitoring Enable customer to define standard Views Dashboard views Diagram for replication Diagram for disk layout Pivot in context e.g. DB performance Templates OLEDB Version Support SQL 2000/ 2005/2008 64 Bit support, Cluster support Agentless support

Démo Vues du management pack Intégration dans des vues de services Exemple d’alerte et base de connaissance

Exemple de basede connaissance

Exemples de rapports

Exploitation SQL Server : Retour d'expérience