Cost Optimization with Azure SQL Elastic Pools

Managing multiple databases can be expensive, especially for SaaS applications with many tenants. Azure SQL Elastic Pools allow you to share resources across databases, potentially reducing costs significantly. Here is how to leverage them effectively.

When to Use Elastic Pools

Elastic pools are ideal when:

• You have multiple databases with variable usage patterns
• Peak usage times differ across databases
• You want predictable costs for multiple databases
• Individual databases use less than 100 DTUs on average

Creating an Elastic Pool

# Create a SQL server
az sql server create \
    --name sql-server-2020 \
    --resource-group rg-sql \
    --location australiaeast \
    --admin-user sqladmin \
    --admin-password 'YourSecurePassword123!'

# Create an elastic pool
az sql elastic-pool create \
    --resource-group rg-sql \
    --server sql-server-2020 \
    --name pool-standard \
    --edition Standard \
    --capacity 100 \
    --db-min-capacity 0 \
    --db-max-capacity 100

# Create databases in the pool
az sql db create \
    --resource-group rg-sql \
    --server sql-server-2020 \
    --name tenant-db-1 \
    --elastic-pool pool-standard

az sql db create \
    --resource-group rg-sql \
    --server sql-server-2020 \
    --name tenant-db-2 \
    --elastic-pool pool-standard

Understanding eDTUs vs DTUs

• DTU - Database Transaction Units for single databases
• eDTU - elastic Database Transaction Units shared across pool
• Same performance characteristics, just shared

Sizing Your Pool

-- Check historical resource usage for existing databases
SELECT
    database_name,
    AVG(avg_cpu_percent) as avg_cpu,
    MAX(avg_cpu_percent) as max_cpu,
    AVG(avg_data_io_percent) as avg_io,
    MAX(avg_data_io_percent) as max_io
FROM sys.resource_stats
WHERE start_time > DATEADD(day, -14, GETDATE())
GROUP BY database_name;

.NET Connection Management

public class TenantDatabaseService
{
    private readonly string _serverName;
    private readonly string _poolName;

    public string GetConnectionString(string tenantId)
    {
        var databaseName = $"tenant-{tenantId}";
        return $"Server=tcp:{_serverName}.database.windows.net,1433;" +
               $"Initial Catalog={databaseName};" +
               "Persist Security Info=False;" +
               "User ID=sqladmin;" +
               "Password=YourSecurePassword123!;" +
               "MultipleActiveResultSets=False;" +
               "Encrypt=True;" +
               "TrustServerCertificate=False;" +
               "Connection Timeout=30;";
    }

    public async Task<SqlConnection> GetConnectionAsync(string tenantId)
    {
        var connectionString = GetConnectionString(tenantId);
        var connection = new SqlConnection(connectionString);
        await connection.OpenAsync();
        return connection;
    }
}

Entity Framework Core with Elastic Pools

public class TenantDbContext : DbContext
{
    private readonly string _connectionString;

    public TenantDbContext(string connectionString)
    {
        _connectionString = connectionString;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer(_connectionString);
    }

    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }
}

// In Startup.cs or DI configuration
public class TenantDbContextFactory
{
    private readonly TenantDatabaseService _databaseService;

    public TenantDbContext CreateContext(string tenantId)
    {
        var connectionString = _databaseService.GetConnectionString(tenantId);
        return new TenantDbContext(connectionString);
    }
}

Monitoring Pool Performance

-- View current pool resource usage
SELECT
    elastic_pool_name,
    avg_cpu_percent,
    avg_data_io_percent,
    avg_log_write_percent,
    avg_storage_percent
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'pool-standard'
ORDER BY end_time DESC;

-- View individual database usage within pool
SELECT
    database_name,
    avg_cpu_percent,
    avg_data_io_percent,
    max_worker_percent
FROM sys.dm_db_resource_stats;

Scaling the Pool

# Scale up the pool
az sql elastic-pool update \
    --resource-group rg-sql \
    --server sql-server-2020 \
    --name pool-standard \
    --capacity 200

# Move a database to a different pool
az sql db update \
    --resource-group rg-sql \
    --server sql-server-2020 \
    --name tenant-db-1 \
    --elastic-pool pool-premium

Automation for Tenant Provisioning

public class TenantProvisioningService
{
    private readonly string _resourceGroup;
    private readonly string _serverName;
    private readonly string _poolName;

    public async Task ProvisionTenantAsync(string tenantId)
    {
        var databaseName = $"tenant-{tenantId}";

        // Create database using Azure Management SDK
        var sqlClient = new SqlManagementClient(credential);

        var database = new Database
        {
            Location = "australiaeast",
            ElasticPoolId = $"/subscriptions/{subscriptionId}/resourceGroups/{_resourceGroup}/providers/Microsoft.Sql/servers/{_serverName}/elasticPools/{_poolName}"
        };

        await sqlClient.Databases.CreateOrUpdateAsync(
            _resourceGroup,
            _serverName,
            databaseName,
            database);

        // Run migrations
        using var context = new TenantDbContext(GetConnectionString(tenantId));
        await context.Database.MigrateAsync();
    }
}

Cost Comparison Example

For 10 databases with varying loads:

Approach	Monthly Cost (estimate)
10 x S1 (20 DTU each)	~$150
1 x 100 eDTU Pool	~$112
Savings	~25%

Elastic pools are a straightforward way to reduce database costs for multi-tenant applications while maintaining performance isolation.