sql server 2008 'best practices' - stéphane haby, dbi services - mövenpick lausanne...

© dbi services

dbi services

SQL Server – Best Practices

1 06.10.2011 www.dbi-services.com

Stéphane Haby

Consultant

Mobile +41 79 103 89 60

[email protected]

www.dbi-services.com

mailto:[email protected]



http://www.dbi-services.com/



© dbi services

Agenda

dbi services

i. Introduction

ii. Planification et dimensionnement

iii. Installation

iv. Configuration

v. Maintenance

vi. Conclusion


06.10.2011 2 www.dbi-services.com

© dbi services

4 axes :

Planification & Dimensionnement

Installation

Configuration

Maintenance

Scope :

Moteur de base de données

Out of Scope :

Autres outils SQL Server (SSAS,SSIS & SSRS)

Système d’exploitation

Documentation en ligne dans 27 langues

http://msdn.microsoft.com/fr-fr/library/ms130214.aspx

Introduction

Scope

www.dbi-services.com 3 06.10.2011






© dbi services

Agenda

dbi services

i. Introduction


iii. Installation

iv. Post installation

v. Maintenance

vi. Conclusion



© dbi services

Quelle utilisation? OLAP? OLTP? Mixte?

Besoin d’un accord de niveau de service (SLA)?

Haute disponibilité (HA) / reprise après sinistre (DR)?

Combien de ressources sont nécessaires?

Virtualisation?

Comment va fonctionner la BdD dans l’environnement existant?

Planification et dimensionnement

Comment commencer ?


© dbi services

Construire un système équilibré, sans goulots d'étranglement

Principes des « Fast Track reference architectures »

Fast Track 3.0 System Sizing Tool

Référence : http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/fast-track.aspx


Composants du système de base


Sous-système de disques

Serveur

NIC

Mémoire

Réseau

1

5 3

4

2

Schéma des fichiers SQL

HBA

© dbi services

Agenda

dbi services

i. Introduction


iii. Installation

iv. Configuration

v. Maintenance

vi. Conclusion



© dbi services

Au démarrage

Sélectionnez uniquement les fonctions nécessaires

Choisissez l’assemblage approprié

Paramétrage des chemins

Utilisez un utilisateur de domaine pour un compte de service

Stratégie de sécurité

Verrouiller les pages en mémoire

Effectuer des tâches de maintenance sur les volumes

Installation

SQL Server Setup


© dbi services

1. Installer et configurer l'OS

2. Installer SQL Server Management Studio et les autres composants services partagés nécessaires (SSIS, BIDS,...)

3. Préparer les instances SQL Server

4. SysPrep de l'OS

5. Déployer l'image

6. Démarrer l'OS, ajouter au domaine, ...

7. Finaliser les instances SQL Server

8. Configurer les instances SQL Server via des scripts T-SQL

Référence : http://msdn.microsoft.com/en-us/library/ee210664.aspx

Installation

Étapes à suivre pour un SQL SysPrep


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



© dbi services

Agenda

dbi services

i. Introduction

ii. Planification et Dimensionnement

iii. Installation

iv. Configuration

v. Maintenance

vi. Conclusion



© dbi services

Rôles

Définir temporairement des caractéristiques de serveur spécifiques

Désactiver un comportement particulier

Internet ou une présentation PowerPoint n’est pas forcément une source fiable

Appliquez les bonnes pratiques conseillées par votre fournisseur d’application

Configuration

Trace flags/Knowledge Base articles


Ne pas se contenter de configurer son système, il faut savoir en quoi un paramètre l’affecte avant de l’appliquer

© dbi services

Indicateur au démarrage lorsque plusieurs fichiers de données sont présents par base de données

Trace tous les fichiers de données

Recommandé pour

SAP (Note 1238993)

FastTrack DWH

SharePoint

Configuration

Indicateur de trace 1117


DBCC TRACEON (1117,-1)

© dbi services

Configuration

Propriétés du serveur


© dbi services

Mémoire

«Verrouillage des pages en mémoire» est accordé

Laisser au moins 2 Go de mémoire à l'OS

Processeurs

Valeurs par défaut

Masque d’affinité CPU peut être utilisé que si d'autres applications sont exécutées sur le système

Sécurité

Utiliser l'authentification Windows uniquement

Compte 'sa' avec un mot de passe qui respecte des exigences de complexité

Configuration

Propriétés serveur – Résumé 1/2


© dbi services

Connections

Valeurs par défaut

Paramètres de bases de données

Taux de remplissage par défaut de l’index à 70%

Compresser la sauvegarde

Avancé

Ne pas activer FILESTREAM si pas nécessaire

MAXDOP à 1 pour de l’OLTP (ERP/CRM/SharePoint/SAP,...)

MAXDOP au moins à 2 pour de l’OLAP

Permissions

Valeurs par défaut

Configuration

Propriétés serveur – Résumé 2/2


© dbi services

Règle d'or:

¼ - ½ fichier de données par cœur CPU

1 fichier de journal des transactions

Exemple :

4 CPU quadri-core (16 cœurs) 16 * ¼ et 16 * ½ = 4 à 8 fichiers TempDB

Débuter avec le minimum de fichiers

sauf si le stress imposé aux fichiers TempDB des applications est connu

+ de 8 fichiers ne sert à rien

sauf si spécifié dans la documentation du logiciel

Configuration

TempDB : nombre de fichiers


© dbi services

Calculer la taille initiale : beaucoup de formules compliquées

La bonne pratique au départ est d’avoir plusieurs fichiers de

2 Go avec un incrément de 1 Go pour les données

1 Go avec un incrément de 512 Mo pour les log

Surveiller régulièrement le TempDB

Configuration

TempDB : Taille des fichiers


© dbi services

Créer un script pour ces paramètres

Utiliser les variables sqlcmd

Commenter tout

Utilisation de « Server Dashboard Report » pour les paramètres non configurés par défaut

Utilisation de « Configuration Change History Report » pour surveiller les changements

Faire des stratégies pour chaque paramètre

Configuration Astuces pour configurer l’instance de base


Aidez vous du « Ressource Governor »

© dbi services

Agenda

dbi services

i. Introduction


iii. Installation

iv. Configuration

v. Maintenance

vi. Conclusion



© dbi services

Définir une stratégie de sauvegarde normalisée pour l’ensemble de l’environnement

Bien choisir le type de sauvegarde

Etapes de la sauvegarde (sur disques, sur bandes ?)

Tâches planifiées

Standardiser le chemin de restauration/récupération

Réduire les fenêtres de sauvegarde avec les flux de sauvegardes multiples et la compression

TESTER la restauration régulièrement

Maintenance Stratégie de sauvegardes & restaurations


© dbi services

Contrôle d’intégrité

DBCC CHECKDB pour les contrôles de cohérence

DBCC CHECKDB avec l’option WITH PHYSICAL_ONLY

Défragmentation d’index

Reconstruction : ALTER INDEX…REBUILD

Taux de fragmentation > 30%

Réorganisation : ALTER INDEX…REORGANIZE

10% > Taux de fragmentation < 30%

Référence : http://msdn.microsoft.com/fr-fr/library/ms189858.aspx

Maintenance Plan de maintenance 1/2


© dbi services

Statistiques

Maintenir automatiquement les statistiques avec AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS

Historiques

Utiliser les sp_delete_xxxx & sp_purge_xxxx

Utilisation des scripts d’Ola Hallengreen

http://ola.hallengreen.com

Pas de support Microsoft, mais facilite la vie de DBA

Maintenance Plan de maintenance 2/2


http://ola.hallengreen.com/

© dbi services

Virtual Log File (VLF)

Nombre de VLF < 100 n’affecte pas les performances

Taille : Toujours < 512 Mo par VLF

Référence : http://dbamohsin.wordpress.com/2011/02/11/448-virtual-log-files-vlfs-on-a-11gb-file-hmmmmmm/

Création de la base de données

Pré-allocation des fichiers de données

RECOVERY=Full & PAGE VERIFY=CHECKSUM

Paramètres de correction automatique (Auto_Close, Auto_Shrink)

Penser à la compression de table

Référence : http://www.dbi-services.com/index.php/blog/entry/sql-server-tuning-detect-sparse-columns-candidate

Maintenance Virtual Log File & Création de la BdD


DBCC Loginfo

http://dbamohsin.wordpress.com/2011/02/11/448-virtual-log-files-vlfs-on-a-11gb-file-hmmmmmm/




















© dbi services

Service Packs

Installation recommandée

Support dépend du dernier service pack

Cycle: tous les 12 mois

Mises à jour cumulatives

A installer que si un problème survient ou si cela concerne la sécurité

Cycle: tous les 2 mois

Info sur : http://blogs.msdn.com/b/sqlreleaseservices/

Maintenance

SPs & CUs


http://blogs.msdn.com/b/sqlreleaseservices/



© dbi services

Mises à jour

Préparer et exécuter le plus tôt possible

Garantir un produit supporté

Bénéficier de nombreuses améliorations, corrections et mises à jour de sécurité

Maintenance Le support d’SQL Server


SQL Version Mainstream Support Extended Support

SQL Server 2000 SP4 08.04.2008 09.04.2013

SQL Server 2005 SP4 12.04.2011 12.04.2016

SQL Server 2008 14.01.2014 08.01.2019

SQL Server 2008 R2 14.01.2014 08.01.2019

© dbi services

Maintenance System Center Advisor


https://www.systemcenteradvisor.com/

Code du projet : Atlanta

Evalue les configurations des serveurs

Détection pro-active des incidents

Suggère des améliorations

Avise les clients sur les mises à jour

https://www.systemcenteradvisor.com/

© dbi services

Agenda

dbi services

i. Introduction


iii. Installation

iv. Configuration

v. Maintenance

vi. Conclusion



© dbi services


Posez vous les bonnes questions

Utilisez les outils disponibles

Installation

Installez que les fonctions nécessaires

Utilisez SysPrep pour la duplication

Configuration

Configurez correctement les propriétés du serveurs

Dimensionnez convenablement le TempDB

Maintenance

Utilisez les plans de maintenance

Surveillez les Virtual Log File

Conclusion dbi services recommande


© dbi services

dbi services

Any Questions? Please Do Ask!

32 06.10.2011 www.dbi-services.com

„We look forward to working with you!“

Stéphane Haby

Consultant

Mobile +41 79 103 89 60

[email protected]

www.dbi-services.com







sql server 2008 'best practices' - stéphane haby, dbi services - mövenpick lausanne...

Technology

donnes sontprsents par

donnes par cur cpu

installer et configurer

autres outils sql server

sql sysprep1

remplissage par dfaut

instances sql server4

instances sql server8