Comment déployer et optimiser Azure SQL Database en 2026

az login
az account set --subscription "VotreSubscriptionID"
az version

param location string = resourceGroup().location
param sqlAdmin string = 'sqladmin2026'
param sqlPassword string {
  @secure()
  minLength: 12
}

resource rg 'Microsoft.Resources/resourceGroups@2021-04-01' Existing = {
  name: resourceGroup().name
}

resource sqlServer 'Microsoft.Sql/servers@2023-05-01-preview' = {
  name: 'sqlserver-${uniqueString(resourceGroup().id)}'
  location: location
  properties: {
    administratorLogin: sqlAdmin
    administratorLoginPassword: sqlPassword
    version: '16.0'
  }
}

output serverName string = sqlServer.name
output fullServerName string = '${sqlServer.name}.database.windows.net'

az group create --name "rg-sql-advanced-2026" --location "francecentral"
az deployment group create --resource-group "rg-sql-advanced-2026" --template-file main.bicep --parameters sqlPassword='P@ssw0rdAvance2026!SqlDb' --query properties.outputs.serverName.value -o tsv

SERVER_NAME=$(az deployment group show --resource-group "rg-sql-advanced-2026" --name "bicep-deployment" --query properties.outputs.serverName.value -o tsv)

az sql db create --resource-group "rg-sql-advanced-2026" --server $SERVER_NAME --name "hyperscale-db-2026" --service-objective "HS_Gen5_2" --family "Gen5" --edition Hyperscale

az sql server firewall-rule create --resource-group "rg-sql-advanced-2026" --server $SERVER_NAME --name AllowMyIP --start-ip-address $(curl -s ifconfig.me) --end-ip-address $(curl -s ifconfig.me)

USE [hyperscale-db-2026];
GO

CREATE TABLE Users (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Email NVARCHAR(255) NOT NULL UNIQUE,
    CreatedAt DATETIME2 DEFAULT SYSDATETIMEOFFSET()
);
GO

CREATE INDEX IX_Users_Email ON Users(Email) INCLUDE (CreatedAt);
GO

CREATE TABLE Orders (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    UserId INT NOT NULL,
    Amount DECIMAL(10,2),
    OrderDate DATETIME2 DEFAULT SYSDATETIMEOFFSET(),
    FOREIGN KEY (UserId) REFERENCES Users(Id)
);
GO

INSERT INTO Users (Email) VALUES ('test@exemple.com');
INSERT INTO Orders (UserId, Amount) VALUES (1, 99.99);
GO

SERVER_NAME=$(az deployment group show --resource-group "rg-sql-advanced-2026" --name "bicep-deployment" --query properties.outputs.serverName.value -o tsv)

# Auditing to Log Analytics
az sql server create-audit-policy --resource-group rg-sql-advanced-2026 --server $SERVER_NAME --state Enabled --storage-key-type ServiceManaged

# TDE
az sql db tde set --resource-group rg-sql-advanced-2026 --server $SERVER_NAME --database-name hyperscale-db-2026 --status Enabled

# Threat Detection
az sql server advanced-threat-protection-settings create --resource-group rg-sql-advanced-2026 --server $SERVER_NAME --state Enabled --retention-days 90 --storage-endpoint 'https://mystorageacct.blob.core.windows.net/blobcontainer/'

SERVER_NAME=$(az deployment group show --resource-group "rg-sql-advanced-2026" --name "bicep-deployment" --query properties.outputs.serverName.value -o tsv)

az sql db replica create --resource-group rg-sql-advanced-2026 --server $SERVER_NAME --name geo-replica-db --partner-server myserver-secondary.database.windows.net --service-objective HS_Gen5_4

az sql db failover --resource-group rg-sql-advanced-2026 --server myserver-secondary.database.windows.net --name geo-replica-db

-- Activer Automatic Tuning
ALTER DATABASE [hyperscale-db-2026] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = ON, DROP_INDEX = ON);
GO

-- Top queries by CPU
SELECT TOP 10
    qt.query_sql_text,
    rs.avg_cpu_time,
    rs.count_executions
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_cpu_time DESC;
GO

-- Créer index recommandé
CREATE INDEX IX_Orders_UserId_Amount ON Orders(UserId) INCLUDE (Amount, OrderDate);
GO