PI Services

Le blog des collaborateurs de PI Services

SQL Server – Envoyer par email le résultat d’une requête

27. décembre 2016 Hatem-DJELASSI (0)

Introduction

L’une des requêtes qui peut être demandé à un adminsitrateur SQL est l’execution régulière d’une requête et le transmission de son résultat par email.

Prérequis

Afin de répondre à ce type de demande, les outils suivants seront utiles :

  • La procédure “sp_send_dbmail”,
  • L’agent SQL Server,
  • La configuration d’un profile Database Mail.

Réalisation

Le script suivant permet d’envoyer par mail le résultat de la procédure stockée “usp_StoredProcedure” :

image

Depuis la base MSDB, executer la procedure “sp_send_dbmail” (ligne 1), cette procédure prends les paramètres suivants :

@profile_name : profile mail configuré sur SQL Server sous le dossier “Management”

@recipients : adresse mail du destinataire

@subject : sujet qui figureras dans le mail envoyé

@query : requête à executer

@query_result_width : largeur en nombre de caractère du fichier qui seras envoyé en pièce jointe, par défaut la valeur est de 256 caractères, ce paramètre est important si l’on souhaite réaliser par la suite un import vers excel

@attach_query_result_as_file : utiliser la valeur “1” afin de fournir le resultat de cette requête dans un fichier en pièce jointe

@query_attachment_filename : nom du fichier en pièce jointe

@query_result_separator : caractère de séparation qui seras utilisé

L’article suivant explique plus en détails les paramètres qu’il est possible d’utiliser : https://msdn.microsoft.com/fr-fr/library/ms190307.aspx

SQL Server – Chiffrer les connexions Client/Serveur

2. octobre 2016 Hatem-DJELASSI SQL Server, Sécurité, s (0)

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

1. Depuis SQL Server Configuration Manager, faire un clic-droit sur l’instance souhaitée et sélectionner “Properties”

image

2. Depuis l’onglet certificate, sélectionner le certificat à utiliser pour le chiffrement des connexions,

image

3. Depuis l’onglet Flags, passer la valeur de “ForceEncryption” à ‘'Yes”,

image

4. Rebooter l’instance.

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

image

  • Après

image

SQL Server – Configuration de la base TempDB (Partie 1/2)

2. octobre 2016 Hatem-DJELASSI SQL Server, sql (0)

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 :

clip_image001

Réalisation

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

1. Récupérer l’emplacement actuel de la base

Use master
GO
SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

clip_image002[5]

2. Déplacer les fichiers utilisés par TempDB (attention à veiller à ce que le compte du service SQL Server y ait un droit de Lecture/Ecriture)

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf');
GO

3. Redémarrer le service SQL Server

Verification

Vérifier le nouvel emplacement des fichiers TempDB :

Use master
GO
SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

SQL Server – Configuration de la base TempDB (Partie 2/2)

2. octobre 2016 Hatem-DJELASSI SQL Server, SQL (0)

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)

Dans cette seconde partie nous verrons comment repartir la base TempDB à travers plusieurs fichiers comme Microsoft le recommande.

Recommendation

Bien que Microsoft recommande de répartie la base TempDB à travers autant de fichiers que de processeurs logiques présents sur le serveur et ce jusqu’à 8 fichiers, dans le cas d’un serveur avec plus de 8 CPU, si malgré l’utilisation de 8 fichiers la base TempDB souffre toujours de lenteurs, Microsoft préconise d’augmenter le nombre de fichiers par un multiple de 4.

Différents membres de la communauté SQL s’accordent à penser qu’au-delà de 8 fichiers, le gain en performance seras négligeable par rapport à l’effort d’administration.

Réalisation

1. Récupérer le nombre de CPU logique du serveur SQL

clip_image001

2. Ajouter trois fichiers TempDB sur trois disques différents (attention à veiller à ce que le compte du service SQL server y ait un droit de Lecture/Ecriture), il est préférable d’avoir des options de tailles et d’Autogrowth identiques

ALTER DATABASE tempdb

ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 1024);

ALTER DATABASE tempdb

ADD FILE (NAME = tempdev3, FILENAME = 'X:\tempdb3.mdf', SIZE = 1024);

ALTER DATABASE tempdb

ADD FILE (NAME = tempdev4, FILENAME = 'Y:\tempdb4.mdf', SIZE = 1024);

GO

SQL Server - Utiliser TDE (Transparent Data Encryption) sur SQL Server 2012 pour chiffrer vos bases de données

20. juin 2016 Hatem-DJELASSI (0)

Introduction

Disponible depuis SQL Server 2008 Enterprise TDE permet de chiffrer un base de données de manière totalement transparente pour les application qui s’en appuient. A l’aide de TDM, les fichier .mdf et .ldf ainsi que les sauvegardes sont chiffrées avec les standards AES ou 3DES.

Le chiffrement est réalisé au niveau de la page. La donnée est chiffrée lors de son écriture sur le disque et déchiffrée lors de sa lecture, chiffrer et déchiffrer l’information au niveau de la page permet aux applications clientes de ne pas nécessiter de prérequis.

Dans le cas où la base de donnée est utilisée dans un mode de réplication (AlwaysOn, Mirroring ou Log Shipping), toutes les bases participant à la réplication seront chiffrées.

Attention, TDM chiffre la donnée au niveau de son support physique ce qui permet de la protéger en d’accès au disque contenant la base de donnée, les communications (client/server) ne sont elles pas chiffrées, une connexion SSL client/server doit être utilisée dans ce cas.

Prérequis et commandes utilisées

Réalisation

1.Créer la “Master Key” de la base de donnée

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Password';
GO

Afin d’activer TDE, il est nécessaire de créer une master key. La master key doit être créée depuis la base master.

Microsoft recommande de la clé principale à l'aide de l'instruction BACKUP MASTER KEY et stocker la sauvegarde en lieu sûr.

2. Créer un certificat pour proteger “Master Key” créé précédemment

CREATE CERTIFICATE MyCert
WITH SUBJECT='MyDatabase DEK';
GO

Créer un certificat appelé “MyDatabase” dans l’exemple plus haut.

3. Créer la “Database Encryption Key”

USE MyDatabase
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyCert;
GO

Se positionner sur la base à chiffrer.

Créer la clé utilisée pour protéger la base de donnée, cette clé est appelée DEK (Database Encryption Key), cette clé est sécurisée à l’aide du certificat créé précédemment.

Les algorithmes de chiffrement suivant sont proposés :

  • AES_128
  • AES_192
  • AES_256
  • TRIPLE_DES_3KEY

4. Activer le chiffrement au niveau de la base à chiffrer

ALTER DATABASE MyDatabase
SET ENCRYPTION ON;
GO

Durant le chiffrement la base est toujours accessible le processus de chiffrement étant réalisé en arrière-plan, cependant, des dégradation des performances sont à prévoir.

Vérification

La commande suivante permet de vérifier la progression du chiffrement :

SELECT db_name(database_id),encryption_state,percent_complete,
key_algorithm,key_length
FROM sys.dm_database_encryption_keys

 

Exploitation

En plus des tâches de sauvegarde usuelles, l’utilisation de TDM impose la sauvegarde du certificat utilisé pour le chiffrement de la base. Sans ce certificat, il est impossible d’accéder aux données chiffrée, même depuis une sauvegarde (le chiffrement s’appliquant au niveau de la page).

Le script suivant est un exemple qui présente comment sauvegarder le certificat ainsi que la clé privée (inutile de préciser l’emplacement entre parenthèses dans le switch “WITH PRIVATE KEY” si l’on souhaite exporter le certificat avec sa clé privée) utilisés pour le chiffrement :

-- Use master car la master key est stockée dans la base master
USE Master
GO

BACKUP CERTIFICATE MyCert
TO FILE = 'C:\_cert\MyCert'
WITH PRIVATE KEY (file='C:\_cert\MyCertPrivateKey',
ENCRYPTION BY PASSWORD='Password')

SQL–Restauration et déplacement des bases de données chiffrées par TDM

20. juin 2016 Hatem-DJELASSI (0)

Introduction

Afin de déplacer ou restaurer une base de donnée chiffrée à l’aide de TDE (Transparent Data Encryption), il est nécessaire de restaurer le certificat utilisé pour le chiffrement de la base sur la future instance SQL hôte.

Prérequis et commandes utilisées

Réalisation

Le script suivant permet de créer une master key depuis la base master de l’instance SQL cible :

USE Master
GO

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'Password'

 

Avant de restaurer la base, le certificat (avec la clé privée) provenant de l’instance source doivent être restaurés :

CREATE CERTIFICATE MyCert
FROM FILE='c:\_cert\MyCert'
WITH PRIVATE KEY (
FILE = 'c:\_cert\MyCertPrivateKey',
DECRYPTION BY PASSWORD='Password')

 

La base peut maintenant être restaurée.

Vérification

La commande suivante requête la vue sys.certificates et la vue sys.dm_database_encryption_keys afin de retourner les bases chiffrées (dont l’encryption state est à “3”)

USE master
GO
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;

Upgrade d'Edition SQL Server 2012R2

20. juin 2016 Hatem-DJELASSI (0)

Introduction

Afin de profiter de nouvelles fonctionnalités sur un serveur SQL existant il est souvent possible de passer par l’upgrade d'Edition et ce de manière très rapide avec un temps d’inactivité minime (juste le temps de rebooter les services SQL).

La procédure suivante décrit comment upgrader une version Standard de SQL Server 2012 SP2 en version Enterprise, voici le lien décrivant les chemins supportés d’upgrade : https://msdn.microsoft.com/en-us/library/ms143393.aspx

Réalisation

Monter l'ISO : correspondant à votre version d' et double-cliquer sur “setup.exe” :

clip_image001

Depuis l’onglet maintenance, cliquer sur '”Edition Upgrade” :

clip_image002

Patienter durant la vérification des prérequis :

clip_image005

clip_image006

Une fois les prérequis valider, entrer la clé de la nouvelle licence :

clip_image007

Accepter les termes et licences :

clip_image008

Sélectionner l’instance à upgrader

image

Patienter durant la validation :

clip_image010

Cliquer sur “Upgrade” :

clip_image011

L’upgrade est terminé, cliquer sur “OK” et rebooter si c’est demandé :

image

Vérification

 

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

 

clip_image013

Sources : https://msdn.microsoft.com/en-us/library/cc707783.aspx

SQL Server – Détecter et mapper les utilisateurs orphelins

23. mars 2016 Hatem-DJELASSI (0)

Contexte

L’une des tâches régulièrement exécutées par un DBA est de restaurer ou d’attacher des bases entres différentes instances SQL. Lors de ce type d’opération, il est fréquent de rencontrer des erreurs d’authentification du type :

  • Error 15023 : User already exists in the current database,
  • Error 4064 : Cannot open user default database. Login failed,
  • The database <VOTREBASE> is not accessible. (Object Explorer). A la vue de ces erreurs, un DBA peux être tenté de retirer manuellement les utilisateurs des différentes bases de données pour les ajouter, cette action – bien que fastidieuse – résoudrais le problème décrit, il existe néanmoins des solutions intégrées à SQL pour détecter et mapper les utilisateurs orphelins (orphaned users).
     

    Explication du problème

    Les utilisateurs orphelins apparaissent lorsque le SID d’un utilisateur d’une base de donnée ne correspond pas au SID du même utilisateur qui est renseigné dans la base master.

    Le SID d’un utilisateur apparait donc à deux niveaux :

  • Au niveau instance : dans les vues système '”sys.server_principals” et “sys.syslogins” présentes dans la table Master,
  • Au niveau base de donnée : dans la table système sysusers.

Le SID d’un utilisateur (identifiant unique) sert à mapper un utilisateur d’une base de donnée au login renseigné dans l’instance SQL, donc lors de la restauration d��une base de donnée vers une instance SQL différente, il faut penser à re-mapper les utilisateurs orphelins.

Résolution

Afin de re-mapper les utilisateurs orphelins sans avoir à passer sur chaque utilisateur manuellement, on peux s’aider de sp_change_users, cette commande prends trois paramètres différents :

  • Report pour détecter les utilisateurs orphelins :

USE <YOURDATABASE>
GO
sp_change_users_login @Action='Report'
GO
  • Update_one pour mapper manuellement un utilisateur de la base à un login de l’instance :
    USE <YOURDATABASE>
    GO
    sp_change_users_login @Action='update_one'
    @UserNamePattern='User1'
    @LoginName='User1'
    GO
  • Auto_fix pour mapper automatiquement un utilisateur de la base à un login de l’instance et pour créer le login si absent de l’instance :
    USE <YOURDATABASE>
    GO
    sp_change_users_login @Action='auto_fix' , 'User1', null, 'Password'
    GO

SQL Server – Récupérer les droits sysadmin après la perte du mot de passe du compte SA

20. mars 2016 Hatem-DJELASSI (0)

Contexte

Le post suivant explique comment récupérer des droits sysadmin sur un serveur de base de données suite à la perte du mot de passe du compte SA.

Afin de réaliser cette procédure il faut :

  • Un compte administrateur local,
  • Planifier un créneau d’interruption durant la récupération des droits SA.

Récupération des droits sysadmin

1. Démarrer l’instance SQL en “single user mode” (ou monouser) avec la commande suivante depuis le répertoire “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn” :

SQLServr.Exe –m

 

Il est également possible d’exécuter l’opération avec l’instance démarrées en “minimal configuration” avec le switch –f.

2. Une fois la base de données démarrée en mode “single user” il est possible d’utiliser l’outil SQLCMD pour récupérer les droits sysadmin, exécuter la commande suivante :

SQLCMD –S <NOMSERVEUR\NOMINSTANCE>

 

3.Une fois connecté depuis SQLCMD, il faut créer un nouvel utilisateur (User1 dans l’exemple suivant) à qui affecter les droits sysadmin à l’aide des commandes suivantes :

1. CREATE LOGIN ‘User1’ with PASSWORD=’Password’
2. GO
3. SP_ADDSRVROLEMEMBER 'User1','SYSADMIN'
4. GO

4. Redémarrer l’instance SQL (avec la commande SQLServr.Exe) puis se connecter avec le login User1 afin de modifier le mot de passe du compte SA.

SQL Server – Mettre en place une réplication asynchrone via le Log Shipping

20. mars 2016 Hatem-DJELASSI SQL Server (0)

Contexte

Le log shipping est l’une des technologies proposée par SQL Server pour mettre en place une haute-disponibilité d’une ou plusieurs bases de données à moindre prix.

Le fonctionnement du log shipping peux être résumé en trois étapes “backup-copy-restore”.

Les logs de transactions des bases de donnée à répliquer sont sauvegardées depuis le serveur primaire puis copiés vers le (ou les) serveur secondaire où ils y seront restaurés sur les bases secondaires correspondantes.

Plus d’informations sur le fonctionnement du log shipping sont disponibles ici.

Prérequis

Les prérequis à l’installation sont les suivants :

Prérequis Applicatifs :

  • Au moins deux serveurs avec SQL Server 2005 (Standard, Entreprise ou Workgroup) ou ultérieur,
  • Un partage de fichier pour la copie des logs de transactions,
  • Le service SQL Server Agent,
  • Il est fortement conseillé d’avoir des instances SQL partenaires configurés à l’identique.

Permissions nécessaires :

  • Disposer des droits sysadmin sur l’instance primaire et la secondaire,

Configuration du log shipping

Les actions décrites sont effectuées depuis le serveur primaire.

1. Vérifier que la base de donnée à répliquer est en mode de recovery “full” ou “bulk-logged” à l’aide de la requête suivante :

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'MyPrimaryDB'

 

2. Depuis les propriétés de la base à répliquer, depuis l’onglet “Transaction Log Shipping” (1) cochez la case “Enable this as a primary database in a log shipping configuration” (2) puis cliquer sur “Backup Settings” (3) :

image

3. Depuis la fenêtre Backup Settings, indiquer le chemin réseau où le serveur primaire écriras les backups des logs de transactions des bases de données à répliquer (1) et éventuellement le chemin local (2) si un chemin local est utilisé. Noter que dans les deux cas le compte utilisé par le service SQL Server Agent du serveur secondaire doit avoir un accès en lecture à ce dossier, et le service SQL Server du serveur primaire doit avoir un accès en read/write.

Indiquer le nom du job de backup du serveur primaire (3) de manière à facilement le reconnaitre, éventuellement modifier le schedule proposé (4) par un schedule correspondant mieux à vos contraintes spécifiques puis cliquer sur OK.

image

4. Depuis la fenêtre Secondary Database Settings, se connecter au serveur secondaire (1), choisir une base de donnée existante ou indiquer le nom d’une nouvelle base (2) qui seras utilisée/créée sur le serveur secondaire, puis choisir le mode pour initialiser la base de donnée secondaire.

Ayant déjà créé initialisé la base distante, j’ai choisi l’option “No, the secondary database is initialized” (3)

image

5. Depuis l’onglet “Copy Files” (1), indiquer le chemin où les sauvegardes des logs de transactions seront copiés (2), indiquer la rétention des fichiers copiés (3) en faisant attention à ce que les fichiers ne soient pas supprimés avant leur restauration sur le serveur secondaire, personnaliser le nom du job de copie (4) et modifier son schedule si nécessaire (5)

image

6. Depuis l’onglet “Restore Transaction Logs” (1), choisir “Standby Mode” et cocher la case “Dissconnet users in the database when restoring backups” (2) celà permet d’accéder à la base de données secondaire en Read-Only contrairement au mode “No-Recovery” qui met la base en état de restauration, personnaliser le nom du job de restauration (3) et en modifier le schedule si nécessaire (4)

image

Vérification

Il convient suite à la mise en place du log shipping de vérifier son fonctionnement en mode manuel (c-à-d en exécutant manuellement les jobs de Backup-Copy-Restore) et automatiquement selon les schedule définis pour chaque jobs.

Il est possible de mettre en place des alertes par email pour informer le DBA de la bonne exécution des jobs de réplication depuis l’onglet Transaction log shipping des propriétés de la base répliquée.