0

Production SQL Server : banalisation des instances

Posted on by Benjamin VESAN
twitterlinkedinmail

Septième article de la série Une approche pragmatique de la production SQL Server, c’est le couple normalisation/banalisation qui est abordé ici.

(On me glisse à l’oreillette que le terme « banalisation » n’est jamais employé dans le domaine de la base de données, mais puisque je le trouve assez bien adapté au concept que j’aborde ici, je l’utiliserai quand même !)

Par instance normalisée, j’entends instance installée selon un ensemble de règles définissant :

  • L’édition et la version de SQL Server,
  • l’emplacement des différents fichiers,
  • le nommage de l’instance,
  • le port TCP de l’instance,
  • Le mot de passe du login sa et la liste des logins disposant de forts privilèges,
  • La Collation de l’instance
  • Le nom et le contenu de la base contenant les scripts d’exploitation,
  • Le compte de service qui lance SQL Server et SQL Agent, et les privilèges de ce compte sur la machine

Par instance banalisée, j’entends instance configurée avec des valeurs fixes pour certains paramètres :

  • Mémoire allouée
  • Masque CPU
  • Paramètres de configuration tels que xp_cmdshell, backup compression default, clr enabled, cross db ownership, cost threshold for parallelism, max degree of parallelism

L’idée est donc de mettre à disposition une instance qui aura une configuration correspondant à peu près à tous les types d’utilisation.

Pourquoi banaliser ?

  • Déployer une instance banalisée est très simple. Il suffit de lancer un script de post installation pour fixer les valeurs des paramètres, qui peut être facilement inclus dans le processus de déploiement.
  • Mettre en place la supervision d’une instance banalisée est aussi très simple, puisque les seuils de consommation de ressources sont les mêmes.
  • Puisque les instances sont les mêmes, le DBA est toujours en environnement connu pour tout ce qui est tâches courantes d’exploitation et gestion d’incidents.

Faut-il tout banaliser ?

Bien sûr que non !

Une instance banalisée, par définition, n’est pas optimisée pour l’usage qui en est fait. Mais en nous basant sur la règle dite des 80/20, il semble réaliste de banaliser 80% des instances, ce qui permettra de concentrer la charge de travail du DBA sur les 20% des instances restantes (a priori les 20% les plus critiques).

Et concrêtement ?

Une fois vos règles de normalisation définies, utilisez l’option  « configuration file » de SQL Server Installation Center pour faciliter l’installation :

  1. Lancez une installation classique en vous arrêtant à la dernière étape. Rien ne sera installé, mais un fichier de configuration sera généré.
  2. Editez ce fichier en remplaçant certaines valeurs par des variables (nom d’instance, chemin des fichiers de données et de journaux, compte de service, …), afin d’en faire un modèle.

    3. Exemple de fichier “configurationfile.modele” issu d’une installation de SQL Server 2012:
    [OPTIONS]
    ACTION="Install"
    ENU="True"
    UIMODE="Normal"
    QUIET="False"
    QUIETSIMPLE="False"
    UpdateEnabled="False"
    FEATURES=SQLENGINE
    UpdateSource="MU"
    HELP="False"
    INDICATEPROGRESS="False"
    INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
    INSTANCENAME="%INSTANCE%"
    INSTANCEID="%INSTANCE%"
    SQMREPORTING="False"
    ERRORREPORTING="False"
    INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
    AGTSVCACCOUNT="%COMPTESERVICE%"
    AGTSVCPASSWORD="%MDPCOMPTESERVICE%"
    AGTSVCSTARTUPTYPE="Automatic"
    COMMFABRICPORT="0"
    COMMFABRICNETWORKLEVEL="0"
    COMMFABRICENCRYPTION="0"
    MATRIXCMBRICKCOMMPORT="0"
    SQLSVCSTARTUPTYPE="Automatic"
    FILESTREAMLEVEL="0"
    ENABLERANU="False"
    SQLCOLLATION="French_CI_AS"
    SQLSVCACCOUNT="%COMPTESERVICE%"
    SQLSVCPASSWORD="%MDPCOMPTESERVICE%"
    SQLSYSADMINACCOUNTS="%GROUPESYSADMIN%"
    SECURITYMODE="SQL"
    SQLBACKUPDIR="D:\BACKUP\%INSTANCE%"
    SQLUSERDBDIR="D:\DATA\%INSTANCE%"
    SQLUSERDBLOGDIR="D:\TLOG\%INSTANCE%"
    SQLTEMPDBDIR="D:\DATA\%INSTANCE%"
    SQLTEMPDBLOGDIR="D:\TLOG\%INSTANCE%"
    ADDCURRENTUSERASSQLADMIN="False"
    TCPENABLED="1"
    NPENABLED="0"
    BROWSERSVCSTARTUPTYPE="Automatic"

  3. Créez un fichier de commandes qui valorise les variables, génère à la volée un fichier de configuration puis appelle le setup.exe de SQL Server en utilisant le fichier de configuration généré.

    4. Exemple de script d’installation “install_sql.cmd”
    @echo off
    del configurationfile.ini
    echo Nom de l'instance à installer:
    set /p INSTANCE=
    echo Compte de Service windows:
    set /p COMPTESERVICE=
    echo Mot de passe du compte de Service windows
    set /p MDPCOMPTESERVICE=
    echo Groupe(s) Sysadmin:
    set /p GROUPESYSADMIN=

    for /f "delims=" %%i in ('type configurationfile.modele') do (
    call echo %%i>>configurationfile.ini
    )

    I:\setup.exe /configurationfile=configurationfile.ini

  4. Créez un script sql contenant toutes les modifications de configuration correspondant à votre banalisation, et lancez le après chaque installation (le lancement peut bien entendu être inclus dans le script d’installation…)

Exemple de script de banalisation “banalisation.sql”

/***************************************************************************
   ^
  / \
 / ! \	ATTENTION
/_____\

Les modifications proposées dans ce script auront un impact
sur le fonctionnement de votre instance SQL Server (performances, sécurité).
Il est donc impératif de prendre connaissance des modifications proposées et
de ne les mettre en place que lorsque les impacts sont maîtrisés.

Cap Data consulting ne saurait être tenue responsable des conséquences du
lancement de ce script sur un système.
**************************************************************************/
set nocount on
declare @commande varchar(200), @cpus int, @memoire_physique int, @MAX_DOP int, @MEMOIRE_INSTANCE int, @chemin_fichiers_tempdb nvarchar(520), @compteur int
select @cpus=cpu_count, @memoire_physique=physical_memory_in_bytes/1048576 from sys.dm_os_sys_info

SELECT	 @MAX_DOP=4				--Degré max de parallélisme
		,@MEMOIRE_INSTANCE=512 --Mémoire à allouer à l'instance

IF (@MAX_DOP>@cpus)select @MAX_DOP=@cpus
IF (@MEMOIRE_INSTANCE > @memoire_physique - 512)select @MEMOIRE_INSTANCE = @memoire_physique - 512 

-- Paramètres de l'instance
EXEC sp_configure 'show advanced options', 1
reconfigure
create table #parametres(nom nvarchar(35), valeur int)
insert into #parametres values	('max degree of parallelism', @MAX_DOP),
								('min server memory (MB)', @MEMOIRE_INSTANCE),
								('max server memory (MB)', @MEMOIRE_INSTANCE),
								('remote admin connections',1 ),
								('xp_cmdshell', 1),
								('backup compression default', 1)

DECLARE CURSEUR_PARAMETRES INSENSITIVE CURSOR FOR
select 'sp_configure '''+nom+''', '+cast(valeur as varchar(10)) from #parametres P
inner join sys.configurations C ON P.nom=C.name
WHERE P.valeur !=cast(C.value as int)
and P.valeur between cast(C.minimum as int) and cast(C.maximum as int)
order by nom 

OPEN CURSEUR_PARAMETRES
FETCH NEXT FROM CURSEUR_PARAMETRES  into @commande
WHILE(@@FETCH_STATUS=0)
BEGIN
	PRINT @commande
	EXEC(@commande)
	FETCH NEXT FROM CURSEUR_PARAMETRES  into @commande
END
CLOSE CURSEUR_PARAMETRES
DEALLOCATE CURSEUR_PARAMETRES
drop table #parametres

--Modification de TempDB
alter database tempdb modify file(name='tempdev', SIZE=128 MB, MAXSIZE = UNLIMITED, FILEGROWTH= 128 MB)
select @chemin_fichiers_tempdb=replace(physical_name,'tempdb.mdf','') from tempdb.sys.database_files where file_id=1
select @compteur=count(1)+1 FROM tempdb.sys.database_files where type_desc='ROWS'
WHILE(@compteur < =@cpus)
BEGIN
	set @commande='ALTER DATABASE tempdb ADD FILE (NAME=''tempdev_'+cast(@compteur as varchar(3))+''', FILENAME='''+@chemin_fichiers_tempdb+'tempdb_'+cast(@compteur as varchar(3))+'.ndf'', SIZE=128 MB, MAXSIZE=UNLIMITED, FILEGROWTH=128 MB)'
	PRINT @commande
	select @compteur = @compteur + 1
END

--Modification de Model
alter database model modify file(name='modeldev', MAXSIZE = UNLIMITED, FILEGROWTH= 128 MB)
alter database model modify file(name='modellog', MAXSIZE = UNLIMITED, FILEGROWTH= 128 MB)
alter database model set recovery SIMPLE

Enfin, sachez que grâce au mécanisme de “Policy Management” de SQL Server, vous disposez d’un moyen simple de vérifier régulièrement que la configuration des instances est toujours celle que vous avez défini à l’installation.

Continuez votre lecture sur le blog :

twitterlinkedinmail

Benjamin VESAN

More Posts

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.