PI Services

Le blog des collaborateurs de PI Services

SQL Server–Supprimer l’un des fichiers utilisé par la TempDB

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/

SQL Server–Reconfigurer les plans de maintenances après avoir renommé une instance SQL

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” :

SELECT  x.*,
        LocalServerConnectionString = cm.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";DTS:ObjectData[1]/DTS:ConnectionManager[1]/@DTS:ConnectionString', 'varchar(1000)')
FROM (
    SELECT  id, name, packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
    FROM dbo.sysssispackages
    WHERE id IN (SELECT id FROM dbo.sysmaintplan_plans)
) x
CROSS APPLY packageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager[@DTS:ObjectName="Local server connection"]') p(cm)

Voici un exemple de résultat :

image

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

UPDATE dbo.sysssispackages SET packagedata = CAST(CAST(REPLACE(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)), 'OldServerName', 'NewServerName') AS XML) AS VARBINARY(MAX))
WHERE id = ‘ID'

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

KEMP – Ré-écriture d’URL

Introduction

Lors de la publication d’une application WEB via KEMP, vous pouvez être amené à publier l’application en utilisant une URL différente de celle utilisée en interne.

Cet article explique la configuration à mettre en place afin de publier une application sous différents noms sans avoir à créer les bindings correspondant sur le serveur WEB.

Contexte

Une application nommée “APPLI1” est utilisée en interne via l’URL “https://appli1.internaldomain.dom”, le domaine publié en externe est nommé “https://appli1.externaldomain.com”.

L’application “APPLI1” ne supportant pas l’utilisation de bindings multiples, le KEMP de publication doit donc

  1. recevoir depuis l’extérieur les demandes provenant de l’URL “https://appli1.externaldomain.com”
  2. transmettre ces demandes en interne vers l’URL “https://appli1.internaldomain.dom”

Réalisation

Afin de mettre en place la réécriture d’URL, il faut créer les règles suivantes :

HTTP Header Modifications Rule :

image

Cette règle seras utilisée pour transformer l’URL “appli1.externaldomain.com” en “appli1.internaldomain.dom”

Cette règle doit être appliquée au niveau de la VIP dans “Advanced Properties\HTTP Header Modification”

image

Content Matching Rule :

image

Cette règle est utilisée afin que la VIP réponde à l’URL “*.externaldomain.com/*”

L’option content switching doit être activée depuis “Advanced Properties\Content Switching”

image

Cette règle doit être appliquée au niveau de la VIP dans “Real Servers\Rules” pour chaque serveur devant répondre à l’URL externe

image

Dans le cas où l’URL interne serait également publiée, il faut penser à activer la “default” content switching rule niveau de la VIP dans “Real Servers\Rules” pour chaque serveur devant répondre à l’URL externe et interne, la default rule doit toujours être positionnée en dernier :

image

PowerShell – Envoi de fichier chiffrés via FTP

Introduction

Afin de réaliser une action régulière et standart, deux solutions sont possible :

  • Réaliser l’action manuellement,
  • Développer un script qui s’en charge automatiquement.

Prérequis

  • Powershell,
  • 7zip,
  • Créer une tâche planifiée

Script

Le script suivant permet d’automatiquement envoyer par FTP dans un fichier chiffré, ce script utilise principalement les variables suivantes :

  • $days_to_send : L’age maximum des fichiers à envoyer
  • $source_path : Repertoire contenant les fichier à envoyer
  • $zipdirectory : Repertoire provisoire où seront compressé les fichiers
  • $log_location : emplacement du fichier de log
  • $sz : emplacement l’executable de 7zip
  • $ftp : FTP cible
  • $user : username utilisé pour se connecter au FTP
  • $pass : password utiliser pour se connecter au FTP et pour chiffré le fichier ZIP
  • $From : Adresse email qui emet les notifications par mail
  • $To : Adresse email recevant les notifications par mail
  • $SMTPServer : serveur SMTP utilisé pour les notifications par mail

 

#MAIN
function main
{
#Date Variables
$full_date = Get-Date
$custom_date = $full_date.ToString("yyyyMMdd")
$days_to_send = 4
$limit = $full_date.AddDays(-$days_to_send)

#Location Variables
$source_path = "E:\BACKUP\DATA\Database"
$zipdirectory = "G:\FTP_Folder\TEMP_ZIP"
$log_location = "G:\FTP_Folder\Logs"
$sz = ("C:\Program Files\7-Zip\7z.exe")

#FTP Variables
$ftp = "ftp://ftpr.domain.com/" 
$user = "FTP_Username" 
$pass = "FTP_Password"

#General variables
$LogFile
$compressed_folder
$folder_to_compress
$environment = "PROD"

#Mail Variables
$From = "Hatem.DJELASSI@piservices.com"
$To = "Hatem.DJELASSI@piservices.com"
$Cc = "Jean.DUPONT@piservices.com"
$Attachment
$Subject 
$Body
$SMTPServer = "smtp.domain.com"
$SMTPPort = "25"

Create-log-file -_log_location $log_location;

#Start Log
Append-log-file -_text "---------------------------------------------------------------"
Append-log-file -_text "START LOG @$full_date"

Create-Directory -_path $zipdirectory;
Copy-backups-older-than -_source_path $source_path -_destination_path $folder_to_compress -_limit $limit;
Compress_folder -_sz $sz -_pass $pass -_zipdirectory $zipdirectory -_folder_to_compress $folder_to_compress;
Delete_Folder -_file_to_delete $folder_to_compress;
Send_FTP -_user $user -_pass $pass -_ftp $ftp -_file_to_send $compressed_folder;
Notify -_from $From -_to $To -_cc $cc -_subject $Subject -_body $Body -_smtp $SMTPServer -_port $SMTPPort;

$compressed_folder = Get-ChildItem -Path $zipdirectory -Recurse -Force | Where-Object {$_.extension -match ".7z"} -ErrorAction stop
try
    {
        Remove-Item $compressed_folder.FullName -Recurse -ErrorAction Stop
        Append-log-file -_text "OK Deletion of the item has been made successfully" -_LogFile $LogFile;
    }
catch
    {
        Append-log-file -_text "ERROR when deleting the item $compressed_folder" -_LogFile $LogFile;
        write-host "An error occurred when deleting the item $compressed_folder" + $_.Exception.Message 
    }


#End log
Append-log-file -_text "END LOG @$full_date"
Append-log-file -_text "---------------------------------------------------------------"

}

#SendMail Function
function Notify
{
    param($_from, $_to, $_cc, $_attachment, $_subject, $_body, $_smtp, $_port)
    try 
            {           
                Send-MailMessage -From $_From -to $_To -Cc $_Cc -Subject $_Subject `
                -Body $_Body -SmtpServer $_SMTP -port $_Port 
                Append-log-file -_text "The email notification has been sent successfully" -_LogFile $LogFile;
            }
        catch
            {
                write-host "An error occurred when sending the notification" + $_.Exception.Message 
                Append-log-file -_text "The email notification has not been sent" -_LogFile $LogFile;
            }
}


#Log file functions
function Create-log-file 
{
    param ([string]$_log_location)        
    if (!(Test-Path "$_log_location\$custom_date - FTP_Script.log"))
    {
        try 
            {           
                $_LogFile = "$_log_location\$custom_date - FTP_Script.log"
                Set-Variable -Name LogFile -Value $_LogFile -Scope 1
            }
        catch
            {
                write-host "An error occurred when creating the log file" + $_.Exception.Message 
            }
        
    }
    else
    {
        try 
            {
                $_LogFile = "$log_location\$custom_date - FTP_Script.log"
                Set-Variable -Name LogFile -Value $_LogFile -Scope 1
            }
        catch
            {
                write-host "An error occurred when reusing the log file" + $_.Exception.Message 
            }
    }
}

function Append-log-file 
{
    param ($_text)
    try 
        {
            $custom_date+" "+$_text | Out-File $LogFile -Append -Force
        }
    catch
        {
            write-host "An error occurred when writing into the log file" + $_.Exception.Message 
        }
}

#Folders & files functions
function Create-Directory
{
    param ($_path)
    try 
        {
            $_folder_to_compress = New-Item -ItemType directory -Path "$_path\$custom_date - $environment Database Export" -force -ErrorAction Stop
            Set-Variable -Name folder_to_compress -Value $_folder_to_compress -Scope 1
            Append-log-file -_text "OK New folder to compress created successfully" -_LogFile $LogFile;

        }
    catch
        {
            Append-log-file -_text "ERROR when creating new folder to compress" -_LogFile $LogFile;
            write-host "An error occurred when creating new folder to compress" + $_.Exception.Message 
        }
}

function Copy-backups-older-than
{
    param ($_source_path, $_destination_path, $_limit)
    try
        {
            Get-ChildItem -Path $_source_path -Recurse -Force | Where-Object {$_.extension -in ".dif",".bak" -and !$_.PSIsContainer -and $_.CreationTime -gt $_limit } | copy-item -Destination  $_destination_path -Force -Container -ErrorAction stop
            Append-log-file -_text "OK The copy of the backups made after $limit has been made successfully" -_LogFile $LogFile;
        }
    catch
        {
            Append-log-file -_text "ERROR when copying the backups made after $limit" -_LogFile $LogFile;
            write-host "An error occurred when copying the backups made after $limit" + $_.Exception.Message 
        }
}

function Compress_folder
{
    param ($_sz, $_pass, $_zipdirectory, $_folder_to_compress)
    try
    {
        & $sz a -p"$_pass" "$_zipdirectory\$custom_date - $environment Database Export.7z" "$_folder_to_compress\*"
        $_compressed_folder = Get-ChildItem -Path $_zipdirectory -Recurse -Force | Where-Object {$_.extension -match ".7z"} -ErrorAction stop
        Set-Variable -Name compressed_folder -Value $_compressed_folder -Scope 1
        Append-log-file -_text "OK Compression/encryption of the folder has been made successfully" -_LogFile $LogFile;
    }
    catch
    {
        Append-log-file -_text "ERROR during compression/encryption of the folder" -_LogFile $LogFile;
        write-host "An error occurred during compression/encryption of the folder" + $_.Exception.Message 
    }
}

function Delete_Folder
{
    param ($_file_to_delete)
    try
    {
        Remove-Item "$_file_to_delete" -Recurse -ErrorAction stop
        Append-log-file -_text "OK Deletion of the item has been made successfully" -_LogFile $LogFile;
    }
    catch
    {
        Append-log-file -_text "ERROR when deleting the item $_file_to_delete" -_LogFile $LogFile;
        write-host "An error occurred when deleting the item $_file_to_delete" + $_.Exception.Message 
    }
}

#FTP Function
function Send_FTP
{
    param ($_user, $_pass, $_ftp, $_file_to_send, $_subject, $_body)
    #Create Web client
    try
    {
        $webclient = New-Object System.Net.WebClient -ErrorAction stop
        $webclient.Credentials = New-Object System.Net.NetworkCredential($_user,$_pass) -ErrorAction Stop 
        $webclient.Proxy = $NULL 
        Append-log-file -_text "OK FTP WebClient created successfully" -_LogFile $LogFile;
    }
    catch
    {
        Append-log-file -_text "ERROR when creating the FTP WebClient" -_LogFile $LogFile;
        write-host "An error occurred when creating the FTP WebClient" + $_.Exception.Message 
    }
    #Create URI
    try
    {
        $_uri = New-Object System.Uri($_ftp+$_file_to_send.Name) -ErrorAction stop 
        Append-log-file -_text "OK FTP URI created successfully" -_LogFile $LogFile;
    }
    catch
    {
        Append-log-file -_text "ERROR when creating the FTP URI" -_LogFile $LogFile;
        write-host "An error occurred when creating the FTP URI" + $_.Exception.Message 
    }
    #Send the file
    try
    {
        $webclient.UploadFile($_uri, $_file_to_send.FullName)
        Append-log-file -_text "OK FTP file sent successfully" -_LogFile $LogFile;
        $global:Subject = "FTP Upload Success"
        $global:Body = "The file $_file_to_send has been sent successfully @$full_date"
    }
    catch
    {
        Append-log-file -_text "ERROR when sending the file through FTP" -_LogFile $LogFile;
        write-host "An error occurred when sending the file through FTP" + $_.Exception.Message 
        $global:subject = "FTP Upload Failure"
        $global:Body = "An error occurred when sending the FTP backups from $environment to FTP @$full_date"
    }
}

main

SQL Server – Executer une procédure stockée en “RUN AS”

Introduction

Afin de fournir à un utilisateur le droit d’executer une procédure stockée, un administrateur SQL peut fournir à un utilisateur le droit “EXECUTE” sur celle-ci.

Cependant, il est possible que l’utilisateur rencontre une erreur lors de l’execution de la procédure stockée de type :

image

Celà est souvent du à la présence de code SQL dynamique, généré lors de l’execution.

Explication

Il est possible de fournir à l’utilisateur le droit ��SELECT” sur l’objet de la requête et le script pourras être executé avec succès, néanmoins, celà vas contre le principe d’une procédure stockée qui permet de fournir aux utilisateurs un accès aux données via l’interface restrictive de la procédure stockée.

Afin de résoudre ce problème il est possible (à partir de SQL 2008) d’utiliser la clause “EXECUTE AS” en spécifiant un compte disposant des droits nécessaires à l’execution de la procédure stockée.

Il est important de noter que l’utilisation de “EXECUTE AS” ne fournit pas à l’utilisateur final des droits d’impersonnalisation qui permetteraient à l’utilisateur de “récuperer” les droits du compte d’execution.

Réalisation

L’exemple suivant explique comment utiliser “EXECUTE AS” :

image

La ligne 2 spécifie le compte “SQLUser” à utiliser pour l’execution du script,

La ligne 5 retourneras le compte “SQLUser”,

La ligne 7 “EXECUTE AS CALLER” permet d’utiliser le compte de l’utilisateur executant la procédure stockée,

La ligne 8 retourneras le compte de l’utilisateur executant la procédure stockée,

La ligne 10 “REVERT” retablis l’utilisation du compte “SQLUser” spécifié au début du script.

Source

https://msdn.microsoft.com/fr-fr/library/ms181362.aspx

http://www.sqlmatters.com/Articles/Using%20Execute%20As%20with%20Stored%20Procedures%20Containing%20Dynamic%20SQL.aspx

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

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

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)

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)

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

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')