CloudFormation: MS SQL Multi-AZ

dbAfter 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.