vendredi 12 décembre 2008

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 )

mercredi 10 décembre 2008

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é.

lundi 8 décembre 2008

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

jeudi 4 décembre 2008

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

mardi 2 décembre 2008

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 ' '
...

lundi 1 décembre 2008

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