[SQL Server] Configuration élémentaire d’une instance OLTP SQL Server 2014

Ce billet traite de quelques éléments de configuration usuels d’une instance OLTP (OnLine Transactional Processing, ou Database Engine) SQL Server 2014. Les exemples seront principalement réalisés sous SSMS (SQL Server Management Studio).

Pour savoir comment installer une instance SQL Server 2014, vous pouvez faire un saut ici.

 

Paramétrage de la mémoire

Activation d’AWE (SQL Server 32 bits seulement)

L’activation de /3GB et/ou de /USERVA et/ou de /PAE ne permettant pas à SQL Server de profiter pleinement de la RAM supplémentaire, c’est là qu’intervient toute l’utilité d’AWE (Address Windowing Extensions).

AWE est une fonctionnalité qui permet à des systèmes, basés sur une architecture 32 bits, d’accéder à de grandes quantités de mémoire.

Dans le cas de SQL Server, AWE n’est utile que si la mémoire physique disponible est supérieure à l’espace d’adressage virtuel en mode utilisateur, et que plus de 4 Go de RAM est demandé par l’instance SQL Server.

A partir de SQL Server 2012, l’activation d’AWE n’est plus possible. En revanche, vous pouvez effectuer le verrouillage de pages de mémoire en affectant le compte de service de l’instance SQL Server à la liste des utilisateurs autorisés à le faire. Plus de détails ici.


Paramétrage de Min server memory et de Max server memory

Vu que souvent, il devient nécessaire de laisser un peu de ressources mémoire à quelques petites applications systèmes, il faut idéalement donner une valeur de Maximum server memory de sorte qu’au moins 2 Go soient laissés à l’OS.

Pour ce qui est de la valeur de Min server memory, il est souhaitable d’allouer une taille d’au moins 1024 Mo (ou 1 Go). En effet, cela permettrait de s’assurer que le buffer pool (allocateur permettant de fournir de la mémoire aux pages de données) dispose toujours d’au moins 1 Go de mémoire (qui ne seront pas laissés à d’autres systèmes).

La taille minimale ne doit, tout de même, pas être supérieure à la taille maximale allouée à l’instance SQL Server.

Pour paramétrer le Min server memory et le Max server memory de l’instance SQL Server, suivez les étapes suivantes :

  • Au sein de SSMS, double-cliquez sur le nom de ladite instance, tout en haut de l’explorateur d’objets pour accéder à ses propriétés.


  • Dans la fenêtre modale qui s’ouvrira, allez dans l’onglet Mémoire.



Au sein de la section Memory, vous noterez que par défaut, la taille maximale de la mémoire consommable par le buffer pool de SQL Server est paramatré à 2 To. Autrement dit, SQL Server est configuré, par défaut, de sorte qu’il puisse consommer toute la RAM de l’OS (hors espace de mémoire réservé au kernel), ce qui n’est pas une bonne idée du point de vue des performances.

  • Paramétrez la taille minimale et maximale de la mémoire de SQL Server.

L’OS ayant 8 Go de RAM (dans notre cas), nous pouvons nous permettre de lui laisser 2 Go, affectant, par exemple, 6,4 Go maximum à SQL Server (et 1 Go minimum), ce qui est correct. Et du côté de la mémoire minimale, on procède à l’affection de 1 Go de RAM à SQL Server de sorte que le buffer pool dispose toujours d’au moins 1024 Mo de pages de mémoire non-utilisables par d’autres ressources :


Vous pouvez aller ici pour avoir une idée d’une bonne approche de paramétrage de la mémoire de SQL Server.

Notez bien que les configurations faites via le Min server memory et le Max server memory (qui ne nécessitent aucun redémarrage de l’instance SQL Server ou de l’OS) ne permettent que d’allouer (ou réduire) de la mémoire au buffer pool et non à toute l’instance SQL Server. Cela signifie que même si 6 Go de RAM sont affectés à (et consommés par) SQL Server, il est possible qu’il y ait un excédent de quelques Mo supplémentaires à cause de la possible utilisation de pages hors-normes (i.e., pages multiples de plus de 8 ko et/ou pages larges de 16 ko), sachant que le buffer pool ne fournit que des pages « simples » (i.e., de taille normalisée de l’ordre de 8 Ko). Les pages hors-normes sont puisées au sein de l’OS via le virtual memory allocator d’un nœud de mémoire SQL Server qui va utiliser l’API Windows VirtualAlloc.

 

Il est possible d’effectuer le paramétrage de la mémoire via T-SQL. Pour ce faire :

  • Lancez une fenêtre d’analyse de requêtes, sous SSMS (si ce n’est déjà fait).
  • Exécutez le script T-SQL suivant :
sp_configure
'show advanced options', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'min server memory', '1024'
sp_configure 'max server memory', '6400'
GO
RECONFIGURE WITH OVERRIDE
GO

 

Paramétrage du fill factor

Pour paramétrer le fill factor par défaut de l’instance SQL Server pour toutes les bases de données (et indexes), suivez les étapes suivantes :

  • Dans les propriétés de l’instance, allez dans l’onglet Database Settings puis modifiez la valeur du fill factor.

Comme vous pouvez le noter sur la figure ci-dessous, la valeur 85 a été choisie, ce qui est un bon compromis pour les indexes fréquemment accédés en écriture (mises-à-jour) et ceux fréquemment en accédés en lecture.


  • Cliquez sur OK pour valider la modification effectuée.

Via T-SQL, vous pouvez exécuter le script suivant au sein d’une fenêtre d’analyse de requête sous SSMS :

sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'fill factor', 85
GO
RECONFIGURE WITH OVERRIDE
GO

Spécification du chemin d’accès par défaut des fichiers de bases de données

Lors de la création d’une base de données (dans son ensemble), d’un fichier de données, d’un filegroup ou d’un fichier de logs, le moteur SQL choisit, par défaut, le disque où ont été installés les fichiers d’installation de SQL Server. Afin de mieux respecter la répartition des fichiers de données et de logs à chaque création d’une base de données, il faut donc définir le chemin d’accès par défaut desdits fichiers.

Pour ce faire, il vous suffira simplement d’accéder à Database Settings, au sein des propriétés du serveur, puis de modifier l’emplacement des fichiers de données, de logs et de sauvegardes par défaut des bases de données de l’instance :


Vous pouvez également modifier l’emplacement par défaut des sauvegardes via SSMS. Bien que l’exemple ci-dessus peut paraître trompeur, l’emplacement concerne tout type de sauvegardes (complètes, différentielles, transactionnelles).

Paramétrage réseau

N’hésitez pas à jeter un coup d’œil ici.

Protocoles et accès distants

Par défaut, le protocole Shared Memory (Mémoire partagée) est activé, ainsi que le protocole TCP/IP et cela, contrairement au protocole Named Pipes (Canaux nommés).

Vous pouvez y jeter un coup d’œil au sein de SQL Server Configuration Manager et l’activer comme suit :


Cette modification nécessitera un redémarrage de l’instance SQL Server (soit via services.msc, soit via l’onglet Services SQL Server de SQL Server Configuration Manager, soit via clic-droit sur le nom de l’instance au sein de l’explorateur d’objets de SSMS, puis sélection de Restart).

Du côté des accès distants, il est nécessaire de s’assurer que l’option Allow Remote Access, au sein de l’instance SQL Server, est activée, et l’activer le cas échéant.

Comment s’y prendre ? Deux approches possibles :

  • Via SQL Server Configuration Manager :
    • Accédez aux propriétés de l’instance SQL Server (via clic-droit sur le nom de l’instance, tout en haut de l’explorateur d’objets de SSMS, comme vu dans les sections précédentes).
    • Allez dans Connections puis activez Allow remote connections to this server.


  • Via T-SQL :

 

 

sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'remote access', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Notez que :

  • Au sein de l’onglet Connections, vous pouvez également paramétrer le nombre maximum de connexions concurrentes (par défaut à 0, c’est-à-dire illimité).
  • Si vous souhaitez que l’instance puisse travailler avec des requêtes distribuées (via OPENROWSET ou OPENDATASOURCE), il faut également activer la fonctionnalité Ad Hoc Distributed Queries. Cela peut se faire comme suit :
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OVERRIDE
GO

 

Pour des raisons de sécurité, n’activez Ad Hoc Distributed Queries que si vous comptez utiliser OPENROWSET ou OPENDATASOURCE au lieu d’un linked server (avec OPENQUERY).

En outre, notez également que l’option Require distributed transactions for server-to-server communication qui permet de renforcer la sécurité des transactions distribuées via le MSDTC (Microsoft Distributed Transaction Coordinator). Son utilisation n’est pas recommandée car vouée à disparaître (voir ici : http://msdn.microsoft.com/en-us/library/ms190773.aspx).

 

SQL Server Browser et configuration de ports d’écoute

Par défaut, le moteur SQL écoute sur le port statique 1433. Et de manière générale, sauf cas particuliers, il n’est pas nécessaire de modifier les configurations par défaut des ports d’écoute de SQL Server.

Toutefois, si vous privilégiez l’utilisation de ports dynamiques, et que vous disposez de plusieurs moteurs SQL au sein du même serveur, il est recommandé d’activer le SQL Server Browser afin qu’il puisse fournir les bons ports aux bonnes connexions. Ce dernier (qui utilise le port UDP 1434) permet de récolter les informations de connexion d’une instance (notamment son port d’écoute) pour ensuite les fournir à toute requête de connexion.

S’il est généralement requis dans le cas d’instances utilisant des ports TCP/IP dynamiques c’est simplement parce qu’il se charge, lui-même, de résoudre le numéro de port de chaque instance concernée par une connexion, ce qui peut être utile si, entre-temps, ledit port a changé de numéro (suite l’utilisation de l’ancien numéro de port par un autre processus après un redémarrage, par exemple).

Du fait donc de la sensibilité de son travail, il est plus recommandé qu’en cas d’activation, un compte de service avec des droits minimaux lui soit attribué. Et cela, pour des raisons de sécurité.

Notez que le SQL Server Browser est relativement inutile dans le cas où, au sein d’un serveur, il n’y a pas plusieurs moteurs SQL d’installé (comme c’est notre cas ici).

Bien que cela puisse décharger l’utilisateur de toute nécessité de spécifier manuellement un port à chaque moteur SQL à installer, il est recommandé, dans le cas d’un cluster, d’opter pour un port statique (choix par défaut de SQL Server), et non dynamique.

 

Sur le plan pratique, pour :

  • Activer le SQL Server Browser :

En ligne de commande vous pouvez simplement taper net start sqlbrowser. Ou bien, au sein de SQL Server Configuration Manager, allez dans SQL Server Services puis double-cliquez sur SQL Server Browser pour le démarrer :


Assurez-vous, au préalable, qu’il ne soit pas désactivé, sinon activez-le en accédant à ses propriétés (via clic-droit) :


  • Spécifier un numéro et type de port (dynamique ou statique) :

Allez dans le SQL Server Configuration Manager, puis dans la section SQL Server Network Configuration\Protocols for SQL2014 (nom de l’instance), puis double-cliquez sur TCP/IP pour accéder à ses propriétés :


Cela aura pour effet de lancer une boîte de dialogue au sein de laquelle, il faut aller dans l’onglet IP Adresses :


L’instance pouvant disposer de plusieurs IPs (et ports), vous pouvez spécifier pour chaque IP le numéro de port et son type (statique ou dynamique). Pour utiliser un port dynamique, spécifiez la valeur 0 pour TCP Dynamic Ports. Si vous souhaitez juste utiliser un port statique, spécifiez la valeur adéquate pour TCP Port au sein de chaque IP concernée, et assurez-vous que TCP Dynamic Ports soit vide.

Notez que vous pouvez directement effacer toute éventuelle valeur des deux types de ports pour chaque IP (IP1, IP2, IP3,…, IPn), puis, ensuite, spécifier la valeur souhaitée pour le type de port choisi au sein d’IPAll :


Paramétrage du MAXDOP (Maximum degree of parallelism)

Pour configurer le MAXDOP de SQL Server (on suppose que l’on possède 4 CPUs, ce qui reviendrait donc à en affecter conventionnellement 2 à SQL Server), vous pouvez le faire :

  • Soit en accédant dans les propriétés de l’instance SQL Server via SSMS, et dans l’onglet Avancé pour (dé)cocher les processeurs que l’on souhaite utiliser :


     

  • Soit en exécutant la commande suivante au sein de l’analyse de requêtes de SSMS :
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure N'max degree of parallelism', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO

 

Notons qu’il s’agit seulement d’instructions dites « générales ». Il peut, en effet, arriver de fournir exceptionnellement un nombre maximum de processeurs à l’instance SQL Server si elle est régulièrement emmenée à traiter des opérations impliquant un très grand nombre de ressources. Et c’est le cas, par exemple, des traitements analytiques.

 

Pour aller plus loin…

Vous pouvez garder un œil ici si vous souhaitez consulter des billets relatifs à l’installation et configuration de SQL Server 2014.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s