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
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)."
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.
$ sudo apt-get update
$ sudo apt-get install ansible python3 python-pip3 -y
$ 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.
- 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.
#!/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
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 **
---
- 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
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
About The Author
A. Nagesh
Head 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