Amazon AWS SysOps – Databases for SysOps Part 2

5. RDS Multi AZ vs Read Replicas Hands On

So I am going to create a Postgres database, and we’ll see the difference between multiaz and read replicas in this lecture. So I’ll create Postgres and then I’ll click on Next and I’ll create a dev test type of instance. And we’ll enable multiaz on our own. If you use production, you can see that you can already have multiaz out of the box and provision IOP storage. But for now, we’ll just do dev tests and do things manually. Click on Next and we have Postgres secure license model. This is great. The DB engine version. We can choose whatever you want. I’ll use the latest 10. 4 R one. And the DB instance class is going to be a T two micro just so I try to stay within the free tier.

Now, there’s no guarantee we’ll remain within the free tiers for this lecture, but I’ll try my best to do so. So we’ll use T two micro. Now, for multiaz, I can create a replica in a different zone. And so what it means is that we’ll have another replica, a standby, and it’s only there for doing data redundancy, removing IO freeze, minimizing latency spikes during system backups. It’s just here for disaster recovery. It’s not here to scale the reads. In terms of storage type, I’ll do SSD. 20 gigabytes is fine. This is the minimum we can do. So we’ll just do 20GB. All right, great. Now, the monthly cost is going to be this much. So be sure that if you do this tutorial, you may have to pay a lot of money for this. So you can just watch me do it otherwise.

Now, for the DB Identifier, I’ll just call it my DB. I’ll make things very simple. The username, I’ll call it username admin. And then the password. I’ll say password to make things very simple because it’s not the point here and I’m happy. And passwords don’t match, obviously. Okay. Click on next. Now we configure the advanced settings. So here we do default VPC, default subnet group. And then we want it to be accessible. So I can access this from my own computer. But if you wanted the privates, we could say no here, no preference for the AZ. And then we can either create a new VPC security group or choose an existing one. We’ll create a new VPC security group. For database name. I’ll just specify DB mydb. The port is fine and Postgres is fine. I won’t enable IAM authentication.
As we can see, we can’t have encryption using this instance class, but otherwise we could have encryption. And you can only enable encryption when you start first launch this database. You cannot enable it later. We’ll have seven days of backup, and the backup will happen on the standby replica because it’s multiaz. So the backups won’t impact our performance at all. And for now, I’ll disable enhanced monitoring and I won’t need to export the logs. I will not change any of these settings and I’ll go ahead and just click on Create Database.

So just a quick review and we can’t use Admin as a username. So I’ll just go back very quickly and call it Stefan. This way, for sure, I can use it and create database. All right, here we go. So now the database has been created and it is a Multicar database. As you can see, there was no option to create a read Replica just yet. I’m going to pause just until the database has been created. So my database has been created, but as you can see now, the info still says modifying. And if I go to configuration, I can see that at the very, very bottom here, multiaz says no, so it’s not yet multiaz. What I have to do is actually wait. And this is where it says modifying, because the Replica is going to be credited. The standby Replica is going to be credit right now.

So let me wait just a little bit more. So now my instance is in backup state, so it’s actually backing up. But if you look at the recent events, so under logs and events, recent events, as you can see at 1416, there was a modification done to make it multiaz automatically. And if we go to page two, then what we’ll see is that at 1424. So about ten minutes later, then it became a multi adz instance, and then it started doing a backup. So if we look at our connectivity, our configuration, as we can see now, it says multiaz. Yes. So it’s really, really nice because now I know that my database has been fully multiaz. Now though, if we look at the endpoint and port, as you can see, it’s still only one endpoint that I get. And I can see that my instance is an EU. West one b. So this is where my master instance is. Now, what if I want to show you how it works when we do a failover?

 So what I’m going to do is to reboot my instance right after the backing up process is done. As we can see, my instance is now available. So I’m going to do a reboot and I’ll say, yes, I want to reboot with failover. So this will basically make my instance failover to the standby. So now I’m starting to reboot. And as you can see, it’s EU West one B right now.

But I expect this to change right after the reboot happens. Okay, so it took a really long time, I promise you. But now we can see that the region naz is EU West One A. So just a lot of patience if you’re trying to observe the same behavior. But from looking at the DNS names, you could see that the DNS name resolving behind this was changing every time I was rebooting. So now, all in all, I have the right region and AZ us one A. So it has failed over between multiaz. And this is the behavior we wanted to see next. What we would like to do is to create read Replicas. So for this you have to go to actions create read Replica and basically you can start creating your read Replicas from this instance.

So you can say any region you want. I’ll keep the same region. But this is where you can have cross region read Replicas. As I said. Then you can set a subnet group. I’ll use a default one AZ. You could be have a preference. So maybe because my one A, I have one B, maybe I’ll create one C in there. You can say whether or not this instance should be publicly accessible. I’ll say yes. Here you can enable or disable encryption, I’ll just leave it as disabled. You can choose a DB instance class and you can say whether or not you want to read Replica to be multiaz. I will say no for this time. I’ll use SSD and then you have to specify the source Identifier of the database you want to replicate. I’ll use My DB and this is good. So now you can say a database instance Identifier.

So I’ll call it my DB replica. At least I know it’s the replica. And the database port is going to be the exact same. I can even copy tags to snapshots. But I will disable it. I can enable monitoring, I’ll just keep it disabled and click on Maintenance. As you can see is a very similar creation screen that we had before from RDS. But now we’re creating a read Replica. So I’m going to create that. And now as you can see in the bottom so when you go under connectivity at the very bottom, as you can see now we have Mydb, which is the master. We actually don’t get to see the standby for multiaz, we just don’t get to see it here. But we see a read Replica right here. So mydb Replica is a Replica and if I click on it now, I’m able to go to the console for my read Replica. And you can see the state right now is creating and it will soon give me an endpoint and a port.

You can also see this by going into databases. And as you can see now we have basically two databases appearing right here. One is a Master and one is a Replica. And right now it’s in the creating state. So I’ll just wait a little bit until this is done. So My database and My database Replica are now available and you could always click on this plus minus to basically fall down. But as you can see, this is a Replica of Mydb because it just rolls up on the Mydb. And so what I’m going to do is open a database and we’re just going to test replication. So for this, I’m going to take this endpoint and I’m going to take the Replica endpoint and connect to it using my SQL editor.

Okay, so I’m going to establish connectivity to my first database instance. So for this, I’m going to go and add a new connection. The name is going to be My. DB. The database type is going to be Postgres. The address is what I just copied. The user is going to be Stefan. The password is going to be Password. The initial database is going to be Postgres. And I think I’m good. I’ll click on Test and it has successfully connected. I’ll save it if you don’t get a successful connection. Check your security groups. Okay, so now I can add another database. And this time this will be my Read Replica. So I’ll call it my DB Replica. So I’ll just copy this and I’ll call this My. DB Replica.

And the database type is Postgres. And I’ll use the same username, so Stefan. And the password is going to be Password, and the initial database is going to be Postgres. Click on Test successful as well, so I can connect to my Replica. So what I’m going to do is just connect to my first this one, my first database. I’ll say Create Database and I’ll just call it Mydb or something like this. And then I will execute my statements. So now I’ve created a new database. And in there I can go ahead and I will create basically some tables. Then I’ll just go to my file, create table SQL. So I’ll create a table named Films into Mydb. So just make sure you have the Mydb open, click on Execute, and now it’s successful. So I have public films. That’s a table that’s available for me.

And now we’ll just go ahead and insert one film into it. So I’ll just copy the statement and insert some data. So I’ve affected one row. So now if I go to this and click select, basically it’s just going to show me that indeed I have my data into this table. So Public Films, there has been some data. So now let’s see if I get the exact same thing in the other database.

So for this I’m going to go to mydb Replica Connect, and as you can see, we already have a Mydb database that has been created. And within mydb there is a public films and if I select from Public Films, I find the exact same data. So my Read Replica is working, and as you can see, it has replicated the data right away. So it’s pretty awesome. And that just demonstrates how we Replica works. So I hope you enjoyed it and I will see you in the next lecture.

6. RDS Parameter Groups

Just a very short lecture on parameter groups. So I talked about them, but basically you can configure your entire DB engine as we’ll see using parameter groups and if it’s a dynamic parameter, it can be applied immediately. But if it’s a static parameter, then it will only be applied after you reboot your instance, so that when the instance reboots, basically it just picks up these parameters. We can also modify the parameter group associated with the DB so we can replace the default one by our custom one. But for this we also must reboot our database.

And to know all the parameters available for a specific DB technology, you can look at the documentation or as well use the AWS console as we’ll see in a second. Now there is one must know parameter you need to know for the exam and that’s the one called for Postgres and SQL Server RDS force underscore SSL equals one and that’s the way to enforce SSL connection to a postgres or SQL Server database. You must know this parameter going into the exam, but as a reminder, it doesn’t work for MySQL, for MySQL or Mario dB, you must run a SQL statement called Grant select on database star two and then you just at the end say require SSL. So it’s pretty funky that there’s two different ways of doing it, but that’s the way it is. So for postgres you can use a parameter group, but for MySQL it’s a sequel statement. Remember this going into the exam. Now let’s just have a quick look at parameter groups in the console.

So parameter groups are accessible from the left hand side panel and as you can see, when we create a database, it creates a default parameter groups for us and we could click on it and see anything, but we won’t be able to change this group. In this parameters filter you can just type any parameters and see how it is we’ll create our own parameter group. So I’ll call it a parameter group and I need to select my database instance. So for me it’s going to be postgres ten because we use ten four. The group name is I’ll call it Group Demo postgres and I’ll just call it Demo group. For postgres you have to put a description in click on Create and here I’m basically able in this group to change any of my parameters to whatever I want. So you can see we can change the

authentication timeout to whatever value we wanted. If we click on it and click on Edit parameters, then you’re literally able to change the value for you want for anything. The one parameter that I want you to look at is going to be called Force underscore SSL and as you can see, RDS that force SSL is here. It’s a dynamic type of parameters so we won’t need to reboot our instance and it’s a boolean. And this is to force SSL connections so I can just click on it, edit the parameters and say one. And this will force my database to have SSL connections only.

So this is our force encryption for postgres. Okay, I saved it. So now my parameter group has been saved and let’s see if that works. So I’ll just refresh my page and then after refreshing my page, I’m going to tap force on the score SSL again. Okay, now the value is one, so it means it’s enabled. So now what I can do is assign this parameter group to my postgres database. So what I want to do is take this parameter group, which by the way, I can edit, copy, compare, reset or delete. I want to assign it to my database. So I’ll go to my database and I’ll click on it and I’ll modify it, and within it I can now assign my parameter group. So let’s scroll down, and in there I will have here the DB parameter group and I will say it’s group demo postgres.

Because I changed the parameter group, I will have to reboot my instance. So I’m fine. Everything else I’ll leave the same, I’ll click on continue and then modify DB instance, but I will apply immediately instead of applying during the next schedule maintenance window. So I’ll do this right now, modify DB instance. And now my instance is going to be restarting and rebooting using this new parameter group. But this time it will basically have forced SSL connections, which is what I wanted. So after a few reboots, it turns out that if you scroll down, you see that the parameter group is group demo postgres and now it’s in sync and that’s using all the parameters that have specified. So that’s it for this lecture. I hope you enjoyed it and I will see you in the next lecture.

7. RDS Backup vs Snapshots

All right, let’s talk about the difference between an RDS backup and an RDS snapshots because the names are actually quite similar. So backups are continuous and they happen all the time. And what they allow you to do is do a point in time recovery. So we can say, hey, let’s, let me reset my database how it was yesterday at 04:00 p. m. . That’s what a backup would be helpful for. Backups will happen during maintenance windows, and when you delete a DB instance, you can retain the automated backups. But the thing is, the backup have a retention period and you can set it between zero and 35 days. And after you’ve deleted your database, even though you have automated backups that are still there, they will be deleted eventually.

So backups are something that are going to be temporary. They’re great for operations on your database, but they’re temporary snapshots. On the other hand, they were going to be more long term. So when you do snapshots, it will do a lot of I O, and it can stop the database from within seconds to minutes, so it can take a long time. And if you do a snapshot on the multiazdb, though, as we said, it doesn’t impact the master, it will just impact the standby. And so that means that your master database will not have any performance impacts. Now, the snapshots, once you do a full one, the rest is going to be incremental. So like an EBS volume, really. And this is pretty cool because that means that the next snapshots are going to be way quicker to do. And just like CBS snapshots, we can copy and share DB snapshots. And if you do a manual snapshot, it will never expire.

So it’s a great way of making sure you have a database and that you won’t lose its state even if you delete the database itself. What you can do is that when you delete a database, you can do what’s called a final snapshot, such as you can always recover your database in the future if you wanted to. So the really interesting things to see here is that backups are here temporarily and will eventually get deleted, whereas snapshots are never deleted, but you have to make them manual and snapshots. You can share them with people. So it’s a great way of duplicating a database, maybe across accounts or across regions. All right, so that’s it. You need to remember these things and I will see you in the next lecture. So regarding backups and snapshots, there is a whole tab called Maintenance and Backups. So in there we can see that the backups are automated and they’re enabled and they will be retained for seven days.

Then we can also see that I have snapshots and snapshots. These ones are automated. This is basically when I did some kind of maintenance on my database, they have been automated. But I could go and basically create a snapshot and it will be a manual one. And I’ll call this one manual snapshots. And this will be a full snapshot that will stay there until the end of time. And the end of time could be very long. But the idea is that now under the snapshots, we can see that we have a different snapshot right here called Manual snapshots. And this one will be created and the type of it is manual. So what we need to remember here is that this snapshot is created manually so it will survive my database being deleted or anything like this.

And I can always restore to this snapshot anything I want for the automated backups. As you can see, the backups are going to be right here and then tell me what’s going on with my backups and the one they’re active. And then using these backups, I could basically roll back my database in time if I wanted to. So if I go to my database and I could click on Action, I could say restore two points in time. And this is how I would basically say, okay, using the backups I have, I want to restore to yesterday 04:00 P. m. Something you can do with snapshots. As you can see, I’ll just wait, but it will be to copy them. So let’s wait for the Actions menu to be available.

And actually, I could just use one of these two snapshots above to do what I want to do. So I’ll just click on this one, click on Action. And here, as you can see, I can restore a database from a snapshot, but I can also copy a snapshot. And by copying a snapshot, I can copy within the same region or to another region, create a new snapshot Identifier, enable encryption on the fly. So this is how I would enable encryption from my database. Pretty cool. And so this is a good way of dealing with databases and creating copies, et cetera, et cetera.

You could also do an action and migrate the snapshots. And this is to basically migrate to a new database base engine, which I think is kind of cool how it works. And this is just another option. You have to migrate, for example, from postgres to Aurora postgres. And I’ll tell you in the second next lectures how Aurora works. But yeah, that’s it for snapshots and backups. I hope you understand the difference. Play a little bit with it. But now hopefully it’s clear and I will see you in the next lecture.

8. RDS Security

Okay, let’s talk about security for a moment. For Syrups, so it’s things you already know, but I have to repeat them, and you need to make sure you know them, obviously, because for the exam, they will ask you some questions. So, encryption at rest can only be enabled when you first create your database instance. If you remember, I did not enable encryption at Rest, and so I won’t be able to enable it in the future. You can’t just modify your instance and enable encryption at rest if you wanted to. You go from an unencrypted database to an encrypted database.

You would do the same thing as you would do with EBS volumes. That means you take a snapshot, then the snapshot will also be unencrypted. Then you copy the snapshot and you encrypt it in the process. And now you have an encrypted snapshot, and then you create a database from that encrypted snapshot, and your database will be encrypted.

So it’s the same as EBS. So in your mind, how to encrypt an RDS database should be the exact same thought process. How do I encrypt EBS? Now, what is your responsibility for RDS? Well, it’s to check the ports, the IP, the security groups, inbound rules for your DB security group, so you have to check those, and if it doesn’t work, then it’s up to you. So make sure that’s actually an exam question. So you need to know the ports and the IP for the security groups are correct. Whatever happens in the database is for you. So if you create users or permissions, it’s up to you. If you create a database with or without public access, that’s also your call.

So either you allow access from outside your VPC, or you create access without public access. So it’s up to you then all the parameter groups, as we’ve seen, is what you should be configuring yourself. So if you only want to allow SSL connection, then it’s up to you to configure that on your database. And what is AWS responsibility? Well, they won’t give you any SSH access, so you cannot SSH into your RDS database. You will never be able to do a manual database.

Patching it’s up to Amazon to upgrade your database. You will never actually have to audit the system. The OS, it’s Amazon’s responsibility to patch it as well. And you can never audit the RDS database, so you cannot run any audit tool or AWS services which allows you to run security analysis against your RDS database. And the exam will trick you into thinking that, but you cannot. RDS is a managed service, and so you really need to understand what is your responsibility, and it’s outlined here versus what Amazon does and never gives you access to. So that’s it. It’s just a theory lecture, but you need to make sure you know these responsibilities by heart. And I will see you in the next lecture.