SQL Server – Durabilité retardée

Plus tôt dans la journée j’au publiée une petite vidéo au sujet de cette fonction méconnue de SQL Server : la durabilité retardée, ou Delayed Durability.

Lors des audits de performance que je mène chez mes clients, l’attente de type WRITELOG est une des attentes les plus fréquemment rencontrée. Cette attente est liée à la difficulté que rencontre SQL Server pour écrire les transactions dans le fichier LDF, le fameux journal des transactions.

Lorsque je suis d’humeur joueuse, je propose alors à mon client de tirer au sort els transactions qui seront autorisées à écrire dans le journal de transaction, et donc à satisfaire au « D » de « ACID » (Atomicité, Consistance, Isolation et Durabilité). Car en effet, le sous-système disque, principalement le volume qui héberge le journal de transactions doit avoir la capacité de supporter les IOs demandés par SQL Server, sous peine de provoquer de sérieux ralentissements. L’écriture dans le journal étant synchrone, tant que la transaction n’a pas été persistée dans le fichier LDF, souvenez vous que l’on fonctionne sous régime de WAL (Write Ahead Logging), il n’est pas possible de rendre l amain à l’application.

Donc lorsque le volume qui supporte le journal de transaction est la source des problèmes de performance, il n’existe que peu de solutions, mis à part l’augmentation de performance hardware … Sauf à soulager les IOs, d’où la blague qui consiste à tirer au sort les transactions autorisées à perdurer. Mais l’idée n’est pas farfelue pour autant car il suffit parfois de jeter un œil aux statistiques d’usage des index pour se rendre compte à quel point certains sont inutiles. Or, à chaque INSERT, UPDATE ou DELETE, l’index va lui aussi être modifié et générer une entrée dans le journal de transaction …

L’exemple suivant est assez flagrant, une table comportant plusieurs index noncluster supporte une charge conséquente en écriture, mais jamais les index sont utilisés pour la lecture …

Diminue les IOs, et donc la pression sur le journal de transaction peut être censé. Par exemple, Hekaton propose de type de fonctionnements. En effet le stables InMemory dans SQL Server permettent une durabilité de type SCHEMA_ONLY, autrement dit, on n’écrit JAMAIS dans le journal de transactions, seule la structure de la table est persistante. Il est possible d’utiliser ces tables « Temporaires » dans bien des cas.

Mais qu’en est-il pour les tables de type Row Disk Based, par opposition à InMemory. On ne va pas enregistrer une transaction sur 2, on est bien d’accord. Et contrairement à des problématiques de PAGEIOLATCH qui peuvent être amoindries par un ajout de mémoire, l’augmentation de capacité du BUFFER POOL ne changera rien à la donne si vous souffres d’attente de type WRITELOG.

Il faut donc se tourner vers une fonctionnalité méconnue apparue avec SQL Server 2014 qui permet d’opter pour une durabilité retardée. En fait, rendre l’IO d’écriture dans le journal de transaction asynchrone, et donc permettre à l’application de poursuivre le traitement sans attendre l’écriture physique sur disque.

Ci-dessous el bout de code m’ayant servi lors de démos :

USE MASTER
GO
DROP DATABASE IF EXISTS [DemoDelayedDurability];
CREATE DATABASE [DemoDelayedDurability];
GO

USE [master]
GO
ALTER DATABASE [DemoDelayedDurability] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [DemoDelayedDurability] 
MODIFY FILE ( NAME = N'DemoDelayedDurability', SIZE = 1GB )
GO
ALTER DATABASE [DemoDelayedDurability] 
MODIFY FILE ( NAME = N'DemoDelayedDurability_log', SIZE = 512MB )
GO

USE [DemoDelayedDurability]
GO
CREATE TABLE TestTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Val VARCHAR(100)
)
GO

Une fois la base et la table créées, place au test. Simple. 50 000 inserts dans la table. La première exécution en durabilité retardée désactivée, le mode historique dans SQL Server.

USE [DemoDelayedDurability]
GO
SET NOCOUNT ON
DECLARE @counter AS INT = 0
DECLARE @start DATETIME
SELECT @start = GETDATE()
WHILE (@counter < 50000)
BEGIN
    BEGIN TRAN
        INSERT INTO TestTable VALUES( @counter)
        SET @counter = @counter + 1
    COMMIT
END
SELECT DATEDIFF(MILLISECOND, @start, GETDATE() ) [Durable_Insert in msec] 

SELECT * FROM sys.dm_exec_session_wait_stats
WHERE session_id = @@spid
ORDER BY wait_time_ms DESC;

Résultat : 4 secondes d’exécution, dont près de 3 secondes d’attente de type WRITELOG, 50 000 attentes, comme par hasard …

La durabilité retardée est une option de base de données qui peut prendre les valeurs Forcée ou Autorisée.

En mode Forcé, toutes les transactions se verront faire des IOs asynchrones sur le fichier LDF.
En mode Autorisé, le choix est donné au développeur d’activer ou non la fonctionnalité, transaction par transaction.
Car il ne faut pas perdre de vue que lorsque l’on opte pour ce mode de fonctionnement, on s’expose à une perte potentielle de données en cas de crash dans les millisecondes ou secondes qui suivent la transaction.

On opte donc pour le mode Allowed.

USE [master] 
GO
ALTER DATABASE [DemoDelayedDurability] 
SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
GO

Et on rejoue le même test, attention à l’ordre COMMIT qui est modifié.

USE [DemoDelayedDurability]
GO
SET NOCOUNT ON
DECLARE @counter AS INT = 0
DECLARE @start DATETIME
SELECT @start = GETDATE()
WHILE (@counter < 50000)
    BEGIN
    BEGIN TRAN
        INSERT INTO TestTable VALUES( @counter)
        SET @counter = @counter + 1
    COMMIT WITH (DELAYED_DURABILITY = ON)
    END
SELECT DATEDIFF(MILLISECOND, @start, GETDATE()) [DelayedDurability_Insert in msec] 

SELECT * FROM sys.dm_exec_session_wait_stats
WHERE session_id = @@spid
ORDER BY wait_time_ms DESC;

Immédiatement la différence de performance saute aux yeux, plus d’attente de type WRITELOG

Ce test a été menu sur une machine dont le disque dur est de type SSD sur protocole NVMe, donc relativement rapide.

Mais plus le sous système disque est lent, plus le phénomène est présent, et donc grève les performances de l’application.

J’ai reçu un commentaire qui m’a conduit à écrire ce post en complément de la petite vidéo, justement en lien avec des systèmes aux performances disque limitées. Ce qui suit n’est donc pas présent dans la vidéo publiée.

Afin de mettre en exergue le phénomène, j’ai choisi de mener un test similaire (sur 10000 itérations seulement) sur Azure SQL Edge, mon SQL Server qui s’exécute dans un conteneur Docker sur un Raspberry Pi (on cumule donc les « problèmes »). L’installation se passait ici.

La création de la base et de la table sont strictement identiques. On exécute le premier test, durabilité retardé désactivée.

Plus d’une minute ont été nécessaires à l’exécution des 10 000 itérations d’insert. On voit les limites d’une carte SD, même de bonne facture.
Notez au passage l’attente de type PREEMPTIVE_OS_FLUSHFILEBUFFERS, apparue avec SQL Server 2017 pour les environnements Linux pour marquer effectivement le fait que l’on force cette écriture synchrone sur disque.

A présent un test avec durabilité retardé. Le constat est sans appel, moins de 5 secondes.

Il n’y a plus d’attente de type WRITELOG. Problème « résolu ».

Et donc, le commentaire reçu faisait état de problème similaire, des insertions multiples, mais il n’était pas possible de tolérer une perte de données, et donc l’obligation de conserver une durabilité immédiate.

Alors que faire ? Il ne reste pas grand-chose dans la botte du DBA. Peut être une revue de code afin de revoir la notion de transaction. Car le Flush des LOG Blocks sur disque s’effectue aussi en liaison avec le commit, donc une multitude de « petites » transactions va provoquer plus de « petits » IOs, compliqués à gérer pour le système, alors que si l’on déplace la transaction pour englober la boule, dans notre exemple, et donc exécuter l’ensemble des inserts dans UNE SEULE transaction, alors, le temps d’exécution n’est pas ridicule, loin de là.

L’exemple montre l’exécution en durabilité retardée, mais en positionnant e paramètre à OFF, le temps d’exécution est quasi similaire, avec … 1 seule attente de type WRITELOG. Logique mon cher Watson !

Cette « technique » d’optimisation est assez fréquente pour des bases hébergées sur SQL Azure, mais reste d’actualité pour tous les autres systèmes …

Comprendre le fonctionnement de SQL Server est important si vous souhaitez optimiser les performances de votre application. Si vous souhaitez une formation, n’hésitez pas à me contacter.

Happy Delayed Durability !

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

SQL Server sur Raspberry Pi

Suite à l’article sur Azure SQL Edge, j’ai reçu plusieurs messages demandant s’il était possible d’installer Azure SQL Edge, en fait une édition pouvant évoluer sur plateforme ARM, directement sur le Raspberry Pi sans passer par la couche IoT Hub Microsoft.

La réponse est OUI.

Je ne vais pas détailler ici l’installation d’un OS sur une carte SD à destination du Raspberry Pi. J’ai opté pour une distribution Ubuntu 20.04 qui fonctionne particulièrement bien avec le Pi4 8GB qui me servira pour ce test.

Une fois l’écriture de l’image Ubuntu sur la carte SD via Win32Diskimager (ou Rufus si vous préférez ce dernier), je procède simplement au renommage de mon OS, suivi d’une mise à jour.

sudo hostnamectl set-hostname rpi4ram8
sudo reboot
sudo apt-get update
sudo apt-get upgrade

L’étape suivant consiste à installer Docker, cela fonctionnerait également avec d’autres Runtime come CRI-O ou Podman.

sudo apt-get install -y docker.io
sudo systemctl enable docker

Docker est à présent installé et prêt à l’emploi.

sudo docker version

Il suffit de télécharger l’image du conteneur depuis le repository Microsoft

sudo docker pull mcr.microsoft.com/azure-sql-edge:latest

Et enfin de créer le conteneur

sudo docker run -e 'MSSQL_PID=developer' -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=Password1!' -p 1433:1433 --name azuresqledge -h azuresqledge -d mcr.microsoft.com/azure-sql-edge:latest

Il suffit à présent de tester la connexion à l’instance :

Comme attendu, cela fonctionne parfaitement.

Happy SQL Server on ARM / Raspberry Pi !

Publié dans Docker, Linux, SQL Server | Tagué , | 1 commentaire

Haute disponibilité … de vos sauvegardes

Il est amusant de constater à quel point les mots clé employés dans le titre d’un post conditionnent le nombre de vues. Je ne cours pas après les followers, vous avez pu constater que je ne suis pas vraiment un grand adepte des réseaux sociaux, mais je regarde quand même les chiffres pour comprendre quelles sont vos préoccupations. Et donc proposer des articles en lien avec vos centres d’intérêt, plutôt que de disserter sur les méthodes d’irrigation du riz en Chine pendant la dynastie Ming (1368-1644 pour les curieux), ou bien sur les premières traces de sa domestication en -9000. Merci Wikipédia !

Bref. Je souhaitais revenir aujourd’hui sur un problème rencontré il y a quelques mois par une société qui a, par la suite, souhaité être conseillé de manière régulière. Si vous avez eu l’occasion de suivre une de mes formations liées à l’administration SQL Server, ou bien à la haute disponibilité, ou encore relative au dépannage de SQL Server, vous avez peut être remarqué mon manque total de confiance dans le matériel, principalement les baies de disques. Malgré tous les efforts fait par les déférents constructeurs relayés par les revendeurs à grand renfort de formations voyages, je n’y arrive pas. Impossible de faire confiance. Et je ne cible personne en particulier. Après 22 ans passés a faire du SQL Server, j’ai eu l’occasion de croiser bon nombre de modèles de baies, de différents fabriquant. Aucun n’a pu me faire changer d’avis. Non, désolé.

Alors, pas pitié, ne stockez JAMAIS, JAMAIS, JAMAIS vos sauvegardes de bases de données sur la même baie de disque que vos données.

Des exemples, j’en ai … trop malheureusement. Et cela s’est accentué depuis la généralisation de la virtualisation. Il est tellement simple de créer un volume / disque supplémentaire pour une machine virtuelle sur le même datastore … Des pannes totales, comprenez par là, impossibilité de lire ou d’écrire quoi que ce soit sur la baie de disque, j’en ai vu. Ce qui met à mal bon nombre de stratégie basée sur la restauration des derniers backups. En effet, si vous n’avez qu’une seule baie, impossible de relancer ou de recréer des VMs. De même, si vous pensez restaurez vos données sur des VM Azure par exemple, encore une fois, cela ne sera pas pas possible.

Pensez donc à la disponibilité voire la haute disponibilité de vos sauvegardes. Gardez toujours en mémoire le bon vieil acronyme 3-2-1:

  • 3 copies des données : les données de production et 2 copies
  • 2 supports différents : attention, je ne parle pas de 2 volumes sur une même baie mais bien de 2 baies de disque distinctes, si possible de marque différentes pour éviter tout risque de problèmes sur les contrôleurs ( non, ça n’arrive jamais lors d’opération de mise à jour, jamais …)
  • 1 copie à l’extérieur : le gage de pouvoir effectuer une restaure en cas de non disponibilité de votre Datacenter.

Oui, tous ces points sont importants. Le dernier, la copie extériorisée est plus simple à implémenter que par le passé grâce aux différents services en ligne offerts par Microsoft, Amazon ou Google, ainsi que les éditeurs de solutions de sauvegarde / archivage. Auparavant, OK, on pouvait sortir un bande tous les X jours par exemple. Mais qui pensait à garder également à l’extérieur un exemplaire du matériel / logiciel nécessaire à la restauration ? Avoir une bande et devoir patienter 4 à 6 semaines pour réceptionner un lecteur qui aurait été détruit dans l’incendie de la salle technique est tout bonnement impensable, et réellement préjudiciable pour l’entreprise.

J’ai déplacé le débat niveau matériel, mais on peut également étendre la discussion aux sauvegardes SQL Server. Vous effectuez les test d’intégrité sur vos base de manière quotidienne, avant la sauvegarde ? Est-ce que vous testez régulièrement vos backups ? A J+1, mais aussi à J+7 ou J+30 ? Quelles sont vos contraintes légales en matière de rétention ? Si on vous demande 1 an, assurez vous très régulièrement que vous êtes en mesure de lire et de restaurer durant toute la période. Une corruption disque peut arriver à n’importe quel moment, ce n’est pas parce qu’un backup s’est terminé correctement que son utilisation sera possible plus tard …

Il n’est pas une semaine sans que l’on relate l’histoire d’une entreprise traitant d’une attaque de Ransomware. Encore une fois, mixer les technologies peut permettre de limiter les dégâts. J’ai en stock une expérience d’un client dont le Ransomware a affecté la production mais également le NAS hébergeant les sauvegardes. Bref, posez vous une bonne journée pour évaluer les différentes scénarii et planifier un DRP / PRA.

Autre mythe que je voudrais mettre à bas également : les sauvegardes restent OBLIGATOIRES même si vous disposez d’une solution de haute disponibilité SQL Server telles que le cluster de basculement ou les groupes de disponibilité. Je ne compte même plus le nombre de bases n’ayant pas fait l’objet de sauvegardes durant plusieurs années … No comment.

Abordons la gestion des sauvegardes pour SQL Azure, dont vous pouvez vous inspirer pour votre stratégie. Le premier point à retenir, c’est que … vous n’avez rien à faire ! SQL Azure est un service PaaS, donc la sauvegarde est à la charge du hosteur, Microsoft. D’un point de vue stratégie de sauvegarde, du classique. La fréquence des sauvegardes prévoit un backup full toutes les semaines, un backup différentiel toutes les 12 ou 24 heures et une sauvegarde du journal des transactions toutes les 5 à 10 minutes (basé entre autres sur l’activité de votre base, l’idée étant de limiter au maximum la croissance du fichier journal). Rien d’extraordinaire jusqu’à présent, il y a fort à parier que votre stratégie est très similaire à celle employée sur Azure SQL Databases et sur les instances managées.

La rétention par défaut des sauvegardes SQL Azure est de 7 jours. Vous pouvez étendre la période à 35 jours … Voire opter pour une politique LTR (Long Term Retention) qui conserve vos sauvegardes jusqu’à 10 ans.

Pour être raccord avec le thèse de ce billet, on peut s’intéresser à la redondance du stockage. Par défaut, ces sauvegardes sont hébergées sur un stockage redondant type RA-GRS. Si vous êtes novices en matière de stockage de type Blobs sur Azure, je vous renvoie à cette page qui vous donne les différents types de stockages. Mais globalement, GRS stipule que le stockage est redondant dans la région. Une région étant un ensemble de datacenters interconnectés en faible latence, disposant chacun d’alimentation électrique, d’accès internet et de climatisation distinctes.

AzureGeography

Votre Backup posé sur un tel stockage sera répliqué de manière synchrone 3 fois à l’intérieur de la région primaire (stockage LRS : Local Redundant Storage) et ensuite copié de manière asynchrone vers la région pairée (France Central <-> France Sud). les lettres RA signifie simplement Read-Access, vous pouvez lire les backups sur la région pairée en cas de perte totale d’une région Azure. La distance entre les zones étant de quelques centaines de kilomètres il y a peu de chances que les 2 zones soient indisponibles en même temps. Pour les adeptes des « Nines » pour évaluer la disponibilité, la durabilité des objets sur le stockage Azure est de 99,99999999999999 % (16 neufs) sur 1 an et d’au moins 99.99% en ce qui concerne la disponibilité.

La copie étant asynchrone entre la région principale et la région secondaire, le RPO sera donc plus important que si votre région primaire est encore disponible. Mais vous aurez au moins la possibilité de repartir sur un backup … Cela se passe dans le portail Azure, dans l’assistant de création d’une base de données. Vous avez la possibilité de créer une base de données vierge, mais aussi de restaurer une sauvegarde antérieure.

La date et heure (UTC) accolée à chaque sauvegarde correspond à la dernière copie asynchrone sur la région pairée. Ces sauvegardes sont accessibles à un serveur qui n’appartient pas forcément à la région primaire ni à la région secondaire de vos backups. Vous pouvez donc restaurer sur un serveur localisé partout ailleurs dans le monde.

Si par contre vous souhaitez un point de restauration plus précis, dans ce cas il est obligatoire d’utiliser le stockage localement redondant (LRS) et donc d’utiliser l’assistant de restauration d’une base dans le portail.

Vous avez donc à présent conscience de l’infrastructure mise en œuvre par Microsoft pour la conservation des sauvegardes des bases de données et la disponibilité de ces sauvegardes. J’entends les remarques relatant le fait que Microsoft n’a pas forcément les mêmes contraintes, probablement bien plus élevées que la très grande majorité des sociétés, mais il reste que le modèle en place est hautement disponible. Il est peut être temps de repenser votre stockage pour augmenter la disponibilité de vos sauvegardes. Je n’ai pas réussi à remettre la main (mais je compte sur vous …) sur une étude relatant les faillites de sociétés après un désastre informatique. J’ai des chiffres en tête, 80% de faillites dans les 5 ans à venir, mais j’aurais préféré les résultats de l’étude. Le chiffre peut faire peur, et doit faire peur. Il est du rôle du DBA de s’assurer que les données de l’ERP, du site Web de vente en ligne, de la comptabilité seront accessibles en cas de problème …

Je n’ai rien à y gagner, je ne vais pas vous vendre une baie de disque, ni du stockage cloud et encore moins une solution de sauvegarde/archivage, mais je voudrais juste éviter d’avoir à dire une nouvelle fois « désolé, là, je ne peux rien faire pour vous » car votre baie est HS, ou bien les données et les sauvegardes étaient sur la même VM cryptolockée. Volontairement, je n’ai pas abordé la notion de sauvegarde de VM. Je ne suis pas fan de la solution, au vu de tous les problèmes que cela peut engendrer.

Happy HA !

Publié dans Azure, Matériel, SQL Server | Tagué , , | Laisser un commentaire

Azure Logic App – Insérer des données dans SQL Azure et CosmosDB

Dans un article précédent j’utilisais Azure SQL Edge et Azure Data Sync pour remonter des informations issues de ma station météo vers une base de données SQL Azure. Ce scénario est un typique du monde IoT où un certain grand nombre de données issues de capteurs multiples sont poussés sur une base de données quelconque avant d’être analysées, éventuellement par une IA, en vue de détecter des problèmes, faire de la maintenance prédictive, etc …

Dans ce billet, donc, j’abordais l’outil de « programmation » Node-Red pour insérer les données dans Azure SQL Edge. Mais l’histoire n’était pas complète. Je souhaitais aussi que la ces données soient exportées vers des sites internet spécialisés (WeatherCloud, WeatherUnderground, …). J’ai donc utilisé les API de ces sites afin de pousser les données via des URL, comme c’est le cas ci-dessous.

Les heures passant, j’ai remarqué que le Rapsberry sur lequel était installé Azure SQL Edge souffrait de problèmes mémoire. De fait, la solution ne fonctionnait plus. J’ai commandé un modèle pourvu de 8GB de RAM afin de vérifier le comportement. Mai dans l’intervalle je souhaitais une solution simple à mettre en œuvre qui me permette de continuer à publier ces données dans Azure SQL Server. J’aurais très bien pu utiliser un node MSSQL. Cela fonctionne, je m’en sers pas ailleurs, en parallèle d’une insertion dans InfluxDB.

Mais, tant qu’à utiliser des service Azure, autant chercher des solutions alternatives. Rapidement l’envie de jouer avec les Logic Apps est venue.

Un déclencheur de type Request HTTP permet de capter exactement la même URL que celle utilisée pour les autres sites Météo. Il en reste alors qu’à écrire les données dans Azure SQL. Extrêmement simple …

J’ai donc utilisé le market place pour rechercher le service Logic App. Aucune difficulté lors de la création. Un nom, un groupe de ressources, une localisation géographique. Une fois la ressource créée, lorsque l’on passe en mode Design, il nous est proposé des templates, ainsi que des triggers afin de débuter notre application.

Je choisis donc le trigger HTTP request. Par défaut, le composant est configuré pour des requêtes de type POST. Sachant que je souhaite passer les valeurs en paramètre de l’URL, pas simplicité, il suffit de cliquer sur la combo « Add New Parameter », puis sélectionner la case à cocher « Method ». La validation se fait en cliquant … ailleurs, sur le canevas. OK, je vous l’accorde, ce n’est pas un modèle d’ergonomie évident à la première utilisation.

Une fois fait, la combo Method vous propose plusieurs choix, dont GET, que vous sélectionnez et validez.

Note : Pas d’affolement si au niveau du Design du composant, « HTTP POST URL » est encore affiché, il faut sauvegarder afin que cela soit modifié. Notez également, que l’URL de votre application n’est générée qu’après al sauvegarde.

Afin de vérifier que nous sommes capables de lire les données passées en paramètre, nous allons simplement utiliser une nouvelle étape de type HTTP Response. Pour cela, cliquez sur le bouton « New Step » qui apparait sur le canevas, dans la zone de recherche saisissez « response » et selectionnez le composant HTTP Response.

Le principe des Logic Apps, tout comme Node-Red ou même SSIS consiste à faire transiter un flux de données entre les différentes composants. Il suffit donc à présent de piocher la valeur de notre paramètre dans le flux. Lorsque vous cliquer sur « Enter response content » une fenêtre apparait vous proposant des éléments déjà connus et des fonctions telle que TriggerOutputs(). Nous aurions pu utiliser un Body de type JSON sur l’étape précédente et ainsi avoir accès aux différentes valeurs directement ou presque. Dans notre cas, il suffit d’utiliser une simple ligne de code dans la zone de saisie Body: @triggerOutputs()[‘queries’][‘temp’] , temp étant le nom du paramètre dans l’URL.

Vous pouvez aussi utiliser la fenêtre d’ajout de contenu dynamique, cliquer sur Expression, rechercher « triggerOutput » et saisir le reste de la commande [‘queries’][‘temp’].

Sauvegardez, et notez que l’URL est a présent disponible.

Afin de tester, copiez cette URL, ajoutez un paramètre (par exemple &temp=28.5) et testez depuis un navigateur. Vous devez recevoir en réponse une page Web vous présentant la valeur du paramètre.

Reste maintenant à insérer cette valeur dans Azure SQL Databases. POur cela on peut soit supprimer le composant HTTP Response et ajouter un composant SQL Server, soit ajouter une branche parallèle, notez le plus qui apparait sur la flèche de liaison entre les composants. Saisissez les informations d’authentifications (Azure AD, authentification SQL), le nom de votre server, de votre base de données.

Ensuite choisissez une opération de type Insert row (v2). Si tout se passe bien, vous devez pouvoir sélectionner le serveur et la base sélectionnés précédemment. Les tables de votre base doivent êtres elles aussi visible dans la liste. Et une fois la table sélectionnée, la liste des champs doit apparaitre. Pour ma part le DeviceID correspond à un de capteur de température que je possède. Pour les besoins de cette démo je vais arbitrairement utiliser un ID « factice ». La colonne Value est renseignée a l’aide du même code que précédemment : @triggerOutputs()[‘queries’][‘temp’]. Il n’y a plus qu’à sauvegarder et tester.

La page « Run History » de votre Logic App vous permet d’accéder aux informations relatives à l’exécution de votre App, la durée et le résultat. Ici tout est coché en vert, il semble donc que l’insert se soit fait correctement dans la table, ce que nous pouvons vérifier avec un simple SELECT.

Mais pourquoi proposer une solution aussi alambiquée alors qu’il était possible de faire un insert directement depuis Node-Red ? En fait, la station météo fournit, en plus de la température, des informations sur la force et la direction du vent, les précipitations, la pression atmosphérique, l’index UV, …. Toutes informations serait assez simples à stocker en NoSQL, sous forme d’un document JSON, non ? Et c’est là le fin mot de l’histoire. Profiter d’une seule URL pour insérer dans SQL Server et CosmosDB.

Here we go ! je vous laisse créer votre serveur CosmosDB, votre base et votre collection. Pour ma part, pas de clé de partition naturelle, je vais donc ventiler les données via une simple fonction Random, élément partition. Je commence par créer une variable également nommée partition initialisée par une valeur aléatoire rand(0,9). J’ai ensuite choisi de composer mon document XML au travers d’un composant spécifique afin d’appliquer quelques transformations aux données, des divisions par 10 dues au format d’entrée des sites Météo. Ensuite, j’ajoute un composant « Create or Update document » CosmosDB avec la sortie « Outputs » du composant précédent. il ne faut pas oublier d’ajouter le paramètre Partition key avec la valeur calculée précédemment.

Modifions le flux Node-Red afin de faire appel à l’URL de la Logic App.

Vérifions à présent si les données sont bien enregistrées dans CosmosDB …

Yes … tout est à présent inséré dans Azure SQL Database et CosmosDB.

Avec un peu d’habitude, il est aisé de se dispenser de la couche graphique de design d’une Logic App. Le code généré est clair et simple à lire. Visual Studio ou Visual Studio Code sont parfaitement outillés pour se passer avantageusement du portail.

D’autant plus qu’une version Designer est disponible…

Enjoy

Publié dans Azure, Azure Logic App, CosmosDB, SQL Azure | Laisser un commentaire

Problèmes de performance, mais pas que …

Il y a quelques jours, je faisais un point sur les prestations que j’avais réalisées récemment et celles à venir dans les prochaines semaines / mois. Il ressort de cette étude qu’une majorité de mes prestations est liée à des problèmes de performance.

Les prestations d’audit et d’optimisation semblent être au cœur des préoccupations. Cela se comprend. recevoir des complaintes d’utilisateurs plus ou moins aigris n’est jamais agréable. Mais au-delà du côté râleur bien français il est possible de mesurer l’impact financier d’une application souffrant de problèmes de performance. Des traitements de nuit trop lents peuvent aussi avoir un impact sur la production dans la journée. Et je ne parle pas forcément des opérations de maintenance telle que les sauvegardes, les tests d’intégrité ou la gestion des index.

Dans bien des cas les problèmes de performance sont résolus côté serveur, sans remise en cause de l’application. Des index correctement positionnés sur les clé étrangères (on en rit pas SVP, il y a encore des bases de données qui partent en production sans index sur les FK, voire même pas de clé primaire (!!!!) dans certains cas. Une bonne configuration hardware, système et SQL complète la liste des vérifications a effectuer. Malheureusement, et cela reste frustrant pour un DBA car on na pas d’impact direct sur l’amélioration des performances, l’amélioration des performance passe souvent par des réécritures de requêtes, une revue des l’algorithmique, l’utilisation de boucles qui envoient des centaines de milliers de requêtes pour un traitement ….

Je râle aussi énormément après des développeurs qui ne prennent pas le temps de typer correctement des paramètres de requêtes, principalement les requêtes LinQ ou bien via Entity Framework. Extrêmement simple à déceler via une session xEvent ou bien via le profiler car les paramètres sont tous de type NVARCHAR(4000). Lorsque ce type de données ne correspond pas aux colonnes de la table, cela implique une conversion implicite de la part de SQL Server, ce qui se traduit par un Cluster Index scan / Table Scan dans le pire des cas, ou un Nonclustered index scan dans le meilleur des cas. Bref, bien moins optimum qu’un index seek.

Afin de démontrer ce comportement, je vous propose de créer un table dont la colonne FirstName est de type VARCHAR, de créer un index non cluster sur cette même colonne et d’exécuter une requête SELECT avec un paramètre de type NVARCHAR.

CREATE TABLE Contact
(
	ID INT PRIMARY KEY,
	FirstName VARCHAR(50),
	LastName NVARCHAR(50)
);


CREATE INDEX IX_FirstName
ON Contact (FirstName);

SELECT *
FROM Contact
WHERE FirstName = 'Carina';
GO

SELECT *
FROM Contact
WHERE FirstName = N'Carina';
GO

Il apparait clairement que le mauvais typage des données rends la requête non SARGABLE. Bon, OK, vous ne trouverez pas ce mot dans le dictionnaire, mais basiquement cela veut dire que SQL Server ne pourra pas utiliser les arguments dans une recherche (comprenez un index seek) : Search ARGument ABLE.

Alors, oui, cela embête forcément le développeur de revoir son code, mais al performance est à ce prix là.

Résoudre un problème de performance est gratifiant, on voir immédiatement le fruit de son travail et l’on peut aisément mesurer la satisfaction client/utilisateur.

A l’inverse d’une solution de haute disponibilité qui au final n’est que peu visible par l’utilisateur final ! Au vu des différentes prestations effectuées, je suis étonné que l’on ne me sollicite pas davantage sur l’implémentation d’architectures hautement disponibles. Idem pour les formations que je dispense, la formation sur la haute disponibilité a moins de succès que les formations relatives à la performance !

Ne vous inquiétez pas pour autant, je ne passe pas une semaine sans faire de la HA, mais je n’arrive pas à comprendre que ce point ne soit pas plus une préoccupation pour l’IT comme pour le métier.

Alors oui une application performante c’est bien, mais si votre serveur SQL est HS, il n’y a plus d’application du tout ! Est-ce que vous avez réellement évalué la perte financière (chiffre d’affaire, salaire, …) liée à la non disponibilité de votre base de donnée ? Investir quelques milliers d’euros supplémentaires dans une solution hautement disponible pourrait vous éviter de perdre des dizaines ou centaines de milliers d’euros plus tard.

Les solutions actuelles de haute disponibilité offertes par SQL Server couvrent de multiples scénarios, en HA, en DR et offloading de traitements en lecture seule. La simplicité de mise en œuvre est sans pareille sur le marché, que ce soit sur des instance de basculement (FCI), des groupes de disponibilité, éventuellement du Log Shipping. Pourquoi ne pas tirer parti des possibilités offertes par Kubernetes pour héberger votre instance SQL Server ???

Attention cependant, beaucoup de mes clients se reposent sur les systèmes de « haute disponibilité » offertes par leur hyperviseur pour maintenir Online une machine virtuelle. Une VM online ne signifie pas un SQL Server Online. Rien à voir. Donc une VM en HA ne peut nullement être comparée à de la haute disponibilité SQL Server, ne vous en déplaise.

Alors, oui, j’entends les critiques qui pointent du doigt l’impact sur les performance d’un systèmes de haute disponibilité. Et effectivement, je ne peux pas le nier, en fonction de la solution choisie, il peut y avoir un impact négatif. Mais est-ce que vous êtes réellement à la recherche des 5%, peut être un peu plus, de différence en performance ? Le jeu en vaut il la chandelle si votre SQL reste indisponible des heures ?

On revient invariablement à deux acronymes : RPO et RTO
RPO : Recovery Point Objective, autrement dit les données que vous vous autorisez à perdre dans le pire des cas. Si votre RPO est de 24heures, alors probablement pas besoin de haute disponibilité, et une sauvegarde quotidienne peut vous satisfaire. Mais si le RPO tend vers zéro, alors il est probable que, quelques soit al stratégie de sauvegarde choisie, cela ne suffise pas et qu’il faille ajouter une brique de haute disponibilité pour satisfaire au besoin.
RTO : Recovery Time Objective. Bon, OK, tout est cassé, il y a eu un problème, maintenant il faut redevenir opérationnel le plus rapidement possible. N’espérez pas pouvoir restaurer une base de donnes de 5TB en 2 minutes, même avec un sous système disque hyper rapide. Il faut donc garder en réserve une base de données quasi prête à l’emploi (Hot Standby pour les intimes).

Et d’autres considérations rentrent en ligne de mire : quelle est la granularité de protection souhaitée ? le datacenter, le serveur, l’instance, une base, une table, un traitement ? En H24 7/7, ou bien sur une période ouvrée de 8h à 19h ? Bref, en fonction des cas, l’architecture mise en place et la technologie utilisée vont différer.

Loin de moi l’idée de noircir le tableau pour vous faire adopter une solution de HA. Prenez un bout de papier et notez les incidents liés à un SQL Server crashé (OS et/ou SQL Server, pas de cause lié à l’infra). Il ne doit vraiment pas y en avoir beaucoup. SQL Server est fiable, et ce depuis des années. J’interviens sur des environnements qui n’ont pas été stoppés / rebootés depuis des années. Oui, ces environnements n’ont pas été patchés, mais cela fonctionne très bien. Il faut discuter avec les responsables sécurité pour voir si cela pose un problème …

Il y a une chose que je ne peux pas faire pour vois, et que pourtant je vous suggère de faire rapidement. Prenez une journée avec les différents acteurs, management, métier, IT, et évaluez la perte financière en cas de non disponibilité de SQL Server. Le chiffre, en euros, qui en ressortira, vous indiquera clairement si il faut mettre en place une solution de HA. Une fois qu’elle sera en place vous pourrez dormir sur vos deux oreilles et penser … à l’optimisation 🙂

Enjoy !

Publié dans SQL Server | Laisser un commentaire

PASS Summit 2020 – Rendez-vous … virtuel

Ce n’est pas une nouvelle en soi. L’édition 2020 du PASS Summit sera virtuelle, comme bien des évènements depuis quelques mois malheureusement.

Pour cet opus 2020 je suis honoré d’être sélectionné en tant que Speaker dans le Learning Pathway : Modernizing with SQL Server. Il y a quelques semaines déjà que j’avais l’information, mais un embargo puis les congés ont un peu décalé la parution de ce post.

Cela fait maintenant une bonne dizaine années que je me déplace pour le PASS Summit, que ce soit à Seattle ou bien lors de l’édition 2013 à Charlotte. Mon billet pour Houston est acheté depuis début Janvier … J’animerai donc cette session depuis mon bureau, mais toujours avec autant d’enthousiasme.

A titre personnel, en tant que speaker, je préfère très largement l’interaction avec l’auditoire lors d’un évènement présentiel que de parler devant une camera… Mais, respectons les règles actuelles pour pouvoir profiter de nouveau très rapidement de ces évènements communautaires!

Bien des clients chez qui je suis amené à faire du consulting ou de la formation regrettent de ne pouvoir assister à ce tels évènements, du fait de la distance principalement. Aucune raison cette fois-ci de ne pas participer, vu que l’évènement sera virtuel …

Hâte de vous retrouver lors du PASS Summit…

Publié dans Conférence, Docker, Evènements, Kubernetes, Linux, SQL Server | Tagué | Laisser un commentaire

Modernisation de votre plateforme SQL Server – Replay disponible

L’heure de la rentrée va bientôt sonner. Pour reprendre en douceur, je vous propose de visionner le replay de la session « Modernisation de votre plateforme SQL Server » que j’ai eu le plaisir d’animer avec Jean Glaudon, Microsoft Data & AI Sales Specialist, et Stéphane Papaix, Microsoft Cloud Solution Architect.

4 heures … mais vous pouvez aussi faire des pauses !

Happy Webinar !

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

Azure SQL Edge – Synchronisation des données

Après avoir déployé Azure SQL Edge, j’ai profité des différents capteurs de température qui surveillent mon domicile pour intégrer des données dans une base créé spécifiquement sur mon instance SQL locale sur le Raspberry.

Pour aller à l’essentiel, j’ai installé un conteneur Node-Red sur un autre serveur Docker, mais j’aurais très bien pu réutiliser le moteur Moby du Raspberry hôte de ma solution Azure IoT.

Node-Red

Le but n’est pas d’expliquer en détail Node-Red, mais un petit overview est toujours intéressant, cela pourrait vous donner des idées ….

Node-Red est un outil de programmation basé sur des flux de données initié en 2013 par des employés d’IBM. Chacun des nœuds prends (ou pas, s’il s’agit de déclencheurs par exemple) des données en entrée, effectue une tâche, et potentiellement fournit un flux de données en sortie. N’hésitez pas à télécharger et à tester, cela peut fournir bien des services (qui a osé penser à l’appel de la solution de maintenance de OLA Hallengren pour des instances SQL Server Express ….).

Comme bien des projets open-source, chacun peut apporter sa contribution et développer ses propres nodes. Ainsi, si vous ne trouvez pas votre bonheur avec les composants installés par défaut, piochez dans le catalogue … C’est exactement ce que j’ai fait pour pouvoir communiquer avec mon instance Azure SQL Edge, j’ai ajouté un composant MSSQL.

Mon flux est donc extrêmement simple. Toutes les minutes, je récupère les valeurs des capteurs de température que j’insère dans la base SQL Server sur mon Raspberry.

Requêtes time-series

Une des fonctionnalités spécifiques de l’édition Edge de SQL Server est de pouvoir requêter simplement des données « groupées » par sous-ensembles au travers de la fonction SQL Date_Bucket. Cette fonction renvoie une Date Heure depuis le 1er janvier 1900.

Pour l’exemple suivant, j’ai opté pour des intervalles de temps de 10 minutes, alors que mes capteurs remontent des informations toutes les minutes.

Cela permet de lisser les données lorsque l’on n’a pas besoin d’informations très détaillée par exemple. Il est techniquement possible d’écrire une requête similaire, probablement au travers de la clause OVER, mais cela serait bien plus complexe. La fonction Date_Bucket permet de sélectionner un type d’intervalle (jour, semaine, heure, minute, seconde) mais également la portée de cet intervalle. Vraiment simple à utiliser.

Synchronisation des données vers Azure

Nous venons d’aborder le premier bénéfice de Azure SQL Edge avec sa capacité à exécuter des requêtes Time Series.

Nous allons maintenant aborder la synchronisation des données vers la base AzureSQLEdgeReplica hébergée en mode PaaS sur Azure (Single Database en mode basique pour cette démo). Azure SQL Edge peut faire office d’abonné dans une réplication transactionnelle en mode PUSH, mais cela ne sert à rien dans le cas présent puis qu’il est question de remonter des données dans une base sur Azure afin de l’exploiter hors site, pour du reporting par exemple.

Je créé également un utilisateur de base de données spécifique pour la synchronisation. Db_owner n’est pas vraiment nécessaire dans le cas présent, mais il s’agit que d’une démo.

Ne pouvant pas utiliser de mécanisme tel que le log shipping vers Azure SQL Database, ni la réplication (seulement en tant qu’abonné), Il faut alors se tourner vers le service Azure Data Sync.

La technologie se base sur des triggers afin de capturer les changements de données. Désolé, j’aurais préféré vous vendre quelque chose de plus sexy, mais ce n’est pas le cas.

Sur la base « destination », on va donc créer un groupe de synchronisation :

Lors de la création de ce groupe de synchronisation, je spécifie la base AzureDataSyncMetaData contenant les métadonnées nécessaires au fonctionnement de la solution.
Cette base a été créée pour un test précédent. Elle peut en effet servir à différents groupes de synchronisation.
Ensuite je spécifie la fréquence de synchronisation et le vainqueur en cas de conflit un peu comme une réplication de fusion en somme.

Une fois le groupe créé, il faut alors ajouter un membre (par rapport au modèle Hub And Spoke de Azure Data Sync). Dans notre cas, il s’agit d’ajouter l’instance Azure SQL Edge qui fonctionne sur le Raspberry Pi.

Si vous n’avez pas créé d’agent SQL Azure Data Sync, c’est le moment, sinon, vous pouvez utiliser un agent existant.

<Configuration de Microsoft SQL Data Sync>

Si vous n’avez pas encore créé votre agent, il faut :

  • Télécharger l’agent
  • Installer l’agent et fournir un login Windows lui permettant de s’exécuter
  • Sur le portail Azure : générer Azure une clé (toujours sur le même écran qui vous permet de créer un nouvel agent)
  • Sur l’application Agent SQL Data Sync : soumettre la clé copiée depuis le portail et fournir un Login SQL permettant de se connecter à la base Azure contenant les métadonnées (SQLDataSyncUser dans mon cas)
  • Sur l’application Agent SQL Data Sync : Enregistrer une connexion vers l’instance SQL Server que l’on souhaite synchroniser. Ici, il s’agit donc de Azure SQL Edge qui tourne sur le Raspberry

Une fois terminé, vous pouvez valider la connectivité avec le service de synchronisation sur Azure en cliquant sur Ping Sync Service.

</Configuration de Microsoft SQL Data Sync>

Sur le portail vous pouvez donc sélectionner l’agent de synchronisation ainsi qu’indiquer quel est le sens de synchronisation. Nous souhaitons pousser les données vers Azure, donc « To The hub » afin de recopier els données vers la base AzureSQLEdgeReplica.

A présent 2 bases sont répertoriées dans la topologie, la base SQL Azure et la base locale du Raspberry. Cliquer sur Tables afin d’ajouter les tables participant au process de synchronisation. Attention, contrainte de clé primaire obligatoire pour pouvoir synchroniser.

En fonction des données à remonter vers Azure et de l’intervalle de synchronisation choisi au niveau du sync group, vous devez voir arriver vos données sur Azure

Ce que nous pouvons vérifier au travers de SSMS

Les données sont à présent présentes sur Azure, prêtes à être exploitées.

Happy Azure SQL Edge !

Publié dans Azure, Containers, Docker, IoT, Linux, SQL Server | Tagué , | 1 commentaire

Cockpit – K8s scaling

Chose promise chose due … Le billet précédent faisait référence à une vidéo que j’avais faite en Aout 2019. Je l’ai retrouvée et publiée sur Youtube.

L’interface Cockpit, présent un service hébergé par un cluster 3 nœuds sous CentOS. Sur un écran non visible, en ligne de commande, je changeais le nombre de pods du replicaset. On voit donc les Pods qui s’agglutinent au service Kubernetes au fur et à mesure qu’ils sont jugés opérationnels par Kubernetes.

Publié dans Kubernetes, Linux | Tagué | Laisser un commentaire

Ubuntu – Installation de Cockpit

Si vous lisez quelques articles sur ce blog, il ne vous aura pas échappé que l’OS Linux est de plus en plus présent. Que ce soit pour le support de Docker ou bien de Kubernetes, ou même pour supporter directement SQL Server. Mes compétences et mon offre de service reste toujours focalisée sur SQL Server, mais en tant que consultant je me dois de connaitre les OS, les plateformes de virtualisation, les possibilités offertes par les Clouds public le plus populaires mais aussi toucher au matériel comme les serveurs et baie de disques afin de vous conseiller au mieux sur vos choix d’architecture.

Historiquement, Windows s’administrait de manière graphique, à la souris. Mais il était également possible d’effectuer des tâches d’administration en ligne de commande. J’avoue pour ma part utiliser de plus en plus de PowerShell, voire exclusivement du PowerShell pour certaines choses.

Linux, quant à lui, s’administre exclusivement en ligne de commande, même si pour le fun on peu monter une interface graphique. Je ne vais pas entrer dans des querelles de chapelles à savoir quelle distribution est la meilleure, ce n’est pas mon propos. Pour ma part, j’utilise volontiers du CentOS et de l’Ubuntu serveur, et en poste client Ubuntu desktop, Fedora. Je ne me permettrai pas de juger des capacités de l’une ou l’autre. Mon discours est simple. Chacun de mes clients à ses habitudes, ses compétences, je me plie donc à leur standard, tant qu’il respecte ce qui est supporté par SQL Server.

Revenons à nos moutons, ou plutôt à nos IHMs. Windows, vous connaissez. Le serveur Manager qui se lance automatiquement à l’ouverture de session (grrrrrr, je ne supporte pas et je désactive systématiquement). La console PowerShellISE, le gestionnaire de service, l’event viewer … Bref vous êtes en terrain connu.

Mais avez-vous remarqué cette fenêtre qui apparait, en fonction de la version de votre OS serveur ?

Je détaillerais l’installation dans un autre billet, mais, en 2 mots, une interface Web qui vous permet d’administrer votre serveur. Un « Must Have » si vous voulez mon avis. Et je trouve le graphisme particulièrement réussi.

Revenons maintenant à Linux. Depuis que SQL Server est supporté sur Linux, je me suis concocté une panoplie de scripts d’administration, en Bash ou en PowerShell puisqu’il est à présent possible d’installer les modules PowerShell sur Linux. Mais je souhaitais aussi retrouver une interface graphique qui me permette de vérifier les éléments de base sans avoir à me connecter en SSH et utiliser un HTOP ou autre.

Depuis des années j’utilise Cockpit, un projet sponsorisé par Red Hat, que ce soit sur Ubuntu ou CentOS.

Mon précédent billet parlait du déploiement de Azure SQL Edge sur un Raspberry Pi3 sous Ubuntu 18.04. Je n’avais encore jamais testé sur plateforme ARM, mais Cockpit fonctionne parfaitement. Les images Docker sont disponibles et fonctionnent parfaitement sur le runtime Moby.

L’installation se fait en quelques lignes de script :

sudo add-apt-repository universe

sudo apt-get update

sudo apt-get install cockpit cockpit-docker

Reste ensuite à se connecter à l’interface Web.
HTTP://[ip or DNS]:9090

Je ne vais pas forcément détailler tous les onglets, mais on peut ainsi superviser l’activité système (CPU, Stockage, Réseau, services, ..). Notez également, et c’est là un intérêt majeur, on peut voir les conteneurs qui s’exécutent sur l’OS (cela fonctionne même avec Kubernetes, il faut que je vous retrouve la vidéo faite il y a plusieurs mois lors d’une démo de Scaling d’un réplicaset …).

On retrouve les conteneurs créés précédemment. On peut également accéder au détail du conteneur, à la log, le stopper, le redémarrer, bref utile …

Happy Cockpit !

Publié dans Linux | Tagué | Laisser un commentaire