Datafly.pro

Datafrogs juin 2024 : ménage de printemps

Arian Papillon — Thu, 06 Jun 2024 09:48:00 +0100

Pour cette session des Datafrogs, le 1er juin, le sujet de notre présentation était "Ménage de printemps".

Il y a sûrement plein de choses qui ne servent plus à rien dans vos instances SQL Server, ce qui crée des trous de sécurité et augmente inutilement la consommation de ressources :

Combien de login avez vous qui ne servent plus à rien ?
Combien d'utilisateurs SQL sont orphelins dans mes bases ?
Combien de bases ne sont plus accédées ?
Combien de fichiers de données ou de groupes de fichiers sont vides ?
Combien de table ne sont plus accédés ?
Combien d'index ne sont plus utilisés ?
Combien d'index sont redondants ou inclus ?
Combien de statistiques sont redondantes ?

Vous verrez que ce n'est pas si simple que cela à trouver et nettoyer.

Les diapositives sont ici, et les scripts (une partie) là. La vidéo suivra.

Bon ménage...

Retrouvez la version d'origine

Arian Papillon — Thu, 18 Apr 2024 23:23:00 +0100

Savez-vous que les fichiers de données ou de sauvegarde de SQL Server embarquent l'information sur la version d'origine qui a servi à les créer ?

Dans pas mal de cas, il peut être utile de savoir de quelle version provient un fichier de données ou de sauvegarde, voire connaître pour une base de données existante dans quelle version elle a été créée au départ.

Voici comment obtenir cette information...

Un numéro de version interne est enregistré dans le fichier (mdf ou bak), voici la liste :

Appellation commerciale	N° version	N° interne
2000	80 / 8.0	539
2005	90 / 9.0	611,612
2008	100 / 10.0	655
2008R2	100 / 10.5	661
2012	110 / 11.0	706
2014	120 / 12.0	782
2016	130 / 13.0	852
2017	140 / 14.0	869
2019	150 / 15.0	904
2022	160 / 16.0	957

Voici maintenant le moyen d'extraire l'information, reportez-vous au tableau pour trouver la version correspondante...

Depuis une sauvegarde

Pour obtenir l'information depuis un fichier de sauvegarde .BAK, lancez la commande TSQL suivante et relevez la valeur dans la colonne DatabaseVersion :


RESTORE HEADERONLY FROM DISK = 'x:\path\file.bak'  -- remplacez par votre fichier bak

A partir d'un fichier de données

Si vous ne voulez pas attacher la base de données (et potentiellement la migrer vers une version ultérieure), il faut aller chercher l'information en hexadécimal dans le fichier.

Heureusement il y a plus simple, avec la procédure stockée spDBVersion que vous trouverez ici. Elle n'est pas de moi et provient d'un forum msdn.

Dans une base en ligne

Pour obtenir l'information de la version de création depuis une base de données existante et en ligne, utilisez la commande suivante et relevez la valeur dans la ligne dbi_createversion :


DBCC DBINFO(nom_de_la_base) WITH TABLERESULTS

Bonne exploration archéologique dans les versions de SQL Server... Comme cela m'arrive souvent, vous constaterez peut-être que les bases de données de certains progiciels dits "modernes" ont été créées à l'origine sous SQL Server 2000 il y a 20 ans...

Nouveau SSMS 20.0

Arian Papillon — Fri, 22 Mar 2024 14:00:00 +0000

Le nouveau SSMS (SQL Server Management Studio) version 20 est arrivé.

Pas encore visible sur le site français, mais déjà téléchargeable (et en français si souhaité) sur le site US : https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16

A noter :

Cette version 20 ne met pas à jour les versions précédentes (19.x et antérieures) mais s'installe en plus !
Vous pouvez récupérer votre configuration depuis les versions 19.x ou 18.x mais pas les précédentes.

A première vue, pas de changement majeur de l'interface, mais attention aux paramètres de sécurité de connexion (chiffrement, trust server certificate), maintenant configurables à la connexion.

Vous pouvez visiter les release notes à partir de la page citée plus haut.

SSMSInfoReports : nouvelle release v9.2

Arian Papillon — Tue, 19 Mar 2024 16:32:00 +0000

Encore une nouvelle version de mes rapports custom pour SQL Server Management Studio, la 9.2 !
Avec dans cette release des corrections de bugs, des améliorations, et un nouveau rapport qui interroge le Query Store.
Il y a maintenant 18 rapports !
Pour télécharger les rapports, c'est sur github : https://github.com/datafly/SSMSInfoReports
J'avais aussi fait il y a quelque temps une vidéo qui explique comment les installer et les utiliser : https://www.youtube.com/watch?v=rIY4QGWvvzQ

Tracer l'activité des développeurs sur la production

Arian Papillon — Mon, 12 Feb 2024 11:26:00 +0000

Hélas, on a laissé jouer les développeurs sur la base de production !

Question posée : par quel moyen surveiller leurs actions ?

Dans cet article, on utilise la fonction d'audit de SQL Server pour capturer toutes leurs requêtes.

Comment fonctionne l'audit de SQL Server ?

L'audit dans SQL Server permet d'assurer le suivi des actions effectuées sur les bases de données. D'une manière générale, cela permet aux administrateurs de surveiller et d'enregistrer une série d'activités et d'événements du système.

SQL Server propose deux niveaux d'audit :

Audit au niveau du serveur : Suivi des actions au niveau du serveur, comme les changements de configuration ou les tentatives de connexion.
Audit au niveau de la base de données : Surveillance des actions spécifiques à une base de données, telles que les modifications de schéma ou l'accès aux données : select, insert, update, delete, etc..

Pour configurer l'audit dans SQL Server, vous devez d'abord :

Configurer l'audit avec la commande CREATE SERVER AUDIT, en spécifiant la destination (généralement un fichier, mais il est possible d'envoyer l'audit vers l'event log windows applications ou sécurité). Dans le cas d'une destination fichier, vous pouvez préciser ses caractéristiques : taille, nombre, rotation.
Créer une ou plusieurs spécifications d'audit : Définissez ce que vous voulez auditer, au niveau du serveur ou de la base de données, en précisant vers quel audit serveur (créé à l'étape précédente) doivent être envoyées les données.

Une fois l'audit configuré, vous pouvez activer ou désactiver à volonté l'audit ou une spécification d'audit : vous avez la flexibilité de démarrer ou d'arrêter l'audit selon vos besoins.

Pour lire les données d'audit, vous pouvez passer par SQL Server Management Studio, ou lire le fichier avec la fonction SQL fn_get_audit_file.

Attention : l'audit peut avoir un impact sur les performances du serveur, surtout s'il est configuré pour auditer un grand nombre d'événements. Limitez l'audit aux actions et aux événements les plus critiques.

Exemple

Voici un exemple de configuration à adapter à votre cas de figure : mes développeurs sont membre d'un rôle de bases de données 'Developpers_role', il va donc être plus facile de les tracer.

Je configure d'abord l'audit, vers une destination de fichiers dans le répertoire log de SQL Server, avec 5 fichiers de 100 MB en rotation.

USE master;
GO
DECLARE @errorlogpath NVARCHAR(1000)
      , @sql NVARCHAR(MAX);
SELECT @errorlogpath = LEFT(path, LEN(path) - 1)
FROM sys.dm_os_server_diagnostics_log_configurations;
-- Create server audit
SET @sql
    = N'CREATE SERVER AUDIT [Developpers_Audit]
TO FILE
(    FILEPATH = ''' + @errorlogpath
      + N'''
    ,MAXSIZE = 100 MB
    ,MAX_ROLLOVER_FILES = 5
    ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);';
EXEC (@sql);
-- Enable the server audit.  
ALTER SERVER AUDIT Developpers_Audit   
WITH (STATE = ON) ;  
GO

Je vais maintenant configurer la spécification d'audit dans la base de données à surveiller : je capture les SELECT, UPDATE, INSERT, DELETE et EXECUTE sur tous les objets du schéma dbo, uniquement pour les développeurs. Facultativement, je peux aussi capturer les CREATE, ALTER ou DROP de tous les utilisateurs.

-- Target database
USE AdventureWorks2019;  
GO  
-- Create the database audit specification.  
CREATE DATABASE AUDIT SPECIFICATION DB_Developpers_Audit  
FOR SERVER AUDIT Developpers_Audit  
-- actions filtrées sur les développeurs
ADD (SELECT , UPDATE, INSERT , DELETE, EXECUTE
     ON Schema::dbo BY Developpers_role )
-- si nécessaire pour tous les create/alter/drop
-- , ADD  (SCHEMA_OBJECT_CHANGE_GROUP)  
WITH (STATE = ON) ;  
GO

Et maintenant, voici comment lire mon fichier d'audit en SQL (je peux bien sûr envoyer cela vers une table...)

-- Read audit file
SELECT event_time
     , action_id
     , server_principal_name
     , database_principal_name
     , database_name
     , object_name
     , statement
     , duration_milliseconds
     , response_rows
     , affected_rows
FROM fn_get_audit_file('Developpers_audit*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id IN ( 'SL', 'UP', 'DL', 'EX','CR','AL','DR');

Bon audit, et n'oubliez pas de gronder les développeurs pour leurs requêtes gourmandes sur la production...

Comment utiliser les rapports SSMSInfoReports

Arian Papillon — Thu, 28 Dec 2023 11:34:00 +0000

Pour comprendre à quoi servent mes rapports custom pour SSMS (SSMSInfoReports), comment les installer et les utiliser : la vidéo.

SSMSInfoReports : nouvelle release v9

Arian Papillon — Tue, 31 Oct 2023 16:28:00 +0000

Une nouvelle release de mes rapports custom pour SQL Server Management Studio, la v9 !

Enormément d'améliorations partout dans cette release et en prime un rapport supplémentaire sur les locks et deadlocks, ce qui porte maintenant le nombre de rapports à 17.

Pour ceux qui ne les connaîtraient pas déjà, cet ensemble de rapports chaînés vous permettent d'avoir un aperçu rapide et graphique d'une instance et de ses bases de données, sous tous ses aspects : configurations, erreurs, stockage, backups, jobs, sécurité, performances... Et tout cela sans devoir passer par des dizaines de requêtes ou boîtes de dialogue. Un sacré gain de temps !

Côté pratique, ils se lancent directement dans SSMS et peuvent être exportés (pdf) voire imprimés... je m'en sers tous les jours ! (pour l'anecdote, j'ai appris que des consultants Microsoft les utilisent et les déploient chez leur clients...)

Vous pouvez les télécharger sur https://github.com/datafly/ssmsinforeports

La v9 est publiée comme il se doit dans la section "Releases"

Notes de version :

En plus des nombreuses améliorations d'affichage ou de requêtes, voici les principales :

Main Server Dashboard

Liste des volumes/disques améliorée : utilise désormais xp_cmdshell et WMIC plutôt que Ole automation. Affiche maintenant les noms de volume et les points de montage.
Amélioration du parsing d’errorlog : ne traite pas les journaux trop grands
Amélioration de l’affichage des jobs : affichage des catégories de travail et appel du rapport filtré sur les catégories

Server Report

Affichage correct de la version pour SQL Server 2022
Affichage des protocoles et ports (si la version le permet)
Affichage de l’Instant File Initialization (si la version le permet)
Affichage de Lock Pages In Memory
Intégrité : affichage des « suspect pages » et des memory dumps

All Databases Report

Refonte complète du tableau des backups
Nouvelles options de tri

Database Report

Affichage des database scoped configurations (si existantes)
Amélioration de l’affichage des fichiers

Index Report

Affichage des missing indexes pour les tables sélectionnées (lorsqu’il y en a)

Performance Report

Affichage des bases avec Query Store activé
N’inclut plus les graphiques de ressources par base (long à afficher), qui sont maintenant reportées vers un rapport à part appelé à partir d’un bouton.
Bouton d’appel du nouveau rapport « Lock-Deadlocks » (si la version le permet)

Nouveau rapport « Locks-Deadlocks »

Affichage détaillé des dernières attentes de verrous et derniers deadlocks à partir du system_health
Ce rapport n’est pas compatible avec SQL Server 2008/R2.

Utiliser les indicateurs de requête, la présentation

Arian Papillon — Mon, 30 Oct 2023 11:42:00 +0000

Téléchargez la présentation de la conférence Datafrogs "utiliser les indicateurs de requête (Query hints)".

https://blog.datafly.pro/public/Documents/Datafrogs_-_Query_Hints.pdf

La vidéo de la présentation est aussi en ligne (il manque quelques minutes d'image à la fin) :

Conférence Datafrogs 14 octobre 2023

Arian Papillon — Fri, 13 Oct 2023 08:59:00 +0100

Samedi 14 octobre à 14h45, j'aurai le plaisir de présenter une sessions sur les "Query Hints" ou "Indicateurs de requête".

L’optimiseur de SQL Server décide du meilleur plan d’exécution et il s’en débrouille plutôt bien. Mais comment faire pour lui forcer la main si on est sûr de pouvoir faire mieux que lui ?

Dans cette session, je vous montrerai les différents indicateurs de requêtes et leurs effets.

Le lien (linkedin) vers l'évênement : https://lnkd.in/gZZRT7zK

Best practices N° 4 - configuration de l'instance SQL

Arian Papillon — Mon, 25 Sep 2023 14:23:00 +0100

Etape importante de configuration post-installation, à minima retoucher les paramètres de l'instance :

Mémoire maximale
Compression et checksum des sauvegardes
Parallélisme

Voyons comment faire cela avec quelques scripts SQL

Concernant la mémoire maximale, il est utile de configurer ce paramètre pour éviter que SQL Server lutte avec l'OS pour sa consommation de mémoire. S'il s'agit d'un serveur dédié à SQL Server, la règle suivante peut s'appliquer :

Réserver 1 Go pour l'OS
Y ajouter 1 Go pour chaque 4 Go de RAM installée entre 4 et 16 Go
Y ajouter 1 Go pour chaque 8 Go de RAM installée à partir de 16 Go
Configurer max server memory avec la mémoire qui reste.

Le script suivant fait le calcul et applique la modification :

-- configurer max server memory
DECLARE @memOsBase DECIMAL(9, 2)
      , @memOs4_16GB DECIMAL(9, 2)
      , @memOsOver_16GB DECIMAL(9, 2)
      , @memOsTot DECIMAL(9, 2)
      , @memForSql DECIMAL(9, 2)
      , @sql NVARCHAR(MAX)
      , @memInMachine DECIMAL(9, 2);
-- Get current machine memory configuration
SELECT @memInMachine = physical_memory_kb / (1024 * 1024.)
FROM sys.dm_os_sys_info;
SET @memOsBase = 1;
SET @memOs4_16GB = CASE
                       WHEN @memInMachine <= 4 THEN
                           0
                       WHEN @memInMachine > 4
                            AND @memInMachine <= 16 THEN
(@memInMachine - 4) / 4
                       WHEN @memInMachine >= 16 THEN
                           3
                   END;
SET @memOsOver_16GB = CASE
                          WHEN @memInMachine <= 16 THEN
                              0
                          ELSE
(@memInMachine - 16) / 8
                      END;
SET @memOsTot = @memOsBase + @memOs4_16GB + @memOsOver_16GB;
SET @memForSql = @memInMachine - @memOsTot;

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
SET @sql
    = N'EXEC sp_configure ''max server memory'',' + CAST(CAST(@memForSql * 1024 AS INT) AS VARCHAR(10))
      + N'; RECONFIGURE;';
PRINT @sql;
EXEC (@sql);

Bien sûr, il faut adapter cette configuration si d'autres processus tournent sur le serveur.

N'oublions pas de configurer la compression et le checksum des sauvegardes, qui améliorent leur performance, leur volumétrie et leur sécurité :

EXEC sys.sp_configure N'backup compression default', N'1'
GO
EXEC sys.sp_configure N'backup checksum default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Concernant le parallélisme, c'est plus délicat : cela dépend du type de travail exécuté sur notre serveur SQL. Une règle de départ pourrait être :

Positionner "max degree of parallelism" (nombre de processeurs pour le parallélisme) à la moitié des CPU
Augmenter "cost threshold for parallelism" (seuil de coût) à 30
On peut aussi activer "Optimize for adhoc workload" (optimiser pour les charges de travail adhoc permet d'éviter de saturer le cache de plans avec des requêtes qui ne sont exécutées qu'une seule fois)

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
DECLARE @cpu_count INT, @sql NVARCHAR(MAX);
-- Get current machine cpu configuration
SELECT @cpu_count=cpu_count
FROM sys.dm_os_sys_info;
SET @sql=N'EXEC sys.sp_configure ''max degree of parallelism'','
         +CAST(CASE WHEN @cpu_count<=3 THEN 2 ELSE FLOOR(@cpu_count / 2)END AS VARCHAR(3))+N'; RECONFIGURE;'+CHAR(10);
SET @sql=@sql+N'EXEC sys.sp_configure ''cost threshold for parallelism'',30; RECONFIGURE;'+CHAR(10);
SET @sql=@sql+N'EXEC sys.sp_configure ''optimize for ad hoc workloads'',1; RECONFIGURE;';
PRINT @sql
EXEC (@sql)

Best practices N° 3 : configurer les historiques

Arian Papillon — Sun, 24 Sep 2023 11:13:00 +0100

SQL Server ne conserve par défaut que 6 archives du journal d'erreur, et l'historique des travaux de l'agent SQL est bien trop court.

Nous allons remédier à cela avec quelques scripts SQL.

Voici encore deux petits scripts :

-- Passer le nombre d'archives d'errorlog à 12
DECLARE @Numlogs INT;
DECLARE @RegTable TABLE
(
    Value VARCHAR(128)
  , Data VARCHAR(128)
);
INSERT @RegTable
(
    Value
  , Data
)
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE'
                       , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                       , N'NumErrorLogs';
SELECT @Numlogs = CAST(Data AS INT)
FROM @RegTable;
IF @Numlogs < 12
   OR @Numlogs IS NULL
BEGIN
    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
                            , N'Software\Microsoft\MSSQLServer\MSSQLServer'
                            , N'NumErrorLogs'
                            , REG_DWORD
                            , 12;
END;

-- augmenter la taille de l'historique de l'agent
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = 100000
                                       , @jobhistory_max_rows_per_job = 10000;

Best practices N° 2 : configurer database mail, les notifications et les alertes

Arian Papillon — Sat, 23 Sep 2023 10:35:00 +0100

Etre notifié par mail en cas de problème, c'est indispensable !

Ici, nous configurons Database Mail, étape importante des best practices après l'installation d'une instance SQL Server.

Tant qu'à faire, simplifions l'opération en configurant avec un script SQL.

Activer et configurer Database Mail : modifiez les variables en début de script selon votre environnement et exécutez le script :

DECLARE @server_email_address NVARCHAR(128) = N'myserver.mydomain.com' -- notifications : sender email address
      , @mailserver_name NVARCHAR(128) = N'192.168.0.1'                -- smtp server name or ip address
      , @smtpport INT = 25                                             -- smtp server port
      , @smtpenable_ssl BIT = 0                                        -- smtp server : enable ssl (0|1)
      , @smtpusername NVARCHAR(128) = NULL                             -- smtp server authentication username
      , @smtppassword NVARCHAR(128) = NULL                             -- smtp server authentication password
      , @display_name NVARCHAR(128) = N'SQL Alerting - ' + CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128));

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE WITH OVERRIDE;
IF NOT EXISTS
(
    SELECT *
    FROM msdb.dbo.sysmail_profile
    WHERE name = N'AdminDBMail'
)
    EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'AdminDBMail'
                                          , @description = 'Notification mail profile';
ELSE
    PRINT 'Mail profile already existing, not modified';

IF NOT EXISTS
(
    SELECT *
    FROM msdb.dbo.sysmail_account
    WHERE name = N'SQLAlert'
)
    EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SQLAlert'
                                          , @description = 'SMTP account for SQL Agent Notifications'
                                          , @email_address = @server_email_address
                                          , @display_name = @display_name
                                          , @mailserver_name = @mailserver_name
                                          , @port = @smtpport
                                          , @enable_ssl = @smtpenable_ssl
                                          , @username = @smtpusername
                                          , @password = @smtppassword;
ELSE
    PRINT 'Mail account already existing, not modified';

IF NOT EXISTS
(
    SELECT *
    FROM msdb.dbo.sysmail_profileaccount PA
        JOIN msdb.dbo.sysmail_account A
            ON PA.account_id = A.account_id
        JOIN msdb.dbo.sysmail_profile P
            ON PA.profile_id = P.profile_id
    WHERE A.name = N'SQLAlert'
          AND P.name = 'AdminDBMail'
)
BEGIN
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'AdminDBMail'
                                                 , @account_name = 'SQLAlert'
                                                 , @sequence_number = 1;

    EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 1
                                           , @databasemail_profile = N'AdminDBMail'
                                           , @use_databasemail = 1;
END;
ELSE
    PRINT 'Profile_account already existing, not modified';

Configurons maintenant l'opérateur, activons les notifications sur échec des jobs de maintenance et configurons les alertes indispensables : même principe, modifions les variables de début de script avant de l'exécuter.

DECLARE @operator_name NVARCHAR(128) = N'SQLAdmin'                -- operator name
      , @operator_email NVARCHAR(128) = N'myemail@mydomain.com';    -- operator email

IF NOT EXISTS
(
    SELECT *
    FROM msdb.dbo.sysoperators
    WHERE name = @operator_name
)
    EXEC msdb.dbo.sp_add_operator @name = @operator_name
                                , @enabled = 1
                                , @pager_days = 0
                                , @email_address = @operator_email;
ELSE
    PRINT 'Operator already existing, not modified';

-- Configuration des notifications
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseIntegrityCheck - SYSTEM_DATABASES',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name;
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseIntegrityCheck - USER_DATABASES',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name;
EXEC msdb.dbo.sp_update_job @job_name=N'IndexOptimize - USER_DATABASES',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name;
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - SYSTEM_DATABASES - FULL',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - USER_DATABASES - FULL',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - USER_DATABASES - DIFF',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name
EXEC msdb.dbo.sp_update_job @job_name=N'DatabaseBackup - USER_DATABASES - LOG',
        @notify_level_email=2,
        @notify_level_page=2,
        @notify_email_operator_name=@operator_name
-- Configuration des alertes
IF NOT EXISTS
(
    SELECT *
    FROM msdb.dbo.sysoperators
    WHERE name = @operator_name
)
    EXEC msdb.dbo.sp_add_operator @name = @operator_name
                                , @enabled = 1
                                , @pager_days = 0
                                , @email_address = @operator_email;
ELSE
    PRINT 'Operator already existing, not modified';

-- Configure alerts
DECLARE @sev INT = 19;
DECLARE @alert_name sysname;
WHILE @sev <= 25
BEGIN
    SET @alert_name = @@SERVERNAME + ' Alert - ' + 'Severity level ' + CAST(@sev AS VARCHAR);
    IF NOT EXISTS (SELECT * FROM msdb.dbo.sysalerts WHERE name = @alert_name)
    BEGIN
        EXEC msdb.dbo.sp_add_alert @name = @alert_name
                                 , @severity = @sev
                                 , @delay_between_responses = 600     --10 minutes
                                 , @include_event_description_in = 1; --Email
        EXEC msdb.dbo.sp_add_notification @alert_name = @alert_name
                                        , @operator_name = @operator_name
                                        , @notification_method = 1;
    END;
    ELSE
        PRINT @alert_name + ' : alert already existing, not modified';
    SET @sev = @sev + 1;
END;

Best practices N° 1 : tâches de maintenance

Arian Papillon — Fri, 22 Sep 2023 09:40:00 +0100

A l'installation d'une instance SQL Server, il y a au minimum quelques "best practices" à appliquer.
Cette série d'articles décrit les principales configurations indispensables et les moyens de les mettre en place avec de simples requêtes SQL : tâches de maintenance, alertes, notifications par email, configuration de l'instance, ...

Nous commençons ici par la configuration des tâches de maintenance.

Mettre en place les tâches de maintenance : backup, check d'intégrité, optimisation d'index.

Pour cela les scripts du MVP suédois Ola Hallengren sont les meilleurs (et bien plus puissants que les plans de maintenance intégrés à Management Studio). Ils sont utilisés et recommandés quasi unanimement par la communauté d’experts et MVP renommés : Jonathan Kehayias (SQLSkills), Brent Ozar (brentozar.com), Paul Randall (SQLSkills), Benjamin Nevarez (SQLSkills), Tibor Karaszi, etc…

Ils sont régulièrement maintenus par leur auteur depuis janvier 2008 et prennent en charge les versions de SQL Server 2005 à 2022 (non, pas 2000… !)

Le script crée seulement 4 procédures stockées qui gèrent l’ensemble des tâches de maintenance, avec nombre d’options utiles entièrement documentées.

DatabaseBackup : backups de tous types
DatabaseIntegrityCheck : intégrité
IndexOptimize : index et statistiques
CommandExecute : procédure de lancement des commandes

Comment faire :

Téléchargez le script SQL sur https://ola.hallengren.com/scripts/MaintenanceSolution.sql
Modifiez les quelques paramètres à votre convenance au début du script :

USE [master] -- Specify the database in which the objects will be created.

SET NOCOUNT ON

DECLARE @CreateJobs nvarchar(max)          = 'Y'         -- Specify whether jobs should be created.
DECLARE @BackupDirectory nvarchar(max)     = NULL        -- Specify the backup root directory. If no directory is specified, the default backup directory is used.
DECLARE @CleanupTime int                   = NULL        -- Time in hours, after which backup files are deleted. If no time is specified, then no backup files are deleted.
DECLARE @OutputFileDirectory nvarchar(max) = NULL        -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.
DECLARE @LogToTable nvarchar(max)          = 'Y'         -- Log commands to a table.

Base de données où créer les objets de maintenance : personnellement, je préfère remplacer [master] par [msdb]
@CreateJobs : par défaut, des travaux de l'agent SQL Server seront créés et vous n'aurez plus qu'à les planifier
@BackupDirectory : si non renseigné, les sauvegardes sont créées dans le dossier de backup par défaut, sinon indiquer le chemin de remplacement
@CleanupTime : indiquer en heures la rétention des sauvegardes sur le disque
@OutputFileDirectory : si non renseigné, les tâches créeront des rapports (fichiers texte) dans le dossier log par défaut, sinon indiquer le chemin de remplacement
@LogToTable : par défaut, toutes les opérations effectuées seront journalisées dans une table nommée CommandLog

Exécutez le script (en tant qu'administrateur SQL Server, bien sûr).
Vous n'avez plus qu'à planifier chacun des 11 travaux de maintenance :

DatabaseIntegrityCheck - SYSTEM_DATABASES
DatabaseIntegrityCheck - USER_DATABASES
IndexOptimize - USER_DATABASES
DatabaseBackup - SYSTEM_DATABASES - FULL
DatabaseBackup - USER_DATABASES - FULL
DatabaseBackup - USER_DATABASES - DIFF
DatabaseBackup - USER_DATABASES - LOG
CommandLog Cleanup
Output File Cleanup
sp_delete_backuphistory
sp_purge_jobhistory

Pourquoi pas planifier tout cela avec des commandes SQL ? Par exemple (modifiez selon votre convenance) :-- Integrity Check - SYSTEM_DATABASES : 23:00

DECLARE @schedule_id INT
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'DatabaseIntegrityCheck - SYSTEM_DATABASES')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DatabaseIntegrityCheck - SYSTEM_DATABASES', @name=N'DatabaseIntegrityCheck - SYSTEM_DATABASES',
        @enabled=1,
        @freq_type=4, -- Daily
        @freq_interval=1, -- Once
        @freq_subday_type=1, -- at specified time
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20240101,
        @active_end_date=99991231,
        @active_start_time=230000,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

-- Integrity Check - USER_DATABASES : 23:00
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'DatabaseIntegrityCheck - USER_DATABASES')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DatabaseIntegrityCheck - USER_DATABASES', @name=N'DatabaseIntegrityCheck - USER_DATABASES',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20210101,
        @active_end_date=99991231,
        @active_start_time=230500,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

-- Index Optimize : 00:00
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'IndexOptimize - USER_DATABASES')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'IndexOptimize - USER_DATABASES', @name=N'IndexOptimize - USER_DATABASES',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20210101,
        @active_end_date=99991231,
        @active_start_time=000000,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

-- Backups Full SYSTEM_DATABASES : 01:00
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'DatabaseBackup - SYSTEM_DATABASES - FULL')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DatabaseBackup - SYSTEM_DATABASES - FULL', @name=N'DatabaseBackup - SYSTEM_DATABASES - FULL',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20220207,
        @active_end_date=99991231,
        @active_start_time=010000,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

-- Backups Full USER_DATABASES : 01:00
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'DatabaseBackup - USER_DATABASES - FULL')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DatabaseBackup - USER_DATABASES - FULL', @name=N'DatabaseBackup - USER_DATABASES - FULL',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20220207,
        @active_end_date=99991231,
        @active_start_time=010500,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT


-- Backups differential - USER_DATABASES : 12:30
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'DatabaseBackup - USER_DATABASES - DIFF')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DatabaseBackup - USER_DATABASES - DIFF', @name=N'DatabaseBackup - USER_DATABASES - DIFF',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20220207,
        @active_end_date=99991231,
        @active_start_time=123000,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT

-- Backups log - USER_DATABASES : hourly between 5:15 and 1:00
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'DatabaseBackup - USER_DATABASES - LOG')
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'DatabaseBackup - USER_DATABASES - LOG', @name=N'DatabaseBackup - USER_DATABASES - LOG',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=8,
        @freq_subday_interval=1,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20220207,
        @active_end_date=99991231,
        @active_start_time=051500,
        @active_end_time=010000, @schedule_id = @schedule_id OUTPUT

-- All Cleanup tasks : Daily 5:30
IF NOT EXISTS (SELECT * FROM msdb..sysschedules WHERE name = N'Maintenance History Cleanup')
BEGIN
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'CommandLog Cleanup', @name=N'Maintenance History Cleanup',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20210101,
        @active_end_date=99991231,
        @active_start_time=053000,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
EXEC msdb.dbo.sp_attach_schedule @job_name=N'Output File Cleanup',@schedule_id=@schedule_id
EXEC msdb.dbo.sp_attach_schedule @job_name=N'sp_delete_backuphistory',@schedule_id=@schedule_id
EXEC msdb.dbo.sp_attach_schedule @job_name=N'sp_purge_jobhistory',@schedule_id=@schedule_id
END

N'oubliez pas les correctifs

Arian Papillon — Sat, 08 Jul 2023 17:46:00 +0100

On ne le dira jamais assez, il faut installer les correctifs sur vos instances SQL Server.

Microsoft a enfin mis en ligne une page tenue à jour qui recense les correctifs pour toutes les versions de SQL Server, de la version 6.5 (hé oui !) à la version 2022.

Mais bien sûr, si vous utilisez SQL Server 2012 (fin du support étendu le 12 juillet 2023, on y est) ou une version encore plus ancienne, envisagez à court terme la mise à jour vers une version encore supportée par Microsoft.

Pour installer un correctif, pour ma part, j'attends en général une quinzaine de jours avant de l'appliquer, histoire d'éviter des régressions imprévues (il y a même eu des retraits de correctifs en catastrophe dans le passé, quelques jours après leur sortie).

En ce qui concerne SQL Server 2022 par exemple, le correctif Cumulative Update 4 avait quelques bugs qui pourraient se révéler gênants.

En voilà un, heureusement corrigé depuis par le Cumulative Update 5 :

-- Créons une table avec un  index descendant
DECLARE @T table (i integer NOT NULL INDEX ii (i DESC));
INSERT @T (i) VALUES (1), (2), (3);

-- Cette requête renvoie les données en ordre descendant !
SELECT Ascending = T.i
FROM @T AS T
WHERE T.i IN (1, 2, 3)
ORDER BY T.i ASC; -- Mais nous avions demandé un ordre ascendant

-- Cette requête renvoie les données en ordre ascendant !
SELECT Descending = T.i
FROM @T AS T
WHERE T.i IN (1, 2, 3)
ORDER BY T.i DESC; -- Mais nous avions demandé un ordre descendant

-- CU 4 : L'ordre est incorrect avec un index descendant et un prédicat IN sur cette clé

Datafrogs Conférence : Recherche Full-Text

Arian Papillon — Sat, 18 Mar 2023 18:12:00 +0000

Vous trouverez ici les diapositives et les démonstration de notre session sur la recherche en texte intégral lors de la conférence Datafrogs de ce 18 mars 2023.

Slides de la présentation

Démonstrations

Les index filtrés

Arian Papillon — Thu, 02 Mar 2023 10:27:00 +0000

Depuis SQL Server 2008, il est possible de créer des index filtrés : des index nonclustered avec un prédicat WHERE, qui ne vont indexer qu'une partie de la table.

Bien que peu utilisés (je le constate souvent dans les bases que j'examine), l'utilisation des index filtrés peut nous aider à résoudre certaines problématiques de performances et peuvent réduire le volume de stockage.

Il faut cependant bien comprendre quelles sont les limites à leur utilisation : dans beaucoup de cas, les index filtrés que j'ai rencontré étaient mal utilisés !

Un index filtré est un index avec un prédicat de filtre : une cause WHERE ! Une partie des lignes seulement est donc indexée : l'index est "partiel", sa taille est réduite. Cela permettra d'améliorer les performances de certaines requêtes, consommera moins d'espace de stockage qu'un index "complet", et cette taille réduite permettra de limiter les coûts de maintenance.

Comment créer un index filtré

En ajoutant simplement une clause WHERE lors de la création de l'index, par exemple :

CREATE NONCLUSTERED INDEX ix$CommandesActives ON IDClient WHERE CommandeActive = 1

Seuls sont supportés les opérateurs de comparaison simples : =,<,<=,>,>=,<>.
Vous pouvez assembler plusieurs conditions avec AND, mais vous n'avez pas le droit au OR ou NOT, ni au LIKE ou aux fonctions dans le prédicat de filtre. Et vous ne pouvez pas non plus créer un index filtré sur une colonne calculée ou sur une vue indexée. Le filtre ne peut pas non plus être appliqué à une clé primaire ou une contrainte unique.

Chose importante : pour qu'un index filtré puisse être créé ou mis à jour, certaines options SET de la connexion doivent être correctement positionnées. C'est le cas par défaut dans SQL Server Management Studio, mais pas forcément dans votre application cliente : si les options requises ne sont pas toutes positionnées correctement, les mises à jour échoueront !

Échec de UPDATE car les options SET suivantes comportent des paramètres incorrects...

Doivent être activés : ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER.
Doit être désactivé : NUMERIC_ROUNDABORT.

Index filtrés et performances

L'index filtré est utile pour les requêtes qui effectuent des sélection dans un sous-ensemble de données bien défini (celui du filtre de l'index), surtout si il y a besoin de parcourir l'index en entier (index scan) ou en partie : l'index étant beaucoup plus petit, le parcours nécessite moins de lectures et est donc plus rapide.

C'est surtout utile lorsqu'une colonne n'a que quelques valeurs pertinentes, inégalement distribuées. Par exemple, ma grosse table des commandes a une colonne CommandeActive : la plus grande partie des commandes de ma table sont déjà livrées et terminées (CommandeActive=0) et je n'ai qu'un faible sous-ensemble de commandes actuellement actives (CommandeActive=1).

Si j'indexe ma table des commandes avec un index filtré qui filtre sur WHERE CommandeActive = 1, certaines requêtes, celles qui ciblent uniquement des commandes actives (et qui le précisent bien dans le prédicat de recherche) pourront être améliorées. Non, pas toutes les requêtes, dans certains cas un autre index existant (ou un parcours de table) peut être plus efficace, et l'optimiseur choisit !

Autre avantage de l'index filtré : des statistiques filtrées seront de fait créées, améliorant l'estimation des cardinalités !

Les limites à l'utilisation d'un index filtré : attention !

Tout d'abord, pour qu'un index filtré puisse être choisi par l'optimiseur dans un plan d'exécution, il est indispensable que le prédicat de recherche correspondant au filtre soit exprimé dans la requête SELECT (ou UPDATE ou DELETE). Par exemple :

SELECT ID, ClientID, DateCommande FROM Commande WHERE CommandeActive = 1

Mais il faut savoir que le prédicat de recherche doit utiliser une constante, et surtout pas une variable. La requête suivante sera incapable d'utiliser l'index filtré :

SELECT ID, ClientID, DateCommande FROM Commande WHERE CommandeActive = @VarActive

Dans ce cas on pourra trouver un avertissement dans le plan d'exécution : "Unmatched Index" (à regarder dans les propriétés du plan d'exécution). Cela signifie qu'un index existe bien, mais il ne peut pas être utilisé pour résourdre la requête. En effet, lorsqu'on utilise une variable dans un prédicat de recherche, l'optimiseur ne connait pas la valeur au moment de la compilation du plan.

Il est donc parfois nécessaire de "doubler" l'index filtré par sa version non filtrée pour parer à différents cas de figure !

Et les NULLS ?

L'index filtré peut aussi nous permettre de contourner une problématique absurde propre à SQL Server : lorsqu'on crée un index unique sur une colonne, SQL Server traite le NULL comme une valeur et ne nous permet d'en insérer qu'un seul !

L'index filtré peut nous permettre de parer à cela, comme dans l'exemple suivant :

CREATE UNIQUE INDEX ux$UniqueNameAllowNulls ON dbo.Persons (UniqueName) WHERE UniqueName IS NOT NULL

SQL Server 2022 : Database Ledger

Arian Papillon — Mon, 27 Feb 2023 09:16:00 +0000

Un petit webcast sur Database Ledger, nouvelle fonctionnalité de sécurité de SQL Server 2022. Basé sur la technologie de la blockchain, cela permet d'assurer la traçabilité des modifications et de garantir l'intégrité des données, pour des bases de données à haute sécurité. Dans la démo, j'ai joué au vilain pirate pour altérer ma base de données...

SQL Server 2022 : bug Filestream

Arian Papillon — Thu, 23 Feb 2023 09:20:00 +0000

En construisant mes démos, je suis tombé sur ce bug : lorsqu'on tente d'ajouter un filegroup filestream à une base de données, on obtient le message d'erreur suivant.

Msg 35221, Level 16, State 1, Line 2

Could not process the operation. Always On Availability Groups replica manager is disabled on this instance of SQL Server. Enable Always On Availability Groups, by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry the currently operation. For information about how to enable and disable Always On Availability Groups, see SQL Server Books Online.

Cela n'a bien entendu aucun rapport avec Always On...

Ce problème (entre autres) est corrigé avec le Cumulative Update #1, qu'il est donc urgent de télécharger ici.

Question : Random values

Arian Papillon — Wed, 08 Feb 2023 16:37:00 +0000

Question : Aurais-tu une technique pour qu’au sein d’un SELECT, la fonction Rand() change de valeur sur chaque ligne ? Car malheureusement, elle garde la même valeur sur chaque ligne...

Réponse : effectivement, pour un UPDATE SET col = RAND(), la fonction n'est effectuée qu'une seule fois et la valeur sera la même pour toutes les lignes. Mais il existe une solution, avec la création d'une vue et d'une fonction, qui forcera la fonction à s'exécuter pour chaque ligne.

-- Création d'une vue
CREATE VIEW [dbo].[V_RAND] AS
SELECT RAND() AS RAND  
GO
-- Création d'une fonction
CREATE FUNCTION [dbo].[F_GET_RAND] ()
    RETURNS FLOAT
    AS
    BEGIN
      RETURN (SELECT RAND FROM V_RAND)
    END;
GO

UPDATE MATABLE SET MACOLONNEFLOAT = dbo.F_GET_RAND()

Installations rapides

Arian Papillon — Sun, 20 Nov 2022 13:01:00 +0000

Si comme moi vous déployez fréquemment des instances SQL Server pour des tests divers et que vous êtes pressé, le plus rapide est de le faire avec un fichier de configuration.

Dans cet article, un exemple de fichier de configuration compact pour une installation simple de SQL Server 2022.

Voici un exemple de fichier de configuration minimal pour une installation simple et rapide (uniquement le moteur de bases de données, instance par défaut). Nommez le fichier avec l'extension .ini.

A vous de changer :

Le nom et l'ID de l'instance si c'est une instance nommée
La collation par défaut
Les comptes de service et passwords
Les chemins

Ensuite, il suffit de lancer l'installation avec le setup de SQL Server et le paramètre configurationfile, par exemple :

d:\setup.exe /configurationfile=c:\temp\SQLInstall.ini

;SQL Server 2022 INI Installation Configuration File
[OPTIONS]
;Change installation parameters below
;SQL Server instance name
INSTANCENAME="MSSQLSERVER"
INSTANCEID="MSSQLSERVER"
;Default collation
SQLCOLLATION="French_CI_AS"
;Service domain accounts and passwords (SQL Server and SQL Agent)
SQLSVCACCOUNT="MyDomain\SQLSvc"
SQLSVCPASSWORD="MyP@ssw0rd"
AGTSVCACCOUNT="MyDomain\SQLSvc"
AGTSVCPASSWORD="MyP@ssw0rd"
SQLSYSADMINACCOUNTS="MyDomain\SQLAdmins"
;Security mode and password of sa account
SECURITYMODE="SQL"
SAPWD="MyP@ssw0rd"
;Path configurations
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
SQLUSERDBDIR="D:\MSSQLData\MSSQL16.MSSQLSERVER\MSSQL\Data"
SQLUSERDBLOGDIR="L:\MSSQLData\MSSQL16.MSSQLSERVER\MSSQL\Data"
SQLTEMPDBDIR="T:\MSSQLData\MSSQL16.MSSQLSERVER\MSSQL\Data"
SQLTEMPDBLOGDIR="T:\MSSQLData\MSSQL16.MSSQLSERVER\MSSQL\Data"
SQLBACKUPDIR="S:\MSSQLData\MSSQL16.MSSQLSERVER\MSSQL\Backup"

;For simple installation, no changes are needed here
ACTION="Install"
FEATURES=SQLENGINE
QUIET="False"
QUIETSIMPLE="True"
IACCEPTSQLSERVERLICENSETERMS="True"
SUPPRESSPRIVACYSTATEMENTNOTICE="True"
SUPPRESSPAIDEDITIONNOTICE="True"
SQLSVCSTARTUPTYPE="Automatic"
AGTSVCSTARTUPTYPE="Automatic"
SQLSVCINSTANTFILEINIT="True"
TCPENABLED="1"
NPENABLED="0"