Sql Server - Archiver des données

Voici une petite marche à suivre pour archiver les données d'une DB.
Ce cas de figure s'applique à l'archivage partiel d'une DB ou d'une table.

Etape 1
Faire un dump/unload des données dans des fichiers.
voir cet article à propos de BCP.

Etape 2
Effacer les records archivés.
Attention à la méthode utilisée! Un delete de plusieurs milliers/millions de records peuvent facilement engorger le transaction log (ou le disque dur)
Voir cet article proposant des méthodes alternatives.

Etape 3
Si nécessaire, tronquer le data file avec l'instruction DBCC SHRINKFILE.
DBCC SHRINKFILE ne require pas lock particulier sur la DB, cette commande pourra donc être utilisée sans risque pendant son utilisation.
Pour connaîte le nom du data file, il faut utiliser la commande sp_helpfile .

DBCC SHRINKFILE ('MyDatabase_Data')

Etape 4
Si nécessaire, réduire la taille du transaction Log avec la store procedure sp_force_shrink_log.
Si cette store procedure n'est pas encore installée, voir cet article pour en localiser le code source.

Sql Server - Bulk Insert

Dans un précédent article, je parlais de l'utilitaire BCP (Bulk Copy Program) permettant de décharger et recharger le contenu d'un table (voir cet article).

Cette fois-ci, je parlerais de l'instruction TSQL "BULK INSERT' permettant également de recharger des données dans une table.

BULK INSERT DataSignature FROM 'c:\temp\SignData.bcp' 

WITH (FIELDTERMINATOR = '|', TABLOCK, BATCHSIZE=1000 )

Excel compare

Entre deux versions de notre logiciel ou à la suite de modifications, nous sommes amenés à vérifier que nous n'avons pas corrompu le fonctionnement du logiciel.
Après les tests tradionnels en court de développement, nous comparons également le résultat de nos rapports. En se fiant au contenus identiques de ces derniers (avant et après modifications), nous avons déjà l'assurance de ne pas avoir casser la vaisselle. En effet, une petite erreur peu prendre des proportions énorments lors du rendu d'un rapport (décalage de ligne, colonne, montant, somme cumulative, etc).

Cependant, il n'est pas toujours facile de comparer le contenu de grands rapports.
Pour ce faire, le contenu du rapport est copier/coller dans une feuille Excel que nous comparons ensuite à une version plus ancienne du rapport (lui aussi copier/coller dans une feuille Excel).
Pour nous aider dans le travail de comparaison, nous utilisons deux méthodes:

1. Une formule Excel comparant la même cellule dans deux fichiers différents (et indiquant le resultat de la comparaison)
2. Le logiciel Excel Compare
   

Logiciel Excel Compare
Voici un logiciel à la fois simple et complet permettant de comparer des feuilles Excel.
Le resultat de la comparaison est évidement rendu dans une nouvelle feuille Excel.
Ce dernier logiciel effectue avec brio la comparison de cellules (ou lignes) fusionnées.

Voir Excel Compare sur le site de Formula Soft.

La formule Excel.
Soit le fichier Excel Before.xls contenant l'ancienne version du rapport.
Soit le fichier Excel After.xls contenant la nouvelle version du rapport.
Le fichier Difference.xls contiendra les formules comparant cellule par cellule le contenue du fichier before.xls avec celui du fichier after.xls.

La formule magique est:

=IF(('C:\temp\[Before.xls]Sheet1'!A1<>'C:\temp\[After.xls]Sheet1'!A1);("Before: '"&'C:\temp\[Before.xls]Sheet1'!A1&"' After: '"&'C:\temp\[After.xls]Sheet1'!A1&"'");"OK")

Dans la formule, les signes $ ont étés éliminés de la référence de cellule (c'est A1 et non $A$1) afin de permettre une copie relative de la formule.
Après avoir copier la formule dans d'autres cellules, nous obtenons alors un pavé mentionnant des "OK" lorsque les cellules sont rigoureusements identiques. Dans le cas contraire, un message explicatif y est affiché.

SqlServer - Delete en masse

Tous les records effacés par l'instruction SQL Delete sont loggées dans le transaction log.
Lors de l'effacement d'enregistrements en masse dans de grandes tables, cette opération peut rapidement saturer le Transaction Log (et même le disque).
Lorsque la table doit être entièrement vidée, l'utilisation de TRUNCATE TABLE vient à point.
Cependant, pour les opérations d'archivage partiel (ou certains records doivent rester présents dans la table) ce n'est pas aussi simple.
Heureusement, il existe une solution.

Etape 1: 
Modifier la Configuration de la DB et activer l'option "Truncate Log On Checkpoint".
Lorsque la DB est configurée en "Truncate Log On Checkpoint" (condition nécessaire dans notre cas), le transaction Log sera automatiquement tronqué à chaque checkpoint effectué par la DB.
Il est possible d'effectuer des effacements en masse en utilisant de multiples petites transactions sans saturer le transaction Log.

Etape 2: 
Utiliser l'une des méthodes suivantes:

Méthode 1

Utiliser des batch... utilisant SET ROWCOUNT pour limiter le nombre d'enregistrements effacés par passe.
L'utilisation de multiples passes permet à SQL server d'éventuellement placer un checkpoint. Dans le cas contraire, tous les enregistrements seraient poussés en une seule fois dans la transaction log (aie-aie-aie!!).

WHILE EXISTS ( SELECT * FROM table WHERE condition_to_delete )

BEGIN

SET ROWCOUNT 1000

DELETE Table WHERE condition_to_delete

SET ROWCOUNT 0

END

Commentaire:
Durant mes essais, je n'ai pas eu l'occasion de voir SQL server placer efficacement un checkpoint.
Mon transaction log devenait suffisament grand (> 200Mb) pour que j'arrête moi même l'exécution du batch.
Bref, bien que le SQL et le principe semblent correct, la méthode ne m'a pas convaincu!


Méthode 2:
Dans ce cas de figure les modifications sont régulièrement commitées dans la DB. Par conséquent toute option de rollback est impossible... soyez certains de ce que vous faite!

set rowcount 5000

while 1=1

begin

begin tran

delete from X

commit

if @@rowcount = 0

break

end

set rowcount 0

Méthode recommandée:
J'ai personnelement utilisé la variation suivante (mixant la méthode 1 et 2) m'ayant permis d'éliminer 4 Go de donnée avec un transation log n'ayant pas dépassé 80 Mo.
C'est par conséquent la méthode que je recommande.

WHILE EXISTS ( SELECT * FROM tblXmlMsg where ID_Session < 4356)

BEGIN

print '.'

begin transaction

SET ROWCOUNT 300

DELETE tblXmlMsg where ID_Session < 4356

Commit transaction

SET ROWCOUNT 0

CHECKPOINT

END 

20/05/2009 - mise à jour - Une méthode encore bien meilleure car elle évite le "Select" avant l'effacement des enregistrement. Dans une table de 40 millions de records, cela fait une différence vraiment impressionnante.

DECLARE @iTotal INT
SELECT @iTotal = 0 

DECLARE @iRowCount INT
SELECT @iRowCount = 1
WHILE @iRowCount > 0
BEGIN
  SET ROWCOUNT 1000
  DELETE tblSSAudit WHERE 
    ID_Audit > 22726409 and ID_Audit <= 45799397 and 
    ID_ObjectType = 78 and ID_Person = 7818 
  SELECT @iRowCount = @@RowCount
  CHECKPOINT
  SET ROWCOUNT 0
  SELECT @iTotal = @iTotal + @iRowCount
  print Cast( @iTotal as VarChar(10) )+' records deleted'
END

Etape 3: 
Ne pas oublier de restaurer les options d'origine de la DB.
Faire un full backup et non un Backup LOG parce que le contenu du transaction log n'est plus "continu" (ce qui empêchera une tâche de restauration complète).

source: Cet article sur SQL Server Forums

The Google File System

Note de dernière minute

Cela fait maintenant plusieurs mois que cet article est prêt.
A l'origine, ce billet devait être accompagné de 6 autres articles, qui faute de temps, ne verrons pas le jour.
C'est qu'il est assez ardu de résumer mes découvertes tellement les surprises et points d'intérêts sont abondants.
Il n'en reste pas moins que la lecture de "Google File System" fut un bon et grand moment. Rentrer et inspecter l'antre du monstre fut en soit une riche expérience.
Si les informations techniques ne vous rebutent pas de trop, plongez y la tête baissée.

L'article...

J'ai récemment terminé la lecture de l'article "The Google File System" (GFS pour les intimes) publié par Google.
Ce dernier traitait du système de fichier mis en place par Google pour répondre à ses besoins de stockages que l'on sait gigantesque.
Bien évidemment, on n'imagine pas Google maintenir tout ses indexes et ses fichiers sur un seul et gros serveur bourré à craquer de disques.
Google utilise un système de fichier distribué constitués de serveurs montés en grappes (cela s'appelle un cluster).
Plutôt que d'opter pour une solution clé en main, Google a étudié les solutions disponibles et ses propres besoins (en accès lecture/écriture) afin de construire un système de fichiers distribués optimisé pour ses activités. Ce système de fichier porte le charmant nom de Google File System (GFS).

Plusieurs éléments m'ont interpelé durant la lecture de cet article.
En premier, il y a réplication intelligente des données.
Mais sans conteste, l'élément le plus marquant était le principe de base de GFS, à savoir que "tout est faillible". Le réseau, les machines, les disques, les systèmes d'exploitations et les logiciels sont susceptibles de connaître des problèmes et qu'immanquablement ces éléments défaillirons.
GFS a donc été conçu avec cette idée qu'il doit résister seul (ou presque) à toutes les défaillances.
Ainsi, selon son principe du "ca va quand même foirer" :

• Si un disque croit avoir correctement écrit une information, le GFS ne prend pas cela pour un acquis et écrit également une clé de vérification (checksum).
• Si le noeud master du système de fichier (contenant la liste des fichiers et leur localisation dans le cluster) crash et ne réponds plus, un autre master sera redémarré endéans la seconde sur la même machine (ou une machine redondante).
• Les noeuds du cluster ou les disques peuvent à tout moment disparaître, devenir défaillants ou réapparaître... et GFS n'en est pas perturbé le moins du monde. Mieux encore, le Master programmera même les tâches de maintenances nécessaires à la restauration des données.
• Si une partie du réseau tombe, ce n’est pas grave, le câblage est redondant. Mais si cela ne permet toujours pas d’accéder aux données, le Master ira extraire les informations depuis un réplica distant.
• Même les API utilisés dans les programmes accédant aux fichiers de GFS partent du principe que l’appel échouera et qu’il faudra réessayer (ce qui présente un avantage décisif durant l’exploitation de GFS).
  

GFS pousse ce principe de tolérance à la défaillance tellement loin que le redémarrage d'un cluster entier (+/- 180 Tb) s'effectue simplement en tuant les processus du master (kill process).

En concevant son système de fichier, Google voulait obtenir un système fiable, rapide, évolutif et autonome. Ces dix dernières années leur donne raison.

Idées cadeaux

Ma petite Françoise se sert énormément de cette liste et ne peut malheureusement pas la modifier.

Afin d'éviter les doubles emplois, pensez à la contacter avant vos achats... elle se fera un plaisir de jouer la coordinatrice.
 

Livres
Je suis et reste un grand livri-vore :-)... il n'est donc pas difficile de me faire plaisir dans ce domaine.

Ma liste est tellement grande que je l'ai placé dans un autre article.
Pour la liste des livres, cliquez ici sinon, il y a aussi la possibilité de chèques cadeaux de la librairie Graffiti



DVD
Peu le savent mais j'aime aussi énormément les dessins animés et les films d'animation.

Dessins animés:

• Wall E
• KungFu panda
• Madagascar
• Small soldier
  

Autres ou films cultes:

•  + +  Nos amis les terriens info
  Il semble également exister un livre "Nos amis les terriens - guide de poche" de Werber info
  
•  + +  Série "Disparition" (Taken en anglais) de Steven Spielerg info
  
• Le comte de Monté-Cristo de Josee Dayan avec Gerard Depardieu, Pierre Arditi info info 2
  
• Trilogie: Retour vers le futur info
  

CD Audio

Encore quelques idées complémentaires.

• Florent Pagny - Pagny Chante Brel info
  

Weezo - Accédez à vos fichiers (photos, musique) à distance

Weezo est un logiciel qui permet d'accéder à vos fichiers (photos, musique) à distance. Il permet également de partager du contenu avec des proches qui n'ont qu'à se connecter à votre espace pour visualiser vos fichiers.

Le produit est simple, gratuit, puissant et s'administre via une interface WEB.
Dans sa version originale, le PC sous XP ou Windows 2000 devait rester soit allumé en permanence (ce qui n'est peut être pas très économique).
Une version plus récente de Weezo permet également de profiter du Wake-Up Lan (activation/allumage d'une machine a distance)

A se fier aux commentaires relatifs à la fiche produit sur Clubic, Wezzo serait un produit vraiment génial et idéal pour le partage entre amis et membres de la famille.

Liens et source:

• Article Clubic "Vos fichiers multimédia vous suivent partout" où Wezzo est abordé.
• FAQ concernant Weezo... entre autre, comment configurer le Wake-Up lan.
• Le site officiel de Weezo.
  

sp_msForEachTable, la perle cachée

 sp_msForEachTable est une stored procedure non documentée de Microsoft mais néanmoins bien partique.
Cette dernière permet d'exécuter une série de commandes pour chacune des tables utilisateur se trouvant dans une base de donnée.


Executer des commandes
Les commandes à executer sont passées en paramètres dans une chaine de caratère.
Les différentes commandes sont séparées par un point virgule et le point d'interrogation sera substitué avec le nom de la table.

sp_msForEachTable ' DBCC CheckTable( ''?'' ) ; go ;'

A noter que les simple quotes sont doublées dans la chaine de caractères.

Generer du script SQL
Cependant, cet utilitaire à un revers... car il execute les commandes.
Dans notre cas, il y a queqlues tables pour lesquelles nous ne désirons pas exécuter CheckTable.
C'est le cas de notre table d'audit qui nécessite 4h30 de vérification.

Pour contourner ce problème, il faut utiliser sp_msForEachTable pour générer le script de vérification mais non pour l'exécuter.
Le script ainsi généré pour être copier/coller et modifier en fonction des besoins avant sont éxécution.

use myDatabase

go

sp_msforeachtable 'print "print ''---- Check table ? ------------------------''" ; print "DBCC CheckTable(''?'')" ; print "GO" ; print "print '' ''" ; print "print '' ''" '

Les commandes sont imprimées dans l'output à l'aide de l'instruction print.
Cette approche réclame cependant de jongler avec les doubles quotes, et la doublure des simple quotes.

1. L'argument de l'instruction print (donc les commandes à imprimer) est enfermé dans des doubles quotes
2. Les parametres texte des commande à imprimer sont enfermés dans des simples quotes doublées

La commande suivante:

sp_msforeachtable 'print "print ''---- Check table ? ------------------------''" ; print "DBCC CheckTable(''?'')" ; print "GO" ; print "print '' ''" ; print "print '' ''" '

Produit le résultat suivant:
print '---- Check table [dbo].[tblWarning] ------------------------'
DBCC CheckTable('[dbo].[tblWarning]')
GO
print ' '
print ' '
print '---- Check table [dbo].[tblHistory] ------------------------'
DBCC CheckTable('[dbo].[tblHistory]')
GO
print ' '
print ' '
print '---- Check table [dbo].[tblForms] ------------------------'
DBCC CheckTable('[dbo].[tblForms]')
GO
print ' '
print ' '
...

SQL Server - Bulk Copy Program

Bulk Copy Program (BCP pour les intimes) permet:

• Soit de décharger le contenu d'une table dans une fichier text
• Soit de recharger un fichier texte dans une table.

Cet utilitaire en ligne de commande est vraiment très utile et rapide... à considérer comme un amis.
Il est par exemple possible de décharger (ou recharger) 20 millions de records dans une table en 35 minutes!

Unload 
Export depuis une table

BCP dbname..tableName out myfile.txt -n -T

options:
-T BCP utilise le Trusted Connection pour se connecter a SQL Server. Les options -U user -P password peuvent également être utilisés.
-n BCP utilise les data types natif de la DB pour faire l'export des données.

Export depuis une requête SQL

bcp "select * from MyDatabase..tblXmlProps where ID_Session < 4356" queryout "d:\backups\unlXmlData\tblXmlProps.txt" -U sa -P ***PASSWORD*** -n

Reload
La réimportation se fait toujours dans une table.
Exemple 1

BCP dbname..tableName in myDatafile.txt -n -T

Options:
-T BCP utilise le Trusted Connection pour se connecter a SQL Server/
-n BCP utilise les data types natif de la DB pour faire l'export des données.

Exemple 2

bcp MyDatabase..tblXmlProps in "d:\backups\unlXmlData\tblXmlProps.txt" -n -b 100 -U sa -P ***PASSWORD***

Options:
-b Batch_Size, nombre de records rechargés par transaction.
-n BCP utilise les data types natifs de SQL server (pour faire la correspondance avec les data types indiqués dans le fichier bcp)

Les champs IDENTITY

Pour recharger des données dans une table contenant un champ IDENTITY, il convient d'autoriser l'assignation de valeur dans le champ identity.
Cela est fait à l'aide de l'instruction suivante:

SET IDENTITY_INSERT dbo.TableName ON

Un fois les informations rechargés, il faut bien entendu réactiver la fonction IDENTITY.
La colonne IDENTITY recouvre sa pleine fonctionalité sans qu'il soit nécessaire d'effectuer une opération complémantaire (ex: re-initialiser la dernière valeur de l'identité). 

Note: L'option IDENTITY ne peut être activé que pour une seule table à la fois (par session).



References

• Détails de la commande bcp sur Microsoft MSDN
  
• Article Microsoft "Importing and Exporting Bulk Data by Using the bcp Utility" (point d'entrée vers des exemples)
• Common BCP questions sur Microsoft MSDN
  

Google Web Toolkit

En novembre 2008, je faisais la découverte de Google Web Toolkit (GWT) par l'intermédiaire une vidéo.
Faisant activement du développement Web et goutant donc aux diverses péripéties JavaScript, je trouvais l'idée maitresse de la vidéo génial (à tomber par terre).
Cependant, le temps manquant, je n'ai jamais entamé cet article à l'époque... voila chose faite :-) 

Je trouvais (et trouve toujours) astucieux de coder une interface en Java (langage très structuré) et ensuite utiliser un compilateur spécifique pour produire du code JavaScript optimisé pour un navigateur donné.

La vidéo "Look for JavaScript and DOM Programming in GWT" (voir ci dessous) présente le concept.
Et si je ne me trompe pas, la vidéo présentait une nouvelle version de la librairie/GWT permettant d'écrire un code plus élégant, renforcent l'utilisation du typage et autorisant l'extention des classes.



Stop watch at 41:37 

SQL Server Training (Jour 5) - Ressources diverses

Finalement, voici quelques ressources bien utiles en relation avec le cette semaine d'entrainement.

• SQL2005 Scripts and samples archive zip.
  Scripts issus du cours "Developing and optimizing databases using SQL Server 2005/2008"
• Aussi cet autre article "SQL 2005 - Index Tuning' (encore à venir).
  

SQL Server Training (Jour 5) - Functions

Voici encore quelques informations complementaire sur les fonctions.

Execution Context
Le contexte d'execution (droit/credentials relatif à l'utilisateur SQL connecté) n'est pas un élément à prendre à la légère.
En effet, losrqu'une vue est basée sur une fonction, le résultat dependra fortement de l'application du contexte d'exécution. Dans certains cas de cascade d'appel avec restrictions/droits diverses, le résultat peut même apparaître imprévisible.

Cependant, il existe des cas où le contexte d'exécution (et sa modification) peut représenter un avantage de grande valeur.
Il est possible de modifier le contexte d'exécution d'une fonction en utilisant la syntaxe WITH EXECUTE AS 'UserToUseForContext' lors de la creation de la fonction.
Ainsi, un utilisateur X ne pouvant pas accéder à la table PRODUCT pourrait en lire le contenu via une Inline table-valued fonction ReadProduct( @Parameter ) utilisant un WITH EXECUTE AS 'AnUserAbleToReadTheProductTable'.
Il va de soit que l'utilisateur X doit disposer du droit d'exécution sur la fonction ReadProduct().
Pour plus d'info, voir pg 9-20 du syllabus.

Exemple

CREATE FUNCTION GetOrders RETURNS TABLE

WITH EXECUTE AS 'Pat'

AS

  RETURN ( Select * from Sales.Orders )

Views et fonction
Comme l'on s'en doute, il est possible de construire une vue sur le resultat d'une fonction. Si la vue est fortement solicitée, il pourrait même être opportun d'y placer un index persistant.
Pour plus d'information, voir l'article sur les Views et Stored Proc

Indexation de function
Comme déja précisé par le passé, il est possible d'indexer une VIEW.
Cependant, certaines vues peuvent être produite sur base du résultat d'une fonction. Dans ce cas, l'indexation d'une telle vue doit répondre à des conditions complémentaires.
En effet, il ne sera possible d'établir un index sur une fonction non determisniste.
Une fonction non deterministe peu retourner des valeurs differentes pour des paramètres identiques (ex: utilisation de fonction Rand, Convert, cast, checksum ou bien une fonction dépendant de l'état de la base de donnée).
Plus d'information disponible dans le sylabus à la section 9-15 "Deterministic and Nondeterministic Functions".

Converttir une stored Proc en fonction
Lorsque cela est possible, il est préférable de convertir les stored procedure en fonction. La raison principale est l'amélioration des performances.

Ainsi, les table-valued functions pourront être utilisées pour remplacer des stored procedures dans les cas suivants:

• Utilisation de SELECT avec paramètre.
• Pas opération d'UPDATE
• Pas d'execution dynamique de requête SQL.
• Utilisation de table temporaire pour résultats intermédiaire.

Les table-valued functions peuvent également être utilisées pour convertir des stored procedures utilisant des cursor.
Lorsque cela est possible, cette conversion permet de diviser le temps d'exécution jusqu'a 30 fois.
A cette fin, les exemples du cours inclus des fichiers spécialements dédiés à ce sujet.
Voir répertoire /Module XX - Extras/03_Getting_rid_of_Cursors/

A noter que l'un des rares cas ou les curseurs restent les plus performant est l'aggregation (sum, average, max, ...) sur de très très larges tables.

Creation de Foreign Key avec des fonctions
Comme déjà précisé, il n'est pas possible de construire une Foreign Key constraint si la destination n'est pas une clé primaire.
Hors, il existe des cas ou les occurences de la destination d'une Foreign Key puissent être multiples.
Dans ce cas, l'on utilise une fonction.

Etape 1:

Créer une fonction avec SCHEMABIDING qui, par exemple compte le nombre d'occurences dans la table de destination.

FUNCTION CountEmployeesOf( @IDManager ) returns Int

WITH SCHEMABINDING

AS

  Declare @RetValue int

  Select @RetValue = count(*) from employee where employee.Manager = @IDManager

  if (@retValue is NULL)

    Select @RetValue = 0

  Return @RetValue

end

Etape 2:

Créer un CHECK CONSTRAINT faisant office de vérification ForeignKey.
Le CHECK CONSTRAINT utilisera la fonction countEmployeesOf( ... ) > 0 pour s'assurer que la condition est bien respectée.

Utilitaires pour SQL Server 7

Cet article est surtout dédié au SQL 7... toujours très actif dans notre société.

Stored procedures
sp_force_Shrink_log
Stored procedure de Andrew Zanevsky (AZ Databases) pour SQL 7.
Cette stored proc est l'outil indispensable pour tronquer le log-file de façon significative (permettant ainsi a ce dernier de retrouver une taille acceptable). J'ai déjà été amener à l'utiliser plusieurs fois avec grand success.
Voir cet artcile sur SQL Server Central

EXEC sp_force_shrink_log @target_size_MB=250

sp_who3
Cette stored procedure pour Sql7 & Sql2000 de Rodrigo Acosta fournit une liste très complete de tous les processus SQL en cours d'exécution. On y trouvera les commandes exécutées, les processus bloqués, etc.
Nouvelle ressource apparue durant ma formation SQL, sp_who 3 peut fournir des informations de premier plan lors d'une situation de stress.
sp_Who3 permet de filter sur spid, login, hostname et dbname.
Voir cet article sur SQL Server Central.

EXEC sp_Who3 @DBName='MyDatabase'

sp_activity
Stored procedure de Mitch van Huuksloot pour SQL 2000 fournissant une liste des processus SQL, les commandes exécutées et des locks actifs.

Pour plus d'information, voir cet article.

Note:
Pour fonctionner correctement sur SQL7:

1. Remplacer le data type BigInt par Numeric.
2. Remplacer la lecture de la colonne req_transactionID (inexistante en SQL7) par NULL

sp_msforeachtable
sp_msforeachdb et sp_msforeachtable sont deux petites perles non documentée facilitant grandement la vie des administrateur.
En effet, cette stored procedure peut lancer des commandes (séparées par des point-virgule) pour toutes les tables d'une base de donnée (ou toutes les bases de données).

Dans l'exemple suivant, la stored procedure génère un script de maintance.
Cette approche permet de modifier le script avant de l'exécuter... utile si l'on sait que le checktable sur notre table d'audit met 2h40m.

sp_msforeachtable 'print ''DBCC CHECKTABLE( "?" )'' ; print ''GO'' ;' 

Un autre exemple plus expéditif

sp_msforeachtable 'print ''Check table ?'' ; DBCC CHECKTABLE( ''?'' ) ; GO ;'

Forcer le check-point
Permet d'imposer un check point (ecriture des données dans le data file) afin de pouvoir tronquer le log file sereinement.

CHECKPOINT

Déplacer la TempDB sur un autre drive
La tempDB est sollicitée pour certaines opérations SQL. C'est en autre le cas de DBCC CHECKTABLE.
Dans ce cas, il est nécessaire d'autoriser la TempDB à grandir... mais que faire lorsque 11 Go sont nécessaires et que seulement 4 Go sont disponible sur le disque system?
Et bien, il faut déplacer la DB temporaire sur un autre disque.

Méthode 1:
Facile et efficace, elle permet de faire rapidement l'operation avec deux commandes SQL.
Mais attention, après l'excution des commandes, il faut:

1. Arrêter et redémarrage le service SQL.
2. Effacer manuellement les anciens fichiers TempDB (surtout s'ils sont gros).
   

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
    (NAME = tempdev, FILENAME = 'd:\MSSQL7\data\tempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
    (NAME = templog, FILENAME = 'd:\MSSQL7\data\templog.ldf')
GO

Pour plus d'informations, voir cet article sur blog.sqlauthority.com.

Méthode 2:
En utilisant cette méthode plus alambiquée décrite sur cet article de Microsoft MSDN

Single User Mode
Placer la base de donnée en modesimple utilisateur (single user) est nécessaire lorsqu'un DBCC CHECKTABLE est exécuté avec un paramétrage de réparation.
Il est plus facile de placer une base de donnée en Single User Mode en utilisant l'interactive SQL.

EXEC sp_dboption 'pubs', 'single user', 'TRUE'

Utilitaires DBCC pour SQL 7

• DBCC CHECKTABLE( 'tablename' )
  DBCC CHECKTABLE( 'tablename', REPAIR_ALLOW_DATA_LOSS )
• DBCC CHECKDB ( 'DBName' )
  Attention: peut literallement exploser la tempDB et remplir completement l disque système. A utiliser avec partimonie sur les grosses bases de données. Préférez DBCC CheckTable
• DBCC command references chez Microsof.
• DBCC undocumented stuff article très intéressant du Sql User Group belge.
  C'est en autre là que j'ai déniché sp_msforeachtable et sp_msforeachtable.

Réduire la taille de TempDB

Executer le script suivant dans une session ISQL:

use tempDB

go

sp_helpfile

Ce script SQL retoune la liste des fichiers (data, log, taille, file-group, grow config... ) composant la base de données TempDB.
Relever le nom d'identification du data file (celui à réduire)... dans mon cas"tempdev".

Executer la commande suivante:

DBCC SHRINKFILE ( 'tempdev' )

Dans mon cas, je suis passé des 11Go à 9 Mo ;-)

Limitations de SQL Server

Suite a une récente demande, me voila à la recherche des spécifications stipulant les limitations des différentes versions de SQL server.

• SQL server 7 et SQL server 2000 
• SQL server 2005
• SQL server 2008
  

Vocabulaire Anglais

Mate
Fr: Mec, copain.
Everything is all right, mate.


Clueless
En: lacking the key to solving a problem, without a hint; being totally unaware, being naive, being innocent and unsuspecting.

Throughput
Fr: Rendement maximum, sortie; débit
En: yield; rate of transfer, amount of data that may be transferred in a data channel or through a device in one second
Using database snapshot will improve the database throughput

Help Yourself

Fr: Servez-vous.

Though
Fr: Quoique, Néamoins.
I do not speak french but my understanding is very good though.

Thought
Fr: pensée, réflexion.
En: idea, concept, product of mind.

Liens pour developpements mono

Voici quelques liens utiles relatifs au développement sous mono:

• Captcha.Net utilisable directement et sans recompilation. Cliquer ici.
• LinQ toujours utile de savoir le support LinQ (System.Data.Linq.dll) sous mono est assuré par le projet Olive.
  
• DbLinqQ est une version de LinQ pour d'autres bases de données.
  MySql, PostgreSQL, Oracle et SQLite sont actuellement supportés.
  Voir le site DbLinq, le project sur Google code ou finallement la page sur Code2Code.net 
• Mono.Options sera disponible dans la version 2.2 de Mono.
  Mono.Options est une superbe libraire de parsing pour ligne de commande. Elle est petite, succinte, facile d'utilisation et puissante... un "tout en un" vraiment jouissif a utiliser.
  Plus d'information ici.
• C5 est une librairie de collection dite générique. C5 fournit des fonctionnalités et des structures de données non disponible en standard dans le namespace System.Collections.Generic namespace.
  On y trouvera en autre des arbres persistent, Une priority queue basée sur une heap, des arrays lists et linked list disposant d'indexe hash, des événements onChange sur les collections. La libraire C5 est gratuite. Pour plus d'informations, cliquer ici pour accéder au site ou ici pour visionner la vidéo de présentation
• Oxygene de RemObjects semble proposer toute une série d'outil pour les développements en .Net, Delphi et Mono. Ils fournissent aussi un complateur pascal pour .Net (compatible Mono), Un remote Object Framework (compatible Mono), etc. A voir absolument!
• DevStudio 2 est un autre produit de RemObjects (incluant Visual Studio 2008). DevStudio semble être un outil de développement performant pour .Net à destination des plateformes Mono et Windows.
  A voir absolument! 
• GENDARME fait son apparition. Gendarme est un analyseur de code utilisant des "règles" (pouvant êtres étendues) permettant de trouver des problèmes dans les applications et librairies .Net. Gendarme inspecte les programmes et les libraries contenant du code au format ECMA CIL (Mono et .NET) pour les analyser et y déceler les problemes courants de développment, le genre de problèmes qu'un compilateur de vérifie pas.
• GUI Shell est un outil développé par Miguel de Icaza.
  GUI Shell permet simplement d'utiliser l'environnement mono comme un shell. Il est donc possible de charger des librairies et d'invoquer des méthodes pour obtenir des résultats en "live".
  GUI Shell est un shell (ligne de commande) basé sur le l'environnement Mono et la syntaxe C#.
  C'est l'un des outils et projets auquel je m'interesse le plus car derrière son interface rudimentaire, ce projet développe toute la puissance de l'environnement .Net et Mono.
  Un nouvel épisode de GUI Shell est apparu sur le blog de Miguel De Icaza. Cette fois, il est possible de faire un "register" de methode statique (ou fonction Lamba) permettant de transformer des objects C# en widget visuel (dans l'interface GUI).
  La démonstration évaluant une expression booléènne est a tomber par terre.
  

Sources:
- le blog de Miguel De Icaza.
- C5 homepage
- la page du projet Gendarme
- GUI Shell

Utilitaires Linux pour Stick USB

Voici un petit acticle en complément d'un précédent relatif aux utilitaires Windows pour Stick USB.

Puppy Linux (USB)
Puppy Linux ("Toutou" Linux en français) est une "mini" distribution Linux initiée par Barry Kauler. Fonctionnant à la fois sur les plus anciens systèmes mais aussi sur les PC les plus modernes (portables ou de bureau ), il présente un environnement graphique de type Windows. Cette distribution est régulièrement mise à jour (de nouvelles versions apparaissent en général tous les 2 à 3 mois). Elle est supportée par l'intermédiaire de son forum de discussion et à la fois par son créateur (Barry Kauler) et par les utilisateurs avertis de Puppy.
Lorsque la distribution est démarrée depuis le CD, celui-ci peut être retiré après le démarrage. Elle peut également démarrer depuis une clef USB, un CD-R(W) ou un DVD-R(W) et on peut y enregistrer ses données personnelles (si le CD est multisession).

Puppy nécessite 64M de RAM. Cette mémoire permet de démarrer des applications telles que le navigateur de Mozilla, l'éditeur de textes AbiWord, des logiciels de graphiques vectoriels, le tableur Gnumeric, et de nombreux autres logiciels libres. Les applications, chargées en RAM, démarrent instantanément
(mêmes sur les plus vieux systèmes) et répondent aux besoin des utilisateurs.

Cliquez ici pour plus d'informations concernant Puppy Linux.

Linux, Netbook, Clef USB et Compagnie
Encore un bon dossier PCInpact faisant le tout des solutions pour installer différentes distributions Linux depuis une clef USB.
Cet article dévolu aux systèmes d'exploiration Linux fera la pert belle à Ubuntu.

Cliquez ici pour accéder au dossier sur PCInpact.

Ultimate Boot CD
Ultimate Boot CD (pouvant aussi démarrer depuis un stick USB) est un outil de diagnotic pouvant être démarré depuis un CD-ROM.
Ce CD contient autant d'outils de diagnotic qu'il est possible d'en stocker sur un CD bootable.

Cliquez ici pour plus d'informations concernant Ultimate Boot CD.

Framework Design Guidelines

Framework Design Guidelines: Conventions, Idioms, and Patterns for Reusable .NET Libraries (2nd Edition) (Microsoft .NET Development Series)

Si je me fie au commentaire de Miguel De Icaza, cet ouvrage serait une référence pour les dévelopements .Net.

Ce livre regroupe un énorme résumé des meilleures partiques à utiliser pour concevoir un logiciel tout en évitant les pièges et voies sans issues.
Il serait aussi l'une des meilleures sources d'idiomes et development patterms utilisé pour  le Framework .Net.
Finalement, en étudiant ces idiomes le lecteur devrait être capable d'écrire du code comme un vrai développeur C# en très peu de temps.

Pour plus info, voir ce lien sur Amazon.

Prism - Delphi pour .Net sous Windows et Mono

J'apprends qu'Embarcadero Technologies (nouveau propriétaire de Delphi) distribue un produit nommé Prism.
Ce dernier est un environnement de développement Delphi pour la plateforme .Net basé sur le célébrissime Visual Studio.Net.
Basé sur un compilateur d'une nouvelle génération (produit par Oxygène), cette version de Delphi.Net est capable de manipuler les types génériques et fonctions anonymes.
Les assemblies produites sont 100% compatibles avec l'environnement .Net de Microsoft (et Mono).
Par ailleurs, l'environnement Prism (basé sur Visual Studio .Net) inclus toutes les technologies .Net récentes (WPF, LinQ, WCF, SilverLight, WinForms, Asp.Net,  ...) mais également quelques autres technologies issues du monde Delphi (RemObjects d'Hydra, dbExpress, DataSnap clients).
La version Architect de Delphi Prism inclus des capacités de design, modélisation et documentation de base de données basés sur  ER/Studio.

Quelques caractéristiques clés:

• Solution de développement complete pour .Net
• Puissance du langage de développement Delphi Prism (Generics).
• Framework dbExpress pour construire des applications Base de Données.
• Support de la platform Mono
• Modélisation et design de base de donnée à l'aide du produit ER/Studio.
• Création de clients DataSnap.
• Base de donnée Blackfish SQL incluant les licenses de déploiement.

Pour en savoir plus, cliquez ici

Utilitaires Windows pour Stick USB

Création de la clef USB Ultime pour Netbook
Super dossier de PCInpact expliquant comment installer et booter un système d'exploitation WinXP ou Vista depuis une clé USB.
Au cours de cet article des utilitaire tel que BartPE ou Ultimate Boot CD sont également abordés.
Poursuivre la lecture du dossier sur PCInpact.

Bart's PE Builder 
PE Builder vous permet de créer un CD-Rom (ou un DVD) bootable (appelé BartPE) de Windows XP (SP1 minimum) ou Windows Server 2003 pour assurer facilement vos opérations de maintenance PC.
Cliquez ici pour plus d'informations sur BartPE

Ultimate Boot CD for Windows
Basé sur BartPE, UBCD4Win inclus le support reseau et vous donne la possibilité de modifier les volumes NTFS, récupérer des fichiers effacés, créer de nouveau volumes NTFS, scanner les disques durs à la recherche de virus, etc.
UBCD4Win est un CD bootable de maintenance contenant des utilitaire de disgnostic, restauration et de réparation réparant presque n'importe quel problème informatique.
Cliquez ici pour plus d'information sur UBCD4Win.
Ou cliquez ici pour avoir une liste des outils disponibles sur UBCD4Win.

Débogger des ISAPI avec Delphi 2007

Configurer correctement IIS
En suivant scrupuleusement les recommandations de cet article, nous pouvions débogger nos DLL isapi écritent en Delphi 2007.

Des breakpoints qui ne s'activent pas!
Cependant, de temps à autres, il y avait des modules isapi pour lesquels les breakpoints n'étaient pas activés alors même que les "debug infos" étaient visiblement inclus (petit point bleu dans la marge après la compilation) .
Comme signe caractéristiques de ce disfonctionnement, les breakpoints viraient à la couleur "caca d'oie" tout en arborant un symbole breakpoint univoque (un signe d'interdiction) au moment du chargement du module isapi.
Après de multiples essais pour inclure autant de debug information que possible, c'est une peu part hasard que j'ai activé l'option "Include remote debug Symbols" dans la section LINKER des options du projet.
Après cet activation, mes breakpoints ont soudainement fonctionnés correctement. Génial!

Configuration screenshots
Voici quelques prise de vue d'une configuration qui fonctionne parfaitement pour débogguer des dll ISAPI avec IIS.
Cliquer pour agrandir

 

  

  

 

Découvrez Ubuntu

Pour les curieux disposant de peu de temps où manquant cruellement de courage pour entamer la lecture d’un ouvrage d’une centaine de page, voici un superbe mini-guide sur Ubuntu.
Du haut de ses 5 pages richement illustrées, ce mini-guide (au format PDF) introduira le lecteur au système d’exploitation libre Ubuntu ainsi qu'a son richissime écosystème.
Ce guide est un excellent point de départ pour tous les néophytes et assurément un document de référence pour les autres.

Merci à Aurélien Paulus, étudiant Belge à l’origine de cet ouvrage.
Vous pouvez poursuivre votre lecture sur son article ici.

GMail 2.0 pour Téléphone Mobile

Google vient de publier la nouvelle version de GMail 2 pour les appareils SmartPhone.
GMail 2.0 est une application Java. Cette particularité la rend compatible avec presque tous les SmartPhones du marché.
Cette nouvelle version présente une amélioration significative des performances, un support "offline" et quelques améliorations bien utiles.

Pour installer cette nouvelle application sur votre téléphone, naviguer vers l'adresse m.google.com/mail



Source: Cet article sur PCInpact.

Citation de Walt Disney

Ne regardez pas en arrière.
Allez de l'avant, 
pour ouvrir de nouvelles portes, 
faire de nouvelles choses par curiosité.
La curiosité nous fait découvrir de nouveaux chemins.

Walt Disney

Merci françoise.

La surchauffe de l'eau au micro-onde

Au détour d'une lecture sur le net, je suis tombé sur le message ci-dessous.
Je dois avouer que l'explication "scientifique" semblait plausible, malgré cela, je restais perplexe quand à l'histoire.
J'ai donc décidé de faire quelques recherches complémentaires sur le Net afin d'étayer les propos.

Comme la surchauffe de l'eau au micro-onde existe bien... je fais suivre l'information.
Je partage d'ailleurs le résultat de mes recherches... jetez donc un coup d'oeil sur les liens et vidéos au bas de cet article.

Danger dû au sur échauffement de l'eau dans un micro-ondes.

Il y a environ cinq jours, mon fils de 26 ans a décidé de prendre une tasse de café instantané. Il a pris une tasse d'eau et l'a mise dans le four à micro-ondes pour la réchauffer (ce qu'il fait régulièrement). Je ne suis pas certain de combien de temps il a mis sur la minuterie, mais il m'a dit qu'il voulait l'amener à ébullition. Lorsque la minuterie s'est arrêtée, il a enlevé la tasse du four. En prenant la tasse, il a noté que l'eau ne bouillait pas. Soudainement l'eau dans la tasse lui a 'explosé' au visage. La tasse est demeurée intacte jusqu'à ce qu'il la jette hors de ses mains, mais l'eau lui a sauté au visage à cause d'une accumulation d'énergie. Il a des cloques partout sur le visage et a subi des brûlures au premier et deuxième degré qui resteront probablement. De plus, il a perdu partiellement la vue de son oeil gauche. Lors de son arrivée à l'hôpital, le médecin qui le traitait a mentionné que c'est un fait courant et que l'eau (seule) ne devrait jamais être réchauffée au four à micro-ondes. Si on doit le faire de cette manière, quelque chose devrait être introduit dans la tasse, tel un bâton de bois ou une poche de thé (sans agrafe) afin de diffuser l'énergie.

Explication scientifique de ce phénomène.

Ce phénomène est connu sous le nom de sur-réchauffement. Ça se produit lorsque l'eau est chauffée et particulièrement lorsque le contenant est neuf. Ce qui se produit, c'est que l'eau se réchauffe plus rapidement de ce que les bulles de vapeur peuvent se produire. Considérant que les bulles ne peuvent se former et dégager la chaleur accumulée, le liquide ne bout pas, se réchauffe et dépasse son point d'ébullition. Ce qui se produit par la suite, c'est que lorsque l'eau est déplacée soudainement, le choc est suffisant pour causer la création rapide de bulles qui expulsent l'eau chaude. La formation rapide de bulles est également la raison pour laquelle des liqueurs carbonisées se déversent lorsqu'elles sont brassées avant de les ouvrir.

Prière de faire circuler ces informations aux personnes que vous connaissez afin de prévenir les blessures graves et la souffrance aux autres.

D'après le site HoaxBuster, le phénomène existerait bien (voir ce post).
Voici quelques informations complémentaires via une recherche de "superheating water in microwave" sur Google.
Vous trouverez ici une explication du phénomère... malheureusement, c'est en anglais.

Racket virtuel dans un monde réèl

De nos jours, je crois que la plupart des parents font attention aux problèmes de racket scolaire.Ainsi, il est courant de voir des parents conseiller à leur enfant de ne pas prendre sa console de jeux portable, de faire attention à son GSM, son lecteur MP3, de ne pas ostensiblement arborer des signes de richesse, etc.
Bref, le parfait manuel pour retrouver nos enfants en vie chaque soir....

Maintenant, il faudra également mettre nos enfants en garde contre le racket virtuel.
Avoir des objets dans un monde/jeu virtuel peut également motiver la convoitise.
Ansi, deux adolescents des Pays-Bas viennent de se faire condamner pour racket virtuel.

Cela semble peut être ridicule... mais réalisez quand même qu'un gosse s'est retrouvé tabassé et menacé avec un couteau pour céder une amulette qu'il possédait dans un jeu en ligne!

Source: Cet article sur PCInpact.

Generation PDF

Voici quelques références utiles concernant la génération de document PDF:

Outil de conversion pour application WEB:

• Prince pour la génération PDF depuis des documents html, css et XML.
  Prince est utilisé par Google Doc.
• Apache FOP permet également la génération de documents en ligne mais bassé sur des documents XML et une XSL-FO (formatting Object)

 Applications pour Windows:

• CutePDF writer (freeware)
• doPDF (freeware)
• PDFCreator (freeware)
  

 Références:

• Liste de logiciels sur Wikipedia (très bon article en anglais).
• Description de PDF (Portable Document File) sur Wikipedia (très complet).
• Article comparant PDF et XPS (Xml Paper Specification)
  

SQL Server Training - Estimated Execution plan

L'affichage du plan d'exécution est possible de différentes façon (entre autre via l'interface du SQL Analyser ou via SET STATISTICS XML ON).
Un précédent article sur les stored procedure traitait de cet activation.
Celui ci, plus pragmatique, fournira quelqyes de détails utiles pour la lecture du plan d'exécution.

Les flux d'informations
Les flux d'informations sont symbolisés par des flèches plus ou moins épaisses.
Plus elles sont épaisses, plus le flux d'information est gros (en terme de milliers de records).
Il va de soi que les flux d'informations en amont du plan d'exécution (les sélections de données depuis la DB) doivent être aussi restreints que possible.
Plus ils seront importants et plus le coût s'en ressentira sur le temps de traitement (jointure, tri, filtrage) et la consommation de la mémoire de travail.

Les jointures
En interne, l'engine SQL utilise plusieurs algorithmes pour joindre des rowset de source différentes.
Parmis ces algorithmes, il y a:

1. Le Merge Join.
2. Le Hash Join.
3. Le Nested Loop Join
   

Le Merge Join
Le merge join est l'opération la moins couteuse et la plus rapide.
Les deux rowsets peuvent être joints en une seule opération continue.
Cette opération n'est possible que si les deux rowset sont stockés (produit) avec le même tri.

Le Hash Join
Opération plus couteuse que le merge join et donc plus lente. Dans ce cas de figure, une clé de hashage doit être produite sur les données avant une opération de Merge Join.
Cet opération nécessite l'usage d'une "table temporaire" pour stocker les résultats intermédiaires, elle est donc gourmande en mémoire.

Le Nested Loop Join
L'opération Nested Loop est sans conteste l'opération la plus couteuse (et de loin). 
Pour chaque entrée d'un rowset, SQL Serveur effectue une opération de recherche dans le second. Le Nested Loop Join a donc des performances catastrophiques.
Cette jointure n'est envisagée par SQL server qu'en dernier recours.
Une façon de l'éviter est, en autre, de donner un maximum d'informations au "query optimizer".
Cela est possible:

1. En complétant au maximum la requête SQL (plus de critères dans la clause WHERE, augmenter les critères de jointures dans les joints, etc).
2. Réécrire la requête SQL différement (par exemple, remplacer les outer join par des sub-query).
   

SQL Server Training (Jour 4) - View et Stored Proc

Views
Les vues sont vraiment très populaire dans beaucoup de companies pour les raisons suivantes:

• Les données sont publiées via une View sur laquelle on applique la sécurité. Un access direct aux data model n'est jamais fournit.
• Les données sont modifiés à l'aide de stored proc (utilisation de sécurité EXECUTE).
• Dans ce cas de figure, la view intervient comme une couche d'abstration masquant le Data Model sous-jacent. Il est dont possible de faire évoluer le schéma sans que cela perturbe les applications clientes en adaptant les views.
• Permet d'organiser les données pour export (bien qu'il y ait d'autres outils pour cela).
• Permet de nommer clairement (humainement compréhensible) les colonnes.
• Permet de masquer les informations non pertinentes.
• Par défaut, les enregistrements d'une view peuvent être mis-à-jour ou effacés (si la view n'est, bien entendu, pas trop compliqué. Voir 7-10).
  

Désavantages:

• Il faut garder à l'esprit que les views diminuent les performances du moteur DB (parce que les noms sont évalués on-the-fly). Ainsi, on veillera à ne jamais dépasser 5 niveaux (View built on another view).
• Eviter les jointures dans le views (après 5 jointures, les performances sont terriblement mauvaises).
  

SCHEMABINDING

Les views n'utilisent pas de contrainte fortes sur le schema (Par default, SCHEMABINDING n'est pas actif). Cela présent un avantage et un inconvénient.
L'avantage est qu'il est possible de modifier le schéma sous-jacent sans devoir effacer et recréer la view. C'est vraiment pratique pour les opérations de maintenance. Cependant, si erreur il y a (parce qu'il manque une colonne nécessaire dans la table sous-jacente), elle ne sera révélée que lorsque la view sera accédée.
Avec un SCHEMABINDING, il sera impossible de modifier les tables sous-jacentes sans détruire les views qui l'utilise (pour les recréer après).
Cela diminue certe le risque d'erreur mais rends la maintenance très contraignante.

Persistant Index

Comme précisé précédemment, les Views ne sont pas très performantes. C'est encore plus vrai si elles couvrent de grandes tables ou de multiple jointure.
L'une des façon de contourner ces problèmes de performances est d'utiliser une "persistant index" sur la view.
Cette opération d'indexation de view crée effectivement un CLUISTERED INDEX. C'est a dire un index B-TREE contenant les pages de données... comme il s'agit d'une vuew, c'est une copie des pages de donnée qui se retrouvent dans l'arbre.
Et bien entendu, ce sont toutes les données qui sont dupliquée. La view sera effectivement performante... mais il y a un prix (les données sont dupliquées).
Bien que couteuse, cette métode a un avantage indéniable. En cas de corruption de la table source, SQL Server sera capable de récupérer les données corrompues depuis le "persistant index" (avec DBCC DBRepair).  

Maintenance: Utilisation de view et Triggers
Si une table disparait du data-model pour être remplacé par une structure plus compliquée, il est possible de la "re-rendre disponible" via une View.
L'utilisation de TRIGGER INSTEAD OF permet même de continuer à supporter les operations insert/Update/Delete sur la view.

Stored Procedure
Pas grand chose de nouveau à apprendre dans ce domaine. Cependant, ce ne fût pas une perte de temps.

Il est fortement conseillé d'éviter l'usage des tables temporaires (#TempTableName) et des Curseurs lorsque cela est possible. L'utilisation de nouvelles structures et fonctionnalités de SQL Server permettent d'obtenir des codes 30 fois plus rapides.

Il ne faut JAMAIS utiliser le préfix "sp_" pour nommer les procédure. "sp_" est réservé aux "System Procedures". Lorsque l'interpreteur SQL essaye de résourde le nom de la stored proc commençant par "sp_" dans la master DB. Si elle n'y est pas retrouvée, une tentative de localisation dans la DB courante est alors entamée. Cela représente une surcharge d'exécution inutile!

Une stored procedure retourne toujours un entier. Cette fonctionnalité devrait être utilisée pour retourné un status d'éxécution à l'appellant. Par convention 0 = false, 1..N indique un status.
Le status est assigné avec avec l'instruction return (ex: RETURN 2).
Le status est récupèré lors de l'appel par une assignation directe.
Exemple:
DECLARE @UspStatus integer
DECLARE @AParam varchar(50)
SELECT @AParam = 'Hello'
DECLARE @MyResultingText varchar(100)
EXEC @UpsStatus = MyStoredProc "Param1", 5, @AParam, @MyResultingText OUTPUT

Recommandations

• Préfixer les stored procedure avec "usp_" pour "User Stored Procedure" (recommandation).
• Lors des appels de stored procedure, ne pas oublier d'utiliser le mot clé OUTPUT pour récupérer un paramètre par valeur (sinon NULL est retourné).
  EXECUTE @ProcStatus = ProcedureName ParameterValue, @ParameterVariable, @ReturnedValue OUTPUT
• Une stored procédure doit toujours vérifier la validité de sess paramètres.
  IF (@Value <0 )
  RAISERROR( 'Error Message', 14, 1 )
  RETURN
  END
• Lors d'un RAISERROR, ne pas utiliser des erreurs avec une trop grande sévérité car cela rompt la connection (avec des effets indésirés).
• Utiliser la variable @@recordCount pour connaître le nombre de records retourné par la dernière opération SQL.
• Lors de la modification d'une stored procédure, ajouter les nouveaux parametres avec une assignation de valeur par défaut. Cela permet aux applications existantes de continuer à fonctionner sans modification.
• Lors de l'appel d'une stored procédure, utiliser le mot clé DEFAULT pour utiliser la valeur par défaut (signalée dans la définition dans la store procédure).
  

Plan d'éxécution des stored procedure
Le plan d'exécution permet d'avoir une synthèse des opérations effectuées par SQL Server durant l'exécution d'une requête SQL.
L'activation de l'EXECUTION PLAN depuis la console SQL est triviale (depuis la barre des boutons).
Cependant, lorsqu'il s'agit d'une stored procedure, il faut faire appel à des options spécifiques.
Il est ainsi possible de détecter les opérations couteuses dans une stored procedure afin de les optimiser.

Il y a différentes façons d'obtenir des informations d'exécution:

SET STATISTIC IO ON/OFF
Affiche dans les messages le nombre de pages de 8kb lues depuis le disque.
Logical Reads: # de pages qui devaient lues pour atteindre l'objectif.
Physical Reads: # de pages chargées depuis le disque (les autres pages se trouvaient dans le cache).
Read-Aheads: Lors d'operations avec temps de processing perdu, SQL serveur prend l'initiative de lire des pages complémentaires (# de pages chargées en plus dans le cache).

SET STATISTIC TIME ON/OFF

Affiche les details de temps d'exécution dans les messages.

SET STATISTICS PROFILE ON
Fonctionnalité très utile mais malheureusement disparue en SQL2008.
Fournit le plan d'exécution sous forme de table relationnelle.
Il y est possible de lire l'entièreté du schema d'execution presqu'aussi simplement que le graphique lui-même.

SET STATISTICS XML ON
Produit les même information que STATISTICS PROFILE au format XML.
Ce format à l'inconvenient d'être moins lisible et donc plus difficile à exploité par simple lecture. Cependant, il existe une foule de logiciel exploitant ce format pour en reconstituer un graphique.
Contient en autre un noeud nommé MissingIndexGroup.

SQL Server Training (Jour 3) - Intégrité et Trigger

Voila, je vais vite profiter d'une petite pause pour publier le résumé d'avant hier.
Je commence vraiment à crouler sous l'information :-)

Intégrité des données
Aujourd'hui cours sur la gestion de l'intégrité dans les DB.
Il y a trois façons de gérer l'intégrité:

1. Domain Integrity: Contrainte sur les colonnes, restreindre les valeurs dans le colonnes.
2. Entity Integrity: intégrité des enregistrements. Chaque record doit être identifié de façon unique (utilisation de Primary Key)
3. Referential Integrity: L'information contenue dans une colonne doit correspondre au contenu d'une clé primaire d'une autre table.
   

Options pour renforcer l'intégrité des données:

• Data types.
• Rules:  utilisé pour définir les valeurs acceptables dans une colonne. (OBSOLETE, not Ansi compliant).
• Default Values: permet de définir la valeur par défaut de certains champs. (OBSOLETE, not Ansi compliant).
• Xml Schema: Permet de renforcer la validation des données dans les champs XML.
• Triggers: Permet l'execution de code au moment de la sauvegarde/mise-à-jour/effacement d'enregistrement. Les triggers permettent un controle poussé de l'intégrité et du flux de donnée.
• Constraints: Ansi compliant. Definit comment la moteur DB doit renforcer l'intégrité des données.
  Il existe plusieurs type de contraintes:
  
  
  1. Primary Key
  2. Default
  3. Check
  4. Unique - La valeur contenue dans la colonne (ou combinaison de colonnes) doit être unique pour toute la table.
  5. Foreign Key  
     

Limite des contraintes:

1. Ne peut pas extraire des données depuis une autre table.
2. Ne peut pas faire appel à une stored procedure.
   

Check Constraint
Cette contraite permet de faire des vérifications sur les données (sur base d'expression) lorsqu'elles sont modifiées.
Cette contrainte est l'une des plus mal estimée alors qu'elle est la plus simple et fournit l'une des plus grande valeur ajoutée.
Plusieurs Check Constraint peuvent exister sur un même champ.
Un Check Constraint peut faire référence à un autre champs de la table (mais pas à une autre table).
Les "Check Constraint" ne peuvent pas contenir de sous query (voir Trigger).
Avantages:
Permet la modification d'information depuis une application tiers (Access) ou l'administrateur (SQL Statement) sans mettre l'intégrité des données en danger.
Note:
Il est possible de désactiver des "Check Constraint" pour accélérer le traitement de longues opérations (update, reload).

Foreign Key Constraint
Cette contrainte permet de s'assurer que la valeur d'une colonne correspond bien au contenu de la clé primaire (unique) d'une autre table.
C'est une contrainte d'intégrité référentielle.
Pour un champs soumis à une foreign key, il est impossible d'y introduire une valeur illégale. Bien que protégeant bien l'intégrité des données, les Foreign Keys présentent quelques désavantages les rendant peu populaire.
C'est ainsi que beaucoup de sociétés préfère ne pas implémenter ce genre de contraintes en production (leur préférant les triggers).

Actions:

Lors de la définition des "Foreign Key" constrainte, il est possible de préciser une clause CASCADE (update or delete). Cette dernière clause permet d'indiquer quel operation doit être exécuté sur le record lorsque la référence est modifiée ou effacée.
Par default, l'action est NO ACTION dans les deux cas.
Il est possible d'utiliser CASCADE qui est vraiment dangereux lors de l'effacement de la référence car il efface également les dependences.
Si orderDetails.ProductID reference la table product avec DELETE Cascade, alors l'effacement d'un produit efface également toutes rows orderDetails (where orderDetails.ProductID=product.productID). OUPS!
Il existe également des options SET NULL ou SET DEFAULT (voir 5-21). 

Désavantages:

• Limite les operations de modification de schéma (renommer des colonnes).
• Consomme des ressources (il faut faire un choix entre performance et sécurité).
• Peut se montrer lents lors que la clé primaire de référence appartient à une table très grande.
• Nécessaire de désactiver ce type de contrainte lors du rebuild des indexes.
• Représente des contraintes d'utilisation vraiment excessives pour les DB de reporting. Il est en effet courant d'effacer et de recharger de nouvelles données en masse dans ce genre de DB. Dans ce cas, les foreign key représentent des contraintes non nécessaire puisqu'il n'est pas possible de tronquer la table... et la mise à jour d'information en masse est fortement ralentie par la vérification de la contrainte.
  

Default Constraint
Vraiment utile pour s'assurer que certains champs soient tooujours correctement initialisés.
Sont utilisation est recommandée. 

Triggers
Les triggers permettent d'activer du code (actions) lors d'un événement d'insertion/update/delete de records sur une table.
Le trigger est appelé une seule fois par opération (quelque soit le scope de l'opération). Qu'une requête sql modifie ou efface 1 ou 2000 enregistrements (en fonction de sa where clause), le trigger ne sera activé qu'une seule fois pour la modification entière.
Les triggers sont toujours executés dans la même transaction que l'événement.
Il est par conséquent possible de faire un rollback pour annuler l'événement d'origine.
Mais en contre partie, il faut veiller à avoir un trigger aussi court que possible afin de limiter le temps de transaction.

Il y a deux type de triggers:

1. After Trigger: Trigger qui sont exécutés après l'événement sur la table. Ce qui permet par exemple de vérifier des contraintes d'intégrité ou de maintenir des tables d'audit. 
2. Instead Of Trigger: Trigger qui remplace l'événement. Ce qui permet de modifier le comportement standard de SQL Server. Eg: remplacer un effacement physique par un effacement logique ou encore répartir l'information au sauvegarder entre plusieurs tables (utile lorsque le trigger est placé sur une vue).
   

Recommandation d'usage:

1. Récuperer les records insérés ou mis-à-jour depuis la table virtuelle "udpated".
2. Récuperer les records effacés depuis la table virtuelle "deleted".
3. Le trigger doit être écrit de façon fonctionner avec une table deleted et updated contenant plusieurs enregistrement (erreur courante).
4. En cas d'effacement, toujours faire un count(*) from deteled pour s'assurer qu'il y a des informations à effacer.
5. Par default, un trigger n'est pas récursif sur sa propre table.
6. Un trigger peut déclencher un trigger sur un autre table (cascading firing)
   

Informations diverses:

• Pour tester si un champ a été modifié, utiliser la fonction Update(FieldName)
• Pour stopper une trigger (rollback), utilser RaiseError( N'Message', 10, 1 )
• Lorsqu'un trigger insert des records dans une autre table, utiliser le mot clé OUTPUT pour récupérer les valeurs des champs identity dans une variable de type table (voir autre article à venir)
  

Cas d'utilisations:

• Effectuer un effacement logique (active=0) lors d'une instruction DELETE FROM.
• Eviter les effacements physique en les interdisants. Meme si la sécurité est mal configuré, il ne sera pas possible d'effacer les records.
• Propagation d'état. Par exemple, la désactivation d'une catégorie de produit peut egalement désactiver tous les produits et ajouter un commentaire aux commandes en cours sur ces produits.
• Permettre d'effectuer des vérifications complexes et de retourner des messages d'erreurs préçis.
• Remplacement de Foreign Key constraint... car les trigger peuvent être désactivés et réactivé pour accélérer des opérations de maintenance. La contrainte d'intégrité implémenté dans le trigger ne sera revérifiée que lors d'une nouvelle modification de l'enregistrement.
  

SQL Server Training - Migration et compatility Level

Durant mon training, j'ai eu l'occasion de mettre la main sur une série de script de diagnostique SQL2005 plus que très intéressant (missing indexes, Index Fragmentation, Index usage, etc).
Ces scripts sont, bien évidement, exécutés depuis une console TSQL et fonctionnent comme attendu.

Par contre, certains d'entre-eux ne fonctionnaient absolument pas la DB de TrialXS.
Vraiment étrange si l'on sait que ces scripts n'analyse que des données système.


La réponse est simple... La DB TrialXS est restaurée depuis un backup SQL2000.
Par conséquent, le compatibility mode "SQL 2000" est appliqué à la DB (voir DB Options) sour SQL2005.
En modifiant le "compatibility mode" j'ai enfin réussit à tester mes merveilleux scripts.

Par contre cela lève une nouvelle question: 
Comment nos applications ISAPI (ou script de migration DB) vont-ils réagir en se connectant sur une même DB configurée en compatibility mode SQL7 (prod actuel) ou SQL2000 (ancien env. développement) ou SQL2005 (actuellement non activé)?


Pour des raisons évidentes de facilités de maintenance (à venir... mais avec les super trainings scripts SQL), les "compatibility mode" des DB de production doivent être configurés SQL2005.

SQL Server Training (Jour 2)

Que retenir de cette deuxième journée:

XML
Maintenant que SQL Serveur dispose d'un datatype natif XML, il est possible:

1. De stocker du contenu XML dans un champs et d'y appliquer des méthodes de traitement spécifiques (XQuery).
2. De générer directement du contenu XML depuis une requête SQL (FOR XML).
3. D'accepter un input XML, de le parser et le transformer en données relationnelles (OPEN XML).
   

Ce chapitre fut relativement intéressant. Surtout concernant la génération de document XML directement depuis des requêtes sql.
Dans ce dernier cas, si l'output n'est pas trop conséquent, cela peut vraiment présenter un avantage. Dans le cas contraire, la mémoire cache et Execution Plan Cache seront pénalisés afins de pouvoir générer le document.
L'intégration de XQuery permet de faire des requêtes vraiment puissantes mixant traitement XML (sur du contenu XML d'un champ) et datatype SQL. Cependant, cela nécessite un parsing des documents XML row par row... ce qui est vraiment très pénalisant pour un moteur de DB.
A noter que la mise en place d'index XML primaire et secondaire (FOR PATH) permettent de réduire le temps de processing XML de façon significatif (D'un coût de 266 à 0.1).
Finallement, d'un avis tout personnel, je ne suis pas certain qu'il soit intelligent de stocker du contenu XML dans une DB en vue d'un traitement quelconque (surtout s'il est récurrent).
L'intégration de contenu XML n'est pas en concordance avec l'aspect d'exploitation relationnel de l'information. Par ailleurs, durant le training, nous n'avons pas fait la référence à un cas existant (même sur demande).

Définition des indexes
Nous avons également eu l'occasion de nous consacrer sur la gestion, la création et le l'optimisation des indexes.

Clustered Index:

Index dont les data pages sont stockées dans l'ordre physique de l'index.
L'index cluster est basé sur un arbre B-Tree BALANCE (équilibré autour du noeud root), chaque noeud disposant de relations "sibling" en plus des relations ascendantes et descendantes.
L'introduction d'un nouvel enregistrement dans de tel index est couteux car il faut eventuellement insérer des pages dans l'arbre, modifier les relations entre les différents noeuds... mais surtout garder l'arbre balancé.
L'avantage de cet index, est que SQL server maintien des statistique permettant d'évaluer la pertinance d'une recherche dans l'arbre (au lieu de simplement envisager un table-scan).

Non Clustered Index:
Fonctionne de façon identique au clustered index (B-Tree) A LA DIFFERENCE que les pages sont soit:

1. Stockées dans la heap
2. Soit déjà stockée dans un clustered index.
   

Chaque noeud du "Non Clustered Index" fait une référence à la page de donnée à l'aide d'une clé.

Note 1: Dans la cas d'une table ne disposant pas d'un Clustered Index, les pages sont stockées dans la heap. Il n'y a donc pas d'emplacement prédefinit pour créer de nouvelles les pages de données. Dans ce cas, les insertions sont plus rapides car la nouvelle page de donnée peut être placée arbitrairement.

Note 2: Dans le cas d'une table disposant d'un Clustered Index, pour accéder à l'information depuis un Non Cluster Index, SQL serveur doit faire beaucoup d'opérations en lecture.
A savoir:

1. La lecture du Non Clustered Index pour récupérer 'identification des pages data (DataPageID)
2. Parcours de l'arbre B-Tree pour localiser les pages (DataPageIDs) de données dans le Clustered Index. (il faut garder à l'esprit que dans ce cas, le clustered index n'est trié dans le même ordre).
   

Conditions de selection d'un index

1. Pertinence de l'index.
   Un index est utilisé par SQL serveur s'il permet d'exclure 95% (au moins) des records de la table (lors d'une selection).
   Pour ce faire, SQL serveur consulte les statistiques d'index qui permettent d'évaluer la pertinance de l'index pour certaine valeur (Si la table contient 10.000 records et que la statistique mentionne un range de 2.500 records pour une selection particulière alors l'index sera rejeter... un table scan sera plus performant ).
   Il est également important de savoir que lors d'index sur plusieurs colonnes, seul les statistiques de la première colonne de l'indexe sont utilisées.
   Par conséquent, la sélection d'un index couvrant plusieurs colonnes ne se fait que sur base de la pertinance de la première colonne de celui-ci.
   
2. Couverture de l'index.
3. Ordre de tri.
4. Les hints inclus dans les requêtes SQL pour modifier le comportement du moteur DB.
   

Gestion des indexes

En SQL2005 Enterprise, il est possible de faire des mise-à-jours d'indexes à la'ide de l'option WITH (ONLINE=ON). Cela évite de locker la table durant toute l'opération permattant ainsi aux applications de poursuivre leurs traitement sans interruption. Cette opération consomme néanmoins beaucoup de temps et de ressources.
Il est a noter qu'en SQL2000, le création d'un index place un Lock exclusif sur la table. Il ne faut donc jamais créer d'index sur les DB de production durant les heures de bureau.


En SQL2005, il est possible:

1. De modifier (ALTER) ou de reconstuire (REBUILD) un index. Sous SQL2000, la seule option est de détruire et recréer l'index.
2. d'indiquer le nbre maximum de processeur affecté à la modification d'un index. WITH( MAXDOP=3)
3. Il est possible de definir une granularité de locking plus fine sur les indexes (ALLOW_ROW_LOCKS). Par default le moteur SQL utilise des locks au niveau de la table!!!. 
4. D'inclure des colonnes dans l'index sans que ces dernières n'interviennent dans l'index lui-même. Cette dernière optimisation permet de tirer parti des index sur colonnes multiples sans en avoir les inconvénients. En général, les colonnes additionnelles sont ajoutées pour retrouver rapidement des information pertinantes depuis l'index sans lecture de data pages complémentaire. Avant SQL2005, ces colonnes faisaient partie intégrante de l'index... par conséquent, toute modification des colonnes complémentaires réclamaient la mise-à-jour des liens internes et une opération de re-balancing... alors même que ces informations ne participent pas activement à l'index... c'était donc des indexes couteux.
   
   Avec les "Included Columns", la modification des données complémentaires  n'implique pas les opérations de mise à jours de liens internes et de re-balancing.
   
   Les indexes en SQL2005 sont à ce point plus efficaces qu'il est possible d'éliminer jusqu'à 60% des indexes nécessaires en SQL2000 tout en gardant les mêmes performances
   
   
5. De créer des "Partitionned index" fonctionnant de façon similaire aux tables partitionnées.
6. D'indexer le contenu de champs XML afin d'améliorer les performances et cout de recherche de façon spectaculaire.
7. De désactiver/réactiver des indexes lors de l'exécution de gros batch. Cela permet de gagner du temps machine considérable en évitant à SQL server de constamment mettre à jour l'index. Lors de la réactivation de l'index, ce dernier est reconstruit.
   

Optimisation des indexes

Database Engine Tuning Advisor:
Cet outil de Microsoft est maintenant bien peaufiner et est un incontournable pour l'optimisation des indexes.
Afin d'effectuer une évaluation des indexes, le Database Engine Tuning Advisor utilise une série de requêtes SQL sensées représenter les cas d'utilisations pratique de la base de données. Ces informations peuvent être fournie depuis un fichier SQL, XML ou une trace collectée à l'aide du profiler SQL.
Malheureusement, ce cas de figure peut difficilement s'appliquer aux DB de production.

Exploiter les statistiques des DB:
Pour les DB de production, il faut savoir que SQL Serveur maintien des vues dynamiques reprenant les statistiques d'usage des différents indexes (dm_db_index_usage_stats).
Par ailleurs, le processus de plannification d'execution tiens des statistiques utiles à propos des indexes idéals répondant à certaines requêtes (dm_db_missing_index_details). Cette vue est utilisée par SQL Serveur lui-même pour éviter certaines conception de plan d'exécution inutiles.
Ces informations peuvent être exploitées avec IndexTuning.sql faisant des jointures sur sys.Indexes pour obtenir de précieux conseils... même en production.
From the result of indexTuning.sql, high cumulated_cost_reduction must be addressed!

Fragmentation des indexes:
Il existe deux types de fragmentations.
La fragmentation interne est due à SQL server lui même et nous ne pouvons rien y faire. Elle a d'ailleurs peu d'influence.
Par contre, la Fragmentation externe correspond à la distribution des pages de données sur le disque (espaces disques alloués par le système d'exploitation).
L'identification de la fragmentation se fait à l'aide de Fragmentation.sql (fichier de démonstration du module 4).
Pour une fragmentation inférieure à 30%, un simple ALTER INDEX ... REORGANIZE est suffisant.
Par contre, pour une fragmentation supérieur à 30% un ALTER INDEX.... REBUILD est absolument nécessaire. Cette dernière opération (couteuse) demande au système d'exploitation d'allouer un espace continu.