SQL Best Practices Analyser

1

SQL Best Practices Analyser

Thierry DEMANThierry DEMANConsultant [MVP Exchange,SQL/Server]Consultant [MVP Exchange,SQL/Server]Sté “BY THE WAY”Sté “BY THE WAY”

2

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

Présentation du produit

• Il s’agit de la version 1.0 Beta. Cette version date du 24 Mai 2004 et n’a pas eu de mise à jour depuis.

• Son nom plus complet : “Best Practices Analyzer Tool for Microsoft SQL Server 2000”

• Il s’agit donc bien d’une première version car de nombreuses améliorations sont encore possibles, surtout quand on compare avec l’équivalent pour Exchange (EXBPA)!

• En fait, il y a encore très peu d’informations sur ce produit.

3

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

Les besoins

- Cette version est fortement basée sur SQL 2000 (ou MSDE) qui est indispensable à son installation.( En effet, un produit dédié à SQL se doit d’utiliser SQL, n’est ce pas ?)A l’usage, seules les bases SQL 2000 sont analysées.

- Pour l’installation de cet outil, l’outil vérifie que la version « .Net Framework 1.1 » soit installée.Si nécessaire, l’outil indique la page web permettant de charger cette version.

- IE6 est nécessaire.

- L’installation peut se faire sur les systèmes suivant :Microsoft Windows XP ,2000 Pro, 2000 Server, Server 2003.

4

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

Le type de tests réalisés

Actuellement 134 tests sont réalisés dans les domaines suivants!

Sauvegardes et récupérationsOptions de configurationDesign des bases de donnéesL’administration des basesDeprecation (usages non approuvés ou tombés en désuétudes)La recherche en texte intégralL’administration généraleLes fonctions de bases/génériquesT-SQLYukon Readiness (compatibilité à SQL 2005)

5

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

Les règles

• Certaines catégories contiennent des règles configurables.

Par exemple, on peut définir le nombre de jours acceptés pour la dernière sauvegarde ( 30 jours par défaut).

• Quelques règles intéressantes:

La fragmentation autorisée des indexLa taille minimale en pages d’un index pour être inclus dans l’analyseL’emplacement des fichiers de données et de log (disques différents)Les arrêts inattendus dans les évènements du système.Les suffixes et préfixes d’objets permettent de retrouver tous les objets qui ne suivraient pas un standard défini.La gestion des valeurs NULLLes procédures stockées, les vues, les triggers et les fonctions sont analysées, notamment pour les tests concernant la valeur «NULL »L’utilisation des tables temporaires, et l’éventuelle utilité de remplacer par des variables temporaires.

6

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

Création d’un groupe de tests

On peut sélectionner toutes les catégories souhaitées sans problèmes sauf les options “object Prefixes” et “object Suffixes” qui nécessitent de connaître les bases et les standards en vigueurs.

En effet, ces règles permettent de vérifier que les standards d’écritures et de nommages de certains objets soient respectés.

Choisir le type d’objet en utilisant les codes suivants, et le suffixe ou préfixe correspondant.

C = CHECK constraintD = Default or DEFAULT constraintF = FOREIGN KEY constraintFN = Scalar functionIF = Inlined table-functionL = LogP = Stored procedurePK = PRIMARY KEY constraint. R = RuleRF = Replication filter stored procedureS = System tableTF = Table functionTR = TriggerU = User tableUQ = UNIQUE constraintV = ViewX = Extended stored procedure

7

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

En ligne de commande : BPACMD.exe

OPTIONS DE CONNEXION A LA BASE

-S <server name> Nom du serveur SQL contenant la base de “Repository” (SQLBPA par défaut)-d <database name> Nom de la base de “Repository”

-E Authentification intégréeou-U <username> Login SQL-P <password> Mot de passe SQL

OPTIONS des GROUPES DE “Best Practices”

-r <best practice group> Groupe de tests à utiliser

OPTIONS DIVERSES-q Mode sans affichage-l Log de l’outil BPA vers un fichier msbpa.log.-? Affiche la liste de ces options !

Exemple : bpacmd -S MainServer -d sqlbpa -E -r tsql_rules -r upgrade_prep Les tests "tsql_rules" and "upgrade_prep“ sont utilisés à partir de la base SQLBPA du serveur MainServer

8

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

L’installation

• L’installation se réalise à partir du fichier BPASETUP.MSI que l’on peut télécharger (gratuitement) sur le site Microsoft.

• L’installation demande où se trouve le serveur SQL:

9

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

L’utilisation

10

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

11

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

12

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

Définitions des serveurs SQL et des bases à étudier

On peut noter l’utilisation possible de fichiers traces…

13

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

Choisir le groupe de tests à réaliser

Le groupe contient les serveurs et les bases…

14

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

Les rapports

15

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

Détails d’un rapport

16

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

Description d’une règle

La documentation définit la règle utilisée et renvoie sur le site MSDN.

17

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

Conclusion

Ce produit apporte déjà les éléments fondamentaux dans l’utilisation courante et basique des bases des données.

La version suivante de ce produit apportera probablement les mêmes évolutions intéressantes que son homologue Exchange!

2 remarques importantes:

- Seuls les serveurs SQL accessibles en mode « authentifiés » peuvent être scannés automatiquement !

- Certains utilisateurs ont parfois constaté des soucis (de type performance) sur des bases spécifiques. Un essai préalable sur un environnement de test sera donc préférable.

18

Titre2 Titre2 Titre2 Titre2 Titre2 Titre2 Titre2

Pour tester et améliorer le produit!

http://www.microsoft.com/downloads/details.aspx?familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en(ou rechercher « SQLBPA »)

Pour transmettre vos demandes et retours sur le produit :

- Christian Kleinerman (Program Manager in the SQL Server Engine Team)- sqlbpa@microsoft.com