Developpez.com
Accueil
Lorsqu’on installe SQL Server, on a bien sûr à considérer comment placer les fichiers de bases de données. A ce stade, il est probable que vous suivrez les bonnes pratiques bien connues ou les guidelines fournis pour Microsoft mais êtes vous réellement au courant de ceux fournis par les vendeurs de stockage? Parlons en dans ce billet.
> Lire la suite (en anglais)
David Barbarin
MVP & MCM SQL Server
During the night, you receive an alert concerning your SQL Server failover cluster or your availability groups. You’re in panic because the message displayed is « a failover has occured .. see the log for more details » …
Durant la nuit, vous recevez une alerte concernant un basculement d’une de vos instances SQL Server FCI ou de vos groupes de disponibilités. Mode panique !!! parce que le message qui vous arrive est le suivant : « a failover has occured .. see the log for more détails » … Bien sûr vous tentez de rester calme (après tout le basculement automatique est fait pour cela non ?) et après avoir été connecté à votre environnement vous ne trouvez rien … Que s’est-il passé ? Peut être qu’une intervention manuelle est à l’origine de cette alerte et vous n’êtes pas au courant.
Lire la suite (en anglais)
David BARBARIN (Mikedavem)
MVP et MCM SQL Server
Récemment j’ai rencontré un problème avec l’un de mes clients (oui je recontre beaucoup de problèmes à vrai dire …) qui m’expliquait que leur instance Server 2008 R2 avait une fuite de mémoire . Waaou une fuite mémoire! Le serveur posède 64 Go de RAM et l’instance de SQL Server concernée est limitée à 54 Go selon l’option de serveur de mémoire serveur max (GB ) . Cependant, il avait remarqué que SQL Server utilise 60 Go de mémoire, ce qui ne correspond pas à l’option serveur « max server memory (MB) ». Qu’Est-ce qui passe ici ? Je vous propose de voir cela dans le billet.
Lire la suite (en anglais)
David BARBARIN (Mikedavem)
MVP et MCM SQL Server
Au cours de mes audits clients, il arrive encore de trouver des bases de données avec une option de vérification de page configurée à NONE. J’ai toujours alerter mes clients sur ce type de configuration parce qu’elle peut avoir un impact certain sur l’intégrité des bases de données concernées. Un de mes clients me disait justement que de toute façon la vérification d’intégrité via les plans de maintenance pouvaient étaient faite pour cela mais est-ce vraiment le cas ?
Pour le savoir c’est par ici (en anglais)
David BARBARIN (Mikedavem)
MVP et MCM SQL Server
C’est officiel je suis renouvelé pour la 5ème fois en tant que MVP SQL Server.
Enjoy !
David BARBARIN (Mikedavem)
MVP et MCM SQL Server
Transparent Data Encryption requières la création d’une clé de chiffrement de bases de données. Cette clé fait elle même partie d’une hiérarchie de chiffrement de clé utilisée par SQL Server. Cette hiérarchie offre une certaine souplesse dans la gestion des clés qui comprend la restriction des clés aux personnes autorisées, la sauvegarde des clés vers un stockage sécurisé et la rotation des clés de chiffrements pour éviter que celles-ci soient compromises par une personne malveillante. Comme vous pouvez l’imaginer un aspect important de cette gestion des clés est l’alignement de rotation des clés avec la stratégie de sauvegarde des bases de données.
>> Pour lire la suite
David BARBARIN (Mikedavem)
MVP et MCM SQL Server
Comme promis voici les slides de ma session sur les événements étendus. Malheureusement je n’ai pas pu effectuer toutes les démonstrations que je voulais .. eh oui il faut bien commencer par expliquer comment fonctionne les événements étendus et cela m’a pris un peu plus de temps que prévu 
. Mais ce n’est que partie remise, je garde cette partie pour une session uniquement orientée démonstration. J’espère pouvoir vous la présenter rapidement.
Merci aux personnes qui ont voulu assister à ma présentation et de assister de manière générale à ce premier SQL Saturdays Paris. Ce fut un bon moment d’échange et de rencontres !
David BARBARIN (Mikedavem)
MVP SQL Server
La vérification d’intégrité des bases de données s’effectue par la commande DBCC CHECKDB. Le résultat de cette commande est assez verbeuse par défaut et peut poser problème lorsque la vérification d’intégrité est utilisé dans des scripts T-SQL personnalisés de maintenance par exemple.
Posons un peu le contexte et le pourquoi de ce billet. En réalité j’ai eu l’occasion de travailler (comme beaucoup je pense) sur la création de plans de maintenance "custom". L’idée a été de construire un jeu de script permettant de pouvoir gérer l’ensemble des tâches que l’on peut avoir à utiliser pour la maintenance des bases de données. Une de ces tâches concerne la vérification d’intégrité des bases. Cette vérification se fait à l’aide de la commande bien connue DBCC CHECKDB. Dans notre contexte l’idée est de pouvoir exécuter une tâche de maintenance et d’enregistrer dans une table de log les données suivantes :
Ceci nous permet d’avoir une traçabilité des tâches de maintenance par la suite et pouvoir les consommer à des fins d’analyse.
Lorsque l’on scripte une vérification d’intégrité à l’aide de la commande DBCC CHECKDB on s’aperçoit rapidement que la gestion des erreurs liées à cette commande n’est pas triviale. C’est que nous verrons dans le cadre de ce billet.
Une sortie verbeuse
Voici la sortie d’une commande DBCC CHECKDB sur une base de données corrompue :
DBCC results for ‘DemoCorruptMetadata’.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=1) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=2) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
DBCC results for ‘sys.sysrscols’.
There are 872 rows in 9 pages for object "sys.sysrscols".
DBCC results for ‘sys.sysrowsets’.
There are 124 rows in 1 pages for object "sys.sysrowsets".
DBCC results for ‘sys.sysclones’.
There are 0 rows in 0 pages for object "sys.sysclones".
DBCC results for ‘sys.sysallocunits’.
There are 138 rows in 2 pages for object "sys.sysallocunits".
DBCC results for ‘sys.sysfiles1′.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for ‘sys.sysseobjvalues’.
There are 0 rows in 0 pages for object "sys.sysseobjvalues".
DBCC results for ‘sys.syspriorities’.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for ‘sys.sysdbfrag’.
There are 0 rows in 0 pages for object "sys.sysdbfrag".
DBCC results for ‘sys.sysfgfrag’.
There are 0 rows in 1 pages for object "sys.sysfgfrag".
DBCC results for ‘sys.sysdbfiles’.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for ‘sys.syspru’.
There are 0 rows in 0 pages for object "sys.syspru".
DBCC results for ‘sys.sysbrickfiles’.
There are 0 rows in 0 pages for object "sys.sysbrickfiles".
DBCC results for ‘sys.sysphfg’.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for ‘sys.sysprufiles’.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for ‘sys.sysftinds’.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for ‘sys.sysowners’.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for ‘sys.sysdbreg’.
There are 0 rows in 0 pages for object "sys.sysdbreg".
DBCC results for ‘sys.sysprivs’.
There are 136 rows in 1 pages for object "sys.sysprivs".
DBCC results for ‘sys.sysschobjs’.
There are 2180 rows in 29 pages for object "sys.sysschobjs".
DBCC results for ‘sys.syscolpars’.
There are 698 rows in 11 pages for object "sys.syscolpars".
DBCC results for ‘sys.sysxlgns’.
There are 0 rows in 0 pages for object "sys.sysxlgns".
DBCC results for ‘sys.sysxsrvs’.
There are 0 rows in 0 pages for object "sys.sysxsrvs".
DBCC results for ‘sys.sysnsobjs’.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for ‘sys.sysusermsgs’.
There are 0 rows in 0 pages for object "sys.sysusermsgs".
DBCC results for ‘sys.syscerts’.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for ‘sys.sysrmtlgns’.
There are 0 rows in 0 pages for object "sys.sysrmtlgns".
DBCC results for ‘sys.syslnklgns’.
There are 0 rows in 0 pages for object "sys.syslnklgns".
DBCC results for ‘sys.sysxprops’.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for ‘sys.sysscalartypes’.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for ‘sys.systypedsubobjs’.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for ‘sys.sysidxstats’.
There are 130 rows in 1 pages for object "sys.sysidxstats".
DBCC results for ‘sys.sysiscols’.
There are 317 rows in 3 pages for object "sys.sysiscols".
DBCC results for ‘sys.sysendpts’.
There are 0 rows in 0 pages for object "sys.sysendpts".
DBCC results for ‘sys.syswebmethods’.
There are 0 rows in 0 pages for object "sys.syswebmethods".
DBCC results for ‘sys.sysbinobjs’.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for ‘sys.sysaudacts’.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for ‘sys.sysobjvalues’.
There are 134 rows in 13 pages for object "sys.sysobjvalues".
DBCC results for ‘sys.syscscolsegments’.
There are 0 rows in 0 pages for object "sys.syscscolsegments".
DBCC results for ‘sys.syscsdictionaries’.
There are 0 rows in 0 pages for object "sys.syscsdictionaries".
DBCC results for ‘sys.sysclsobjs’.
There are 16 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for ‘sys.sysrowsetrefs’.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for ‘sys.sysremsvcbinds’.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for ‘sys.sysxmitqueue’.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for ‘sys.sysrts’.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for ‘sys.sysconvgroup’.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for ‘sys.sysdesend’.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for ‘sys.sysdercv’.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for ‘sys.syssingleobjrefs’.
There are 156 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for ‘sys.sysmultiobjrefs’.
There are 107 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for ‘sys.sysguidrefs’.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for ‘sys.sysfoqueues’.
There are 0 rows in 0 pages for object "sys.sysfoqueues".
DBCC results for ‘sys.syschildinsts’.
There are 0 rows in 0 pages for object "sys.syschildinsts".
DBCC results for ‘sys.syscompfragments’.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for ‘sys.sysftsemanticsdb’.
There are 0 rows in 0 pages for object "sys.sysftsemanticsdb".
DBCC results for ‘sys.sysftstops’.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for ‘sys.sysftproperties’.
There are 0 rows in 0 pages for object "sys.sysftproperties".
DBCC results for ‘sys.sysxmitbody’.
There are 0 rows in 0 pages for object "sys.sysxmitbody".
DBCC results for ‘sys.sysfos’.
There are 0 rows in 0 pages for object "sys.sysfos".
DBCC results for ‘sys.sysqnames’.
There are 100 rows in 1 pages for object "sys.sysqnames".
DBCC results for ‘sys.sysxmlcomponent’.
There are 100 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for ‘sys.sysxmlfacet’.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for ‘sys.sysxmlplacement’.
There are 19 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for ‘sys.sysobjkeycrypts’.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for ‘sys.sysasymkeys’.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for ‘sys.syssqlguides’.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for ‘sys.sysbinsubobjs’.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for ‘sys.syssoftobjrefs’.
There are 0 rows in 0 pages for object "sys.syssoftobjrefs".
DBCC results for ‘sys.queue_messages_1993058136′.
There are 0 rows in 0 pages for object "sys.queue_messages_1993058136".
DBCC results for ‘sys.queue_messages_2025058250′.
There are 0 rows in 0 pages for object "sys.queue_messages_2025058250".
DBCC results for ‘sys.queue_messages_2057058364′.
There are 0 rows in 0 pages for object "sys.queue_messages_2057058364".
DBCC results for ‘sys.filestream_tombstone_2089058478′.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2089058478".
DBCC results for ‘sys.syscommittab’.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for ‘sys.filetable_updates_2121058592′.
There are 0 rows in 0 pages for object "sys.filetable_updates_2121058592".
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DemoCorruptMetadata’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
On se rend vite compte que la quantité d’information qui nous intéresse ici reste négligeable par rapport à la quantité totale d’information produite en sortie par notre commande. Ici la commande DBCC CHECKDB a produit 162 lignes d’information alors que moins de 10 lignes nous intéresse ici. Heureusement pour nous, nous pouvons jouer avec les options NO_INFOMSGS et ALL_ERRORMSGS pour limiter le nombre d’informations produites par DBCC CHECKDB. Pour rappel NO_INFOMSGS permet d’éliminer les messages d’information produites en sortie et ALL_ERRORMSGS permet d’afficher toutes les erreurs rencontrées lors d’une opération de vérification de bases de données. Par défaut seulement les 1000 premiers messages d’erreur sont affichés depuis SQL Server Management Studio.
Utilisation des options NO_INFOMSGS, ALL_ERRORMSGS
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=1) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=2) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DemoCorruptMetadata’.
On a ici bien réduit le nombre de lignes produites par notre commande DBCC CHECKDB avec l’affichage du détail des erreurs rencontrées et 2 messages d’information qui ne sont ni plus ni moins qu’un récapitulatif de ces erreurs.
Gestion de la sortie de la commande DBCC CHECKDB dans un script T-SQL
Après avoir limité le nombre d’information utiles comment nous en servir pour construire une logique de maintenance par script ? L’idée ici est simplement de pouvoir détecter un problème d’intégrité et de prévenir les personnes concernées (notamment l’équipe DBA) qu’un problème est survenu à ce moment lÃ
A priori 2 voies d’exploitation évidentes peuvent être prises en compte :
Avec la variable @@ERROR :
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=1) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=2) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DemoCorruptMetadata’.
–> error during dbcc checkdb
La variable @@ERROR nous permet effectivement de pouvoir détecter les erreurs engendrées par la commande DBCC CHECKDB. Il suffit ensuite de coder la logique de maintenance qui nous intéresse. Cependant si on utilise notre script T-SQL dans un job SQL Server, les erreurs produites impliqueront une étape de job en échec. La question est donc de savoir ce résultat est attendu ou non ? Effectivement ce n’est pas parce qu’une commande DBCC CHECKDB produit une erreur qu’un job SQL Server doit forcément partir en échec. En réalité tout dépendra de la logique d’exécution globale concernée par ce script. On peut tout à fait traiter l’erreur de job et effectuer une surveillance sur l’état de ce job par envoi d’alerte ou via des outils tiers par exemple. On peut aussi vouloir enregistrer l’erreur sans que le job parte en échec, avec l’exécution d’étapes supplémentaires sans pour autant avoir une logique d’exécution complexe du style "si étape 1 échec –> aller à l’étape 3 sinon aller à l’étape 2 etc … A mon humble avis les jobs SQL Server ne sont pas vraiment désignés pour gérer des logiques complexes de workflow . Dans le cadre de notre script nous avons retenu une solution générique et simple : si une tâche génère une erreur alors celle-ci est enregistrée dans une table de log et l’information pourra être envoyé à posteriori par email. Le choix est laissé à l’utilisateur final.
Avec le bloc de gestion d’erreur BEGIN TRY, CATCH :
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DemoCorruptMetadata’.
Comme on peut le remarquer le bloc de gestion TRY , CATCH ne va malheureusement pas nous être d’une grande utilité ici car les dernières lignes d’information produites par la commande DBCC CHECKDB empêchent notre bloc de gestion d’erreur de détecter les erreurs produites par notre commande DBCC CHECKDB quelques lignes plus haut.
Avons nous une autre alternative dans ce cas ? La réponse est oui. Il est également possible d’enregistrer les erreurs produites lors d’une vérification d’intégrité dans une table SQL en utilisant l’option TABLERESULTS de cette manière :
Utilisation de l’option TABLERESULTS
Il suffit ici d’extraire les informations produites dans la table et de gérer sa propre logique de maintenance. Dans mon cas je remplis une variable de table avec le résultat de la commande DBCC CHECKDB et l’option TABLERESULETS et je vérifie qu’au moins une ligne existe pour détecter la présence d’une erreur (la commande DBCC CHECKDB ne produit aucune ligne d’erreur si aucun problème d’intégrité n’est détecté). L’information est ensuite enregistrée dans une table de log avec le numéro d’erreur et sera envoyé par mail à postériori.
L’option TABLERESULTS est-elle là solution miracle ? Malheureusement non car la commande DBCC CHECKDB peut produire certaines erreurs propres à son exécution et qui stopper franchement le script en cours d’exécution. C’est le cas de certaines erreurs sévères rencontrés par la commande DBCC CHECKDB.
Gestion des erreurs liées à l’exécution même de la commande DBCC CHECKDB
Voici quelques cas où l’exécution de la commande DBCC CHECKDB provoquera une erreur grave et un arrêt net du script T-SQL en cours d’exécution.
- Après avoir corrompu la page PFS (page id = 1) à l’aide d’un éditeur hexadécimal de ma base DemoCorruptMetada le script SQL se stoppera net avec le message d’erreur suivant :
Msg 0, Level 11, State 0, Line 0
A severe error occured on the current command. The results, if any, should be discarded
- De la même manière Après avoir supprimer une ligne dans la table système sys.syscolpars en redémarrant l’instance SQL Server en mode mono utilisateur et se connectant en mode administrateur dédié la commande DBCC CHECKDB génère le message suivant :
Msg 0, Level 20, State 0, Line 0
A severe error occured on the current command. The results, if any, should be discarded
Pour une description des niveaux d’erreurs voir la documentation en ligne SQL Server (section "Understanding DBCC errors messages"). Dans ce cas il faudra effectivement gérer l’erreur de manière différente. Dans mon cas au début de chaque tâche une ligne est enregistrée dans une table de log. Si le script T-SQL est arrêté a date de fin de la tâche ne sera pas renseigné. Un email est envoyé pour les cas suivants :
Bonne gestion d’erreur !!
David BARBARIN (Mikedavem)
MVP SQL Server
Chez un de mes clients on est venu me dire la chose suivante : visiblement lorsque j’utilise le hint NOLOCK ou un niveau de transaction READ UNCOMMITED lorsque j’exécute une requête je vois quand même des verrous posés sur mes tables alors que je croyais le contraire et du coup ma requête en lecture doit bloquer les mises à jour … A cela j’ai répondu : la vérité est ailleurs 
Plus sérieusement, la bonne question à se poser est la suivante : est-ce que le fait d’utiliser un hint NOLOCK ou un niveau de transaction permettant la lecture sale veut dire que aucun verrou n’est posé ?
Pour répondre prenons un exemple simple avec une requête dans la base de données AdventureWorksDW2012 :
Voyons les verrous posées par cette requête à l’aide de la DMV sys.dm_tran_locks :
…
Avec un niveau d’isolation de transaction en read committed (niveau d’isolation par défaut) :
Avec un niveau d’isolation de transaction en read uncommitted :
On voit d’abord que le niveau d’isolation de transaction en mode read uncommitted ne veut pas dire qu’il n’existe plus de verrou. Le 1er verrou (de type SHARED_TRANSACTION_WORKSPACE) nous intéresse moins ici car c’est un verrou posé lorsqu’il existe une connexion sur une base de données (en l’occurence database_id = 6 ici). Cela permet de protéger la base de données contre les suppressions accidentelles par exemple lorsqu’il existe une connexion sur cette dernière.
Les verrous suivants sont plus intéressants dans notre cas. On voit que la différence notoire est qu’il existe des verrous de type IS et type S dans un cas (read committed) et plus de verrou de ce type dans l’autre cas (read uncommitted). Le niveau d’isolation de transaction par défaut utilisé par SQL Server garantit une lecture propre des données c’est la raison pour laquelle il existe des verrous de type S et IS à différents niveaux (OBJECT et PAGE) . Avec le niveau d’isolation permettant de la lecture sale ces verrous n’existent évidemment plus mais d’autres verrous existent cependant comme celui que l’on voit dans la dernière image : verrou de type Sch-S. Ce verrou protège la table contre les modifications pendant qu’une lecture est effectuée même si celle-ci n’est pas propre.
Faisons le même test avec une table HEAP (sans index cluster) avec la requête suivante :
… et voyons les verrous posées :
Avec un niveau d’isolation de transaction en read committed (niveau d’isolation par défaut) :
Avec un niveau d’isolation de transaction en read uncommitted :
La aussi on peut voir qu’avec les verrous de type S ne sont plus présents sur les objets de type PAGE ou OBJET avec un niveau d’isolation de transaction en read uncommitted. On a néanmoins un type de verrou supplémentaire que l’on n’avait pas avec une table possédant un index cluster à la ligne 2 (resource_type = HOBT et resource_subtype = BULK_OPERATION). Ce type de verrou n’est présent que sur les tables HEAP pour protéger les lectures contre les pages potentiellement mal formatés via des opérations BULK (merci à Paul Randal pour la précision).
On vient donc de voir que la lecture sale sur SQL Server engendrait certains types de verrous. Cependant on peut pousser le raisonnement plus loin. Qui dit verrou veut dire aussi latch pour accéder aux structures physiques en mémoire … on peut essayer de voir si une requête avec un niveau d’isolation en read uncommitted produit des latchs. Pour cela on peut utiliser la DMV sys.dm_os_waiting_tasks comme ceci :
En parallèle la requête suivante est lancée :
Après avoir lancé une des requêtes avec un niveau d’isolation de transaction en read uncommitted on peut constater que cette dernière a effectivement générée des latchs de type SH dans notre cas :
On peut regarder à quel objet appartient la page 154 par exemple :
…
Pour conclure l’idée que l’utilisation de la lecture sale avec SQL Server ne génère pas de verrous est faussée. Certains les verrous de type S pouvant gêner les écritures sont absents mais on a pu constater au cours de ce billet que d’autres types de verrous étaient et bien présents ainsi que des latchs pouvant même aboutir à une potentielle contention !!
Bonne utilisation de lecture sale
David BARBARIN (Mikedavem)
MVP SQL Server
L’emprunt d’identité peut s’avérer nécessaire lorsque l’on commence à vouloir faire de la sécurité à un niveau granulaire relativement bas. Par exemple la politique de sécurité en vigueur peut nous contraindre à restreindre l’accès aux données via des procédures stockées. Cependant si ces données sont distantes et implique l’implémentation d’un serveur lié pour y avoir accès, cela peut donner matière à plus de réflexion. En effet, dans ce cas il va falloir donner les droits adéquates aux utilisateurs concernés sur le serveur distant pour accéder aux données, ce qui risque pour le coup d’augmenter la surface d’exposition. Un utilisateur A devra pouvoir exécuter une procédure stockée et sélectionner les données sur le serveur distant. L’utilisateur A peut par conséquent passer outre la procédure stockée et se connecter directement au serveur distant pour accéder aux données. Pour éviter cela on peut tout à fait utiliser le mécanisme d’emprunt d’identité dans une procédure stockée à l’aide de WITH EXECUTE AS et associer l’utilisateur dédié à cette tâche à un utilisateur sur le serveur distant. On bénéficie ainsi d’un contrôle d’accès beaucoup fort et maitrisé.
Le schéma ci-dessous illustre bien notre problématique :
Dans le 1er cas un mapping est nécessaire pour chaque utilisateur qui va vouloir accéder aux données du serveur distant Instance 2, ce qui signifie qu’il devra exister les mêmes logins sur le serveur distant Instance 2. Comme je l’ai évoqué au début cela implique également que les utilisateurs concernés pourront directement accéder aux données du serveur distant avec leurs informations de connexion.
Dans le 2ème cas nous mettons en place un mécanisme d’emprunt d’identité via l’option WITH EXECUTE AS USER dans la procédure stockée. L’idée ici est de n’autoriser que cet utilisateur à avoir accès aux données distantes.
La mise en place d’un tel mécanisme dans le contexte des serveurs liés nécessitent un certain paramétrage :
Si l’on utilise directement l’option WITH EXECUTE AS dans une procédure stockée de la manière suivante :
… avec l’appel suivant …
… il y a de grandes chances que cela ne fonctionne pas surtout si l’on a définit le contexte d’authentification de serveur lié suivant :
Dans ce cas seul le mapping défini pour l’utilisateur USER1 fonctionnera et aucun autre login ou aucun autre type d’authentification ne fonctionnera.
Si maintenant j’exécute ou je crée la procédure stockée dbo.GetRemoteData dans le contexte d’un utilisateur différent de l’utilisateur USER1 ayant les droits de création et d’exécution je peux me retrouver avec le message suivant :
Pourtant me direz-vous que nous avons bien défini l’emprunt d’identité dans la procédure stockée et qu’ici visiblement SQL Server n’est pas capable de trouver le mapping défini dans les options de sécurité du serveur lié. En réalité il faut bien comprendre ce qui se passe lorsqu’une procédure stockée est compilée et lorsque celle-ci est exécutée (run-time context). SQL Server compile une procédure stockée avec les credentials de l’appelant de la procédure et non ceux de l’utilisateur associée à l’emprunt d’identité. C’est pendant l’exécution de la procédure que le mécanisme d’emprunt d’identité jouera tout son rôle. Lorsqu’on accède aux données aux travers d’un serveur lié la compilation va générer l’erreur 7416 si l’appelant de la procédure n’a pas un mapping inter-instances configuré (SQL Server va vérifier lors de la compilation l’accès aux différents objets du serveur distant).
Pour contourner ce problème on peut user de 2 mécanismes qui permettent de différer l’exécution de la procédure stockée et de laisser le mécanisme d’emprunt d’identité opérer en premier lieu :
Par exemple :
ou encore :
Le choix de la méthode dépendra bien du contexte.
Bonne programmation !
David BARBARIN (Mikedavem)
MVP SQL Server
© 2000-2020 - www.developpez.com
Partenaire : Hébergement Web
