Module 2: Using RDS with ASP.NET Applications

In module 1: Provisioning Durable storage with S3, I discussed how to move local storage files and images to the cloud storage and how to integrate cloud files link in website sources code.

In this module2: I will migrate my local MySQL database to Amazan MySQL RDS. Therefore, my website data will be access and restore in the Cloud database instead of my local machine.

MySQL is the popular one source relational database system. By the help of AmazonRDS, it is very easy to setup, operate and scale MySQL deployment in the cloud. Amazon RDS for MySQL provides amazon clients access to the capabilities of an existing MySQL database engine. As a result, we can use our existing application and tools whose we used with our local system, now we can use those with amazon RDS. Amazon RDS automatically patches the database software and backs up the database, storing the backups for a user-defined retention period and enabling point-in-time recovery.

I will also create amazon RDS read-only replica for enhanced performance and durability for database (DB) instances. The read replica operates as a DB instance that allows only read-only connections. Your primary production DB instance as the source database when you create a read replica.  Amazon RDS takes a snapshot of the source instance and creates a read-only instance from the snapshot.

I will also discuss how to create MySQL RDS instance and how I will use RDS with ASP.NET application.

Task lists for module 2

  • Launch a new MySQL RDS instance
  • Connect to the AWS RDS from the local machine MySQL workbench
  • Create a read-replica of dinostoredb in the Amazon RDS
  • Create a new connection to the replica in MySQL workbench and Test the new databases
  • To changes Code by building the project, and opening it in a browser
  • Check out cloud dinostoremembershipdb tables in MySQL workbench
  • Check the replica database working while rebooting the primary database

The architecture of the project:

diagram1.png

Steps for the task: Launch a new MySQL RDS instance 

As my local database system is the MySQL database, I will migrate my local database in the cloud platform. Therefore, I chose MySQL RDS instance because of similarity of table and attribute formats, easy to establish connectivity, and familiar to operate and maintenance. I will launch a New MySQL RDS instance for creating my cloud database system.

  1. Click RDS under Database from services menu in the management console. Click “Launch a DB instance” from Instance create instance panel. Then choose a DB instance engine “MySQL” and click select.1
  2. Now you have to choose Production database plan.Select MySQL database instance. and click Next Step.2.jpg
  3. Now you have to specify the DB details. I chose DB instance class db2.t2.miceo -1vCPY, 1 GB memory because it is Burst Capable Current Generation. The facilities of Burst Capable Current Generation is that provide baseline performance level with the ability to burst to full CPU usage. Note: This DB instance class requires that the DB instance is in a VPC.  If you want to move your existing DB to db2.t2 Instance, you must require a VPC. Without a VPC, You can’t move your existing Database.It is also well suited for the application with lower throughput and websites that require additional periodical computer cycle.3
  4. In the DB Instance setting, You need to provide DB instance Identifier = DinoStoreInstance, Master UserName = admin And Master Password = Walaginsondy1. Note: You can choose your own password. but you need to remember the password because you will require writing this password into your source code. Furthermore, when you will connect your instance from the local machine to cloud RDS though endpoint connectivity. 4.jpg
  5. Click Next Step. In this step, we will do ” Configure Advance Settings“. On the Network & Security option, you have to mention VPC. You can create a new VPC or you can select existing VPC from drop-down options. Note: The purpose of the security group is that it allows your current IP address to connect to your EC2 instance that you will create later.
  6. In the Database options, put the Database name: dinostoredb. Then Launch DB Instance. After a few minutes, Your RDS instance will be created. You can view it in RDS dashboard.
  7. If you want to enable Automatic Backup option, select period of days; otherwise select zero days to be disabled automatic backup (disable = zero days)5.jpg
  8. Now from your RDS instance details copy the end point of DB Instance =dinostoredbinstance when newly created instance will be available.6.jpg7.jpg
  9. Now You need to go the security group of your newly created RDS instance. You have to edit the inbound rule to allow traffic from MySQL/Aurora source from your local machine IP means (MY IP).
  10. 8.jpg9

Note: I’ve allowed My IP because I don’t want anyone from outside of my network will able to access my database instance, mainly for security reasons. MY IP opts only allows the network IP address that is registered under my public IP address.Therefore, My local machine will able to access cloud RDS instance using my public IP address. If you use separate public IP address or move your local machine to the separate network, Your “MY IP” will be changed.

Steps for the task: Connect to the AWS RDS from The local machine MySQL workbench

In this task, I will establish a database connection with my cloud RDS instance from my local machine. Thereafter, I will create two schemas in the cloud DB instance exactly same as my local database schemas such “dinostoredb” and “dinostoremembershipdb”. I will import .csv file of product table data under the dinostoredb schema in the cloud as well.

  1. Go to your MySQL Workbench, click the “Manage connection” to establish a server connection from the local machine to cloud DB instance. Enter the hostname: Endpoint link of your RDS instance dinostoredbinstance.cjoncnib4rsw.us-west-2.rds.amazonaws.com (without port number: 3306). Put the username: cloud DB instance Master username: admin, password. After giving the right credential, you will able to establish. Note: If you aren’t able to establish DB connection after providing the right credential, you need to check your inbound rules from security group and VPC setup11.jpg
  2. Now click “Connect to Database”. Now our new connection has been established with the new cloud database system.12.jpg13.jpg
  3. After connecting with Cloud database, You need to create two database schemas by the following script. “CREATE DATABASE dinostoredb;
    CREATE DATABASE dinostoremembershipdb;
    USE dinostoredb;”
  4. Now you need to create tables under the dinostoredb scheme similar to your local dinostoredb scheme tables.Then add products attributes to the products table by uploading CSV file with the cloud S3 Buckets Image Reference field information. For checking the product table data, execute the following query: “select * from products;”. Note: Sometimes you can lose your cloud DB connection because of your network bandwidth or weak connection. In this case, again establish your connection from your local machine to cloud via MySQL workbench.14

Steps for the task: Create a read-replica of dinostoredb in the Amazon RDS and Test the new databases

The previous task,  I created a production database which is my source database. Now I will create a database a read-replica Amazon RDS for dinostoredb in the cloud platform. This replica database will be worked as failover as if our production database goes down accidently, replica database supports the system as a backup production database. It is a read-only connection and it also helps to increase the performance of the DB instance.

  1. To create the dinostoredb replica database, Selecting the dinostoredb in the RDS dashboard instance page and right click the mouse pointer and select “Create Read Replica”.

    15

  2. Now you have to provide the exact information for the Read-only DB instance that you previously provided your original database system.* DB instance identifier = dinostoreinstancereplica* Keep DB instance class the same as the main DB (=micro)* Pick a different availability zone from the main DB 16.jpg
  3. Click “Create Read Replica“. Your Read-only replica database will be ready in minutes. Now you can see from RDS Dashboard page, your two DB instance is “Available” status.17.jpg
  4. This step I will connect my replica database from the local machine same way I connected previously my production cloud database from my local MySQL workbench.
  5. To establish the connection, You need to open your MySQL Workbench. Go to the “Manage connection” from Database tab. You need to provide the necessary credential to establish the connection such as hostname: Endpoint:
    dinostoreinstancereplica.cjoncnib4rsw.us-west-2.rds.amazonaws.com (without the port number: 3306), username: admin, Password: your DB instance password.18.jpg
  6. Click “Test connection“. The connection will be established successfully. Connect to your replica connection, It will show the exact result that your production database has been giving.
  7. 19.jpg20.jpg

Steps for the task: Changing the code by building the project, and opening it in a browser

In the previous two tasks, we configured our Production database and replica database in the cloud. And We also made the connection from our local machine to cloud database.

In this task, I will edit the source code for change connection link in the Microsoft Visual studio. As you remember, how did I changed the URL link in my module-1, that way, I will change the connection cloud link URL with the local connection link.

  1. You need to go the “Web.config” file from your solution NET702.DinoStore project in the visual studio. Now change the connection strings and use the new cloud RDS strings for “DefaultConnection and StoreSqlb“, and also copied the another line with the name “StoresqlDBReplica” and put the link there to the appropriate link of cloud replica database. Note:  Be sure your code has the correct user ID and password for your cloud RDS instances. Otherwise, you could not able to connect your website from the browser after compile and run through the browser.21.jpg
  2. In the “Default.aspx.cs source code”, you need to change ConfigurationManager that is connectionStrings from ‘StoreSqlDb’ to StoreSqlDbReplica’under the “//connection string for MySQL” comment line. 22

The result of this change help the manage website efficiently and it will increase the performance of the system because the images of the websites will be pulled from replica database instead of the primary database. It will reduce the workload of the primary database system, and l increase the writes capacity cycles.

3. Now you need to save your project file. Then start the debug. After 100% debug, click the “Build Solution” for compiling the code and apply the changes we have done. 23.jpg

4. After the “Build the project“, open the project with google chrome browser. Create the new account with your details.25.jpg

5. If you forward the next without giving all information, It will show the following caution. 26.jpg

6. Provide the username and password, answer the security question (if your account will be locked in the future, security question and answer will help you to unlock the account) and your email address. Click “Create User“. A user will be created.26.jpg

7. After creating the user account, go to the database to check the newly created user whether it is created or not. As you see the following screenshot, after executing the query on users table, I got the details information of users that I recently created.

If you want to check your replica database whether the user has been created there or not. Execute the following query and you will get the same result.

27.jpg28.jpg

Steps for the task: Check out cloud dinostoremembershipdb tables in MySQL workbench

In this task, I will check the cloud dinostorememebership database from MySQL Workbench.

1. Now I will try to login the website to check -can I log in or not with my your ID and password. Yes, I can log in my website with my user id credential. 29.jpg

It will create the cloud membership database table, and create one for the custom table. You should now see ‘Logout’ available as it recognises that you are logged in.30.jpg

2. Now I will check the several tables of my cloud dinostoremembershipdb schema from my MySQL Workbench for checking user activities.

The my_aspner_users table contains the new user of the website: 31.jpg

Steps for the task: Check the replica database working while rebooting the primary database

In this task, I will check my replica database is working or not while I reboot my primary database and at that time, User is connected with dino store website. For testing this connection, I log in my Dinostore website.

  1. For testing your replica database working properly or not, be sure you are working Dinostore website open in your browser.32
  2. Now go to AWS RDS instance dashboard.  Right click your mouse and click “Rebootwith failover.33.jpg34.jpg

3. While the primary database is rebooting, I will check the website by refreshing the pages: Yes, I can still able to access my website while my primary database is rebooting because my replica database is working here backup standby database. My user is retrieving data from the replica database system.35.jpg

4. I will try to login the website with my user login credential to check I can log in on the website when my primary database is rebooting:

Yes, I can login into the website while my primary database is rebooting because my user information is already stored in the replica database, and also Amazon RDS takes a snapshot of the source instance while creating the replica database as a result replica database is the mirror of the primary database.

In my previous tasks,  I already checked my replica database and primary database user tables. They both contains the same information. If my primary server goes down, my replica database will take over the system activity automatically without any interrupt.36.jpg37.jpg

Now I will check my primary database is working properly after reboot the system by checking the status of the instance and accessing the website by apply refresh page:38.jpg39.jpg

The Problems can occur and solutions:

Even though I did not get any error during implementing of this module, but you can come across some errors such asCloud DB instance connection error during

Problem 1: Cloud DB instance connection error during establishing the connection from the local machine.

Solution 1: Check your hostname. Give the endpoint of respective RDS instance without the port number. DB instance name must be similar to the database instance name that provided during configuring Cloud DB instance, and your correct password.

Solution 2: If your solution 1 does not work after providing the right credential.  Go to the Amazon Security Group setting for your DB instance. Check your inbound rules and MyIP. Are you changed your network location? If not, edit the rules again, check your MYIP. If you changed your local network location, edit inbound rule and pick my IP again, and Save it.Make sure you allow the MYSQL/Aurora port number 3306 for inbound traffic.

Solution 3: If your solution 2 does not work after changing the inbound rules, go to the VPC. Make sure your RDS instance is running the same VPC you have configured for that instance. Check the IP address. Another reason, you will not able to get the connection if your instance region and VPC region is different. You have to create RDS instance under the same region of VPC.

Solution 4: If the solution 3 does not work, check all steps of module 2 tasks from the begin to end. Maybe you have made some small mistake during implementing of the RDS instance.

Troubleshooting tip: You will able to solve any problems if you have the proper understanding of your system. You need to develop your technical and analytical skill for the understanding of any systems. Moreover, You can’t predict any errors because systems are unpredictable. But you need to prepare yourself to handle those unpredictable problems by reading and researching on technologies.

The next Module I will discuss “Using DynamoDB as a session State Provider”

Thank you 🙂

Lab7: Introduction to Amazon Relational Database Service (RDS) Linux and Windows

Amazon Relational Database Service (Amazon RDS) is a relational database web service that makes easier to configure, operate, and scale the database system in the cloud. Amazon client is allowed to create and use MYSQL, PostgreSQL, Oracle, or Microsoft SQL server database. The tools, application, code you already use in you local machine with existing databases, now you can be used them with Amazon RDS.

Amazon RDS is cost-efficient and capacity is resizable whilst managing time-consuming database administration tasks, and it also freeing you to focus on your applications and business.

The benefits of Amazon RDS:

  • Easy to Administer
  • Highly Scalable
  • Available and Durable
  • Fast, Secure, and Inexpensive

Amazon RDS Standard Use Cases:

  • The Web and Mobile Applications
  • E-commerce Applications
  • Mobile and Online Games

This lab I will cover the following topics:

  • Creating an Amazon Relational Database Services (RDS) Instance
  • Connecting to the RDS Instance with Client Software

Steps for the task: Creating an Amazon Relational Database Services (RDS) Instance

  1. You need to log in your  AWS management Console. Click  RDS on the services menu. 1.jpg
  2. Now we are going to select database engine. In this lab, we demonstrate on “MYSQL” database system which is most popular open source relational database in the world.
  3. Click the select button across from the MYSQL database engine type on the Select Engine page.
  4. Select MySQL under Dev/Test on the Production. As we are going to do practice lab; therefore we are not going select production database.3.jpg
  5. Cleck “Next Step“. We will get “Specify DB Details” screen. We need to set some information there. In the Instance Specification area type the following data: DB Instance class: db2.t2.micro, Multi-AZ Deployment: No, Storage Type: General Purpose (SSD),  Allocation storage: 5. Note: This is only lab demo setup but in a production environment, you have to setup based on your business and application requirements.4
  6. In the setting area of the same Specify DB Details page, you need to provide your instance identification, master username and master password. Then Click “Next“.6.jpg
  7. Now You can view “Configuration Advanced Setting“. On the screen, You need to assign who will access your database instance, VPC security group and Database name, and also your backup policy.8.jpg
  8. On this Configure Advanced settings page,  you need to setup your Network and security. You can create new VPC here or you can select the previous VPC from the drop-down list.  I selected to create a new  VPC and new security group.

Database options: Give your database name. It is also called schema.

9. On the same page, you also need to configure your Backup system. The number of days for which automated backups are retained. You have put a positive number to enable this option and zero (0) for disable. I put here zero because I don’t need the backup because this configuration I made only lab purpose. 8.jpg

10. Click “Launch DB Instance“.  Your database instance will be successfully created. Then Click on “View Your DB Instance“.9.jpg

Now you can see your newly created RDS instance is ready and status is Available. 10.jpg

11. Now you need to go EC2 dashboard and select Security group. Select the “rds-launch-wizard” security group and Edit Inbound connection Rules. Traffic is allowed for anywhere. Then click  “Save“.11.jpg

Steps for the task: Create an Amazon Linux Instance from An Amazon Machine Image (AMI)

  1. Go to Amazon console and Click “EC2” from Services Menu. Then Click Launch Instance and select “Amazon Linux AMI” instance, Click “Select for the selected AMI“.12.jpg
  2. Select the default instance type t2.micro, general purpose. Click “Next: Configure Instance Details“. This is the area of networking where you would set access, monitoring, network setting, and other options.13
  3. Click Next: Add Storage. Leave this section with the default setup, But You can add more virtual hard drives, and you can also change hard drive size in this screen.14.jpg
  4. Now click Next: Add tags. And give the tag name values such as RDSlab1. Then click Next: Configure Security group. 14.jpg
  5. On the Configure Security Group page, You need to assign security group which you already created or you can assign new security group. This security group will handle your inbound and outbound traffic from your EC2 network. I selected an existing security group which is rds-launch-wizard.15.jpg
  6. Click Review and launch. Then Launch. You will see a Key-pair wizard. This key pair will allow you to access your instance after it is created. Without key pair, you will not able to access your instance. 16.jpg
  7. You can select an existing key pair or create a new key pair. I already have an existing key pair, so I chose that option; However, If you do not have any existing key pair – select create a new key pair.
  8. Click “Launch Instance” and your instance will be ready in minutes. When you will see the status is running and status check =2/2 checks. That means your instance is ready for access.

Steps for the task: Connect to Amazon EC2 instance via SSH from your Local Windows machine

You need to download PuTTY, PuTTYgen, and Pageant. If you don’t have those apps and you don’t know how to work with those application services please visit my lab practices for details (Lab4: https://momatajbegum.wordpress.com/2017/03/20/lab4-introduction-to-amazon-elastic-compute-cloud-ec2-with-linux-server/)

  1. Open your PuTTY and provide Hostname which is your public DNS of your EC2  instance or Public IP Address: 18.jpg19.jpg
  2. Go to the SSH and click Auth: Provide the Private key file authentication that you downloaded during EC2 Instance launch. Remember, You have to convert the file .pem to .ppk because by default, it is .pem extension file.20.jpg
  3. When you get the PuTTY connection via SSH, the following warning message will be displayed. Click “Yes”, You will get log in user option in the PuTTY terminal.
  4. Now log in as an “ec2-user“. 22.jpg

Steps for the task: Connecting to RDS instance

  1. Now again go to the RDS dashboard, select the newly created RDS instance, you need to copy the endpoint name without port number part to make the connection through SSH with RDS connection from your local machine. Endpoint:
    rdslab1.cjoncnib4rsw.us-west-2.rds.amazonaws.com (without port number).

Steps for the task: Connect to RDS from Amazon Window EC2 instance  

  1. Go to your instance and log in your Ec2 instance. If you don’t have SQL workbench, download it and install it on your windows machine.
  2. Open your SQL workbench and click database connection Screenshot (493).png3. Ater connection established from your EC2 instance RDS database, you will get following connection. Screenshot (492).png

Cost Analysis:

Amazon RDS for MySQL Pricing

The paying system for Amazon RDS is what you use. There is no minimum fee.

On-Demand DB Instances

On Demand Instance calculation is depend on for computing capacity by the hours you run your DB instance with no long-term commitment.  It will relieve you from the cost and complexities of the planning, purchasing, and be maintaining hardware and transforms that is actually large fixed costs into much smaller variable costs.

The Deployment price:

Single-AZ Deployment: 

The deployment price is varied in region and availability zone. Each region price depends on Amazon RDS instance size. Price is calculated based on per hour.

As I used in my practical lab, Region is US West (Oregon), I am giving the comparison the price list with my nearest availability zone of Asia Pacific (Sydney): Here I am showing the instance price for that region. But You will monitor and get your price information from the (https://aws.amazon.com/rds/mysql/pricing/).

US West (Oregon):23.jpg

Asia Pacific (Sydney):

26.jpg

Multi-AZ Deployment: 

When you run your DB Instance as a Multi-AZ deployment for enhanced data durability and availability, Amazon RDS provisions and maintains a standby in a different Availability Zone for automatic failover in the event of a scheduled or unplanned outage.

The price list for the US West (Oregon) for Multi-AZ Deployment:

US West (Oregon):24.jpg

 Asia Pacific (Sydney):

25.jpg

For both Single-AZ and Multi-AZ deployments, pricing is per DB Instance-hour consumed, from the time a DB Instance is launched until it is terminated. Each partial DB Instance-hour consumed will be billed as a full hour.

Calculating Budget plan for two regions and comparison:

Suppose you are using db.t2.micro in the US West (Oregan) for single AZ- Deployment, the standard Instance cost is $0.022 per hour. If you use it 1 month, It will be

If you use it 1 month, It will be $0.022*24*30= $158.4, and For 12 months, It will be = $15.84 * 12 =$190.08

On the other hand, If we consider the price in the Asia pacific (Sydney) for single AZ-Deployment. The standard instance cost is $0.026. The cost will be for one month = $0.026*24*30= $18.72, For 12 months, It will be = $18.72*12= $224.64. 

The cost will be for one month = $0.026*24*30= $18.72, For 12 months, It will be = $18.72*12= $224.64. 

The difference between two region cost is = $224.64-$190.08= $34.56. 

So the mathematical calculation of deployment pricing per instance for 12 months shows that region in also effect our AWS budgets plan. Therefore,  You have to analysis on the cost of different region and availability zone before selecting your RDS instance region.

Data Transfer Rate:

Inter-Region Data Transfer Out: 500 GB per month

Data Transfer Out: 100 GB per month

Data Transfer In:  100 GB per month 

Intra-Region Data Transfer: 1000 GB per month 

The Overall estimated bill will be for you per month with that data transfer: $91.90 (http://calculator.s3.amazonaws.com/index.html)

Thank you 🙂

.