After we’ve reviewed CloudFormation template for EC2 let’s go on with MS SQL with Multi-AZ presence.
Database will be accessible only from default VPC with no Internet wide access.
{
"AWSTemplateFormatVersion": "2010-09-09",
"Description": "test RDS",
"Parameters": {
"DBUser": {
"NoEcho": "true",
"Description": "The database admin account username",
"Type": "String",
"MinLength": "1",
"MaxLength": "16",
"AllowedPattern": "[a-zA-Z][a-zA-Z0-9]*",
"ConstraintDescription": "must begin with a letter and contain only alphanumeric characters."
},
"DBPassword": {
"NoEcho": "true",
"Description": "The database admin account password",
"Type": "String",
"MinLength": "8",
"MaxLength": "41",
"AllowedPattern": "[a-zA-Z0-9]*",
"ConstraintDescription": "must contain only alphanumeric characters."
},
"DBInstanceId" : {
"Description" : "Database instance identifier",
"Type" : "String"
},
"SNSEndpoint": {
"Description": "Email for notifications",
"Type": "String"
}
},
"Resources": {
"NotificationTopic": {
"Type": "AWS::SNS::Topic",
"Properties": {
"DisplayName": "RDS-SQL-SE-demo",
"Subscription": [
{
"Endpoint": {
"Ref": "SNSEndpoint"
},
"Protocol": "email"
}
],
"TopicName": {
"Ref" : "DBInstanceId"
}
}
},
"DBServerSecurityGroup" : {
"Type" : "AWS::EC2::SecurityGroup",
"Properties" : {
"GroupDescription" : "Enable 1433 access from VPC",
"SecurityGroupIngress" : [
{"IpProtocol" : "tcp", "FromPort" : "1433", "ToPort" : "1433", "CidrIp" : "172.31.0.0/16"}
]
}
},
"OptionGroup": {
"Type": "AWS::RDS::OptionGroup",
"Properties": {
"EngineName": "sqlserver-se",
"MajorEngineVersion": "12.00",
"OptionGroupDescription": "OptionGroup",
"OptionConfigurations":[
{
"OptionName": "Mirroring"
}
]
}
},
"DBInstance": {
"Type": "AWS::RDS::DBInstance",
"Properties": {
"AllocatedStorage": "200",
"AllowMajorVersionUpgrade": "false",
"AutoMinorVersionUpgrade": "true",
"BackupRetentionPeriod": "30",
"DBInstanceClass": "db.m3.xlarge",
"DBInstanceIdentifier": {
"Ref" : "DBInstanceId"
},
"Engine": "sqlserver-se",
"EngineVersion": "12.00.4422.0.v1",
"Iops": "1000",
"LicenseModel": "license-included",
"PreferredBackupWindow": "01:00-03:00",
"PreferredMaintenanceWindow": "sun:06:00-sun:06:30",
"MasterUsername": {
"Ref": "DBUser"
},
"MasterUserPassword": {
"Ref": "DBPassword"
},
"OptionGroupName": {
"Ref": "OptionGroup"
},
"PubliclyAccessible": "false",
"StorageEncrypted": "true",
"StorageType": "io1",
"VPCSecurityGroups": [ {
"Fn::GetAtt" : [ "DBServerSecurityGroup" , "GroupId" ]
} ]
},
"DeletionPolicy" : "Snapshot"
},
"RDSEventSubscription" : {
"Type" : "AWS::RDS::EventSubscription",
"Properties" : {
"Enabled" : "true",
"EventCategories" : [
"availability",
"backup",
"configuration change",
"deletion",
"failover",
"failure",
"low storage",
"maintenance",
"notification",
"recovery",
"restoration"
],
"SnsTopicArn" : {"Ref": "NotificationTopic"},
"SourceType" : "db-instance"
}
},
"RDSCPUUtilization" : {
"Type" : "AWS::CloudWatch::Alarm",
"Properties": {
"ActionsEnabled": "true",
"AlarmActions": [
{
"Ref": "NotificationTopic"
}
],
"AlarmDescription": "RDS CPU Utilization",
"ComparisonOperator": "GreaterThanThreshold",
"Dimensions": [
{
"Name": "DBInstanceIdentifier",
"Value": {"Ref" : "DBInstanceId"}
}
],
"EvaluationPeriods": "1",
"InsufficientDataActions": [
{
"Ref": "NotificationTopic"
}
],
"MetricName": "CPUUtilization",
"Namespace": "AWS/RDS",
"Statistic": "Average",
"Period": "300",
"Threshold": "75"
}
},
"RDSFreeStorageSpace" : {
"Type" : "AWS::CloudWatch::Alarm",
"Properties": {
"ActionsEnabled": "true",
"AlarmActions": [
{
"Ref": "NotificationTopic"
}
],
"AlarmDescription": "RDS Free Storage Space",
"ComparisonOperator": "LessThanThreshold",
"Dimensions": [
{
"Name": "DBInstanceIdentifier",
"Value": {"Ref" : "DBInstanceId"}
}
],
"EvaluationPeriods": "1",
"InsufficientDataActions": [
{
"Ref": "NotificationTopic"
}
],
"MetricName": "FreeStorageSpace",
"Namespace": "AWS/RDS",
"Statistic": "Average",
"Period": "300",
"Threshold": "20000000000"
}
},
"RDSDiskQueueDepth" : {
"Type" : "AWS::CloudWatch::Alarm",
"Properties": {
"ActionsEnabled": "true",
"AlarmActions": [
{
"Ref": "NotificationTopic"
}
],
"AlarmDescription": "RDS Disk Queue Depth",
"ComparisonOperator": "GreaterThanThreshold",
"Dimensions": [
{
"Name": "DBInstanceIdentifier",
"Value": {"Ref" : "DBInstanceId"}
}
],
"EvaluationPeriods": "1",
"InsufficientDataActions": [
{
"Ref": "NotificationTopic"
}
],
"MetricName": "DiskQueueDepth",
"Namespace": "AWS/RDS",
"Statistic": "Average",
"Period": "300",
"Threshold": "10"
}
},
"RDSFreeableMemory" : {
"Type" : "AWS::CloudWatch::Alarm",
"Properties": {
"ActionsEnabled": "true",
"AlarmActions": [
{
"Ref": "NotificationTopic"
}
],
"AlarmDescription": "RDS Freeable Memory",
"ComparisonOperator": "LessThanThreshold",
"Dimensions": [
{
"Name": "DBInstanceIdentifier",
"Value": {"Ref" : "DBInstanceId"}
}
],
"EvaluationPeriods": "1",
"InsufficientDataActions": [
{
"Ref": "NotificationTopic"
}
],
"MetricName": "FreeableMemory",
"Namespace": "AWS/RDS",
"Statistic": "Average",
"Period": "300",
"Threshold": "536870912"
}
},
"RDSReadLatency" : {
"Type" : "AWS::CloudWatch::Alarm",
"Properties": {
"ActionsEnabled": "true",
"AlarmActions": [
{
"Ref": "NotificationTopic"
}
],
"AlarmDescription": "RDS Read Latency",
"ComparisonOperator": "GreaterThanThreshold",
"Dimensions": [
{
"Name": "DBInstanceIdentifier",
"Value": {"Ref" : "DBInstanceId"}
}
],
"EvaluationPeriods": "1",
"InsufficientDataActions": [
{
"Ref": "NotificationTopic"
}
],
"MetricName": "ReadLatency",
"Namespace": "AWS/RDS",
"Statistic": "Average",
"Period": "300",
"Threshold": "0.1"
}
},
"RDSWriteLatency" : {
"Type" : "AWS::CloudWatch::Alarm",
"Properties": {
"ActionsEnabled": "true",
"AlarmActions": [
{
"Ref": "NotificationTopic"
}
],
"AlarmDescription": "RDS Write Latency",
"ComparisonOperator": "GreaterThanThreshold",
"Dimensions": [
{
"Name": "DBInstanceIdentifier",
"Value": {"Ref" : "DBInstanceId"}
}
],
"EvaluationPeriods": "1",
"InsufficientDataActions": [
{
"Ref": "NotificationTopic"
}
],
"MetricName": "WriteLatency",
"Namespace": "AWS/RDS",
"Statistic": "Average",
"Period": "300",
"Threshold": "0.1"
}
},
"RDSDatabaseConnections" : {
"Type" : "AWS::CloudWatch::Alarm",
"Properties": {
"ActionsEnabled": "true",
"AlarmActions": [
{
"Ref": "NotificationTopic"
}
],
"AlarmDescription": "RDS Database Connections",
"ComparisonOperator": "GreaterThanThreshold",
"Dimensions": [
{
"Name": "DBInstanceIdentifier",
"Value": {"Ref" : "DBInstanceId"}
}
],
"EvaluationPeriods": "1",
"InsufficientDataActions": [
{
"Ref": "NotificationTopic"
}
],
"MetricName": "DatabaseConnections",
"Namespace": "AWS/RDS",
"Statistic": "Average",
"Period": "300",
"Threshold": "50"
}
}
}
}
I hope this info will be useful for you, and if you need any help feel free to use contact from on the main page.