maxime/Procedure-Systel
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
Procedure-Systel/Production SI/Configuration serveur/Serveur BD_SQL.md
T

18 KiB
Raw Permalink Blame History

Configuration d'un serveur BD_SQL

Prérequis

Suivre la procédure Exporter et importer une VM depuis la plateforme de Production pour importer les VM BDx (SQL) et SQLWitness dans l'environnement client.

Préparer les serveurs

Suivre la procédure Préparation d'un serveur Windows pour les serveurs BD. La VM SQLWitness utilise Windows Server Core.

Fichier d'échange (SWAP)

Configurer le fichier d'échange des serveurs BD sur le disque F: avec une taille de 4096 Mo. Après Sysprep, vérifier que ce paramètre est toujours appliqué.

Configurer la VM SQLWitness

Après l'import, ouvrir la VM en mode console depuis l'hyperviseur.

Configuration réseau

Dans l'interface de configuration, saisir 15 pour ouvrir la ligne de commande.

Exécuter Get-NetAdapter et ipconfig afin de relever l'index (ifIndex), l'adresse IP et la passerelle de l'interface :

Supprimer la configuration actuelle de la carte en remplaçant l'index et les valeurs par les informations relevées précédemment :

Remove-NetIPAddress -InterfaceIndex 6 -IPAddress xxx.xxx.xxx.xxx -PrefixLength 24 -DefaultGateway xxx.xxx.xxx.xxx

Enregistrer la nouvelle configuration de la carte en remplaçant les valeurs par celles du DCSR :

New-NetIPAddress -InterfaceIndex 6 -IPAddress xxx.xxx.xxx.xxx -PrefixLength 24 –DefaultGateway xxx.xxx.xxx.xxx

Revenir à l'écran de configuration avec la commande :

sconfig

Saisir 8 afin de configurer l'interface réseau, puis sélectionner l'interface principale.

Choisir 2 pour configurer les serveurs DNS associés à la carte réseau.

Joindre le domaine Active Directory

Dans sconfig, saisir 1 pour configurer le nom de domaine. Entrer les informations demandées, puis redémarrer le serveur.

Après le redémarrage, se connecter en tant qu'administrateur du domaine. Ajouter le compte AD SQLWitness au groupe local Administrateurs du serveur, en remplaçant xx par le numéro de département :

Add-LocalGroupMember -Group "Administrateurs" -Member "CTAxx\SQLWitness"

Fermer ensuite la session d'administrateur du domaine et ouvrir une session avec le compte SQLWitness.

Comptes locaux

Dans sconfig, saisir 15 pour ouvrir PowerShell.

Le compte Administrateur est désactivé par défaut. Définir un nouveau mot de passe pour les comptes locaux concernés :

$Password = Read-Host "Enter the new password" -AsSecureString
Set-LocalUser -name "AdminLocal" -Password $Password
Set-LocalUser -name "Administrateur" -Password $Password

Créer le partage SQLWitness

Les commandes suivantes suppriment le partage existant et son contenu, puis recréent le partage SQL_Witness :

Remove-SmbShare -Name "SQL_Witness"
Get-ChildItem C:\SQL_Witness\* | Remove-Item -recurse -force
New-SmbShare -Name SQL_Witness -Path C:\SQL_Witness -FullAccess "Administrateurs"
Grant-SmbShareAccess -Name "SQL_Witness" -AccountName "CTAXX\GRP_BD_T1" -AccessRight Full -Force

Créer le cluster Windows

  1. Sur un serveur AD, ouvrir le Gestionnaire DNS.
  2. Accorder au groupe Utilisateurs authentifiés les droits requis sur l'hôte BDWINVIP, conformément à la configuration de référence.
  3. Sur BD1, connecté avec un compte administrateur du domaine, ouvrir le Gestionnaire du cluster de basculement.
  4. Faire un clic droit sur Gestionnaire du cluster, puis choisir Créer le cluster.
  5. Sélectionner les deux serveurs SQL et exécuter les tests de validation.
  6. Nommer le cluster BDWINVIP et saisir l'adresse IP de sa VIP conformément au DCSR.
  7. Décocher l'option de stockage, puis terminer l'assistant.

Ajouter le quorum (témoin de partage de fichiers)

  1. Dans le Gestionnaire du cluster de basculement, ouvrir Configurer les paramètres du quorum du cluster.
  2. Sélectionner Configuration avancée et conserver tous les nœuds.
  3. Choisir Configurer un témoin de partage de fichiers.
  4. Parcourir les partages et sélectionner le partage créé sur SQLWitness.
  5. Valider la configuration et vérifier que le témoin apparaît en état opérationnel.

Sélectionner configuration avancée :

Laisser à défaut avec tous les noeuds :

Sélectionner Configurer un témoin de partage de fichiers :

Via le bouton Parcourir, aller chercher le partage sur le serveur SQLWITNESS :

Valider une fois effectué :

Vous devriez obtenir un résultat similaire :

Configurer SQL Server

Reconnecter les serveurs BD concernés avec les comptes de domaine prévus et fermer les sessions ouvertes avec le compte Administrateur du domaine. SQL Server est préinstallé et configuré dans le modèle de VM.

  1. Ouvrir SQL Server Configuration Manager 2022 depuis le raccourci du bureau.
  2. Arrêter les trois services SQL.

Configurer les comptes de service SQL du domaine avec leurs mots de passe à jour et vérifier que leur type de démarrage est Automatique.

Activer également Always On sur le service SQL Server destiné au cluster de bases de données. Ne pas effectuer cette étape sur les serveurs BDF et BDT.

Redémarrer SQL Server et SQL Server Agent depuis le gestionnaire de configuration.

Une fois les serveurs redémarrés, ouvrir SQL Server Management Studio et se connecter avec le compte sa. Récupérer son mot de passe dans RDM; ne pas le conserver dans cette procédure.

Renommer l'instance SQL

Après le changement de nom du serveur, vérifier le nom configuré dans SQL Server :

SELECT @@SERVERNAME AS NomServeurConfig,
       SERVERPROPERTY('MachineName') AS NomMachine,
       SERVERPROPERTY('ServerName') AS NomServeurComplet;

Exécuter le script suivant sur chaque serveur pour remplacer l'ancien nom par le nom actuel de la machine :

DECLARE @AncienNom NVARCHAR(128);
DECLARE @NouveauNom NVARCHAR(128);

-- Récupération de l'ancien nom configuré dans SQL Server
SET @AncienNom = @@SERVERNAME;

-- Récupération du hostname réel de la machine
SET @NouveauNom = CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(128));

PRINT 'Ancien nom du serveur : ' + ISNULL(@AncienNom, 'Non défini');
PRINT 'Nouveau nom du serveur (hostname machine) : ' + @NouveauNom;

-- Supprimer l'ancien nom
IF @AncienNom IS NOT NULL
BEGIN
    EXEC sp_dropserver @server = @AncienNom;
END

-- Ajouter le nouveau nom
EXEC sp_addserver @server = @NouveauNom, @local = 'local';
GO

Redémarrer SQL Server, puis relancer la première requête pour vérifier que les noms correspondent.

Restaurer la base StartDB

Restaurer la base StartDB à partir d'une sauvegarde du client. Cette restauration n'est pas nécessaire sur BD2.

Sélectionner le fichier .bak du client.

Dans Fichiers, cocher l'option permettant de déplacer les fichiers vers les répertoires appropriés.

Dans Options, cocher Remplacer, décocher l'option de sauvegarde, puis valider avec OK.

La restauration peut prendre quelques minutes.

Dans les propriétés de StartDB, régler le mode de récupération sur Complet pour BD1/BD2 et sur Simple pour les autres serveurs.

Si le niveau de compatibilité est inférieur à 2012, ouvrir D:\Requetes et exécuter Migration_SQL_2012.sql.

Si le niveau de compatibilité est 2012, il peut être porté à 2014, mais pas au-delà selon la consigne de cette procédure.

Sauvegarder avant Always On

Effectuer une sauvegarde avant de configurer le cluster Always On. Dans SQL Server Management Studio, ouvrir Sauvegarder et renseigner les onglets Général et Options de support selon la configuration validée pour le client.

Valider avec OK, puis suivre l'avancement dans Progression.

Configurer le cluster SQL Always On (BD1/BD2 uniquement)

  1. Sur un serveur AD, ouvrir le Gestionnaire DNS et supprimer l'hôte BDSQLVIP; il sera recréé automatiquement lors de la configuration du listener.
  2. Sur l'AD, ouvrir Active Directory Users and Computers et activer les Fonctionnalités avancées.
  3. Ouvrir les propriétés de l'OU T1-SERVERS.
  4. Dans l'onglet Sécurité, ajouter l'ordinateur BDWINVIP et lui accorder le contrôle total sur l'OU selon les autorisations validées.
  5. Sur BD2, supprimer la base StartDB.
  6. Sur BD1, dans SQL Server Management Studio, créer un nouveau groupe de disponibilité.

Si l'erreur suivante apparaît (généralement suite à une recréation du cluster)

Il sera nécessaire de désactiver puis de réactiver la fonctionnalité AlwaysOn en se basant sur la documentation Microsoft disponible ici : https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/enable-and-disable-always-on-availability-groups-sql-server?view=sql-server-ver16

Dans l'assistant, saisir DBSQLHA comme nom du groupe de disponibilité.

Sélectionner la base StartDB, puis cliquer sur Suivant.

Cliquer sur Add Replica et ajouter le serveur BD2.

Activer Automatic Failover pour les deux serveurs.

Dans l'onglet Listener, créer la VIP SQL BDSQLVIP avec l'adresse IP indiquée dans le DCSR, puis cliquer sur Suivant.

Sélectionner Amorçage automatique, puis cliquer sur Suivant.

Vérifier que tous les contrôles de validation sont réussis, puis cliquer sur Suivant.

Une fois la configuration terminée et validée, cliquer sur Close.

Vérifier que le cluster SQL apparaît dans la section HA.

Une fois le cluster créé, régler le paramètre concerné sur Élevé afin d'optimiser le temps de démarrage, conformément à la configuration de référence.

Configurer les comptes SQL

Trois comptes SQL sont utilisés :

  • start : compte applicatif utilisé par les applications Start et associé à la base StartDB.
  • admin : compte utilisé pour administrer la base (configuration, requêtes, etc.).
  • sa : compte super-administrateur, à utiliser uniquement en dernier recours.

Dans l'onglet General, modifier les mots de passe admin et sa, puis les enregistrer dans RDM. Le mot de passe sa doit comporter 20 caractères avec des majuscules, des minuscules et des chiffres.

Créer le compte start sur BD1

Exécuter le script suivant sur BD1. Remplacer la valeur d'exemple de @Password par le mot de passe défini et stocké dans RDM, puis relever le SID retourné.

USE [master];

DECLARE @UserName SYSNAME = N'start';  
DECLARE @Password NVARCHAR(128) = N'MonMotDePasseSécurisé';  
DECLARE @DBName SYSNAME = N'StartDB';  

------------------------------------------------------------
-- 1. Supprimer le login s'il existe déjà
------------------------------------------------------------
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @UserName)
BEGIN
    EXEC(N'DROP LOGIN [' + @UserName + N']');
    PRINT 'INFO Ancien login supprimé.';
END;

------------------------------------------------------------
-- 2. Créer le login
------------------------------------------------------------
EXEC(N'CREATE LOGIN [' + @UserName + N'] 
      WITH PASSWORD = N''' + @Password + N''', 
           CHECK_POLICY = OFF, 
           CHECK_EXPIRATION = OFF;');

PRINT 'OK Login créé.';

------------------------------------------------------------
-- 3. Supprimer l’utilisateur dans la base s’il existe
------------------------------------------------------------
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '
USE [' + @DBName + '];

IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''' + @UserName + ''')
BEGIN
    DROP USER [' + @UserName + '];
    PRINT ''INFO Ancien utilisateur supprimé.'';
END;
';
EXEC(@SQL);

------------------------------------------------------------
-- 4. Créer l’utilisateur lié au login
------------------------------------------------------------
SET @SQL = '
USE [' + @DBName + '];

CREATE USER [' + @UserName + '] FOR LOGIN [' + @UserName + '] WITH DEFAULT_SCHEMA = [dbo];
PRINT ''OK Utilisateur créé.';

-- Ajouter au rôle db_owner
ALTER ROLE [db_owner] ADD MEMBER [' + @UserName + '];
PRINT ''OK Utilisateur ajouté au rôle db_owner.'';
';
EXEC(@SQL);

------------------------------------------------------------
-- 5. Récupérer le SID du login
------------------------------------------------------------
SELECT name, sid 
FROM sys.server_principals 
WHERE name = @UserName;

Une fois le SID récupéré sur BD1, exécuter le script suivant sur BD2 pour recréer le login start avec le même mot de passe et le SID obtenu sur BD1. Remplacer les valeurs d'exemple avant l'exécution :

USE [master];

DECLARE @UserName SYSNAME = N'start';  
DECLARE @Password NVARCHAR(128) = N'MonMotDePasseSécurisé';  
DECLARE @SID VARBINARY(85) = 0x5077C0831A396E4692011149BD90C403; -- Remplacer par le SID du 1er serveur
DECLARE @DBName SYSNAME = N'StartDB';  -- Nom de la base à contrôler

------------------------------------------------------------
-- 1. Supprimer et recréer le login avec le bon SID
------------------------------------------------------------
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @UserName)
BEGIN
    EXEC(N'DROP LOGIN [' + @UserName + N']');
    PRINT 'INFO Ancien login supprimé.';
END;

DECLARE @CreateLoginSQL NVARCHAR(MAX);

SET @CreateLoginSQL = N'CREATE LOGIN [' + @UserName + N'] 
      WITH PASSWORD = N''' + @Password + N''', 
           CHECK_POLICY = OFF, 
           CHECK_EXPIRATION = OFF, 
           SID = ' + CONVERT(NVARCHAR(MAX), @SID, 1) + N';';

EXEC(@CreateLoginSQL);

PRINT 'OK Login recréé avec le SID du premier serveur.';

------------------------------------------------------------
-- 2. Récupérer le SID du login
------------------------------------------------------------
SELECT name, sid 
FROM sys.server_principals 
WHERE name = @UserName;

Le SID du login start sur BD2 doit correspondre au SID du login sur BD1.

Le script suivant compare le SID du login et celui de l'utilisateur start dans StartDB. Sur un réplica qui n'autorise pas la lecture de la base, le script renvoie un message d'information.

USE [master];

DECLARE @UserName SYSNAME = N'start';  
DECLARE @DBName SYSNAME = N'StartDB';  
DECLARE @LoginSID VARBINARY(85);  
DECLARE @UserSID VARBINARY(85);  
DECLARE @SQL NVARCHAR(MAX);  
DECLARE @RoleDesc NVARCHAR(60);  
DECLARE @DbState NVARCHAR(60);

------------------------------------------------------------
-- 1. Récupérer le SID du login (instance)
------------------------------------------------------------
SELECT @LoginSID = sid 
FROM sys.server_principals 
WHERE name = @UserName;

------------------------------------------------------------
-- 2. Vérifier le rôle du réplica local (PRIMARY ou SECONDARY)
------------------------------------------------------------
SELECT @RoleDesc = rs.role_desc
FROM sys.dm_hadr_availability_replica_states rs
JOIN sys.availability_replicas r 
     ON rs.replica_id = r.replica_id
WHERE rs.is_local = 1;

------------------------------------------------------------
-- 3. Vérifier l’accessibilité de la base
------------------------------------------------------------
SELECT @DbState = state_desc 
FROM sys.databases 
WHERE name = @DBName;

------------------------------------------------------------
-- 4. Si la base est accessible (READ_WRITE ou READ_ONLY), comparer
------------------------------------------------------------
IF @DbState IN ('ONLINE') 
BEGIN
    BEGIN TRY
        SET @SQL = '
        SELECT @UserSID_OUT = sid 
        FROM [' + @DBName + '].sys.database_principals 
        WHERE name = @UserName_IN;
        ';

        EXEC sp_executesql 
            @SQL, 
            N'@UserName_IN SYSNAME, @UserSID_OUT VARBINARY(85) OUTPUT', 
            @UserName_IN = @UserName, 
            @UserSID_OUT = @UserSID OUTPUT;

        SELECT 
            @RoleDesc AS [ReplicaRole],
            @DBName AS [Base],
            @UserName AS [Nom],
            @LoginSID AS [SID_Login_Instance],
            @UserSID AS [SID_Utilisateur_Base],
            CASE 
                WHEN @LoginSID IS NULL THEN 'NOK Le login n’existe pas sur l’instance'
                WHEN @UserSID IS NULL THEN 'NOK L’utilisateur n’existe pas dans la base'
                WHEN @LoginSID = @UserSID THEN 'OK : Login et utilisateur ont le même SID'
                ELSE 'WARNING Problème : SID différents (utilisateur orphelin)'
            END AS [Résultat];
    END TRY
    BEGIN CATCH
        SELECT 
            @RoleDesc AS [ReplicaRole],
            @DBName AS [Base],
            @UserName AS [Nom],
            @LoginSID AS [SID_Login_Instance],
            NULL AS [SID_Utilisateur_Base],
            'INFO : Impossible de lire la base sur ce réplica (erreur capturée)' AS [Résultat];
    END CATCH
END
ELSE
BEGIN
    SELECT 
        @RoleDesc AS [ReplicaRole],
        @DBName AS [Base],
        @UserName AS [Nom],
        @LoginSID AS [SID_Login_Instance],
        NULL AS [SID_Utilisateur_Base],
        'INFO : Base non accessible en lecture sur ce réplica secondaire' AS [Résultat];
END;

Si le résultat indique que le login et l'utilisateur ont le même SID, aucune action supplémentaire n'est nécessaire.

Vérifier les jobs SQL

Les jobs SQL sont préconfigurés. Vérifier qu'ils sont activés et qu'ils s'exécutent correctement.

Mettre à jour les paramètres en base

Les modèles de requêtes se trouvent dans D:\Requetes.

MiseAJourDesParametresEnBaseSTART.sql

Avant l'exécution, remplacer les valeurs XX par les adresses IP ou noms correspondant à l'infrastructure. Comparer les valeurs avec celles déjà présentes dans StartDB et identifier les paramètres à modifier dans les tables concernées.

Exemple de vérification :

SELECT * FROM PARAMETRES_CTA_COMPLEMENTAIRES;

MiseAJourDesParametresPOSTES.sql

Avant l'exécution, remplacer les valeurs XX par les adresses IP ou noms correspondant à l'infrastructure. Comparer les valeurs avec celles déjà présentes dans StartDB.

Exemple de vérification :

SELECT * FROM UC;

Scripts d'import BDF

Ajouter le compte BDF au groupe local Administrateurs du serveur MULTIF, comme dans l'environnement de test. Cette autorisation permet au script d'import de fermer les applications.

Dans D:\Import_PROD, adapter Restauration_base_OPE_vers_FORM.bat aux noms des serveurs, applications, machines virtuelles et paramètres de l'infrastructure. Les mots de passe doivent être récupérés dans RDM et ne doivent pas être inscrits dans le script ou cette procédure.

Adapter également D:\Requetes\MiseAJourDesParametresEnBaseSTART-FORM.sql. Le script de production peut servir de base après adaptation à l'environnement de formation.

Reference in New Issue View Git Blame Copy Permalink