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
- One Ubuntu 18.04 server with ansible server setup.
- 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
- AllocatedStorage: Allocated storage is total size allocate 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
- SQLServer-SE
- 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 internet
- 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:
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
