Identifying Unutilized Amazon RDS Instances to Optimize AWS Costs using PowerShell

Introduction:

Amazon RDS (Relational Database Service) is a powerful tool that simplifies the setup, operation, and scaling of relational databases in the cloud. However, with its ease of use, it’s also easy to overlook instances that are no longer needed, leading to unnecessary costs. In this blog,

We’ll explore how to identify orphaned RDS instances those that are no longer in use and how to clean them up to optimize your AWS bill.

Why Orphaned RDS Instances are a Problem:

Orphaned RDS instances are databases that were once used for development, testing, or even production, but have since been forgotten. These instances continue to incur charges as long as they exist, even if they are not being accessed. Over time, these unnecessary costs can add up, especially in large organizations with multiple teams and projects.

Step 1: Identifying Orphaned RDS Instances

To find orphaned RDS instances, you need to look for instances that have low or no CPU utilization, low or zero IOPS, or have been running without any recent connections. Here’s how you can identify these instances:

1. Check CloudWatch metrics:

   • Use Amazon CloudWatch to monitor RDS metrics like CPU utilization, database connections, and read/write IOPS.

   • Look for instances with consistently low or zero activity over a significant period, as these are strong indicators of being orphaned.

2. Review database connections:

   • Use the rds:DescribeDBInstances API or AWS CLI command to check the status and connections of each RDS instance.

   • Instances with no active connections over a specified period are likely candidates for termination.

3. Analyze Billing Reports:

   • AWS Cost Explorer can help you identify RDS instances that are contributing to your bill without corresponding usage.

   • Filter by service and region to get a detailed view of RDS costs, and correlate with usage data.

Step 2: Decommissioning Orphaned RDS Instances

Once you’ve identified orphaned instances, it’s time to decommission them. Here’s a safe approach:

1. Backup Before Deletion:

   • Always take a final snapshot of the RDS instance before deleting it. This ensures you have a backup in case the instance was mistakenly identified as orphaned.

2. Notify Stakeholders:

   • Before deletion, notify the stakeholders or teams associated with the instance. This gives them a chance to confirm whether the instance is truly unused.

3. Terminate the instance.

   • Use the AWS Management Console, CLI, or SDKs to delete the RDS instance. Ensure that all automated backups and manual snapshots are also deleted if they are no longer needed.

Step 3: Automating the Process

To prevent future orphaned instances, consider setting up automation:

1. Scheduled Reports:

   • Use AWS Lambda and CloudWatch to automate the generation of reports on RDS usage and potential orphaned instances.

2. Automated Tagging:

   • Implement tagging policies that automatically tag new RDS instances with relevant metadata, including project and owner details.

3. Lifecycle Policies:

   • Create lifecycle policies to automatically archive or delete instances that haven’t been accessed for a certain period of time.

PowerShell Script

To list all orphaned Amazon RDS instances using a PowerShell script, you can use the AWS Tools for PowerShell. Here's a script that checks for RDS instances with low CPU utilization and zero connections, which are indicators of being orphaned:

# Import AWS PowerShell Module
Import-Module AWSPowerShell

# Set AWS region
$region = 'us-west-2'

# Define a period to check for low utilization (e.g., last 7 days)
$startTime = (Get-Date).AddDays(-7)
$endTime = Get-Date

# Retrieve all RDS instances
$rdsInstances = Get-RDSDBInstance -Region $region

# Initialize an array to store orphaned instances
$orphanedInstances = @()

foreach ($rdsInstance in $rdsInstances) {
    $dbInstanceIdentifier = $rdsInstance.DBInstanceIdentifier

    # Get CPU Utilization metric from CloudWatch
    $cpuUtilization = Get-CWMetricStatistics -Namespace AWS/RDS `
                                             -MetricName CPUUtilization `
                                             -Dimensions @{Name='DBInstanceIdentifier';Value=$dbInstanceIdentifier} `
                                             -StartTime $startTime `
                                             -EndTime $endTime `
                                             -Period 3600 `
                                             -Statistics Average `
                                             -Region $region

    # Check the average CPU utilization over the period
    $averageCPUUtilization = ($cpuUtilization.Datapoints | Measure-Object -Property Average -Average).Average

    # Get DatabaseConnections metric from CloudWatch
    $dbConnections = Get-CWMetricStatistics -Namespace AWS/RDS `
                                            -MetricName DatabaseConnections `
                                            -Dimensions @{Name='DBInstanceIdentifier';Value=$dbInstanceIdentifier} `
                                            -StartTime $startTime `
                                            -EndTime $endTime `
                                            -Period 3600 `
                                            -Statistics Average `
                                            -Region $region

    # Check the average number of connections over the period
    $averageConnections = ($dbConnections.Datapoints | Measure-Object -Property Average -Average).Average

    # Determine if the instance is orphaned based on low CPU and zero connections
    if ($averageCPUUtilization -lt 5 -and $averageConnections -eq 0) {
        $orphanedInstances += $rdsInstance
    }
}

# Print orphaned RDS instances
if ($orphanedInstances.Count -eq 0) {
    Write-Output "No orphaned RDS instances found."
} else {
    Write-Output "Orphaned RDS Instances:"
    foreach ($orphanedInstance in $orphanedInstances) {
        Write-Output " - Instance ID: $($orphanedInstance.DBInstanceIdentifier)"
    }
}

Explanation:

1. Region: Set theregion variable to your AWS region.

2. Time Period: The script checks for low CPU utilization and zero database connections over the last 7 days.

3. CloudWatch Metrics: The script retrieves CPU utilization and database connections for each RDS instance from CloudWatch.

4. Orphaned Criteria: The script considers an instance orphaned if it has an average CPU utilization below 5% and an average of zero connections.

5. Output: If orphaned instances are found, the script prints their identifiers.

Conclusion:
By regularly identifying and decommissioning orphaned RDS instances, you can significantly reduce your AWS costs. Implementing automated monitoring and tagging policies will help maintain a clean and cost-efficient environment.

In the next blog, we will dive into how to identify and clean up orphaned EBS volumes to further optimize your AWS infrastructure.

Thanks for reading! I hope you understood these concepts and learned something.
If you have any queries, feel free to reach out to me on LinkedIn.