# Database Operations

Advanced database operations for Azure SQL.

## Create Database with vCore SKU

```csharp
using Azure.ResourceManager.Sql;
using Azure.ResourceManager.Sql.Models;

var databaseData = new SqlDatabaseData(AzureLocation.EastUS)
{
    Sku = new SqlSku("GP_Gen5_2")
    {
        Tier = "GeneralPurpose",
        Family = "Gen5",
        Capacity = 2 // vCores
    },
    MaxSizeBytes = 32L * 1024 * 1024 * 1024, // 32 GB
    ZoneRedundant = false,
    ReadScale = SqlDatabaseReadScale.Disabled,
    RequestedBackupStorageRedundancy = SqlBackupStorageRedundancy.Geo
};

var operation = await databaseCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "my-vcore-database",
    databaseData);
```

## Create Serverless Database

```csharp
var databaseData = new SqlDatabaseData(AzureLocation.EastUS)
{
    Sku = new SqlSku("GP_S_Gen5_2")
    {
        Tier = "GeneralPurpose",
        Family = "Gen5",
        Capacity = 2 // Max vCores
    },
    AutoPauseDelay = 60, // Minutes of inactivity before auto-pause
    MinCapacity = 0.5,   // Minimum vCores (can be fractional)
    MaxSizeBytes = 32L * 1024 * 1024 * 1024
};

await databaseCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "my-serverless-db",
    databaseData);
```

## Scale Database

```csharp
// Get existing database
var database = await databaseCollection.GetAsync("my-database");

// Update SKU
var updateData = new SqlDatabaseData(database.Value.Data.Location)
{
    Sku = new SqlSku("S3") { Tier = "Standard" }
};

await databaseCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "my-database",
    updateData);
```

## Copy Database

```csharp
var copyData = new SqlDatabaseData(AzureLocation.EastUS)
{
    CreateMode = SqlDatabaseCreateMode.Copy,
    SourceDatabaseId = sourceDatabase.Id,
    Sku = new SqlSku("S0") { Tier = "Standard" }
};

await targetDatabaseCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "database-copy",
    copyData);
```

## Restore Database from Point-in-Time

```csharp
var restoreData = new SqlDatabaseData(AzureLocation.EastUS)
{
    CreateMode = SqlDatabaseCreateMode.PointInTimeRestore,
    SourceDatabaseId = sourceDatabase.Id,
    RestorePointInTime = DateTimeOffset.UtcNow.AddHours(-2), // 2 hours ago
    Sku = new SqlSku("S0") { Tier = "Standard" }
};

await databaseCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "restored-database",
    restoreData);
```

## Restore Deleted Database

```csharp
// List deleted databases
var deletedDatabases = server.GetRestorableDroppedDatabases();

await foreach (var deleted in deletedDatabases)
{
    Console.WriteLine($"Deleted: {deleted.Data.DatabaseName} at {deleted.Data.DeletionOn}");
    
    // Restore the deleted database
    var restoreData = new SqlDatabaseData(AzureLocation.EastUS)
    {
        CreateMode = SqlDatabaseCreateMode.Restore,
        RestorableDroppedDatabaseId = deleted.Id,
        Sku = new SqlSku("S0") { Tier = "Standard" }
    };
    
    await databaseCollection.CreateOrUpdateAsync(
        WaitUntil.Completed,
        "restored-deleted-db",
        restoreData);
    
    break; // Restore first one
}
```

## Geo-Restore Database

```csharp
// List recoverable databases (geo-replicated backups)
var recoverableDatabases = server.GetRecoverableDatabases();

await foreach (var recoverable in recoverableDatabases)
{
    var restoreData = new SqlDatabaseData(AzureLocation.WestUS) // Different region
    {
        CreateMode = SqlDatabaseCreateMode.Recovery,
        RecoverableDatabaseId = recoverable.Id,
        Sku = new SqlSku("S0") { Tier = "Standard" }
    };
    
    await targetDatabaseCollection.CreateOrUpdateAsync(
        WaitUntil.Completed,
        "geo-restored-db",
        restoreData);
    
    break;
}
```

## Configure Long-Term Retention

```csharp
var ltrPolicy = new LongTermRetentionPolicyData
{
    WeeklyRetention = "P4W",  // 4 weeks
    MonthlyRetention = "P12M", // 12 months
    YearlyRetention = "P5Y",   // 5 years
    WeekOfYear = 1 // First week for yearly backup
};

var ltrPolicyResource = database.GetLongTermRetentionPolicy();
await ltrPolicyResource.CreateOrUpdateAsync(
    WaitUntil.Completed,
    ltrPolicy);
```

## Configure Short-Term Retention

```csharp
var strPolicy = new ShortTermRetentionPolicyData
{
    RetentionDays = 14, // 7-35 days
    DiffBackupIntervalInHours = 12 // 12 or 24 hours
};

var strPolicyResource = database.GetShortTermRetentionPolicy();
await strPolicyResource.CreateOrUpdateAsync(
    WaitUntil.Completed,
    strPolicy);
```

## Export Database to Bacpac

```csharp
var exportRequest = new DatabaseExportDefinition
{
    StorageKeyType = StorageKeyType.StorageAccessKey,
    StorageKey = "<storage-account-key>",
    StorageUri = new Uri("https://mystorageaccount.blob.core.windows.net/backups/mydb.bacpac"),
    AdministratorLogin = "sqladmin",
    AdministratorLoginPassword = "YourPassword123!"
};

var exportOperation = await database.ExportAsync(WaitUntil.Completed, exportRequest);
```

## Import Database from Bacpac

```csharp
var importRequest = new ImportExistingDatabaseDefinition
{
    StorageKeyType = StorageKeyType.StorageAccessKey,
    StorageKey = "<storage-account-key>",
    StorageUri = new Uri("https://mystorageaccount.blob.core.windows.net/backups/mydb.bacpac"),
    AdministratorLogin = "sqladmin",
    AdministratorLoginPassword = "YourPassword123!"
};

// Import into existing database
var importOperation = await database.ImportAsync(WaitUntil.Completed, importRequest);
```

## Rename Database

```csharp
var renameRequest = new ResourceMoveDefinition(
    new ResourceIdentifier($"{server.Id}/databases/new-database-name"));

await database.RenameAsync(renameRequest);
```

## Delete Database

```csharp
var database = await databaseCollection.GetAsync("my-database");
await database.Value.DeleteAsync(WaitUntil.Completed);
```

## Transparent Data Encryption

```csharp
// TDE is enabled by default for new databases
// To check or modify:
var tdeResource = database.GetSqlDatabaseTransparentDataEncryption();
var tde = await tdeResource.GetAsync();

Console.WriteLine($"TDE State: {tde.Value.Data.State}");

// Disable TDE (not recommended)
var tdeData = new SqlDatabaseTransparentDataEncryptionData
{
    State = TransparentDataEncryptionState.Disabled
};
await tdeResource.CreateOrUpdateAsync(WaitUntil.Completed, tdeData);
```

## Database Threat Detection

```csharp
var threatDetectionData = new SqlDatabaseSecurityAlertPolicyData
{
    State = SecurityAlertsPolicyState.Enabled,
    DisabledAlerts = { }, // Empty = all alerts enabled
    EmailAddresses = { "security@contoso.com" },
    EmailAccountAdmins = true,
    RetentionDays = 30,
    StorageEndpoint = "https://mystorageaccount.blob.core.windows.net",
    StorageAccountAccessKey = "<storage-key>"
};

var securityPolicy = database.GetSqlDatabaseSecurityAlertPolicy();
await securityPolicy.CreateOrUpdateAsync(WaitUntil.Completed, threatDetectionData);
```

# Elastic Pools

Elastic pool management for Azure SQL.

## Create DTU-Based Elastic Pool

```csharp
using Azure.ResourceManager.Sql;
using Azure.ResourceManager.Sql.Models;

var poolData = new ElasticPoolData(AzureLocation.EastUS)
{
    Sku = new SqlSku("StandardPool")
    {
        Tier = "Standard",
        Capacity = 100 // 100 eDTUs
    },
    PerDatabaseSettings = new ElasticPoolPerDatabaseSettings
    {
        MinCapacity = 0,   // Min eDTUs per database
        MaxCapacity = 100  // Max eDTUs per database
    }
};

var poolCollection = server.GetElasticPools();
var operation = await poolCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "my-standard-pool",
    poolData);

ElasticPoolResource pool = operation.Value;
```

## Create vCore-Based Elastic Pool

```csharp
var poolData = new ElasticPoolData(AzureLocation.EastUS)
{
    Sku = new SqlSku("GP_Gen5_2")
    {
        Tier = "GeneralPurpose",
        Family = "Gen5",
        Capacity = 2 // vCores
    },
    PerDatabaseSettings = new ElasticPoolPerDatabaseSettings
    {
        MinCapacity = 0,
        MaxCapacity = 2
    },
    ZoneRedundant = false,
    LicenseType = DatabaseLicenseType.LicenseIncluded // or BasePrice for Azure Hybrid Benefit
};

await poolCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "my-vcore-pool",
    poolData);
```

## Create Premium Elastic Pool with Zone Redundancy

```csharp
var poolData = new ElasticPoolData(AzureLocation.EastUS)
{
    Sku = new SqlSku("PremiumPool")
    {
        Tier = "Premium",
        Capacity = 125 // 125 eDTUs
    },
    PerDatabaseSettings = new ElasticPoolPerDatabaseSettings
    {
        MinCapacity = 0,
        MaxCapacity = 125
    },
    ZoneRedundant = true
};

await poolCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "my-premium-pool",
    poolData);
```

## Add Database to Elastic Pool

```csharp
// Create new database in pool
var databaseData = new SqlDatabaseData(AzureLocation.EastUS)
{
    ElasticPoolId = pool.Id
};

await databaseCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "pooled-database",
    databaseData);
```

## Move Existing Database to Elastic Pool

```csharp
// Get existing database
var database = await databaseCollection.GetAsync("standalone-database");

// Update to use elastic pool
var updateData = new SqlDatabaseData(database.Value.Data.Location)
{
    ElasticPoolId = pool.Id
};

await databaseCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "standalone-database",
    updateData);
```

## Remove Database from Elastic Pool

```csharp
// Move database out of pool by assigning a standalone SKU
var updateData = new SqlDatabaseData(database.Value.Data.Location)
{
    Sku = new SqlSku("S0") { Tier = "Standard" },
    ElasticPoolId = null
};

await databaseCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "pooled-database",
    updateData);
```

## Scale Elastic Pool

```csharp
var pool = await poolCollection.GetAsync("my-elastic-pool");

var updateData = new ElasticPoolData(pool.Value.Data.Location)
{
    Sku = new SqlSku("StandardPool")
    {
        Tier = "Standard",
        Capacity = 200 // Scale up to 200 eDTUs
    },
    PerDatabaseSettings = new ElasticPoolPerDatabaseSettings
    {
        MinCapacity = 10,
        MaxCapacity = 100
    }
};

await poolCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "my-elastic-pool",
    updateData);
```

## List Databases in Elastic Pool

```csharp
var pool = await poolCollection.GetAsync("my-elastic-pool");

// Get databases in this pool
var poolDatabases = pool.Value.GetElasticPoolDatabases();

await foreach (var db in poolDatabases)
{
    Console.WriteLine($"Database: {db.Data.Name}");
    Console.WriteLine($"  Current SKU: {db.Data.CurrentSku?.Name}");
    Console.WriteLine($"  Max Size: {db.Data.MaxSizeBytes / (1024 * 1024 * 1024)} GB");
}
```

## Monitor Elastic Pool Metrics

```csharp
// Get pool resource utilization
var pool = await poolCollection.GetAsync("my-elastic-pool");

// Pool metrics are available via Azure Monitor
// Use Azure.ResourceManager.Monitor for detailed metrics

Console.WriteLine($"Pool: {pool.Value.Data.Name}");
Console.WriteLine($"State: {pool.Value.Data.State}");
Console.WriteLine($"Max Size: {pool.Value.Data.MaxSizeBytes / (1024 * 1024 * 1024)} GB");
Console.WriteLine($"DTU Capacity: {pool.Value.Data.Sku?.Capacity}");
```

## Delete Elastic Pool

```csharp
// First, move all databases out of the pool or delete them
var pool = await poolCollection.GetAsync("my-elastic-pool");

// Check for databases
var poolDatabases = pool.Value.GetElasticPoolDatabases();
await foreach (var db in poolDatabases)
{
    // Either delete or move to standalone
    await db.DeleteAsync(WaitUntil.Completed);
}

// Now delete the pool
await pool.Value.DeleteAsync(WaitUntil.Completed);
```

## Elastic Pool SKU Reference

### DTU-Based Pools

| SKU Name | Tier | eDTU Range | Max DBs |
|----------|------|------------|---------|
| `BasicPool` | Basic | 50-1600 | 500 |
| `StandardPool` | Standard | 50-3000 | 500 |
| `PremiumPool` | Premium | 125-4000 | 100 |

### vCore-Based Pools

| SKU Pattern | Tier | vCore Range |
|-------------|------|-------------|
| `GP_Gen5_{n}` | GeneralPurpose | 2-80 |
| `GP_Fsv2_{n}` | GeneralPurpose | 8-72 |
| `GP_DC_{n}` | GeneralPurpose | 2-8 |
| `BC_Gen5_{n}` | BusinessCritical | 2-80 |
| `BC_DC_{n}` | BusinessCritical | 2-8 |

## Best Practices

1. **Right-size pools**: Start with estimated total DTU/vCore needs, monitor, and adjust
2. **Set per-database limits**: Prevent a single database from consuming all resources
3. **Use zone redundancy** for production Premium/BusinessCritical pools
4. **Monitor utilization**: Scale up when consistently above 80% utilization
5. **Consider Hyperscale**: For databases > 4TB or needing rapid scale
6. **Use Azure Hybrid Benefit**: Set `LicenseType = BasePrice` if you have SQL Server licenses

# Server Management

Advanced server operations for Azure SQL.

## Create Server with Azure AD Authentication

```csharp
using Azure.ResourceManager.Sql;
using Azure.ResourceManager.Sql.Models;

var serverData = new SqlServerData(AzureLocation.EastUS)
{
    // SQL authentication (optional, can be disabled)
    AdministratorLogin = "sqladmin",
    AdministratorLoginPassword = "YourSecurePassword123!",
    
    // Azure AD authentication
    Administrators = new ServerExternalAdministrator
    {
        AdministratorType = SqlAdministratorType.ActiveDirectory,
        Login = "admin@contoso.com",
        Sid = Guid.Parse("<azure-ad-user-object-id>"),
        TenantId = Guid.Parse("<azure-ad-tenant-id>"),
        AzureADOnlyAuthentication = false // Set true to disable SQL auth
    },
    
    Version = "12.0",
    MinimalTlsVersion = SqlMinimalTlsVersion.Tls1_2
};

var operation = await serverCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "my-sql-server",
    serverData);
```

## Update Server Administrator Password

```csharp
// Get existing server
var server = await serverCollection.GetAsync("my-sql-server");

// Update password (requires full server data)
var updateData = server.Value.Data;
// Note: Password cannot be read, only set
// You need to create new SqlServerData with the new password

var newServerData = new SqlServerData(server.Value.Data.Location)
{
    AdministratorLogin = server.Value.Data.AdministratorLogin,
    AdministratorLoginPassword = "NewSecurePassword456!"
};

await serverCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "my-sql-server",
    newServerData);
```

## Configure Public Network Access

```csharp
var serverData = new SqlServerData(AzureLocation.EastUS)
{
    AdministratorLogin = "sqladmin",
    AdministratorLoginPassword = "YourSecurePassword123!",
    
    // Disable public access (use private endpoints only)
    PublicNetworkAccess = ServerNetworkAccessFlag.Disabled,
    
    // Or enable with restrictions
    // PublicNetworkAccess = ServerNetworkAccessFlag.Enabled,
    // RestrictOutboundNetworkAccess = ServerNetworkAccessFlag.Enabled
};
```

## Get Server by Resource ID

```csharp
var resourceId = SqlServerResource.CreateResourceIdentifier(
    subscriptionId,
    "my-resource-group",
    "my-sql-server");

var server = armClient.GetSqlServerResource(resourceId);
var serverData = await server.GetAsync();
```

## Delete Server

```csharp
var server = await serverCollection.GetAsync("my-sql-server");
await server.Value.DeleteAsync(WaitUntil.Completed);
```

## Check Server Name Availability

```csharp
var checkRequest = new SqlNameAvailabilityContent
{
    Name = "proposed-server-name",
    ResourceType = "Microsoft.Sql/servers"
};

var result = await subscription.CheckSqlNameAvailabilityAsync(checkRequest);

if (result.Value.IsAvailable == true)
{
    Console.WriteLine("Name is available");
}
else
{
    Console.WriteLine($"Name unavailable: {result.Value.Reason}");
}
```

## Virtual Network Rules

```csharp
// Allow access from a specific subnet
var vnetRuleData = new SqlServerVirtualNetworkRuleData
{
    VirtualNetworkSubnetId = new ResourceIdentifier(
        "/subscriptions/{sub}/resourceGroups/{rg}/providers/" +
        "Microsoft.Network/virtualNetworks/{vnet}/subnets/{subnet}"),
    IgnoreMissingVnetServiceEndpoint = false
};

var vnetRuleCollection = server.GetSqlServerVirtualNetworkRules();
await vnetRuleCollection.CreateOrUpdateAsync(
    WaitUntil.Completed,
    "allow-app-subnet",
    vnetRuleData);
```

## Private Endpoints

```csharp
// Private endpoints are created via Microsoft.Network, not SQL SDK
// The SQL server just needs PublicNetworkAccess = Disabled

// After creating private endpoint, approve the connection:
var privateEndpointConnections = server.GetSqlServerPrivateEndpointConnections();

await foreach (var connection in privateEndpointConnections)
{
    if (connection.Data.PrivateLinkServiceConnectionState.Status == "Pending")
    {
        // Approve the connection
        var approvalData = connection.Data;
        approvalData.PrivateLinkServiceConnectionState.Status = "Approved";
        approvalData.PrivateLinkServiceConnectionState.Description = "Approved by admin";
        
        await privateEndpointConnections.CreateOrUpdateAsync(
            WaitUntil.Completed,
            connection.Data.Name,
            approvalData);
    }
}
```

## Server Auditing

```csharp
var auditingData = new SqlServerBlobAuditingPolicyData
{
    State = BlobAuditingPolicyState.Enabled,
    StorageEndpoint = "https://mystorageaccount.blob.core.windows.net",
    StorageAccountAccessKey = "<storage-account-key>",
    RetentionDays = 90,
    IsStorageSecondaryKeyInUse = false,
    IsAzureMonitorTargetEnabled = true
};

var auditingPolicy = server.GetSqlServerBlobAuditingPolicy();
await auditingPolicy.CreateOrUpdateAsync(WaitUntil.Completed, auditingData);
```

## Transparent Data Encryption (Server-Level Key)

```csharp
// Configure customer-managed key for TDE
var protectorData = new EncryptionProtectorData
{
    ServerKeyType = SqlServerKeyType.AzureKeyVault,
    ServerKeyName = "mykeyvault_mykey_abc123",
    AutoRotationEnabled = true
};

var protector = server.GetEncryptionProtector();
await protector.CreateOrUpdateAsync(WaitUntil.Completed, protectorData);
```