The SQL database servers use the available memory for caching to speed up the database operation. If we do not restrict the SQL database server memory usage, the operating system will not have enough memory to run. This setting is also necessary for an AWS RDS instance, otherwise, you will get the alert
ALARM FreeableMemory <=… MB
In AWS we can specify the maximum SQL server memory dynamically, so every RDS instance type will leave enough memory for the operating system regardless of the size of the available memory size. in this example, we will leave 1.5 GB (1536 MB) memory for the operating system so the default 1024 MB free memory alarm will not sound.
DBInstanceClassMemory returns the total memory size in bytes, so we need to convert the value to MB, to be able to set the value of “max server memory (mb)” to the correct number.
If you use Terraform to create your RDS instance, create a script with the aws_db_parameter_group resource to create a Parameter Group in your AWS account. You need to execute it once, as all RDS instances will use the same group.
resource "aws_db_parameter_group" "default" {
name = "max-server-memory"
family = "sqlserver-se-12.0"
description = "DBInstanceClassMemory"
parameter {
name = "custom-sqlserver-se-12-0"
value = "SUM({DBInstanceClassMemory/1048576},-1536)"
apply_method = "immediate"
}
}
In the RDS instance creation script assign the Parameter Group to the RDS instance and increase the timeout of the create and delete operations to make sure Terraform waits during the creation and deletion process.
resource "aws_db_instance" "default" {
...
# Add the Max Server Memory parameter group to the instance
parameter_group_name = "custom-sqlserver-se-12-0"
...
timeouts {
create = "120m"
delete = "120m"
}
...
}