Amazon AWS Certified SysOps Administrator Associate – Databases for SysOps Part 2

3. [SAA/DVA] RDS Hands On

Play with RDS. So I’m going to go to the RDS service and we are going to create our first database in RDS. So we’ll go to the Databases tab and we’ll click on Create Database. Now we are getting into this new interface and we need to create a database. Now we have the standard Create or the easy Create. Obviously we want to learn how RDS works. So we’ll not go into the easy Create because this will set up everything for us. We want to know all the options and understand how we can tune them one by one.

So we’ll go into the standard Create. Now as we scroll down, we can see we have six different engine types. At the moment we have Amazon Aurora, MySQL, MariaDB, PostgreSQL, Oracle and Microsoft SQL Server. So if you want to use the free tier with RDS, aurora does not work. Aurora is the new kind of offering by AWS for databases and it is not compatible with the free tier.

So we’ll not use Aurora in this lecture. We’ll be using MySQL because it is free tier compatible. You could also use PostgreSQL if you wanted to, but for this hands on it will be MySQL. So we’ll use MySQL community and we’ll use a recent version, for example 5722. I’ll scroll down and then we can have templates. So do we want it to be ready for production, devstest or free tier? So obviously we want to remain within the free tier. So I’ll click on free tier and this will come with a few values already set up for us. But we’ll still go ahead and scroll down and see what we can do with it. So first we need to name our database, which is a database instance Identifier, and this needs to be unique across your region.

So I’ll just call it my first MySQL Then we need to specify credentials for the master username. So in this case I’ll choose Stefan and then for the master password I’ll choose something very simple, for example password and I’ll enter their password again. So this is for the master logging in your database and they will have admin power in it. Now we need to scroll down and look at the DB instance size. So because we are in the free tier, we want to only have the option to do a TB t two micro.

So all the other options are grayed out for the very reason that we have specified that we wanted free tier. So it’s a safety, but if you clicked on Def test or Production then you would have access to all the different kind of instances. In here we can choose standard class or memory optimized and more. So in this case though we’ll continue with DBT two micro which has one vCPU and 1GB of Ram. Then we need to learn about how much storage we want for our database. In our case we want SSD storage and 20GB.

This is something that is available only for free tiers, so it can be up to 20 and sometimes you get this really weird UI quirks. So if you get this, just increase to 21 and back to 20 and it will be fixed. Okay, now for the storage auto scaling, we can enable storage auto scaling. That means that if we keep on writing to our database and we reach this 20GB of storage, it will automatically increase it for us. So we don’t have to do it for now, but I will disable it and we’ll go right back to 20. Okay, now for the availability and durability, what do we do? Do we want to have multiaz?

So multiaz as we saw, is a way to have a standby database instance ready for use in case an availability zone goes down. And so this is something we definitely want in production, but this is not something that’s available for us when we do the free tier. Therefore this option is disabled. But if we wanted to have production and wanted to make sure that one AZ could go down and we could still use our database, definitely using multi AZ deployment would be the way to go.

Now, for connectivity, you launch your database in a VPC or virtual private cloud. And so as such, we’ll use the default VC that has been created for us. When we created our account to launch our database, we can specify additional connectivity configuration. And one that’s very important is whether or not we want this database to be publicly accessible. So in most cases you will not want your database to be accessible publicly because you just want it to be available within your virtual private cloud or VPC.

But because we want to connect in this hands on to our database just to show that it works, we’ll use yes and will allow connections to our database directly from our machine. Now, for security group, we can choose an existing security group or create a new one that will be directly created for us for this database. So I’ll just enter my first RDS as security group SG and then where do we want this database to be created? No preference, but we could choose it in U s, one, three A, three B or three C.

Okay, finally, the data of this port is the standard port for MySQL, which is 3306. We’ll scroll down and we can look at additional configuration. So the initial database name will have my DB. This way will be automatically created by RDS, the parameter group we don’t touch.

Right now we could have IAM DB authentication, so that means that we could connect to our database directly using Im users and roles, but we won’t enable it right now. We just need to remember that it is an option. For now, we’ll just connect to our database using the standard username and password that we have created from before, but it’s good to know that it’s an option.

And then do we want to have backups? Yes, we want to have automatic backups. And how much do we want to retain these backups? For seven days, all the way to 35 days. So I’ll just keep it as seven days. The backup window. So do we want to specify a specific window of time when we want RDS to perform our backups or do we have no preference? I’ll just keep no preference then. Finally, do we want enhanced monitoring to get more metrics such as number of threads and so on? Log exports. If you wanted to export the logs into a service called Amazon Cloud Watch Logs and the Im role to do all this stuff, we won’t need to specify it, we only need to do it. If we had ticked one of these boxes, we’ll scroll down. Do we want to enable auto minor version upgrade? Does you mean because RDS is a managed service, we definitely want it to automatically upgrade our database for minor versions. Automatically. And they will happen during the maintenance windows of the database. And for the maintenance window, what preference do we have? Do we want to have a specific day, start time and duration, or do we have no preference?

Finally. Last one, Henry. Go through. Go. We’ll have deletion protection, and if we click on this, that means that we won’t be able to delete our database without removing first the deletion protection. So I’ll leave this unticked for now. So here we go. We see that this RDS is within the free tier. We get T two micro instance. So this is good. It’s what we have single as, it’s not multi AZ, 20GB of SSD and 20GB of automatic backup storage and so on. So click on Create Database.

And here we go. The database should be created and this may take a few minutes to launch. So I’ll be right back by clicking on the database right here. I’ll be right back when this is done. So while my instance is still being created, I want to show you a little SQL program I like, which is called SQL Electron. And SQL Electron is basically a DB client for your database. And so it works for Linux, mac and Windows. That’s why I like it. And it gives you a Gui. So a graphical user interface to connect to your database.

So I invite you to download the GUI and then install it on your computer. Basically, you go onto this GitHub page and here if you’re on Mac, you use the DMG. If you’re on Windows, you use the Wind package, and if you’re on Linux, you can use the Sh package or the DBN and Rpm. So once you download it and install it after this, my SQL Electron is installed and I click it add in here I can add a connection so I can connect to my RDS database. And just a name for it. And I created a MySQL database now to put the server address, the port and so on. So for this we can go back to our page and now we can see that the instance is backing up. So it’s been created and if we scroll down all the way down, we can see a connect block with the endpoint.

And the endpoint is basically the URL I need to use to connect to my database. And the port is 3306 and it’s publicly accessible, so I should be able to access it from within my computer. So let’s hope for this, I copy this and put the server address right here and then the username is going to be Stefan and the password is going to be Password. So it looks good. Now we’d be able to enable SSL if we wanted to be having a secure connection, but for now we’re good. So we’ll just click on Test and the connection test is successfully connected. Happy days. I save it and I connect to My database. And here I am, I am in My database and we can see that there is my DB on the left hand side.

This is the database that was created first for us. You don’t need to be a SQL expert to use MySQL or database, but this is not a course about SQL anyway. So as soon as we connected, we know everything worked and that’s about it for the hands on. Okay, what you should know about is that all the connections and options you had when creating this RDS database, the fact that there is a security group that was created for your instance, and you can click on it to go and see the details of your security group. So we can see that it authorizes My IP on the port 3306, which is good, and you can see all the network and security configuration, if you’re a backup, if you’re multiaz, et cetera, et cetera. So this is quite nice. The last thing you can do is do instance actions and as you can see on the right hand side, we could stop it, reboot it and create a read replica.

So if we wanted to create a read replica, we could just click here and have a second database that we can connect to just for reads. We can also take a snapshot to basically take a backup on ourself and then restore to point in time using the backups and migrate a snapshot if you wanted to move the snapshot away into another region. So I hope that was helpful. That was a quick overview of RDS, but we’re able to create a database MySQL connect to it using SQL Electron, and basically this is a database that’s available for our application to be used if they need to have one. Okay, so that was it, hope you enjoyed it and I will see you in the next lecture.

4. RDS Multi AZ – Failover Conditions

Okay, so just a very, very short theory lecture, but what are the failure conditions for multiaz? You need to remember them. So the failover will happen from the primary DB to the secondary DB, if there is a failure of the primary DB, or if the operating system of your primary DB is undergoing some software patching, or if your primary DB becomes unreachable due to loss of network of connectivity. T or if you modify your primary DB instance, for example, you’re changing the instance type.

Or if it’s busy and unresponsive, if there’s an underlying storage failure. And you need to remember those, okay, because they can be tested on the exam. So just need to make sure that you are on with it. But they’re quite natural. Also, if you have an AZ outage and obviously it’s going to failover because it’s multi AZ, if you initiate a manual failover of the DB instance using the option named Reboot with failover. So that’s it, that’s all you know, I will see you in the next episode.

• Category: Uncategorized