RDS Budgeting

As the end of the month has drawn closer, I’ve been receiving emails from AWS in regards to my RDS budget’s expiration.

AWS Expiration Message

When I designed the RDS budget, I designed it with the DinoStore project in mind. As the DinoStore project was due on the 29th of April, I set the budget to also finish at the end of the month.

So, with the RDS budget coming to an end, I decided that it would be a good time to check my monthly expenditure in comparison to the budgeted amount. My first look at the budget list showed the RDS forecast to be lower than what I expected, when also considering the RDS billing amount:

RDS All Regions

I considered that the underestimated RDS budget was likely due to an incorrect parameter that I had given it, so I adjusted it to focus on the availability zones; ap-southeast-2, and us-east-1, which correspond to Sydney and North Virginia.
Edit DinoStore RDS Budget

Once these adjustments had been made, the RDS budget showed a current expenditure of $9.28 USD, which matches the billing amount.
RDS Budget Details

Although, my adjustment were made at the end of the budget’s lifespan, the realization and application of correcting my budget will enable me to create a more accurate budget plan for my next project.
From an accounting perspective, rather than a technical one, the RDS budget allowance was an accurate prediction of expenditure for the DinoStore project. This knowledge may be able to be extrapolated into my next project in order to provide a reliable budget in relation to the project and its progress.

Resolving Bills in AWS

Over the past week, I have been keeping a close eye on my AWS billing log so as to determine which services have charging me without my knowledge.

On the 14/4/2017, I received an EC2 alarm that I was able to determine that it came from the EBS service. To my knowledge, that was the cause of credit expenditure, so I shut it down.

However, I received another alarm notification on the 17/4/2017, this time in regards to my RDS budget.
Billing Alarm 17_4

This concerned me, as I had removed my instances from being Multi-AZ enabled, which meant that I was running them from the free tier. To gain more understanding, I looked through the billing specifics for the RDS service.
RDS N Vir Bill 17_4 2237

What I determined, was that my instances were being charged under the North Virginia region. Due to my assumption that they were running completely within the free-tier conditions, I was not using best practices, and was keeping my instances running while not in use. I had not considered that my decision to have them backed up in another region would invoke a fee.

My next move, was to snapshot and delete my main instance. I chose not to delete the replica immediately, as in my previous experience with backing up and restoring instances, I had found the replica to be more difficult to re-establish during the restoration process.

I checked my bills the next morning to find that the running replica instance was still charging my account. I then proceeded to take a snapshot of the instance, then delete it. My concern for unnecessary charges took precedence over the increased difficulty for restoration. The following screenshot shows the billing amount for the RDS service in North Virginia.

RDS N Vir Bill 18_4 0933 RDS Closed NVR

Billing amount at 09:33am 18/04/2017

I then took another screenshot later in the day to check whether there was any increase in the billing amount.

RDS N Vir Bill 18_4 1145 RDS Closed NVR

Billing amount at 11:45am 18/04/2017

The lack of increase confirmed that it was the two running instances that were causing my credit to be charged.

Just in case there were any other expenditures that were increasing my bill, I also checked the overall billing amount during the course of the day, Once at 11:45am and once at 01:25pm. I saw no increase in the overall budget. This meant that I had managed to resolve all of the unnecessary expenditures.

Lab 2: Using RDS with ASP.NET Applications

Launching the MySQL RDS Instance and related MySQL connection
The first action required to use RDS with ASP.NET applications, is to launch an RDS instance. In this project the specifications for the instance are as follows:

  • MySQL instance
  • Micro instance
  • Multi-AZ deployment (Not eligible on Free-Tier)
  • General purpose storage type
  • 5GB storage
  • DB Instance Identifier: DinoStoreInstance
  • Master username
  • Master password
  • Default VPC
  • Security group: Create new security group
  • Database name: dinostoredb
  • Disabled automatic back-ups

In the RDS security group, I made the input rules to be MySQL with sources for both my home IP address and NMIT’s address. This was to allow me to access it through MySQL at both places.
Inked008 Security Group Inbound Sources_LI

In MySQL workbench, a new connection is created, this time with the RDS instance endpoint as the Hostname. The connection name is AWS Dinostore, with the username and password being the ones created during the instance set-up.
009 MySQL Connection

In the connection, the create-tables script from lab one is used to create the tables, but within the cloud. Doing this also creates another schema called dinostoremembershipdb. The products are then added to the product table by uploading the CSV file that contains the S3 bucket image references for the image field.
010 MySQL import db

Creating a read-replica RDS and its related MySQL connection
The next move is to create a read replica of the dinostoredb instance. This is done by selecting the instance and clicking ‘Create Read Replica’ in the ‘Action’ menu. The instance is identified as “dinostoreinstancereplica”, and keeps the same class but is set in a different availability zone.
014 RR Instance Specs

In MySQL, another connection can be made, this time with the Hostname linking to the replica. The connection name is AWS DinoStore Replica and contains the same  username and password as the AWS DinoStore DB connection.
016 MySQL Replica Test

Configuring Visual Studio
In Visual Studio, in the Web.Config page, the code can be organized to use the cloud database rather than the local one. This is done by editing the connection strings. For both the “DefaultConnection” and “StoreSqlDB” the local host IP is replaced with the main RDS instance’s endpoint. Another connection line is also created by copy-and-paste of one of the above lines, but replacing the name with “StoreSqlDbReplica”, and replacing the hyperlink to be associated to the read replica instance.
018 VS Adding Replica to WebConfig

In the Default.aspx page, in the ConfigurationManager.ConnectionStrings, the “StoreSqlDb” should be changed to “StoreSqlDbReplica” as this organizes the images to be taken from the replica rather than the main database. The lab text makes mention that this reduces the load on the primary database and so leaves more cycles available for writes.

The Visual Studio Code adjustments can be tested by building the code and running it in a browser.
006 Updated Dinostore with png files attached -prep

Inputting data into membership database from browser
As of current, there are no tables in the cloud membership database in the MySQL Workbench.

In the site being hosted on the browser, a new account and user can be created. This should create a cloud membership database table and also create one for the custom table.
020 Web Login Page

In MySQL, in the dinostoremembershipdb tables, there is now a my_asnet_users table that contains basic information about the user.
039 MySQL my_aspnet_user

If the master RDS server is rebooted while the site is still running, a couple of things occur: The website can still be seen as all of this data is held in the replica, however, if I try to make a new account or logout/login, the site crashes as this information is still being sourced from the master RDS.

Challenges
I encountered a few challenges with this lab that stumped me for quite a while, most that were only resolved by asking for help from other classmates.

The first challenge that I encountered arose from my choice to specifically follow the instructions and create my RDS with Multi-AZ deployment. The Multi-AZ deployment enabled instance costs to run, and I, somewhat unaware and unfamiliar with how RDS instances work, left the instance to run overnight. As I stated in this week’s budget blog, my decision cost me (in USD credits), and so I started using snapshots in order to reduce my expenses. This is where the second challenge arose.

The second challenge involved the restoration of my instances from their snapshots, and while this part was successful, I was unable to connect to my restored instances. (Meanwhile, Multi-AZ RDS is charging me now that I’ve restored it.) After a few hours of searching for causes of the problem through Visual Studio, MySQL workbench, the information of my RDS instances, and AWS help guides, I finally managed to determine the source of my inability to connect: The restored RDS instances were automatically connecting to the wrong security group. By modifying the instances to connect with the right security group, I was able to form the connections from the databases in MySQL to my RDS instances. This meant success once more, and a means to progress with the lab. By my understanding,  I had resolved the cost issue of running a non-free tier instance, and how to properly reconnect to the restored instance.

Here’s where my third challenge arose. When I next went to work on my lab, I was again unable to properly connect to my databases. I checked, and made sure that the security groups were correct, I made sure that the security group itself contained the polytech’s IP and my home IP, and I made sure that I had copied the endpoints correctly into the areas required in Visual Studio and MySQL. I had exhausted my understanding of what would be causing the connection problem, and so I tried creating new RDS instances and new MySQL connections to see if there was a problem connecting during the creation of the MySQL connections. As I discovered, there was indeed a problem during the inception of the MySQL connection, but not for a reason I could understand.

It was at this point that I asked for help from a classmate. He asked about the inbound rules for my security group. To my knowledge, I had used my home IP address, but I figured that I may as well give it a try and reinstate it in one of the inbound rules. Unfortunately challenge number four came along at this point.

Challenge number four was less of a challenge, and more of an unfortunate situation that somehow occurred. During the time of seeking advice, my computer that holds my Visual Studio and MySQL programs had a slight problem and I was unable to use it properly. I managed to turn it off and on again, which resolved my utilization problem, but now MySQL had cleared itself of all of my connections. This challenge was easily solved by creating new connections and ensuring that the tabular data was correctly stored and transferred between the connections. This resolution however, could not occur without first resolving the third challenge.

In the DinoStore security group, I reinstated my IP as an allow rule for inbound traffic. As it turned out, my classmate was correct in his line of thinking, and my IP address had changed. Once the security group was updated, I was able to connect to the RDS instances through MySQL again, and so progress on with my lab.

In reviewing the challenges that I faced during the course of this lab, although time consuming, I have found that the end result of these challenges are that they have built up my understanding and confidence in working with RDS and AWS, and helped me to gain a greater idea of the interconnections between AWS, Visual Studio, and MySQL in hosting a website. This troubleshooting should help me with the further issues that I will face during the course of this project.

Budget Report: 09/04/2017

Having been working on the DinoStore lab project for a little over a week now, it is time for me to check how I am progressing with my S3 budget.

As it turns out, the Mulit-AZ deployment option for the RDS created is quite expensive to run for long periods of time. As I am still new to using any cloud service at this point in time, so when I created the RDS instance, I was unsure of how to close it without losing all my data. As such, I left it running overnight, which cost me for my lack of knowledge and lack of sensibility to research how to efficiently use an RDS instance.

RDS Billing Table

RDS cost after 24 hours

After realizing my mistake, I figured out what a snapshot was, and how to use it in saving RDS based expenses. The use of the snapshot caused further problems for me, which will be elaborated in DinoStore: Lab 2 blog, and resulted in quite a few more hours of running the costing RDS instance with no progress to show for it.

RDS Billing Table 7_4

RDS Billing on 08/04/17 at 08:34pm

In order to save short-term future costs, I have created a new RDS that runs on the free tier available for AWS. This will save me credits as I learn how to better use the RDS instance so that if I find myself needing to use the Multi-AZ one later in the project, I should have the experience to be able to set it up without causing unnecessary cost to myself.

I have also set up a new budget that focuses solely on any credit expenditure for RDS. This is because I spent at least 200% of the S3 budget, and that expense was only from the RDS service.

Budget Alarm Email

Email Notification for S3 Budget

New Billing Alarm RDS Focussed

RDS Budget for DinoStore

Introduction to Amazon Relational Database Service (RDS)-Linux

Introduction and Aim
The purpose of this lab is to create and use an Amazon Relational Database service through AWS. Amazon RDS is a cloud based service that deals with databases. Databases can be created, operated, and scaled within the Amazon RDS, which has the ability to make MySQL, PostGRE, Oracle, and SQL Server databases.

 

Goals

  • Create an Amazon Relational Database Service (RDS) instance
  • Connect to the RDS instance with client software

 

Creating a Relational Database Service (RDS) instance
RDS is a service of its own within the Amazon Management Console, rather than being one created through EC2. The lab script requires the MySQL database to have many specific features, These are as follows:

  1.          Specify DB Details
    InkedMySQL database option_LI
  2. DB Instance Class: db.t1.micro (The free tier one)
    DB Instance Class db.t1.micro
    AWS makes mention that this DB instance class is a ‘previous generation instance, that may have lower performance and higher cost than the newer generation.’ Because of this, I looked into the db.t2.micro, which is the current generation instance. The current instance has higher memory and network performance while still being on the free tier, so I will be using the current generation instance in this lab.
    DB Instance Class db.t2.micro
  3. Multi-AZ Deployment: No
  4. Storage Type: General Purpose (SSD)
  5. Allocated Storage: 5
  6. DB Instance Identifier: RDSLab
  7. Master Username: AWSMaster
  8. Master Password: AWS12345
  9. Confirm Password: AWS12345
    Specify DB Details Full
  10.          Next Step->Configure Advanced Settings
  11. Publicly Accessible: No
  12. VPC Security Group(s): Choose a security group containing the text qls. I’m using a security group that I’ve created as I don’t have access to the QwikLabs one.
  13. Database Name: RDSLab
    Configure Advanced Settings
  14. Backup Retention period: 0 days (to disable automatic backups)
    CAS Part 2
  15.           Launch DB Instance

Now that the database instance has been launched, it is important to double check the security groups of the selected VPC and make sure that the inbound rules contain: Type-MySQL/Aurora (3306) with Source-0.0.0.0/0.
Editing Inbound Rules of SG

 

Create an Amazon Linux instance from an Amazon Machine Image (AMI)
Under the EC2 Launch Instance, the Amazon Linux AMI is selected. The instance type is kept as default, which is t2.micro. The next steps, ‘Configure Instance Details’, and ‘Add Storage’, are kept with their default settings. In the ‘Tag Instance’ step, the value given for the name attribute is RDS Free Lab. The final step is to review and launch.
RDS Free Lab Instance

Connecting to Amazon EC2 instance via SSH
Once the instance is launched, the PuTTY Secure Shell client is used to connect to the server. This involves using the instance’s public DNS value into the PuTTY Host name box, prefixed by <ec2-user@>. In the category list, under the SSH option, the Auth option can be clicked which will provide a ‘Private key file for authentication’ box. This is where I use my private key that I’ve previously created.

Connecting to the RDS instance
Within the terminal that opens up, the command ‘sudo yum install mysql’ is typed in, and the install agreement is accepted.
Install mysql

Once installed connect to MySQL, the following text is typed in, with the endpoint name of the RDS instance.
‘mysql –host cjcfraykqpwn.rds.ap-southeast-2.amazonaws.com –password –user AWSMaster’.
This prompts for the AWS12345 password that was created earlier.
InkedEnter mySQL_LI
The darker text at the top is where I accidentally typed the command incorrectly.

The MySQL is now logged into, and the mysql> prompt is visible. The ‘show database’ command can be entered in order to check whether any records return.
mySQL Show DatabasesThe returned output shows that the RDS instance has been connected to successfully.

 

Conclusion
I found this to be an interesting lab with using bash to install MySQL and connect to the RDS. Prior to attempting the Linux RDS lab, I had attempted to complete the Windows RDS Lab. I’m curious to find out whether the Windows’ VM command tool would be as successful in connecting to the RDS.