🗄️ SQL Database Services - Advanced Strategies

Enterprise Architecture Patterns

Multi-Region Database Architecture

AWS RDS + Read Replicas (Cross-Region)

Primary DB (Region 1)
    ↓ (Async replication)
Read Replica (Region 2)
Read Replica (Region 3)
Disaster Recovery Replica (Region 4)

GCP Cloud SQL with HA + Backups

Primary Instance (Zone A)
    ↓ (Sync HA)
Standby Instance (Zone B - auto failover)
Backup Storage (Multi-region)

Azure SQL with Geo-Replication

Primary (Region 1)
    ↓ (Async)
Secondary (Region 2 - readable)
Secondary (Region 3 - readable)

Performance Optimization

Query Performance Analysis

AWS RDS - Performance Insights

-- Identify slow queries
SELECT digest_text, calls, mean_time
FROM performance_schema.events_statements_summary_by_digest
ORDER BY mean_time DESC
LIMIT 10;

GCP Cloud SQL - Query Insights

Automatic collection of:
- Database CPU usage
- Query execution metrics
- Lock contention patterns

Azure SQL - Query Performance Insight

DMV queries show:
- Top resource-consuming queries
- Wait statistics
- Execution plans

Indexing Strategies

-- Composite index for common queries
CREATE INDEX idx_user_status_created 
ON users(status, created_at DESC);

-- Partial index for filtered queries
CREATE INDEX idx_active_users 
ON users(id) 
WHERE status = 'active';

-- Full text search index
CREATE FULLTEXT INDEX ON posts(content)

Connection Management

Connection Pooling (Critical for Serverless)

AWS RDS Proxy

Application → RDS Proxy (connection pool) → RDS
- Multiplexing
- IAM authentication
- Failover handling

GCP Cloud SQL Auth Proxy

cloud_sql_proxy -instances=project:region:instance &

Azure Connection Pooling

Application Pool → Azure SQL
Built-in connection pooling
Min/Max connections configured

Advanced Scaling Patterns

Horizontal Scaling (Sharding)

// Shard key: user_id % 4 = shard number
function getShardId(userId) {
  return userId % 4;
}

const shards = [
  'db-shard-0.rds.amazonaws.com',
  'db-shard-1.rds.amazonaws.com',
  'db-shard-2.rds.amazonaws.com',
  'db-shard-3.rds.amazonaws.com'
];

async function getUserData(userId) {
  const shardIndex = getShardId(userId);
  const shard = shards[shardIndex];
  
  const connection = await mysql.createConnection({
    host: shard,
    user: 'admin',
    password: process.env.DB_PASSWORD,
    database: 'users'
  });
  
  return connection.execute(
    'SELECT * FROM users WHERE id = ?',
    [userId]
  );
}

Caching Layer (Read Optimization)

// Cache hot data
const redis = require('redis');
const client = redis.createClient({
  host: 'elasticache.amazonaws.com',
  port: 6379
});

async function getUserWithCache(userId) {
  // Try cache first
  const cached = await client.get(`user:${userId}`);
  if (cached) return JSON.parse(cached);
  
  // Query database
  const db = await getDatabase();
  const user = await db.query('SELECT * FROM users WHERE id = ?', [userId]);
  
  // Store in cache (24 hours)
  await client.setex(`user:${userId}`, 86400, JSON.stringify(user));
  
  return user;
}

Backup and Recovery

Recovery Time Objective (RTO) & Recovery Point Objective (RPO)

RTO (Recovery Time) | RPO (Data Loss)
AWS RDS:
  - Regional failover: ~2 minutes
  - Cross-region restore: ~30 minutes
  - RPO: Seconds (Multi-AZ)

GCP Cloud SQL:
  - HA failover: ~1 minute
  - Backup restore: ~15 minutes
  - RPO: Near-zero (HA)

Azure SQL:
  - Failover: ~30 seconds
  - Geo-restore: ~1 hour
  - RPO: Depends on PITR retention

Backup Strategy

# AWS: Automated backups + manual snapshots
aws rds create-db-snapshot \
  --db-instance-identifier mydb \
  --db-snapshot-identifier mydb-backup-$(date +%s)

# GCP: Point-in-time recovery enabled
gcloud sql backups create \
  --instance my-instance \
  --description "manual backup"

# Azure: Automatic retention + LTR
az sql db ltr-backup create \
  --name "my-db" \
  --server "my-server" \
  --resource-group "my-rg"

Security Implementation

Encryption

AWS RDS

At-rest: AWS KMS encryption
In-transit: SSL/TLS mandatory

GCP Cloud SQL

At-rest: Google Cloud KMS
In-transit: SSL/TLS with CMEK

Azure SQL

At-rest: Transparent Data Encryption (TDE)
In-transit: TLS 1.2 minimum

Authentication

// AWS IAM Database Authentication
const AWS = require('aws-sdk');
const signer = new AWS.RDS.Signer({
  region: 'us-east-1',
  hostname: 'mydb.c9akciq32.us-east-1.rds.amazonaws.com',
  port: 3306,
  username: 'iamdbuser'
});

const token = signer.getAuthorizationHeader({
  username: 'iamdbuser'
});

// Use token for connection

Cost Management at Scale

Compute Cost Optimization

Reserved Instances:
- 1-year: 30-35% discount
- 3-year: 50-55% discount
- Best for baseline capacity

On-demand:
- Pay per hour
- Full flexibility

Aurora (MySQL/PostgreSQL):
- Pay per GB stored + compute
- 25% cheaper than RDS for same workload

Storage Optimization

AWS:
- GP2 (default): $0.23/GB/mo
- IO1 (high I/O): $1.25/GB/mo + provisioned IOPS

GCP:
- Standard: $0.18/GB/mo
- SSD: $0.27/GB/mo

Azure:
- Standard: $0.12/GB/mo
- Premium: $0.23/GB/mo

Network Cost Optimization

Data Transfer (out):
AWS: $0.02 per GB (first 10TB)
GCP: $0.12 per GB
Azure: $0.025 per GB

Optimization:
- Minimize cross-region queries
- Use caching for repeated reads
- Deploy in same region as compute

Monitoring and Alerting

Key Metrics to Monitor

CPU Utilization: Alert > 80%
Memory Utilization: Alert > 85%
IOPS: Alert > 80% of provisioned
Connections: Alert > 80% of max
Read Latency: Alert > 100ms
Write Latency: Alert > 10ms
Replication Lag: Alert > 5 seconds

Custom Monitoring

// CloudWatch metrics (AWS)
const cloudwatch = new AWS.CloudWatch();

await cloudwatch.putMetricData({
  Namespace: 'MyApplication/Database',
  MetricData: [{
    MetricName: 'QueryLatency',
    Value: elapsedTime,
    Unit: 'Milliseconds',
    Timestamp: new Date()
  }]
}).promise();

Migration Strategies

AWS DMS (Database Migration Service)

Source DB → DMS → Target DB
Supports:
- Homogeneous migrations (MySQL → MySQL)
- Heterogeneous (Oracle → PostgreSQL)
- Zero downtime (CDC)

GCP Database Migration Service

Source → Validation → GCP Target
Features:
- Schema conversion
- Data validation
- Minimal downtime

Azure Data Migration Service

Source → Online migration → Azure SQL
- Transactional consistency
- Minimal downtime
- Database & schema migration

Key Takeaways

• **Sharding required** for databases >100GB with heavy writes

• **Connection pooling essential** for serverless environments

• **Multi-region mandatory** for critical applications

• **Backups are insurance** - test restoration regularly

• **Monitoring prevents surprises** - alert on latency, not just availability

• **Cost varies by access pattern** - optimize for your workload

• **Migration planning critical** - test thoroughly before cutover

• **Encryption and IAM non-negotiable** for compliance

• **Read replicas for scaling reads**, not writes

• **Cache aggressively** for hot data

Recommendations by Scenario

High-Traffic OLTP

→ AWS RDS with Read Replicas + ElastiCache + Sharding

Analytical Workloads

→ GCP BigQuery or AWS Redshift (data warehouse)

Real-time Applications

→ Firebase Realtime DB or GCP Firestore

Microsoft Stack

→ Azure SQL with Always On AG

Startup (Cost Sensitive)

→ GCP Cloud SQL with Postgres (most affordable)