How to Create RDS-MSSQL with CloudFormation and Ansible

INTRODUCTION

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the AWS Cloud. This article will help you to create RDS-MSSQL in AWS Cloud with CloudFormation and Ansible.

GET TO KNOW THE RESOURCES AND TOOLS USED

  • ANSIBLE

Ansible is an open-source software provisioning, configuration management, and application-deployment tool enabling infrastructure as code. We are going to run cloud formation template using Ansible’s Cloud Formation module instead of AWS CLI.

  • CLOUDFORMATION

AWS CloudFormation provides users with a simple way to create and manage a collection of Amazon Web Services (AWS) resources by provisioning and updating them in a predictable way. For instance, we usually do manual work in AWS console to create or delete resources.

What if,

  • We need to create same resource in another region
  • In another AWS Account
  • And everything got messed up or deleted accidentally.

We going to make a declarative way of defining the infrastructure. CloudFormation use template as input which can be YAML or JSON file

The building blocks of CloudFormation template are,

  • Resources: Your AWS resources declared in the template (mandatory)
  • Parameters: The dynamic input for your template
  • Mappings: The static variable for your template
  • Outputs: Reference to what has been created
  • Conditionals: List of condition to perform resource creation
  • RDS – RELATIONAL DATABASE SERVICE

It’s a managed DB Service for Database use SQL as a query language. It allows you to create Database which are managed by AWS in the cloud. RDS Database managed by AWS are: – MySQL, Postgres, Oracle, Microsoft SQL Server (MSSQL), Aurora (AWS Proprietary Database).

PREREQUISITES

  1. One Ubuntu 18.04 server with ansible server setup.
  2. An AWS account access (programmatic access) with sufficient privileges.

PROCEDURE

Step 1 – Create CloudFormation template

In CloudFormation template, lets define dynamic value in parameter, which make this template flexible to create other resources with different configuration.

AWSTemplateFormatVersion: '2010-09-09'
Description: 'This CloudFormation script provisions a RDS instance(s). '
Parameters:
  DBInputCIDR:
    Description: CIDR  to allow access to DB instances
    Type: String
    AllowedPattern: "(\\d{1,3})\\.(\\d{1,3})\\.(\\d{1,3})\\.(\\d{1,3})/(\\d{1,2})"
    ConstraintDescription: must be a valid IP CIDR range of the form x.x.x.x/x.
  DBPortNumber:
    Description: The port number on which the database accepts connections.
    Type: Number
    Default: '1433'
    MinValue: '1433'
    MaxValue: '1433'
    ConstraintDescription: 1150-65535 except for 1434, 3389, 47001, 49152, and 49152    
  AllocatedStorage:
    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: "Value must be a valid number between 20-16384."
  DBInstanceClass:
   Description: The name of the compute and memory capacity classes of the DB instance.
   Type: String
   Default: db.t2.micro
  Engine:
   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."
  MasterUsername:
   Description: The master user name for the DB instance.
   Type: String
  MasterUserPassword:
   Description: The master password for the DB instance.
   Type: String
    NoEcho: true
  VPCSecurityGroups:
    Description: Specifies if the database instance is a multiple Availability Zone deployment.
    Type: String
     ConstraintDescription: "Please provide valid ids for the security group(s)."

Create a file named cloudformation. j2 and copy the following contents.

Resources:
  SGBaseIngress:
    Type: AWS::EC2::SecurityGroupIngress
    Properties:
      GroupId: !Ref VPCSecurityGroup
      IpProtocol: tcp
      FromPort: !Ref DBPortNumber
      ToPort: !Ref DBPortNumber
      CidrIp: !Ref DBInputCIDR
  MyDB:
    Type: "AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: !Ref AllocatedStorage
      AllowMajorVersionUpgrade: false
      AutoMinorVersionUpgrade: false
      BackupRetentionPeriod:  7
      CopyTagsToSnapshot: true
      DBInstanceClass: !Ref DBInstanceClass
      Engine: !Ref Engine
      #EngineVersion: "14.00.3192.2.v1"
      LicenseModel: license-included
      MasterUsername: !Ref MasterUsername
      MasterUserPassword: !Ref MasterUserPassword
      MultiAZ: false
      MonitoringInterval: 0
      PubliclyAccessible: true
      StorageType: gp2
      Tags:
        - Key: Name
          Value: !Sub
          - ${AWS::StackName}-${Name}
          - { Name: !Ref Engine }
  • Resource:

In resources section, we need to define name of resources. We named this resources as ‘MyDB’, and add firewall ingress rule to Security group, which will act as firewall for our Database instance,  to give access to DB from Internet. Resources type in template are in the form of AWS : : aws-product-name : : data-type-name

  • Properties:

In properties section DB instance value is mandatory. Other properties should be declared as per defaults. Important attributes are

  1. AllocatedStorage: Allocated storage is total size allocate for the DB
  2. Engine: which type of SQL service we need to use. Some of the valid values of Engine are:
    • Aurora (for MySQL 5.6-compatible Aurora)
    • Aurora-MySQL (for MySQL 5.7-compatible Aurora)
    • Aurora-PostgreSQL
    • MariaDB
    • MySQL
    • Oracle-EE
    • Oracle-SE2
    • Oracle-SE1
    • PostgreSQL
    • SQLServer-EE
    • SQLServer-SE
  1. DBInstanceClass: Check AWS documentation for supported instance class for your specific engine here. We use instance class as db.t3.xlarge, for engine SQLServer-SE
  2. MultiAZ: This field is for High Availability across multiple Availability Zone in AWS Region
  3. Publicly Accessible: This value should be true to make dB accessible to internet
  4. StorageType: Specifies the storage type to be associated with the DB instance.

Step 2 – Setup Ansible server

Run the following commands to enable AWS support for Ansible server.

$ sudo apt-get update
$ sudo apt-get install ansible python3 python-pip3 -y
$ sudo pip install boto boto3 botocore ansible awscli

Step 3 – Create Ansible playbook

Next, create a playbook to run CloudFormation template. Create a file named play.yaml and copy the following contents in it.

- become: true
  hosts: 127.0.0.1
  name: Run my CloudFormation stack
      cloudformation:
        stack_name:  "{{ lookup('env','RDS_STACK_NAME') }}"
        aws_access_key: "{{ lookup('env','AWS_ACCESS_KEY') }}"
        aws_secret_key: "{{ lookup('env','AWS_SECRET_KEY') }}"
        region: "{{ lookup('env','REGION') }}"
        state: "present"
        template_body: "{{ lookup('template', 'cloudformation.yaml.j2') }}"
        template_parameters:
          DBInstanceClass: "{{ lookup('env','DBINSTANCECLASS') }}"
          Engine: "{{ lookup('env','ENGINE') }}"
          MasterUsername: "{{ lookup('env','SQLSERVER_USERNAME') }}"
          MasterUserPassword: "{{ lookup('env','SQLSERVER_PASSWORD') }}"
          AllocatedStorage: "20"
          DBPortNumber: "{{ lookup('env','SQLSERVER_DBPORT') }}"
          DBInputCIDR: "{{ lookup('env','RDS_CIDR') }}"
          VPCSecurityGroups: "{{ lookup('env','RDS_SECURITYGROUPID') }}"
        on_create_failure: DELETE
  • Here lookup option will look cloudformation.j2 template in template folder

Step 4 – Setup environment variables and run playbook

Finally, create file named start.sh and copy all the environment variables needed. At the end of the bash script add the Ansible add-hoc command to run the playbook.

#!/bin/bash
# MSSQL
export RDS_STACK_NAME=MyRDSStack'
export ENGINE=sqlserver-se'
export DBINSTANCECLASS=db.t3.xlarge'
export SQLSERVER_USERNAME=appz'
export SQLSERVER_PASSWORD='$dbpass
export SQLSERVER_DBPORT=1433'
export RDS_CIDR=0.0.0.0/0'
export RDS_SECURITYGROUPID=yoursgid'
ansible-playbook play.yaml

Now you will get the output as below.

PLAY [playbook for running aws_cft_rds] ****************************************

TASK [Gathering Facts] *********************************************************
ok: [127.0.0.1]

TASK [task for aws_cft_rds] ****************************************************

TASK [aws_cft_rds : Run my CloudFormation stack] ******************************
changed: [127.0.0.1]

PLAY RECAP *********************************************************************
127.0.0.1                  : ok=2    changed=1    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

Step 5 – Check the output

After creating all the resources, let’s check the AWS console for created resources.

In Cloud formation Console:

, CLOUDCONTROL

CONCLUSION

Here we have created RDS-MSSQL DB in AWS Cloud with CloudFormation and Ansible. We have our infrastructure as code, and it can be used to delete and spin up the entire infrastructure easily. Hope it benefits you.

REFERENCES

About The Author

, CLOUDCONTROL

Asik Rasool

Cloud Dev-Ops Engineer | Cloud Control