Developpez.com
Accueil
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 !
David BARBARIN (Mikedavem)
MVP SQL Server
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 :
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 :
Ecriture aléatoire :
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.
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.
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
Lors d’une de mes dernières interventions nous avions implémenté chez un client le chiffrage des connexions SQL Server via SSL avec la mise en place d’un certificat. Ce même client m’ a demandé s’il était possible d’utiliser ce même certificat pour chiffrer une de leurs bases de données via TDE au lieu d’utiliser un certificat auto-signé. Vu de loin on pourrait penser que oui et que l’implémentation du certificat dans SQL Server est une chose aisée mais il n’en est rien. La raison est la suivante : l’export du certificat avec la clé privée au travers du magasin de certificat Windows génère un fichier avec l’extension .pfx qui n’est pas exploitable directement par SQL Server. Il faut donc trouver un moyen d’extraire de ce fichier le certificat et la clé privée dans 2 fichiers .cer et .pvk. Pour cela nous allons utiliser openssl.
Pour commencer et après avoir exporté le certificat du magasin certificat, on se retrouve avec un fichier ayant l’extension .pfx. Ce fichier stocke notre certificat, la clé publique et la clé privée.
…
Ensuite j’ai utilisé la version light de SSL (Win32_OpenSSL_v1.0.1c_light) et j’ai installé au préalable les composants redistribuables C++ 2008 (prérequis à l’installation de openSSL).
Une fois installée il suffit de lancer les commandes suivantes pour :
Ce qui nous donne les fichiers suivants :
Il suffit ensuite de créer le certificat correspondant dans SQL Server dans le contexte de la base master avec le certificat et la clé publique (.cer) et la clé privée correspondante (.pvk) :
,
Vérification de la création correcte de notre certificat à l’aide de la vue système sys.certificates :
Bien entendu j’avais déjà créé la clé de service master (SERVICE MASTER KEY) sur le serveur SQL. C’est donc en toute logique que la clé privée du certificat que je viens de créer soit chiffrée par la clé master ici. Il ne reste ici plus qu’à créer la clé de chiffrement de la base de données qui sera elle même chiffrée par notre certificat.
Bon chiffrement !
David BARBARIN (Mikedavem)
MVP SQL Server
Lors d’une prestation chez un client, celui-ci m’a demandé s’il était possible d’ajouter un fichier à une base de données concernée par une session en miroir. La réponse est évidement oui mais cette opération nécessite quelques actions supplémentaires pour qu’elle soit également validée sur le serveur en miroir. En effet l’ajout d’un fichier de bases de données va suspendre la session en miroir comme on peut le voir sur l’image ci-dessous :
Le fait de reprendre la session ne changera rien. Si on jette un coup d’œil dans le journal des évènements SQL Server sur l’instance en miroir on peut voir le message suivant :
La modification du schéma de bases de données n’a pas été répercuté automatiquement sur l’instance en miroir et par conséquence la session en miroir est suspendue. La question évidente ici est comment répercuter la modification de schéma de base sur l’instance en miroir ? L’opération n’est pas très compliquée en réalité : il suffit de :
Sur le serveur principal :
Sur le serveur miroir :
Sur le serveur principal :
Et voilà cette manipulation permet de pouvoir resynchroniser nos bases de données en miroir sans avoir à effectuer une restauration complète
David BARBARIN (Mikedavem)
MVP SQL Server
J’ai eu récemment à définir une procédure d’installation de serveurs liés SQL Server pour une application. Cette procédure doit être bien entendu testée en environnement de qualité et en production. De plus chez mon client, un 2ème environnement de qualité est prévu en parallèle pour installer l’application concernée. Le principal souci ici est que les noms de serveurs liés vont changés et que cela risque d’avoir un impact au niveau du code de l’application. Nous ne pouvions pas nous permettre de mettre à jour l’ensemble du code TSQL applicatif à chaque changement d’environnement. Pour répondre à cette problématique nous avons choisi d’utiliser des alias SQL Server. L’utilisation des alias est beaucoup plus flexible avec les serveurs liés. Il suffit de changer ces noms sans avoir un impact sur le code.
Mais revenons à notre problématique initiale : comment scripter la création des alias SQL Server ? Il n’existe pas de procédure système T-SQL qui permette de faire cela. La solution ici est d’utiliser les classes WMI correspondantes (ici j’utilise un SQL Server 2008). Comme nous sommes à l’air du PowerShell voici le script utilisé permettant la création d’alias sous SQL Server :
… à utiliser de la manière suivante :
Vous pouvez tout à fait modifier ce script et le rendre plus paramétrable (nom de l’alias, no de port etc …). Encore une chose, en fonction du besoin vous allez sans doute devoir installer les alias pour les 2 types d’architecteur x86 et x64. Pour se faire il suffit de lancer le script avec Windows PowerShell (x64) et Windows PowerShell (x86).
Bon déploiement
David BARBARIN (Mikedavem)
MVP SQL Server
Un problème assez particulier que je viens de rencontrer au cours d’un audit d’un cluster SQL chez un de mes clients. Je vois une multitude d’erreurs dans le journal des évènements 1205 et 1069 liée à une ressource DTC. Le hic c’est qu’il existait bien une ressource DTC sur le serveur mais elle n’était pas concernée. Me voilà bien avancé avec mon erreur …
Pour bien situer le contexte voici l’erreur rencontré dans le journal des évènements:
Seulement en regardant les ressources au niveau du cluster management en mode GUI on voit que la ressource DTC est en ligne et fonctionne correctement. Je passe en mode commande (cluster.exe) et voici ce que je remarque :
J’ai en réalité 2 DTC sur mon cluster SQL. La ressource MSDTC-SQL2005Dtc est la ressource qui a été créée lors de l’installation du cluster SQL. En revanche la ressource DTC (statut failed) est présente dans le groupe Cluster Group mais n’est pas visible dans la console cluadmin.msc. Visiblement c’est cette 2ème ressource qui pose problème. A noter que les ressources qui font parti du groupe Cluster Group ne sont pas visible en mode GUI.
La suppression de cette ressource fera disparaitre le message d’erreur intermittent présent dans les différents journaux d’évènements.
En vérifiant dans les services de composants on peut s’assurer que c’est bien la ressource cluster qui sera utilisé :
David BARBARIN (Mikedavem)
MVP SQL Server
Le premier volet des journées SQL Server a visiblement connu un très grand succès. C’est la raison pour laquelle GUSS s’est relancé dans la préparation d’un second volet qui devrait se dérouler en décembre. Les dates définitives seront communiqués un peu plus tard. Cependant pour que cet évènement soit de nouveau un succès nous avons besoin de vos avis qui se présente sous la forme d’un sondage qui ne vous prendra que quelques minutes de votre temps 
et qui nous permettront de mieux cibler vos attentes à tous les niveaux (contenu des sessions, organisation etc …)
Pour le remplir c’est par ici
Merci par avance !!
David BARBARIN (Mikedavem)
MVP SQL Server
Pour ceux qui se demandent comment interpréter les noms de statistiques créées automatiquement par l’optimisateur de requêtes ce billet est pour vous 
. Comme vous le savez sans doute lorsqu’un index créé des statistiques lui sont également associées. Ces dernières servent à l’optimisateur de requêtes afin de déterminer un plan optimal pour retrouver les données initiés par une requête. Autrement dit en fonction des cardinalités ou de la sélectivité des données sous jacentes celui-ci va déterminer quelle(s) méthode(s) il devra pour utiliser pour les retrouver avec un minimum de coût. Cependant que se passe-t-il lorsqu’une requête composée d’un prédicat ne concerne pas un index ? He bien celui-ci va créer des statistiques sur la ou les colonnes concernées pour pouvoir estimer la cardinalité des données à retourner. Ces statistiques se retrouvent sous la forme _WA_Sys_00000004_0F382DC6 par exemple. Comment interpréter ce nom plutôt abscons à première vue. C’est ce que nous allons voir dans la suite de billet.
Il peut arriver parfois de vouloir retrouver un login Windows via son SID (ou Security Identifier) au format Windows sur une instance SQL Server. Le problème est que le SID stocké sur SQL Server est au format varbinary(85). Il nous faut donc formater la valeur de cette colonne pour pouvoir la mettre au format Windows S-X-X-XX-XXXXXXXXXX-XXXXXXXXXX-XXXXXXXXX-XXXX.
Chez un de mes clients qui utilise SCOM pour monitorer les jobs SQL en erreur voici ce que l’on peut voir comme message lorsqu’un job lié à la récupération des données avec data collector (extraction ou updload) part en erreur : « Set_{xxxxxxxxxxxxxxxxx}_Master_package_Upload » Failed. Autant dire qu’avec ce type de message cela reste compliqué de savoir quel collection set, quel job SQL ou quel package SSIS est à l’origine de l’alerte. Voici un script SQL qui permet de déterminer rapidement ces informations.
© 2000-2020 - www.developpez.com
Partenaire : Hbergement Web
