Procedure-Systel/Production SI/Configuration serveur/Serveur BD_SQL.md

# 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 :

```powershell
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 :

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


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

```powershell
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 :

```powershell
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 :

```powershell
$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` :

```powershell
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 :

```sql
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 :

```sql
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é.

```sql
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 :

```sql
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.

```sql
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 :

```sql
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 :

```sql
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.