rds_instance.yaml

#  Copyright 2018 1Strategy, LLC

#    Licensed under the Apache License, Version 2.0 (the "License");
#    you may not use this file except in compliance with the License.
#    You may obtain a copy of the License at

#        http://www.apache.org/licenses/LICENSE-2.0

#    Unless required by applicable law or agreed to in writing, software
#    distributed under the License is distributed on an "AS IS" BASIS,
#    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#    See the License for the specific language governing permissions and
#    limitations under the License.

# Dependencies:
# navitaire-vpc stack:
#   subnet ids for private subnets
#   vpc id 
# Sample CLI:
# aws cloudformation create-stack --stack-name abc-db --template-body file://rds_instance.yaml --parameters file://params-rds.json --capabilities CAPABILITY_NAMED_IAM --profile navitaire-pat
---
AWSTemplateFormatVersion: '2010-09-09'
Description: 'This CloudFormation script provisions an RDS instance(s) with a bastion host to create the navitaire database. It also creates DMS resources for replicating to the database.'


Parameters:

  AllocatedStorageParam:
    Description: The allocated storage size, specified in gigabytes (GB).
    Type: String
    AllowedPattern: "^([2-9]?[0-9]{1}|[0-9]{3,4}|1[0-5]?[0-9]{3}|16[0-2]?[0-9]{2}|163[0-7]?[0-9]{1}|1638[0-4]?)$"
    ConstraintDescription: "For SQL Server Express or Web Edition, the value must be a valid number between 20-16384. For SQL Server Standard or Enteprise editions, the value must be a valid number between 200 and 16384"

  AllowMajorVersionUpgradeParam:
    Description: If you update the EngineVersion property to a version that's different from the DB instance's current major version, set this property to true.
    Type: String
    AllowedValues: [true, false]
    ConstraintDescription: "Value must be boolean value."

  AutoMinorVersionUpgradeParam:
    Description: Indicates that minor engine upgrades are applied automatically to the DB instance during the maintenance window. The default value is true.
    Type: String
    AllowedValues: [true, false]
    #Default: true
    ConstraintDescription: "Value must be boolean value."

  BackupRetentionPeriodParam:
    Description: The number of days during which automatic DB snapshots are retained.
    Type: String
    AllowedPattern: "^([0-9]{1}|[1-2]{1}[0-9]{1}|3[0-5]{1})$"
    ConstraintDescription: "Value must be a valid number between 0-35."

  DBInstanceClassParam:
    Description: The name of the compute and memory capacity classes of the DB instance.
    Type: String
    Default: db.t2.medium

  EngineParam:
    Description: The name of the database engine to be used for this instance.
    Type: String
    AllowedValues: [sqlserver-ee, sqlserver-se, sqlserver-ex, sqlserver-web]
    ConstraintDescription: "Please specify either a sqlserver-ee, sqlserver-se, sqlserver-ex, or sqlserver-web engine for the RDS instance."
    Default: sqlserver-se

  SubnetIdsParam:
    Description: The subnet Ids to include in the DB subnet group.
    Type: List<AWS::EC2::Subnet::Id>
    # Default: [!ImportValue Navitaire-Private-Subnet-A, !ImportValue Navitaire-Private-Subnet-B]
  
  PublicSubnetParam:
    Description: The public subnet where your bastion will be created
    Type: String
    # Default: !ImportValue Navitaire-Public-Subnet

  AvailabilityZoneParam:
    Description: The availability zone into which the master RDS instance will be deployed.
    Type: AWS::EC2::AvailabilityZone::Name
    Default: us-west-2a

  RDSMasterUsernameParam:
    Description: The master user name for the DB instance. You will need this for the DMS resources as well.
    Type: String

  RDSMasterUserPasswordParam:
    Description: The master password for the DB instance. You will need this for the DMS resources as well.
    Type: String
    NoEcho: true

  MultiAZParam:
    Description: Specifies if the database instance is a multiple Availability Zone deployment.
    Type: String
    AllowedValues: [true, false]
    #Default: false
    ConstraintDescription: "Value must be boolean value."

  StorageTypeParam:
    Description: Storage type for the RDS instance
    Type: String
    AllowedValues: [standard, pg2, io1]
    Default: standard

  LatestAmiId:
    Type: 'AWS::SSM::Parameter::Value<AWS::EC2::Image::Id>'
    Default: '/aws/service/ami-amazon-linux-latest/amzn-ami-hvm-x86_64-gp2'

  paramVpcId:
    Type: String

  MyIpAddress:
    Type: String
    
Resources:

  RdsSubnetGroup:
    Type: "AWS::RDS::DBSubnetGroup"
    Properties: 
      DBSubnetGroupDescription: A collection of subnets for the Replication RDS instance.
      DBSubnetGroupName: !Sub "${AWS::StackName}-db-subnet-group"
      # Pull Subnet IDs from VPC resources
      SubnetIds: !Ref SubnetIdsParam

  RdsInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: !Ref AllocatedStorageParam
      AllowMajorVersionUpgrade: !Ref AllowMajorVersionUpgradeParam
      AutoMinorVersionUpgrade: !Ref AutoMinorVersionUpgradeParam
      AvailabilityZone: !Ref AvailabilityZoneParam
      BackupRetentionPeriod: !Ref BackupRetentionPeriodParam
      CopyTagsToSnapshot: true
      DBInstanceClass: !Ref DBInstanceClassParam
      DBInstanceIdentifier: !Sub "${AWS::StackName}-db"
      DBSubnetGroupName: !Ref RdsSubnetGroup
      Engine: !Ref EngineParam
      LicenseModel: license-included #Required if using sql server standard or enterprise.
      MasterUsername: !Ref RDSMasterUsernameParam
      MasterUserPassword: !Ref RDSMasterUserPasswordParam
      MonitoringInterval: 0
      MultiAZ: !Ref MultiAZParam
      PubliclyAccessible: false
      StorageType: !Ref StorageTypeParam
      VPCSecurityGroups: [ !Ref RdsInstanceSecurityGroup ]

  BastionInstance:
    Type: AWS::EC2::Instance
    Properties:
      InstanceType: t2.micro
      SecurityGroupIds: [!Ref BastionInstanceSecurityGroup]
      ImageId: !Ref LatestAmiId
      KeyName: pat-keypair
      NetworkInterfaces:
        - AssociatePublicIpAddress: true
          SubnetId: !Ref PublicSubnetParam
          DeviceIndex: "0"
      UserData:
        Fn::Base64:
          !Join [
            "\n",
            [
              "#!/bin/bash",
              "yum update -y",
              "pip install pymssql",
              "echo 'import pymssql",
              "import sys",
              "",
              "def create_db(server,user,password,dbname):",
              "  conn = pymssql.connect(server, user, password, \"master\")",
              "  cursor = conn.cursor()",
              "",
              "  conn.autocommit(True)",
              "  cursor.execute(\"CREATE DATABASE {}\".format(dbname))",
              "  conn.autocommit(False)",
              "",
              "  cursor.execute(\"SELECT name FROM master.dbo.sysdatabases\")",
              "  row = cursor.fetchone()",
              "  while row:",
              "    print(row)",
              "    row = cursor.fetchone()",
              "",
              "  conn.close()",
              "",
              "if __name__ == \"__main__\":",
              "  server = sys.argv[1]",
              "  user = sys.argv[2]",
              "  password = sys.argv[3]",
              "  dbname = sys.argv[4]",
              "  create_db(server,user,password,dbname)' > create-db.py", #echo the functin to a file.
              !Join [" ",
                [
                  "python create-db.py",
                  !GetAtt RdsInstance.Endpoint.Address, #endpoint
                  !Ref RDSMasterUsernameParam, #username
                  !Ref RDSMasterUserPasswordParam, #password
                  "patdb" #database name
                ]
              ]
            ]
          ]
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-bastion-host

  BastionInstanceSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Enable SSH access via port 22
      VpcId: !Ref paramVpcId
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-bastion-host-sg
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: 22
        ToPort: 22
        CidrIp: !Ref MyIpAddress

  RdsInstanceSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties: 
      GroupName: !Sub ${AWS::StackName}-rds-landing-sg
      GroupDescription: Security Group for the replication RDS instance.
      VpcId: !Ref paramVpcId
      SecurityGroupEgress:
        - IpProtocol: "-1"
          CidrIp: 0.0.0.0/0
      SecurityGroupIngress:
        - Description: Bastion host IP address
          IpProtocol: tcp
          FromPort: 1433
          ToPort: 1433
          SourceSecurityGroupId: !GetAtt BastionInstanceSecurityGroup.GroupId
      Tags:
        - Key: Name
          Value: !Sub ${AWS::StackName}-rds-sg

Outputs:
  RdsInstanceIdentifier:
    Description: Replication RDS Instance Id
    Value: !Ref RdsInstance

  BastionInstance:
    Description: The linux box created for access to your RDS instance
    Value: !Ref BastionInstance

  # RdsInstanceSecurityGroup:
  #   Description: security group for the target RDS database
  #   Value: !Ref RdsInstanceSecurityGroup
  #   Export:
  #     Name: DestinationRdsSecurityGroup
  
  # RdsEndpoint:
  #   Description: The endpoint/server name for the RDS instance.
  #   Value: !GetAtt RdsInstance.Endpoint.Address
  #   Export:
  #     Name: DestinationRdsEndpoint