Comment déployer Azure SQL Database en 2026

#!/bin/bash

RESOURCE_GROUP="rg-sql-tutorial-2026"
LOCATION="francecentral"

az group create \
  --name $RESOURCE_GROUP \
  --location $LOCATION \
  --tags project=tutorial environment=dev

# Vérifier la création
echo "Resource group créé : $(az group show --name $RESOURCE_GROUP --query 'name' -o tsv)"

# Variables pour les étapes suivantes
export RESOURCE_GROUP=$RESOURCE_GROUP
export LOCATION=$LOCATION

#!/bin/bash

SQL_SERVER_NAME="sqlserver-tutorial-$(date +%s)"
ADMIN_LOGIN="sqladmin2026"
ADMIN_PASSWORD="MonP@ssw0rdTr3sS3cur1s2026!"

az sql server create \
  --name $SQL_SERVER_NAME \
  --resource-group $RESOURCE_GROUP \
  --location $LOCATION \
  --admin-user $ADMIN_LOGIN \
  --admin-password $ADMIN_PASSWORD \
  --enable-public-network true \
  --minimal-tls-version 1.2 \
  --version 16.0

# Activer Microsoft Entra admin (optionnel, nécessite Entra ID)
# az sql server ad-admin create --server-name $SQL_SERVER_NAME --resource-group $RESOURCE_GROUP --display-name "azuread-admin" --object-id "votre-object-id"

export SQL_SERVER_NAME

# Full server name pour connexion
echo "Serveur SQL : ${SQL_SERVER_NAME}.database.windows.net"

#!/bin/bash

DB_NAME="sqldb-tutorial"

az sql db create \
  --resource-group $RESOURCE_GROUP \
  --server $SQL_SERVER_NAME \
  --name $DB_NAME \
  --service-objective S0 \
  --compute-model Serverless \
  --min-capacity 0.5 \
  --max-size 32GB \
  --sample-name AdventureWorksLT \
  --edition GeneralPurpose

export DB_NAME

# Vérifier
az sql db show --resource-group $RESOURCE_GROUP --server $SQL_SERVER_NAME --name $DB_NAME --query '{name:name, status:status}' -o table

#!/bin/bash

# Règle pour votre IP actuelle (trouvée via curl ifconfig.me)
CLIENT_IP=$(curl -s ifconfig.me)

az sql server firewall-rule create \
  --resource-group $RESOURCE_GROUP \
  --server $SQL_SERVER_NAME \
  --name AllowClientIP \
  --start-ip-address $CLIENT_IP \
  --end-ip-address $CLIENT_IP

# Règle pour Azure services (ex: App Service)
az sql server firewall-rule create \
  --resource-group $RESOURCE_GROUP \
  --server $SQL_SERVER_NAME \
  --name AllowAzureServices \
  --start-ip-address 0.0.0.0 \
  --end-ip-address 0.0.0.0

# Activer auditing
az sql server update \
  --resource-group $RESOURCE_GROUP \
  --name $SQL_SERVER_NAME \
  --set securityPolicy.state=Enabled securityPolicy.auditingPolicy.auditActionsAndGroups='SCHEMA_OBJECT_CHANGE_GROUP'

-- Connexion via sqlcmd ou Azure Data Studio:
-- sqlcmd -S ${SQL_SERVER_NAME}.database.windows.net -d ${DB_NAME} -U ${ADMIN_LOGIN} -P ${ADMIN_PASSWORD} -G

USE [sqldb-tutorial];

-- Créer table Users avec clustered index
CREATE TABLE Users (
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    Email NVARCHAR(255) NOT NULL UNIQUE,
    Name NVARCHAR(100) NOT NULL,
    CreatedAt DATETIME2 DEFAULT SYSDATETIMEOFFSET(),
    IsActive BIT DEFAULT 1
);

-- Index non-clustered pour queries Email
CREATE NONCLUSTERED INDEX IX_Users_Email ON Users(Email) INCLUDE (Name);

-- Insérer données de test
INSERT INTO Users (Email, Name) VALUES
    ('user1@example.com', 'Alice Dupont'),
    ('user2@example.com', 'Bob Martin'),
    ('user3@example.com', 'Charlie Leclerc');

-- Query optimisée
SELECT Id, Email, Name FROM Users WHERE Email = 'user1@example.com' AND IsActive = 1;

-- Vérifier count
SELECT COUNT(*) AS TotalUsers FROM Users;

#!/bin/bash

# Scale vers Serverless 2 vCore max
az sql db update \
  --resource-group $RESOURCE_GROUP \
  --server $SQL_SERVER_NAME \
  --name $DB_NAME \
  --service-objective GP_Serveless \
  --max-size 100GB

# Activer auto-scaling (serverless auto)

# Monitoring : Query perf via DMV (exécuter en SQL)
# SELECT * FROM sys.dm_db_resource_stats WHERE end_time > DATEADD(minute, -15, SYSDATETIME());

# Alertes CPU >80%
az monitor metrics alert create \
  --name HighCPUAlert \
  --resource-group $RESOURCE_GROUP \
  --scopes "/subscriptions/$AZURE_SUBSCRIPTION_ID/resourceGroups/$RESOURCE_GROUP/providers/Microsoft.Sql/servers/$SQL_SERVER_NAME/databases/$DB_NAME" \
  --condition "avg Percentage_CPU > 80" \
  --description "Alerte CPU élevée sur DB tutorial"

# Cleanup (exécuter à la fin)
# az group delete --name $RESOURCE_GROUP --yes --no-wait