Créer une instance SQL Server hautes performances

Ce tutoriel montre comment créer une instance de VM Google Compute Engine exécutant SQL Server et optimisée pour garantir un haut niveau de performances. Ce tutoriel vous explique comment créer l'instance puis configurer SQL Server pour des performances optimales sur Google Cloud Platform. Vous découvrirez un certain nombre d'options de configuration à votre disposition pour ajuster les performances du système.

Ce tutoriel utilise SQL Server Standard Edition 2014. De ce fait, certaines des options de configuration présentées dans ce guide ne sont pas nécessairement adaptées à tous les utilisateurs, et certaines options ne fourniront pas nécessairement de gains de performances notables pour toutes les charges de travail.

Objectifs

  • Configurer l'instance et les disques Compute Engine.
  • Configurer le système d'exploitation Windows.
  • Configurer SQL Server.

Coûts

Ce tutoriel fait appel à des composants payants de Cloud Platform, par exemple :

  • Instance Google Compute Engine à haute capacité de mémoire
  • Stockage Compute Engine sur disque persistant SSD
  • Stockage Compute Engine sur disque SSD local
  • Image pré-configurée de SQL Server Standard

Le simulateur de coût peut générer une estimation des coûts en fonction de votre utilisation prévue. Le lien fourni présente le coût estimé pour les produits utilisés dans ce tutoriel, qui peuvent représenter plus de 4 dollars US par heure et plus de 3 000 dollars par mois. Les nouveaux utilisateurs de Cloud Platform peuvent bénéficier d'un essai gratuit.

Avant de commencer

  1. Connectez-vous à votre compte Google.

    Si vous n'en possédez pas déjà un, vous devez en créer un.

  2. Sélectionnez ou créez un projet Google Cloud Platform.

    Accéder à la page "Gérer les ressources"

  3. Assurez-vous que la facturation est activée pour votre projet Google Cloud Platform.

    Découvrir comment activer la facturation

  4. Si vous n'utilisez pas Windows sur votre ordinateur local, installez un client RDP tiers tel que l'extension Chrome RDP de FusionLabs.

Créer l'instance et les disques Compute Engine

Créez l'instance Compute Engine avec SQL Server ainsi que deux disques persistants.

  • Un SSD local fournit un emplacement à hautes performances pour tempdb et le fichier d'échange de Windows.

    Il convient de noter certains points importants lorsque vous utilisez un disque SSD local. Quand vous arrêtez votre instance depuis le système Windows ou que vous la réinitialisez à l'aide de l'API, le disque SSD local est supprimé. Cette action rend l'instance impossible à amorcer. Pour relancer la machine, vous devez d'abord détacher vos disques persistants, créer une nouvelle instance avec ces disques, puis définir un nouveau disque SSD local. Après le démarrage, vous devez également formater le nouveau disque et redémarrer. Par conséquent, vous devez éviter de stocker des données critiques de façon permanente sur un disque SSD local, ou de mettre l'instance hors tension, sauf si vous êtes prêt à la reconstruire.

  • Un disque persistant SSD fournit un stockage à hautes performances pour les fichiers de base de données.

    Les performances des disques persistants SSD sont basées sur un calcul utilisant le nombre de processeurs et la taille du disque. Avec 32 processeurs virtuels et un disque de 1 To, les performances atteignent 40 000 opérations de lecture par seconde et 30 000 opérations d'écriture par seconde (ops). En régime continu, le débit total en termes de lectures et d'écritures est, respectivement, de 800 Mo/s et 400 Mo/s. Ces mesures représentent la somme de tous les disques persistants associés à la machine virtuelle, y compris le lecteur `C:\` drive.. C'est pourquoi il est recommandé de créer un SSD local visant à déporter la charge de toutes les IOPS requises par le fichier d'échange, This is why you should create a local SSD to offload all the IOPS needed for the paging file, les données de préproduction et les sauvegardes.

Pour en savoir plus sur les performances des disques, consultez la page Optimiser les performances des disques persistants et des disques SSD locaux.

Créer l'instance Compute Engine

Créez une VM dotée de SQL Server 2014 Standard préinstallé sur Windows Server 2012.

  1. Dans la console Google Cloud Platform, accédez à la page Instances de VM.

    Accéder à la page Instances de VM

  2. Cliquez sur le bouton Créer une instance.

  3. Nommez votre instance "ms-sql-server".

  4. Définissez le type de machine : 16 processeurs virtuels, 104 Go, n1-highmem-16.

  5. Dans la section Disque de démarrage, cliquez sur Modifier pour commencer à configurer le disque de démarrage.

  6. Dans l'onglet Images d'application, choisissez SQL Server 2014 Standard sur Windows Server 2012 R2.

  7. Dans la section Type de disque de démarrage, sélectionnez Disque persistant standard.

  8. À la section Taille (Go), définissez la taille du disque de démarrage à 50 Go.

  9. Cliquez sur Sélectionner.

  10. Développez Gestion, sécurité, disques, mise en réseau et location unique.

  11. Cliquez sur Disques.

  12. Sous Disques supplémentaires, cliquez sur Ajouter un disque pour créer un disque supplémentaire.

  13. Laissez le champ Nom inchangé.

  14. Dans Type, sélectionnez Espace de travail SSD local (maximum 8).

  15. Cliquez sur Terminé pour conclure la création de ce disque.

  16. Sous Disques supplémentaires, cliquez à nouveau sur Ajouter un disque pour créer un second disque supplémentaire.

  17. Laissez le champ Nom inchangé.

  18. Dans Type, sélectionnez Disque persistant SSD.

  19. Dans Type de source, sélectionnez Disque vierge.

  20. Cliquez sur Terminé pour conclure la création du deuxième disque.

  21. Cliquez sur Créer pour créer l'instance.

Configurer Windows

Maintenant que vous disposez d'une instance de travail exécutant SQL Server, connectez-vous à votre instance et configurez le système d'exploitation Windows. Vous apprendrez à configurer SQL Server un peu plus loin dans une prochaine section.

Se connecter à votre instance

  1. Revenez à la page des Instances de VM dans la console GCP.

    Accéder à la page Instances de VM

  2. Dans la colonne Nom, cliquez sur le nom de votre instance, ms-sql-server.

  3. En haut de la page de détails de l'instance, cliquez sur le bouton Définir un mot de passe Windows.

  4. Spécifiez un nom d'utilisateur.

  5. Cliquez sur Définir afin de générer un nouveau mot de passe pour cette instance Windows.

  6. Notez le nom d'utilisateur et le mot de passe pour pouvoir vous connecter à l'instance.

  7. Connectez-vous à votre instance au moyen de RDP :

    • Si vous avez installé l'extension Chrome RDP de FusionLabs, cliquez sur le bouton RDP en haut de la page de détails de l'instance.
    • Si vous utilisez un autre client RDP, tel que le client Connexion Bureau à distance de Windows, cliquez sur le menu à développer du bouton RDP, puis téléchargez le fichier RDP. Ouvrez ce fichier RDP au moyen de votre client.

Configurer les volumes de disque

Créez et formatez les volumes :

  1. Dans le menu Démarrer, recherchez et ouvrez l'option "Gestionnaire de serveur".
  2. Sélectionnez Services de fichiers et de stockage, puis sélectionnez Disques.

    Le disque SSD local est nommé Google EphemeralDisk. Le SSD local et le SSD persistant sont tous les deux indiqués comme ayant des partitions Unknown :

    Rechercher l'entrée Google EphemeralDisk

  3. Cliquez avec le bouton droit sur le disque SSD local de 375 Go nommé "Google Ephemeral Disk", puis sélectionnez Nouveau volume.

  4. Continuez avec les valeurs par défaut et assignez au disque la lettre P:, car ce sera le disque du fichier d'échange (paging-file).

  5. Lorsque vous atteignez l'étape des paramètres du système de fichiers, définissez Allocation unit size (Taille d'unité d'allocation) sur 8192 et saisissez "pagefile" dans le champ Volume label (Nom du volume).

    Assistant Nouveau volume

  6. Répétez les étapes ci-dessus pour le deuxième disque persistant SSD, avec toutefois les trois modifications suivantes :

  • Choisissez comme lettre de lecteur D:.
  • Définissez "32k" comme Taille d'unité d'allocation.

    Microsoft recommande de formater les disques de données et journaux SQL Server avec une taille d'unité d'allocation de 64k, mais la technologie de disque persistant dans GCP fonctionne mieux avec 32k. Cette modification réduit également le nombre d'opérations sur disque comptabilisées dans le cadre de votre limite d'E/S de disque persistant.

  • Comme Nom du volume, saisissez "sqldata".

Correction de l'erreur Failed to mount path - Invalid Parameter

Si vous rencontrez cette erreur, procédez comme suit :

  1. Cliquez sur Fermer.
  2. Cliquez sur l'icône d'actualisation des disques, en haut à droite.
  3. Dans la liste, cliquez sur le disque persistant de 500 Go.
  4. Dans le panneau Volumes, cliquez avec le bouton droit sur le volume, puis choisissez Manage Drive Letter and Access Paths (Gérer la lettre de lecteur et les chemins d'accès).

    Modifier la lettre de lecteur et les chemins d'accès

  5. Comme lettre de lecteur, choisissez D:.

  6. Cliquez sur OK.

Déplacer le fichier d'échange de Windows

Maintenant que les nouveaux volumes sont créés et montés, déplacez le fichier d'échange de Windows vers le SSD local. Cela libère des IOPS de disque persistant et améliore les temps d'accès à votre mémoire virtuelle.

  1. Dans le menu Démarrer, recherchez Afficher les paramètres système avancés, puis ouvrez la boîte de dialogue correspondante.
  2. Cliquez sur l'onglet Avancé et, dans la section Performances, cliquez sur Paramètres.
  3. Dans la section Mémoire virtuelle, cliquez sur le bouton Modifier.
  4. Décochez la case Gestion automatique du fichier d'échange pour les lecteurs. Le système doit déjà avoir configuré votre fichier d'échange sur le lecteur `C:`, et vous devez le déplacer.
  5. Cliquez sur C:, puis activez la case d'option Aucun fichier d'échange.
  6. Cliquez sur le bouton Définir.
  7. Pour créer le fichier d'échange, cliquez sur le lecteur P:, puis activez la case d'option Taille gérée par le système.
  8. Cliquez sur le bouton Définir.
  9. Cliquez trois fois sur OK pour quitter les paramètres système avancés.

    L'assistance Microsoft a publié des conseils supplémentaires pour les réglages de mémoire virtuelle.

Définir le profil de gestion d'alimentation

Définissez le profil de gestion d'alimentation à High-Performance au lieu de Balanced.

  1. Dans le menu Démarrer, recherchez "Choisir un mode de gestion d'alimentation", puis ouvrez la boîte de dialogue correspondante.
  2. Sélectionnez le bouton radio Performances élevées.
  3. Quittez la boîte de dialogue.

Configurer SQL Server

Utilisez SQL Server Management Studio pour réaliser l'essentiel des tâches d'administration. Les images pré-configurées pour SQL Server 2014 sont livrées avec Management Studio déjà installé, mais si vous utilisez l'image SQL Server 2016, vous devez la télécharger et l'installer manuellement. Après l'installation, lancez Management Studio, puis cliquez sur Connexion pour vous connecter à la base de données par défaut.

Déplacer les données et les fichiers journaux

L'image préconfigurée pour SQL Server est fournie avec tout le nécessaire installé sur le lecteur `C:`, y compris les bases de données système. Pour optimiser votre configuration, déplacez ces fichiers vers le nouveau lecteur `D:` que vous venez de créer. Rappelez-vous également que toute nouvelle base de données, à partir de maintenant, doit être créée sur le lecteur `D:`. Comme vous utilisez un disque persistant SSD, vous n'avez pas besoin de stocker les fichiers de données et les fichiers journaux sur des partitions de disque distinctes.

Il existe deux méthodes pour déplacer l'installation vers le disque secondaire : utiliser le programme d'installation ou déplacer les fichiers manuellement.

Utiliser le programme d'installation

Pour utiliser le programme d’installation, exécutez c:\setup.exe et choisissez un nouveau chemin d’installation sur votre disque secondaire.

Déplacer les fichiers manuellement

Déplacez les bases de données système et configurez SQL Server pour enregistrer les données et les fichiers journaux sur le même volume :

  1. Créez un nouveau dossier nommé D:\SQLData.
  2. Ouvrez une fenêtre de commande.
  3. Saisissez la commande suivante pour accorder un accès complet à NT Service\MSSQLSERVER :

    icacls D:\SQLData /Grant "NT Service\MSSQLServer":(OI)(CI)F
    
  4. Utilisez Management Studio ainsi que les guides suivants pour déplacer vos bases de données système et modifier les emplacements de fichiers par défaut pour les nouvelles bases de données.

  5. Si vous prévoyez d'utiliser les fonctionnalités de Serveur de rapports (Report Server), déplacez également les fichiers ReportServer et ReportServerTempDB.

Une fois que vous avez déplacé les fichiers maîtres et redémarré, vous devez configurer le système pour qu'il pointe vers le nouvel emplacement du modèle et des bases de données MSDB. Voici un script auxiliaire à exécuter dans Management Studio :

ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'D:\SQLData\model.mdf' )
ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'D:\SQLData\modellog.ldf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\SQLData\MSDBData.mdf' )
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBlog , FILENAME = 'D:\SQLData\MSDBLog.ldf' )

Après avoir exécuté ces commandes :

  1. Utilisez le composant logiciel enfichable services.msc pour arrêter le service de base de données SQL Server.
  2. Utilisez l'explorateur de fichiers Windows pour déplacer les fichiers physiques du lecteur `C:` où se trouve la base de données maître vers le répertoire D:\SQLData.
  3. Démarrez le service de base de données SQL Server.

Définir les autorisations système

Après avoir déplacé les bases de données système, modifiez certains paramètres supplémentaires, en commençant par les autorisations du compte utilisateur Windows créé pour exécuter votre processus SQL Server et dénommé NT Service\MSSQLSERVER.

Accorder les autorisations pour Lock Pages in Memory

L'autorisation de stratégie de groupe Lock Pages in Memory empêche Windows de déplacer les pages de la mémoire physique vers la mémoire virtuelle. Afin de maintenir la disponibilité et l'organisation de la mémoire physique, Windows essaie de déplacer les pages anciennes, rarement modifiées, vers le fichier de pagination de la mémoire virtuelle sur le disque.

SQL Server stocke en mémoire des informations importantes telles que la structure des tables, les plans d'exécution et les requêtes mises en cache. Certaines de ces informations changent rarement et deviennent donc une cible de choix pour le fichier de pagination. Si ces informations sont déplacées vers le fichier de pagination, les performances de SQL Server peuvent se dégrader. Accorder au compte de service de SQL Server l'autorisation de stratégie de groupe Lock Pages in Memory empêche ces échanges en mémoire.

Procédez comme suit :

  1. Cliquez sur Démarrer, puis recherchez Modifier la stratégie de groupe pour ouvrir la console.
  2. Développez Local Computer Policy > Computer Configuration > Windows Settings > Local Policies > User Rights Assignment (Stratégie Ordinateur local > Configuration ordinateur > Paramètres Windows > Stratégies locales > Attribution des droits utilisateur).
  3. Recherchez puis double-cliquez sur Lock pages in memory (Verrouiller les pages en mémoire).
  4. Cliquez sur Ajouter un utilisateur ou un groupe.
  5. Recherchez "NT Service\MSSQLSERVER".
  6. Si vous voyez plusieurs noms correspondants, double-cliquez sur le nom MSSQLSERVER.
  7. Cliquez deux fois sur "OK".
  8. Gardez la console de l'Éditeur de stratégie de groupe ouverte.

Verrouiller les pages

Accorder l'autorisation Perform volume maintenance tasks

Par défaut, lorsqu'une application demande à Windows une tranche d'espace disque, le système d'exploitation recherche un fragment d'espace disque de taille appropriée, puis met à zéro la totalité de ce fragment avant de le renvoyer à l'application. Étant donné que SQL Server est capable de faire croître ses fichiers et de remplir l'espace disque disponible, ce comportement n’est pas optimal.

Il existe une API distincte pour l'allocation d'espace disque à une application, que l'on désigne souvent par le nom initialisation instantanée des fichiers. Malheureusement, ce paramètre fonctionne uniquement pour les fichiers de données, mais dans l'une des sections à venir, vous en apprendrez davantage sur la croissance des fichiers journaux. L'initialisation instantanée des fichiers nécessite que le compte de service exécutant le processus SQL Server dispose d'une autre autorisation de stratégie de groupe dénommée Perform volume maintenance tasks.

  1. Dans l'Éditeur de stratégie de groupe, recherchez "Effectuer les tâches de maintenance du volume".
  2. Ajoutez le compte "NT Service\MSSQLSERVER" comme vous l'avez déjà fait à la section précédente.
  3. Redémarrez le processus SQL Server pour activer les deux réglages.

Configurer TempDB

Par le passé, optimiser l'utilisation du processeur par SQL Server en créant un fichier TempDB par CPU était considéré comme une bonne pratique. Toutefois, comme le nombre de processeurs a augmenté avec le temps, suivre cette directive peut aujourd'hui entraîner une baisse des performances. Un bon point de départ consiste à utiliser 4 fichiers TempDB. L'évaluation des performances de votre système peut, dans de rares cas, vous conduire à augmenter progressivement le nombre de fichiers TempDB jusqu'à un maximum de 8.

Vous pouvez exécuter un script T-SQL au sein de SQL Server Management Studio pour déplacer les fichiers TempDB vers un dossier du lecteur `P:`.

  1. Créez le répertoire p:\tempdb.
  2. Accordez un accès de sécurité complet au compte d'utilisateur "NT Service\MSSQLSERVER" :

    icacls p:\tempdb /Grant "NT Service\MSSQLServer":(OI)(CI)F
    
  3. Exécutez le script suivant dans SQL Server Management Studio afin de déplacer le fichier de données TempDB ainsi que le fichier journal :

    USE Master
    GO
    ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf')
    GO
    ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf')
    GO
    
  4. Redémarrez SQL Server.

  5. Exécutez le script suivant pour modifier les tailles de fichiers et créer trois fichiers de données supplémentaires pour le nouveau dossier TempDB.

    ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'p:\tempdb\tempdb.mdf', SIZE=8GB)
    ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'p:\tempdb\templog.ldf' , SIZE = 2GB)
    ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev1', FILENAME = 'p:\tempdb\tempdev1.ndf' , SIZE = 8GB, FILEGROWTH = 0);
    ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev2', FILENAME = 'p:\tempdb\tempdev2.ndf' , SIZE = 8GB, FILEGROWTH = 0);
    ALTER DATABASE [tempdb] ADD FILE (NAME = 'tempdev3', FILENAME = 'p:\tempdb\tempdev3.ndf' , SIZE = 8GB, FILEGROWTH = 0);
    GO
    

    Si vous utilisez SQL Server 2016, trois fichiers TempDB supplémentaires doivent être supprimés à la suite des étapes précédentes :

    ALTER DATABASE [tempdb] REMOVE FILE temp2;
    ALTER DATABASE [tempdb] REMOVE FILE temp3;
    ALTER DATABASE [tempdb] REMOVE FILE temp4;
    
  6. Redémarrez SQL Server de nouveau.

  7. Supprimez les fichiers model, MSDB, master et tempdb de leur emplacement d'origine sur le lecteur `C:`.

Vous avez déplacé avec succès vos fichiers TempDB vers la partition SSD locale. Ce déplacement comporte certains risques, que nous avons mentionnés précédemment. Si toutefois, pour une raison quelconque, les fichiers sont perdus, SQL Server peut reconstruire les fichiers TempDB. Déplacer TempDB vous offre les performances supplémentaires du disque SSD local et réduit les IOPS utilisées sur vos disques persistants.

Régler max degree of parallelism

Le réglage par défaut recommandé pour max degree of parallelism est de le faire correspondre au nombre de processeurs sur le serveur. Cependant, il existe un seuil au-delà duquel l'exécution d'une requête en 16 ou 32 blocs parallèles et la fusion des résultats est beaucoup plus lente que son exécution en un seul processus. Si vous utilisez une instance à 16 ou 32 cœurs, vous pouvez définir la valeur de max degree of parallelism à 8 au moyen du script T-SQL suivant :

USE Master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max degree of parallelism', 8
GO
RECONFIGURE WITH OVERRIDE
GO

Régler max server memory

Ce paramètre est fixé par défaut à un nombre très élevé, mais vous devez le régler sur le nombre de méga-octets de mémoire RAM physique disponible, moins quelques giga-octets réservés au système d'exploitation et à la gestion des débordements. L'exemple T-SQL suivant règle max server memory à 100 Go. Modifiez-le pour ajuster cette valeur en fonction de votre instance.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
exec sp_configure 'max server memory', 100000
GO
RECONFIGURE WITH OVERRIDE
GO

Pour terminer

Redémarrez l'instance une dernière fois pour vous assurer que tous les nouveaux paramètres prennent effet. Votre système SQL Server est configuré et vous êtes prêt à créer vos propres bases de données et à tester vos charges de travail spécifiques. Consultez le Guide des bonnes pratiques SQL Server pour plus d'informations sur les activités opérationnelles, les autres considérations relatives aux performances et les fonctionnalités Enterprise Edition.

Effectuer un nettoyage

Une fois que vous avez terminé ce tutoriel SQL Server, vous pouvez nettoyer les ressources que vous avez créées sur Google Cloud Platform afin d'éviter qu'elles ne vous soient facturées à l'avenir. Dans les sections suivantes, nous allons voir comment supprimer ou désactiver ces ressources.

Supprimer le projet

Le moyen le plus simple d'empêcher la facturation est de supprimer le projet que vous avez créé dans le cadre de ce tutoriel.

Pour supprimer le projet, procédez comme suit :

  1. Dans la console GCP, accédez à la page "Projets".

    Accéder à la page Projets

  2. Dans la liste des projets, sélectionnez celui que vous souhaitez supprimer, puis cliquez sur Supprimer.
  3. Dans la boîte de dialogue, saisissez l'ID du projet, puis cliquez sur Arrêter pour supprimer le projet.

Supprimer des instances

Pour supprimer une instance Compute Engine, procédez comme suit :

  1. Dans la console GCP, accédez à la page "Instances de VM".

    Accéder à la page Instances de VM

  2. Cochez la case à côté de Il s'agit de l'instance que vous souhaitez supprimer.
  3. Cliquez sur le bouton Supprimer en haut de la page pour supprimer l'instance.

Supprimer les disques persistants

Pour supprimer un disque persistant, procédez comme suit :

  1. Dans la Console GCP, accédez à la page Disques.

    Accéder à la page "Disques"

  2. Cochez la case située à côté du nom du disque à supprimer.

  3. Cliquez sur le bouton Supprimer en haut de la page.

Étapes suivantes

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…