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.
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.
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.
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.
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.
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.
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.
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.
In MySQL, in the dinostoremembershipdb tables, there is now a my_asnet_users table that contains basic information about the 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.
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.