Archives pour la catégorie SQL Server 2012

Créer un serveur lié avec une connexion en lecture seule vers un réplica secondaire SQL Server 2012 AlwaysOn

Mis en avant

Publié le par
Répondre

Petite question que je me posais aujourd’hui vu qu’en ce moment je travaille souvent sur des environnements haute disponibilité avec SQL Server 2012 et AlwaysOn. Est-il possible de créer un serveur lié qui se connecte sur un réplica secondaire en lecture et profiter du coup d’une redirection transparente en cas de changement de rôle de réplica lors d’un basculement automatique par exemple ?

Le meilleur moyen de le savoir est de faire un test.

Créons tout d’abord un serveur lié en utilisant le provider SQL Server Native Client 11.0 pour pouvoir utiliser le paramètre de connexion applicationintent=readonly. Le script de création du serveur lié est le suivant :

 

EXEC master.dbo.sp_addlinkedserver    
 @server = N'LST_APPLIX',
 @srvproduct=N'',    
 @provider=N'SQLNCLI', -- Provider SQL Native Client    
 @datasrc=N'LST_APPLIX', -- Availability group listener    
 @provstr=N'applicationintent=readonly',  -- Readonly intent parameter    
 @catalog=N'ApplixEnterprise' --Target database to connect

 

icon_arrow Mon groupe de disponibilité est composé de 3 réplicas avec :

  • REPLICA1 (réplica primaire)
  • REPLICA2 (réplica secondaire en lecture seule utilisé en priorité)
  • REPLICA3 (réplica secondaire en lecture seule utilisé si REPLICA2 n’est plus joignable)

 

icon_arrow Mon serveur lié est quant à lui configuré sur un serveur standalone à part.

 

La requête suivante illustre l’état de note scénario :

 

SELECT  
 g.name AS group_name,    
 r.replica_server_name,    
 rs.role_desc,    
 rs.operational_state_desc,    
 ro.replica_server_name AS replica_ro_server_name,    
 rol.routing_priority,    
 r.secondary_role_allow_connections_desc    
FROM sys.dm_hadr_availability_replica_states AS rs    
 JOIN sys.availability_groups AS g    
  ON rs.group_id = g.group_id    
 JOIN sys.availability_replicas AS r    
  ON r.replica_id = rs.replica_id    
   AND r.group_id = rs.group_id    
 JOIN sys.availability_read_only_routing_lists rol    
  ON rol.replica_id = r.replica_id    
 JOIN sys.availability_replicas AS ro    
  ON ro.replica_id = rol.read_only_replica_id    
WHERE g.name = 'ApplixGrp'    
 AND r.replica_server_name = 'REPLICA1'

 

image

 

Tentons de retrouver le serveur sur lequel le serveur lié pointe :

SELECT *    
FROM OPENQUERY(LST_APPLIX, 'SELECT @@SERVERNAME AS SERVER_NAME');

qui donne

image

 

Ok visiblement cela fonctionne. En utilisant le listener la requête est automatiquement redirigée vers le REPLICA2 comme le stipule notre configuration.

 

On lance maintenant un basculement manuel de groupe de disponibilité ApplixGrp de REPLICA1 vers REPLICA2. REPLICA2 devient donc le nouveau primaire.REPLICA1 et REPLICA3 deviennent les réplicas secondaires. On exécute à nouveau nos requêtes :

 

SELECT  
 g.name AS group_name,    
 r.replica_server_name,    
 rs.role_desc,    
 rs.operational_state_desc,    
 ro.replica_server_name AS replica_ro_server_name,    
 rol.routing_priority,    
 r.secondary_role_allow_connections_desc    
FROM sys.dm_hadr_availability_replica_states AS rs    
 JOIN sys.availability_groups AS g    
  ON rs.group_id = g.group_id    
 JOIN sys.availability_replicas AS r    
  ON r.replica_id = rs.replica_id    
   AND r.group_id = rs.group_id    
 JOIN sys.availability_read_only_routing_lists rol    
  ON rol.replica_id = r.replica_id    
 JOIN sys.availability_replicas AS ro    
  ON ro.replica_id = rol.read_only_replica_id    
WHERE g.name = 'ApplixGrp'    
 AND r.replica_server_name = 'REPLICA2'

 

image

 

et

SELECT *    
FROM OPENQUERY(LST_APPLIX, 'SELECT @@SERVERNAME AS SERVER_NAME');

qui donne

image

 

Comme on peut le voir l’utilisation d’un serveur lié vers une architecture SQL Server 2012 AlwaysOn avec une redirection des connexions en lecture seule vers les réplicas secondaires fonctionne très bien !

 

Bon paramétrage de serveurs liés

SET TRANSACTION ISOLATION LEVEL READ COMMITTED et NOLOCK = pas de verrous ?

Mis en avant

Publié le par
Répondre

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 Sourire

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 :

SELECT *  
FROM dbo.FactInternetSales AS f  
CROSS JOIN dbo.FactInternetSales AS f2

 

Voyons les verrous posées par cette requête à l’aide de la DMV sys.dm_tran_locks :

SELECT  
 resource_database_id,    
 resource_type,  
 resource_subtype,    
 request_mode,  
 request_type,    
 request_status,    
 request_owner_type    
FROM sys.dm_tran_locks    
WHERE request_session_id = "session"

…

 

icon_arrow Avec un niveau d’isolation de transaction en read committed (niveau d’isolation par défaut) :

image

 

icon_arrow  Avec un niveau d’isolation de transaction en read uncommitted :

image

 

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 :

SELECT *  
FROM dbo.DatabaseLog AS f    
CROSS JOIN dbo.DatabaseLog AS f2

 

… et voyons les verrous posées :

icon_arrow Avec un niveau d’isolation de transaction en read committed (niveau d’isolation par défaut) :

image

 

icon_arrow  Avec un niveau d’isolation de transaction en read uncommitted :

image

 

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 :

-- Création à la volée d'une table temporaire pour les tâches en attente    
SELECT TOP 1 *  
INTO #waiting_tasks    
FROM sys.dm_os_waiting_tasks;    
GO

-- On vide la table avant le test  
TRUNCATE TABLE #waiting_tasks;    
GO

-- Récupération des tâches en attente générées par la requête de test    
WHILE 1 = 1    
BEGIN    
 INSERT #waiting_tasks    
 SELECT *    
 FROM sys.dm_os_waiting_tasks    
 WHERE SESSION = "session"  
END

 

En parallèle la requête suivante est lancée :

SELECT *  
FROM dbo.DatabaseLog AS f  
CROSS JOIN dbo.DatabaseLog AS f2

 

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 :

image

 

On peut regarder à quel objet appartient la page 154 par exemple :

DBCC TRACEON(3604);  
GO    

DBCC PAGE(6, 1, 154) WITH TABLERESULTS;    
GO

 

image

…

image

 

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 Sourire 

David BARBARIN (Mikedavem)
MVP SQL Server

SQL Server 2012 : Installation et intégration des mises à jour

Mis en avant

Publié le par
Répondre

Pour ceux qui ont eu à installer SQL Server 2012, vous avez sans doute remarqué que le processus d’installation incorporait maintenant des mises à jour avant même d’installer SQL Server. Cette petite nouveauté est en réalité très pratique lorsque l’on doit installer des binaires dans une certaine version et que l’on doit installer un service pack ou un cumulative update. On remplace les fameuses installations "slipstream" où l’incorporation d’un service pack dans des binaires d’installation était une opération plutôt laborieuse. De plus il existe 2 méthodes de mise à jour : l’une permet de récupérer les mises à jour via internet et l’autre permet de spécifier un dossier dans lequel il est possible de placer les mises à jour d’installation à incorporer durant l’installation d’une instance SQL Server. Par exemple il est tout à fait possible d’incorporer en même temps le service pack 1 de SQL Server 2012 et le cumulative update 3. Si plusieurs cumulative update sont présent l’installation ne prendra que le dernier en vigueur !

 

icon_arrow J’ai créé ici un dossier nommé SQL2012_SP1_CU3 où j’ai placé les fichiers d’installation du service pack1 de SQL Server 2012 et 2 cumulatives updates : CU1 et CU3

 

sp1_et_cu1_et_cu3

 

icon_arrow Il suffit ensuite d’utiliser les paramètres supplémentaires fournis avec SQL Server 2012 (/UpdateEnabled et /UpdateSource). J’effectue ici une installation en ligne de commande

 

image

 

icon_arrow Lorsque l’installation se lance on peut remarquer que l’installation détecte les mises à jour et ne prend que le service pack 1 et le dernier cumulative update (CU3)

 

install_gui_product_updates

 

Le cumulative update 3 correspond au KB 2812412 :

kb_cu3

 

icon_arrow Une fois l’installation terminée une petite vérification de la version :

 

result_after_install

 

Bonne installation !!

David BARBARIN (Mikedavem)
MVP SQL Server

A propos de l’erreur 33203 : SQL Server audit could not write to the security log

Mis en avant

Publié le par
Répondre

Lors de la mise en place des audits de sécurité je me suis heurté à cette fameuse erreur 33203 sur une instance nommée qui indique explicitement que SQL Server ne peut pas écrire dan le journal de sécurité Windows.  Ma configuration est la suivante : SQL Server 2012 SP1  et Windows 2012 Server.

Je précise avant de commencer que les prérequis de fonctionnement des audits avec le journal de sécurité Windows sont respectés. La conséquence directe de l’erreur 33203 est qu’au évènement n’est enregistré dans le journal de sécurité Windows, ce qui est plutôt gênant pour des audits. De plus les journaux des erreurs Windows et SQL Server ne sont pas spécialement bavards quant à la cause de cette erreur.

 

image

 

Dans ce cas comment savoir ce qui empêche SQL Server d’écrire dans le journal ? Je dois dire que les outils sysinternals sont bien utiles dans ces moments et en particulier procmon.

Une capture procmon me donne ceci lorsque j’active un objet d’audit depuis SQL Server :

 

image

On peut remarquer ici que SQL Server tente un accès à la clé de registre HKLM\System\CurrentControlSet\Services\EventLog\Security et que cet accès lui est visiblement refusé. Un message est également inscrit dans le journal des erreurs SQL. Pas de souci, nous allons configurer les permissions adéquates sur cette clé de registre.

 

image

 

Le paramétrage des permissions a visiblement permis à l’instance SQL Server de créer une nouvelle clé de registre dans HKLM\System\CurrentControlSet\Services\EventLog\Security :

image

 

…

image

 

Me croyant sauvé, j’active mes audits mais je me heurte à une nouvelle erreur. Une nouvelle trace procmon me révèle le problème suivant :

 

image

 

SQL Server tente de lire le fichier C:\Windows\System32\LogFiles\Sum\Api.log mais encore un fois ce dernier se voit refuser l’accès. Le paramétrage en lecture seule du fichier Api.log me permet enfin d’activer mes audits et de valider que les évènements soient bien enregistrés dans le journal de sécurité Windows. Ce deuxième problème visiblement a été remonté chez Microsoft et est toujours d’actualité. A noter que je n’ai eu ce souci que pour mon instance nommé dans mon cas.

 

Quelques remarques supplémentaires :

  • Le fait d’enlever le compte de service SQL des permissions de la clé de registre HKLM\System\CurrentControlSet\Services\EventLog\Security ne semble plus être gênant à partir du moment où la nouvelle clé a été créé.
  • Il n’est pas possible cependant d’enlever les permissions sur le fichier C:\Windows\System32\LogFiles\Sum\Api.log sous peine d’avoir dans le journal des évènements Windows le type d’erreur suivant et plus d’évènement d’audit enregistré. A voir si le problème sera résolu plus tard par Microsoft (dans un CU4 peut être ?) avec Windows Server 2012. L’ensemble des problèmes répertoriés concerneraient Windows Server 2012.
  • Je ne pense pas que le problème soit lié à la version de SQL Server. Si j’ai le temps je testerai sur une version SQL Server 2008 R2 et mettrait à jour mon blog.

 

image

 

Bonne activation d’audit !! Sourire

 

David BARBARIN (Mikedavem)
MVP SQL Server

Problème d’utilisation de l’option WITH EXECUTE AS dans une procédure stockée avec les serveurs liés

Mis en avant

Publié le par
Répondre

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 :

 

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

 

image

 

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

 

image

 

La mise en place d’un tel mécanisme dans le contexte des serveurs liés nécessitent un certain paramétrage :

  • L’activation obligatoire de l’option de bases de données trustworthy. Malheureusement seule cette option est viable lorsqu’il s’agit d’accès inter instances. L’utilisation des certificats dans ce cas ne fonctionne pas.
  • Différer l’exécution de la procédure stockée concernée. Je n’ai pas trouvé d’article Microsoft sur le sujet mais simplement un item connect. C’est ce point que je vais détailler ici.

 

Si l’on utilise directement l’option WITH EXECUTE AS dans une procédure stockée de la manière suivante :

 

CREATE PROCEDURE dbo.GetRemoteData
WITH EXECUTE AS 'USER1'
AS

SELECT col1, col2
FROM [linked_server].[DATABASE].[schema].[TABLE]
GO

 

… avec l’appel suivant …

 

EXEC dbo.GetRemoteData

 

… 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 :

 

image

 

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 :

 

image

 

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 :

  • Créer une nouvelle procédure stockée qui va scinder l’exécution de notre procédure stockée initiale en 2 étapes
  • Encapsuler le code à exécuter de la procédure stockée avec EXEC( »), ce qui permettra au final de faire la même que dans la 1ère solution.

 

Par exemple :

CREATE PROCEDURE dbo.GetRemoteDataWithImpersonate
WITH EXECUTE AS 'USER1'  
AS

EXEC dbo.GetRemoteData
GO

ou encore :

CREATE PROCEDURE dbo.GetRemoteData    
WITH EXECUTE AS 'USER1'    
AS

EXEC('SELECT col1, col2 FROM [linked_server].[database].[schema].[table]')    
GO

 

Le choix de la méthode dépendra bien du contexte.

 

Bonne programmation !

David BARBARIN (Mikedavem)
MVP SQL Server

Prochain évènement GUSS le 24 avril – 19h00 : Webcast sur SQL Server 2012 AlwaysOn pour les fermes Sharepoint 2013

Mis en avant

Publié le par
3

J’aurai le plaisir d’animer le prochain évènement du GUSS sous un nouveau format. Le principe est simple : où que vous soyez, vous pouvez rejoindre le webcast en ligne avec le son, la vidéo et un chat pour poser des questions.

 

Ce webcast sera composé de 2 sujets de 30 minutes :

 

icon_arrow  Always-On : rappel des bases
La première session rappellera le fonctionnement et les bases de la Haute-Disponibilité avec AlwaysOn (avantages, architecture, groupes de disponibilités, type de réplications de données, etc.).

 

icon_arrow AlwaysOn dans les fermes sharepoint 2013
Mise en pratique avec une implémentation d’AlwaysOn avec SharePoint, produit très consommateur de SQL Server et avec des topologies souvent complexes. L’idée ici est d’avoir une vision beaucoup plus orientée base de données et analyser les avantages mais aussi les impacts à utiliser AlwaysOn avec Sharepoint (création / supression d’applications, authentification, utilisation des connexions readonly, maintenance des bases de données, monitoring …)

 

Les informations de connexion vous seront communiqués la veille sur le site du GUSS. En espérant vous voir nombreux !

 

David BARBARIN (Mikedavem)
MVP SQL Server

MVP SQL Server : Quatrième chapitre pour l’année 2013

Mis en avant

Publié le par
2

Pas de poisson d’avril et une vraie bonne nouvelle de la part de Microsoft aujourd’hui : le renouvèlement de mon titre de MVP SQL Server pour la 4ème fois.

J’en profite pour remercier Martine Tiphaine et Microsoft une nouvelle fois !

 

mvp

 

David BARBARIN (Mikedavem)
MVP SQL Server

Cluster Windows et fileshare quorum : comment changer le chemin de partage du quorum

Mis en avant

Publié le par
Répondre

Sur une installation SQL Server 2012 Always-On avec un cluster composé d’un quorum nÅ“uds et partage de fichiers majoritaire j’ai eu à changer le chemin de partage du quorum. Avec surprise j’ai pu m’apercevoir rapidement que le changement du chemin au travers de la console de gestion du cluster était bien pris en compte mais restait actif sur l’ancien partage. J’ai pu reproduire ce problème sur mon environnement de test

Voici donc la situation initiale :

image

 

image

 

Le partage réseau n’est plus accessible dans mon cas. Il faut donc trouver un nouveau path.

image

 

image

 

Cependant une fois configurée on peut remarquer que la nouvelle ressource est effectivement créée et pire que l’ancienne ressource est toujours utilisée comme faisant parti du quorum.

image 

 

A ce stade l’interface graphique ne nous empêche de supprimer la ressource concernée. Si on tente l’opération en passant par les cmdlets powershell du cluster nous nous heurtons également à un message d’erreur qui stipule qu’une ressource cluster core ne peut être supprimée.

image

 

Donc comment faire en sorte que notre changement de chemin soit pris en compte par le cluster. En réalité l’astuce consiste tout simplement à supprimer temporairement ce type du quorum de cluster et de revalider le nouveau chemin une nouvelle fois comme ceci :

image

 

image

 

Une fois le changement de quorum changé la ressource qui nous gênait a bien été supprimée.

image 

 

On peut également supprimé dans notre cas la ressource fileshare restante. Enfin il ne reste plus qu’à revalider notre type de quorum avec le nouveau chemin en suivant la procédure décrite un peu plus haut.

image

 

Bonne configuration de quorum !

 

David BARBARIN (Mikedavem)
MVP SQL Server

Virtualisation et SQL Server 2012 : disques non reconnus avec VSphere 5.X

Mis en avant

Publié le par
Répondre

Lors d’une installation SQL Server 2012 chez un de mes clients j’ai été confronté à un problème surprenant. La machine virtuelle comportait 3 disques avec les partitions C, D et E et SQL Server ne reconnaissait uniquement que le premier disque. Après avoir cherché un petit moment et notamment des problèmes aux niveaux des permissions de compte de service SQL nous nous sommes aperçu que le problème venait en réalité des types de disques que VSphere présentait au système d’exploitation. Les disques D et E étaient vus comme périphériques amovibles ce qui peut être gênant pour SQL Server !!

L’astuce consiste donc à désactiver cette option au niveau de la machine virtuelle (devices.hotplug = false).

>> Le KB VMWARE pour plus de précision

 

Bonne installation !!

David BARBARIN (Mikedavem)
MVP SQL Server

Instant File Initialization et SetFileValidData()

Mis en avant

Publié le par
Répondre

Instant File Initialization est une option connue des DBA et permet d’accélérer certaines opérations faisant de la réservation d’espace disque. Avec SQL Server cela se traduit par les opérations de création, restauration de bases de données ou encore ajout de fichiers, modification de taille ou encore expansion de fichiers … Bref cette option est souvent très bénéfique pour les performances mais celle-ci exige quelques prérequis qui sont les suivants :

  • Le fichier n’est pas un fichier journal d’une base de données
  • Le système d’exploitation supporte l’utilisation de la fonction SetFileValidData()
  • Le compte de service SQL possède le privilège SE_MANAGE_VOLUME_NAME
  • Le fichier n’est pas un fichier sparse
  • Le fichier n’est pas concernée par du chiffrement TDE
  • Trace flag 1806 n’est pas activé sur l’instance SQL (ce traceflag désactive l’utilisation de cette fonctionnalité)

 

Ce qui nous intéresse ici c’est l’utilisation de la fonction SetFileValidData(). Nous voila donc plongé dans la documentation des  API Windows et de notre fonction SetFileValidData(). Voici ce que la documentation nous propose :

Sets the valid data length of the specified file. This function is useful in very limited scenarios. For more information, see the Remarks section.

Dans la section remarques nous avons :

The SetFileValidData function sets the logical end of a file. To set the size of a file, use the SetEndOfFile function. The physical file size is also referred to as the end of the file.

Each file stream has the following properties:

  • File size: the size of the data in a file, to the byte.
  • Allocation size: the size of the space that is allocated for a file on a disk, which is always an even multiple of the cluster size.
  • Valid data length: the length of the data in a file that is actually written, to the byte. This value is always less than or equal to the file size.

…

The SetFileValidData function allows you to avoid filling data with zeros when writing nonsequentially to a file. The function makes the data in the file valid without writing to the file. As a result, although some performance gain may be realized, existing data on disk from previously existing files can inadvertently become available to unintended readers

…

A caller must have the SE_MANAGE_VOLUME_NAME privilege enabled when opening a file initially

 

Pour résumer : cette fonction va nous permettre d’éviter le remplissage de zéros dans un fichier lorsque les écritures ne sont pas séquentielles

Pourquoi pour des opérations non séquentielles ?  Effectivement si l’on écrit de manière séquentielle dans un fichier la phase d’écriture de zéro est relativement rapide puisque l’on écrit toujours à la suite des données précédentes

Cependant si l’on écrit de manière aléatoire, le problème est tout autre. Imaginez que l’on écrive la première au début du fichier et la 2ème fois à la fin, il va donc falloir remplir de zéros le fichier de la première écriture jusqu’à la 2ème. La documentation le stipule bien : chaque fichier doit posséder notamment une propriété Valid data length qui correspond à la longueur des données écrites dans le fichier.  Cette opération, vous l’avez compris, peut prendre du temps en fonction de la longueur à initialiser. SQL Server écrit de manière aléatoire dans le fichier même lors de son initialisation. C’est la raison pour laquelle l’utilisation de cette option est très intéressante ici.

 

Ecriture séquentielle :

 

image

 

Ecriture aléatoire :

 

image

 

L’utilisation de procmon peut nous permettre de visualiser l’utilisation des fonctions SetEndOfFile() et SetFileValidData(). J’ai simplement créé une base de données simple avec le fichier de données TestIntantFileInitializationWith.mdf.

 

  • Dans le cas où Instant File Initialization n’est pas activé

 

image

 

On ne voit ici que l’utilisation de la fonction SetEndOfFile(). Cette fonction est utilisée dans tous les cas pour fixer physiquement la fin du fichier. Dans le cas présent la fin du fichier est fixée à 10485760 octets soit 10Go.

 

  • Dans le cas où Instant File Initialization est activé

 

image

 

Une fonction supplémentaire est utilisée ici SetFileValidDate() qui permet de valider la longueur des données dans le fichier mdf :  ValidDataLength : 10485760 (10Go de données dans un fichier de 10Go)

 

David BARBARIN (Mikedavem)
MVP SQL Server