Azure Monitor – un service sous utilisé

Microsoft Azure se compose de plus de 200 services à l’heure où ce billet est rédigé. Certains sont très connus et largement utilisés, comme les services Web, des machines virtuelles ou les services liés aux données comme SQL Azure Databases dans ses différentes déclinaisons (Single Database, Elastic Pool, Managed Instance et les containers ACI ou AKS, sans oublier SQL Edge) ou les comptes de stockage. Les services d’intelligence artificielle, d’IoT, ou d’identité sont également régulièrement mis en lumière.

Et puis il y a des services, pourtant essentiels, mais dont on parle moins. Les service réseau en font partie. C’est pourtant une pièce angulaire de toute architecture dont la maitrise est assez complexe il faut bien l’avouer. Mais tout autant que l’est la maitrise d’une infrastructure réseau OnPrem ! Chaque métier a son équivalent cloud avec de nouvelles compétences à acquérir et de nouveaux défis à relever. Le passage au cloud marque le début d’un nouveau chapitre de votre vie professionnelle.

Et puis un service dont on parle finalement peu, le monitoring et l’alerting. OnPrem, il faut l’avouer c’est souvent le parent pauvre. Ce n’est pas très fun à mettre en œuvre, on se pose énormément de question sur l’outil à utiliser (Outil payant, solution OpenSource …).

Azure Monitor offre un service très intéressant en centralisant les informations issues des métriques applicatives (votre appli .net), de l’infrastructure au travers d’agents et des différentes ressources Azures comme SQL Server par exemple.

De quoi suivre les performances de bout en bout … Un must have en termes de troubleshooting avec des outils de visualisation intégrés ou tiers (Power BI),

3 vidéos ont été publiées sur la chaine YouTube Microsoft Azure. Prenez quelques minutes pour les consulter.

Pour SQL Server, au-delà des métriques « standard », je vous suggère de ne pas faire l’impasse sur SQL Insights, qui ajoute une VM Telegraf, qui va vous permettre d’unifier la vision des performances IaaS et PaaS, y compris si vous avez des cluster Always On Availability Groups.

La surveillance, l’automatisation de certaines tâches en réponse à des conditions de performance, l’observabilité sont des thèmes qui prennent de plus en plus d’importance au sein d’une DSI. Gageons qu’Azure Monitor va encore évoluer …

Happy monitoring !

Publié dans Non classé | Laisser un commentaire

NAS Synology – Exécuter SQL Server

Depuis bien des années j’utilise des NAS Synology. D’une part pour stocker des fichiers liés à mon activité professionnelle, come stockage partagé de mon cluster de voyage (en plus d’utiliser le stockage cloud OneDrive et Dropbox synchronisés sur 4 appareils distincts !) mais aussi pour des besoins plus personnels comme le support des photos entre autres.

Pourquoi un NAS plutôt que des disques externes : tout simplement pour pouvoir bénéficier de la redondance RAID1 ou RAID5 offerte par ces boitiers. Oui, j’ai aussi des disques externes, en archive externalisée mais je n’ai qu’une trais faible confiance quant à la durabilité. 1 égale 0. Si vous perdez ce disque, vous avez tout perdu. 2 égale 1. Avec un RAID1, si un disque tombe en panne, vous avez la chance de récupérer vos précieuses données car elles sont également présentes sur le second disque.

Et pour être transparent, je n’ai pas 1 NAS, mais 3, avec des synchronisations entre ces différents appareils. Pour ceux qui ne me connaissent pas, oui, je travaille énormément sur les volets la partie HA et DR de SQL Server …

Revenons à nous moutons, ou à nos NAS. Pourquoi Synology ? Probablement parce qu’à mes yeux il s’agit du meilleur matériel, avec de surcroit une offre logicielle vraiment intéressante. Mais des constructeurs comme QNAP et autres ne sont pas loin, je ne veux pas déclencher une querelle de clochers.  

Autre la gestion de mes fichiers, les NAS me servent de poste de pilotage pour la vidéo surveillance, mais également, certains modèles offrent la possibilité de prendre en charge vos sauvegardes, d’héberger des machines virtuelles ou de supporter l’exécution de conteneurs. L’article traitant des conteneurs, inutile de faire ici une liste exhaustive.

Car oui, j’ai des conteneurs qui tournent sur des VMs sur Hyper-V, mais également quelques conteneurs sur Raspberry Pi et encore d’autres sur le DSM Synology.

Sur ce Blog, plusieurs articles traitent de SQL Server dans un conteneur. Aucune raison donc de ne pas utiliser également le NAS, et cela fonctionne parfaitement.

Je vous laisse ajouter le module Docker à votre DSM.

Ensuite, si ce n’est déjà fait, c’est d’activer, au moins temporairement, l’accès SSH car il n’est pas possible d’ajouter la registry Microsoft à la configuration de Docker sur le NAS.

Tout comme on téléchargerait l’image sur un système Docker plus « traditionnel », on va se positionner en root et faire un Docker Pull.

sudo -i
docker pull mcr.microsoft.com/mssql/server:2019-latest

Petite vérification dans les images, mais en version graphique cette fois-ci :

Il ne reste plus qu’à créer le conteneur en cliquant sur bouton Launch et donner les paramètre d’environnement comme on le ferait en ligne de commande.

Et ensuite valider la création du conteneur. Le conteneur est créé et en cours d’exécution.

La connexion vers l’instance SQL est à présent possible

Happy SQL Server on Docker on Synology !

Publié dans Non classé | 3 commentaires

Backup to URL – Gestion de la rétention

La sauvegarde de vos bases de données est un point crucial de votre stratégie de PRA.

L’actualité récente a montré combien il était important de suivre la règle des 3-2-1 en matière de backups. 3 copies de vos données à minima dont les données de production et au moins 2 backups si possible stockés sur des média distincts (ou des baies distinctes) et au moins une sauvegarde externalisée. Car en cas de sinistre majeur sur votre datacenter principal, il est tout à fait plausible que vos sauvegardes soient également impactées. D’où l’utilité de la sauvegarde externalisée.

Autre point non négligeable à prendre en compte : les fameux cryptolockers ou ransomware qui fleurissent en ce moment. Comment se protéger contre ce risque, car un simple backup sur une baie accessible via un partage réseau laisse planer le risque… Et ce n’est pas la recopie à postériori de vos backups sur un second stockage qui vous solutionnera le problème car, potentiellement, dès le backup terminé, le handle sur le fichier est levé et donc le cryptolocker peut agir. Le fichier que vous recopierez sera alors déjà corrompu.

Compliqué donc de se sortir de cette situation a moins d’aborder la notion de stockage immuable pour vos backups. La version 11 de VEEAM de mémoire propose ce genre de choses, mais cela veut dire utiliser un outil tiers pour gérer ses sauvegardes, et j’avoue ne pas y être favorable. Avec près de 25 ans de SQL au compteur (cela remonte à 1997 pour être exact), bien des éditeurs m’ont fait les yeux doux en essayant de m’expliquer (ou en criant très fort) que c’était vraiment beaucoup mieux, mais non, désolé. Une fois votre solution en place, en tant que DBA je ne suis plus libre de mettre en place mes Log Shipping, relancer des backups pour mettre en place des groupes de disponibilité (avant l’apparition de l’auto-seeding), sans compter avec les gros Fail rencontrés sur des scénarios de restaure moins conventionnels et les schedulers fainéants qui rechignent à planifier du backup log fréquemment au risque de faire exploser des journaux de transactions… Bref très peu pour moi. Seule la partie VDI me semble viable, mais je perds beaucoup de maitrise.

Comment donc solutionner ce problème. J’avoue que la solution Azure, au travers des blobs offre l’avantage d’un accès via une URL HTTPS, avec un jeton d’accès à durée limité (je parle de block blob et pas de page blob), et un credential positionné dans SQL Server. Pas de partage réseau, donc beaucoup moins de risque !

Je ne vais pas aborder en détail ici la partie mise en place, les best practices, et les différences entre page et block blogs, ce n’est pas le propos.

Le but de cet article est d’aborder la rétention des fichiers. Comment supprimer les « vieux » backups, ceux qui ont dépassé la durée de rétention.

Les conteneurs, partie intégrante des comptes de stockage, proposent plusieurs solutions, donc certaines extrêmement simples à mettre en place.

Le plus simple consisterait à utiliser la notion de Life Cycle Management. Qui de plus naturel que de spécifier une durée de rétention et ensuite de laisser Azure supprimer automatiquement les fichiers …

Tout comme l’utilisation d’un Runbook, planifié tous les jours par exemple qui purgerait les fichiers antérieurs à notre rétention.

Mais ces deux solutions sont imparfaites à mon sens, car elles ne satisfont pas à la règle des 3-2-1. Imaginons par exemple que des sauvegardes échouent pour une raisons quelconque et que personne n’y prête attention (ne rigolez pas j’ai vu des jobs en erreur durant plus d’un an …). Votre conteneur serait alors totalement vide, puis que l’exécution de la purge n’est pas liée à l’arrivée d’un nouveau fichier.

On peut conserver le runbook pour le côté exécution de la tâche, mais pour le déclanchement, je vous propose d’utiliser la notion d’évènement : dès qu’un fichier de backup est écrit dans le conteneur, alors, une fois la sauvegarde terminée, on peut purger …

Exemple : un backup database (.bak) est déposé, on peut purger le .bak de cette base de données datant de plus de x jours. Idem pour les backups log et backup différentiels.

C’est donc ce scénario que je vous propose d’explorer dans cet article, dont les grandes étapes vont être :
– Création du compte de stockage et du conteneur
– Création du Runbook à base de PowerShell
– Configuration d’un WebHook pour lancer l’exécution du runbook.
– Création d’un Eventgrid topic
– Abonnement à l’évènement de création de fichier et appel au WebHook

Il existe un tout petit préalable pour faire fonctionner la solution : enregistrer EventGrid dans votre abonnement. Cette ressource est à la base de « tout » Azure, mais pour pouvoir l’utiliser, vous devez l’ajouter à votre abonnement. A faire une et une seule fois …

Rendez-vous dans votre abonnement, sélectionnez l’item Resource Provider, faites la recherche EventGrid et ensuite il n’y a plus qu’à cliquer sur Register.

L’opération n’est pas instantanée, mais cela ne nous empêche pas de continuer avec la création d’un storage account.

Le propos n’est pas ici de discuter des différences entre un tiers Chaud ou un tiers Froid, vu la différence de cout au TB et à la latence induite par le tiers froid en matière de restaure, tout comme les pénalités en cas de suppression anticipée, j’ai ici opté pour un tiers chaud, en mode RA-GRS.

Si vous avez suivi le propos en introduction, utiliser une réplication LRS (locally Redundant Storage) ne fait pas sens pour du backup de données critiques pour l’entreprise. Eventuellement, opter pour une réplication ZRS (Zone Redundant Storage) pourrait s’entendre, mais vu la différence ce prix, autant opter pour une réplication GRS (Geo-Redundant Storage), qui si vous travaillez un peu avec Azure SQL Databases ou les Managed Instances, constituent l’option par défaut inclue dans le prix. Ne doutons pas un seul instant qu’il s’agisse de la solution la mieux adaptée à notre cas de figure. La redondance GZRS ne nous apporte rien, mais par contre l’option RA (Read-Access) parait couler de source. Optons donc pour RA-GRS …

La création du compte de stockage ne pose pas de problème particulier. Ce qui me dérange le plus c’est de ne pas pouvoir appliquer ma convention de nommage comme je peux le faire sur les autres ressources Azure. 😦

Les options des différents onglets de l’assistant sont laissées telles quelles. Une fois le compte de stockage créé, cela peut prendre une minute ou deux, il suffit de créer un conteneur afin de supporter nos fichiers. Notez qu’il est possible de créer plusieurs containers, pour ma part j’ai souvent « sql-backup », « sql-backpac », « sql-audit », « sql-xevent » et « sql-bulk ». Cela couvre une grande majorité de mes besoins. Le nom que vous allez donner à cotre conteneur va recouvrir une certaine importance dans le filtrage des évènements, car il n’est pas question de purger des fichiers d’audit lorsque nous en créons un nouveau. Ou même de supprimer un fichier xEvent lorsqu’un backup est créé.

Ensuite, la création d’un RunBook nécessite la présence d’un compte d’automatisation, dont la création ne requiert pas d’attention particulière.

Vous noterez au passage que 3 Runbooks ont été créé par défaut, ce sont des exemples que vous pouvez bien entendu supprimer.

Il suffit ensuite d’aller sur le compte d’automation et de créer un RunBook

Afin de rendre le système relativement générique, j’ai opté pour 3 variables, chacune relative à la rétention de chaque type de sauvegarde : Complète, Différentielle et Journaux de transaction.

Une fois fait, nous pouvons passer à la partie création du Runbook de type Powershell.

Et d’ajouter du code afin d’effectuer les travaux de purge de fichiers.

Procédez ensuite à la sauvegarde et à la publication du code, ce qui le rendra exploitable.

Mais revenons quelques instants sur ce bout de code pour éviter un bête copier/coller sur votre environnement.

En premier lieu, nous trouvons un paramètre de type Object, sérialisé en JSON et qui va décrire l’évènement (n’oubliez pas que ce RunBook sera appelé via un EventGrid et un Topic d’ajout de nouveau fichier sur le Container).

Voilà à quoi pourrait ressembler le document JSON :

{
  "WebhookName": "webhook-purge-old-backup-files",
  "RequestBody": "[{\"topic\":\"/subscriptions/xxxxxx/resourceGroups/rg-conseilit-francecentral-001/providers/Microsoft.Storage/storageAccounts/stconseilit001\",\"subject\":\"/blobServices/default/containers/sql-backup/blobs/SQL2019/master/FULL/SQL2019_master_FULL_20210416_082843.bak\",\"eventType\":\"Microsoft.Storage.BlobCreated\",\"id\":\"75ed5bed-001e-004f-7689-32ff72063bfa\",\"data\":{\"api\":\"PutBlockList\",\"requestId\":\"75ed5bed-001e-004f-7689-32ff72000000\",\"eTag\":\"0x8D900A0EBC3D5E4\",\"contentType\":\"application/octet-stream\",\"contentLength\":1179648,\"blobType\":\"BlockBlob\",\"url\":\"https://stconseilit001.blob.core.windows.net/sql-backup/SQL2019/master/FULL/SQL2019_master_FULL_20210416_082843.bak\",\"sequencer\":\"0000000000000000000000000000247900000000000ba5ec\",\"storageDiagnostics\":{\"batchId\":\"9c08ea16-3006-0054-0089-32c171000000\"}},\"dataVersion\":\"\",\"metadataVersion\":\"1\",\"eventTime\":\"2021-04-16T06:28:59.9893243Z\"}]",
  "RequestHeader": {
    "Connection": "close",
    "Accept-Encoding": "gzip",
    "Host": "xxxxxxxxxxxxxxxxxxxxxxxxxx.ab.webhook.fc.azure-automation.net",
    "aeg-subscription-name": "SUBSCRIPTION-PURGE-DELETE-BACKUP-FILES",
    "aeg-delivery-count": "0",
    "aeg-data-version": "",
    "aeg-metadata-version": "1",
    "aeg-event-type": "Notification",
    "x-ms-request-id": "d921eef1-3d32-45fc-8953-659374135ffb"
  }
}

La partie la plus intéressante transmise est contenue dans la balise URL :

« url\ »:\ »https://stconseilit001.blob.core.windows.net/sql-backup/SQL2019/master/FULL/SQL2019_master_FULL_20210416_082843.bak\ »

L’idée consiste alors à analyser cette URL qui caractérise totalement le travail que nous devrons réaliser dans le code PowerShell pour purger les backups de la base Instance et base. Tout d’abord, sachez que les backups sont réalisés au travers de la solution de maintenance écrite par Ola Hallengren et dont j’ai déjà eu l’occasion de présenter que ce soit dans ce blog ou au travers de mes vidéos consacrées aux dbaTools. Et cela va nous faciliter la tâche car une structure de dossiers et sous dossiers va automatiquement être créée dans le container.

L’URL contient donc les informations du Container, de l’instance SQL, de la base et du type de backup. Il ne reste alors qu’à extraire ces informations.

$url = "https://stconseilit001.blob.core.windows.net/sql-backup/SQL2019/master/FULL/SQL2019_master_FULL_20210416_082843.bak\"
$storageContainerName =  (($url).split("/")[3])
$storageAccountName =  (($url).split("/")[2]).split(".")[0]
$storagePrefixName = ($url).split("/")[4..(($url).split("/").Count-2)] -join("/")
$backupType = ($url).split("/")[($url).split("/").Count-2]
write-output "Account      : $storageAccountName"
write-output "Container    : $storageContainerName"
write-output "Prefix       : $storagePrefixName"
write-output "Backup type  : $backupType"

La notion de préfixe revêt toute son importance car il s’agit tout simplement du « répertoire » dans lequel nous devons effectuer la purge de fichier. Dans notre cas, une sauvegarde FULL sur la base Master de l’instance SQL2019.

Maintenant que nous avons accès au type de sauvegarde, nous recherchons dans les variables créées précédemment sur le compte d’automatisation quelle est la durée de rétention qui s’applique et calculons la date d’ancienneté maximale :

    switch ($backupType)
    {
        "FULL" { $backupRetention = $(Get-AutomationVariable -Name 'fullBackupRentionInDays') }
        "DIFF" { $backupRetention = $(Get-AutomationVariable -Name 'diffBackupRentionInDays') }
        "LOG" { $backupRetention = $(Get-AutomationVariable -Name 'logBackupRentionInDays') }
    }

    $CleanupTime = [DateTime]::UtcNow.AddHours(- $backupRetention)  

Enfin, nous récupérons le contexte de stockage, recherchons tous les fichiers antérieurs à la date d’ancienneté maximale dans le « répertoire » en question avant d’afficher les fichiers correspondant au critère et de les supprimer (en fonction du booléen).

    $context = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey "xxxxxxxxxxxxxxxxxxxxxxxxxx"

    $blogsToDelete = Get-AzureStorageBlob -Container $storageContainerName -prefix $storagePrefixName -Context $context | Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime }
    $blogsToDelete | Select-Object Name,LastModified | sort-object LastModified -descending | Format-Table -autosize

    if ($performDelete){
        $blogsToDelete | Remove-AzureStorageBlob           
    }

Ce qui nous donne un script complet :

param
(
    [Parameter (Mandatory = $false)]
    [Object] $WebHookData
)

$performDelete = $false

if ($WebHookData) {
    $WebHookData = ConvertFrom-Json $WebHookData
    $body = (ConvertFrom-Json -InputObject $WebHookData.RequestBody)
    
    $storageContainerName =  (($body.data.url).split("/")[3])
    $storageAccountName =  (($body.data.url).split("/")[2]).split(".")[0]
    $storagePrefixName = ($body.data.url).split("/")[4..(($body.data.url).split("/").Count-2)] -join("/")
    $backupType = ($body.data.url).split("/")[($body.data.url).split("/").Count-2]

    switch ($backupType)
    {
        "FULL" { $backupRetention = $(Get-AutomationVariable -Name 'fullBackupRentionInDays') }
        "DIFF" { $backupRetention = $(Get-AutomationVariable -Name 'diffBackupRentionInDays') }
        "LOG" { $backupRetention = $(Get-AutomationVariable -Name 'logBackupRentionInDays') }
    }

    $CleanupTime = [DateTime]::UtcNow.AddHours(- $backupRetention)  
    
    write-output "Event time   : $($body.eventTime)"
    write-output "Subject      : $($body.subject)"
    write-output "Blob URL     : $($body.data.url)"
    write-output "Account      : $storageAccountName"
    write-output "Container    : $storageContainerName"
    write-output "Prefix       : $storagePrefixName"
    write-output "Backup type  : $backupType"
    write-output "Retenion     : $backupRetention"
    write-output "Delete prior : $CleanupTime "

    $context = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey "xxxxxxxxxxxxxxxxxx"

    $blogsToDelete = Get-AzureStorageBlob -Container $storageContainerName -prefix $storagePrefixName -Context $context | Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime }
    $blogsToDelete | Select-Object Name,LastModified | sort-object LastModified -descending | Format-Table -autosize

    if ($performDelete){
        $blogsToDelete | Remove-AzureStorageBlob           
    }

}

Tout est donc prêt à présent pour l’exécution de la purge, il reste à se préoccuper du déclanchement des opérations.

Pour ce faire nous allons créer un WebHook sur le RunBook. Pensez à copier l’URL du WebHook car pour des raisons de sécurité, pour ne pourrez plus y accéder une fois créée. Il s’agit là de sa seule protection …

Attention à la durée limite d’utilisation également !!! Allez ensuite sur l’onglet paramètres, il n’est pas nécessaire de mettre une valeur pour le paramètre WebHookData mais c’est obligatoire de valider (par le OK) cette étape dans le process de création du WebHook.

Okay, nous sommes proches de la fin … Il faut à présent créer un Topic système et créer un abonnement

Pensez à filtrer les évènements pour ne conserver que les « Blobs Created » et configurez le WebHook avec l’URL préciseument mise de côté lors d’une précédente étape.

Comme dit précédement, l’idée n’est pas d’activer le runbook lors de la création d’audit, ou bien de xEvents, c’est pour cela que nous allons filtrer en fonction du sujet avec le conteneur sql-backup.

Et afin de ne pas déclancher d’évènement multiples lors de la création d’un backup, nous allons mettre un filtre avancé avec « data.api » contains « PutBlockList ». En fait cette API est appelée en fin de sauvegarde une fois tous les Blocks ( API « PutBlock ») écrits. N’hésitez pa sà faire appel à votre moteur de recherche favori pour de plus amples détails sur le mode de fonctionnement.

Il ne reste à présent plus qu’à tester la solution …

Côté SQL Server, nous créons le credential et démarrons une sauvegarde complète des bases système.

CREATE CREDENTIAL [https://stconseilit001.blob.core.windows.net/sql-backup] 
WITH IDENTITY='Shared Access Signature', 
SECRET='sp=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
GO

EXECUTE [_DBA].[dbo].[DatabaseBackup]
	@Databases = 'SYSTEM_DATABASES',
	@URL = 'https://stconseilit001.blob.core.windows.net/sql-backup',
	@BlockSize=65536,
	@MaxTransferSize=4194304,
	@Compress='Y',
	@BackupType = 'FULL',
	@CheckSum = 'Y',
	@LogToTable = 'Y'

Après quelques secondes, nous pouvons constater que des répertoires sont apparus dans le container, avec un sous répertoire « FULL » contenant la sauvegarde de chacune des bases.

Si l’on bascule sur le RunBook, nous constatons que des exécutions ont été faites, correspondant à chaque fichier de backup déposé.

Il est possible de sélectionner une exécution et de visualiser la zone Output, qui contient les données issues des lignes write-output du code PowerShell.

Lorsque vous aurez dépassé la durée de rétention configurée dans vos variables, la liste des fichiers devant être supprimée s’affichera alors.

Des statistiques d’exécution sont disponibles à la fois au niveau de l’event subscriptions sur le compte de stockage, tout comme sur le compte d’automatisation.

Cette solution m’a paru être relativemetn simple à mettre en place, tout en offrant des garanties sur la rétention des fichiers, même en cas de problème au niveau des backups (pas de backup -> pas de déclanchement de la purge pour ce type de sauveagrde et pour cette base).

En fonction des cas, un simple Life Cycle Management pourrait suffire, par exemple au-delà de 30 jours déplacement des fichiers vers du stockage froid, puis au-delà de 180 jours suppression sur le stockage froid …. A vous de voir en fonction de votre politique de rétention et de votre PRA.

Happy Backups on Azure !

Publié dans Azure, Azure Runbook, PowerShell, SQL Server | Laisser un commentaire

Conseil du jour : après le PRA, le DBA !

L’actualité récente a rappelé à certaines sociétés, institutions ou associations, que le « cloud » n’est rien d’autre que des serveurs hébergés dans un « autre » datacenter. Datacenter qui est sujet à des pannes voire des incendies.

Petite note pour ceux qui voudraient critiquer OVH rapport à l’incendie survenu dans le datacenter à Strasbourg : AWS a subit un désastre de ce type en Aout 2018 au Japon. Et il doit y avoir d’autres exemples que je n’ai plus en tête.

Le métier de DBA évolue du fait du déplacement des ressources vers le « cloud », mais ne devient pas moins intéressant (il y a un certain désamour pour la profession) ou crucial (oui, la data c’est le pétrole de demain et les applications passent mais les données restent).

L’installation et la configuration système et SQL Server a bien évolué. Mais la problématique reste la même, au lieu de choisir du matériel, nous devons composer avec les différentes offres PaaS et IaaS, jongler également avec les coûts …

Le capacity planning est plus simple en revanche, car opter pour un tiers inférieur, supérieur, voire du serverless offre une latitude et une simplicité impensable lors mes débuts dans ce métier au siècle dernier.

Le backup / restore est facilité par l’adoption du cloud (stockage quasi illimité voire service managé), certes, mais les notions de RPO et RTO sont toujours d’actualité, alors pensez à tester votre restore pour mesurer votre temps maximal d’disponibilité.

Le design d’une base de données, l’optimisation, l’indexation sont d’autant plus important car non seulement cela aura un impact sur la performance de l’application et la satisfaction des utilisateurs, mais en outre, cela aura également un impact direct sur le coût du hosting.

Parmi les tâches du DBA, on retrouve également la disponibilité des bases de données, et donc la redondance de zone, redondance géographique, mais également, les problématiques liées à la sécurité ainsi qu’à la régulation (RGPD …).

L’actualité a fait une piqure de rappel cinglante : la nécessité de disposer d’un PRA et de le tester.

Mon conseil à présent : pensez également à vous munir d’un DBA ! Oui, je prêche pour ma paroisse, mais reportez vous à mon article prémonitoire sur la disponibilité des données … Mieux vaut s’en préoccuper avant !

Un DBA en temps partagé peut constituer la solution idéale pour un grand nombre de sociétés (PME, PMI voire grande entreprise).

Vous savez où me trouver !

Publié dans Non classé | Laisser un commentaire

Actu – retour sur la redondance des services PaaS et SaaS

Il y a quelques temps, je publiais un article sur la nécessite de se préoccuper de la haute disponibilité de SQL Server sur un cloud public tel qu’Azure. Et plus largement, la question de la redondance des services lorsque l‘on utilise des services managés.

Globalement, la réponse était Oui, il faut s’en préoccuper.

Malheureusement, l’actualité m’aura donné raison, trop tôt probablement pour certains qui auraient songé à modifier leur infrastructure.

Je ne vais pas tirer sur l’ambulance, j’apprécie OVH pour certains de leurs services que j’ai utilisé par le passé, que j’utilise encore pour certains, mais il manque encore une brique à l’édifice.

Aucun hébergeur n’est prémuni contre tous les risques, j’ai déjà écrit cette phrase dans l’article précédent. Il est donc nécessaire de prévoir son propre PRA / DRP au cas où les service fournis par le Cloud Provider ne vous paraissent pas suffisamment protégés.

Pour reprendre le tweet du fondateur d OVH, il était temps d’activer le DRP … Le votre …

Bien m’sieur, compris.

Mais encore faut-il pouvoir …

La console de gestion n’est toujours pas disponible à l’heure où cet article est écrit, soit plus de 8 heures après le début du sinistre. Bref, l’histoire n’est pas complète si les services basculent mais le switch d’un IP Failover ne permet pas d’atteindre ses services.

Pour la petite histoire, d’un point de vue SQL, la bascule s’est bien passée pour mon client, mais il a fallu en urgence revoir les chaines de connexion car l’IP failover était défaillant.

La redondance des services est une chose, la redondance des outils de gestion en est une autre.

Besoin d’audit ou de conseil ? Vous savez où me trouver …

Publié dans Non classé | Laisser un commentaire

SQL Azure – Doit-on se préoccuper de la haute disponibilité ?

Si vous suivez ce blog, il ne vous aura pas échappé qu’une partie conséquente de mon activité de consultant indépendant est liée à la haute disponibilité de SQL Server. Que ce soit sur des machines physiques ou virtuelles, dans vos datacenters ou bien externalisé, voire hébergées sur un cloud public, la disponibilité de votre instance, de votre base ou bien d’une table revêt toute son importance … quand vous ne pouvez pas y accéder.

C’est un exercice très compliqué à réaliser, mais il est fondamental de quantifier l’indisponibilité de votre instance ou de votre base. Beaucoup de questions à se poser, j’utilise cette diapositive lors de mes formations et interventions, pour obtenir un maximum d’information. Une grande partie des réponses est issue du Business, côté technique on doit s’adapter.

Une fois l’indisponibilité quantifiée, en $ ou €, vous êtes en mesure de décider s’il est rentable / important de mettre en HA votre base de données, car la HA a forcément un coût, ainsi qu’un impact potentiel sur la performance de l’application.

OnPrem, ou bien en IaaS sur un cloud public, vous avez donc le choix entre du Cluster de Basculement ou bien des Groupes de Disponibilité. Je parle de HA, donc exit le Log Shipping qui est une solution de Disaster Recovery. En fonction de la granularité de protection, de la nécessité de disposer d’un RPO nul, d’un RTO très faible, de modèle de récupération simple, vous allez choisir entre du FCI ou AGs.

Sur le un Cloud Public tel qu’Azure, le problème se corse, car le niveau de service (SLA) promis est déjà élevé, généralement au moins de 99.9% voire 99.995%.

Ce qui, reporté au fameux tableau des « Nine », nous donne une indisponibilité annuelle comprise entre 52 minutes et 26 minutes pour un niveau de service General Purpose / Standard / Basique …. Est-ce suffisant ? Probablement dans une très grande majorité de cas.

Il faut bien faire la différence entre disponibilité et Uptime. L’OS et SQL Server peuvent être Up and Running, mais la disponibilité comprend également la capacité d’accéder à une donnée dans un temps imparti : par exemple, tel select doit se faire en x millisecondes ou secondes. On entrevoit donc l’impact sur la disponibilité d’un plan de maintenance qui reconstruit des index cluster sur une édition standard : le rebuild est offline ! Le tableau nous parles certes de disponibilité, mais globalement cela couvre Hardware+Network+OS+Service SQL.

On vous garanti l’accès à la donnée, ce que vous en faites est une autre histoire. Si vous avez des locks ou des performances qui ne sont pas au niveau attendu, cela n’est pas couvert par le SLA.

Mais ….

Que se passe t’il si votre base devient inaccessible durant …. 11 heures ? Les provider cloud ne sont pas a l’abris de tout problème, ils essaient seulement de prendre en considération plus de risque que vous dans votre Datacenter et s’en protègent du mieux qu’ils peuvent. Cependant, l’imprévu est toujours possible.

Imaginez que, le Lundi 14 septembre 2020, à compter de 13h30, votre base soit injoignable, et ce jusqu’à 00h41 le 15/09 … C’est ce qu’il s’et passé, sur la région UK South … Et ce n’est pas une seule base qui a été impacté, mais la connectivité globale de la région ! 11 heures d’indisponibilité, malgré la promesse d’un SLA de 99,99 %.

Microsoft n’est pas le seul cloud provider à avoir expérimenté de tels problèmes…

Il est donc important de ne pas se fier totalement au SLA annoncé et conserver les réflexes de haute disponibilité… D’un point de vue Azure, cela se traduit par des services redondants à l’intérieur d’une région (Availability Zones) voire inter régions (Regions pairs : France Central + France Sud par exemple).

Traduit pour SQL Server en mode PaaS, cela se présente sous forme de Géo-Réplication, Failover Groups et redondance de zone, ce dernier point étant encore en preview. Je ne traite pas le cas de la Managed Instance, mais sachez que la redondance de zone et les groupes de basculement automatiques sont disponibles.

Chacune de ces fonctionnalités a ses avantages et inconvénients. Le slide suivant, que j’utilise lors des formations, permet de visualiser rapidement les principales différences entre la géo-réplication et les groupes de basculement automatiques.

Je ne vous cache pas que le fait de disposer d’un failover automatique et d’une chaine de connexion inchangée pour un ensemble de bases me fait préférer le failover group. Mais il reste limité à 1 seul réplica secondaire, qui est cependant accessible en lecture seule pour offloader le reporting par exemple. Vous aurez reconnu un groupe de disponiblité, et, effectivement, les failover groups en sont dérivés.

Comme dit précédemment, la HA peut avoir un coût. La géo réplication tout comme les failover groups nécessitent la création d’une base ou d’un pool élastique. Il y a un coût additionnel.

Si vous n’avez pas besoin d’un réplica en lecture seule mais que vous souhaitez disposer d’une solution de continuité d’activité en niveau de service General Purpose, alors la redondance de zone est faite pour vous.

Sachant qu’en niveau General Purpose, le compute et le storage sont séparés, il est alors simple de déposer les fichiers MDF/LDF sur un stockage géographiquement redondant : des disques Azure Premium Storage ZRS (Zone Redundant). Ainsi en cas de problème sur une zone, une bascule automatique de zone sera effectuée. Si votre application suit les mêmes règles de disponibilité, par exemple vos VMs dans 2 ou 3 availability zones, la continuité de service est assurée.

Je ne l’ai pas encore mentionné mais la redondance de zone est également disponible sur les pools élastiques.

La capture montre le SLO General Purpose, mais cette option est bien entendu également disponible en Business Critical, bien qu’implémentée techniquement de manière différente car le BC utilise un stockage local SSD.

L’incident UK South donné en exemple ne concernait qu’une seule zone, en utilisant ce principe, notre base de données n’aurait subi qu’une légère perte de service, le temps de basculer les ressources vers la seconde zone.

Conclusion : oui, il faut encore se préoccuper de la haute disponibilité, même lorsque l’on est hébergé sur un cloud public.

Happy cloud HA !

Publié dans Azure, SQL Azure | Tagué | 4 commentaires

dbaTools – Restauration d’une base de données

Tout DBA se doit de disposer d’une boite à outil prête à l’emploi pour répondre le plus rapidement à une série de problèmes susceptibles de se produire.

Combien de fois vous a-t-on demandé de restaurer une base de données ? Rapidement, très rapidement, car la prod est plantée. Ou bien restaurer la base de production vers l’environnement d’intégration. Ou encore restaurer une copie de base de données à un point précis dans le temps, histoire de vérifier les données à ce moment (alors que la fonctionnalité Temporal Table est disponible – je dis ça je ne dis rien …). Ce n’est pas à ce moment-là qu’il faut se poser des questions sur comment faire. Utiliser ou non l’assistant fourni dans SSMS …

Pour rappel, l’assistant SSMS vous permet de faire un restaure Database de manière très simple. Deux sources possibles : l’historique des sauvegardes et un device, un backup en fait.

J’avoue que je ne suis pas fan de cette option. Et pour plusieurs raisons. La première : l’assistant utilisé avec l’historique des sauvegardes est inopérant lorsque votre base est mise en HA dans un groupe de disponibilité. Les backups peuvent s’effectuer sur le réplica primaire, tout comme n’importe lequel des secondaires (je ne vais pas rentrer dans le détail de FULL COPY_ONLY et des DIFF). Mais la base MDSB n’st pas (encore) « répliquée » sur tous els réplicas d’un AG. Donc l’assistant devient inutilisable.

Tout comme il est inutilisable si vous avez « oublié » de purger les historiques et que SSMS doit scanner la table Backupset sur 10 ans d’historiques ….

Pour contrer ce genre de problèmes, on peut utiliser la notion de Device en tant que source, autrement dit des fichiers de sauvegarde. Vous pouvez ainsi sélectionner 1 ou N fichiers, voire tous les fichiers d’un répertoire, et SSMS va effectuer des RESTORE HEADERONLY et RESTORE FILELISTONLY pour reconstituer la bonne séquence de restauration. Il m’est fréquemment arrivé de crasher SSMS lorsque des centaines de fichiers sont concernés (imaginez juste un backup toutes les minutes, sur 1 semaine …).

Donc encore une fois, le jour où cela se produit et que vous n’avez pas de plan B, vous risquez de suer à grosses gouttes …

C’est pour cela que depuis des années j’avais construit des scripts PowerShell de restaure de base, avec toutes les options imaginables pour me simplifier la vie.

Et puis dbaTools est arrivé, en proposant une commande restore-dbaDatabase … J’ai donc jeté une partie de mes scripts pour basculer sur ce cmdlet.

Restaurer plusieurs bases de données sur une même instance, par exemple depuis le serveur de production vers le serveur d’intégration peut être extrêmement chronophage, surtout si vous avez des dizaines, voire des centaines de bases à traiter. La commande ci-dessous vous permet de restaurer toutes les bases contenues dans les backups présents dans un répertoire et de déplacer les fichiers de DATA et LOG au moment du restore, l’option MOVE TO, pour parer à des éventuelles différences de structure disque entre les serveurs source et destination.

# List of backup files
$backupPath = "\\Rebond\backup\MultipleDatabases"
Get-ChildItem -Path $backupPath 

# Restore multiple databases at once - the quickest way
Restore-DbaDatabase -SqlInstance SQL15AG1 `
                    -Path $backupPath `
                    -UseDestinationDefaultDirectories
 
# list databases
get-dbaDatabase -SqlInstance SQL15AG1 -ExcludeSystem | format-table -autosize

Parfois on est amené à effectuer une restauration d’une base vers plusieurs instances. Le script suivant permet à la fois de déplacer les fichiers sur des emplacement spécifiques, mais aussi d’obtenir les scripts de restauration, avant de l’exécuter sur différentes instances.

# Restore single databases on different location on multiple instances
$backupPath = "\\Rebond\backup\AdventureWorks"
Get-ChildItem -Path $backupPath 

$tSQL = Get-ChildItem -Path $backupPath `
            | Select-Object -First 1 `
            | Restore-DbaDatabase -SqlInstance SQL15AG1 `
                                  -DestinationDataDirectory G:\MSSQL15.MSSQLSERVER\MSSQL\DATA `
                                  -DestinationLogDirectory H:\MSSQL15.MSSQLSERVER\Log `
                                  -WithReplace `
                                  -NoRecovery `
                                  -OutputScriptOnly
Write-Host $tSQL
 
Invoke-DbaQuery -SqlInstance SQL15AG1,SQL15AG2 `
                -Query $tSQL


# list databases
get-dbaDatabase -SqlInstance SQL15AG1,SQL15AG2 -ExcludeSystem | format-table -autosize

Et enfin un dernier exemple qui permet de restaurer une copie d’une base de données à un point précis dans le temps, en ajoutant un préfixe au nom de la base ainsi qu’aux fichiers. On présume également que la structure des dossiers de sauvegarde suit la solution de maintenance de Ola Hallengren.

# Restore a copy of a database at a specific point in time
$BackupPath = "\\Rebond\backup\AdventureworksLT"
Get-ChildItem -Path $backupPath -Recurse -Directory # looks like OH folder structure 🙂
Get-ChildItem -Path $backupPath -Recurse

$RestoreTime = Get-Date('16:20 20/02/2021')
$RestoredDatabaseNamePrefix = $RestoreTime.ToString("yyyyMMdd_HHmmss_") 


$Result = Restore-DbaDatabase -SqlInstance SQL15AG1 `
                    -Path $BackupPath `
                    -UseDestinationDefaultDirectories `
                    -MaintenanceSolutionBackup `
                    -RestoredDatabaseNamePrefix $RestoredDatabaseNamePrefix `
                    -DestinationFilePrefix $RestoredDatabaseNamePrefix `
                    -RestoreTime $RestoreTime `
                    -WithReplace 
 
 
$Result | Select-Object Database, BackupFile, FileRestoreTime, DatabaseRestoreTime | Format-Table -AutoSize
 
Write-Host "Time taken to restore $($Result[$Result.count - 1].Database) database : $($Result[$Result.count - 1].DatabaseRestoreTime) ($($Result.count) files)"
 
# list databases
get-dbaDatabase -SqlInstance SQL15AG1 -ExcludeSystem | format-table -autosize

Bref, vous aurez compris qu’au travers de quelques lignes de code extrêmement simples vous pouvez restaurer des bases de données comme vous le souhaitez sans vous soucier du « bon » fonctionnement de SSMS.

Happy dbaTools !

Publié dans Non classé | Tagué | 1 commentaire

Deadlock – Trouver les enregistrements impliquées

Si vous administrez un tant soit peu un serveur SQL, il vous est probablement arrivé de devoir traiter d’une problématique de DeadLock.

pour mémoire, le DeadLock est le stade utile d’un situation de verrouillage où il n’existe pas de solution car chacun des processus impliqués attend que l’autre se termine afin de pouvoir continuer son traitement. L’histoire du serpent qui se mort la queue pour faire simple.

Dans des version « préhistoriques » de SQL Server, on faisait appel à des Trace Flags afin d’enregistrer els informations dans le fichier errorlog, afin de pouvoir comprendre et dépanner ces sutuations.

Avec l’arrivée des xEvents, et principalement de la sessionSystem_health, nul besoin des TF 3605, 1204 et 1205. Par défaut tout est enregistré ….

Voici le bout de code que je vais utiliser afin de provoquer le dead lock sur la base AdventureWorks

-- Session 1
Use AdventureWorks
GO
BEGIN TRAN

	update Production.Product
	set name = 'Session 1 - Product 722'
	where ProductID = 722
-- Session 2
Use AdventureWorks
GO
BEGIN TRAN
	
	update Production.Product
	set name = 'Session 2 - Product 512'
	where ProductID = 512

-- Session 1
	update Production.Product
	set name = 'Session 1 - Product 512'
	where ProductID = 512

A ce stade, rien de particulier, simple situation de blocage, il suffit qu’une des session fasse un COMMIT ou un ROLLBACK, les verrous seront levés et donc la seconde session pourra se terminer normalement. La commande UPDATE de la session 1 est bloquée par l’update de la session 2. Le verrou Exclusif de la session 2, qui a été posé sur l’enregistrement 512 est incompatible avec le verrou exclusif demandé par la session 1 sur ce même enregistrement.

Tout se complique lorsque la session 2 veut accéder à la ressource qui est déjà verrouillée par la session 1, l’enregistrement 722.

-- Session 2
	update Production.Product
	set name = 'Session 2 - Product 722'
	where ProductID = 722

La situation est inextricable, car chacune des sessions attend que l’autre ait terminé pour se terminer.
SQL Server doit prendre une décision et tuer un des processus.

le xEvent System_Health enregistre l’évènement dans le ring buffer et dans un fichier xel afin de résister à un restart de SQL Server.

L’onglet Deadlock nous permet de visualiser la situation :

Si l’on s’attarde avec la souris sur les processus, on peut visualiser les requêtes mises en cause. Dans notre exemple, le deadlock est simple, mais il peut arriver que le nombre de process impliqués soit plus important. La requête nous renseigne sur les ressources lockées, mais si cela se produit avec des index noncluster (ah….les index avec colonne incluse et les index composites …), le troubleshooting est un peu plus complexe.

En creusant un peu, toutes les informations sont présentes afin de découvrir quels sont les enregistrements impliqués. Nous allons donc décoder les informations renvoyées.

Nous allons nous plonger dans la version XML du deadlock.

<deadlock>
 <victim-list>
  <victimProcess id="process22fa607d848" />
 </victim-list>
 <process-list>
  <process id="process22fa607d848" taskpriority="0" logused="2076" 
		   waitresource="KEY: 5:72057594045136896 (4637a194cfd9)" waittime="138859" ownerId="246372" 
		   transactionname="user_transaction" lasttranstarted="2021-02-15T16:40:45.307" XDES="0x22f970fc428" 
		   lockMode="U" schedulerid="6" kpid="7164" status="suspended" spid="76" sbid="0" ecid="0" priority="0" 
		   trancount="2" lastbatchstarted="2021-02-15T16:41:00.320" lastbatchcompleted="2021-02-15T16:41:00.307" 
		   lastattention="1900-01-01T00:00:00.307" clientapp="Microsoft SQL Server Management Studio - Query" 
		   hostname="X1CARBON7TH" hostpid="1620" loginname="X1CARBON7TH\chris" isolationlevel="read committed (2)" 
		   xactid="246372" currentdb="5" currentdbname="AdventureWorks" lockTimeout="4294967295" 
		   clientoption1="671090784" clientoption2="390200">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="2" stmtend="176" sqlhandle="0x020000003c46ff230e8ad1d8bbb3b8dea1a668590c8e06da0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="adhoc" line="1" stmtstart="2" stmtend="176" sqlhandle="0x020000009f4c85181b7afcb14ee5aa3466b9df887ae827bf0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
	update Production.Product
	set name = 'Session 1 - Product 512'
	where ProductID = 512   </inputbuf>
  </process>
  <process id="process22f8d89c4e8" taskpriority="0" logused="6304" 
		   waitresource="KEY: 5:72057594045136896 (b147776edda1)" waittime="4401" ownerId="246434" 
		   transactionname="user_transaction" lasttranstarted="2021-02-15T16:40:53.210" XDES="0x22f8cfac428" 
		   lockMode="U" schedulerid="2" kpid="2892" status="suspended" spid="87" sbid="0" ecid="0" priority="0" 
		   trancount="2" lastbatchstarted="2021-02-15T16:43:14.723" lastbatchcompleted="2021-02-15T16:43:14.710" 
		   lastattention="1900-01-01T00:00:00.710" clientapp="Microsoft SQL Server Management Studio - Query" 
		   hostname="X1CARBON7TH" hostpid="1620" loginname="X1CARBON7TH\chris" isolationlevel="read committed (2)" 
		   xactid="246434" currentdb="5" currentdbname="AdventureWorks" lockTimeout="4294967295" 
		   clientoption1="671090784" clientoption2="390200">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="2" stmtend="176" sqlhandle="0x020000003c46ff230e8ad1d8bbb3b8dea1a668590c8e06da0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="adhoc" line="1" stmtstart="2" stmtend="176" sqlhandle="0x0200000060843c2be9d50655cbffbb6a0c183e87382ea3b20000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
	update Production.Product
	set name = 'Session 2 - Product 722'
	where ProductID = 722   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594045136896" dbid="5" objectname="AdventureWorks.Production.Product" indexname="PK_Product_ProductID" id="lock22f904aae80" mode="X" associatedObjectId="72057594045136896">
   <owner-list>
    <owner id="process22f8d89c4e8" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process22fa607d848" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057594045136896" dbid="5" objectname="AdventureWorks.Production.Product" indexname="PK_Product_ProductID" id="lock22fa0a1ee00" mode="X" associatedObjectId="72057594045136896">
   <owner-list>
    <owner id="process22fa607d848" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process22f8d89c4e8" mode="U" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

Nous allons nous intéresser à la propriété WaitRessource qui détient la clé (sans jeu de mot !) du problème.

La base de données N°5 correspond effectivement au numéro de la base AdventureWorks.

L’information suivante est un HOBT (Heap or BTree). Et l’on retrouve notre table Production.Product.

SELECT  c.name as schema_name, 
		o.name as object_name, 
		i.name as index_name,
		p.object_id,p.index_id,p.partition_id,p.hobt_id
FROM sys.partitions AS p
INNER JOIN sys.objects as o on p.object_id=o.object_id
INNER JOIN sys.indexes as i on p.index_id=i.index_id and p.object_id=i.object_id
INNER JOIN sys.schemas AS c on o.schema_id=c.schema_id
WHERE p.hobt_id = 72057594045136896;

Bon, OK, trouver la table n’était pas vraiment compliqué.

On va faire appel à une commande magique afin de trouver les enregistrements impliqués dans le Deadlock. Il suffit de recopier la valeur entre parenthèses de la propriété WaitRessource que nous allons appeler KeyHashValue.

Grâce à la fonction non documentée %%lockres%%, il devient simple de retrouver les enregistrements concernés.

On retrouve par ailleurs cette information lorsque l’on visualise les données de la page via DBCC PAGE.

Grâce à une seconde fonction magique, sys.fn_physlocFormatter, nous pouvons retrouver quelle datapage contient l’enregistrement en question. Nous allons nous focaliser sur le productID 512 qui est donc présent dans la page 788 du fichier n°1, à l’emplacement 0.

DBCC TRACEON(3604)
DBCC PAGE('Adventureworks',1,788,3)

Je ne vais pas détailler dans ce post les information renvoyées par DBCC PAGE, la partie Header en particulier. Ensuite, on va trouver les enregistrements, donc l’enregistrement slot 0. Attention, Slot 0 ne signifie pas forcément le premier enregistrement de la datapage, il faut se référer au tableau d’offset en bas de page pour connaitre à quel emplacement retrouver l’enregistrement.

On retrouve la valeur KeyHashValue qui était présente dans le xml_deadlock_report. Nous somme donc bien sur l’enregistrement qui est impliqué dans le verrou. Enregistrement d’un index cluster, les données donc, ou bien d’un index non cluster, cela ne changerait rien.

Il suffit de procéder de même avec le second enregistrement pour terminer la résolution de cet incident.

Pour les adeptes du journal de transactions, il est possible d’y retrouver les informations de verrouillage directement. Il est possible de visualiser le contenu du journal actif avec fn_dblog() ou bien dans une sauvegardes au travers de fn_dump_dblog().

Si l’on repart des transactions, identifiées par les paramètres xactid dans le xml_deadlock_report, il est possible de retrouver les mêmes informations.

SELECT [Operation],[Context,SPID],[Transaction SID],[Xact ID],[Transaction ID],[Page ID],
       [Slot ID],AllocUnitName,[Lock Information],[Begin Time],[End Time] 
FROM sys.fn_dblog(null,null)
WHERE [Transaction ID] in (
    SELECT [Transaction ID] 
    FROM sys.fn_dblog(null,null) 
    WHERE [Xact ID] IN (246372,246434)
)

On retrouve les numéros de page dans lesquelles trouver les enregistrements, notée en hexadécimal, 314 et 318 correspondant respectivement aux valeurs 788 et 789. On retrouve ensuite la propriété KeyHashValue dans la colonne LockInformation.

Au passage, si vous souhaitez savoir qui a passé la commande, la colonne [Transaction SID] correspond au SID que vous trouvez dans server_principal et/ou database_principal.

Au travers de quelques lignes de code il a été relativement simple de déterminer quels étaient les enregistrements impliqués dans un deadlock.

Comment éviter cette situation d’interblocage ? Cette fois, la réponse est loin d’être simple. Peu de possibilités côté DBA : on peut essayer de positionner des index nonclustered afin de permettre à SQL Server d’accéder aux enregistrements de manière différente, mais rien de garanti. On peut faire usage du RCSI ou bien du niveau d’isolation SNAPSHOT, mais cela peut aussi changer le comportement de l’application. Généralement, le problème se situe au niveau du code client, accéder aux tables dans le même ordre, limiter au maximum la durée et les ressources consommées par les transactions, éviter toute interaction utilisateur durant une transaction.

Pensez également à bien clore vos transactions, durant mes audits, je constate malheureusement trop souvent des sessions sleeping durant des heures avec des transactions ouvertes. Là, désolé, je ne peux plus rien faire pour vous. Toutes les actions en base de données doivent être réalisées de la sorte:
– Ouvrir une connexion
– Optionnellement créer une transaction
– Exécuter la requête
– Si une transaction a été ouverte, COMMITER ou ROLLBACKer la transaction
– Fermer la connexion.

Le pool de connexion se chargera de maintenir la connexion ouverte pour être réutilisée. A cela pensez à ajouter une logique de Retry, avec un délai constant ou croissant entre chaque tentative et vous vous rapprochez des bonnes pratiques.

Happy xml_deadlock_report !

Publié dans SQL Server | Tagué , | Laisser un commentaire

dbaTools – Installation de SQL Server

L’utilisation de PowerShell est devenu un standard de l’administration, y compris pour les DBAs.

L’installation de SQL Server peut se faire via l’utilisation d’un assistant graphique, mais également via la ligne de commande setup.exe. Pour mémoire, l’assistant graphique n’est qu’un super éditeur de fichier texte car une fois que vous avez parcouru tous les écrans de l’assistant, un fichier configuration.ini est généré afin de rappeler setup.exe avec le paramètre /configurationfile. Très pratique lorsque l’on souhaite répéter un certain nombre de fois la même installation.

Pour ma part, je travaille principalement avec la ligne de commande, on va dire que c’est historique !

Voici un bout de code que j’utilise régulièrement :


$HostName = Hostname
D:\Setup.exe /ACTION=Install /FEATURES=SQLEngine,Replication,IS,Conn,FullText  `
			/INSTANCENAME=MSSQLSERVER `
			/SQLSVCACCOUNT="NT Service\MSSQLServer" `
			/AGTSVCACCOUNT="NT Service\SQLServerAgent" `
			/FTSVCACCOUNT="NT Service\MSSQLFDLauncher" `
			/ISSVCACCOUNT="NT Service\MsDtsServer150" `
			/AGTSVCSTARTUPTYPE="Automatic" `
			/TCPENABLED="1" `
			/FILESTREAMLEVEL="3" `
			/FILESTREAMSHARENAME="MSSQLSERVER" `
			/INSTALLSQLDATADIR="E:" `
			/SQLBACKUPDIR="E:\MSSQL\Backup" `
			/SQLUSERDBDIR="E:\MSSQL\Data" `
			/SQLUSERDBLOGDIR="E:\MSSQL\Log" `
			/SQLTEMPDBFILECOUNT=4  `
			/UpdateEnabled=FALSE `
			/SECURITYMODE=SQL /SAPWD="Password1!" /SQLSYSADMINACCOUNTS="$Hostname\Administrator" `
			/SQLUSERDBDIR="E:\MSSQLServer\Data" `
			/SQLUSERDBLOGDIR="E:\MSSQLServer\Log" `
			/SQLTEMPDBDIR="E:\MSSQLServer\Data" `
			/SQLTEMPDBLOGDIR="E:\MSSQLServer\Log" `
			/SQLTEMPDBFILESIZE=256 `
			/SQLTEMPDBFILEGROWTH=64 `
			/SQLTEMPDBLOGFILESIZE=256 `
			/SQLTEMPDBLOGFILEGROWTH=256 `
			/HELP="False" /INDICATEPROGRESS="False" /QUIET="True" /QUIETSIMPLE="False" `
			/X86="False" /ENU="True" /ERRORREPORTING="False" /SQMREPORTING="False" `
			/SQLSVCINSTANTFILEINIT=TRUE `
			/IACCEPTSQLSERVERLICENSETERMS 
	

D’un point de vue PowerShell strict, rien de neuf quant à l’installation de SQL Server. Mais la communauté, au travers des modules dbaTools, a créé un cmdlet pour « simplifier » le process d’installation. Lorsque ‘l’on a travaillé des années en ligne de commande, le côté « simple » n’apparait pas immédiatement, je l’avoue.


Mais la commande Install-DbaInstance offre l’avantage de pouvoir effectuer la même installation sur plusieurs serveurs simultanément, sans effort supplémentaire.

En toute honnêteté, je n’ai pas encore opté systématiquement pour cette méthode d’installation, mais lorsque je dois installer plusieurs serveurs de test de manière identique pour des formation de type haute disponibilité, par exemple un cluster Always On Availability Group X nœuds, je fais appel à Install-DbaInstance.


# Source reporsitory configuration
Set-DbatoolsConfig -Name Path.SQLServerSetup -Value '\\rebond\sources'

# my super secure sa password for demos
$Username = 'sa'
$Password = 'Password1!'
$pass = ConvertTo-SecureString -AsPlainText $Password -Force
$saCred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass

# the credential used to install SQL Server on the remote computers
$CurrentUser = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name
$InstallCred = Get-Credential -Message "Enter current user and password to connect remote computer" -UserName $CurrentUser

# some configuration stuffs
$config = @{
    AGTSVCSTARTUPTYPE = "Automatic"
    SQLCOLLATION = "Latin1_General_CI_AS"
    BROWSERSVCSTARTUPTYPE = "Manual"
    FILESTREAMLEVEL = 1
    INSTALLSQLDATADIR="G:" 
    SQLBACKUPDIR="G:\MSSQL15.MSSQLSERVER\Backup" 
    SQLUSERDBDIR="G:\MSSQL15.MSSQLSERVER\MSSQL\Data" 
    SQLUSERDBLOGDIR="H:\MSSQL15.MSSQLSERVER\Log" 
    SQLTEMPDBDIR="T:\MSSQL15.MSSQLSERVER\Data" 
    SQLTEMPDBLOGDIR="U:\MSSQL15.MSSQLSERVER\Log" 
}

# Perform the installation
Install-DbaInstance -SqlInstance SQL15AG1,SQL15AG2 `
                    -Credential $InstallCred `
                    -Version 2019 `
                    -Feature Engine,Replication,FullText,IntegrationServices `
                    -AuthenticationMode Mixed `
                    -AdminAccount $CurrentUser `
                    -SaCredential $saCred `
                    -PerformVolumeMaintenanceTasks `
                    -SaveConfiguration C:\InstallScripts `
                    -Configuration $config `
                    -Confirm:$false


# Connect using Windows authentication
$Servers = Connect-DbaInstance -SqlInstance SQL15AG1,SQL15AG2
$Servers | Select-Object DomainInstanceName,VersionMajor,Edition


# Connect using SQL authentication
$Servers = Connect-DbaInstance -SqlInstance SQL15AG1,SQL15AG2 -SqlCredential $saCred
$Servers | get-dbaDatabase | format-table -autosize

L’utilisation de la commande requiert de disposer d’un partage réseau hébergeant les sources de SQL Server. La commande va chercher récursivement le « bon » répertoire, correspondant à la version que vous souhaitez installer. De même, si vous disposer des Cumulative Updates, une installation SlipStream est possible.

Un certain nombre de paramètres d’installation sont accessibles directement depuis les paramètres de la commande. pour d’autres, il faut se tourner vers une variable de configuration qui reprend les items de la ligne de commande setup.exe

Je trouve cependant deux points très bien pensés pour ce module:
– La possibilité de spécifier plusieurs instance à installer (et cela fonctionne également pour des instances nommées)
– La possibilité d’enregistrer localement, sur la machine de rebond, le fichier INI correspondant à l’installation, cela peut servir …

Happy dbaTools !

Publié dans PowerShell | Tagué | 2 commentaires

dbaTools – Introduction et installation

L’utilisation de PowerShell est devenu un standard de l’administration, y compris pour les DBAs. Certes il existe des cmdlets natifs pour SQL Server, mais la bibliothèque dbaTools, projet communautaire, est devenu un incontournable et indispensable outil que tout DBA se doit de connaitre.

Cette courte vidéo est en fait une introduction pour une série d’articles et vidéos à venir sur les commandes PowerShell liées à la bibliothèque dbaTools.

Ci dessous le code utilisé lors de cette vidéo pour l’installation du module dbaTools.

Set-ExecutionPolicy Unrestricted

# Simplest way to install
Install-Module dbatools 

# Manual installation
Invoke-WebRequest "https://github.com/sqlcollaborative/dbatools/archive/master.zip" -OutFile "C:\sources\dbatools.zip"
Expand-Archive -Path "C:\sources\dbatools.zip" -DestinationPath "C:\Program Files\WindowsPowerShell\Modules"
Rename-Item "C:\Program Files\WindowsPowerShell\Modules\dbatools-master"  "C:\Program Files\WindowsPowerShell\Modules\dbatools" 
Get-ChildItem -Recurse "C:\Program Files\WindowsPowerShell\Modules\dbatools" | Unblock-File 
import-module dbatools


# check everything is fine
$Server = Connect-DbaInstance -SqlInstance SQL2019
$Server | Select-Object DomainInstanceName,VersionMajor,DatabaseEngineEdition

Happy dbaTools !

Publié dans PowerShell | Tagué | 4 commentaires