Freigeben über

SQL Server 2014, Azure VM série D/G et TempDB

  • Artikel

Avec les nouvelles séries, D et G, des machines virtuelles Azure, nous bénéficions nativement d’un disque local temporaire basé sur la technologie SSD. Cette configuration est idéale pour les scénarios nécessitant un faible temps de latence et de beaucoup d’I/O dans l’accès de données temporaire. Dans le cas de SQL Server, on peut penser à utiliser ce disque avec les  fichiers de la base TempDB ou utiliser la nouvelle fonctionnalité offerte par SQL Server 2014 qu’est le Buffer Pool Extension (BPE) (La fonctionnalité Buffer Pool Extension est disponible dans les versions Standard, Business Intelligence et Entreprise de SQL Server, mais uniquement dans les versions 64 bits : Scalability and Performance).

J’insiste sur le côté temporaire de ce disque local (D). Effectivement, à chaque redémarrage de la machine virtuelle, toutes les données sur ce disque seront réinitialisées. Pour un stockage permanent sur disque SSD, on pourra regarder du côté du stockage premium d’Azure.

Pourquoi cet article

Cet article prend sa source sur le blog Technet : https://blogs.technet.com/b/dataplatforminsider/archive/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions.aspx

A la base, j’ai écrit cet article pour servir de pas à pas dans les sessions de labs que je vais animer prochainement :

https://msevents.microsoft.com/cui/EventDetail.aspx?EventID=1032613228&culture=fr-FR

Puis je me suis dit, pourquoi pas le publier, finalement :) !

TempDB sur le disque SSD

Création de la machine virtuelle

Connectez-vous au portail Azure : https://manage.windowsazure.com

En bas de la page, cliquez sur le bouton “Nouveau”.

image

Dans le menu “Calcul/Machine Virtuelle”, cliquez sur “A partir de la galerie”.

image

Dans la fenêtre “Choisir une image”, cliquez sur “SQL Server” dans la partie gauche, puis sélectionnez “SQL Server 2014 RTM Enterprise” ou “SQL Server 2014 RTM Standard” :

 

image

 

Renseignez les informations de configuration de votre machine virtuelle :

image

Dans le champ “Région/Groupe d’affinités/réseau virtuel”, sélectionnez la région appropriée en fonction de votre localisation :

 

image

Dans la dernière fenêtre, laissez les options par défaut et validez la création de la machine virtuelle

image

Configuration de la localisation de la base TempDB

Une fois la machine virtuelle démarrée, allez sur la racine du disque D puis créez un répertoire. Dans cet exemple, je vais créer un répertoire “D:\Temp”.

image

Exécutez SQL Server Management Studio (SSMS), et connectez-vous à l’instance SQL Server. Cliquez sur le bouton “New Query”.

image

Dans la partie centrale de management studio, saisissez le script suivant :

USE MASTER
GO

ALTER DATABASE tempdb MODIFY FILE (NAME= tempdev, FILENAME= 'D:\Temp\tempdb.mdf')
GO

ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'D:\Temp\templog.ldf')
GO

image

Exécutez la requête en cliquant sur le bouton “Execute”.

image

Pour que les modifications prennent effet, il est nécessaire de redémarrer le service SQL. Une des manières de faire, via SSMS, consiste à faire un clic droit sur le nom de l’instance SQL et de cliquer sur “Restart”.

image

Puis de valider le redémarrage en cliquant sur le bouton “Yes”.

image

Après redémarrage du service, les fichiers de la base TempDB sont maintenant présents dans le répertoire.

image

Cependant, comme énoncé au début de cet article, le disque D est un disque temporaire, c’est à dire que les données ne sont pas persistantes et sont susceptibles d’être réinitialisées lors d’un redémarrage de la machine par exemple. Par conséquent, le dossier ne sera plus présent, et l’instance SQL Server ne retrouvera pas le chemin pour créer les fichiers de la base TempDB.

Après un redémarrage de la machine, et une tentative de connexion à l’instance SQL Server voici le message que l’on obtient

image

Si on regarde dans le gestionnaire d’évènements de Windows on peut retrouver les erreurs suivantes :

image

dont le message : (The system cannot find the path specified.) D:\Temp\tempdb.mdf

Pour éviter ce problème, il est possible de mettre en place une tâche Windows qui s’exécutera au démarrage de la machine pour créer le dossier “Temp” sur le disque D, via un script PowerShell.

Création automatique du dossier via une tâche Windows

Création d’un script PowerShell

Dans un premier temps nous allons créer un fichier PowerShell avec le code suivant. En supposant que le service SQL pointe sur l’instance SQL par défaut.

Dans un dossier se trouvant sur le disque C (par exemple C:\Script), créez un fichier texte.

image

Ouvrez ce fichier texte et copiez puis modifiez le script ci-dessous

$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempfolder="D:\Temp"
if (!(test-path -path $tempfolder)) {
New-Item -ItemType directory -Path $tempfolder
}
Start-Service $SQLService
Start-Service $SQLAgentService

Enregistrez le fichier. Fermez le fichier et renommez son extension en “.ps1”. Ci-dessous une copie d’écran de mon exemple.

image

Il est aussi nécessaire d’autoriser l’exécution de scripts signés par PowerShell.

Pour ce faire, exécutez la commande PowerShell suivante :

Set-ExecutionPolicy RemoteSigned.

Validez l’exécution de la commande avec “Y” et la touche “Entrer”.

image

Paramétrage du service SQL

Le script PowerShell démarre les services SQL Server et SQL Server Agent après la création du dossier.

Il faut donc paramétrer les services pour un démarrage manuel.

Via l’interface moderne, exécutez “SQL Server 2014 Configuration Manager”

image

Repérez le service SQL SQL Server (MSSQLSERVER) (dans le cas d’une instance par défaut).Faîtes un clic droit et cliquez sur “Properties”.

image

Sélectionnez l’onglet “Service”. Dans la liste déroulante Start Mode, sélectionnez “Manual”.

Cliquez sur le bouton “Ok”

image

Normalement, le service SQL Server Agent est déjà paramétré sur “Manual”. Sinon, répétez l’opération pour ce service.

Création d’une tâche Windows

Via l’interface moderne, démarrez le gestionnaire de tâche de Windows.

image

Une fois le gestionnaire de tâche démarré, faîtes un clic droit sur “Task Scheduler Library”, puis cliquez sur “New Folder…”. Nous allons juste créer un nouveau dossier pour y ranger notre tâche planifiée.

image

Donnez un nom au dossier, puis cliquez sur “Ok” :

image

Double cliquez sur le nouveau dossier puis faîtes un clic droit sur ce dossier. Cliquez sur “Create Basic Task…”

image

 

Donnez un nom à votre tâche puis cliquez sur “Next”.

image

Sélectionnez “When the computer starts”. Cliquez sur “Next”.

image

Sélectionnez “Start a program”. Cliquez sur “Next”

image

Dans la fenêtre “Start a program”, renseignez les champs suivants avec les valeurs ci-dessous:

Program/script : powershell.exe

Add arguments : –file “C:\Script\CreateFolder.ps1”

image

Dans la fenêtre “Finish”, cochez la case “Open the Properties dialog for this task when I click Finish”, puis cliquez sur “Finish”.

image

La fenêtre des propriétés s’affiche. Sélectionnez “Run whether user is logged on or not”. Puis cliquez sur “Ok”

image

Cliquez sur le bouton “Ok”.

Entrez le mot de passe du compte utilisé pour exécuter le script. Cliquez sur “Ok”

image 

Vous devez obtenir une fenêtre similaire à celle ci-dessous :

image

Un bon moyen de voir si la création du dossier fonctionne est de tester notre tâche. Faîtes un clic droit dessus et cliquez sur “Run”.

image

Le dossier Temp doit se créer sur le disque temporaire D.

image

Et les fichiers de la base TempDB sont présents, suite au démarrage des services SQL.

image

Activation de l’historisation de l’exécution des tâches

Dans le cas où notre tâche ne fonctionnerait pas comme prévue, il peut être utile d’avoir des informations sur l’exécution de la tâche. Par défaut, l’historique des exécutions des tâches est désactivé.

image

Pour activer l’historisation, sur le panneau de droite “Actions”, cliquez sur “Enable All Tasks History”

image

Ci-dessous un exemple de l’historique de l’exécution de la tâche

image

TempdDB à la racine du disque D

Il est aussi possible de mettre les fichiers TempDB directement à la racine du disque SSD. Ce qui nous évitera toute la phase de création de la tâche Windows. Cependant, il faut que le service SQL s’exécute avec un compte Administrateur local de la machine afin de permettre l’accès à la racine du disque D.

Buffer Pool Extension

Une des nouveautés de SQL Server 2014 est la possibilité d’utiliser des disques rapides pour étendre le buffer pool via le “Buffer Pool Extension” (BPE). Vous trouverez plus de détail sur le BPE ici :

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

Mise en place du BPE

Pour activer le BPE, il suffit d’exécuter le script SQL ci-dessous dans SSMS :

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION ON

( FILENAME = 'D:\SQL_BPE\ExtensionFile.BPE' , SIZE = <size> [ KB | MB | GB ] )

Pour le paramètre “SIZE”, il est recommandé d’utiliser 4 à 6 fois la taille de la mémoire vive de la machine virtuelle

Dans mon exemple j’utilise donc :

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON
(FILENAME = 'D:\SQL_BPE\ExtensionFile.BPE', SIZE = 28GB)
GO

image

Si je regarde sur mon disque D, j’ai bien un nouveau fichier avec l’extension BPE.

image

Une fois le BPE activé, il est possible d’avoir plus de détail avec la gestion de vue dynamique sys.dm_os_buffer_pool_extension_configuration :

SELECT * FROM sys.dm_os_buffer_pool_extension_configuration
GO

image

Afin de savoir si le BPE est utilisé, il est possible d’utiliser la vue dynamique sys.dm_os_buffer_descriptors.

Le script ci-dessous retourne les bases de données, ainsi que le nombre de pages, qui utilisent le BPE

select DB_NAME(database_id) Database_Name, count(*) Pages , is_in_bpool_extension
from sys.dm_os_buffer_descriptors
group by database_id, is_in_bpool_extension
order by database_id

image

Enfin, pour désactiver le BPE, il suffit d’utiliser le script ci-dessous

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF
GO

Franck Mercier

Sessions de formation gratuites :

Pour tester Windows Server 2012, Windows 8, SQL Server 2012 et SQL Server 2014 CTP2, vous pouvez télécharger gratuitement la version d’évaluation disponible sous la forme :

Windows Server 2012 :

SQL Server 2012 :

Evaluation SQL Server 2014 CTP2 :

Evaluation Power BI :

Testez Azure gratuitement pendant un mois :

Comments

  • Anonymous
    January 01, 2003
    Merci Nicolas :)
  • Anonymous
    April 01, 2015
    Merci Franck pour cet article :). Trop bien pour mes tests et montrer ça au client :)
  • Anonymous
    April 21, 2015
    Excellent Journée DataCamp
  • Anonymous
    October 08, 2015
    Merci Franck, excellent article !!!