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

One Ubuntu 18.04 server with ansible server setup.
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

.fusion-syntax-highlighter-38 > .CodeMirror, .fusion-syntax-highlighter-38 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-38 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-38 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax HighlighterAWSTemplateFormatVersion: '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)."

Resources: SGBaseIngress: Type: AWS::EC2::SecurityGroupIngress Properties: GroupId: !Ref VPCSecurityGroups 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 }### 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
AllocatedStorage: Allocated storage is the total size allocated for the DB
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
** **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
MultiAZ**: **This field is for High Availability across multiple Availability Zone in AWS Region
Publicly Accessible**:** This value should be true to make dB accessible to the internet
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.

.fusion-syntax-highlighter-39 > .CodeMirror, .fusion-syntax-highlighter-39 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-39 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-39 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter$ sudo apt-get update .fusion-syntax-highlighter-40 > .CodeMirror, .fusion-syntax-highlighter-40 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-40 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-40 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter$ sudo apt-get install ansible python3 python-pip3 -y.fusion-syntax-highlighter-41 > .CodeMirror, .fusion-syntax-highlighter-41 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-41 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-41 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter$ sudo pip install boto boto3 botocore ansible awscli### Step 3 – Create an Ansible playbook. Create a file named play.yaml and copy the following contents in it.

.fusion-syntax-highlighter-42 > .CodeMirror, .fusion-syntax-highlighter-42 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-42 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-42 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter- 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 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.

.fusion-syntax-highlighter-43 > .CodeMirror, .fusion-syntax-highlighter-43 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-43 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-43 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter#!/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.yamlExecute 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.

Cloud formation Console:

Step 6 – Cleaning the infrastructure.

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

**Cleanup.yaml **

.fusion-syntax-highlighter-44 > .CodeMirror, .fusion-syntax-highlighter-44 > .CodeMirror .CodeMirror-gutters {background-color:var(--awb-color1);}.fusion-syntax-highlighter-44 > .CodeMirror .CodeMirror-gutters { background-color: var(--awb-color2); }.fusion-syntax-highlighter-44 > .CodeMirror .CodeMirror-linenumber { color: var(--awb-color8); }Copy to ClipboardSyntax Highlighter---

name: Removing "{{ lookup('env','RDS_STACK_NAME') }}" hosts: localhost connection: local tasks:
- name: delete a stack cloudformation: aws_access_key: "{{ lookup('env','ACCESS_KEY') }}" aws_secret_key: "{{ lookup('env','SECRET_KEY') }}"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

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

About The Author### A. NageshHead of Delivery | Cloud Control

Head of Delivery with more than five years of experience in supporting, automating, and optimizing deployments to hybrid cloud platforms using DevOps processes, CI/CD, containers and Kubernetes in both Production and Development environments

RDS-MSSQL- * On Linked-In