Le raccourci vers la maîtrise de SQL Server 2025 sur un projet réel

Cette première section de scripts jette les fondations physiques et logiques de l'environnement de données.

• 001 - SCRIPT_CREATE_SERVER_ADMIN_LOG.sql & 005 - SCRIPT_CREATE_LOG_USER_ROLE.sql : L'architecture de sécurité est remarquable. Elle s'appuie sur le principe de moindre privilège. La création de SERVER_ADMIN_LOG avec CHECK_POLICY = ON garantit que le compte maître respecte les stratégies de mot de passe de l'OS. Le script 005 isole les droits : Application_User subit un DENY SELECT, INSERT, UPDATE, DELETE et ne peut qu'exécuter des procédures stockées (GRANT EXECUTE). L'audit des utilisateurs masque spécifiquement les hachages avec un accès de niveau colonne (Column-Level Security).
• 002 - SCRIPT_CREATE_DATABASE.sql : L'optimisation physique des disques est digne d'un environnement de production lourd. Le fichier .mdf (disque D:), le .ndf (groupe secondaire sur le disque E:), et le journal .ldf (disque F:) sont séparés physiquement pour éliminer la contention I/O entre lectures aléatoires et écritures séquentielles. La croissance automatique (FILEGROWTH) est bloquée sur une valeur fixe (10MB) pour éviter la fragmentation.
• 003 - SCRIPT_CREATE_TABLE.sql & 004 - SCRIPT_ALTER_DATABASE_TABLE.sql : Ce script colossal révèle l'ossature métier. Quatre schémas isolent logiquement les données (Metier, Geographie, Securite, Audit). L'utilisation du SYSTEM_VERSIONING = ON couplé au Ledger garantit une traçabilité inviolable. L'utilisation de types adéquats, de contraintes CHECK stricts, de colonnes calculées (PERSISTED) et de l'objet SEQUENCE pour les clés primaires montre une maîtrise avancée de la conception relationnelle.
• 006 à 009 (Initialisation et CRUD Data) : Ces scripts assurent le chargement des données de référence et l'initialisation des séquences et jointures de départ, offrant un déploiement "clé en main".

Cette section constitue le cœur opérationnel. Une règle d'or est appliquée scrupuleusement : le principe de responsabilité unique (SRP).

• Scripts d'AJOUT & de VALIDATION AJOUT : Pour les entités Locataires, Propriétaires, Biens, Contrats et Utilisateurs, chaque insertion physique (le bloc TRY...CATCH avec l'INSERT) est totalement découplée de sa validation métier.
• Scripts de MODIFICATION & SUPPRESSION : L'architecture privilégie la suppression logique (Soft Delete) en basculant le flag Supprimer_X = 1 plutôt que le DELETE physique. Des scripts de "Récupération" sont prévus pour annuler une suppression.
• Fonctions Spécialisées : Le code utilise des iTVF (Inline Table-Valued Functions). Contrairement aux fonctions scalaires qui détruisent les performances (RBAR), les iTVF s'exécutent aussi vite qu'une vue.
• Intégration JSON Native et clause MERGE : Les autorisations de l'utilisateur sont envoyées à la base sous forme d'un tableau JSON brut. SQL Server le décompose nativement via OPENJSON(). Le résultat est ensuite injecté dans une clause MERGE qui effectue la synchronisation en une seule passe transactionnelle !
• Sécurité & Authentification Niveaux Expert : Le "Sel" cryptographique est généré dynamiquement par SQL Server lui-même via CRYPT_GEN_RANDOM(16). Ce sel est concaténé au mot de passe avant hachage (SHA2_512), rendant toute attaque par dictionnaire mathématiquement impossible.

Ces 10 scripts exposent les données à l'application.

• Scripts spSELECTION_* : Chacune de ces procédures de sélection multicritère utilise le SQL dynamique de la bonne manière via sp_executesql en passant les paramètres. C'est la méthode de référence pour annuler tout risque d'Injection SQL. De plus, les clauses de tri sont protégées par QUOTENAME().
• Toutes les requêtes renvoient une structure JSON formatée directement depuis le moteur SQL via FOR JSON PATH, soulageant considérablement le backend.

L'audit de la base de données est l'un de ses points les plus robustes.

• Les Déclencheurs : Les Triggers sont codés de manière purement ensembliste ("Set-based"). L'utilisation de l'opérateur IS DISTINCT FROM est la touche d'expertise : le trigger ne trace que ce qui a réellement changé.
• Exploitation de l'historique temporel : Les procédures utilisent intensivement les CTEs et les Fonctions de fenêtrage. Le chef-d'œuvre technique se trouve dans l'utilisation de CROSS APPLY (VALUES (...)) pour accomplir un Unpivot dynamique.
• Fenêtrage Analytique : Illustration pure du T-SQL avancé pour calculer des moyennes par groupes sans perdre le détail avec OVER(PARTITION BY...).

Les 8 derniers scripts concernent l'administration système et la résilience (Plan de Reprise d'Activité).

• Sauvegardes et Certificats : La base subit des sauvegardes avec CHECKSUM et COMPRESSION. Toutes les sauvegardes sont cryptées en AES-256 à l'aide d'un certificat serveur asymétrique.
• Automatisation SQL Agent et Alerte : Le script 103 déploie l'automatisation complète de la stratégie RPO : Base Complète quotidienne, Différentielle toutes les 6h, et Journal de transactions toutes les 15 minutes.

Le packaging et la restaurabilité de la base de données démontrent une automatisation digne d'une approche DevOps.

• Le Master Script : Le fichier batch génère automatiquement le script maître en itérant sur les fichiers. Il utilise la directive SQLCMD pour concaténer logiquement l'exécution.
• Générateur de Script PRA Point-in-Time : Cette procédure stockée interroge les tables système msdb.dbo.backupset pour retrouver dynamiquement la chronologie exacte des logs nécessaires. Elle génère dynamiquement le script T-SQL complet de restauration, assurant un RTO extrêmement rapide en cas de crise !