HOW TO CREATE RDS-MSSQL WITH CLOUDFORMATION AND ANSIBLE

HOW TO CREATE RDS-MSSQL WITH CLOUDFORMATION AND ANSIBLE

INTRODUCTION

Amazon Relational Database Service (Amazon RDS) is a relational database service that makes it easier to set up, operate, manage 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

CLOUDFORMATION

AWS CloudFormation is a simple way to create and manage a collection of AWS resources by provisioning and predictably updating them. For instance, we usually do manual work in the AWS console to create or delete resources

What if,

               –    We need to create the same resource in another region

               –    In another AWS Account 

               –    And everything got messed up or deleted accidentally.

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

The building blocks of the 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

ANSIBLE

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

RDS – RELATIONAL DATABASE SERVICE

It is a distributed relational database service by Amazon Web Services. It is a web service running “in the cloud” designed to simplify the setup, operation, and scaling of a relational database for use. 

PREREQUISITES

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

PROCEDURE

Step 1 – Create CloudFormation template

A template is a declaration of the AWS resources that make up a stack. The template is stored as a text file whose format complies with the JavaScript Object Notation (JSON) or YAML standard. A sample template named cloudformation.j2 can be found below.

Inside cloudformation.yaml.j2, define dynamic resource names as parameters.  The resource section with reference to the parameters will read values from the environment.

Cloudformation.yaml.j2

Cloudformation template in Brief

• Resource: In the resources section, give a resource name (eg: MyDB ) as in the above file. In the case of RDS add another resource (eg: SGBaseIngress) which is the firewall ingress rule. This will act as a firewall for our Database instance to get access from the Internet.

• Properties: Here we define properties for the DB  that will refer to values provided as environment variables. The important attributes are

1. AllocatedStorage: Allocated storage is the total size allocated 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

3.  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
4. MultiAZ: This field is for High Availability across multiple Availability Zone in AWS Region

5. Publicly Accessible: This value should be true to make dB accessible to the internet

6. StorageType: Specifies the storage type to be associated with the DB instance.

Step 2 – Set up Ansible server

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

Step 3 – Create an Ansible playbook.

Create a file named play.yaml and copy the following contents in it.

– Here lookup option is used to fetch cloudformation.yaml.j2 and Environment variables for the playbook

Step 4 – Set up environment variables and run playbook

Create a file named start.sh and copy all the environment variables needed. At the end of the script, add the ansible add-hoc command to run the playbook.

Execute start.sh, 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.

Login to AWS console and navigate to Cloud formation stacks, you can see the resources getting created

Cloud formation Console:

Step 6 – Cleaning the infrastructure.

If you want the created resources to be deleted below is the ansible-playbook 

Cleanup.yaml 

Run the “ansible-playbook cleanup.yaml” command to execute cleanup.

You will get the output as below.

PLAY [Removing "MyRDSStack" ]
**********************************************************************************
TASK [Gathering Facts]
**********************************************************************************
ok: [localhost]
TASK [delete a stack]
**********************************************************************************
changed: [localhost]
PLAY RECAP
**********************************************************************************
localhost : ok=2  changed=1   unreachable=0   failed=0   skipped=0   rescued=0    
ignored=0

Login to AWS console and navigate to Cloud formation stacks and in Cloud formation Console and confirm the output.

File structure

CONCLUSION

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

REFERENCES

• AWS CloudFormation 
• Ansible CloudFormation