Ce sujet est bien trop vaste pour un billet de blog, je me contenterai donc d’aborder le sujet.

Que suivre ?

L’objectif du suivi est double : s’assurer que le Service « disponibilité de la base de données » est offert à l’utilisateur, et Maîtriser les instances du parc afin que leur exploitation reste simple. Les points à suivre sont donc disponibilité et performance pour la partie Service, et maintient de la configuration pour la partie Maîtrise.
Disponibilité :

• L’instance tourne et accepte les connexions des utilisateurs sur les différentes bases
• Les bases utilisateurs sont pleinement opérationnelles (pas de corruption de données, pas de problème de saturation d’espace)
• Le temps de réponse des requêtes est satisfaisant (performances acceptables)

Maîtrise :

• La configuration de chaque instance/machine est connue, ainsi que son évolution dans le temps
• Chaque instance déployée est suivie

Pour répondre à ces besoins, il existe, entre autres, deux approches : Le développement interne, et les outils Microsoft.

Le développement interne
Il est possible et relativement simple de mettre en place une gestion centralisée de votre parc.
Une base de données sur une instance SQL Server, une dizaine de tables, et vous disposez d’un référentiel contenant les informations de chaque instance déployée.
Via l’utilisation de serveurs liés, vous pouvez simplement depuis votre instance de gestion consulter les données de chaque instance du parc, donc récupérer les informations de configuration, volumétrie et performance de ces instances.
Via les rapports SQL Server (construits graphiquement avec report Builder 3), vous avez la possibilité de générer des tableaux de bord reprenant les informations collectées.

Avantages :

• le référencement peut être scripté dans votre procédure de déploiement, ce qui le rend automatique.
• Votre base de gestion peut être hébergée par une instance en édition Express de SQL Server, donc pas de coût de licence.
• Puisqu’il s’agit d’un développement interne, l’ajout de fonctionnalités (gestionnaire de sauvegardes/restaurations, rapports personnalisés, ) est possible. Vous disposez en outre d’un référentiel unique, donc pas de problème de redondance d’information et de maintient de cohérence entre sources différentes.

Inconvénients :

• Outre la partie base de données, cette méthode requiert des connaissances de programmation (php, asp), ne serait-ce que pour obtenir une interface graphique descente.

Outils Microsoft
Plusieurs outils de Microsoft, intégrables à la console Management Studio, offrent des fonctionnalités intéressantes pour le suivi :

SQL Server Utilities permet de collecter et d’historiser les compteurs de performance de vos instances SQL Server.  Il offre en outre un tableau de bord orienté « état de santé de l’utilisation des instances » donnant une vue synthétique de l’utilisation des ressources de vos instances.  Plusieurs rapports fournis donnent une vue historique des performances d’une instance. SQL Server Utilities fonctionne pour des instances en version 2008R2 et supérieure.

Avec Management Policies, un modèle de  configuration est créé et peut être évalué régulièrement sur les instances du parc. Lorsque des différences entre le modèle et l’instance sont trouvées, elles peuvent être notifiée ou corrigée automatiquement. Plusieurs modèles (en fonction des versions de SQL Server, par exemple) peuvent être définis.
Le modèle peut contenir des conditions sur une instance(paramètres de configuration),  ses objets (logins, serveurs liés, endpoints, …), une base et ses objets (options de la base, tables, procédures stockées, indexes,…).
Il est donc possible par exemple de vérifier qu’aucun login autre que ceux des DBAs ne dispose du privilège sysadmin, que le nom des tables et procédures stockées des bases utilisateurs suit la politique de nommage, et qu’il existe bien une clé primaire pour chaque  table utilisateur.

Dans un Central Management Server, vous enregistrez la chaîne de connexion vers une instance SQL Server. Dans Management Studio, la liste des instances SQL Server référencées (panneau Registered Servers) peut inclure un ou plusieurs Central Management Servers. Donc lorsqu’une nouvelle instance SQL Server est déployée, il suffit de la référencer dans l’un de ces Servers pour que tous les DBAs de l’équipe puissent s’y connecter.

Le mécanisme de Job Multi Serveurs MSX/TSX vous permettra de créer un job unique par type de tâche et de le « pousser » sur toutes les instances de votre parc. Les jobs sont automatiquement mis à jour sur les instances du parc lorsque vous modifiez le job de référence, et déployer les jobs sur une nouvelle instance consiste simplement à référencer cette instance dans MSX.

Avantages :

• Ces mécanismes s’interfacent avec SQL Server Management Studio, leur utilisation est donc simplifiée.

Inconvénients :

• Vous multipliez les référentiels, il peut être délicat de les maintenir tous à jour.

Comment gérer les déploiements/suppressions d’instances ?

Que ce soit via un développement interne ou avec les outils Microsoft, si votre référentiel n’est pas à jour, le suivi n’est pas complet. Il faut donc que toute nouvelle instance soit incluse au plus tôt dans le réferentiel, et que toute instance supprimée n’y apparaisse plus.

Si vous avez industralisé le déploiement (voir article précédent Banalisation des instances), il suffit de créer un script Transact-SQL de post installation qui ajoute l’instance au référentiel (votre développement interne doit idéalement fournir une procédure stockée d’ajout d’instance, et tous les outils Microsoft présentés ci-dessus ont une interface Transact-SQL pour gérer l’ajout d’une instance).

Si le déploiement  n’est pas industralisé, il faut penser à ajouter l’instance manuellement…

La suppression doit suivre le même principe, même si dans les faits une suppression d’instance n’est pas nécessairement gérée correctement. Il est donc utile de vérifier régulièrement les instances apparaissant comme « non joignables » dans les différents outils et de les dé-référencer au plus vite.Continuez votre lecture sur le blog :

• Production SQL Server : Ordonnancement (Benjamin VESAN) [SQL Server]
• Production SQL Server : L’approche (Benjamin VESAN) [SQL Server]
• Production SQL Server : Sauvegardes (Benjamin VESAN) [SQL Server]
• Production SQL Server : Les Statistiques (Benjamin VESAN) [SQL Server]
• Production SQL Server : banalisation des instances (Benjamin VESAN) [SQL Server]

(On me glisse à l’oreillette que le terme « banalisation » n’est jamais employé dans le domaine de la base de données, mais puisque je le trouve assez bien adapté au concept que j’aborde ici, je l’utiliserai quand même !)

Par instance normalisée, j’entends instance installée selon un ensemble de règles définissant :

• L’édition et la version de SQL Server,
• l’emplacement des différents fichiers,
• le nommage de l’instance,
• le port TCP de l’instance,
• Le mot de passe du login sa et la liste des logins disposant de forts privilèges,
• La Collation de l’instance
• Le nom et le contenu de la base contenant les scripts d’exploitation,
• Le compte de service qui lance SQL Server et SQL Agent, et les privilèges de ce compte sur la machine

Par instance banalisée, j’entends instance configurée avec des valeurs fixes pour certains paramètres :

• Mémoire allouée
• Masque CPU
• Paramètres de configuration tels que xp_cmdshell, backup compression default, clr enabled, cross db ownership, cost threshold for parallelism, max degree of parallelism

L’idée est donc de mettre à disposition une instance qui aura une configuration correspondant à peu près à tous les types d’utilisation.

Pourquoi banaliser ?

• Déployer une instance banalisée est très simple. Il suffit de lancer un script de post installation pour fixer les valeurs des paramètres, qui peut être facilement inclus dans le processus de déploiement.
• Mettre en place la supervision d’une instance banalisée est aussi très simple, puisque les seuils de consommation de ressources sont les mêmes.
• Puisque les instances sont les mêmes, le DBA est toujours en environnement connu pour tout ce qui est tâches courantes d’exploitation et gestion d’incidents.

Faut-il tout banaliser ?

Bien sûr que non !

Une instance banalisée, par définition, n’est pas optimisée pour l’usage qui en est fait. Mais en nous basant sur la règle dite des 80/20, il semble réaliste de banaliser 80% des instances, ce qui permettra de concentrer la charge de travail du DBA sur les 20% des instances restantes (a priori les 20% les plus critiques).

Et concrêtement ?

Une fois vos règles de normalisation définies, utilisez l’option  « configuration file » de SQL Server Installation Center pour faciliter l’installation :

1. Lancez une installation classique en vous arrêtant à la dernière étape. Rien ne sera installé, mais un fichier de configuration sera généré.
2. Editez ce fichier en remplaçant certaines valeurs par des variables (nom d’instance, chemin des fichiers de données et de journaux, compte de service, …), afin d’en faire un modèle.

Exemple de fichier « configurationfile.modele » issu d’une installation de SQL Server 2012:
[OPTIONS]
ACTION="Install"
ENU="True"
UIMODE="Normal"
QUIET="False"
QUIETSIMPLE="False"
UpdateEnabled="False"
FEATURES=SQLENGINE
UpdateSource="MU"
HELP="False"
INDICATEPROGRESS="False"
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTANCENAME="%INSTANCE%"
INSTANCEID="%INSTANCE%"
SQMREPORTING="False"
ERRORREPORTING="False"
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
AGTSVCACCOUNT="%COMPTESERVICE%"
AGTSVCPASSWORD="%MDPCOMPTESERVICE%"
AGTSVCSTARTUPTYPE="Automatic"
COMMFABRICPORT="0"
COMMFABRICNETWORKLEVEL="0"
COMMFABRICENCRYPTION="0"
MATRIXCMBRICKCOMMPORT="0"
SQLSVCSTARTUPTYPE="Automatic"
FILESTREAMLEVEL="0"
ENABLERANU="False"
SQLCOLLATION="French_CI_AS"
SQLSVCACCOUNT="%COMPTESERVICE%"
SQLSVCPASSWORD="%MDPCOMPTESERVICE%"
SQLSYSADMINACCOUNTS="%GROUPESYSADMIN%"
SECURITYMODE="SQL"
SQLBACKUPDIR="D:\BACKUP\%INSTANCE%"
SQLUSERDBDIR="D:\DATA\%INSTANCE%"
SQLUSERDBLOGDIR="D:\TLOG\%INSTANCE%"
SQLTEMPDBDIR="D:\DATA\%INSTANCE%"
SQLTEMPDBLOGDIR="D:\TLOG\%INSTANCE%"
ADDCURRENTUSERASSQLADMIN="False"
TCPENABLED="1"
NPENABLED="0"
BROWSERSVCSTARTUPTYPE="Automatic"

3. Créez un fichier de commandes qui valorise les variables, génère à la volée un fichier de configuration puis appelle le setup.exe de SQL Server en utilisant le fichier de configuration généré.

Exemple de script d’installation « install_sql.cmd »
@echo off
del configurationfile.ini
echo Nom de l'instance à installer:
set /p INSTANCE=
echo Compte de Service windows:
set /p COMPTESERVICE=
echo Mot de passe du compte de Service windows
set /p MDPCOMPTESERVICE=
echo Groupe(s) Sysadmin:
set /p GROUPESYSADMIN=

for /f "delims=" %%i in ('type configurationfile.modele') do (
call echo %%i>>configurationfile.ini
)

I:\setup.exe /configurationfile=configurationfile.ini


4. Créez un script sql contenant toutes les modifications de configuration correspondant à votre banalisation, et lancez le après chaque installation (le lancement peut bien entendu être inclus dans le script d’installation…)

Exemple de script de banalisation « banalisation.sql »

/***************************************************************************
   ^
  / \
 / ! \	ATTENTION
/_____\

Les modifications proposées dans ce script auront un impact
sur le fonctionnement de votre instance SQL Server (performances, sécurité).
Il est donc impératif de prendre connaissance des modifications proposées et
de ne les mettre en place que lorsque les impacts sont maîtrisés.

Cap Data consulting ne saurait être tenue responsable des conséquences du
lancement de ce script sur un système.
**************************************************************************/
set nocount on
declare @commande varchar(200), @cpus int, @memoire_physique int, @MAX_DOP int, @MEMOIRE_INSTANCE int, @chemin_fichiers_tempdb nvarchar(520), @compteur int
select @cpus=cpu_count, @memoire_physique=physical_memory_in_bytes/1048576 from sys.dm_os_sys_info

SELECT	 @MAX_DOP=4				--Degré max de parallélisme
		,@MEMOIRE_INSTANCE=512 --Mémoire à allouer à l'instance

IF (@MAX_DOP>@cpus)select @MAX_DOP=@cpus
IF (@MEMOIRE_INSTANCE > @memoire_physique - 512)select @MEMOIRE_INSTANCE = @memoire_physique - 512 

-- Paramètres de l'instance
EXEC sp_configure 'show advanced options', 1
reconfigure
create table #parametres(nom nvarchar(35), valeur int)
insert into #parametres values	('max degree of parallelism', @MAX_DOP),
								('min server memory (MB)', @MEMOIRE_INSTANCE),
								('max server memory (MB)', @MEMOIRE_INSTANCE),
								('remote admin connections',1 ),
								('xp_cmdshell', 1),
								('backup compression default', 1)

DECLARE CURSEUR_PARAMETRES INSENSITIVE CURSOR FOR
select 'sp_configure '''+nom+''', '+cast(valeur as varchar(10)) from #parametres P
inner join sys.configurations C ON P.nom=C.name
WHERE P.valeur !=cast(C.value as int)
and P.valeur between cast(C.minimum as int) and cast(C.maximum as int)
order by nom 

OPEN CURSEUR_PARAMETRES
FETCH NEXT FROM CURSEUR_PARAMETRES  into @commande
WHILE(@@FETCH_STATUS=0)
BEGIN
	PRINT @commande
	EXEC(@commande)
	FETCH NEXT FROM CURSEUR_PARAMETRES  into @commande
END
CLOSE CURSEUR_PARAMETRES
DEALLOCATE CURSEUR_PARAMETRES
drop table #parametres

--Modification de TempDB
alter database tempdb modify file(name='tempdev', SIZE=128 MB, MAXSIZE = UNLIMITED, FILEGROWTH= 128 MB)
select @chemin_fichiers_tempdb=replace(physical_name,'tempdb.mdf','') from tempdb.sys.database_files where file_id=1
select @compteur=count(1)+1 FROM tempdb.sys.database_files where type_desc='ROWS'
WHILE(@compteur < =@cpus)
BEGIN
	set @commande='ALTER DATABASE tempdb ADD FILE (NAME=''tempdev_'+cast(@compteur as varchar(3))+''', FILENAME='''+@chemin_fichiers_tempdb+'tempdb_'+cast(@compteur as varchar(3))+'.ndf'', SIZE=128 MB, MAXSIZE=UNLIMITED, FILEGROWTH=128 MB)'
	PRINT @commande
	select @compteur = @compteur + 1
END

--Modification de Model
alter database model modify file(name='modeldev', MAXSIZE = UNLIMITED, FILEGROWTH= 128 MB)
alter database model modify file(name='modellog', MAXSIZE = UNLIMITED, FILEGROWTH= 128 MB)
alter database model set recovery SIMPLE

Enfin, sachez que grâce au mécanisme de « Policy Management » de SQL Server, vous disposez d’un moyen simple de vérifier régulièrement que la configuration des instances est toujours celle que vous avez défini à l’installation.
Continuez votre lecture sur le blog :

• How-To : réduire l’enveloppe de tempdb (David BAFFALEUF) [SQL Server]
• Production SQL Server : L’approche (Benjamin VESAN) [SQL Server]
• Production SQL Server : Ordonnancement (Benjamin VESAN) [SQL Server]
• Production SQL Server : Sauvegardes (Benjamin VESAN) [SQL Server]
• Production SQL Server: Réorganisation des objets (Benjamin VESAN) [SQL Server]

Cohérence d’un objet ?
Un fichier de données est constitué de pages (ensembles contigu de 8 k. octets). Lorsque SQL Server écrit une page sur disque, il calcule une signature (CHECKSUM) sur quelques octets et l’écrit dans l’entête de la page. Cette signature est recalculée lors d’une lecture et comparée à la valeur stockée dans l’entête pour garantir la cohérence.

Cet entête contient aussi plusieurs informations comme l’objet auquel elle appartient, la page qui la précède dans le chaînage de l’objet et la page qui la suit. Certaines pages spécifiques (pages IAM) listent l’ensemble des pages d’un objet.

Problème de cohérence ?
Lors de la lecture d’une page depuis le disque, la signature est calculée et comparée à la signature stockée. Si les deux valeurs sont différentes, la page est considérée non cohérente.
Si la page IAM d’un objet référence une page, et que cette page référence un autre objet, il y a incohérence.
Si une page A désigne une page B comme « suivante » dans le chaînage et que cette page B ne désigne pas la page A comme « précédente », il y a incohérence.
SQL Server refusera de lire une page incohérente et renverra une erreur (que cette lecture corresponde à un « select » ou un « backup database » par exemple…).

Et concrêtement ?
Lorsqu’une page est incohérente, certains objets ne seront plus consultables, et les tâches de maintenance critiques comme la sauvegarde ne fonctionneront plus. Il est donc important de détecter au plus vite un problème de cohérence.

Il est possible de récupérer une page corrompue sans perdre de données sous certaines conditions :
• Soit la page corrompue appartient à un indexe non clustérisé. La page ne contient donc aucune donnée, l’indexe peut être reconstruit sans risque (option REPAIR_REBUILD de la commande DBCC CHECKDB)
• Soit la base est en miroir. Le mécanisme Automatic Page Repair (à partir de SQL Server 2008 ) utilisera la base miroir pour récupérer la page corrompue.
• Soit la base est en mode de recouvrement COMPLET. Il est possible de restaurer une ou plusieurs pages (à partir de la dernière sauvegarde de base) puis de réappliquer les modifications sur ces pages (restauration des sauvegardes transactionnelles). Cette opération entraine une indisponibilité, mais elle est minime.

Donc si vos bases sont en miroir ou qu’elles sont en mode de recouvrement COMPLET, la fréquence de lancement du contrôle de cohérence peut être plus faible. Pour les autres bases, la fréquence de lancement dépend de la perte de données acceptable.

En pratique
Le lancement du contrôle est simple, puisqu’il consiste simplement à lancer la commande DBCC CHECKDB.
Le script suivant permet de lancer DBCC CHECKDB sur toutes les bases, à l’exception des Snapshots, de tempdb et des bases en lecture seule, et ne remonte que les messages d’erreur :
EXEC sp_msforeachdb 'IF (CAST(DATABASEPROPERTYEX(''?'',''Updateability'') as varchar(15)) = ''READ_WRITE'' AND ''?'' NOT IN (''tempdb''))
DBCC CHECKDB(''?'') WITH NO_INFOMSGS, ALL_ERRORMSGS
'

Un fichier de dump est créé chaque fois que DBCC CHECKDB rencontre une erreur de cohérence. La remonté d’alertes lors de corruption peut donc correspondre à l’arrivée d’un nouveau fichier SQLDUMP<Id>.txt ( où <Id> est un nombre à 4 chiffres qui s’incrémente automatiquement).Continuez votre lecture sur le blog :

• Error 8976 / 8978, problèmes de chaînage, comment récupérer les données (David BAFFALEUF) [SQL Server]
• Production SQL Server : Les Statistiques (Benjamin VESAN) [SQL Server]
• Msg 2508, Level 16, State 1: the In-Row data %% for object %% is incorrect (David BAFFALEUF) [SQL Server]
• Production SQL Server : Sauvegardes (Benjamin VESAN) [SQL Server]
• Production SQL Server: Réorganisation des objets (Benjamin VESAN) [SQL Server]

Même s’il est possible de le faire, cela procure un travail considérable et un souci de fragmentation.

Lorsque l’on effectue des inserts/update, il est nécessaire de synchroniser la table $I.

Un article intéressant : http://www.oracle.com/technetwork/database/enterprise-edition/text-dml-processing-092316.html

1) Activité DML

a)  Lors de l’INSERT

Lorsque qu’une ligne est ajoutée dans la table utilisateur, une ligne contenant INDEX_ID et ROWID est ajoutée dans la table CTXSYS.DR$PENDING.

SQL>  insert into test_tab values ('ef');
SQL> select * from CTXSYS.DR$PENDING;
PND_CID    PND_PID PND_ROWID          PND_TIMES P
---------- ---------- ------------------ --------- -
1080          0 AAARvbAAFAAAADMAAA 29-MAR-12 N -- valeur précédente
1080          0 AAARvbAAFAAAADMAAB 29-MAR-12 N -- valeur précédente
1080          0 AAARvbAAFAAAADMAAC 30-MAR-12 N -- nouvelle valeur

Rem : si une modification est faite sur la même ligne, la ligne est placée dans CTXSYS.DR$WAITING

SQL> update test_tab set CLOB_COL='fg' where ROWID='AAARvbAAFAAAADMAAC';
SQL> select * from CTXSYS.DR$WAITING ;
WTG_CID WTG_ROWID             WTG_PID
---------- ------------------ ----------
1080 AAARvbAAFAAAADMAAC          0
SQL> commit;

En conclusion :

- aucune synchronisation de l’index Oracle n’est effectuée lors du COMMIT qui clôt l’INSERT

- même si la valeur de la colonne indexée est nulle, la ligne est ajoutée dans CTXSYS.DR$PENDING

- si la ligne est déjà en traitement dans CTXSYS.DR$PENDING, celle-ci est ajoutée dans CTXSYS.DR$WAITING

b) Lors d’un DELETE  :

Lors du commit qui clôt le DELETE:

- la ligne correspondante avec le ROWID est immédiatement supprimée de DR$<index_name>$K :

- une recherche sur ROWID en utilisant   DR$< index_name>$K ne trouve plus la ligne

- une ligne avec ROWID et INDEX_ID est insérée dans CTXSYS.DR$DELETE

- un SELECT dans la session courante ne voit plus la ligne avant le commit

- permet lors du « commit callback » de supprimer les lignes correspondantes de  DR$< index_name>$R

- une ligne avec ROWID est insérée dans DR$<index_name>$N

- permet la suppression des DOCID dans la table  DR$<index_name>$I lors d’une optimisation

En conclusion :

Un DELETE est immédiatement pris en compte :

- dans la session courante : on ne voit plus la ligne

- dans une autre session : on ne voit plus la ligne au commit

c) Lors d’un UPDATE

Il agit comme un DELETE suivi d’un INSERT.

En conclusion :

- seule la partie DELETE sera visible après le commit

- tout UPDATE sur la colonne entraine la non visibilité du document

d) Lors du COMMIT

Au commit , un « commit callback » est généré :

- pour chaque DOCID de DR$DELETE, une mise à jour de la colonne LOB de  DR$< index_name>$R est effectuée pour le ROWID

- la ligne est ensuite effacée de DR$DELETE

2) Mise en place de la synchronisation Oracle TEXT

Lorsque l’on effectue des inserts/update, il est nécessaire de synchroniser la table $I.

Il existe plusieurs possibilités  :

1) En manuel via ctx_ddl.sync_index

– utilisation d’une mémoire de 256M avec un parallélisme 2

exec ctx_ddl.sync_index('TEST_IDX','256M',NULL,2);

Rem : ne pas utiliser Alter Index … Rebuild Online Parameters (’sync’)

2) Sinon, il est possible à partir de la 10g de placer les paramètres SYNC EVERY ‘interval’ (via DBMS_SCHEDULER)

SQL> ALTER INDEX test_idx REBUILD ONLINE  PARAMETERS (' REPLACE sync (every "sysdate+(1/24)" MEMORY 64M PARALLEL 2)') PARALLEL 2 ;

ou mieux via METADATA car évite le rebuild de l’index

SQL> ALTER INDEX test_idx REBUILDPARAMETERS (' REPLACE METADATA sync (every "sysdate+(1/24)" MEMORY 64M PARALLEL 2)') ;
-- création d'un job via DBMS_SCHEDULER
SQL> select JOB_ACTION from USER_SCHEDULER_JOBS where job_name='DR$TEST_IDX$J';
JOB_ACTION
-------------------------------------------------------------------------------------------------------------------

ctxsys.drvdml.auto_sync_index('TEST_IDX', 67108864, NULL, 2, NULL, 0);

3) Une synchronisation peut avoir lieu au COMMIT

Peut être intéressant pour un commit de plusieurs milliers de lignes

Le COMMIT est envoyé une fois que la synchronisation est effectuée.

SQL>create index test_idx on test_tab (clob_col) indextype is ctxsys.context parameters ('sync (on commit)');
-- ou

SQL>ALTER INDEX test_idx REBUILD ONLINE  PARAMETERS ('REPLACE METADATA MEMORY 64M sync (on commit)') PARALLEL 2 ;

4) Sinon il est possible d’utiliser le paramètre TRANSACTIONAL

Ce paramètre permet de voir les INSERT/DELETE validées en utilisant les tables DR$PENDING DR$WAITING et en effectuant une indexation à la volée

SQL>ALTER INDEX test_idx REBUILD ONLINE  PARAMETERS ('REPLACE METADATA transactional');
SQL> select * from test_tab where CONTAINS(clob_col,'ab') > 0;
no rows selected
SQL> insert into test_tab values ('ab');
SELECT pnd_index_name, pnd_rowid , TO_CHAR(pnd_timestamp, 'dd-mon-yyyyhh24:mi:ss') timestamp
FROM ctx_user_pending;
PND_INDEX_NAME                 PND_ROWID          TIMESTAMP
------------------------------ ------------------ -------------------
TEST_IDX                       AAARvbAAFAAAADPAAA 03-apr-201216:40:11
SQL> select * from test_tab where CONTAINS(clob_col,'ab') > 0;
CLOB_COL
--------------------------------------------------------------------------------
ab

Il est possible pour des questions de performances de désactiver cette fonctionnalité dans la session par une variable PL/SQL :

SQL> exec ctx_query.disable_transactional_query := TRUE;
SQL> select * from test_tab where CONTAINS(clob_col,'ab') > 0;
no rows selected
Pour la résactiver :
SQL> exec ctx_query.disable_transactional_query := FALSE;

Sinon, il est possible de modifier l’index Oracle Text :

SQL> ALTER INDEX test_idx REBUILD ONLINE PARAMETERS ('REPLACE METADATA nontransactional');

2) Gérer la synchronisation

a) Pour optimiser la synchronisation, vous pouvez utiliser :

- le parallélisme en Entreprise Edition

- la clause memory avec une limite à 1 Go en 11gR2

–  modification possible de la valeur maximale :

exec ctxsys.ctx_adm.set_parameter('MAX_INDEX_MEMORY','2147483647');

b) la clause transactional a un surcout en performance

- nécessité de lire la table dr$unindexed

c) le mot clé REBUILD METADATA évite la reconstruction de l’index

Dans la troisième partie, nous allons continuer la gestion d’un index Oracle TEXT.

Continuez votre lecture sur le blog :

• Oracle Text pour DBA Oracle : Partie 1 (Thierry GASCARD) [Oracle]
• Création d’un Dataguard physique (Guillaume DEFENDINI) [Oracle]
• Production SQL Server: Réorganisation des objets (Benjamin VESAN) [SQL Server]
• Oracle, les Rpms plus de souci avec YUM (Thierry GASCARD) [Oracle]
• Suppression accidentelle de ligne : comment retrouver le coupable ? (David BAFFALEUF) [SQL Server]

1) Vérification de l’installation d’ Oracle Text

 SELECT * FROM ctxsys.ctx_version;

  conn SYS/syspasswd AS SYSDBA
  @?/ctx/admin/catctx.sql mypasswd SYSAUX TEMP NOLOCK

conn CTXSYS/mypasswd
@?/ctx/admin/defaults/drdefus.sql

select space_used_kbytes from v$sysaux_occupants where occupant_name='TEXT';

execute ctxsys.dri_move_ctxsys(tbs_name=>'TBS_CONTEXT');

2) Création d’un index Oracle Text simple

create tablespace TBS_TEXT datafile '+DATA' size 10M autoextend on maxsize 200M

 extent management local uniform size  64K;

CREATE USER user_text IDENTIFIED BY text DEFAULT TABLESPACE TBS_TEXT

TEMPORARY TABLESPACE temp PROFILE default QUOTA UNLIMITED ON TBS_TEXT;

GRANT EXECUTE ON ctxsys.ctx_ddl TO user_text;
GRANT CTXAPP TO user_text;
GRANT CREATE PROCEDURE TO user_text;
GRANT CREATE SESSION TO user_text;
GRANT CREATE TABLE TO user_text;
GRANT SELECT ANY DICTIONARY TO user_text;
GRANT CREATE JOB to user_text;
GRANT CREATE TRIGGER to user_text;

CONNECT user_text/text
create table test_tab (clob_col clob);
create index test_idx on test_tab (clob_col) indextype is ctxsys.context;
commit;
insert into test_tab values ('c a b d');
insert into test_tab values ('ab');
commit;

select * from test_tab where CONTAINS(clob_col,'ab') > 0;

3) Structure interne

Au niveau table :

select table_name,IOT_TYPE, tablespace_name from USER_TABLES ;
TABLE_NAME                     IOT_TYPE     TABLESPACE_NAME
------------------------------ ------------ ------------------------------
DR$TEST_IDX$K                  IOT
DR$TEST_IDX$N                  IOT
DR$TEST_IDX$R                               TBS_TEXT
DR$TEST_IDX$I                               TBS_TEXT
TEST_TAB                                    TBS_TEXT

Au niveau des index :

select index_name,table_name, tablespace_name,INDEX_TYPE from user_indexes
where INDEX_TYPE in ('DOMAIN','NORMAL','IOT - TOP')
INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                INDEX_TYPE
------------------------------ ------------------------------ ------------------------------ ----------
TEST_IDX                       TEST_TAB                                                      DOMAIN
SYS_IOT_TOP_72679              DR$TEST_IDX$N                  TBS_TEXT                       IOT - TOP
SYS_IOT_TOP_72674              DR$TEST_IDX$K                  TBS_TEXT                       IOT - TOP
DR$TEST_IDX$X                  DR$TEST_IDX$I                  TBS_TEXT                       NORMAL

Au niveau des colonnes LOB :

SELECT table_name "Table", column_name "Column", segment_name "Segment" , index_name "Index"
FROM user_lobs
WHERE table_name like 'DR$%';
Table                          Column       Segment                        Index
------------------------------ ------------ ------------------------------ ---------------------
DR$TEST_IDX$I                  TOKEN_INFO   SYS_LOB0000072671C00006$      SYS_IL0000072671C00006$
DR$TEST_IDX$R                  DATA         SYS_LOB0000072676C00002$      SYS_IL0000072676C00002$

Conclusion :

Continuez votre lecture sur le blog :

• Oracle Text pour DBA Oracle : Partie 2 (Thierry GASCARD) [Oracle]
• Regénérer le DDL des indexes FULL TEXT (David BAFFALEUF) [SQL Server]
• Error 8976 / 8978, problèmes de chaînage, comment récupérer les données (David BAFFALEUF) [SQL Server]
• Bench avec NetApp / Datacore / ESX (David BAFFALEUF) [SQL Server]
• Msg 2508, Level 16, State 1: the In-Row data %% for object %% is incorrect (David BAFFALEUF) [SQL Server]

Qu’est-ce que la fragmentation ?

Lorsqu’un objet (table ou indexe) subit des modifications, ses pages peuvent se vider partiellement (on parle de fragmentation interne) et certaines pages, voire certaines extensions (groupes de 8 pages contigües) peuvent se vider complètement (il s’agit alors de fragmentation externe).

Quel est l’impact sur les performances ?

Sur disque
Pour lire une donnée sur disque physique, il faut :

Déplacer la tête de lecture du disque. Cette opération est longue, de l’ordre de 5 millisecondes en moyenne

Attendre que le secteur de données à lire passe sous la tête. Cette opération prend en moyenne entre 2 et 6 millisecondes (un demi-tour du disque, à une vitesse de rotation entre 5000 et 15000 tr/min)

Lorsque les secteurs à lire sont contigus (c.-à-d. placés physiquement à la suite les uns des autres), la tête de lecture n’a plus à se déplacer ; lire 100 secteurs contigus prend donc à peine plus de temps que d’en lire un seul (disons 5ms pour la tête+ 4ms pour le demi tour + 1ms pour la lecture des 100 secteurs=10ms contre 100x(6 + 4)=1000ms pour des secteurs non contigus).

Si l’objet est fragmenté, les déplacements de la tête de lecture seront plus fréquents, les temps de lecture seront donc (beaucoup) plus longs.

En mémoire
Lorsqu’un objet n’est pas fragmenté, ses pages sont pleines. L’objet occupe donc moins de place que s’il était fragmenté. Un objet fragmenté occupera donc plus de place en mémoire qu’un objet non fragmenté, au détriment des autres objets.

Pour l’Optimizer
Lorsque SQL Server calcule le coût d’une opération, il distingue une lecture séquentielle (considérée peu coûteuse) et une lecture aléatoire (considérée très coûteuse). Si un objet est fortement fragmenté, les lectures séquentielles deviennent plus coûteuses que ce qu’a estimé SQL Server, le plan d’exécution choisi n’est donc potentiellement plus optimal.

Mesurer la fragmentation

Depuis la version 2005 de SQL Server, le taux de fragmentation est directement consultable via une vue dynamique de gestion.

select object_name(I.object_id) 'Table', I.name 'Indexe', IPS.avg_fragmentation_in_percent 'Taux de Fragmentation'

FROM sys.dm_db_index_physical_stats(DB_ID(),DEFAULT, DEFAULT, DEFAULT,DEFAULT) IPS

inner join sys.indexes I ON I.object_id = IPS.object_id AND I.index_id = IPS.index_id

where IPS.index_level=0

and IPS.index_id !=0

order by 1,IPS.index_id,2

Comment défragmenter ?

Défragmenter un objet consiste à le compacter et à déplacer ses pages de manière à ce qu’elles soient contigües. L’action de défragmenter est la réorganisation, elle est gérée dans SQL Server par deux ordres :

ALTER INDEX REBUILD
--et
ALTER INDEX REORGANIZE

Lorsqu’une table ne contient pas d’indexe clusterisé, il faut soit l’exporter, la vider puis la réimporter (attention aux contraintes référentielles qui pourraient empêcher ces opérations), soit créer un indexe clusterisé puis le supprimer.

Lorsqu’elle en contient un, la méthode de défragmentation dépendra du taux de fragmentation observé.

(les valeurs ci-dessous sont relativement empiriques)

Taux <5% Pas de défragmentation. La consommation des ressources liée à la défragmentation serait a priori supérieure au gain en performance résultant.

Taux compris entre 5 et 25% ALTER INDEX <nom de l’indexe> ON <nom de la table> REORGANIZE . La défragmentation s’opérera uniquement au niveau des feuilles de l’indexe/la table, mais sera relativement peu consommatrice en ressources.

Taux supérieur à 25% ALTER INDEX <nom de l’indexe> ON <nom de la table> REBUILD.

Cette méthode va reconstruire intégralement l’indexe/la table, opération consommatrice mais aboutissant à une table complètement défragmentée.

A noter que :

• La défragmentation est un processus très consommateur en ressources (disque et mémoire), il est donc à lancer en dehors des fortes périodes d’activité.
• La défragmentation via ALTER INDEX REBUILD va mettre automatiquement à jour les statistiques. Inutile donc d’effectuer à nouveau cette mise à jour dans votre tâche de maintenance de mise à jour des stats (cf article 4).
• En édition Enterprise, il est possible de réduire l’impact de la défragmentation en utilisant l’option ONLINE.
• Le taux de fragmentation des tables de très petite volumétrie ne pourra être réduit à 0 (cf cet article), c’est tout à fait normal !

Voici un exemple de procédure stockée permettant de défragmenter les indexes d’une base :

USE outils_dba

GO

IF OBJECT_ID('defragmente_base') IS NOT NULL

DROP PROCEDURE defragmente_base

GO

CREATE PROCEDURE defragmente_base (	@nom_base sysname,

@online varchar(3)='NON'

)

AS

/***************************************************************************************

B. Vesan, Cap Data Consulting, Février 2012

Cette procedure permet de défragmenter les indexes d'une base.

0<Taux<5	Pas de défragmentation

5<=Taux<25	REORGANIZE

25<=Taux	REBUILD

Si la valeur du paramètre @online vaut 'OUI', la reconstruction sera effectuée avec l'

option ONLINE=ON

La procédure retournera 0 en cas de succès, 1 en cas d'échec.

***************************************************************************************/

BEGIN

SET NOCOUNT ON

DECLARE @cmd varchar(4000),@msg varchar(1000), @option varchar(100), @erreur int

CREATE TABLE #table_defrag(commande varchar(4000))

IF DB_ID(@nom_base) IS NULL

BEGIN

SET @msg = 'Echec de l'' exécution de defragmente_base : La base '+ISNULL(@nom_base,'(valeur nulle)')+' n''existe pas'

RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG

return 1

END

IF ( DATABASEPROPERTYEX(@nom_base,'Status ')!='ONLINE' OR EXISTS (select 1 FROM sys.databases WHERE name=@nom_base AND source_database_id IS NOT NULL))

BEGIN

SET @msg = 'La base '+@nom_base+' n''est pas disponible ou il s''agit d''un snapshot'

PRINT @msg

return 0

END

set @option = (CASE UPPER(@online) WHEN 'OUI' THEN ' WITH ( ONLINE = ON )' ELSE '' END)

set @cmd='USE ['+@nom_base+']

select ''ALTER INDEX [''+I.name+''] ON ['+@nom_base+'].[''+S.name+''].[''+T.name+''] ''+

(CASE WHEN IPS.avg_fragmentation_in_percent >= 25 THEN ''REBUILD '+@option+''' ELSE ''REORGANIZE '' END)

FROM sys.dm_db_index_physical_stats(DB_ID(),DEFAULT, DEFAULT, DEFAULT,DEFAULT) IPS

inner join sys.indexes I ON I.object_id = IPS.object_id AND I.index_id = IPS.index_id

inner join sys.tables T ON T.object_id = I.object_id

inner join sys.schemas S ON S.schema_id = T.schema_id

where IPS.index_level=0

and IPS.index_id !=0

and IPS.avg_fragmentation_in_percent >=5

AND alloc_unit_type_desc NOT IN(''LOB_DATA'')

order by object_name(I.object_id),IPS.index_id'

--PRINT @cmd

INSERT INTO #table_defrag

EXECUTE(@cmd)

DECLARE CURSEUR_INDEXES INSENSITIVE CURSOR FOR

select commande from #table_defrag

OPEN CURSEUR_INDEXES

FETCH NEXT FROM CURSEUR_INDEXES INTO @cmd

WHILE(@@FETCH_STATUS=0)

BEGIN

--	PRINT @cmd

EXECUTE(@cmd)

select @erreur=@@ERROR

IF @erreur !=0

BEGIN

SET @msg = 'Echec de l'' exécution de defragmente_base : Erreur '+CAST(@erreur as varchar(5))+' lors de l''appel à la commande "'+@cmd+'"'

RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG

CLOSE CURSEUR_INDEXES

DEALLOCATE CURSEUR_INDEXES

DROP TABLE #table_defrag

return 1

END

FETCH NEXT FROM CURSEUR_INDEXES INTO @cmd

END

CLOSE CURSEUR_INDEXES

DEALLOCATE CURSEUR_INDEXES

DROP TABLE #table_defrag

END

GO

Continuez votre lecture sur le blog :

• Production SQL Server : Les Statistiques (Benjamin VESAN) [SQL Server]
• Production SQL Server : Sauvegardes (Benjamin VESAN) [SQL Server]
• Production SQL Server : Contrôle de cohérence (Benjamin VESAN) [SQL Server]
• Production SQL Server : banalisation des instances (Benjamin VESAN) [SQL Server]
• Regénérer le DDL des indexes FULL TEXT (David BAFFALEUF) [SQL Server]

A+. David.Continuez votre lecture sur le blog :

• Journées SQL Server à Paris les 12 et 13 décembre 2011 ! (David BAFFALEUF) [SQL Server]
• How-To : réduire l’enveloppe de tempdb (David BAFFALEUF) [SQL Server]
• Une approche pragmatique de la production SQL Server (Benjamin VESAN) [SQL Server]
• Production SQL Server: Suivi et Contrôle du parc (Benjamin VESAN) [SQL Server]
• Journées SQL Server 12/13 décembre (suite) (David BAFFALEUF) [SQL Server]

Quand SQL Server met-il à jour les statistiques ?

• Lors de la réorganisation d’un indexe. Les statistiques sont dans ce cas mises à jour à partir de l’intégralité des pages de l’indexe (mode FULLSCAN)
• Lorsque la base est en mode “auto_update_statistics”, que le seuil d’obsolescence d’une statistique a été atteint (article MSDN sur le sujet) et que la compilation d’une requête a besoin de cette statistique.

Faut-il mettre à jour les statistiques manuellement ?
Oui et Non !

Non, lorsque les bases sont de faible volumétrie et que l’activité transactionnelle n’est pas élevée; la gestion automatique des statistiques suffit amplement.

Oui, lorsque l’on sait que les statistiques ne sont plus représentatives bien avant le seuil des 20% (données réparties de manière  non homogène dans certaines tables), ou lorsque l’on ne peut pas se permettre d’ajouter à une compilation le temps mis à mettre à jour les statistiques.
Dans le cadre d’un parc d’instances SQL Server, il est intéressant de disposer d’une tâche de gestion des statistiques exécutée sur toutes les instances qui mettra à jour ou non les statistiques selon quelques règles.

Quelles statistiques mettre à jour ?
Chaque base de données peut contenir des données très volatiles et d’autres statiques, comme par exemple des tables d’historique, souvent volumineuses. Certaines tables peuvent être des tables de travail, régulièrement purgées, pour lesquelles les statistiques ne seront jamais pertinentes.
Il faut donc trouver un mécanisme capable d’identifier les tables de travail et les tables qui n’ont pas subi de modification de données (depuis la dernière mise à jour).

Lorsqu’une donnée est mise à jour dans une table, la valeur de la colonne rowmdctr de la table système sysindexes s’incrémente(MSDN: sysindexes). Cette colonne est remise à zéro lorsque les statistiques sont mises à jour. Récupérer la liste des statistiques qui ne sont pas totalement à jour consiste donc simplement à remonter les lignes de sysindexes à l’exception de celles pour lesquelles rowmodctr vaut 0 !
(Comme son nom ne l’indique pas, sysindexes contient une entrée pour chaque indexe mais aussi pour chaque statistique de la base).

La mise à jour peut s’effectuer sur l’intégralité de l’objet (FULLSCAN) ou sur un échantillon (SAMPLE). La première méthode est plus longue que la seconde puisque les pages balayées sont plus nombreuses. Il existe un risque, même s’il est faible, qu’une statistique créée à partir d’un échantillon ne soit pas représentative (le mécanisme de statistiques filtrées de SQL Server 2008 peut aider à contourner ce problème). Pour les tables de faible volumétrie, la mise à jour en FULLSCAN est préférable. Pour les plus gros objets, le mode SAMPLE peut être utilisé, mais tâchez d’être toujours au moins à 20% de pages balayées.

Attention, la mise à jour des statistiques a un double impact sur les performances:
La lecture des pages de chaque objet s’accompagne d’une pose de verrous partagés, ils entrent donc en concurrence avec les accès en modification sur l’objet.
Les plans d’exécution en cache qui utilisent les statistiques mis à jour sont invalidés. Les prochaines exécutions de requêtes passeront donc nécessairement par la phase de compilation.
il est donc préférable d’effectuer cette mise à jour lors d’une période de faible activité.

Voici un exemple de procédure stockée de mise à jour de statistiques qui repose sur ces quelques règles :

USE outils_dba
GO
IF OBJECT_ID('maj_statistiques_base') IS NOT NULL
DROP PROCEDURE maj_statistiques_base
GO
CREATE PROCEDURE maj_statistiques_base ( @nom_base sysname,
@complet varchar(3)='NON'
)
AS
/***************************************************************************************
B. Vesan, Cap Data Consulting, Mars 2012
Cette procedure permet de mettre à jour les statistiques d'une base.

Si la valeur du paramètre @complet vaut 'OUI', l'intégralité des statistiques sera mise à jour.
Sinon, seules les statistiques sur les tables ayant reçu des mouvements seront mises à jour.

La procédure retournera 0 en cas de succès, 1 en cas d'échec.

***************************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @cmd varchar(4000),@msg varchar(1000), @erreur int
CREATE TABLE #table_stats(commande varchar(4000))

IF DB_ID(@nom_base) IS NULL
BEGIN
SET @msg = 'Echec de l'' exécution de maj_statistiques_base : La base '+ISNULL(@nom_base,'(valeur nulle)')+' n''existe pas'
RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG
return 1
END

IF ( DATABASEPROPERTYEX(@nom_base,'Status ')!='ONLINE' OR EXISTS (select 1 FROM sys.databases WHERE name=@nom_base AND source_database_id IS NOT NULL))
BEGIN
SET @msg = 'La base '+@nom_base+' n''est pas disponible ou il s''agit d''un snapshot'
PRINT @msg
return 0
END

set @cmd = 'USE ['+@nom_base+']
select ''UPDATE STATISTICS ['+@nom_base+'].[''+SCH.name+''].[''+T.name+''](''+SI.name+'') WITH RESAMPLE''
from sysindexes SI
inner join sys.tables T ON T.object_id = SI.id
inner join sys.stats S ON (S.object_id = SI.id AND S.stats_id=SI.indid)
inner join sys.schemas SCH ON (T.schema_id = SCH.schema_id)'
if UPPER(@complet) ='NON' set @cmd = @cmd + ' WHERE SI.rowmodctr 0'

--PRINT @cmd
INSERT INTO #table_stats
EXECUTE(@cmd)

DECLARE CURSEUR_INDEXES INSENSITIVE CURSOR FOR
select commande from #table_stats 

OPEN CURSEUR_INDEXES
FETCH NEXT FROM CURSEUR_INDEXES INTO @cmd

WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT @cmd
EXECUTE(@cmd)
select @erreur=@@ERROR
IF @erreur !=0
BEGIN
SET @msg = 'Echec de l'' exécution de maj_statistiques_base : Erreur '+CAST(@erreur as varchar(5))+' lors de l''appel à la commande "'+@cmd+'"'
RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG
CLOSE CURSEUR_INDEXES
DEALLOCATE CURSEUR_INDEXES
DROP TABLE #table_stats
return 1
END
FETCH NEXT FROM CURSEUR_INDEXES INTO @cmd
END
CLOSE CURSEUR_INDEXES
DEALLOCATE CURSEUR_INDEXES
DROP TABLE #table_stats
END
GO

Continuez votre lecture sur le blog :

• Production SQL Server: Réorganisation des objets (Benjamin VESAN) [SQL Server]
• Production SQL Server : Sauvegardes (Benjamin VESAN) [SQL Server]
• Production SQL Server : banalisation des instances (Benjamin VESAN) [SQL Server]
• Production SQL Server : Contrôle de cohérence (Benjamin VESAN) [SQL Server]
• Regénérer le DDL des indexes FULL TEXT (David BAFFALEUF) [SQL Server]

	SÉMINAIRE INTERNET Le 3 Avril 2012 à 10H00 depuis votre PC Durée: 1H15	Concepts et notions de base de haute disponibilité Présentation des solutions de Haute Disponibilité : Réplication, Cluster, Virtualisation, Cloud (SQL Azure), HADR-AG SQL Server 2012. Quelle solution pour quelle utilisation…Avec quels avantages et inconvénients ? Profitez gratuitement de notre retour d’expérience terrain et de notre éclairage technique objectif !

Continuez votre lecture sur le blog :

• Production SQL Server: Suivi et Contrôle du parc (Benjamin VESAN) [SQL Server]
• Production SQL Server : Sauvegardes (Benjamin VESAN) [SQL Server]
• Journées SQL Server 12/13 décembre (suite) (David BAFFALEUF) [SQL Server]
• Réunion GUSS le 1er juillet ! (David BAFFALEUF) [SQL Server]
• Abonnez-vous au blog de la CapData team ! (Cédric PEINTRE) [GénéralMySQLOracleSQL ServerSybase]

L’idée est de trouver une politique applicable à toutes les instances du parc SQL Server.

Quel type de sauvegardes ?
Le parc pouvant être composé de bases à vocations diverses, il est nécessaire de couvrir les activités principales: forte activité transactionnelle, forte volumétrie, bases d’archive, bases en miroir.
D’expérience, une politique de type “Sauvegarde complète hebdomadaire + Sauvegarde différentielle quotidienne + Sauvegarde de journaux de transactions toutes les demi heures” convient en général très bien.
L’emplacement et le nom des fichiers de sauvegarde suivront idéalement la règle établie (cf Article 1). La politique de nommage <nom de la base>_<type de sauvegarde>_<date>_<heure>.BAK permet d’identifier facilement un fichier de sauvegarde.

Quelles options pour la sauvegarde ?
Depuis la version 2008 R2, la compression de sauvegardes est disponible pour les éditions Enterprise et Standard. Vous gagnerez du temps et de l’espace disque en utilisant cette option, elle est donc fortement recommandée.
Profitez de la sauvegarde pour vérifier l’intégrité de vos pages. C’est automatiquement le cas lors des sauvegardes compressées.
Préférez l’utilisation des fichiers uniques aux Media Sets (qui permettent de stocker plusieurs sauvegardes dans un même fichier), ces derniers peuvent être problématiques lors du passage sur bande des fichiers (le mediaset entier sera passé sur bande intégralement dès qu’une sauvegarde sera ajoutée).

Pour une utilisation simplifiée, créez un unique script (ou procédure stockée) pour gérer les différents types de sauvegarde d’une base. Ce script doit permettre la sauvegarde complète, différentielle et de journaux, et gérer les cas de bases non disponibles et de bases fraîchement créées (sur lesquelles une sauvegarde différentielle ou de journaux est impossible tant qu’une sauvegarde complète n’a pas été effectuée). Idéalement, le script proposera la sauvegarde de type “COPY_ONLY”, très pratique lorsqu’il s’agit de recopier les bases d’une instance sur une autre instance.

Voici un exemple de procédure stockée de sauvegarde qui prend en charge ces fonctionnalités:

EXEC sp_configure 'show advanced options',1
reconfigure

EXEC sp_configure 'xp_cmdshell',1
reconfigure
GO

IF OBJECT_ID('sauvegarde_base') IS NOT NULL
DROP PROCEDURE sauvegarde_base
GO

CREATE PROCEDURE sauvegarde_base (
@nom_base sysname,
@type_sauvegarde varchar(50) = 'COMPLET',
@repertoire_destination varchar(500) = 'DEFAULT'
)
AS
/***************************************************************************************
B. Vesan, Cap Data Consulting, Février 2012
Cette procedure permet de sauvegarder une base de données ou son journal transactionnel.
@type_sauvegarde (type de la sauvegarde ) peut valoir:
COMPLET pour une sauvegarde complète, option par défaut
COPY_ONLY pour une sauvegarde complète sans interrompre la séquence des sauvegardes
DIFFERENTIEL pour une sauvegarde différentielle
LOG pour une sauvegarde du journal de transactions.
@repertoire_destination contient le chemin accueillant la sauvegarde.
un répertoire est créé dans le chemin spécifié
Lorsque sa valeur n'est pas précisée, la sauvegarde s'effectuera sur l'emplacement spécifié dans
la table "parametre", dont la définition peut être trouvée ici:

http://blog.capdata.fr/index.php/table_parametre

Le format des fichiers est:
<nom de la base>_<type de sauvegarde>_<date>_<heure>.BAK
ex: RMLBD_COMPLET_20120201_131738.BAK
La procédure retournera 0 en cas de succès, 1 en cas d'échec.
***************************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @cmd varchar(4000),@prefixe_fichier varchar(100),@destination varchar(4000),/*@compteur int,*/@code_retour int,@msg varchar(1000),@fichier_source varchar(500)
CREATE TABLE #TABLE_OUTPUT(ligne varchar(500))
CREATE TABLE #TABLE_SOURCE (source varchar(500),cpt int)
CREATE TABLE #TABLE_DESTINATION (destination varchar(500),cpt int)

IF upper(@type_sauvegarde) NOT IN ('COMPLET','DIFFERENTIEL','COPY_ONLY','LOG')
BEGIN
SET @msg = 'Echec de l'' exécution de sauvegarde_base : le type de sauvegarde '+ISNULL(@type_sauvegarde,'(valeur nulle)')+' n''est pas reconnu'
RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG
return 1
END

IF DB_ID(@nom_base) IS NULL
BEGIN
SET @msg = 'Echec de l'' exécution de sauvegarde_base : La base '+ISNULL(@nom_base,'(valeur nulle)')+' n''existe pas'
RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG
return 1
END

IF ( DATABASEPROPERTYEX(@nom_base,'Status ')!='ONLINE' OR EXISTS (select 1 FROM sys.databases WHERE name=@nom_base AND source_database_id IS NOT NULL))
BEGIN
SET @msg = 'La base '+@nom_base+' n''est pas disponible ou il s''agit d''un snapshot'
PRINT @msg
return 0
END

IF @repertoire_destination = 'DEFAULT'
BEGIN
IF EXISTS (SELECT * FROM outils_dba.sys.tables where name='parametres')
BEGIN
SELECT @repertoire_destination = CAST( valeur AS varchar(500)) FROM dbo.parametres WHERE nom_parametre='repertoire_sauvegarde' AND cible=@nom_base
IF @repertoire_destination = 'DEFAULT' SELECT @repertoire_destination = CAST( valeur AS varchar(500)) FROM dbo.parametres WHERE nom_parametre='repertoire_sauvegarde' AND cible IS NULL
END
ELSE
BEGIN
SET @msg = 'Echec de l'' exécution de sauvegarde_base : Aucune destination n''est définie pour la base '+ @nom_base
RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG
return 1
END
END

----------- Si aucune sauvegarde complète n'existe pour la base et que la sauvegarde demandée est une Diff ou TLog, il faut demander une sauvegarde complète:

IF ((upper(@type_sauvegarde) NOT IN ('COMPLET','COPY_ONLY')) AND NOT EXISTS (select 1 from msdb.dbo.backupset where database_name=@nom_base and type='D' and is_copy_only = 0))

EXEC sauvegarde_base @nom_base, 'COMPLET', @repertoire_destination

----------- Gérération de la chaîne de caractères correspondant à la destination
set @prefixe_fichier = REPLACE(@nom_base,' ','_') + '_' + upper(@type_sauvegarde) + '_'+ convert(varchar(8),getdate(),112) + '_' + RIGHT('0'+CONVERT(varchar(2),datepart(hh,getdate())),2)+ RIGHT('0'+CONVERT(varchar(2),datepart(mi,getdate())),2)+RIGHT('0'+CONVERT(varchar(2),datepart(ss,getdate())),2)
set @cmd = 'mkdir "'+@repertoire_destination + '\'+REPLACE(@nom_base,' ','_')+'"'

-- Le répertoire est créé à la volée
TRUNCATE TABLE #TABLE_OUTPUT
INSERT INTO #TABLE_OUTPUT
EXECUTE @code_retour=master.dbo.xp_cmdshell @cmd

-- Si le répertoire ne peut être créé pour une cause autre que le fait qu'il existe déjà, on sort en erreur
IF (@code_retour !=0 AND NOT EXISTS (select * from #TABLE_OUTPUT WHERE ligne like '%already exists%' OR ligne like '%existe d%'))
BEGIN
SELECT @msg = 'Echec de l'' exécution de sauvegarde_base lors de l''appel à '+ISNULL(@cmd,'?')+':'+ligne FROM #TABLE_OUTPUT WHERE ligne IS NOT NULL
RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG
return 1
END
SET @destination = 'DISK='''+LTRIM(RTRIM(@repertoire_destination)) + '\'+REPLACE(@nom_base,' ','_')+'\'+@prefixe_fichier+'.BAK'''

-- Génération de la commande BACKUP DATABASE ou BACKUP LOG en fonction du type de sauvegarde demandé
IF (upper(@type_sauvegarde)='LOG')
BEGIN
IF databasepropertyex(@nom_base,'Recovery')='SIMPLE'
BEGIN
SET @msg = 'Echec de l'' exécution de sauvegarde_base : La base '+ISNULL(@nom_base,'(valeur nulle)')+' est en mode de recouvrement SIMPLE, les sauvegardes de LOG sont donc impossibles'
RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG
return 1
END
SET @cmd = 'BACKUP LOG ['+@nom_base+'] TO '+@destination +' WITH INIT'
END
ELSE SET @cmd = 'BACKUP DATABASE ['+@nom_base+'] TO '+@destination +' WITH INIT'
IF (upper(@type_sauvegarde)='COPY_ONLY')
SET @cmd = @cmd + ', COPY_ONLY'

IF (upper(@type_sauvegarde)='DIFFERENTIEL')
SET @cmd = @cmd + ', DIFFERENTIAL'

PRINT @cmd
EXECUTE(@cmd)

IF NOT EXISTS (select 1 from msdb.dbo.backupmediafamily where physical_device_name like '%'+@prefixe_fichier+'%')
BEGIN
SET @msg='Echec de l'' exécution de sauvegarde_base lors de l''appel à '+ISNULL(@cmd,'?') +'. Consultez le journal d''erreurs pour plus d''informations'
RAISERROR (@msg, 16, 1) WITH NOWAIT,LOG
return 1
END
END
GO

Les sauvegardes devront être externalisées au plus tôt, soit en sauvegardant directement sur un répertoire distant, soit en mettant en place une mise sur bande ou un déplacement vers une machine de centralisation.Continuez votre lecture sur le blog :

• Production SQL Server : Les Statistiques (Benjamin VESAN) [SQL Server]
• Production SQL Server: Réorganisation des objets (Benjamin VESAN) [SQL Server]
• Production SQL Server : Ordonnancement (Benjamin VESAN) [SQL Server]
• Production SQL Server : Contrôle de cohérence (Benjamin VESAN) [SQL Server]
• Production SQL Server : banalisation des instances (Benjamin VESAN) [SQL Server]

• Si un ordonnanceur est déjà en place dans le Système d’Informations ($Universe, Control-M, OPC…), le suivi des tâches ordonnancées est  a priori déjà assuré. Les tâches de maintenance SQL Server seront donc gérées (déployées, exécutées, surveillées) par une équipe experte.
• Sinon, l’ordonnanceur de SQL Server, SQL Agent, convient tout à fait, à condition de mettre en place la remonté des erreurs d’exécution de tâches et de surveiller que l’Agent est toujours opérationnel. L’utilisation du mécanisme  natif centralisation de tâches SQL Agent, MSX/TSX, facilitera le déploiement et la mise à jour des tâches (voir par exemple cet article pour la mise en place de MST/TSX).

La tâche exécutée par l’ordonnanceur doit être la plus générique possible pour que sa maintenance reste simple. Pas question par exemple d’avoir à mentionner à chaque appel le nom des bases sur lesquelles effectuer les sauvegardes.
Il existe un moyen simple de répondre à ce besoin: la table de paramètre.
Sur toutes les instances du parc, une table contenant les paramètres de chaque tâche permettra de définir le périmètre et les options de chaque tâche:

En se basant sur l’exemple, la tâche de sauvegarde se déroule donc comme ceci:
Pour chaque base:
S’il existe une ligne ‘Sauvegarde’, ‘Exclusion’, <nom de la base> alors je ne sauvegarde pas cette base
S’il existe une ligne ‘Sauvegarde’, ‘Chemin’, <nom de la base> alors j’utilise le chemin indiqué.
Sinon, j’utilise le chemin indiqué dans la ligne ‘Sauvegarde’, ‘Chemin’, ‘Défaut’

Pour que la tâche soit maintenable facilement, deux possibilités:

• Le script exécuté est stocké sur un répertoire partagé, accessible par tous les SQL Agent du parc. La définition du script n’est pas dupliquée, il n’existe donc qu’une seule version.
• Le script se contente d’appeler une Procédure Stockée, déployée sur chaque instance. Cette méthode a ma préférence puisque le DBA aura accès depuis Management Studio au code et pourra facilement exécuter ces tâches manuellement.

Continuez votre lecture sur le blog :

• Production SQL Server : Sauvegardes (Benjamin VESAN) [SQL Server]
• Production SQL Server: Suivi et Contrôle du parc (Benjamin VESAN) [SQL Server]
• Production SQL Server : banalisation des instances (Benjamin VESAN) [SQL Server]
• Production SQL Server : Contrôle de cohérence (Benjamin VESAN) [SQL Server]
• Production SQL Server : L’approche (Benjamin VESAN) [SQL Server]

Normalisation des instances:
Gérer un incident sur une instance SQL Server que l’on ne connaît pas, c’est perdre énormément de temps à prendre connaissance de l’environnement (configuration de la machine, de l’instance, emplacement sur disques des journaux d’erreur, des fichiers de bases, …). Lorsque les instances du parc sont normalisées, ce temps précieux est économisé !
Il faut donc définir des règles basiques sur le nommage des instances, l’arborescence des fichiers, la configuration standard de l’instance (collation, options de sécurité, …)

Industrialisation des déploiements:
Même avec une norme écrite, rien ne garantit que l’installation manuelle d’une nouvelle instance soit conforme.
A minima, une procédure d’installation/configuration du moteur semble nécessaire.
Mieux, l’installation de l’instance peut être scriptée (fichier de configuration du setup); même chose pour la configuration (ensemble de scripts Transact-SQL). L’industrialisation sera couverte dans un article à venir.

Lorsque ces besoins sont correctement adressés, les tâches d’administration et la supervision pourront être banalisées.Continuez votre lecture sur le blog :

• Production SQL Server : banalisation des instances (Benjamin VESAN) [SQL Server]
• Production SQL Server: Suivi et Contrôle du parc (Benjamin VESAN) [SQL Server]
• Production SQL Server : Ordonnancement (Benjamin VESAN) [SQL Server]
• Production SQL Server : Sauvegardes (Benjamin VESAN) [SQL Server]
• Une approche pragmatique de la production SQL Server (Benjamin VESAN) [SQL Server]

• Production SQL Server: Suivi et Contrôle du parc (Benjamin VESAN) [SQL Server]
• Production SQL Server : Ordonnancement (Benjamin VESAN) [SQL Server]
• Production SQL Server : L’approche (Benjamin VESAN) [SQL Server]
• Production SQL Server : Les Statistiques (Benjamin VESAN) [SQL Server]
• Production SQL Server : Contrôle de cohérence (Benjamin VESAN) [SQL Server]

Un petit mot pour vous dire que je serai présent aux Techdays le mercredi 8 février avec Christophe Laporte (blog|twitter) et François Jehl (blog) sur les stands GUSS et SQL Server pour répondre à toutes vos questions entre 2 sessions.

Venez nombreux !!

David B.Continuez votre lecture sur le blog :

• Journées SQL Server 12/13 décembre (suite) (David BAFFALEUF) [SQL Server]
• Journées SQL Server à Paris les 12 et 13 décembre 2011 ! (David BAFFALEUF) [SQL Server]
• Repérer un package SSIS lors de son exécution (Louis HOCHBERG) [SQL Server]
• Modifier la Collation d’une base SQL 2005 (Louis HOCHBERG) [SQL Server]
• Réunion GUSS le 1er juillet ! (David BAFFALEUF) [SQL Server]

Ce fut l’occasion aussi de permettre aux MVP et aux personnes du support (EE, PFE) de se rencontrer. On a eu la chance également de pouvoir échanger avec Jean-Yves Devant, responsable de la partie Réplication + Change tracking + CDC au niveau du dev à Redmond. La rencontre a été immortalisée dans le hall principal de MS (Merci à Marc Biarnes):

Les sessions sont presque toutes disponibles en webcast , ainsi que les deux keynotes (1 et 2). Quelques unes des sessions que je vous recommande sur la partie  moteur:

• Philippe Geiger (MVP): SQL Server: ses fonctionnalités méconnues donc indispensables.
• Pascal Belaud (MS): Always On
• Arian Papillon : Réussir sa migration avec SQL Server 2008 R2 ou SQL Server 2012.
• Jean-Yves Devant (MS): Synchronisation des données / réplication
• Christophe Laporte (MVP,MCM): Server Core et Virtualisation.
• Frédéric Pichaut (MS): Optimisation et Troubleshooting.
• Frédéric Brouard (MVP): Contraintes et performances
• David Barbarin (MVP): Sécurité dans SQL Server 2012
• Et ma session sur le moteur XE ici.

A+Continuez votre lecture sur le blog :

• Journées SQL Server à Paris les 12 et 13 décembre 2011 ! (David BAFFALEUF) [SQL Server]
• Techdays !!! (David BAFFALEUF) [SQL Server]
• Production SQL Server : L’approche (Benjamin VESAN) [SQL Server]
• Web Séminaire « Haute Disponibilité avec SQL Server » (Benjamin VESAN) [SQL Server]
• Repérer un package SSIS lors de son exécution (Louis HOCHBERG) [SQL Server]

Nous serons une petite vingtaine de MVP, consultants et intervenants MS pour évoquer différents sujets autour du moteur relationnel et de la BI, et des nouveautés de la version 2012 qui sort l’année prochaine.

Je présenterai une session sur les XEvents en Denali CTP3 le lundi matin et serai présent sur le salon les deux jours. Ce sera surtout l’occasion de se rencontrer et d’échanger autour de SQL Server. Tous les détails sur le planning des sessions et l’organisation sont sur le site du GUSS.

Alors à très bientôt !

David B.

Continuez votre lecture sur le blog :

• Journées SQL Server 12/13 décembre (suite) (David BAFFALEUF) [SQL Server]
• Réunion GUSS le 1er juillet ! (David BAFFALEUF) [SQL Server]
• SQL Server 2012 Developper Training Kit (David BAFFALEUF) [SQL Server]
• Techdays !!! (David BAFFALEUF) [SQL Server]
• Repérer un package SSIS lors de son exécution (Louis HOCHBERG) [SQL Server]

use mabase ;

create table #ftsDDL (catalogname sysname, objname sysname, objtype varchar(20),
colname sysname, type_column_id sysname NULL, langid int, indexname sysname,
changetracking varchar(10), stoplistname sysname)

insert into #ftsDDL
select FTC.name 'Catalog Name', T.name 'Object name', 'Table' 'Object Type',
C.name 'colname', FTIC.type_column_id, FTIC.language_id, I.name 'UQIndexName',
FTI.change_tracking_state_desc,
case when STP.name <> '0' then STP.name else 'system' end
from sys.fulltext_indexes FTI
inner join sys.fulltext_catalogs FTC on FTC.fulltext_catalog_id = FTI.fulltext_catalog_id
inner join sys.tables T on FTI.object_id = T.object_id
inner join sys.indexes I on I.object_id = T.object_id
inner join sys.fulltext_index_columns FTIC on FTIC.object_id = FTI.object_id
inner join sys.columns C on C.column_id = FTIC.column_id and C.object_id = FTI.object_id
left outer join sys.fulltext_stoplists STP on STP.stoplist_id = FTI.stoplist_id
where I.is_unique = 1 and I.type = 1
and FTIC.type_column_id is NULL

	 UNION 

select FTC.name 'Catalog Name', V.name 'Object name', 'Indexed View' 'Object Type',
C.name 'colname', FTIC.type_column_id, FTIC.language_id, I.name 'UQIndexName',
FTI.change_tracking_state_desc,
case when STP.name <> '0' then STP.name else 'system' end
from sys.fulltext_indexes FTI
inner join sys.fulltext_catalogs FTC on FTC.fulltext_catalog_id = FTI.fulltext_catalog_id
inner join sys.views V on FTI.object_id = V.object_id
inner join sys.indexes I on I.object_id = V.object_id
inner join sys.fulltext_index_columns FTIC on FTIC.object_id = FTI.object_id
inner join sys.columns C on C.column_id = FTIC.column_id and C.object_id = FTI.object_id
left outer join sys.fulltext_stoplists STP on STP.stoplist_id = FTI.stoplist_id
where I.is_unique = 1 and I.type = 1
and FTIC.type_column_id is NULL
order by 1;

with cte as
 (select p1.catalogname, p1.objname, (select colname
 +case
when p2.type_column_id is NULL then '' else ' TYPE COLUMN *put type column here*' end+
' language '+cast(p2.langid as char(5))+','
from #ftsDDL p2 where p1.objname = p2.objname FOR XML PATH ('') ) as 'Columns',
p1.indexname, p1.changetracking, p1.stoplistname from #ftsDDL p1 group by catalogname,
objname, indexname, changetracking, stoplistname
)

select 'CREATE FULLTEXT INDEX ON '+objname+'('+substring(Columns,1,len(Columns)-1)+
') KEY INDEX ['+indexname+'] on ['+catalogname+'] WITH CHANGE_TRACKING '+changetracking+
', STOPLIST = ['+stoplistname+']' from cte;

drop table #ftsDDL;

/*

CREATE FULLTEXT INDEX ON V_ContentItmListGermany(abstr language 1031 ,desc language 1031 ,
subt language 1031 ,title language 1031 ) KEY INDEX [UI_V_ContentItmsListGermany]
on [Catalog_1] WITH CHANGE_TRACKING AUTO, STOPLIST = [SYSTEM]

CREATE FULLTEXT INDEX ON V_GenAtttGermany(blob language 1031 ) KEY INDEX [UI_V_GenAtttGermany]
on [Catalog_1] WITH CHANGE_TRACKING AUTO, STOPLIST = [SYSTEM]

CREATE FULLTEXT INDEX ON V_GenAttachmentDe(blob TYPE COLUMN *put type column here*
language 1031 ) KEY INDEX  [UI_V_GenAttachmentDe] on [Catalog_LanDe]
WITH CHANGE_TRACKING AUTO, STOPLIST = [SYSTEM]

CREATE FULLTEXT INDEX ON V_GenAtttUS(blob language 1033 ) KEY INDEX [UI_V_GenAtttUS]
on [Catalog_2] WITH CHANGE_TRACKING AUTO, STOPLIST = [SYSTEM]

CREATE FULLTEXT INDEX ON V_GenAttachmentEn(blob TYPE COLUMN *put type column here*
language 1033 ) KEY INDEX  [UI_V_GenAttachmentEn] on [Catalog_LanEn]
WITH CHANGE_TRACKING AUTO, STOPLIST = [SYSTEM]

CREATE FULLTEXT INDEX ON V_GenAtttFrance(blob language 1036 ) KEY INDEX [UI_V_GenAtttFrance]
on [Catalog_3] WITH CHANGE_TRACKING AUTO, STOPLIST = [SYSTEM]

*/

Les indexes FTS, contraitement aux catalogues ne peuvent pas être scriptés directement dans SSMS. Evidemment il faudra recréer les catalogues avant. Attention, si des colonnes indexées sont de type varbinary(max) ou image, il faudra préciser le nom de la colonne qui stocke l’extension (cf http://msdn.microsoft.com/en-us/library/ms187317.aspx) à la place de *put type column here*
(je ne peux pas les deviner)

Petite note intéressante, dans le cas de ce client, les indexes FTS étaient créés sur des vues indexées, d’où l’UNION entre les indexes liés à sys.tables et ceux liés à sys.views dans la table temporaire. Et pour transposer et concaténer les colonnes de l’index sur une ligne on utilise la technique avec FOR XML PATH( ») dans la CTE.

A+

ContentItmListGermany

Continuez votre lecture sur le blog :

• Msg 2508, Level 16, State 1: the In-Row data %% for object %% is incorrect (David BAFFALEUF) [SQL Server]
• Production SQL Server: Réorganisation des objets (Benjamin VESAN) [SQL Server]
• Production SQL Server : Les Statistiques (Benjamin VESAN) [SQL Server]
• Error 8976 / 8978, problèmes de chaînage, comment récupérer les données (David BAFFALEUF) [SQL Server]
• Oracle Text pour DBA Oracle : Partie 1 (Thierry GASCARD) [Oracle]

La fonction qui tue:

Dans un article précédent, nous avons couvert une fonction table valuée fort intéressante qui permet de relire à l’intérieur d’une sauvegarde de journal: fn_dump_dblog(). Nous allons nous en servir pour connaître cette fois l’identité du coupable. La table concernée par le DELETE est la table T1, il faut commencer par récupérer sa valeur de allocation_unit_id:

select allocation_unit_id from sys.allocation_units AU inner join sys.partitions P
on P.hobt_id = AU.container_id where P.object_id = object_id('T1')

allocation_unit_id
---------------------
72057594043301888

Ensuite, rechercher dans les backups transactionnels des opérations de suppression de lignes sur cet objet:

select [Current LSN] ,Operation ,[Transaction ID] ,AllocUnitId
from fn_dump_dblog(DEFAULT, DEFAULT,DEFAULT, DEFAULT,
'C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery.TRAN2.bak',
DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT) where Operation  = 'LOP_DELETE_ROWS' and AllocUnitId = 72057594043301888

Current LSN             Operation          Transaction ID AllocUnitId         
----------------------- ------------------ -------------- --------------------
00000025:00000032:0003  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
00000025:00000032:0005  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
00000025:00000032:0006  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
00000025:00000032:0007  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
00000025:00000032:0008  LOP_DELETE_ROWS    0000:00000a9d  72057594043301888   
...

On récupère le LSN de la transaction pour retrouver le BEGIN TRAN correspondant:  0000:00000a9d:

select [Current LSN] ,Operation ,[Transaction ID] ,AllocUnitId
 ,[Begin Time] ,[Transaction Name] ,[End Time] ,[Description]
 ,[Transaction SID]
from fn_dump_dblog(DEFAULT, DEFAULT,DEFAULT, DEFAULT,
'C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery.TRAN2.bak',
DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
where Operation  = 'LOP_BEGIN_XACT' and [Transaction ID] = '0000:00000a9d'

Current LSN             Operation                       Transaction ID AllocUnitId        
----------------------- ------------------------------- -------------- --------------------
00000025:00000032:0001  LOP_BEGIN_XACT                  0000:00000a9d  NULL                

 Begin Time               Transaction Name                  Description                                  
------------------------ --------------------------------- -----------------------------------------------
2011/10/05 15:21:50:510  tralalala                         tralalala;0x75a770d73d6bf54bb0dc07725254ae57    

Transaction SID
------------------------------------
0x75A770D73D6BF54BB0DC07725254AE57

Remarque intéressante, [Transaction SID] (en rouge) correspond au SID de l’utilisateur dans la base…

Le dénouement…

Il ne reste plus qu’à dégainer la requête sur sys.database_principals:

select name from sys.database_principals where sid = 0x75A770D73D6BF54BB0DC07725254AE57

name
-------------------
user1

A+Continuez votre lecture sur le blog :

• Point-in-time recovery et fn_dump_dblog() (David BAFFALEUF) [SQL Server]
• Msg 2508, Level 16, State 1: the In-Row data %% for object %% is incorrect (David BAFFALEUF) [SQL Server]
• Production SQL Server : Sauvegardes (Benjamin VESAN) [SQL Server]
• Error 8976 / 8978, problèmes de chaînage, comment récupérer les données (David BAFFALEUF) [SQL Server]
• How-To: réduire la taille du journal de transactions sur disque (David BAFFALEUF) [SQL Server]

Le problème inhérent à ce genre de restauration, c’est qu’on ne sait pas exactement jusqu’où restaurer. Lorsque votre collègue vous dit qu’il a supprimé accidentellement les données de la table principale vers 13h45, c’est assez vague comme réponse. Si vous restaurez à 13h44, que fait-on des transactions validées entre 13h44 et le moment précis où le delete a été exécuté ? Il faut jouer avec RESTORE LOG WITH STOPAT, STANDBY pour déterminer le point dans le temps où les données ne sont plus dans la table. Milliseconde par milliseconde ? Bof bof bof…

L’alternative est d’utiliser des marques de transactions:

BEGIN TRAN MiseAJourProduits
GO
DELETE FROM PRODUITS -- WHERE ID_PRODUIT = @id_produit (aïeuuu)
GO
...
COMMIT TRAN MiseAJourProduits
GO

Dès lors il est possible de restaurer avant la transaction qui pose problème en utilisant l’option STOPBEFOREMARK de RESTORE LOG:

RESTORE LOG maBASE FROM DISK='C:\UTRECHT\MSSQL.1\MSSQL\Backup\maBase.20110712.trn'
WITH RECOVERY, STOPBEFOREMARK = 'MiseAJourProduits'
GO

Seul problème, c’est à l’initiative du développeur de le faire, or la plupart du temps les transactions sont non marquées, je ne vous parle même pas des progiciels…

Fiat lux:

J’ai longtemps regretté l’absence d’un outil built-in tel que LogMiner pour pouvoir lire le contenu d’un backup log, et pouvoir effectuer une restauration précise à la transaction près. En effet,  STOPATMARK / STOPBEFOREMARK permettent d’utiliser une syntaxe telle que :

RESTORE LOG ... WITH STOPBEFOREMARK = 'lsn:<LSN number>'.

Seulement voilà, comment connaître les différentes transactions embarquées dans un backup log ? fn_dblog() permet d’obtenir cette information sur le journal lui-même, mais quid des backups…

Et il y a quelques jours, je joue avec OPENROWSET() et là je tombe sur une fonction système non-documentée dans la base resource : fn_dump_dblog(). En lisant le nom, l’émotion m’étreint. Le souffle court, je jette un coup d’oeil au source de la fonction:

create function sys.fn_dump_dblog  
 (  
    @start    nvarchar (25) = NULL,  
    @end      nvarchar (25) = NULL,  
    @devtype  nvarchar (260) = NULL, -- NULL(DISK) | DISK | TAPE | VIRTUAL_DEVICE  
    @seqnum   Int         = 1,  
    @fname1   nvarchar (260) = NULL,  
    @fname2   nvarchar (260) = NULL,  
    @fname3   nvarchar (260) = NULL, 
    ...
    @fname64   nvarchar (260) = NULL  
 )  
returns table  
as  
 return select  
     [Current LSN],  [Operation],  [Context],  [Transaction ID],   [Tag Bits],  
     [Log Record Fixed Length],  [Log Record Length],   [Previous LSN],  
     (...)
     from OpenRowset (DBLog, @start, @end, @devtype, @seqnum,  
     @fname1,  @fname2,  @fname3,  @fname4,  @fname5, 
     (...)
     @fname62,  @fname63,  @fname64)

En lisant le paramètre @devtype j’avale mon sandwich indien poulet-curry de travers. Ça fait dix mois que je bosse sur un provider OLEDB qui permette de lire dans un backup full (un peu l’équivalent de l’Archive Database Access sous Sybase ASE), et là je découvre qu’il existe une fonction qui fait la même chose sur les backups log. Depuis la 2005 RTM. Argl.

Et puis en gouglant un peu, je tombe sur cet article de Dimitri Furman, une personne de MS Consulting Services à New-York qui décrit brièvement la fonction et ses arguments. Je ne peux pas résister, il va falloir tester ça.

Scénario de test:

On va donc créer une base de démo, peupler une table et supprimer des intervalles de valeurs en les entrelaçant de backup logs. On arrive à une suppression par erreur, et on souhaite remonter les données juste à la transaction d’avant, sachant que les transactions ne sont pas marquées.

/*************************************************************************************
 Obj:       DEMO RESTAURATION SUITE A ERREUR MANUELLE
            UTILISATION DU MODE STANDBY POUR EFFECUTER UN POINT-IN-TIME RECOVERY
 Aut:       dbaffaleuf@capdata
 Crdate:    2011/07/13
*/

-- Population de la base
use master
GO
if exists (select 1 from sys.databases where name='demorecovery')
 drop database demorecovery
GO
create database demorecovery
GO

use demorecovery
GO
create table T1(
 a numeric identity,
 b char(4000) default replicate('b',4000),
 c bigint default round(rand()*100,0))
GO
insert into T1 default values
GO 1000
create unique clustered index IDX_T1C on T1(a)
GO
create index IDX_T1NC__c on T1(c)
GO

-- Activation du mode de récupération complet
alter database demorecovery set recovery full
GO
backup database demorecovery to disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_FULL.bak' with init, stats
GO

-- Quelques transactions et quelques backups de transactions
delete from T1 where a < 10
GO
backup log demorecovery to disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG1.bak' with init, stats
GO
delete from T1 where a < 20
GO
backup log demorecovery to disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG2.bak' with init, stats
GO
delete from T1 where a < 30
GO
backup log demorecovery to disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG3.bak' with init, stats
GO

-- Trois autres transactions à suivre, la dernière (a<60) est une erreur manuelle
delete from T1 where a < 40
GO
delete from T1 where a < 50
GO
delete from T1 where a < 60  -- <-- CES DONNEES SONT EFFACEES PAR ERREUR !!!
GO
backup log demorecovery to disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG4.bak' with init, stats
GO

-- Une autre transaction valide passe entre-temps
delete from T1 where a > 990
GO

-- Le téléphone sonne, l'utilisateur demande s'il est possible de récupérer les données avant le delete where a < 60
use master
GO
-- tail log backup
backup log demorecovery to disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_TAILLOG.bak' with init, stats, NO_TRUNCATE, NORECOVERY
GO
select state_desc from sys.databases where name='demorecovery'
GO

state_desc
------------------------------------------------------------
RESTORING

-- On identifie le backup de journal qui embarque l'erreur: demorecovery_LOG4.bak
-- on recherche les différentes transactions contenues à l'intérieur avec fn_dump_dblog():
select *, [Current LSN]
 ,Operation
 ,[Transaction ID]
 ,AllocUnitId
 ,[Begin Time]
 ,[Transaction Name]
 ,[End Time]
 ,[Description]
from fn_dump_dblog(DEFAULT, DEFAULT,DEFAULT, DEFAULT,
'C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG4.bak',
DEFAULT,DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
where Operation  = 'LOP_BEGIN_XACT'

Current LSN             Operation         Transaction ID  Begin Time               Transaction Name    Description
----------------------- ----------------- --------------  ------------------------ ------------------- -----------------------------------------------------------------------
00000041:00000338:000e  LOP_BEGIN_XACT    0000:000008b8   2011/07/13 15:59:43:580  DELETE              DELETE;0x0105000000000005150000006aa3aebb1b5c3491642b62e2e8030000
00000042:00000021:0001  LOP_BEGIN_XACT    0000:000008b9   2011/07/13 15:59:43:587  DELETE              DELETE;0x0105000000000005150000006aa3aebb1b5c3491642b62e2e8030000
00000042:00000076:0001  LOP_BEGIN_XACT    0000:000008ba   2011/07/13 15:59:43:630  DELETE              DELETE;0x0105000000000005150000006aa3aebb1b5c3491642b62e2e8030000


-- On restaure jusqu'au N-1 backup log avant pb
restore database demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_FULL.bak' with stats, norecovery
GO
restore log demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG1.bak' with stats, norecovery
GO
restore log demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG2.bak' with stats, norecovery
GO
restore log demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG3.bak' with stats, norecovery
GO

-- On restaure en standby mode à la première transaction pour déterminer si les données entre 50 et 60 sont toujours là
restore log demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG4.bak' with stats,
standby='C:\UTRECHT\MSSQL.1\MSSQL\Backup\stdby_files.bak', stopbeforemark='lsn:0x00000042:00000021:0001'
GO

select state_desc, is_in_standby from sys.databases where name='demorecovery'
GO

state_desc    is_in_standby
------------- --------------
ONLINE        1

use demorecovery
GO
select * from T1 where a < 60
GO

a      b                            c
------ ---------------------------- ----
40     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 31
41     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 28
42     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 77
43     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 18
44     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 12
45     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 42
46     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
47     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 46
48     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 39
49     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 74
50     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 45
51     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
52     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 32
53     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 10
54     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
55     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 99
56     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 71
57     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 38
58     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
59     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 41

-- OK, allons voir à la transaction suivante
use master
GO
restore log demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG4.bak' with stats,
standby='C:\UTRECHT\MSSQL.1\MSSQL\Backup\stdby_files.bak', stopbeforemark='lsn:0x00000042:00000076:0001'
GO

use demorecovery
GO
select * from T1 where a < 60
GO

a      b                            c
------ ---------------------------- ----
50     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 45
51     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
52     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 32
53     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 10
54     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
55     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 99
56     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 71
57     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 38
58     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
59     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 41

-- On dirait bien qu'on est dans l'état attendu. Allons voir à la dernière transaction pour confirmer:
use master
GO
restore log demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG4.bak' with stats,
standby='C:\UTRECHT\MSSQL.1\MSSQL\Backup\stdby_files.bak'
GO

use demorecovery
GO
select * from T1 where a < 60
GO

a      b                            c
------ ---------------------------- ----

-- Ouaip, donc il faut revenir au point précédent, en rechargeant tout depuis le backup FULL:
use master
GO
restore database demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_FULL.bak' with stats, norecovery
GO
restore log demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG1.bak' with stats, norecovery
GO
restore log demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG2.bak' with stats, norecovery
GO
restore log demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG3.bak' with stats, norecovery
GO

restore log demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_LOG4.bak' with stats,
stopbeforemark='lsn:0x00000042:00000076:0001', recovery
GO

use demorecovery
GO
select * from T1 where a < 60
GO

a      b                            c
------ ---------------------------- ----
50     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 45
51     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
52     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 32
53     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 10
54     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
55     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 99
56     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 71
57     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 38
58     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 2
59     bbbbbbbbbbbbbbbbbbbbbbbbbbbb 41

-- Note: la transaction suivante (a>990) est perdue. On ne peut pas la recharger car il y aurait une cassure dans la chaîne de LSNs.
-- Si on avait tenté de restaurer le tail-log à la suite:
restore log demorecovery from disk='C:\UTRECHT\MSSQL.1\MSSQL\Backup\demorecovery_TAILLOG.bak' with stats, recovery
GO

Msg 4305, Niveau 16, État 1, Ligne 1
Le journal dans ce jeu de sauvegarde commence au numéro de séquence d'enregistrement 66000000017600001, ce qui est trop récent
pour une application à la base de données. Une sauvegarde de fichier journal antérieure qui inclut le numéro de séquence
d'enregistrement 66000000009200001 peut être restaurée.
Msg 3013, Niveau 16, État 1, Ligne 1
RESTORE LOG s'est terminé anormalement.

Donc voilà enfin une méthode pour récupérer une base pile à l’endroit où on veut la récupérer, encore une fois grâce à une fonction non documentée qui mériterait d’être plus connue.

A+. David B.Continuez votre lecture sur le blog :

• Suppression accidentelle de ligne : comment retrouver le coupable ? (David BAFFALEUF) [SQL Server]
• How-To: réduire la taille du journal de transactions sur disque (David BAFFALEUF) [SQL Server]
• Production SQL Server : Sauvegardes (Benjamin VESAN) [SQL Server]
• Pourquoi il faut sauvegarder les bases systèmes (David BAFFALEUF) [SQL Server]
• Principes d’une sauvegarde à chaud (David BAFFALEUF) [SQL Server]

En gros c’est une fonction qui appelle un provider OLEDB, et qui retourne un résultat au format table-valué. Par exemple on l’utilise pour exécuter des requêtes sur des serveurs liés en passant le nom d’un provider (MSDAORA pour oracle, SQLNCLI pour le client natif MSSQL, etc…). Par exemple:

SELECT RemoteDepts.*
FROM OPENROWSET('SQLNCLI', 'Server=MS2K8-Win2008-1;Trusted_Connection=yes;',
     'SELECT * FROM CAPDATA.dbo.DEPARTEMENT') AS RemoteDepts;

Jusque là, rien de révolutionnaire.

Juste pour rire, on réinvente le CSV engine:

Il existe un certain nombre de providers non documentés sur lesquels s’appuient en général  les DMV/DMF et quelques fonctions table-valuées comme fn_dblog(), fn_helpcollations(), etc… qui feront l’objet de l’épisode 2. Il en existe un toutefois, BULK,  qui est parfaitement documenté et très pratique, et qui permet de faire du chargement massif notamment (l’ancêtre du BULK INSERT T-SQL) mais aussi de renvoyer à peu près n’importe quoi sous une forme table-valuée.

Par exemple, on peut reprendre à notre compte le concept du moteur CSV de MySQL, et créer des vues qui mappent des fichiers CSV sur disque, pour visualiser le contenu sans les stocker physiquement dans la base. Un exemple de fichier CSV et de fichier de format (pour plus d’infos voir la page MSDN concernant les fichiers de format):

bulktest.csv:

LOUISH,21941
DAVID,21940
LOUISP,21943
LAURENT,21942
JSEB,21944

bulktest.fmt:

9.0
2
1 SQLCHAR 0 10 "," 1 CurName ""
2 SQLCHAR 0 5 "\r\n" 2 ID ""

La vue T-SQL:

create view CSVENgine
as
    select * from openrowset(bulk 'V:\DBA2\MSSQL.1\MSSQL\Backup\bulktest.csv',
    FORMATFILE = 'V:\DBA2\MSSQL.1\MSSQL\Backup\bulktest.fmt') as CSV1
GO

select * from CSVENgine order by ID desc

CurName    ID
---------- -----
JSEB       21944
LOUISP     21943
LAURENT    21942
LOUISH     21941
DAVID      21940

(5 ligne(s) affectée(s))

Seule petite ombre au tableau, le provider BULK ne permet pas la mise à jour, on ne peut accéder aux données qu’en lecture seule. Mais ça reste quand même bien pratique pour attaquer une trace perfmon directement en SQL.

Charger des fichiers binaires:

Les options SINGLE_BLOB / SINGLE_CLOB/ SINGLE_NCLOB du provider BULK permettent en outre de charger des documents binaires (vidéos, images, PDF, documents word, etc…)  dans des tables, respectivement soit en varbinary(max) / varchar(max) / nvarchar(max) si les fichiers sont inférieurs à 2Gb comme c’est la cas dans l’exemple, soit dans du filestream pour les fichiers supérieurs.

create table Documents (ID numeric identity, title varchar(255), Filedata varbinary(max))

insert into Documents (title, Filedata)
SELECT 'SQL 2008 Licensing',
doc.* from OPENROWSET(BULK 'E:\CAPDATA\DOCUMENTATION\SGBD\SQL Server\WINWORDS\2008
SQL Licensing Overview final.docx', SINGLE_BLOB) as doc

insert into Documents (title, Filedata)
SELECT 'DBM Sharepoint Labs',
doc.* from OPENROWSET(BULK 'E:\CAPDATA\DOCUMENTATION\SGBD\SQL Server\WINWORDS\
DBM_Sharepoint_Labs.docx', SINGLE_BLOB) as doc

insert into Documents (title, Filedata)
SELECT 'DBM and Log Shipping',
doc.* from OPENROWSET(BULK 'E:\CAPDATA\DOCUMENTATION\SGBD\SQL Server\WINWORDS\
DBMandLogShipping.docx', SINGLE_BLOB) as doc

insert into Documents (title, Filedata)
SELECT 'Database Snapshot Performance',
doc.* from OPENROWSET(BULK 'E:\CAPDATA\DOCUMENTATION\SGBD\SQL Server\WINWORDS\
DBSnapshotPerf.docx', SINGLE_BLOB) as doc

Récupérer la sortie d’une procédure stockée sous la forme d’une table:

Une astuce monstrueuse révélée par Ward Pond en 2005, qui détourne l’utilisation d’OPENROWSET pour appeler une procédure stockée sur le serveur local et retransformer le résultat en format table-valué, un peu à la façon dont les tables MDA sont appelées sous Sybase ASE, et qui nous a sauvé la vie bien des fois sur SQL Server 2000 avant que n’apparaissent les DMV:

sp_configure 'Ad Hoc Distributed Queries',1
GO
reconfigure
GO
SELECT  w.SPID, w.Status, w.loginame, w.blk, w.dbname,w.cmd,
Lck.Objid, lck.Type, lck.Mode
FROM    OPENROWSET ('SQLNCLI','Server=(local);TRUSTED_CONNECTION=YES;',
                   'set fmtonly off exec master.dbo.sp_who') as w
INNER JOIN OPENROWSET ('SQLNCLI','Server=(local);TRUSTED_CONNECTION=YES;',
                       'set fmtonly off exec master.dbo.sp_lock') as lck
on lck.spid = w.spid
where w.blk <> 0
GO

SPID   Status       loginame            blk   dbname          cmd     Objid       Type Mode
------ ------------ ------------------- ----- --------------- ------- ----------- ---- --------
55     suspended    DBA2\dbaffaleuf     54    whitepaperz     SELECT  2073058421  TAB  IS
55     suspended    DBA2\dbaffaleuf     54    whitepaperz     SELECT  0           DB   S
55     suspended    DBA2\dbaffaleuf     54    whitepaperz     SELECT  2073058421  RID  S
55     suspended    DBA2\dbaffaleuf     54    whitepaperz     SELECT  2073058421  PAG  IS

Voir plus loin ?

MS fournit des providers OLEDB pour s’interfacer avec OPENROWSET, certains sont publics (providers relatifs aux serveurs liés, BULK), et certains sont non documentés et utilisés par des vues ou fonctions dynamiques dans SQL Server. C’est ceux-ci que nous essaierons de référencer et de comprendre dans l’épisode 2. Enfin dans l’épisode 3,  nous verrons comment créer son propre provider OLEDB avec Advanced Template Library et Visual C++, et nous essaierons de le faire communiquer avec SQL Server. Du pain sur la planche !

A+ David B.Continuez votre lecture sur le blog :

• Regénérer le DDL des indexes FULL TEXT (David BAFFALEUF) [SQL Server]
• How-To : réduire l’enveloppe de tempdb (David BAFFALEUF) [SQL Server]
• Point-in-time recovery et fn_dump_dblog() (David BAFFALEUF) [SQL Server]
• Pourquoi il faut sauvegarder les bases systèmes (David BAFFALEUF) [SQL Server]
• Oracle Text pour DBA Oracle : Partie 1 (Thierry GASCARD) [Oracle]