Exploitation SQL Server : Retour d'expérience

22

Exploitation SQL Server : Retour d'expérience

08/02/2010

Serge DURU – Consultant Senior – WAISSONadia BEN EL KADI – Technology Specialist - MICROSOFTBruno SAILLE – Consultant Avant-vente - MICROSOFT

33

Objectifs

Retour d’expérience sur l’administration quotidiennePrésentation des outils et des nouveautés SQL ServerRappel des bonnes pratiquesLe monitoring avec System Center

44

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éesDélégation de personnel à temps partiel ou à temps plein

Le soutien aux développementsConception et/ou validation de modèlesAide à l’écriture et validation du code applicatif T-SQL

Les audits et l’expertiseOptimisation et Tuning, Opérations ponctuelles « pompiers »Consolidation, rationnalisationCapacity Planning, mise en œuvre de PRA, haute disponibilité

[email protected] www.waisso.com [email protected]

55

Retours du terrain

Lors de nos missions, nous constatons :Plans de maintenance incompletsMéconnaissance des outils et techniques mis à disposition par

SQL ServerPerformances insuffisantesSécurité et fiabilité non garanties

66

AgendaPrésentation de WAISSOOpérations quotidiennes

CompressionGestion par règlesRessource GovernorOpérations en ligne

Outils de diagnosticVues dynamiquesDatabase Tuning EnginePerformance Studio (dashboards)Multi-Server Management

Bonnes pratiquesRappelsPlan de maintenanceSQLRAPPBM

Monitoring avec SCOMPrésentationAdministration de SQL Server avec SCOMExemples de rapports

77






88

Opérations quotidiennes

Compression des backupsAvantages

Occupation disqueGain de tempsDiminution du temps de reprise d’activité

FonctionnementOption du backup classiqueIntégré nativement à SQL Server

Exemple de gainSur des bases de productions « réelles »

75 % de volume en backup40 % de temps en backup27 % de temps en restore

99

DateId CarrierTracking

OfferID

PriceDisc

1 8 10 0.00 1 9 10 0.00 2 10 0.00 2 3 10 0.00 2 4 10 0.00 2 5 10 100.00 3 4E0A-4F89-AE 10 0.00

4911-403C-92 6431-4D57-8320070601

1111111

223333

4

Compression des donnéesTransparent pour les applicationsLongueur variable généraliséeStockage identique sur disque et en mémoirePas d’explosion mémoireGain important I/O disqueStockage : gain de 1,5 à 7


OfferID

PriceDisc

20070601 4911-403C-98 10 0.0020070601 4911-403C-99 10 0.0020070602 6431 10 0.0020070602 6431-4D57-83 10 0.0020070602 6431-4D57-84 10 0.0020070602 6431-4D57-85 10 100.0020070603 4E0A-4F89-AE 10 0.00


1010

10


OfferID

PriceDisc

8 9 3 4 5 100.00 3 4E0A-4F89-AE

4911-403C-92 6431-4D57-8320070601

1111111

223333

4

22 10311 0.004

112222

3333333

44444

4

CREATE TABLE myTable (..) WITH DATA_COMPRESSION =

{NONE|ROW|PAGE}sp_estimate_data_compression_sa

vings


OfferID

PriceDisc

20070601 4911-403C-98 10 0.0020070601 4911-403C-99 10 0.0020070602 6431 10 0.0020070602 6431-4D57-83 10 0.0020070602 6431-4D57-84 10 0.0020070602 6431-4D57-85 10 100.0020070603 4E0A-4F89-AE 10 0.00

Transparent pour les applicationsLongueur variable généraliséeCompression des données

Stockage identique sur disque et en mémoirePas d’explosion mémoireGain important I/O disqueDe x1.5 à x7 attendu


1111

DémoCompression des données

1212


Compression des donnéesRetour sur investissement ?

Gain de stockage sur disque, de 20 à 80 % sur certaines tablesGain 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

1313


Gestion par les règlesFacilite 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…

1414

DémoGestion par les règles

1515


Gestion par règlesRetour 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

1616


Ressource GovernorPermet 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

1717

SQL Server

Admin Workload

Backup

Admin Tasks

Report WorkloadOLTP Workload

OLTP Activity

Executive

ReportsAd-hoc Reports

High

Min Memory 10%Max Memory 20%

Max CPU 20%

Admin Pool

Max CPU 90%

Application Pool

Workload groupResource Pool

Limitation des ressourcesGarantie de ressourceGestion dynamique

Avec SQL Server 2008, contrôle min/max du CPU, de la mémoire, DOP.


1818

DémoRessource Governor

1919


Ressource GovernorRetour sur investissement

Utile pour consolider plusieurs instances sur un seul serveurPermet d’écrêter la courbe CPUUtile pour partager les ressources entre plusieurs applicationsGarantit un niveau de service aux utilisateurs

Limites Allonge la durée d’exécution des traitementsRisque de poses de verrous pendant plus de temps

2020


Opérations en ligneModification d’indexReconstruction d’index

Retour sur investissement :Disponibilité des données et des applicationsPossibilité d’effectuer ces opérations plus souvent, sans

perte de qualité de service de production

2121

DémoOn Line Index Rebuild

2222


PowershellExtensions des possibilités de scriptingOrienté objet : s’appuie sur les objets .NET SMOFacilite l’administration multi-serveurs

2323

PowerShell (exemple de code)

#get instance information$svrs = Get-Content 'serversProd.txt'#liste des serveursForEach ($svr in $svrs) { #Test ping

$resultping = Get-WMIObject -query "select StatusCode from Win32_PingStatus where Address = '$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 $svrtrap { write-host ("ERROR: " + $_) -Foregroundcolor Red; 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

2424






2525

Outils de diagnostic

Vues de gestion dynamiquesInformations de configuration Système et InstanceInformations sur l’état et les performances de l’instanceProcessus, index…

2626

DémoQuelques exemples de Vues de gestion dynamiques

2727


Database Engine Tuning AdvisorEvalue et analyse la mise en place d’indexOptions de prises en compte lors de l’analyse.

Index ,Partitions, Structures existantesAnalyse des préconisations

LimitesRisque de surcharge du serveur analyséRisque de sur-indexation

Retour sur investissementImplication des développeursGain de temps pour les DBA

2828

Présentation Database Engine Tuning Advisor

2929

Database Engine Tuning Advisor

3030


3131


3232


Performance StudioPerformance Dashboards

Vue des performances de l’instance localePossibilité de « drillthrough » pour identifier la requête qui pose problèmeHistorisation des mesures et des événements

Gestion Multi-serveursPermet de donner l’état de santé de plusieurs instances en une seule vueMonitoring sur l’espace disque et l’utilisation CPUPossibilité de gérer les seuils d’alerte, et la durée de rétention des informations

3333

DémoPerformance StudioMulti-Server Management

3434


Multi-Server ManagementPermet de donner l’état de santé de plusieurs instances en

une seule vueMonitoring sur l’espace disque et l’utilisation CPUPossibilité de gérer les seuils d’alerte, et la durée de

rétention des informations

3535




Bonnes pratiquesRappelsPlan de maintenanceBPA/PBMSQLRAP


3636

Checks avant mise en Prod

Coté OS : Options de performanceProcessor Scheduling Memory

Si 32 bit /3GB si nécessaire/PAE (ne pas combiner avec /3gb) si besoin

Coté Carte réseauFile & 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

http://support.microsoft.com/kb/309422/en-us

http://support.microsoft.com/kb/309422/en-us

3737

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émoirePositionner « Max server memory » de telle façon à laisser assez

de mémoire à OS et aux autres processusEn 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 degree parallelism » 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

3838

Coté Bases de données

Pour les bases de productionModèle de Récupération : fonction du DRPAjuster le « compatibility Level » : surtout si upgrade

(80/90/100)Auto Shrink à OFFAuto Close à OFFBien choisir les tailles initiales Autogrow : préférez en Mb plutôt qu’en %Page Verify option: à positionner sur Checksum.Séparer les fichiers de data (MDF/NDF) des fichiers de log

(LDF)

3939

Recommandations : TEMPDB

Rappel du fonctionnement de TEMPDBCré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.

http://msdn.microsoft.com/en-us/library/ms175527.aspx

4040

BPA et PBM

BPA : Best Practice Analyzer (SQL 2005 et antérieur)Outil gratuit en downloadOutil de collecte et d’analyse afin de vérifier le bon usage

des best practicesPBM : Policy Based Management (SQL 2008) – Best Practice

PoliciesImporter les Policy dans SSMS puis lancer la vérificationVous pouvez créer vos propres règles

4141

Plan de maintenance : DO IT !

Pourquoi ? Indispensable pour vos DBs et les DBs systèmesLe scripter ou via SSMS maintenance plan toolIl devrait comporter au minimum

Vérification de l’intégrité Sauvegarde des Bases de données Sauvegarde des journaux de transactionsSuppression 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 besoinPurge 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

4242

Plan de maintenance…suite

Ce qu’il ne faut pas faire Ne pas inclure une opération de Shrink a 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éeInutile de faire un « Update stat » après un « Rebuild » : le

Rebuild inclus déjà cette opération.

4343

Votre boite à outils Coté configuration

MBSA : MS Baseline SecurityBPA pour SQL 2005 et antérieurPBP – PoliciesSQLDIAG

Coté Troubleshooting & PerformanceLES 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 ProfilerSQL DTA

SQLDIAG : PerfMon/EventLogs/Profiler/BlockingInfo/SQLConfigPSSDIAG RML utilities

Readtrace , Reporter, et outil de stress

Site CodeplexPALSQLNEXUS

Pensez à vous créer des « baselines » afin de les exploiter en cas de problèmes

4444

SQLRAP

Audit SQL Server qui couvre OS, SQL et le cluster pour les instances en cluster Vérifie à la configuration, les best practices, les performancesDélivre un rapport avec les points à risquesMise en place d’un plan de « remediation » et de suivi de

l’application des recommandationsDoit passer par le supportFormation sur le fonctionnement des outils utilisés

4545






4646

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érielOS et applications MicrosoftManagement 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é)

4848

Management pack SQL ServerAvailability Monitoring

Component availabilityDB availabilityReplication monitoringSQL events (~400)Agent jobsBackupReporting Services

Performance MonitoringUser ConnectionsProcess utilization per instanceDB and log size (% and absolute)DB and log growthClient response time (template)

Configuration MonitoringBest practice monitoringEnable customer to

define standard

ViewsDashboard viewsDiagram for replicationDiagram for disk layoutPivot in context e.g. DB performance

TemplatesOLEDB

Version SupportSQL 2000/ 2005/200864 Bit support, Cluster supportAgentless support

Database Configuration setting Default value

Auto close alert if enabled

Auto create statistics alert if disabled

Auto shrink alert if enabled

Auto updates statistics alert if disabled

Cross-database ownership chaining alert if enabled

Torn page detection alert if disabled

4949

DémoVues du management packIntégration dans des vues de servicesExemple d’alerte et base de connaissance

5050

Exemple de basede connaissance

5151

Exemples de rapports

5252

5353

Administration de SQL Serveravec System Center

Déploiement, mises à jour et gestion de la configuration avec System Center Configuration Manager (SCCM)

Analyse des bonnes pratiques de configuration pour SQL Server et ses dépendances:“Bonnes pratiques” de Microsoft IT en 3 versions basique/intermédiaire/exhaustifVulnerability AssessmentAnalyse pour les législations SOX, FISMA, EUPD, GLBA, HIPAA

Sauvegarde et restauration avec System Center Data Protection Manager (SCDPM)

Protection native en mode continu des technologies Microsoft, dont SQL Server et des configurations avancées comme le mirroring

Economie d’espace disque, de temps et d’arrêts de service : restauration applicative intégrant les backups “Express Full” et les logs de transactions des applications MicrosoftTechnologie applicable aux entreprises de toutes tailles (utilisation infrastructure disques existantes, intégration avec solution existante de backup sur bandes…)

5555

Synthèse

Connaître les bonnes pratiquesOpérations de maintenance (sauvegardes, reconstruction

d’index…)Configuration matérielle et systèmeMonitoring de l’activité, du stockage, des performances

Actions : Utiliser les outils SQL Server 2008 R2 Automatiser l’exploitation (règles, plans de maintenance…)Automatiser l’administration, améliorer la gestion des

ressources (ressource governor, DTA…)Diagnostiquer, interpréter les mesuresMettre en œuvre un pilotage des compteurs

Documents

Exploitation SQL Server : Retour d'expérience