Lorsqu'une instance est créé dans un serveur SQL, l'authentification des utilisateurs peut être configurée de 2 façons différentes :

• Windows Authentication - les comptes de la machine et/ou du domaine AD peuvent être utilisés pour se connecter avec différents niveaux de privilèges
• Mixed mode - l'authentification Windows ainsi que l'authentification SQL (accès avec des comptes locaux à l'instance) peuvent être utilisé

Lorsque Windows Authentication est choisi, le compte local à l'instance appelé sa qui est membre du groupe sysadmin est désactivé.

sa (system administrator) est un compte utilisateur par défaut local à l'instance SQL dont le mot de passe n'est pas connu

sysadmin est un groupe par défaut local à l'instance SQL dont les membres ont les permissions les plus élevées sur l'instance SQL

Dans l'éventualité ou la personne qui a créé l'instance n'est pas disponible et qu'elle n'a pas configuré ou communiqué des accès de secours, par exemple, mixed mode ou ajout d'autres IT en tant que sysadmin, les accès administrateurs à l'instance SQL semblent impossible.

Les prérequis pour récupérer les permissions sysadmin

• Être administrateur du serveur sur lequel le serveur SQL est installé
• Avoir SQL Server Managmement Studio (SSMS) installé sur le serveur
• Si l'instance concerne un environnement de production, avertir que lors de la récupération des accès, l'instance SQL sera indisponible

Récupérer les permissions sysadmin

1. Se connecter sur le serveur sur lequel le serveur SQL est installé

2. Ouvrir la console SQL Server Configuration Manager

3. Faire un clic droit sur l'instance SQL dont l'accès sysadmin doit être récupéré et cliquer sur Properties

4. Ouvrir l'onglet Startup Parameters, dans Specify a startup parameters saisir -m puis cliquer sur Add, le paramètre -m permet de mettre l'instance SQL en mode maintenance, un utilisateur qui est admin du serveur et qui se connecte à l'instance SQL via SSMS sera, dans ce mode, également sysadmin. Ce mode n'autorise qu'un seul utilisateur à se connecter à la fois.

5. Redémarrer l'instance SQL, dont l'accès sysadmin doit être récupéré, via la console SQL Server Configuration Manager en faisant un clic droit sur l'instance puis choisir Restart

6. Depuis la console SQL Server Configuration Manager, vérifier que le service SQL Server Agent n'est pas démarré, si c'est le cas l'arrêter, auquel cas il utilisera la seule connecxion disponible pour se connecter à l'instance SQL

7. Ouvrir la console SQL Server management Studio en tant qu'administrateur

8. Se connecter à l'instance SQL dont l'accès sysadmin doit être récupéré avec comme méthode d'authentification Windows Authentication

9. Dans l'arborescence de l'instance SQL, déplier le dossier Security puis faire un clic droit sur Logins et clique sur New login...

10. Dans l'onglet General, dans le champs Login name choisir le compte utilisateur ou le groupe qui sera sysadmin, choisir Windows Authentication

11. Aller dans l'onglet Server Roles et cocher sysadmin puis clique sur OK 

12. Le nouvel accès apparaît dans la liste des Login

13. Fermer SSMS

14. Dans SQL Server Configuration Manager retirer le paramètre -m de l'instance SQL dont les accès sysadmin ont été récupéré, en sélectionnant -m et en cliquant sur Remove

15. Depuis la console SQL Server Configuration Manager redémarrer l'instance SQL dont l'accès sysadmin a été récupéré

Contexte

Il arrive souvent qu’un compte utilisateur soit utilisé pour l’exécution d’un job SQL, il s’agit d’une mauvaise pratique car le compte utilisateur peut être amené à changer de mot de passe ce qui provoqueras une erreur d’exécution des jobs en question.

Ce post explique comment détecter puis modifier les comptes utilisés.

Analyse

Le script suivant permet de détecter les jobs exécuté via un compte user (DOMAIN\USER-%) tout en ne remontant pas les jobs lancés par un compte de service (DOMAIN\SERVICE-%).

Les variables “DOMAIN\USER-%” et “DOMAIN\SERVICE-%” sont à modifier en fonction de la nomenclature utilisée pour nommer les comptes. Le “%” correspond à toute chaîne de zéro caractères ou plus Ce caractère générique peut être utilisé comme préfixe ou comme suffixe.

select s.name,l.name
from  msdb..sysjobs s
  left join master.sys.syslogins l on s.owner_sid = l.sid
where l.name like 'DOMAIN\USER-%' and  l.name not like 'DOMAIN\SERVICE-%'

Résolution

Il faut ensuite récupérer le SID du compte utilisateur à changer depuis l’active directory, ainsi que le SID du compte à utiliser, par exemple, SA.

Pour récupérer le SID d’un utilisateur SQL, executer la commande suivante “select sid,name,denylogin from MASTER..syslogins where name='SQLUser'”

Le script suivant permet de modifier l’owner des jobs d’une instance SQL depuis le SID “0x02” vers le SID “0x01” :

UPDATE msdb..sysjobs
SET owner_sid = CONVERT(varbinary(max), '0x01', 1)
where owner_sid = CONVERT(varbinary(max), '0x02', 1)

Introduction

Dans mon blog précédent  , j'ai expliqué comment lancer une instance SQL 2017 installée sur une image docker (linux) depuis Docker installé sous Windows. Dans ce blog, je vais détailler l'installation d'une instance SQL Server 2017 sur Linux. Microsoft SQL Server 2017 supporte les versions d'OS suivants:

• Red Hat Enterprise Linux (7.3 or 7.4)
• SUSE Linux Enterprise Server (v12 SP2)
• Ubuntu (16.04)
• Docker Engine (1.8+)

Attention: l'installation de sql server sur linux nécessite de 2 Gb de ram minimum et de la version de NFS 4.2 ou supérieur.

Installation de SQL Server 2017

Mise à jour de l'os

Avant de démarrer l'installation de SQL Server 2017, assurez vous que Ubuntu est bien à jour en exécutant les deux commandes suivantes:

sudo apt-get update
sudo apt-get upgrade

Installation de SQL Server 2017

Importer les clés GDP:

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

Enregistrer la repository Ubuntu de Microsoft SQL Server:

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"

Exécuter les commandes suivantes pour l'installation de SQL Server 2017:

sudo apt-get update
sudo apt-get install -y mssql-server

A la fin de l'installation, exécuter la commande suivante pour configurer SQL Server:

sudo /opt/mssql/bin/mssql-conf setup

Entrer 1

Accepter les conditions d'utilisations et entrer le mot de passe SA.

L'installation et la configuration de SQL Server 2017 est finie:

Vérification du service SQL Server

Lancer la commande suivante:

systemctl status mssql-server

On constate que le service est bien démarré:

Tests

Utiliser SSMS pour se connecter à ce serveur:

 Exécuter la commande sql ci-dessus pour voir la version de SQL server qui a été installée.

Introduction :

SQL Server est conçu pour optimiser l’exécution des requêtes de manière transparente, l’une des méthodes utilisé par SQL est le calcul parallélisé de requêtes.

Cette méthode d’optimisation consiste à diviser l’exécution d’une requête à travers threads afin d’accélérer l’exécution de la requête.

Ce post composé de deux parties à pour objectif de présenter les paramètres suivants :

• MAXDOP (partie 1/2),
• Cost Threshold for Parallelism (partie 2/2)

Explication :

Le paramètre MAXDOP signifie “Max Degree of Parallelism”, ce paramètre permet de maitriser le nombre maximum de processeurs pouvant intervenir dans l’exécution d’une requête parallélisée, il est important de comprendre que ce paramètre ne peut être utilisé pour limiter le nombre de processeurs utilisés par SQL.

Ce lien liste les valeur recommandées par Microsoft afin de paramétrer cette valeur en fonction des versions de SQL Serveur, ce lien peut également être utilisé pour calculer la valeur optimale à positionner. Il est bien entendu nécessaire de tester les différentes configurations en fonction de la charge soumise par l’application qui utilise le serveur SQL.

Réalisation

Le paramètre MAXDOP peut être modifié depuis les propriétés de l’instance SQL concernée dans la partie Advanced->Parallelism :

Ou via la requête suivante (en remplaçant VALEUR par la valeur désirée) :

La modification du paramètre MAXDOP est prise en compte sans qu’il n’y a à redémarrerredemmarer l’instance.

Sources :

• https://support.microsoft.com/en-gb/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-configuration-option-in-sql-server
• http://www.sqlservercentral.com/blogs/matthew-mcgiffen-dba/2017/05/26/tuning-parallelism-on-sql-server/
• https://blogs.msdn.microsoft.com/sqlsakthi/p/maxdop-calculator-sqlserver/

Introduction :

Comme vu dans le post précèdent, SQL Server dans sa configuration par défaut optimise automatiquement l’exécution des requêtes, notamment en répartissant son exécution à travers différents threads.

Ce post composé de deux parties à pour objectif de présenter les paramètres suivants :

• MAXDOP (partie 1/2),
• Cost Threshold for Parallelism (partie 2/2)

La partie 2/2 de ce post auras pour objectif de configurer SQL afin que seules les requêtes considérées comme volumineuses ne soient traitées en parallèle.

Explication :

Le paramètre “Count Threshold for Parallelism” permet à SQL de ne paralléliser que les requêtes dont le coût d’exécution d’un plan en série (mesuré en secondes) serait plus élevé que la valeur définie.

L’optimisation réalisée par SQL, bien qu’efficace pour les requêtes “volumineuses”, peut se révéler désastreuse pour l’exécution concurrente d’un grand nombre de petites requêtes, il est donc judicieux de revoir la valeur de ce paramètre en fonction du type de charge exercée sur le serveur SQL.

En règle générale, une valeur comprise entre 20 et 50 est un bon début qui doit être affiné par des tests spécifiques à l’application.

Par défaut, la valeur du paramètre “Count Threshold for Parallelism” est de 5, cette valeur provient du temps nécessaire à l’exécution d’une requête sur le poste de “Nick” un employé de Microsoft membre de l’équipe Query Optimizer lorsque le Query Optimizer était en cours de développement pour SQL Server 7.

Nick devait – entre autre – développer la méthode de calcul du coût d’une requête, il a donc utilisé son ordinateur comme étalon, depuis, le coût d’exécution d’une requête estimé par SQL est….le temps d’exécution qu'aurais pris la machine de Nick.

Bien entendu, les machines utilisées ne sont plus les mêmes et les temps d’exécutions sont donc plus courts :

La machine de Nick.

SQL Server ignore la valeur de l'option cost threshold for parallelism dans les cas suivants :

• L'ordinateur ne dispose que d'un seul processeur.
• Un seul UC est disponible pour SQL Server en raison de l'option de configuration affinity mask.
• L'option max degree of parallelism a la valeur 1.

Réalisation

Le paramètre Count Threshold for Parallelism peut être modifié depuis les propriétés de l’instance SQL concernée dans la partie Advanced->Parallelism :

Ou via la requête suivante (en remplaçant VALEUR par la valeur désirée) :

La modification du paramètre Count Threshold for Parallelism est prise en compte sans qu’il n’y a à redémarrer l’instance.

Sources :

• https://technet.microsoft.com/fr-fr/library/ms188603(v=sql.105).aspx
• http://www.sqlservercentral.com/blogs/matthew-mcgiffen-dba/2017/05/26/tuning-parallelism-on-sql-server/
• http://blogs.microsoft.co.il/dannyr/2008/10/09/what-estimated-subtree-cost-1-means-or-a-great-sql-server-history-story/

Introduction

Le rapport de découverte SQL Server est utile afin de lister toutes les informations relatives à aux composants installés sur votre serveur SQL, leur version et leur édition.

L’objectif de ce post est de générer un rapport en mode GUI et en via un script.

Réalisation

• En mode GUI :

Exécuter “SQL Server Installation Center :

Aller dans l’onglet “Tool” et cliquer sur “Installed SQL Server features discovery report” :

Le rapport de découverte SQL Server est enregistré dans “C:\Program Files\Microsoft SQL Server\VERSION\Setup Bootstrap\Log\AAAAMMDD_XXXXX”

• En mode script :

Depuis un invite de commande, exécuter la commande suivante: “Setup.exe /q /Action=RunDiscovery” depuis le répertoire “C:\Program Files\Microsoft SQL Server\VERSION\Setup Bootstrap\SQLServer2012”

Sources

• https://msdn.microsoft.com/fr-fr/library/bb510455.aspx

Contexte

Lors de la suppression d’un des fichiers utilisé par la base TempDB, l’opération échoue avec l’erreur suivante :

Msg 5042, Level 16, State 1, Line 1
The file 'tempdev2' cannot be removed because it is not empty.

Explication

Ce comportement est provoqué par le fait que le fichier est actuellement utilisé par SQL, il faut donc vider le fichier avant de pouvoir le supprimer.

Une solution possible est le redemmarage de l’instance SQL, ce qui va regenerer la base TempDB, moyennant une coupure de service.

Résolution

Afin d’éviter toute coupure de service, il est possible d’executer le script SQL suivant pour vider puis supprimer un fichier utilisé par la TempDB :

USE [tempdb]
GO
DBCC SHRINKFILE('tempdev2', EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
GO

Sources

https://tenbulls.co.uk/2016/01/13/problem-removing-files-from-tempdb/

Introduction

Après avoir renommé un serveur SQL, les plans de maintenances existants échouent avec l’erreur suivante :

Réalisation

1. Lister les procédures stockées en exécutant le script suivant sur la base MSDB et récupérer la valeur “ID” :

Voici un exemple de résultat :

2. Modifier la valeur “Data Source” à l’aide du script suivant :

Modifier les valeur en rouge :

• OldServerName : Ancien nom de l’instance SQL telle qu’indiquée dans la partie “Data Source” de la colonne “LocalServerConnectionString” de la requête exécutée précédemment,
• NewServerName : Nouveau nom de l’instance SQL,
• ID : GUID de la tâche planifiée à reconfigurée telle qu’indiquée dans la colonne“ID” de la requête exécutée précédemment.

Vérification

• Valider en exécutant la requête listant les procédure stockées que le nom de la nouvelle instance apparaît bien dans la partie “Data Source” de la colonne “LocalServerConnectionString”,
• Exécuter le plan de maintenance et valider qu’il s’exécute correctement.

Liens Utiles

• https://msdn.microsoft.com/en-us/library/ms143799.aspx
• http://serverfault.com/questions/289995/renamed-a-sql-server-however-maintenance-plans-still-have-old-name

Introduction

Afin d’améliorer la sécurité des connexions réalisées depuis les clients SQL vers une base, il est possible d’activer la fonction de chiffrement de SQL (Encryption) depuis SQL Server Configuration Manager.

Cette fonctionnalité permet de chiffrer la connexion entre le client et le serveur afin d’éviter l’interception et la lecture des commandes réalisées par le client.

Prérequis

• Un certificat avec sa clé privée délivré pour l’authentification server d’installé sur le serveur SQL (le certificat doit avoir pour nom le même que celui utilisé dans la source de donnée du client, souvent il s’agit du FQDN du serveur),
• Dans le cas d’une utilisation d’un cluster, le nom du certificat doit être correspondre au nom DNS utilisé par le cluster, le certificat devras être installé sur tous les nœuds du cluster,
• Le compte de service utilisé par le service SQL Server doit avoir les droits Read sur la clé privée du certificat,
• Le client doit être en mesure de valider et de faire confiance à l’autorité ayant délivré le certificat,
• Une fenêtre d’interruption de l’instance SQL.

Réalisation

Verification

Un test réalisé à l’aide d’un outil de capture réseau, et d’un client SQL permet de prouver le chiffrement des connexions :

• Avant

• Après

Introduction

Ce post est composé de deux parties :

• Recommandations sur l’emplacement et la taille de TempDB (Partie 1)
• Répartition de la base TempDB sur plusieurs fichiers (Partie 2)

Présentation de la base TempDB :

La base TempDB est une base temporaire régénérée à chaque démarrage du serveur SQL, cette base est accessible à tous les utilisateurs (il est possible de retirer l’accès à cette base aux utilisateurs, mais cela n’est pas conseillé car la base TempDB est utilisé dans des opérations de routines).

Cette base, de par sa nature temporaire, ne permet pas les actions d’administrations suivantes : Sauvegarde/Restauration, Changement du “Recovery Model”.

Dans cette première partie nous verrons comment déplacer la base TempDB vers un stockage dédié.

Recommandations sur l’emplacement et la taille de TempDB

Recommandations Générales relatives à l’emplacement des fichiers de bases de données

De manière générale, il est recommandé de séparer les fichiers Data, Logs et temp. L’objectif de cette séparation est de permettre une meilleure séparation de la charge à travers différents disques physiques.

Actuellement, avec l’augmentation des solutions de stockage virtuel, la séparation des fichiers n’est parfois plus nécessaire pour des raisons de performances, néanmoins, séparer les fichiers à travers différents disques logiques permet plus de souplesse dans l’administration.

Dans le cas d’une infrastructure SQL utilisant un stockage partagé, le déplacement de la base TempDB vers un stockage local peut apporter les bénéfices suivants :

• Meilleure performances notamment lors de l’utilisation de disques rapides (SSD),
• Certaines bases TempDB peuvent être très sollicitées entrainant une baisse de performance générale sur stockage partagé.

Certains administrateurs peuvent être tentés de déplacer la base TempDB vers un stockage local, attention toutefois à bien valider les points suivants pour une configuration cluster :

• Utiliser une base TempDB locale dans une configuration Cluster n’est supporté que depuis SQL Server 2012 (les versions précédentes requièrent que toutes les bases soient présentes sur le stockage partagé),
• Utiliser le même emplacement pour TempDB à travers tous les nœuds du cluster, le compte de service SQL Server doit avoir un droit Lecture/Ecriture sur chacun des dossiers.

Recommandations Générales relatives à la taille des fichiers de bases de données

Lors de la création d’une nouvelle base de données, il est possible de préciser les éléments suivants relatifs à la taille de la base de données :

• Taille initiale de la base de données,

La taille initiale de la base de donnée doit correspondre à la taille que la base va occuper à terme, ce afin d’éviter des actions d’expansion de la base (Autogrowth) qui en plus d’être lourdes entraînent une fragmentation des fichiers ainsi qu’une indisponibilité de ceux-ci durant l’expansion.

• Taille maximum de la base de données,

A moins de parfaitement maitriser la taille maximum que la base de données doit avoir, il est préférable de conserver à “Unlimited” la taille maximum d’une base.

• Autogrowth de la base de données

La fonction Autogrowth permet d’automatiquement augmenter la taille des fichiers de la base de données, ce qui permet une administration plus simple. L’opération d’autogrowth est généralement lourde (à moins d’utiliser l’IFI, Instant File Initialization) et comprends plusieurs contraintes.

Il est donc préférable d’éviter qui doit être considéré comme une solution de fortune. L’idéal en l’absence d’informations précises concernant la taille de la base est d’analyser l’évolution de la base en paramétrant un autogrowth fixe en MB, puis après une période d’analyse suffisante, de paramétrer la taille des fichiers utilisés par la base à une valeur légèrement supérieure à celle constatée (cette préconisation s’applique d’autant plus à la base TempDB, celle-ci étant recréée à chaque démarrage de l’instance SQL).

Afin d’estimer la taille à positionner pour l’Autogrowth, il est possible de visualiser la fréquence d’autogrowth d’une base depuis le rapport Disk de celle-ci :

Réalisation

Afin de déplacer la base TempDB, voici les actions à réaliser :

Verification