Logparser est un petit exécutable fourni gratuitement par Microsoft et qui permet de lire des fichiers structurés propriétaires tels que les eventlogs *.EVT, mais aussi les fichiers CVS, ETW, REG, etc… Tout d’abord il s’agit de le télécharger et de l’installer en trois ou quatre clics. Je passe rapidement sur l’install, il faudra juste bien penser à ajouter le chemin de logparser.exe dans votre PATH.

L’avantage principal est qu’il utilise un pseudo langage proche du SQL pour effectuer des recherches. Par exemple, si je veux remonter les 3 dernières erreurs dans le journal Application :

$ logparser "SELECT TOP 3 TimeGenerated, SourceName, Message
from Application
where eventtype=1
order by TimeGenerated desc" -i:EVT -stats:OFF

TimeGenerated           SourceName      Message
-------------------     -----------     ------------------------------------------------
2010-03-11 13:03:39     MSSQLSERVER     Database mirroring connection error 2 'Connection ...
2010-03-11 13:03:17     MSSQLSERVER     Database mirroring connection error 4 '64...
2010-03-11 13:02:57     MSSQLSERVER     The mirroring connection to "tcp://192...

On peut utiliser le mot-clé ‘into‘ après le SELECT pour indiquer que l’on souhaite placer le résultat dans un fichier. La répartition des codes erreurs dans les journaux d’évènements se fait comme suit:

$ logparser "select distinct EventTypeName, EventType
from Application" -i:EVT -stats:OFF

EventTypeName       EventType
------------------- ---------
Information event   4
Success event       0
Warning event       2
Error event         1
Failure Audit event 16

On recherchera donc en priorité les eventtype = 1.

Parmi les options intéressantes:

-i: Donne le type d’entrée du fichier. exemple, -i:EVT indique que l’on souhaite lire un fichier *.evt. S’il n’est pas indiqué, logparser le trouve tout seul en fonction de l’entrée.
-o: Donne le format de sortie. Par exemple -o:XML pour écrire au format XML. S’il n’est pas indiqué, logparser le trouve tout seul en fonction du fichier indiqué dans la clause ‘into‘.
-stats: ON/OFF: si les stats sont indiquées, alors logparser renvoie le nombre de lignes traitées et le temps passé.
-q: ON/OFF: mode quiet, par exemple pour éliminer les entêtes.

Il possède également des fonction d’agrégation de type GROUP BY / HAVING… Par exemple pour ramener le nombre d’erreurs par Source:

$ logparser "SELECT SourceName, count(*)
from Application where EventType = 1
group by SourceName order by count(*) desc"
-i:EVT -stats:OFF

SourceName                        COUNT(ALL *)
--------------------------------- ------------
MSSQLSERVER                       83
Winlogon                          30
Software Licensing Service        2
Perflib                           2
.NET Runtime Optimization Service 2
SideBySide                        2
SQLDIAG                           1

Dès lors il devient facile de créer un script qui va régulièrement exécuter une recherche des erreurs remontées dans les journaux Application et System. Nous pouvons utiliser un simple fichier par exemple pour stocker la dernière ligne lue à chaque passage, et nous permettre de repartir de cette ligne à l’exécution suivante:

$ logparser "SELECT MAX(RecordNumber) into E:\maxrecod.app.log FROM Application"
-i:EVT -stats:OFF -q:ON
$ cat E:/maxrecod.app.log
3597

… et dans un script, affecter le contenu du fichier dans une variable, et utiliser la variable dans une nouvelle recherche. Par exemple sous cygwin:

MAXRECORD=$(cat E:/maxrecod.app.log)
if [ ! -z ${MAXRECORD} ]
then
      SELECT TimeGenerated, SourceName, Message into E:/Application.err
      from Application where EventType=1 and RecordNumber >= ${MAXRECORD}" 
      -i:EVT -stats:OFF -q:ON
else
      echo "borne invalide"
fi

A+. David  B.Autres articles à découvrir sur le blog :

• Planification dans Adaptive Server (David BAFFALEUF) [Sybase]
• Utiliser ASMCMD (Thierry GASCARD) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Retrouver les tables dont les stats ne sont plus compilées en automatique (David BAFFALEUF) [SqlServer]
• MySQL et les tables temporaires internes (Cédric PEINTRE) [MySQL]

Sauvegarder le cache, oui, mais pour quoi faire ?

Vous devez effectivement comprendre l’intérêt de la chose si vous vous êtes déjà frotté à l’administration d’un MySQL mais je dois vous avouer qu’en abordant le sujet pendant la pose café, tout le monde n’était pas forcement convaincu.

En effet, la mise en œuvre d’une telle fonctionnalité soulève une autre question : Pourquoi redémarrer MySQL ?

Effectivement, le véritable intérêt de cette sauvegarde est de pouvoir couper son MySQL sans perdre les informations stockées dans le cache. Intérêt évidemment compris de tous dans le cas d’un crash du serveur MySQL.

Dans les faits, XtraDB permet de sauvegarder et restaurer le cache même si il n’y a pas eu d’arrêt de l’instance. Ce qui peut d’ailleurs s’avérer utile dans certaines conditions, après le passage d’un gros batch par exemple, afin de retrouver un cache d’activité transactionnelle standard.

Pour en revenir à la question posée par mon collègue, en dehors du contexte de crash, pourquoi redémarrer MySQL ?
Alors que d’autres éditeurs de bases de données concentrent leurs efforts pour justement limiter au maximum le redémarrage des instances, il semble que MySQL soit plus exposé que les autres à ce type de problématique.
Et effectivement, même si la modification dynamique de paramètres gagne du terrain avec les nouvelles versions, la gestion des fichiers physiques reste problématique dans un environnement de production critique.

Il s’agit donc ici de se poser la question à l’envers et c’est donc pour ces différentes raisons que la sauvegarde du cache de données est pertinente avec MySQL !

Comment ça marche ?

Le pré-requis est évidemment d’installer et d’utiliser le moteur de stockage XtraDB fourni par Percona (lien en fin d’article).

Pour comprendre le fonctionnement de cette sauvegarde, j’ai réalisé un petit test en utilisant le compteur Innodb_buffer_pool_pages_data qui indique combien de pages du cache sont utilisées :

1 – Arrêt et relance de l’instance MySQL (Tous les caches sont vidés)

2 – Je contrôle l’état de mon cache :

• mysql> show global status like ‘Innodb_buffer_pool_pages_data’;

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 14    |
+-------------------------------+-------+

3 – Je fais une requête simple sur ma table ETAT :

• mysql> select * from capdata_innodb.ETAT;

4 -Nouvelle vérification du cache, à ce stade, la table ETAT a bien été montée dans le cache (au moins une partie de ses données) :

• mysql> show global status like ‘Innodb_buffer_pool_pages_data’;

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 17    |
+-------------------------------+-------+

5 – Je lance une sauvegarde du cache :

• mysql> select * from information_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_DUMP*/;

+------------------------------+
| result_message               |
+------------------------------+
| XTRA_LRU_DUMP was succeeded. |
+------------------------------+

6 – Arrêt et relance de l’instance MySQL (Tous les caches sont vidés)

7 – Vérification du cache :

• mysql> show global status like ‘Innodb_buffer_pool_pages_data’;

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 14    |
+-------------------------------+-------+

8 -Restauration du cache :

• mysql> select * from information_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_RESTORE*/;

+---------------------------------+
| result_message                  |
+---------------------------------+
| XTRA_LRU_RESTORE was succeeded. |
+---------------------------------+

9 – Dernière vérification du cache :

• mysql> show global status like ‘Innodb_buffer_pool_pages_data’;

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_pages_data | 17    |
+-------------------------------+-------+

Le cache a bien été restauré, à vous le warm restart facile !

Percona a réalisé un petit benchmark comparatif dont voici le résultat :

Retrouvez toutes les informations sur le moteur XtraDB ici : http://www.percona.com/docs/wiki/percona-xtradb:startAutres articles à découvrir sur le blog :

• Principes d’une sauvegarde à chaud (David BAFFALEUF) [SqlServer]
• MySQL et les tables temporaires internes (Cédric PEINTRE) [MySQL]
• Attention : publication transactionnelle et indexes non clusters (David BAFFALEUF) [SqlServer]

Remarque :Toutes les opérations SQL se font connecté en tant que « as sysdba »

1. Configuration  des bases en archivelog et force logging

SQL> ALTER DATABASE FORCE LOGGING;
SQL> alter database archivelog ;

2. Créer une sauvegarde de la base primaire

A) Créer les standby controlfile
SQL> alter database create standby controlfile as ‘/u01/app/oracle/TEST/standby1.ctl’;

B) Créer les standby redo log .
SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/TEST/srl01.log’ SIZE 10M   REUSE;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/TEST/srl02.log’ SIZE 10M   REUSE;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/TEST/srl03.log’ SIZE 10M   REUSE;
SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘/u01/app/oracle/TEST/srl04.log’ SIZE 10M   REUSE;

Il est conseillé :

• de créer un fichier « standby redo log » de plus que les redo log
• d’avoir une taille des standby redo log >= taille des redo log

C) Effectuer une sauvegarde de la base primaire ((il est possible de le faire à chaud par rman)

Arrêter la base primaire

Faire une copie physique dans un répertoire de sauvegarde des types de fichier suivants :
- datafiles
- online redo logs
- standby controlfile
- standby redolog

Redémarrer la base primaire

3.  Modification ou création des  paramètres d’initialisation des bases primaire et standby

Base ayant le rôle primaire (PTEST) :
log_archive_format=%t_%s_%r.arc
log_archive_dest_2=’service=STEST LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=STEST’
log_archive_config=’DG_CONFIG=(PTEST,STEST)’
LOG_ARCHIVE_DEST_STATE_2=ENABLE
db_name=TEST
db_unique_name=PTEST
fal_server=STEST
fal_client=PTEST
DB_FILE_NAME_CONVERT=(‘/u01/app/oracle/TESTSTANDBY’,'/u01/app/oracle/TEST’)
LOG_FILE_NAME_CONVERT=(‘/u01/app/oracle/TESTSTANDBY’,'/u01/app/oracle/TEST’)
standby_archive_dest=/u01/app/oracle/flash_recovery_area/STEST/standby_archive
STANDBY_FILE_MANAGEMENT=AUTO

Base ayant le rôle standby (STEST)  :
fal_server=PTEST
fal_client=STEST
DB_FILE_NAME_CONVERT=(‘/u01/app/oracle/TEST’,'/u01/app/oracle/TESTSTANDBY’)
LOG_FILE_NAME_CONVERT=(‘/u01/app/oracle/TEST’,'/u01/app/oracle/TESTSTANDBY’)
STANDBY_ARCHIVE_DEST=/u01/app/oracle/flash_recovery_area/STEST/standby_archive
log_archive_format=%t_%s_%r.dbf
STANDBY_FILE_MANAGEMENT=AUTO
log_archive_config=’DG_CONFIG=(PTEST,STEST)’
LOG_ARCHIVE_DEST_2=’service=STEST LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=PTEST’
LOG_ARCHIVE_DEST_STATE_2=DEFER
CONTROL_FILES=(« /u01/app/oracle/TESTSTANDBY/standby1.ctl », « /u01/app/oracle/TESTSTANDBY/standby2.ctl », « /u01/app/oracle/TESTSTANDBY/standby3.ctl »)

4.  Configurer le listener, Oracle services names de la base primaire et de la standby

A) Configurer les fichiers $ORACLE_HOME/network/admin/listener.ora pour l’enregistrement statique

listener.ora :

SID_LIST_LISTENER_PRIMARY =
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=TEST)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME=PTEST)
)
)

SID_LIST_LISTENER_STANDBY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TEST)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1 )
(SID_NAME = STEST)
)
)

LISTENER_PRIMARY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LINUX-ORA10-DATAGUARD-PRIMARY)(PORT = 1521))
)
)

LISTENER_STANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LINUX-ORA10-DATAGUARD-PRIMARY)(PORT = 1522))
)
)

B) Configurer les $ORACLE_HOME/network/admin/tnsnames.ora

STEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LINUX-ORA10-DATAGUARD-PRIMARY)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =STEST)
)
)

PTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LINUX-ORA10-DATAGUARD-PRIMARY)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =PTEST)
)
)

C) Redémarrer les listeners des deux bases.

$ORACLE_HOME/bin/lsnrctl start LISTENER_PRIMARY
$ORACLE_HOME/bin/lsnrctl start LISTENER_STANDBY

5. Copie du fichier de mot de passe ou génération avec $ORACLE_HOME/bin/orapwd

6. Faire un startup mount de la base standby :

SQL> startup mount

7. Démarrer le recovery sur la base standby

A l’aide de la commande suivante :
SQL> alter database recover managed standby database disconnect from session;

• Dès qu’un archive log est transmis par la base primaire, la base standby commence à effectuer le recovery.
• L’option “disconnect from session” permet de garder la main sur la session SQL dans laquelle la commande a été exécutée, le process est alors lancé en background.

8.  Vérifier le transport des journaux

En forçant la création d’un archive log sur la base primaire :
SQL> alter system archive log current ;
L’archive log est alors transmis vers le répertoire d’archive de la base standby, et cette dernière fait automatiquement un recovery à partir du log transmis.

9. Vérifier que le data Guard fonctionne

en exécutant la requête suivante sur le primary et sur la standby:
SQL> select max(sequence#) from v$log_history;

Autres articles à découvrir sur le blog :

• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Installation Oracle 64 bits sur Red Hat 5 (Thierry GASCARD) [Oracle]
• Direct i/o, dsync on/off, raw device (David BAFFALEUF) [Sybase]
• Planification dans Adaptive Server (David BAFFALEUF) [Sybase]

1.  Configuration système

1.1 Pré-requis matériel :

-  5 Go pour les binaires Oracle en RAID 10
-  400 Mo sur /tmp
-  disques pour ASM

1.2 Noyau linux utilisé

RH 5 Entreprise 64 Bits Update 4

1.3 Pré-requis noyau

SELinux désactivé
X installé et configuré
Rpms suivants installés en plus des Rpms par défaut (version minimum) :
binutils-2.17.50.0.6-12.el5  (x86_64)
compat-libstdc++-33-3.2.3-61  (x86_64)
compat-libstdc++-33-3.2.3-61  (i386)
elfutils-libelf-0.137-3.el5  (x86_64)
elfutils-libelf-0.137-3.el5  (i386)
elfutils-libelf-devel-0.137-3.el5  (i386)
elfutils-libelf-devel-0.137-3.el5  (x86_64)
elfutils-libelf-devel-static-0.137-3.el5  (i386)
elfutils-libelf-devel-static-0.137-3.el5  (x86_64)
gcc-4.1.2-46.el5  (x86_64)
gcc-c++-4.1.2-46.el5  (x86_64)
glibc-2.5-42  (i686)
glibc-2.5-42  (x86_64)
glibc-common-2.5-42  (x86_64)
glibc-devel-2.5-42  (x86_64)
glibc-devel-2.5-42  (i386)
glibc-headers-2.5-42  (x86_64)
ksh-20080202-14.el5  (x86_64)
libaio-0.3.106-3.2  (x86_64)
libaio-0.3.106-3.2  (i386)
libaio-devel-0.3.106-3.2  (i386)
libaio-devel-0.3.106-3.2  (x86_64)
libgcc-4.1.2-46.el5  (x86_64)
libgcc-4.1.2-46.el5  (i386)
libstdc++-4.1.2-46.el5  (x86_64)
libstdc++-4.1.2-46.el5  (i386)
libstdc++-devel-4.1.2-46.el5  (x86_64)
libstdc++-devel-4.1.2-46.el5  (i386)
make-3.81-3.el5  (x86_64)
sysstat-7.0.2-3.el5  (x86_64)
unixODBC-2.2.11-7.1  (x86_64)
unixODBC-2.2.11-7.1  (i386)
unixODBC-devel-2.2.11-7.1  (x86_64)
unixODBC-devel-2.2.11-7.1  (i386)

La commande suivante nous donne des informations sur les Rpms nécessaires :
rpm -q –qf ‘%{NAME}-%{VERSION}-%{RELEASE}  (%{ARCH})\n’ \
binutils compat-libstdc++-33-3.2.* elfutils-libelf elfutils-libelf-devel \
elfutils-libelf-devel-static gcc gcc-c++ glibc glibc-common glibc-devel glibc-headers \
ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel

Rem : attention à la présence de rpms 32 bits : utilisation possible de l’option force : rpm -ivh –force ..

1.4 Configuration des limites systèmes

[root@]# cat >> /etc/security/limits.conf <<EOF
grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536
EOF

[root@]# cat >> /etc/pam.d/login <<EOF
session    required     pam_limits.so
EOF

[root@]# cat >> /etc/profile <<EOF
if [ \$USER = "oracle" ] || [ \$USER = "grid" ]; then
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
ulimit -n 25165824
else
ulimit -u 16384 -n 65536 -l 25165824
fi
umask 022
fi
EOF

1.5 Configuration du serveur pour oracle

[root@]# cat >> /etc/sysctl.conf <<EOF
kernel.shmmax=25769803776 — soit 24 Go
kernel.shmall=6291456 — avec taille de 4ko soit 24 Go
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048576
fs.aio-max-nr=1048576
EOF

2.   Préparation système

2.1 Création groupe oinstall,asmadmin,asmdba,asmoper

[root@]# groupadd -g 1000 oinstall
[root@]# groupadd -g 1200 asmadmin
[root@]# groupadd -g 1201 asmdba
[root@]# groupadd -g 1202 asmoper

2.2   Création utilisateur grid

[root@]# useradd -m -u 1100 -g oinstall -G asmadmin,asmdba,asmoper -d /home/grid -s /bin/bash -c « Grid Infrastructure Owner » grid
[root@]# id grid
[root@]# passwd grid

2.3 Vérification de l’existence de l’utilisateur nobody

[root@]# id nobody

2.4    Création des répertoires

[root@]# mkdir -p /capdata/app/grid
[root@]# mkdir -p /capdata/app/11.2.0/grid
[root@]# chown -R grid:oinstall /capdata
[root@]# chmod -R 775 /capdata

2.4 Mise à jour du profile de l’utilisateur grid

export ORACLE_SID=+ASM
export ORACLE_BASE=/capdata/app/grid
export ORACLE_HOME=/capadata/app/11.2.0/grid
export ORA_NLS11=$ORACLE_HOME/nls/data
export PATH=${PATH}:$HOME/bin:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/oracm/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
export TEMP=/tmp
export TMPDIR=/tmp
umask 022

3. Installation et configuration de la librairie ASM
3.1   Téléchargement

En fonction du noyau linux (uname -r), télécharger les rpms ASM sur le site Oracle d’asm
- ASMLib Kernel Driver
- Userspace Library
- Driver Support Files

3.2   Installation

[root@]#rpm -Uvh oracleasm*

3.2   Configuration

[root@]# /usr/sbin/oracleasm configure -i
..
Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done

puis chargement
[root@]# /usr/sbin/oracleasm init

3.2   Création des disques ASM
a – partitionnement des LUNs EMC

J’effectue un alignement des partitions en enlevant les 256 premiers secteurs de disques soit 128 Ko

[root@]# /sbin/parted /dev/emcpowera
mklabel msdos
unit s
mkpart primary 256 -1s
print
quit

[root@]# /sbin/parted /dev/emcpowerb
mklabel msdos
unit s
mkpart primary 256 -1s
print
quit

b- création des disques ASM

[root@]# /usr/sbin/oracleasm createdisk DATA_DISK1 /dev/emcpowera1
[root@]# /usr/sbin/oracleasm createdisk BACK_DISK1 /dev/emcpowerb1

4. Installation d’Oracle Grid Infrasture 11gR2

[grid@]# ./runInstaller &

Ordre d’installation
1. Installation Option : « Install and Configure for Standalone Server »
2. Product Languages : « english, french »
3. Create ASM Disk Group : « DATA,BACK »
4. ASM passwaord  : «same password»
5. Operating system Group : « asmdba,asmoper,asmadmin »
6. Installation location :
ORACLE_BASE : /capdata/app/grid
ORACLE_HOME : /capadata/app/11.2.0/grid
7. Prerequisite checks :
Génération d’un script fixit.sh pour fixer d’événtuel problème
8. Summary
9. Setup
Lancement de deux scripts sous root
[root@]# /capdata/app/oraInventory/orainstRoot.sh
[root@]# /capdata/app/11.2.0/grid/root.sh
10.Finish

Et voilà pour la première partie….Autres articles à découvrir sur le blog :

• Installation Oracle 64 bits sur Red Hat 5 (Thierry GASCARD) [Oracle]
• Se connecter à SQL Server à travers Oracle, quelle drôle d’idée ? (Thierry GASCARD) [Oracle]
• Création et utilisation d’OCFS2 (Thierry GASCARD) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Utiliser ASMCMD (Thierry GASCARD) [Oracle]

SQLDIAG est un utilitaire en mode ligne de commandes qui est livré avec SQL Server depuis plus de 10 ans maintenant. Il est bien caché, peu de gens l’utilisent mais il peut être d’une puissance redoutable. Il est capable de lancer en même temps:

• Une collecte perfmon.
• Une trace type SQL Trace.
• Une collecte des journaux d’évènements.
• Un inventaire complet  de la machine basé sur msinfo32.
• Un inventaire complet de l’instance.

Il permettra même de corréler des traces de compteurs perfmon et le traçage de sessions SQL dans Profiler. Il se base sur un fichier de configuration au format XML. Il en existe un par défaut (SQLDiag.xml) qui se trouve sous ~Tools\Binn, mais il est très exhaustif  et trace tous les compteurs et évènements disponibles. L’inconvénient de l’utiliser est qu’il créé des traces pénalisantes et très encombrantes, plusieurs dizaines de Gb de fichiers textes, blg, trc, etc… Donc on a intérêt à se créer son petit fichier XML pour ne capturer que ce dont on aura besoin.

SQLDiag.xml

Dans ce premier épisode, nous allons donc parler de la structure de ce fichier XML. Elle ressemble vu d’en haut à ceci:

<Collection>
<Machines>
        <Machine name=".">
                <MachineCollectors>
                        <EventlogCollector />
                        <PerfmonCollector />
                </MachineCollectors>
                <Instances>
                       <Instance>
                              <Collectors>
                                    <SqldiagCollector />
                                    <BlockingCollector />
                                    <ProfilerCollector />
                                    <CustomDiagnostics />
                             </Collectors>
                     </Instance>
              </Instances>
      </Machine>
</Machines>
</Collection>

Chaque balise %Collector délimite la zone de paramétrage de chaque collecteur de trace. Pour activer / désactiver un collecteur, passer son attribut enabled à true ou false:

• EventlogCollector pour collecter les journaux d’évènements.
  
• PerfmonCollector pour lancer la trace perfmon.
• SQLdiagCollector pour récupérer toute la configuration de l’instance: paramètres, contenu de tous les errorlogs, connexions, verrous, liste des bases et options, xp_msver, etc…
  
• BlockingCollector pour l’évènement BlockedProcessReport de SQL Trace, rapporte les processus bloqués si le paramètre ‘blocked process threshold (s)‘ est configuré.
• ProfilerCollector pour la trace SQL Trace complète avec les évènements que l’on aura indiqué.
  
• CustomDiagnostics pour lancer des exécutables supplémentaires, en l’occurence msinfo32 dans notre cas, et récupérer la configuration complète de la machine.
  

PerfmonCollector

C’est le collecteur de compteurs perfmon. Ses éléments fils sont <PerfmonObject /> qui désigne la famille de compteurs comme « PhysicalDisk(*) » par exemple, et <PerfmonCounter /> qui désigne le compteur associé comme « \Avg. Disk sec/Read« . On aura besoin d’activer chaque compteur en passant son attribut enabled à true. Une zone PerfmonCollector peut ressembler à ceci, avec le nom des compteurs en vert:

<PerfmonCollector enabled="true" pollinginterval="30" maxfilesize="256">
    <PerfmonCounters>
        <PerfmonObject name="\PhysicalDisk(*)" enabled="true">
             <PerfmonCounter name="\Avg. Disk sec/Read" enabled="true" />
             <PerfmonCounter name="\Avg. Disk sec/Write" enabled="true" />
             <PerfmonCounter name="\Disk Read Bytes/sec" enabled="true" />
             <PerfmonCounter name="\Disk Write Bytes/sec" enabled="true" />
             <PerfmonCounter name="\Avg. Disk Bytes/Transfer" enabled="true" />
        </PerfmonObject>
        <PerfmonObject name="\Processor(*)" enabled="false">
            <PerfmonCounter name="\% Processor Time" enabled="true" />
            <PerfmonCounter name="\% User Time" enabled="true" />
            <PerfmonCounter name="\% Privileged Time" enabled="true" />
        </PerfmonObject>
    </PerfmonCounters>
</PerfmonCollector>

L’attribut pollinginterval indique la fréquence d’échantillonnage des compteurs. Le fichier de sortie de ce collecteur sera un fichier BLG intitulé tout simplement SQLDIAG.blg. Il pourra être corrélé plus tard avec la trace qui sera générée par le collecteur ProfilerCollector.

SQLdiagCollector

C’est le collecteur de données de l’instance. Il n’est pas paramétrable est composé d’une seule ligne:

<SqldiagCollector enabled="true" startup="false" shutdown="true" /> 

On l’activera donc en passant son attribut enabled à true là encore. Les paramètres startup et shutdown permettent d’indiquer si la collecte se fait au démarrage ou à l’arrêt de la trace SQLDIAG.

BlockingCollector:

C’est le collecteur des processus bloqués en attente de libération de ressources (verrous). Lui aussi n’est constitué  que d’un seul élément:

<BlockingCollector enabled="true" pollinginterval="5" maxfilesize="350" /> 

L’attribut pollinginterval désigne la fréquence d’échantillonnage  de l’évènement, et maxfilesize la taille maximale du fichier en sortie. Ce collecteur se base sur le paramètre d’instance ‘blocked process threshold‘ qui définit la durée en secondes au delà de laquelle on considère que le blocage est anormalement long. Il faudra définir ce paramètre car sa valeur par défaut est à zéro, ce qui signifie qu’il n’est pas activé. Par exemple pour mettre le seuil à 20 secondes:

sp_configure 'blocked process threshold', 20
go
reconfigure
go

Ensuite, le collecteur va créer une trace SQL Trace avec le seul évènement ‘Blocked Process Report‘, et capturer les sessions qui auront été bloquées pendant plus de 20 secondes. Le fichier de sortie de ce collecteur est un fichier trc classique nommé <INSTANCE>_SQLDIAG_sp_trace_blk.

ProfilerCollector:

Ensuite, ProfilerCollector est le collecteur de trace SQL Trace classique. Comme pour PerfmonCollector, il va disposer d’éléments fils comme <EventType /> et <Event /> dans lesquels on va bien entendu retrouver nos classes d’évènements:

<ProfilerCollector enabled="true" template="_GeneralPerformance100.xml" pollinginterval="5" maxfilesize="350">
     <Events>
         <EventType name="Stored Procedures">
             <Event id="10" name="RPC:Completed" enabled="true"/>
             <Event id="11" name="RPC:Starting" enabled="true" />
             <Event id="43" name="SP:Completed" enabled="true" />
             <Event id="42" name="SP:Starting" enabled="true" />
             <Event id="45" name="SP:StmtCompleted" enabled="true" />
             <Event id="44" name="SP:StmtStarting" enabled="true" />
        </EventType>
       <EventType name="TSQL">
             <Event id="40" name="SQL:StmtStarting" enabled="true"/>
             <Event id="41" name="SQL:StmtCompleted" enabled="true" />
       </EventType>
     </Events>
 </ProfilerCollector>

Le fichier de sortie de ce collecteur sera aussi un fichier trace classique nommé <INSTANCE>_SQLDIAG_sp_trace.

CustomDiagnostics

Enfin, CustomDiagnostics permet de pousser encore plus loin la personnalisation du rapport, en nous permettant d’utiliser nos propres outils de collecte. L’outil qui est utilisé par défaut est msinfo32 qui est utilisé pour afficher de manière graphique (Démarrer -> Exécuter -> msinfo32) ou en ligne de commande toute la configuration de la machine hôte:

<CustomDiagnostics>
 <CustomGroup name="msinfo" enabled="true" />
 <CustomTask enabled="true" groupname="MsInfo" taskname="Get MSINFO32" type="Utility" point="Startup" wait="OnlyOnShutdown" cmd="start /B /WAIT MSInfo32.exe /computer %server% /report
         &quot;%output_path%%server%_MSINFO32.TXT&quot; /categories +SystemSummary+ResourcesConflicts+ResourcesIRQS+ComponentsNetwork+ComponentsStorage+ComponentsProblemDevices+SWEnvEnvVars+
         SWEnvNetConn+SWEnvServices+SWEnvProgramGroup+SWEnvStartupPrograms" />
 <CustomTask enabled="true" groupname="MsInfo" taskname="Get default traces" type="Copy_File" point="Startup" wait="OnlyOnShutdown" cmd="&quot;%sspath%log\log*.trc&quot; &quot;%output_path%&quot;" />
 <CustomTask enabled="true" groupname="MsInfo" taskname="Get SQLDumper log" type="Copy_File" point="Startup" wait="OnlyOnShutdown" cmd="&quot;%sspath%log\SQLDUMPER_ERRORLOG.log&quot;
          &quot;%output_path%%server%_%instance%_SQLDUMPER_ERRORLOG.log&quot;" />
 </CustomDiagnostics>

Mais en plus de récupérer le contenu d’une trace msinfo32, il peut également récupérer le contenu des traces SQL Server par défaut *, et les stacktraces générées par SQL Server lorsqu’il rencontre une erreur fatale sur un module. Il va copier le contenu de ces traces dans le répertoire passé à l’exécution de SQLDIAG.

Exécution d’une trace

Une fois le fichier XML défini, lancer une trace devient un jeu d’enfant:

$ sqldiag /I MyXMLfile.xml /O e:/SQLDIAG/TRACE1
2010/02/04 18:58:10.28 SQLDIAG Collector version
2010/02/04 18:58:10.29 SQLDIAG

IMPORTANT:  Please wait until you see "Collection started" before attempting to reproduce your issue

2010/02/04 18:58:10.29 SQLDIAG Output path: e:\SQLDIAG\TRACE1\
2010/02/04 18:58:10.33 SQLDIAG Collecting from 1 logical machine(s)
2010/02/04 18:58:10.34 SQLDIAG Invalid node fetched. (null)
2010/02/04 18:58:10.34 SQLDIAG Invalid node fetched. (null)
2010/02/04 18:58:10.34 MS2K8-WIN2008-1\* SQL Server version: 10
2010/02/04 18:58:10.35 MS2K8-WIN2008-1\* Machine name: MS2K8-WIN2008-1 (this machine)
2010/02/04 18:58:10.35 MS2K8-WIN2008-1\* Target machine is not a cluster
2010/02/04 18:58:10.35 MS2K8-WIN2008-1\* Instance: (Default) (32-bit)
2010/02/04 18:58:12.40 SQLDIAG Initialization starting...
2010/02/04 18:58:13.98 MS2K8-WIN2008-1\* Starting Profiler trace
2010/02/04 18:58:14.94 MS2K8-WIN2008-1\* Starting Blocking script
2010/02/04 18:58:16.88 MS2K8-WIN2008-1\* MsInfo: Get MSINFO32
2010/02/04 18:58:17.26 MS2K8-WIN2008-1\* MsInfo: Get default traces
2010/02/04 18:58:17.47 MS2K8-WIN2008-1\* MsInfo: Get SQLDumper log
2010/02/04 18:58:17.71 MS2K8-WIN2008-1\* Adding Perfmon counters...
2010/02/04 18:58:20.73 MS2K8-WIN2008-1\* Starting Perfmon
2010/02/04 18:58:20.73 SQLDIAG Initialization complete
2010/02/04 18:58:21.53 MS2K8-WIN2008-1\* Perfmon started
2010/02/04 18:58:21.58 MS2K8-WIN2008-1\* Collecting diagnostic data

2010/02/04 18:58:22.28 SQLDIAG Collection started.  Press Ctrl+C to stop.

La trace est lancée, un simple Ctrl-C suffira à la stopper. Les commutateurs /I et /O permettent d’indiquer le fichier de configuration XML choisi et le répertoire où va se loger le contenu de la trace complète.

Il existe aussi des commutateurs pour indiquer à SQLDIAG de programmer son exécution à des heures déterminées. Il est même possible de l’enregistrer en tant que service dans windows.

Dans le prochain post sur SQLDIAG, on verra comment dépouiller les fichiers de résultat.

A+ David B.

(avec la collaboration de Martial LUCAS. )

* Depuis la version SQL Server 2005, un paramètre instance ‘default trace enabled’ avec une valeur de 1 (valeur par défaut) génère une trace systématiquement au démarrage de l’instance. Cette trace contient les classes évènements Database, Errors and Warnings, Full text, Objects, Performance, Server et Security Audit. Chaque trace vient se loger dans le répertoire des ERRORLOG par défaut.Autres articles à découvrir sur le blog :

• Utiliser ASMCMD (Thierry GASCARD) [Oracle]
• Modes de récupération et journal de transactions, épisode 1 (David BAFFALEUF) [SqlServer]

On va créer une base de test pour démontrer ce point:

create database test_statistics

On va vérifier si les options de création et de mise à jour auto sont bien activées

select databasepropertyex('test_statistics','IsAutoCreateStatistics')
select databasepropertyex('test_statistics','IsAutoUpdateStatistics')

1
1

On créé un peu de volumétrie et on ajoute les indexes clusterisés

use test_statistics
create table STATS1(a numeric identity, b varchar(400))
create table STATS2(a numeric identity, b varchar(400))
insert into STATS1 values (replicate('a',400))
go 1000
insert into STATS2 values (replicate('a',400))
go 1000
alter table STATS1 add constraint PK_STATS1 primary key (a)
alter table STATS2 add constraint PK_STATS2 primary key (a)

On fixe ensuite la date de dernière mise à jour des stats pour les deux tables avec STATS_DATE()

select object_name(object_id), stats_date(object_id,stats_id) FROM sys.stats S
inner join sys.tables T on T.object_id = S.object_id

STATS1    2010-01-28 17:41:45.617
STATS2    2010-01-28 17:41:45.963

A partir de là, on va forcer le calcul des stats en NORECOMPUTE sur STATS1 seulement et revérifier les dates de stats. NORECOMPUTE aura pour effet de dire à SQL Server de ne plus compiler en auto sur cette table.

update statistics dbo.STATS1 PK_STATS1 WITH NORECOMPUTE
 Command(s) completed successfully.

select object_name(S.object_id), stats_date(S.object_id,S.stats_id) FROM sys.stats S
inner join sys.tables T on T.object_id = S.object_id
STATS1    2010-01-28 17:42:14.350 
STATS2    2010-01-28 17:41:45.963

La date pour STATS1 a bien été modifiée. OK
On va générer quelques perturbations identiques aux niveau des deux tables comme ajouter une colonne, supprimer des lignes, etc de manière à déclencher statman, l’utilitaire de recompilation auto des stats… On le verra passer dans une session Profiler:

Si  on revérifie maintenant les dates de mise à jour:

select object_name(S.object_id), stats_date(S.object_id,S.stats_id) FROM sys.stats S
inner join sys.tables T on T.object_id = S.object_id
STATS1    2010-01-28 17:42:14.350
STATS2    2010-01-28 17:47:59.630

Cette fois seule STATS2 a été modifiée. Il faudra donc que je prévoie de mettre à jour manuellement STATS1 à intervalles réguliers, 1 fois par jour la nuit par exemple.

Conclusion pour retrouver sur une base les tables qui sont passées en norecompute au niveau des stats:

select T.name 'tablename', S.name 'statsname', S.no_recompute
from sys.tables T
INNER JOIN sys.stats S on S.object_id = T.object_id
WHERE S.no_recompute = 1

tablename             statsname                        no_recompute
-------------------  -------------------------------  ------------------------------
STATS1                PK_STATS1                       1

A+ [David B.]

Autres articles à découvrir sur le blog :

• MySQL et les tables temporaires internes (Cédric PEINTRE) [MySQL]
• Scruter les journaux d’évènements Windows avec LogParser (David BAFFALEUF) [Service hétérogèneSqlServer]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Retrouver une transaction en échec (David BAFFALEUF) [SqlServer]

Le détail des cas de création de tables temporaires est disponible dans la documentation MySQL :

• http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

Ces tables temporaires sont créées en mémoire par défaut, dans la limite de la taille définie par le paramètre tmp_table_size [taille par défaut dépendante de l'OS]. Au delà de cette limite, la table temporaire est créée sur disque. Attention toutefois, si la valeur du paramètre max_heap_table_size [16M par défaut] est inférieure à celle de tmp_table_size, alors les tables temporaires en mémoire seront limitées à max_heap_table_size. Pour simplifier les choses, positionnez les deux paramètres à la même valeur.

Il est évidemment plus performant de provoquer le maximum de création en mémoire en positionnant la valeur du paramètre tmp_table_size à une taille relativement importante (16M à 32M).
Attention toutefois au débordement mémoire, en effet, cette taille peut potentiellement être consommée par chaque thread connecté !

La colonne Extra de la commande EXPLAIN pour une requête de type SELECT permet d’obtenir une information sur la création d’une éventuelle table temporaire.
Dans ce cas (utilisation d’un GROUP BY), une table temporaire sera créée :

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_evenement
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 14988
Extra: Using temporary; Using filesort

En revanche, cette commande ne permet pas de savoir si la table temporaire est créée sur disque ou en mémoire, pour cela, il faudra s’appuyer sur les compteurs MySQL évoqués dans la suite de l’article.

Le graphe suivant présente un état des tables temporaires créées en mémoire et sur disque pour notre instance de production  :

Tables temporaires MySQL

Les compteurs MySQL utilisés pour relever ces valeurs sont Created_tmp_table et Created_tmp_disk_tables. Il n’est malheureusement pas possible de connaitre la taille consommée en mémoire ou sur disque par ces tables temporaires.

Ici, la quasi totalité des tables temporaires est créée sur disque. La taille maximum d’une table temporaire en mémoire est pourtant positionnée à une valeur relativement importante (48M).

Le fait d’augmenter la valeur du tmp_table_size ne changera rien dans ce cas ! Explications :

MySQL utilise le moteur de stockage MEMORY pour la création des tables temporaires internes. Ce moteur se comporte quasiment comme MyISAM, cependant, une  contrainte  de ce moteur vient perturber le fonctionnement des tables temporaires internes : Il n’est pas possible d’utiliser des colonnes de type BLOB ou TEXT pour les tables MEMORY.

De ce fait, si MySQL doit créer une table temporaire pour un ordre SELECT contenant ces types de colonne, elle sera automatiquement créée sur disque.

Deux possibilités s’offrent à vous dans ce cas :

• Limiter l’utilisation des colonnes de type BLOB ou TEXT
• Monter un système de fichier mémoire pour le stockage des tables temporaires interne MySQL

[ Cédric P ]Autres articles à découvrir sur le blog :

• Retrouver les tables dont les stats ne sont plus compilées en automatique (David BAFFALEUF) [SqlServer]
• XtraDB sauvegarde votre cache, et ça marche ! (Cédric PEINTRE) [MySQL]
• Attention : publication transactionnelle et indexes non clusters (David BAFFALEUF) [SqlServer]
• Scruter les journaux d’évènements Windows avec LogParser (David BAFFALEUF) [Service hétérogèneSqlServer]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]

Une fois le matériel commandé, livré, déballé, racké, etc…, il va falloir installer Windows et SQL Server. Là encore il va falloir regarder certains détails de près :

Paramétrage de Windows Server:

Préparation des disques:

On l’a dit dans l’épisode 1, le disque c’est le nerf de la guerre. Je vais donc apporter un soin particulier à la configuration de mes axes.

Pour placer sur disque une base de données transactionnelle, on essaie de considérer deux règles de base:

• Toujours séparer physiquement le fichier de données du journal de transactions: pour deux raisons. La première pour la sécurité des données: si je perds mon disque de données, j’ai besoin d’avoir accès au journal pour récupérer les dernières transactions validées mais pas encore prises par le backup log. La seconde pour des raisons de performances: le critère qui pèse le plus dans le temps de service d’un disque, c’est le déplacement de la tête au dessus de la bonne piste (seek time). Les accès aux données sont de nature aléatoire et donc augmentent cette latence de recherche. En revanche, les accès sur le journal sont séquentiels, donc limitent le seek time car une fois la tête positionnée sur la bonne piste / bon secteur, elle glisse tranquillement pour lire (recovery) ou écrire (commit / checkpoints) sur les secteurs adjacents. Mélanger les deux, ça plombe les accès au journal, et sur une instance OLTP, on sait que l’accès en écriture sur le journal est critique car il conditionne le temps de réponse du commit.

• Toujours séparer physiquement les données des sauvegardes: pour une raison évidente de sécurité des données, mais il faut y penser avant d’avoir un problème. Et par défaut jusqu’en SQL Server 2005, tout allait dans ~MSSQL\Data et ~MSSQL\Backup.

Séparer physiquement, ça veut dire PAS sur les mêmes disques. Ce n’est pas parce que j’ai trois lecteurs E:\, F:\ et G:\ que j’ai trois disques, il faudra le vérifier avec la console de gestion des volumes (diskmgmt.msc) et avec votre ami l’administrateur système en charge du stockage pour vérifier s’il ne vous a pas refilé 3 LUNs découpées dans le même diskgroup. Si je reprends mon Proliant DL 580 G5  avec ses 16 slots:

Je me paye même le luxe d’avoir un RAID10 pour placer mes filegroups d’indexes, comme sur Oracle. Cela dit, la bagarre va être rude parce que tout le monde pense que l’espace disque est gâché, surtout pour ce qui concerne les journaux de transactions, où on va utiliser peut être 20% de l’espace alloué. Mais il faut le voir comme un investissement. Vous aurez des temps de service garantis de moins de 4 ms en écriture sur le commit, et le nombre de transactions par seconde va en bénéficier. C’est une réalité, on voit des exemples de ce type régulièrement chez nos clients. 

Une fois les axes définis et montrés à la machine, la première chose va être de partitionner le disque en alignant sur le 128ième secteur (=64K), sous diskpart:

diskpart> create partition primary align=64

Globalement, il faut savoir que jusqu’en version 2003 incluse, Windows continue de préréserver les 63 premiers secteurs d’un disque pour y stocker son MBR (master boot record). Lorsque l’on va formater ensuite le file system en 64K, le premier offset va commencer réellement sur le 63ième secteur et décaler les clusters NTFS de 512 octets (1 secteur). il faut que les écritures de SQL Server (64K) soient alignées avec le début de la partition. Pour davantage d’infos, voir l’article de David BARBARIN sur le sujet.

En deux étapes:

• Partitionner le disque en alignant sur le 128ième secteur.
• Puis créer un FS en NTFS avec un cluster size de 64K pour les disques de données et de journaux.

Il faudra aussi aller vérifier que la politique d’écriture est bien en write-through au niveau du cache du contrôleur disque. Cet exemple sous OpenManage:

Paramètres noyau de Windows pour SQL Server:

Pas grand chose à faire dans 99% des cas. Il faut valider que le bail (quantum en anglais) par thread est bien de type ‘long’ dans les performances avancées de la machine (Poste de Travail -> Properties -> Advanced -> Performance -> Advanced):

Background services => chaque thread a le droit de s’exécuter pendant 12 ticks d’horloge avant d’être interrompu par le Dispatcher.  Par défaut sur la gamme Server, le radiobutton doit être sur background services (quantum = 12 ticks d’horloge) et sur la gamme workstation, sur Programs (quantum = 2 ticks d’horloge). En général pour tous les SGBD, le quantum doit être plus long pour limiter le nombre de changements de contexte opérés par le système d’exploitation. Pour plus de détails, voir cet article.

Il faudra aussi aller vérifier que les ressources allouées seront en priorité sur les programmes générant beaucoup d’IOs comme SQL Server (Connexions réseau, clic-droit sur l’interface réseau -> Properties-> File and Printer Sharing (et oui !) -> Properties):

Maximize throughput for network applications: sous entendu on n’est pas là pour faire du partage de fichiers.

Et éviter l’utilisation des antivirus autant que possible.

Compte de service pour SQL Server et l’agent:

Le compte de service est le compte déclaré au niveau de la machine ou du domaine pour supporter l’exécution de SQL Server et l’ensemble de ses interactions avec le système: poster des IOs, atteindre des machines sur le réseau, écrire dans un fichier de sauvegarde, etc… Toutes ces actions seront donc exécutées sous le contexte de droits du compte de service.  Dans la plupart des cas, les SQL  Server sont démarrés sous le contrôle des comptes systèmes locaux (LOCAL SYSTEM) avec lesquels sont lancés la plupart des modules de windows exécutés en mode user (smss.exe, crss.exe, lsass.exe, winlogon.exe, etc…). Et négligence du  DBA, on trouve aussi des SQL Server démarrés sous le compte Administrateur local voire de domaine, ce qui du point de vue sécurité  n’est pas vraiment l’idéal.

Et bien depuis la version SQL Server 2005, il n’y a plus d’excuse: on créé simplement des comptes utilisateurs locaux ou de domaine sans privilège *** pour SQL Server et SQL Agent (je ne parle pas de SQL Browser puisque du point de vue sécurité, on ne doit pas l’utiliser **), on les passe ensuite à l’assistant d’installation qui créé des groupes avec les droits appropriés, et y intègre les comptes qui héritent du coup des droits strictement nécessaires.

Éléments à définir avant l’installation de SQL Server :

Avant de lancer le setup,il faut réfléchir à quelques éléments caractéristiques de l’instance que l’on souhaite installer:

Instance locale / instance nommée:

L’instance locale est l’instance que l’on créé par défaut sur une machine. Elle prend par défaut le nom de la machine et le port 1433 attribué par l’IANA. On ne peut donc en créer qu’une seule par machine. Ma chaîne de connexion ressemblera à :

sqlcmd -Udba_exploit -w1000 -S PIXIES 

Où PIXIES est le nom de ma machine, et donc le nom de mon instance locale par la même occasion. Maintenant, lorsqu’on souhaite en installer plus d’une par machine, on pourra utiliser les instances nommées, dont le nom sera constitué d’un préfixe (nom de la machine hôte) et d’un suffixe (nom de l’instance à choisir) séparés par un backslash (‘\’). Dans ce cas, ma chaîne de connexion ressemblera à :

sqlcmd -Udba_exploit -w1000 -S PIXIES\SQL1 

Où SQL1 sera le nom de nom instance nommée. Autre différence majeure, le port de démarrage sera défini dynamiquement. Ce qui signifie qu’il peut changer entre deux redémarrages, donc problème de firewall à l’horizon. Pour l’éviter, il faudra fixer le port de démarrage de l’instance dans les propriétés TCP, et redémarrer SQL Server. **

Collation:

La collation va définir la manière de coder les caractères dans ma base de données. Là aussi le sujet est vaste et largement débattu sur le net. Il faut simplement réfléchir aux types de champs texte que mon application va devoir stocker et choisir le codepage approprié. Il faut essayer de prendre le codepage le plus large possible. Lors de l’installation en SQL Server 2005, deux types de collations sont proposées: les collations windows et les collations SQL Server. En fait la seconde est incluse pour compatibilité vers les versions précédentes, et ne doit donc être utilisée que pour assurer la compatibilité lors d’une migration. Il vaut mieux essayer de partir avec une collation Windows dès le départ, elles sont plus riches et plus précises, notamment lorsqu’on utilise à la fois des colonnes UNICODE et NON-UNICODE, car la collation windows sait convertir du NON-UNICODE à la volée.  Changer la collation à tous niveaux n’est pas une opération de routine: au niveau de l’instance, il faudra la reconstruire, au niveau de la base c’est plus épique encore.

Mode d’authentification:

Deux solutions:

• Le mode intégré: l’authentification se fait par une autorité telle qu’un contrôleur de domaine ou la machine locale. Avantage, SQL Server ne stocke pas de mot de passe, côté sécurité c’est la meilleure option. Inconvénient, lorsqu’on souhaite atteindre depuis son application cliente une instance qui n’est pas dans le même domaine, ou simplement une machine en workgroup, l’authentification intégrée ne fonctionnera pas.
• Le mode mixte: donc on sera obligé de permettre aussi de créer et d’utiliser  des comptes et des mots de passe dans SQL Server. En mode mixte, on se connecte soit avec un compte windows, soit avec un compte SQL Server.

Les bonnes pratiques de sécurité préconisent de rester en mode intégré, mais ce n’est n’est bien souvent pas très pratique, et donc pas appliqué. Il vaut mieux autoriser le mode mixte mais désactiver le compte sa, et faire appliquer la politique de mots de passe de Windows.

Une fois ces étapes validées, l’installation peut commencer. Le travail de préparation est terminé.

A bientôt !

* Dans cet exemple, on choisit de faire les backups sur un partage CIFS
** Je ne parle pas de sécurité dans cet article, le sujet est vaste et fera l’objet d’un prochain post.
*** Penser à cocher le compte n’expire jamais pour les comptes de service.

[ David B. ]Autres articles à découvrir sur le blog :

• Modes de récupération et journal de transactions, épisode 2 (David BAFFALEUF) [SqlServer]
• Règles d’installation de base (épisode 1) (David BAFFALEUF) [SqlServer]

La solution http://public-yum.oracle.com et YUM.

[root@OEL5-ORA1 yum.repos.d]# cd /etc/yum.repos.d
[root@OEL5-ORA1 yum.repos.d]#wget http://public-yum.oracle.com/public-yum-el5.repo

car j’utilise OEL 5 Update 4

[root@OEL5-ORA1 ~]# cat /etc/enterprise-release
Enterprise Linux Enterprise Linux Server release 5.4 (Carthage)

Je vais modifier le fichier public-yum-el5.repo en mettant enabled=1 pour ma version el5_u4_base

vi public-yum-el5.repo

…

[el5_u4_base]
name=Enterprise Linux $releasever U4 – $basearch – base
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/4/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1

…

[root@OEL5-ORA1 yum.repos.d]# yum list oracleasm
Loaded plugins: security
Error: No matching Packages to list
ocfs2 packages are not available. Let’s install it now

[root@OEL5-ORA1 yum.repos.d]# yum install oracleasm

Loaded plugins: security
Setting up Install Process
Package oracleasm-2.6.18-164.el5-2.0.5-1.el5.i686 already installed and latest version
Nothing to do

Quel plaisir
Autres articles à découvrir sur le blog :

• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Retrouver une transaction en échec (David BAFFALEUF) [SqlServer]
• Direct i/o, dsync on/off, raw device (David BAFFALEUF) [Sybase]
• Planification dans Adaptive Server (David BAFFALEUF) [Sybase]

La collation (ou classement en français) d’une base de données indique le jeu de caractères et l’ordre de tri des données stockées dans la base de données. De nombreux articles expliquent le rôle et traitent des problèmes de collation. L’objectif de celui-ci est de présenter une méthode pour changer la collation d’une base de données.

La collation peut être fixée:

1. Au niveau de l’instance: à l’installation de SQL Server 2005.
2. Au niveau de la base:

    CREATE DATABASE ... COLLATE ou ALTER DATABASE ... COLLATE.

3. Au niveau de la colonne:

   CREATE TABLE maTable ... COL1 NVARCHAR(100) COLLATE French_CI_AS

4. Au niveau de l’expression SQL:

   SELECT A, B from T1, T2 where T1.A = T2.B collate French_CI_AS

On peut parfois se retrouver dans la situation suivante : la base de données a été créée ou restaurée dans une collation qui pose problème à l’application et il faut la modifier.

Attention, gare alors aux problèmes lorsque l’on utilise des bases de collations différentes sur une même instance : La collation de tempdb est héritée de model, qui elle-même hérite de celle de l’instance. Lors de jointures avec des tables temporaires sur des colonnes contenant du texte, des erreurs de comparaison peuvent survenir:

Msg 468, Level 16, State 9, Line 5 Cannot resolve the collation conflict between
"French_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to operation

Pour changer la collation d’une base de données, il existe plusieurs techniques :

Technique 1 :

1. Modifier la collation de la base : ALTER DATABASE … COLLATE
   Toutes les nouvelles colonnes de type caractère créées dans la base hériteront de la nouvelle collation mais pas les colonnes caractère déjà créées .
2. Modifier la collation de chaque colonne à condition quelle ne soit pas référencée dans un index, une contrainte Foreign key ou CHECK, une statistique de distribution ou une colonne calculée
   … pas évident

Technique 2 : celle que je vous propose

1. Générer le script de création de la base et de tous ses objets
2. Modifier ce script, pour créer une nouvelle base vide avec la bonne collation
3. Importer dans la nouvelle base les données de l’ancienne base à l’aide de l’assistant de Management Studio
4. Vérifier si l’application fonctionne correctement sur la nouvelle base
5. Intervertir l’ancienne base et la nouvelle
   On peut renommer une base ( commande ALTER DATABASE … MODIFY NAME). Attention de bien récupérer les comptes utilisateurs de l’ancienne base et le mapping sur les logins.

Exemple : Changer la collation de la base AdventureWorks Latin1_General_CS_AS en En French_CI_AS

Avant de commencer:

– Vérifier la collation du serveur

sp_helpsort

Server default collation
---------------------------------------------------------------------------
French, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

– Vérifier la collation de la base

select DATABASEPROPERTYEX('AdventureWorks', 'Collation')
-----------------------------------------------------------------------
Latin1_General_CS_AS

– Vérifier la collation des colonnes

SELECT B.table_name, column_name, collation_name, data_type FROM INFORMATION_SCHEMA.COLUMNS A,
INFORMATION_SCHEMA.TABLES B
WHERE A.table_name = B.table_name and collation_name is not null
ORDER BY B.table_name

table_name    column_name   collation_name             data_type
-----------------------------------------------------------------------------------
Address       AddressLine1  Latin1_General_CS_AS        nvarchar
Address       AddressLine2  Latin1_General_CS_AS        nvarchar
...

Etape 1 : Générer le script de création de la base et de tous ses objets

A Partir de Management Studio, cliquer sur « Generate Scripts »

—
Choisir la base AdventureWorks,
Cocher l’option « Script All Objects »
Next
—
Choose script Options
Modifier les options suivantes :
Include Descriptive Headers: True
Script Database Create: True
Script Object-Level Permissions : True
Script USE DATABASE : False
Script Full-Text Indexes : True
—
Next
Script to New Query Window
—
Next
—
Finish
—
Close

Etape 2 : Modifier ce script, pour créer une nouvelle base vide avec la bonne collatio

Avec l’outil Find/Replace Remplacer le mot AdventureWorks par AdventureWorks_2
(nom de la nouvelle base)

Modifier la Collation de la base de données avec la valeur voulue en ajoutant la clause collate

CREATE DATABASE
…
( NAME =
N'AdventureWorks_2_Log', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\AdventureWorks_2_Log.ldf' , SIZE = 916352KB , MAXSIZE
= UNLIMITED, FILEGROWTH = 10%)
COLLATE FRENCH_CI_AS
END

Modifier les chemins des fichiers de données en cas de besoin (pour ne pas utiliser les mêmes que la base d’origine).

=> Exécuter le script

Vérifier qu’il n’y a pas d’erreur problématique lors de la création de la base et de ses objetts.
Vérifier la collation de la nouvelle base et de ses colonnes.

Etape 3 : Importer dans la nouvelle base les données les données de l’ancienne

A l’aide SSMS depuis la base AdventureWorks_2, lancer l’assistant d’import de données :

—
Choose a Datasource: AdventureWorks
Next
—
Choose a destination : AdventureWorks_2
Next
—
Copy data from one or more tables or views
Next
—
Ne sélectionner que les tables , pas les vues
Next
—
Execute immediately
Next
Finish
—-

Les lignes de chaque table ont été ajoutées aux tables vides avec la nouvelle collation pour les données type Caractère

L.Hochberg

Autres articles à découvrir sur le blog :

• Principes d’une sauvegarde à chaud (David BAFFALEUF) [SqlServer]

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0000CD53000011DE001000000000, Command ID: 1)

Error messages:
Syntax error, permission violation, or other nonspecific error (Source: MSSQLServer,
Error number: 20025)
Get help: http://help/20025
Syntax error, permission violation, or other nonspecific error (Source: MSSQLServer,
Error number: 42000)
Get help: http://help/42000

Ce n’est même pas la peine d’essayer de voir ce qu’il y a au bout des deux liens http://help/… proposés. En gros l’agent de distribution a trouvé une erreur en essayant d’appliquer une transaction. Le but est de retrouver le code SQL qui correspond à la transaction en échec. Lancer une trace Profiler sur le distributeur ne servira pas à grand chose car la transaction en question sera noyée dans le flot de mises à jour sur les tables systèmes de la réplication. En fait la chose la plus importante dans le log d’erreur est le numéro de transaction: 0×0000CD53000011DE001000000000

Pour retrouver l’ordre correspondant sur le distributeur, utiliser sp_browsereplcmds, regarder la première ligne:

use distribution
sp_browsereplcmds @xact_seqno_start = '0x0000CD53000011DE001000000000'

0x0000CD53000012EE000E	NULL	NULL	1	30	0	0	NULL	NULL
0x00000000000000000000
{CALL sp_MSins_EMAIL MOBILE DATA ('MICHAEL.HEARSLEY#TATOINE.COM', 'J74K6L')}	1

0x0000CD5300001304000F	NULL	NULL	1	30	0	0	NULL	NULL
0x00000000000000000000
{CALL sp_MSins_EMAIL MOBILE DATA ('EDUARDO.ALFONSO#JPMM.COM', 'J91RCX')}	1

0x0000CD530000131A0018	NULL	NULL	1	30	0	0	NULL	NULL
0x00000000000000000000
{CALL sp_MSins_EMAIL MOBILE DATA ('MARK.D.SPELLMAN#ATTACH.CO.UK', 'J731FZ')} 1

0x0000CD530000133B000E	NULL	NULL	1	30	0	0	NULL	NULL
0x00000000000000000000
{CALL sp_MSins_EMAIL MOBILE DATA ('XAVIER.BARTOLDI#NRJ.PICATRIX.COM', 'J92HBF')} 1

...

Effectivement si on lance l’exécution de la proc à la main sur le subscriber:

CALL sp_MSins_EMAIL MOBILE DATA ('MICHAEL.HEARSLEY#TATOINE.COM', 'J74K6L')

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'MOBILE'.

Il y a des espaces dans le nom de la proc. L’assistant graphique de mise en place de la réplication créera la procédure avec des underscores, donc pas de souci, c’est juste pour l’exemple.

[ David B. ]Autres articles à découvrir sur le blog :

• Attention : publication transactionnelle et indexes non clusters (David BAFFALEUF) [SqlServer]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Retrouver les tables dont les stats ne sont plus compilées en automatique (David BAFFALEUF) [SqlServer]

Je vous répondrai que le problème n’est pas trivial. Tout le monde utilise les vues DMV pour récupérer les infos de la session qui occupe l’espace dans tempdb, notamment le texte SQL de la requête:
- sys.dm_db_task_space_usage: pour voir l’espace utilisé par session.
- sys.dm_exec_requests ou sys.dm_exec_query_stats + cross apply sys.dm_exec_sql_text(sql_handle): pour plus d’infos sur les sessions (stats + texte des requêtes).

Seulement voilà: les sessions dont le plan a été purgé du cache ne sont plus visibles dans les DMV sys.dm_exec… donc la jointure entre sys.dm_db_task_space_usage et sys.dm_exec… ne renverra pas d’infos pour des sessions qui utilisent pourtant bel et bien de l’espace dans tempdb.

Donc il faut avoir recours au bon vieux dbcc inputbuffer pour récupérer tout ça.

1) D’abord, on va créer les tables de recueil.

1.1 DBCCINPUTBUFFER va nous permettre de récupérer le contenu du dbcc inputbuffer par session:

CREATE TABLE tempdb.guest.DBCCINPUTBUFFER
(
EventType varchar(50),
Parameters int,
SQLText varchar(2000)
)

1.2 DBCCSHOWFILESTATS va nous permettre de récupérer l’espace total et utilisé dans tempdb:

CREATE TABLE tempdb.guest.DBCCSHOWFILESTATS
(
[fileid] [int], [filegroup] [int],
[totalextents] [int], [usedextents] [int],
[name] [varchar] (255), [filename] [varchar] (255)
)

1.3 monitorTempdb sera notre table finale d’historique.

CREATE TABLE tempdb.guest.monitorTempdb
(
curdate datetime,
spaceintempdb bigint,
spaceusedintempdb bigint,
spid int,
sqltext varchar(1000),
internal_objects_alloc_MB bigint,
internal_objects_dealloc_MB bigint,
user_objects_alloc_MB bigint,
user_objects_dealloc_MB bigint,
program_name varchar(100),
login_time datetime,
hostname varchar(20),
login_name varchar(100)
)

2) Par session récupérer les infos et le texte de la requête: on va placer les sessions consommatrices ainsi que les espaces consommés dans un curseur, puis boucler pour chaque session_id et récupérer plus d’infos (program_name, login, etc… depuis sys.dm_exec_sessions, la seule DMV qui affiche toutes les sessions actives ou non) et le dernier texte SQL généré:

use tempdb
go
declare
	@spid int,
	@sqltext varchar(1000),
	@internal_objects_alloc_page_count bigint,
	@internal_objects_dealloc_page_count bigint,
	@user_objects_alloc_page_count bigint,
	@user_objects_dealloc_page_count bigint,
	@program_name varchar(100),
	@login_time datetime,
	@hostname varchar(20),
	@login_name varchar(100),
	@SQLQUERY varchar(100),
	@spaceintempdb bigint,
	@spaceusedintempdb  bigint

declare cr_sqltext CURSOR READ_ONLY for
select session_id,internal_objects_alloc_page_count,internal_objects_dealloc_page_count,
user_objects_alloc_page_count, user_objects_dealloc_page_count
from sys.dm_db_task_space_usage where internal_objects_alloc_page_count > 12000 

open cr_sqltext
fetch next from cr_sqltext into
	@spid,
	@internal_objects_alloc_page_count,
	@internal_objects_dealloc_page_count,
	@user_objects_alloc_page_count,
	@user_objects_dealloc_page_count

while @@fetch_status = 0
begin
	select
		@program_name = program_name,
		@login_time = login_time,
		@hostname = host_name,
		@login_name = login_name
	from sys.dm_exec_sessions where session_id = @spid

	select @SQLQUERY = 'dbcc inputbuffer('+convert(char(3),@spid)+')'

	insert into tempdb.guest.DBCCINPUTBUFFER exec (@SQLQUERY)
	select @sqltext = SQLText from tempdb.guest.DBCCINPUTBUFFER 

	insert into tempdb.guest.DBCCSHOWFILESTATS exec ('DBCC showfilestats with NO_INFOMSGS')
	select
		@spaceintempdb = sum(totalextents)/16,
		@spaceusedintempdb = sum(usedextents)/16
	from tempdb.guest.DBCCSHOWFILESTATS

	delete from tempdb.guest.DBCCINPUTBUFFER
	delete from tempdb.guest.DBCCSHOWFILESTATS

	insert tempdb.guest.monitorTempdb values
	(getdate(),
	@spaceintempdb,
	@spaceusedintempdb ,
	@spid, 	@sqltext,
	@internal_objects_alloc_page_count*8192/1048576 ,
	@internal_objects_dealloc_page_count*8192/1048576 ,
	@user_objects_alloc_page_count*8192/1048576 ,
	@user_objects_dealloc_page_count*8192/1048576 ,
	@program_name ,
	@login_time ,
	@hostname ,
	@login_name)

fetch next from cr_sqltext into
	@spid,
	@internal_objects_alloc_page_count,
	@internal_objects_dealloc_page_count,
	@user_objects_alloc_page_count,
	@user_objects_dealloc_page_count

end
close cr_sqltext
deallocate cr_sqltext
go

Cette dernière partie peut être mise dans un job planifié toutes les minutes par exemple. C’est ainsi que j’ai découvert que sp_MSget_repl_commands et sp_MSdistribution_cleanup (procs de répli) peuvent être extrêmement consommatrices lorsque la file des requêtes à répliquer est très importante.

A+ [ David B. ]Autres articles à découvrir sur le blog :

• Planification dans Adaptive Server (David BAFFALEUF) [Sybase]

Globalement, ASE utilise des threads pour supporter toutes les tâches qu’il exécute: requêtes, IO réseau ou disque, connexions utilisateur, tâches internes… en conjonction avec son propre planificateur de tâche.

Le rôle du planificateur de tâche sur tout système est de partager le temps d’un CPU entre plusieurs unités d’exécution (tâches). Il existe deux grandes familles de planificateurs, les préemptifs, et les coopératifs.

Le planificateur préemptif a le pouvoir de planifier et d’interrompre les tâches selon des règles qui lui sont propres et sans tenir compte du type de tâche en cours d’exécution. One size fits all, comme on dit. Ce mécanisme, qui garantit qu’aucune tâche ne va monopoliser les ressources CPU, permet à chaque programme de pouvoir avoir la main sur la CPU à un moment où un autre. Sur la plupart des OS (linux, UNIX System V et Windows NT à partir de la 4.0), le planificateur alloue au thread un bail d’exécution ou un quantième. Si la tâche n’a pas terminé son travail dans le temps imparti, elle est interrompue. Lorsqu’il interrompt la tâche en cours, le planificateur sauvegarde son contexte dans un coin (c’est à dire les structures process + thread qui la représentent en mémoire), et planifie une nouvelle tâche sur la CPU. Ce qu’on appelle un changement de contexte.

Le planificateur coopératif, lui, va au contraire laisser à la tâche le soin de se terminer toute seule. C’est là la plus grosse différence, il n’interrompt pas une tâche qui n’a pas terminé son travail. C’est à la charge de la tâche de rendre la main pour laisser de la place aux autres, c’est donc à elle de prévoir un mécanisme de relâche (to yield = « céder »). Windows 3.1 employait un scheduler coopératif, les Windows 98 et 95 aussi pour assurer la compatibilité avec les programmes 16 bits.

Maintenant, on imagine facilement le risque d’employer un tel système de planification: une tâche qui part en vrille ne libèrera jamais la CPU, ça n’a donc plus été employé pour faire des operating systems.

Micheal Stonebraker avait publié un article de quelques pages au début des années 80* expliquant en gros que les ’services’ apportés par UNIX system V n’étaient pas adaptés au monde de la base de données, et notamment en matière de planification. Les tâches typiques issues des SGBD prennent plus de temps à s’effectuer car il ne s’agit pas de calcul mais d’IOs, et le mode préemptif n’est pas adapté car il ‘coupe’ la dynamique et l’efficacité du SGBD en produisant de nombreux changements de contexte.

Donc les développeurs chez Sybase ont répondu en créant un scheduler coopératif ‘on top‘ **, propre à Adaptive Server et par dessus le planificateur de l’OS, de telle manière à ce qu’il masque les choses vis à vis de celui-ci et limite au maximum les changements de contexte.

- Les tranches de temps:
ASE va donc fonctionner comme un OS, mais avec ses règles à lui: il gère sa propre planification, son propre partage du temps CPU, ses propres valeurs de quantième. Car bien que coopératif, il doit pouvoir stopper une tâche qui ne répond plus et éviter la saturation d’un engine. Lorsqu’il planifie une tâche, il va lui allouer un bail. Ce bail n’est pas une valeur temporelle, c’est un nombre de ticks d’horloge qui est décrémenté. Il est calculé en divisant le paramètre ‘timeslice’ par ’sql server clock tick length’. Comme les deux valeurs sont à 100 ms par défaut, le nombre de ticks d’horloge autorisé est de 1. Dans le déroulement de son exécution, une tâche va passer par des parties de code qu’on appelle des yield points, au cours desquels elle va prendre une seconde pour vérifier qu’elle n’a pas dépassé son bail. Si elle découvre que le bail est excédé (bail < 0), elle va demander à ASE de lui accorder une grace supplémentaire (cpu grace time), qui peut aller jusqu’à 500 ticks (soit 50 secondes). Si elle n’a pas terminé au delà de cette valeur, alors ASE sort les gros moyens, termine la tâche, annule ses transactions et affiche une stacktrace dans l’errorlog:

00:00000:00005:2007/04/02 22:09:24.07 kernel  timeslice -501, current process infected
00:00000:00005:2007/04/02 22:09:24.75 kernel  ************************************
00:00000:00005:2007/04/02 22:09:24.77 kernel  curdb = 1 tempdb = 2 pstat = 0x200
00:00000:00005:2007/04/02 22:09:24.77 kernel  lasterror = 0 preverror = 0 transtate = 1
00:00000:00005:2007/04/02 22:09:24.77 kernel  curcmd = 0 program =
...

Tout ceci se passe toujours sans que l’OS ne sache rien, car de son côté, le processus dataserver est toujours en exécution. C’est le but: l’OS ne doit pas interrompre le processus dataserver puisque celui-ci semble toujours travailler.

- Runnable Process search count:
Toujours dans le souci de préserver son exécution vis à vis de l’OS, lorsqu’il a terminé d’exécuter des tâches, et plutôt que de rendre la main tout de suite, Adaptive Server va entrer dans une boucle de vérification des IOs en attente côté réseau puis côté disque. S’il ne trouve rien, il va tourner (spinning) pour rechercher de nouvelles tâches en attente d’exécution, puis de nouvelles IOs réseau, puis de nouvelles IOs disques, etc… tout ça 2000 fois par défaut (runnable process search count), même s’il n’y a rien à traiter dans aucune file d’attente. Ce qui donne une apparente sensation d’hyperactivité côté OS, alors que côté Adaptive Sever, l’électro-encéphalo est plutôt plat.

Pour illustrer tout ça, une petite explication de la section ‘kernel’ de sp_sysmon:

Kernel Utilization
------------------

Your Runnable Process Search Count is set to 5000
and I/O Polling Process Count is set to 10

Engine Busy Utilization        CPU Busy   I/O Busy       Idle
------------------------       --------   --------   --------
Engine 0                       80.0 %      7.5 %     12.5 %
Engine 1                       80.8 %      6.0 %     13.2 %
Engine 2                       82.5 %      5.7 %     11.9 %
Engine 3                       84.0 %      6.7 %      9.3 %
Engine 4                       79.0 %      6.8 %     14.2 %
Engine 5                       80.0 %      7.3 %     12.7 %
Engine 6                       79.6 %      8.3 %     12.0 %
------------------------       --------   --------   --------
Summary           Total         565.9 %     48.4 %     85.7 %
Average                         80.8 %      6.9 %     12.2 %

Avant de commencer, il faut bien rappeler le cheminement de la boucle:

Chaque engine tient le compte de ticks reçus de l’OS (par le biais de signaux SIGALRM) tous les 100ms, et les répertorie selon trois catégories:

CPU Busy: représente le temps (en fait le nombre de ticks ramené en pourcentage) passé par l’engine à exécuter une tâche sur l’intervalle.
Idle: représente le temps passé par l’engine à n’exécuter aucune tâche sur l’intervalle.
I/O Busy: est une soustraction de Idle. Il s’agit du temps passé par l’engine à n’exécuter aucune tâche (donc Idle) mais où il y avait au moins une IO disque en attente dans la file. Elle représente donc la quantité de temps où l’engine n’a pas pu exécuter une tâche parce qu’il y avait au moins une IO disque en attente.

On peut retrouver ces valeurs brutes en interrogeant les variables globales @@cpu_busy, @@idle, et @@io_busy. La durée d’un tick peut être retrouvée avec @@timeticks.

A+. [ David B. ]

*: Michael Stonebraker, Operating System Support for Database Management, CACM 24(7), p412-418 (1981).
**: et Microsoft fit de même en dotant SQL Server 7.0 de son propre scheduler coopératif (UMS), toujours bien présent en version 2008 sous une autre dénomination (SQLOS). Autres articles à découvrir sur le blog :

• Scruter les journaux d’évènements Windows avec LogParser (David BAFFALEUF) [Service hétérogèneSqlServer]
• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]
• Principes d’une sauvegarde à chaud (David BAFFALEUF) [SqlServer]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Sessions consommatrices dans tempdb (David BAFFALEUF) [SqlServer]

WARNING
Although Linux includes support for rawio, it is now a deprecated interface. If your application performs device access using this interface, Red Hat encourages you to modify your application to open the block device with the O_DIRECT flag. The rawio interface will exist for the life of Red Hat Enterprise Linux 5, but is a candi-
date for removal from future releases.

Le bénéfice du raw device n’est pas nouveau, que ce soit pour Adaptive Server ou Oracle, le fait d’écrire directement sur la partition brute a deux avantages majeurs pour les SGBD:
1 – Ca va plus vite.
2 – Ca garantit que ce que l’on écrit va bien sur le disque et non dans un buffer en mémoire. En effet, le système de base de données gère lui-même la bufferisation de ses pages de données et d’indexes, et ne doit pas se reposer sur le système pour ça.

A partir du moment où le raw device cesse d’être supporté, on va devoir écrire dans un fichier, et qui dit écriture dans un fichier sous UNIX/Linux dit écriture bufferisée. C’est là où le critère 2 n’est plus garanti.

Il reste alors deux solutions qui vont nous permettre de garantir celà: DIRECTIO ou DSYNC. Ce sont en fait des arguments qui vont être passés à la primitive d’ouverture du fichier, le plus souvent open(), sous la forme de flags: O_DIRECT, O_DSYNC ou O_ASYNC.

A travers Adaptive Server, ces arguments seront manipulés lors de la création de devices avec la commande disk init:

disk init name='PERFSTATS_data01', physname='/sybase_data/ASE1502/userdb/PERFSTATS_data01.dat',
size=512000,
directio=true

ou

disk init name='PERFSTATS_data01', physname='/sybase_data/ASE1502/userdb/PERFSTATS_data01.dat',
size=512000,
dsync=true

On ne peut pas utiliser à la fois directio=true et dsync=true pour le même device, les options sont mutuellement exclusives.

DIRECTIO:
Lorsque directio est à true, le fichier PERFSTATS_data01.dat sera ouvert avec le flag O_DIRECT:

open("/sybase_log/ASE1502/userdb/PERFSTATS_data01.dat", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFILE) = 17

Lorsque la primitive d’écriture est invoquée (write(), aiowrite()), l’écriture va directement sur le disque. Donc sémantiquement équivalent à ce que donne une écriture sur un raw device, à la fois en termes de performances et de garantie.

DSYNC:
Lorsque dsync est à true, le fichier PERFSTATS_data01.dat sera ouvert avec le flag O_DSYNC:

open("/sybase_log/ASE1502/userdb/PERFSTATS_data01.dat", O_RDWR|O_DSYNC|O_LARGEFILE) = 17

Lorsque dsync est à false, le fichier PERFSTATS_data01.dat sera ouvert avec le flag O_ASYNC:

open("/sybase_log/ASE1502/userdb/PERFSTATS_data01.dat", O_RDWR|O_ASYNC|O_LARGEFILE) = 17

Lorsque la primitive d’écriture est invoquée, l’écriture sera bufferisée mais en fonction de la valeur passée à disk init (true ou false), certains éléments vont être synchronisés sur disque avant que aiowrite() ne revienne au programme appelant.
- dsync=true: alors le flag O_DSYNC est passé à open(). Ce flag garantit que write() reste en attente tant que les données n’ont pas été synchronisées sur disque.
- dsync=false: alors le flag O_ASYNC est passé à open(). Ce flag indique que write() rend la main une fois l’écriture bufferisée mais AVANT la synchronisation sur disque, ce qui ne garantit pas notre critère 2.

On recommandera toujours de mettre dsync=true pour les devices de données et de journaux pour avoir la garantie d’écriture stabilisée, et dsync=false pour les devices tempdb pour favoriser la perf.

- Avantages / Inconvénients pour le DSYNC:
Avantages: c’est complètement supporté par les file system.
Inconvénients: C’est moins performant en écriture.

- Avantages / Inconvénients pour le DIRECTIO:
Avantages: c’est comme du raw device, sauf que c’est dans un fichier. C’est même plus efficace en lecture.
Inconvénients: il faut que le filesystem le supporte (c’est le cas pour ext3), et d’après Linus l’implémentation de directio au niveau du kernel est épouvantable. Le problème c’est que ce qu’il indique comme contournement à destination des développeurs ASE (Wim Ten Have et Dave Wein en tête) n’est pas vraiment applicable.

Pas de doute, le DIRECTIO reste la meilleure option. Ce sera sûrement réécrit un jour, mais pour l’instant on n’a pas trouvé mieux.

Un dernier mot sur le dsync=true sur les noyaux 2.6+, l’écriture asynchrone se transforme en écriture synchrone et les perfs chutent (( . Il faut vraiment utiliser directio sur les noyaux récents. Ce sera peut être le sujet d’un prochain post sur ASE.

A+ [ David B. ]Autres articles à découvrir sur le blog :

• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Utiliser ASMCMD (Thierry GASCARD) [Oracle]
• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]
• Création et utilisation d’OCFS2 (Thierry GASCARD) [Oracle]

Hélas, ce n’est pas plus facile qu’un autre SGBD. C’est juste plus facile de se tromper. Voici quelques règles de bases lorsqu’on s’apprête à installer un SQL Server en production. Episode 1: le matériel.

1) La machine: physique ou virtuelle ?
Le gros débat des mois à venir. Même si la plupart des clients vont conserver des machines physiques pour leurs bases critiques, il y aura des bases de prod, uat, dev, qualif moins critiques qui seront virtualisées.
Les constructeurs (Dell, HP,…) sortent de plus en plus des chassis dédiés à la virtualisation, avec ESXi embarqué. La virtualisation est (heureusement ? maheureusement ?) un train en marche, et plutôt que de freiner des quatre fers, posons-nous la question « comment virtualiser », parce qu’il ne faut pas se voiler la face, on va y venir de toutes façons, ce n’est qu’une question de temps. HYPER-V et ESX sont tous les deux de bons concurrents, et ESX est annoncé compatible avec les produits MS. La combinaison des technos aujourd’hui (full virtualisation + assistance matérielle) fait que l’overhead de performances se réduit de plus en plus (on est quasiment en CPU-direct sur les Intel-VT et les AMD-V). Je penche personnellement pour le raw device mapping, une assistance matérielle CPU et une configuration mémoire appropriée. Dans quelques temps nous aurons un ESX ou deux et nous pourrons tester tout ça.

2) Admettons, machine physique:
LE PRIX ! Franchement les configurations à moins de 8000€ sont assez répandues et fiables. Je pense au HP Proliant DL 580 G5 (2×4 Xeon 3Ghz, 4Gb RAM, 2 cartes 1ge et 16 slots disque pour $7600) ou au très populaire Dell PowerEdge 2950 III (même config mais avec seulement 6 slots disques pour $3600).

DEDIEE/MUTUALISEE: en fonction des moyens. Mais idéalement il faut une machine dédiée. Les petits budgets vont faire tourner IIS et SQL Server sur la même machine, et d’expérience, c’est un mauvais calcul. Il vaut mieux investir un peu plus dans deux machines, et on aura moins de soucis ensuite.

CPU: le plus de cores il a sous le coude, et le mieux il se porte, SQL Server. Il est multithreadé, il sait parralléliser ses traitements. Evidemment il faut partir directement en 64 bits (EM64T, IA64, AMD64). Attention les éditeurs commencent à revoir leur politique de licence par socket, ils perdaient trop d’argent avec les hexacores et certains commencent à limiter à 6 cores par socket. Et attention à ne pas activer l’hyperthreading sur les machines SQL Server.

MEMOIRE: Oublier le 32 bits, la version 2008 de Windows Server sera la dernière à être proposée dans ce mode. En 64 bits, et avec le prix de la DDR2, il ne faut pas lésiner. C’est encore plus important que la CPU. Plus le cache de données est gros, moins SQL Server fera de lectures physiques.

RESEAU: Ca dépend. Je suis intervenu chez un client DRM il y a quelques temps et là le réseau c’était VRAIMENT crucial. Ca va le devenir de plus en plus avec FILESTREAM, la spatialisation, la vidéo HD, le plus de données on stocke, et le plus de données on va faire transiter. Les cartes et les core switches en 10Ge sont là, les Jumbo Frames, donc tout ça suit derrière. Le réseau entre systèmes Production / Secours est aussi crucial (Database mirroring, réplication). Nous avons un client en database mirroring 2005 qui avait installé un lien 100Mb entre ses deux sites, il va passer en fibre car il a de plus en plus de choses à faire passer sur le lien en plus du streaming lié au DB mirroring (sauvegardes, recopies de fichiers, etc…). Autre exemple, un client qui a une réplication transactionnelle entre Frankfort et Londres, le lien est vraiment très lent, et la resynchro de répli (34Gb) se fait en moyenne en 10-12 heures.
Mais si l’application est codée en procédures stockées, que la quantité de données qui transite n’est pas importante et que SQL server est standalone, alors une carte 100Mb/s devrait faire l’affaire.

DISQUES: tout le monde ne peut pas se payer un SAN, je pense aux petites PME du web avec 1 machine serveur, un IIS et une base de données de production, avec des moyens modestes. Donc on va considérer deux configurations: les disques internes pour les petits, et le stockage en réseau pour les riches et la classe moyenne.
– DISQUES INTERNES: choisir du SAS en 15KRPM. Le disque pour la base de données, c’est le nerf de la guerre. Le HP DL580 G5 avec ses 16 slots est parfait, il permet de faire trois RAID10 (data, log, backup) et un RAID1 (système) et de garder deux slots de spare. Il faudra vérifier au niveau de l’interface du contrôleur SAS que celui-ci est bien sur batterie et que celle-ci est valide, et que la politique est en write-through, c’est à dire que le cache du contrôleur n’est pas utilisé en écriture. Les disques SAS 73 Gb 15KRPM sont à 330€ chez Dell en 2′5, ça ne vaut pas le coup de se priver.
- STOCKAGE RESEAU: ne pas utiliser de NAS au sens strict (attachement réseau), pas de CIFS, ce n’est pas supporté de base par SQL Server. Il faut que les disques soient vus comme des disques locaux, donc soit une baie avec un attachement direct, soit un SAN. Pour les riches, le SAN en fibre channel est à privilégier. Pour la classe moyenne, le iSCSI est un bon moyen de faire du SAN à moindre coût, à la condition d’utiliser sur la machine hôte un initiateur matériel et non logiciel, c’est à dire une carte 1Ge qui soit compatible TSO (TCP Segmentation Offload, calcul de la pile TCP déchargée du CPU hôte), et que le réseau soit dédié au stockage. Pour les disques SAN, même politique que les disques internes. Au niveau du choix de la baie, il faut regarder plusieurs choses: Le nombre de storage processors (va déterminer le multiplexage des entrées, il en faut au moins 2), la taille du cache par storage processor et non en globalité, le nombre de slots pour les disques, la capacité globale. Le plus important c’est la taille du cache par SP. Si les caches entre SP sont mirrorés l’un sur l’autre, on perd la moitié de la capacité mémoire, si la baie est mirorrée sur une autre, on perd encore car le soft de miroir utilise des buffers dans le cache de chaque SP. Ensuite, il faut voir comment le cache est partitionné entre les lectures et les écritures. Le plus souvent, c’est fait à la louche pour des systèmes majoritairement en lecture, mais attention à ne pas négliger les écritures en transactionnel. Un cache en écriture plein, et les queues se remplissent au niveau des SP, on observe des outstanding IOs côté SQL Server et c’est rapidement la cata.

Il paraît que je fais des articles trop longs, donc ce sera tout pour aujourd’hui
Prochain épisode, l’installation Windows et les prérequis SQL Server.

A+ [ David B. ]Autres articles à découvrir sur le blog :

• Règles d’installation de base (épisode 2) (David BAFFALEUF) [SqlServer]

Explication: lorsque l’agent de snapshot doit extraire le DDL des articles et sortir les données par bcp en parrallèle, il se base sur deux options passées lors du sp_addarticle:
- @type: qui va indiquer à quel type de publication on a affaire. Dans notre cas, ce sera logbased (=transac)
- @schema_option : bitmask qui va indiquer ce qui doit être généré au niveau DDL.

D’après les BOLs 2005, si @type = ‘logbased’, alors @schema_option doit être à 0×30F3, qui décomposé contient bien à la fois le bitmask pour les index clusterisés (0×10) et celui pour les indexes NC (0×40).

Je vous engage à tester ça depuis SSMS, en créant une publication transac bidon et en jetant un coup d’oeil aux propriétés de l’article:

Dans le script généré, effectivement on a bien un problème:

exec sp_addarticle
@publication = N'pubtest1',
@article = N'Address',
@source_owner = N'dbo',
@source_object = N'Address',
@type = N'logbased',
@description = null,
@creation_script = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual',
@destination_table = N'Address',
@destination_owner = N'dbo',
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_dboAddress',
@del_cmd = N'CALL sp_MSdel_dboAddress',
@upd_cmd = N'SCALL sp_MSupd_dboAddress'
GO

Le bitmask 0×803509F contient bien le 0×10, mais pas le 0×40. Pour en avoir le coeur net, utilisez le ET logique en transac (134434975=0x803509, 64=0x40, 16=0x10):

select 134434975 & 64
select 134434975 & 16

-----------
0

-----------
16

Suite à une petite discussion avec Paul Randal, il se trouve que nombre de ses clients ont eu le même problème en migrant. Donc si vous migrez une réplication de 2000 vers 2005, et que vous constatez des problèmes de perfs sur les abonnés, vérifiez si vous avez tous vos indexes NC, juste comme ça…

A+ [ David B. ]

Autres articles à découvrir sur le blog :

• Retrouver une transaction en échec (David BAFFALEUF) [SqlServer]
• XtraDB sauvegarde votre cache, et ça marche ! (Cédric PEINTRE) [MySQL]
• MySQL et les tables temporaires internes (Cédric PEINTRE) [MySQL]

Nous allons créer un système de fichiers supportant le cluster (Cluster FileSystem).
L’OS utilisé est une SUSE 10 en 64 bits.

1. Installation du driver OCFS2

A effectuer via l’utilisateur système root.
OCFS2 1.4 est compris dans la distribution SLES10 SP2.
sinon sous les autres linux :
Déterminer la version du noyau : uname –rm
Rechercher les drivers sur le site d’Oracle
Télécharger les fichiers correspondants à la plate forme et la version du noyau (ici RH5 64 bits)

• ocfs2-tools-1.4.1-1.el5.x86_64.rpm
• ocfs2console-1.4.1-1.el5.x86_64.rpm
• ocfs2-2.6.18-128.1.1.el5-1.4.1-1.el5.x86_64.rpm

Installer les RPMs sur chaque noeud :
rpm -Uvh ocfs2*.rpm
vérifier l’installation
chkconfig –list o2cb

2. Configuration OCFS2

1) Sur un des nœuds en x window lancer ocfs2console puis cluster/configure node
ajouter hostname1   x.x.x
ajouter hostname2   x.x.x
puis propager la configuration
Rem : attention au nom de domaine
2) vérifier sur chaque nœud le fichier /etc/ocfs2/cluster.conf
3) configurer le service o2cb sur chaque nœud en root
/etc/init.d/o2cb configure

puis répondre y et accepter les valeurs par défaut sauf Use user-space driven heartbeat? (y/n) [y] n

3. Formater la partition en OCFS2 sur un nœud

en x window lancer ocfs2console  puis task/format

• /dev/emcpowerd
• label : oracle
• cluster size : 128 K
• block size 4k

4. Monter le système de fichiers OCFS2

1) créer un point de montage sur chaque nœud
mkdir /ocfs
2) ajouter dans /etc/fstab, voire la note metalink 835839.1
/dev/emcpowerd /ocfs    ocfs2    _netdev,datavolume,nointr,noatime 0 0

les options correspondent avec ocfs2 1.4.1 :

• datavolume : utilisation des « direct IO »
• _netdev : montage après le réseau
• nointr: désactive les siganux durant les opération de cluster
• noatime : désactive la mise à jour de l’heure

Autres articles à découvrir sur le blog :

• Installation Oracle 64 bits sur Red Hat 5 (Thierry GASCARD) [Oracle]
• Se connecter à SQL Server à travers Oracle, quelle drôle d’idée ? (Thierry GASCARD) [Oracle]
• Installation Oracle 11gR2 64 bits sur Red Hat 5 Partie 1 (Thierry GASCARD) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Utiliser ASMCMD (Thierry GASCARD) [Oracle]

Un utilitaire asmcmd permet de naviguer dans les disques ASM.
Voici quelques commandes utiles.
Avant de se connecter, vérifier que les processus de l’instance ASM sont lancés
ps –ef |grep ASM
export ORACLE_SID=+ASM
export ORACLE_HOME=/u01/app/asm/asm_1

Afficher la version : asmcmd –v
Lancer asmcmd sous l’utilisateur asm ou oracle
Asmcmd>
Lister les disques groupes ( affiche également la taille des disques et l’espace libre dans les disques ) : lsdg
Lister les disques : lsdsk
Avoir les io sur les disques : lsdsk -s
Vérifier le status des disques : lsdsk –p
Aller dans un groupe :  cd +diskgroup1/
Afficher l’espace total utilisé : du
Trouver tous les fichiers d’un type dans un diskgroup :
find –t archivelog  + DISKGROUP1/ *
find –t datafile +DISKGROUP1/  *
find –t onlinelog +DISKGROUP1/ *
find –t tempfile +DISKGROUP1/ *
find –t backupset +DISKGROUP1/  *
find –t parameterfile +DISKGROUP1/  *
find –t dataguardconfig + DISKGROUP1/  *
find –t flashback +DISKGROUP1/ *
find –t dumpset + DISKGROUP1/  *
Trouver les fichiers sans le type mais avec une wildcard : find +DISKGROUP1/  undo*
Créer une sauvegarde contenant les métadonnées pour un ou plusieurs groupes  :
md_backup –b /u01/bdd/backupall
md_backup –b /u01/bdd/backupdiskgroup1 –g diskgroup1
Restaurer des métadonnées d’un diskgroup :
md_restore –b backup_file ( lit les métadata de backup_file )Autres articles à découvrir sur le blog :

• SQLDIAG (épisode 1) (David BAFFALEUF) [SqlServer]
• Installation Oracle 11gR2 64 bits sur Red Hat 5 Partie 1 (Thierry GASCARD) [Oracle]
• Création et utilisation d’OCFS2 (Thierry GASCARD) [Oracle]
• Se connecter à SQL Server à travers Oracle, quelle drôle d’idée ? (Thierry GASCARD) [Oracle]
• Installation Oracle 64 bits sur Red Hat 5 (Thierry GASCARD) [Oracle]

1. Installation et configuration du driver ASMLIB

Sous l’utilisateur root

1.1    Installation ASMLIB

- Déterminer la version du noyau : uname –rm
- Rechercher les drivers ASM sur le site d’Oracle
- Télécharger les fichiers correspondants à la plate forme et la version du noyau
oracleasm-support-2.1.3-1.SLE10.x86_64.rpm
oracleasmlib-2.0.4-1.SLE10.x86_64.rpm
oracleasm-2.6.16.60-0.39.3-smp-2.0.5-1.SLE10.x86_64.rpm
- Installer les RPM correspondants :
rpm -Uvh \
> oracleasm-2.6.16.60-0.39.3-smp-2.0.5-1.SLE10.x86_64.rpm \
> oracleasmlib-2.0.4-1.SLE10.x86_64.rpm \
> oracleasm-support-2.1.3-1.SLE10.x86_64.rpm

1.2 Configuration du driver ASM

- Lancer la commande de configuration
# /etc/init.d/oracleasm configure
- Renseigner les informations suivantes :
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration:           [  OK  ]
Creating /dev/oracleasm mount point:                       [  OK  ]
Loading module « oracleasm »:                                [  OK  ]
Mounting ASMlib driver filesystem:                         [  OK  ]
Scanning system for ASM disks :                            [  OK  ]

1.3 Créer les volumes ASM

/usr/sbin/asmtool -C -l /dev/oracleasm -n VOLDB1-s /dev/emcpowerb -a force=yes
/usr/sbin/asmtool -C -l /dev/oracleasm -n VOLBK1 -s /dev/emcpowerc -a force=yes
puis redémarrer le serveur

Rem : oracleasm createdisk ne fonctionne pas note Metalink : 469163.1
erreur de reconnaissance des disques fournis avec powerpath
# /etc/init.d/oracleasm createdisk VOLDB1 /dev/emcpowerb
Marking disk « /dev/emcpowerb » as an ASM disk: asmtool: Device « /dev/emcpowerb » is not a partition
[FAILED]

1.3 Vérifier

lancer la découverte
/etc/init.d/oracleasm scandisks
Vérifier l’existence des volumes sur les deux noeuds
# /etc/init.d/oracleasm listdisks

1.4 configurer les chemins de découverte des disques

Modifier le fichier /etc/sysconfig/oracleasm pour paramétrer la gestion du  « multipathing » en gras :

..
# ORACLEASM_SCANORDER: Matching patterns to order disk scanning
ORACLEASM_SCANORDER= »emcpower »

# ORACLEASM_SCANEXCLUDE: Matching patterns to exclude disks from scan
ORACLEASM_SCANEXCLUDE= »sd »

2. Installation ASM 11g

Dans son propre ORACLE_HOME (ORACLE_ASM_HOME)

3. Passage patch 11.1.0.7

4. Création de l’instance ASM

4.1. Configuration du listener pour ASM

Connexion avec l’utilisateur asm ou oracle sinon
export ORACLE_HOME=/u01/app/asm/11.1.0/asm_1
$ORACLE_HOME/admin/netca &
Creation du listener LISTENER_ASM sur le port 1521 en TCP

4.2 Création de l’instance ASM

Connexion avec l’utilisateur système oracle
export ORACLE_SID=+ASM1
/u01/oracle/11g/asm/bin/dbca &

puis configurer les « disques groupe »Autres articles à découvrir sur le blog :

• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Installation Oracle 64 bits sur Red Hat 5 (Thierry GASCARD) [Oracle]
• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]
• Installation Oracle 11gR2 64 bits sur Red Hat 5 Partie 1 (Thierry GASCARD) [Oracle]
• Création et utilisation d’OCFS2 (Thierry GASCARD) [Oracle]

1 Configuration système

1.1 Pré-requis matériel :

- 12 Go pour les binaires Oracle en RAID 10
- 400 Mo sur /tmp

1.2 Pré-requis logiciel :

RH 5 Entreprise 64 Bits : Oracle 10.2.0.1 Entreprise Edition Linux x86-64

Patch 10.2.0.4 pour Linux x86-64 sur le site du support Oracle

Patch 6620371 pour Linux 64 Bits sur le site du support Oracle

1.3 Pré-requis noyau

version du noyau >=2.6.18-8
SELinux désactivé
X installé et configuré
Rpms suivants installés en plus des Rpms par défaut (version minimum) :
binutils-2.17.50 (x86_64)
compat-db-4.2.52 (x86_64)
compat-libstdc++-296(i386)
compat-libstdc++-33-3.2.3 (x86_64)
compat-libstdc++-33-3.2.3(i386)
control-center-2.16.0 (x86_64)
gcc-4.1.1 (x86_64)
gcc-c++-4.1.1 (x86_64)
glibc-2.5-12 (x86_64)
glibc-2.5-12 (i686)
glibc-common-2.5-12 (x86_64)
glibc-devel-2.5-12 (x86_64)
glibc-devel-2.5-12(i386)
glibc-headers-2.5-12 (x86_64)
ksh-20060214-1.4 (x86_64)
libaio-0.3.96 (x86_64)
libgcc-4.1.1(i386)
libgcc-4.1.1(x86_64)
libgnome-2.16.0 (x86_64)
libgnomeui-2.16.0 (x86_64)
libgomp-4.1.1 (x86_64)
libstdc++-4.1.1 (x86_64)
libstdc++-devel-4.1.1 (x86_64)
libXp-1.0.0-8 (i386)
make-3.81 (x86_64)
sysstat-7.0.0 (x86_64)
unixODBC-2.2.11-7.1 x86_64.
unixODBC-devel-2.2.11-7.1 x86_64
util-linux-2.13-0.50.el5 x86_64
xorg-x11-xinit-1.0.2-15.el5 x86_64

Rem : attention à la présence de rpms 32 bits : utilisation possible de l’option force : rpm -ivh –force ..

1.4 Paramètres noyau

Modification du fichier /etc/sysctl.conf :
kernel.shmmni = 4096
kernel.sem= 250 32000 100 128
kernel.sysrq=1
kernel.msgmnb=65536
kernel.msgmni=2878
kernel.msgmax=8192
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max=2097152
net.core.wmem_default = 262144
net.core.wmem_max = 262144
fs.aio-max-nr=3145728
fs.file-max=327679
vm.lower_zone_protection=100
kernel.shmall = 3145728
kernel.shmmax = 12884901888
vm.nr_hugepages = 6144

Modification du fichier /etc/security/limits.conf :
oracle hard memlock 12582912
oracle soft memlock 12582912
oracle soft core unlimited
oracle hard nofile 131072
oracle hard nproc 131072
oracle soft nproc 131072
oracle soft nofile 131072
oracle hard core unlimited

2    Préparation système

2.1 Création groupe oinstall,dba

root> groupadd -g 55 oinstall
root>groupadd -g 56 dba

2.2    Ajout utilisateur oracle

root> useradd -u 500 -g 55 -G dba -c « Oracle Owner » -d /home/oracle -s /bin/bash oracle
root> passwd oracle

2.2 Modification du fichier /etc/profile
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
ulimit -l 12582912
else
ulimit -u 16384 -n 65536 -l 12582912
fi
fi

2.2    Création des répertoires

root> mkdir /opt/oracle
root> mkdir /u01
root> chown –R oracle:oinstall /opt/oracle
root> chown –R oracle:oinstall /u01
root> su – oracle
mkdir -p /opt/oracle/product/10.2.0/db_1
mkdir -p /opt/oracle/admin

3 Installation Oracle

3.1    Décompression des zips et cpio

$ cat 10201_database_linux_x86_64.cpio.gz | gunzip | cpio -idvm
$ cat 10201_companion_linux_x86_64.cpio.gz | gunzip | cpio –idvm
$unzip p6810189_10204_Linux-x86-64

3.2   installation

export ORACLE_BASE=/opt/oracle/product ;
export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
./runInstaller &

Ordre d’installation
1. Installation « Base Product (10.2.0.1) »
2. Installation “Companion Disk” pour éviter l’erreur NCOMP lors du patch 10.2.0.4
- Choisir l’option «Oracle Database 10g Products 10.2.0.1.0»
3. Installation Patch (10.2.0.4)
4. Installation patch 6620371 pour « huge page »
[oracle]$ cd 6620371/
[oracle]$ /opt/oracle/product/10.2.0/db_1/OPatch/opatch apply

4    Mise à jour de l’environnement Oracle

4.1 mise à jour du .profile_bash d’oracle

4.2 mise à jour du glogin.sql

vi $ORACLE_HOME/slplus/admin/glogin.sql
set sqlprompt « _user ‘on’ _date ‘at’ _connect_identifier > »

5    configuration de huge page sous linux

- vm.nr_hugepages  : nombre de pages (taille de 2Mo sur linux avec grep Hugepagesize /proc/meminfo)
- vm.hugetlb_shm_group : ID du groupe utilisé par oracle (dba)
- kernel.shmmax supérieur à la taille de l’espace mémoire accordé aux « huge page »
- memlock  : avec /etc/security/limit.conf et ulimit -l
Tout est bien expliqué dans le blog d’ arkzoyd: à lire absolument !!!!!Autres articles à découvrir sur le blog :

• Création et utilisation d’OCFS2 (Thierry GASCARD) [Oracle]
• Installation Oracle 11gR2 64 bits sur Red Hat 5 Partie 1 (Thierry GASCARD) [Oracle]
• Se connecter à SQL Server à travers Oracle, quelle drôle d’idée ? (Thierry GASCARD) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Utiliser ASMCMD (Thierry GASCARD) [Oracle]

BACKUP DATABASE maBase to disk='D:\MSSQL.1\MSSQL\Backup\maBase.bak' with init, stats

10 percent processed.

21 percent processed.

32 percent processed.

43 percent processed.

54 percent processed.

60 percent processed.

71 percent processed.

82 percent processed.

93 percent processed.

Processed 144 pages for database 'maBase', file 'maBase' on file 1.

100 percent processed.

Processed 2 pages for database 'maBase', file 'maBase_log' on file 1.

BACKUP DATABASE successfully processed 146 pages in 0.816 seconds (1.463 MB/sec).

Et voilà. L’avantage d’une telle sauvegarde est que je peux la lancer sans stopper mon service, sous entendu avec de l’activité: connexions utilisateurs, transactions en cours, etc… Et tout ça en me garantissant de restaurer une base transactionnellement cohérente. Pour y parvenir, elle commence par sauvegarder les données puis les transactions présentes dans le journal.

Avant de parler de ce que fait le BACKUP DATABASE, il faut parler un peu des rôles respectifs du CHECKPOINT et du COMMIT dans le processus de RECOVERY.

i) Lorsqu’un utilisateur initie une transaction, ses modifications sont d’abord journalisées en mémoire dans une zone qui est réservée pour sa session. Puis les pages de données et d’index concernées sont modifiées dans le cache de données.

ii) Lorsqu’il valide sa transaction de manière implicite ou explicite (avec COMMIT TRANSACTION), le contenu de son journal de session est enregistré physiquement dans le fichier LDF. Il est impératif de s’assurer que le COMMIT ne peut rendre la main à la commande appelante que lorsque la transaction a été véritablement écrite sur disque (le ‘write-ahead logging’ en d’autres termes). Ce mécanisme permet de pouvoir récupérer la transaction validée lorsqu’un problème survient avant que les pages de données n’aient pu être écrites.

iii) De manière complètement indépendante et asynchrone, le processus d’arrière plan CHECKPOINT écrit les pages de données et d’index modifiées (dirty pages) sur le disque, sans se préoccuper de savoir si elles appartiennent à une transaction validée ou non.

A partir de là, deux situations peuvent se produire au point de crash:

1) Une transaction peut être validée (committée) par l’utilisateur mais le CHECKPOINT n’a pas eu le temps d’écrire les pages de données / d’index sur disque. Il va falloir réécrire ces pages dans le fichier de données au redémarrage de l’instance (REDO ou ROLL-FORWARD).

2) Le CHECKPOINT qui n’a pas de vision sur l’état de la transaction peut avoir écrit ses pages sur disque avant qu’elle n’ait été validée par l’utilisateur. Auquel cas il faudra défaire les pages écrites car au point de crash car la transaction ne peut être considérée comme valide. (UNDO ou ROLLBACK).

C’est la combinaison de ces deux actions que l’on appelle le RECOVERY. Ce processus intervient dans de nombreux changements d’état de la base, et notamment pour ce qui nous intéresse, lors de la phase de restauration d’une sauvegarde.

Lorsque le BACKUP DATABASE est exécuté, il commence par forcer l’exécution d’un CHECKPOINT dans la base concernée. Ce CHECKPOINT aura pour effet d’entériner des transactions validées depuis ma dernière sauvegarde de journal : Les pages de données / d’index sont écrites sur disque, la sauvegarde n’a donc plus besoin de s’en préoccuper. Bien sûr il peut aussi écrire les pages de données / d’index d’une transaction qui n’est pas encore validée. Immédiatement ensuite, il va relever un numéro de transaction (LSN pour Log Sequence Number) dans le journal pour s’en servir comme borne de départ pour le RECOVERY. Appelons-le LSN1.

Puis il va lire et écrire les pages de données depuis le fichier MDF ou les différents filegroups, et les écrire dans le fichier de sauvegarde. Une fois terminées ces écritures, il renote le LSN courant (qui entre temps peut avoir évolué), et s’en sert comme borne de fin (LSN2). Puis il va sauvegarder tout ce qui se trouve entre (LSN2 – LSN1). Certaines transactions seront validées, certaines ne seront peut être pas terminées, mais peut importe, car au moment de la restauration, le processus de RECOVERY fera son œuvre pour ramener la base à un état transactionnel consistant.

Si le LSN2 lui ne change pas car c’est toujours le dernier numéro généré dans le journal qui sera utilisé, le problème vient plus du calcul du LSN de départ car il va dépendre de la situation dans laquelle se trouve le BACKUP DATABASE au départ. Globalement, je peux me trouver dans deux cas :

- Soit il n’y a aucune transaction en cours au moment où commence la sauvegarde : auquel cas LSN1 sera égale à la valeur du LSN généré par le CHECKPOINT (lancé par le BACKUP):

L’effet du CHECKPOINT ici est de flusher les pages de données modifiées par T1, T2 et T3, pour ne pas à avoir à les prendre dans la sauvegarde du journal. Ces pages se retrouvent sur disque, et seront capturées lors de la copie de données.

LSN2 est toujours le LSN courant capté dès la fin de la copie de données. Dans ce cas, T4 est terminée mais on est en plein milieu de T5, donc on prend quand même ce ‘fragment’ de T5. Lors de la restauration, T4 sera réappliquée et T5 sera rollbackée:

Soit il y aura au moins une transaction en cours : auquel cas, SQL Server va aller rechercher le LSN de départ de la transaction active la plus ancienne. Dans l’exemple ci-dessous, il s’agit de T2:

Dans ce cas, T2 et T4 qui sont terminées avant la fin de la copie de données seront réappliquées et T5 et T3 rollbackées:

La table msdb.dbo.backupset qui stocke des données pour chaque sauvegarde effectuée permet de vérifier dans quel état se trouvait la base au moment de la sauvegarde. Pour le déterminer, il faut comparer les valeurs de checkpoint_lsn et first_lsn:

- checkpoint_lsn = first_lsn: alors il n’y avait pas de transaction en cours au moment du BACKUP DATABASE.

select first_lsn, checkpoint_lsn, last_lsn from msdb.dbo.backupset where database_name  = ‘CAPDATA’

first_lsn                  checkpoint_lsn             last_lsn

--------------------       --------------------       --------------------

1341 0000000302 00051     1341 0000000302 00051       1341 0000000323 00001

- first_lsn < checkpoint_lsn: alors il y avait au moins une transaction en cours.

select first_lsn, checkpoint_lsn, last_lsn from msdb.dbo.backupset where database_name  = ‘CAPDATA’

first_lsn                checkpoint_lsn          last_lsn

--------------------     ---------------         --------------------

1341 0000000510 0002     1341 0000000523 00098    1341 0000000563 00001

Autres articles à découvrir sur le blog :

• Planification dans Adaptive Server (David BAFFALEUF) [Sybase]
• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• XtraDB sauvegarde votre cache, et ça marche ! (Cédric PEINTRE) [MySQL]

Ici nous allons aborder le troisième mode BULK-LOGGED, ses avantages, inconvénients et contextes d’utilisation. Quelques rappels du post précédent:

BULK-LOGGED: c’est un mode à combiner avec le mode FULL. Dans ce mode, les opérations de modification de masse ne sont pas complètement journalisées, et l’impact de telles opérations sur le journal est limité. Le fait de changer de FULL à BULK-LOGGED ne modifie en rien ma façon de sauvegarder mes bases. En mode BULK-LOGGED, mes sauvegardes transactionnelles horaires continuent de s’exécuter sans problème. Très utile lorsqu’on lance notre réorganisation des indexes hebdomadaire car on gagne un temps fou.

Quelles sont ces opérations de modification de masse:

1. SELECT INTO
2. BCP IN / BULK INSERT
3. ALTER INDEX … REBUILD
4. Manipulation de champs de type TEXT:

On les appelle parfois par abus de langage opérations non loggées, le terme plus juste est minimallement loggées.

Lorsqu’une de ces opérations minimalement loggée est exécutée, seules les allocations de pages sont journalisées, mais elles seules ne suffisent pas à restaurer l’intégralité de mon reindex ou de mon select into en cas de crash. C’est pourquoi pour permettre de restaurer ces données modifiées, la sauvegarde embarque avec elle les pages de données allouées lors de l’opération.

Pour le vérifier, on se propose de comparer sous les deux modes de récupération FULL et BULK_LOGGED, deux opérations minimalement loggées :

1. Un SELECT INTO d’une table de 2,6 millions de lignes dans une table d’archive.
2. Le rebuild d’un index clusterisé sur une table de 2,6 millions de lignes.

Dans chaque cas, nous allons mesurer plusieurs choses :

1. Le temps d’exécution de l’opération.
2. L’impact sur le journal de transactions (en Mb).
3. L’impact sur la taille de ma sauvegarde de transaction (en Mb).

1) En mode de récupération FULL :

Pour pouvoir exécuter la sauvegarde du journal à l’issue de chaque opération, il faut commencer par exécuter une sauvegarde complète (voir article précédent) :

ALTER DATABASE CAPDATA SET RECOVERY FULL

BACKUP DATABASE CAPDATA TO DISK='C:\YUKON\BACKUP\CAPDATA.bak'
WITH init, stats

Ensuite on va vérifier que l’on part sur un journal vide :

dbcc sqlperf(logspace)

Database Name        Log Size (MB) Log Space Used (%) Status
----------------------------------------------------------------------------------------------
CAPDATA            49,99219      2,951633           0

Le journal fait 50Mb, il est vide (2%). On peut donc lancer le SELECT INTO :

set statistics time on
select * into PIECEAPPARTEMENT2 from PIECEAPPARTEMENT

 SQL Server parse and compile time: 

 CPU time = 0 ms, elapsed time = 48 ms.

  SQL Server Execution Times:

 CPU time = 7641 ms, elapsed time = 57673 ms.

 (2644317 row(s) affected)

Quelle est la taille du journal ?

dbcc sqlperf(logspace)

Database Name        Log Size (MB) Log Space Used (%) Status
----------------------------------------------------------------------------------------------

CAPDATA             97,55469       93,43568            0

Il est passé de 50 à 97 Mb, et il est plein. Sauvegardons la transaction pour le vider :

BACKUP LOG CAPDATA TO DISK='C:\YUKON\BACKUP\CAPDATA.trn' with init, stats

(…)

BACKUP LOG successfully processed 11592 pages in 25.525 seconds (3.720 MB/sec).

11592 * 8192 ~= 93 Mb, soit la taille de ma transaction dans mon journal. Normal. Une fois la sauvegarde effectuée, on repart sur un journal vide. On peut donc lancer immédiatement le REBUILD de l’index sur PIECEAPPARTEMENT :

ALTER INDEX PK_PIECEAPPARTEMENT ON PIECEAPPARTEMENT REBUILD WITH (SORT_IN_TEMPDB = ON)

 SQL Server Execution Times:

 CPU time = 11828 ms, elapsed time = 66788 ms.

A nouveau le journal est plein et si je le sauvegarde encore, la taille du fichier sera égale à la taille de ma transaction.

dbcc sqlperf(logspace)

 Database Name      Log Size (MB)     Log Space Used (%)    Status

----------------------------------------------------------------------------------------------

CAPDATA             97,55469          92,42763               0

2) En mode de récupération BULK_LOGGED :

Même scénario mais cette fois la base est passée en mode de récupération BULK_LOGGED, et mon journal est vide. Pour partir sur la même enveloppe initiale, et ne pas fausser les résultats pour le SELECT INTO, je vais réduire la taille de mon journal à sa taille de départ (50Mb) :

dbcc shrinkfile('CAPDATA_log01',50)

DbId   FileId       CurrentSize MinimumSize UsedPages    EstimatedPages

------ ----------- ----------- ----------- ----------- --------------

6      2        6400           6400        6400          6400

Et je lance le SELECT INTO :

SQL Server Execution Times:

 CPU time = 8235 ms, elapsed time = 24353 ms.

 (2644317 row(s) affected)

Déjà, il a mis deux fois moins de temps. Voyons voir la situation du journal :

dbcc sqlperf(logspace)

 Database Name Log Size (MB) Log Space Used (%) Status

----------------------------------------------------------------------------------------------

CAPDATA       49,99219      3,073722             0

Quasiment rien n’a été utilisé !! Non seulement l’enveloppe n’a pas bougé mais en plus mon journal est toujours quasiment vide. J’en profite pour regarder ce qui se trouve dans ces 3% de journal utilisé:

select Operation, COUNT(*) nr_records, SUM([Log Record Length]) totalsize

from ::fn_dblog(null,null) 

group by Operation order by COUNT(*) desc

Operation                       nr_records  totalsize

------------------------------- ----------- -----------

LOP_SET_FREE_SPACE              11111       577772

LOP_SET_BITS                    5650        328272

LOP_MODIFY_ROW                  1570        138852

LOP_COUNT_DELTA                 33          6864

LOP_BUF_WRITE                   31          59512

LOP_INSERT_ROWS                 27          4068

LOP_ROOT_CHANGE                 5           480

LOP_BEGIN_XACT                  4           432

LOP_HOBT_DELTA                  4           256

LOP_COMMIT_XACT                 4           208

LOP_LOCK_XACT                   3           120

LOP_HOBT_DDL                    2           72

LOP_PREP_XACT                   1           68

LOP_END_CKPT                    1           136

LOP_FORMAT_PAGE                 1           84

LOP_BEGIN_CKPT                  1           96

LOP_CREATE_ALLOCCHAIN           1           40

L’opérateur logique LOP_SET_FREE_SPACE est typiquement un opérateur d’allocation, qui met à jour la page PFS du fichier de donnée et indique quels sont les pages qui ne sont plus libres pour de futures allocations car elles ont été utilisées par le SELECT INTO. Si je lance la sauvegarde de mon journal :

Processed 149 pages for database 'CAPDATA', file 'CAPDATA_log01' on file 1.

BACKUP LOG successfully processed 11277 pages in 28.242 seconds (3.270 MB/sec).

La sauvegarde fait 90Mb alors qu’il n’y a que 1,5Mb de transactions dans mon journal, parce qu’elle embarque les pages de données allouées par le SELECT INTO.

Exécutons maintenant le rebuild de l’index :

ALTER INDEX PK_PIECEAPPARTEMENT ON PIECEAPPARTEMENT REBUILD WITH (SORT_IN_TEMPDB = ON)

 SQL Server Execution Times:

 CPU time = 11594 ms, elapsed time = 48860 ms.

48 secondes au lieu de 66, et dans le journal:

dbcc sqlperf(logspace)

 Database Name   Log Size (MB)     Log Space Used (%) Status

----------------------------------------------------------------------------------------------

CAPDATA         49,99219           5,8515             0

Et la sauvegarde de journal fait 91 Mb, loin des 5% utilisés. Là encore, les pages de données créées lors du rebuild sont embarquées dans la sauvegarde.

Conclusion: en mode BULK_LOGGED, pour pouvoir restaurer une opération minimalement loggée, le BACKUP LOG est obligé de prendre avec lui les pages de données allouées dans l’opération. Ici, on ne voit pas de différence de taille des sauvegardes de journal entre les modes FULL et BULK_LOGGED, mais si en plus des opérations minimalement loggées, j’ai des ordres de modifications standards type INSERT/DELETE,UPDATE, la taille de ma sauvegarde va croître (et le temps de sauvegarde avec) en BULK_LOGGED et dépasser la taille d’une sauvegarde en mode FULL. Donc attention à l’espace disponible sur les disques de sauvegardes le week end pendant la reorg !

David B.Autres articles à découvrir sur le blog :

• Règles d’installation de base (épisode 2) (David BAFFALEUF) [SqlServer]
• Modes de récupération et journal de transactions, épisode 1 (David BAFFALEUF) [SqlServer]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]

Dans ce premier épisode:

1. Qu’implique le passage de SIMPLE à COMPLET.
2. Que devient mon journal en mode COMPLET si je ne le sauvegarde pas régulièrement.

Le mode de récupération (recovery model en anglais) est une propriété de base de données qui va préciser pour celle-ci deux choses essentielles:

1. Le recyclage de l’espace consommé par les transactions validées dans son journal de transactions.
2. La possibilité ou non de pouvoir sauvegarder ces transactions validées.

Il existe trois modes de récupération: FULL, SIMPLE et BULK-LOGGED.

FULL: c’est le mode par défaut. Dans ce mode, toutes les transactions sont journalisées, même les opérations de modifications de masse (CREATE INDEX, BULK INSERT ou bcp.exe, SELECT INTO, et les opérations sur les champs TEXT). Dès l’instant où il y a eu au moins une sauvegarde complète sur la base, les transactions validées restent dans le journal. La seule façon de recycler l’espace occupé par ces transactions est de lancer régulièrement une sauvegarde du journal. Les transactions validées sont sauvegardées dans un fichier et sont alors supprimées du journal * .

SIMPLE: dans ce mode, le recyclage des transactions validées est la responsabilité du checkpoint. En plus de flusher sur disque les pages de données modifiées dans le cache de données, le checkpoint supprime les transactions validées du journal sans les sauvegarder. Conséquence de celà, je n’ai pas la possibilité de sauvegarder mes transactions en mode SIMPLE. Je ne pourrai exécuter que des sauvegardes complètes ou différentielles (ou de fichier en lecture seule).

BULK-LOGGED: c’est un mode à combiner avec le mode FULL. Dans ce mode, les opérations de modification de masse (citées plus haut) ne sont pas complètement journalisées, et l’impact de telles opérations sur le journal est limité. Le fait de changer de FULL à BULK-LOGGED ne modifie en rien ma façon de sauvegarder mes bases. En mode BULK-LOGGED, mes sauvegardes transactionnelles horaires continuent de s’exécuter sans problème. Très utile lorsqu’on lance notre réorganisation des indexes hebdomadaire car on gagne un temps fou.

Note: Attention à ne pas confondre mode de récupération FULL et sauvegarde FULL.

Pour retrouver le mode de récupération d’une base:

SELECT DATABASEPROPERTYEX('maBase','RECOVERY') as RECOVERYMODE

Pour changer le mode d’une base:

ALTER DATABASE maBase SET RECOVERY [ FULL | SIMPLE | BULK_LOGGED ]

1) Première remarque: il faudra obligatoirement (re)lancer une sauvegarde complète avant de sauvegarder le journal:
- Quand je créé ma base.
- Quand je passe de SIMPLE à FULL.

La sauvegarde de transaction a besoin d’un numéro de transaction (LSN pour Log Sequence Number) de référence pour savoir à partir de quelle transaction commencer sa sauvegarde. Lorsqu’il n’y a pas eu d’autre sauvegarde de transactions avant elle, comme c’est le cas quand je passe de SIMPLE à FULL ou quand je viens de créer ma base, elle se base sur le LSN de fin de la dernière sauvegarde complète dans le même mode. Le fonctionnement précis de la sauvegarde à chaud fera l’objet d’un autre article prochainement.

Pour le mettre en évidence, créons-nous une petite base CRASHTEST:

CREATE DATABASE CRASHTEST

on PRIMARY

(NAME='CRASHTEST_data',

FILENAME='F:\KATMAI\MSSQL10.KATMAI\MSSQL\DATA\CRASHTEST_data.mdf',

SIZE=100MB, MAXSIZE=500MB)

LOG ON

(NAME='CRASHTEST_log',

FILENAME='F:\KATMAI\MSSQL10.KATMAI\MSSQL\DATA\CRASHTEST_log.ldf',

SIZE=50MB,
MAXSIZE=500MB)

Vérifions son mode de récupération:

SELECT DATABASEPROPERTYEX('CRASHTEST','RECOVERY') as RECOVERYMODE

RECOVERYMODE
---------------------------------------------------------
FULL

Créons une petite transaction et essayons de la sauvegarder:

use CRASHTEST

create table TABLE1 (A numeric identity, B varchar(5000))

insert into TABLE1 values (replicate('b',5000))

backup log CRASHTEST to disk='C:\CRASHTEST.log1.trn' with init, stats

Msg 4214, Level 16, State 1, Line 1

BACKUP LOG cannot be performed because there is no current database backup.

J’ai donc effectivement besoin d’avoir une sauvegarde complète pour initialiser ma chaîne:

backup database CRASHTEST to disk='C:\CRASHTEST.1.bak' with init, stats

Processed 152 pages for database 'CRASHTEST', file 'CRASHTEST_data' on file 1.
100 percent processed.
Processed 1 pages for database 'CRASHTEST', file 'CRASHTEST_log' on file 1.
BACKUP DATABASE successfully processed 153 pages in 0.266 seconds (4.711 MB/sec).

OK, je retente de sauvegarder mon log:

backup log CRASHTEST to disk='C:\CRASHTEST.log1.trn' with init, stats

100 percent processed.

Processed 6 pages for database 'CRASHTEST', file 'CRASHTEST_log' on file 1.

BACKUP LOG successfully processed 6 pages in 0.161 seconds (0.283 MB/sec).

Même problème lorsque je passe de SIMPLE à FULL:

ALTER DATABASE CRASHTEST set RECOVERY SIMPLE

ALTER DATABASE CRASHTEST set RECOVERY FULL

backup log CRASHTEST to disk='C:\CRASHTEST.log2.trn' with init, stats

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.

2) SAUVEGARDEZ VOS TRANSACTIONS !

C’est un problème que l’on retrouve chez beaucoup de clients: grands comptes, PME… Il y a toujours chez eux au moins une base de données en mode de récupération FULL dont le journal n’a JAMAIS été sauvegardé. Ce n’est pas étonnant en un sens puisque FULL est le mode par défaut pour toutes les bases créées par l’utilisateur. Le résultat est un fichier LDF qui fait 80 Gb parce qu’il contient une année ou deux de transactions validées, sauvegardées dans des backup FULL depuis belle lurette, mais on le répète, en mode FULL, il n’y a qu’une sauvegarde de journal qui permet de recycler l’espace occupé par des transactions validées.

Reprenons notre base CRASHTEST, et créons un peu de volumétrie…

USE CRASHTEST

declare @cpt int

set @cpt=1

while(@cpt<=50000)

begin

insert into TABLE1 values (replicate('b',5000))

set @cpt=@cpt+1

end

go

…et vérifions quelle est la situation du journal à l’issue:

DBCC SQLPERF(LOGSPACE)

Database Name       Log Size (Mb)    Log Space Used (%)      Status
------------------  ---------------  ----------------------  --------
(...)               (...)            (...)                   (...)
CRASHTEST           253,3047         97,07731                0

Il a plus que quadruplé de volume et il est plein.

Pour récupérer l’espace sur le disque, il faudra faire un DBCC SHRINKFILE en mode TRUNCATEONLY.
Si je lance le DBCC SHRINKFILE tout de suite:

DBCC SHRINKFILE(’CRASHTEST_log’,'TRUNCATEONLY’)

Dbid        Fileid        CurrentSize        MinimumSize    UsedPages         EstimatedPages
---------   ----------- ----------------  ---------------- ---------------   ------------------
8           2            31680            6400             31680             6400

31680*8192Kb = 247,5 Mb, autant dire que presque rien n’a été fait. Celà vient du fait que le TRUNCATEONLY tronque la taille du fichier jusqu’au dernier extent alloué.
Comme mon journal est plein, le dernier extent alloué est précisément à la fin du fichier.

Donc il faut commencer par vider ce journal: je sauvegarde ma transaction :

backup log CRASHTEST to disk='C:\CRASHTEST.log3.trn' with init, stats, compression

go

100 percent processed.

Processed 31461 pages for database 'CRASHTEST', file 'CRASHTEST_log' on file 3.

BACKUP LOG successfully processed 31461 pages in 16.391 seconds (15.723 MB/sec).

Dans mon journal…

DBCC SQLPERF(LOGSPACE)

Database Name        Log Size (Mb)    Log Space Used (%)      Status
------------------   ---------------  ----------------------  --------
(...)                (...)            (...)                   (...)
CRASHTEST            247,4922         4,201521                 0

Je retrouve mon enveloppe à 247,5Mb, et cette fois elle est bien vide. Je vais pouvoir ramener mon log à sa taille d’origine:

DBCC SHRINKFILE(’CRASHTEST_log’,'TRUNCATEONLY’)

Dbid        Fileid        CurrentSize        MinimumSize    UsedPages         EstimatedPages
---------   ----------- ----------------  ---------------- ---------------   ------------------
8           2            6400               6400            6400              6400

Attention, si je choisis de rester en mode FULL, je devrai programmer des sauvegardes de transactions (à travers le SQL Agent par exemple).

Dans le cas où ce journal fait plusieurs dizaines de Gb, je choisis de le vider en passant la base en mode SIMPLE:

ALTER DATABASE CRASHTEST SET RECOVERY SIMPLE

Un checkpoint initié dès la fin de l’ALTER se chargera de vider le contenu du journal, et il ne restera plus qu’à faire le SHRINKFILE.

A suivre l’effet du BULK-LOGGED sur la taille du journal…

[ David B. ]Autres articles à découvrir sur le blog :

• SQLDIAG (épisode 1) (David BAFFALEUF) [SqlServer]
• Modes de récupération et journal de transactions, épisode 2 (David BAFFALEUF) [SqlServer]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]

La première solution est d’utiliser la « gateway Oracle pour SQL SERVER  » (15000$ par serveur).

La deuxième gratuite est d’utiliser OLE DB.

Je vais m’intéresser à la deuxième solution, vous en devinez la raison

1) créer un fichier UDL (vide puis ajouter l’extension .udl) sous C:\\Documents and Settings\\thierry\\Bureau\\client1.udl, puis double cliquer

-onglet Fournisseur : choisir le fournisseur « Microsoft OLE DB Provider for SQL Server »

s’il n’est pas présent téléchargez MDAC (merci david)
-onglet connexion : nom du serveur SQL (minsk\aquarium), utilisateur/mot de passe, base de données client1
2) sous $ORACLE_HOME\hs\admin
faire une copie du fichier inithsoledb.ora en initclient1.ora
3) configuer initclient1.ora
HS_FDS_CONNECT_INFO = »UDLFILE=C:\\Documents and Settings\\thierry\\Bureau\\client1.udl »
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = client1.trc

attention doubler \ (merci manu..)
4) configuer le fichier tnsnames.ora
CLIENT1 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=pc-thierry)(PORT=1524))
(CONNECT_DATA=(SID=CLIENT1))
(HS=OK)
)
5) configurer le fichier listener.ora
ajouter aux services de votre listener
(SID_DESC=
(SID_NAME=CLIENT1)
(ORACLE_HOME = D:\app\oracle\10.2.0\db_1)
(PROGRAM=hsolesql)
)
6) créer le database link
CREATE PUBLIC DATABASE LINK « CLIENT1″ USING ‘CLIENT1′;

7) tester la connexion
select * from sysobjects@client1;

remarque : en cas de souci, vous pouvez poser une trace.
mettre le paramètre HS_FDS_TRACE_LEVEL = 4
la trace se trouvera sous D:\app\oracle\10.2.0\db_1\hs\traceAutres articles à découvrir sur le blog :

• Installation Oracle 11gR2 64 bits sur Red Hat 5 Partie 1 (Thierry GASCARD) [Oracle]
• Installation Oracle 64 bits sur Red Hat 5 (Thierry GASCARD) [Oracle]
• Création et utilisation d’OCFS2 (Thierry GASCARD) [Oracle]
• Installation ASM sur SUSE 10 en 64 Bits avec multipathing (EMC Powerpath) (Thierry GASCARD) [Oracle]
• Utiliser ASMCMD (Thierry GASCARD) [Oracle]