Amazon AWS Certified Database Specialty – Amazon RDS and Aurora

43. Data API for Aurora Serverless

Aura serverless also provides what is called as Data API. So when you talk to Aura Serverless, you can run queries over APIs. Instead of using a DB connection and firing your SQL queries using a SQL client, you simply run your queries over the API. And you can also run queries using the query editor that’s available within the RDS console. It looks something like this, and you can simply type in your queries and you will get the response right within your RDS console. You can also run queries using Command line, or you can use AWS SDK from your application. There is no need for connection management. The DB credentials for connection are stored in the AWS secrets manager. So whenever you make an API request, the credentials will be picked up from the Secrets Manager and used to

perform the query operation as per your API request. And this type of Credential usage is perfect when you’re using lambda functions. For example, so Lambda is used for serverless applications. And when you use Data API, you can simply use the AWS SDK within your lambda functions to make calls to your Aura Serverless cluster. And you don’t have to store any connection information. It will be picked up from the Secrets Manager. And typically when you talk to Aura Cluster, the provisioned Aura cluster using Lambda, you have to configure your lambda functions for VPC access. But with Aura Serverless, you don’t have to do that because you’re used using a Data API. So you don’t really need to configure your lambda function for VBC access. All right.

44. Aurora multi-master

Now let’s look at aura multimaster. So until now, we looked at aura clusters with a single writer. With aura multimaster, we can have multiple writer nodes. So we don’t have reader replicas here, we have multiple writers. So this is how it looks. We have a shared storage volume and we have writer node. So every node on your aura multimaster cluster master can read as well as write. So this is definitely better than promoting a read replica to a new master. Since every node can write, there is no need for failure. Another writer can take over in case the primary goes down.

So we call this as continuous availability as against high availability. So this typically results in absolutely zero downtime. And in a multimaster cluster, as of now, you can have up to two database instances. Okay, you can’t have more than two for now. And this is something you need to remember when you perform DDL operations on the aura multimaster cluster. DDL stands for Data Definition Language. You use this to define your database schema. For example, the Create Drop Alter Operation operations. Now, when you carry out DDL operations on a table, they typically prevent concurrent rights to that table. So you should really avoid issuing large number of short DDL statements in your application. So that’s something you should remember.

45. Aurora cross region replicas and Aurora Global database

Now let’s look at global version of Aurora. So if you want to use Aurora in multiple regions, then there are two options that you can use. The first one is cross region read Replicas. So these are useful for disaster recovery and they are very easy to put in place. So what you do is you simply go to the Aurora cluster and choose the option to create a cross region read Replica and you’re done. So that’s very straightforward and intuitive. Replica promotion can take a few minutes, depending on the workload. Okay, the cross region Replicas can also be promoted to act as the primary node. And this takes some time, of course, because it’s a cross region Russis, but it’s still good for disaster recovery. And we have another global Aura option which is even better than using cross region read Replicas.

And we call it as Aura global databases. In Aura global database, we have one primary region that can read as well as write. And then we can have up to five other secondary regions which act as read only regions. And the replication lag to the secondary regions is less than 1 second. So you can read locally with under 1 second latency. And this also means that your RPO is less than 1 second.

And again, RPO is recovery point objective. It indicates how much data loss you will have in case of an outage. So with Aura Global Databases, you have RPO as low as 1 second, and you can have up to 16 read Replicas per secondary region. This definitely helps for decreasing the local read latency. And promotion to another region, typically for Dr purposes, has an RPO of 1 second. And RTO is about a minute. So RTO as your downtime after a failover or downtime after an outage. So the recovery time objective or the time to recover from a failure is under 1 minute when you use global databases. All right, let’s continue.

46. Reliability features in Aurora

Now, let’s talk about some of the reliability features in Aurora. So Aura is designed to be reliable, durable, and fault tolerant. So there are a couple of features that make Aura so reliable, durable and fault tolerant. So the first one is storage auto repair, and we have discussed this briefly before as well, that Aura can detect and repair the failed disk volumes and quorum model ensures that there is no data loss due to disk failures. So what this means is you have about six copies of your data across different disk volumes or different storage nodes. So even if one of the disk volume or storage node goes down, aura can repair or replace that node and then use peer to peer application to restore data from the other five nodes that still have the data.

All right, and then we have survivable cache warming. So Aura Page cache is managed in a separate process from the database. So Page cache is something that stores your commonly used queries. Okay? So every time Aura starts or restarts, it preloads the buffer pool cache using this Page cache. Okay? So what this does is you don’t have to warm up your buffer cache whenever there is a failure or a restore. So whenever there is a failover or restore, the newly created instance already has this cache that contains the commonly used queries. So this is going to eliminate any of the performance lag on the new instance. Then we have crash recovery.

So Aurora is designed to recover from crash almost instantaneously. And it does this because it does not need to replay the redo log from the database checkpoint like other databases do. And Aura also doesn’t need the bin log for replication. So bin log is typically used for replication, and it’s also used for Pitr or point in time restore. So if your application does not need any external replication, you can definitely turn off the bin logs. And this is going to improve your recovery time after a crash. So if you have the bin logs or binary logging enabled on your Aura cluster, it’s going to affect your recovery time. Post a crash, higher the bin log data size, longer it’s going to take for your cluster to recover from a crash.

So ideally, it’s a good idea to disable binary logging and it’s going to improve your crash recovery time. So you simply set the bin log format to off and that will disable your binary logging. And remember, that bin log is needed only in case you have extra external replication. By external replication, I mean if you’re replicating your data to another external database, then and only then, you would need binary logging. Otherwise, you can simply disable binary logging and it’s going to improve your recovery time from a crash. All right, let’s continue to the next lecture.

47. Aurora pricing model

Now let’s talk about Aura pricing. So, as per AWS, Aura costs about one 10th of the cost of competing commercial grade relational database solutions. And it typically costs about 20% more than RDS, but it’s way more efficient than RDS. Of course, the pricing model is similar to RDS. So if we use a Pay as you go model and when you create an Aura database, you choose the Instance type, engine type, the Database Instance class and whether you want to create a regional or a global database cluster.

So the instance types you can use on demand instances, you can use reserved instances, that is on a one to three year term. So you get steep discounts if you buy the cluster for an extended period like one year or three year. And another option is to use Serverless cluster. And with Serverless you pay as much you use and you can choose from Postgres and MySQL. We already know that. Then for instance classes, you can definitely use Memory Optimized instances for production and for dev test, you can consider using the Burstable Performance. So, Burstable Performance is typically used for everyday purpose, whereas the Memory Optimized is something you can consider using in production.

And of course you can choose whether you want to create a regional or a global database. And cross region replication latency is typically under 1 second. Then you also pay for storage, you pay for backups backtrack as well as snapshot export to S three. You also pay for IO, so you pay per million requests. And just like any other AWS service, you do pay for data transfer as well then Aura Serverless Pricing so this is pay per second pricing model.

There is no charge when your database is not running. And with Serverless or with Aura Serverless, you pay for database capacity, database storage and IO. So the database capacity is measured using what is called as ACU or Aurora Capacity unit. So, one ACU is about two gigs of memory with corresponding CPU and networking power. And when you choose your ACUs, you provide a min to max range of ACUs for auto scaling. And Aura will automatically adjust your ACUs depending on the workload. So you pay for for the actual ACUs that your applications consume on a per second basis. All right, so that’s about the overall pricing. Let’s continue to the next lecture.

48. Aurora security – Network, IAM and encryption

All right, in this lecture let’s talk about Aurora security. So, security in Aura, be it network security I am security or the encryption, it’s same as the RDS security options. Aura uses the same native RDS infrastructure or the provisions for network I am as well. As for encryption, we can use the Amazon, VPC, IAM and Kms in the same way as we use it for RDS. There is slight difference when you use SSL for Aura Serverless. So let’s look at that. The SSL for Aura Serverless.

So for connecting to Aura Serverless or SSL, the procedure is same as you use with RDS or with the regular provision or cluster. But with Aura Serverless you can also use the ACM certificates. ACM is AWS certificate Manager and you can use it to generate a certificate for your Aura Serverless cluster. There is no need to download the RDS SSL certificates. You can simply use the ACM certificates. And to enforce SSL again, the process is similar, but there is a slight difference.

So to enforce SSL, if you’re using the postgres SQL version, then you set the RDS force SSL parameter to one in the DB cluster parameter group. And remember, unlike the postgres SQL on RDS with Aura Serverless, this RDS force SSL parameter is a dynamic parameter. So in postgres for RDS you would have to manually reboot your cluster. But with Aura Serverless you don’t need a manual reboot because this is a dynamic parameter. And even with the provisioned flavor of Aura postgres SQL, the force SSL parameter is a dynamic one. So you don’t need to reboot your cluster when you want to enforce SSL on Aurora cluster, all right? And with MySQL is the same, you simply use the Alter user statement with required SSL. And here are the connection examples with postgres SQL and with MySQL. So these are the same as you would use with RDS or with the provision Aura cluster. All right, let’s continue.

49. Parameter groups in Aurora and Aurora Serverless

Parameter groups in Aura. So Parameter Groups again work in the same manner as they do with other RDS engines. And in addition to the Parameter Groups or database instance Parameter Groups, aurora also has something called as a Cluster Parameter Group. So the Database Parameter Group is used to provide engine configuration for any given DB instance, while the Cluster Parameter Group can be used to set up the engine configuration for all the DB instances within a particular Aurora database cluster. All right? So cluster Level Parameter Groups apply to all the DB instances, whereas the DB Parameter Group applies only to the particular DB instance.

Now let’s look at changing the Parameter Groups. So when you change the Parameter Group associated with a DB instance, this requires a manual reboot. So for example, you have a Parameter Group PG One attached to your DB instance, and you create a new Parameter Group, PG Two and attach that to your DB instance. Then, for that change to take effect, you have to manually reboot the DB instance. And in case of the Cluster Parameter Groups, for example, if you want to change the Parameter Group associated with Aurora DB cluster, then this does not require a reboot. So let’s say you have Cluster Parameter Group PG One attached to a database cluster, and you change it to another cluster Parameter Group, PG Two.

Then for these changes to take effect, this is not going to require any reboot. These changes will take effect immediately. And then, since we have two types of Parameter Groups with Aurora DB instance Level Parameter Groups and the cluster Level Parameter Group, we have to talk about the precedence of the Parameters Parameter Group precedence. So by default, the cluster Level Parameter Group values take precedence over the DB instance Level Parameter Group. And any database Parameter settings that you modify will take precedence over the Cluster Parameter Group values. And this is true even if you change those values to their default values. All right? So if you want the DB Cluster Parameter Group values to take the precedence again, then you must reset the instance Level Parameter Group and you can do that using the Reset DB Parameter Group command.

Or you can also use API like the Reset DB Parameter Group API to do this. And if you want to find out which are the parameters that have been overridden by the database instance Parameter Groups, then you can use the described DB Parameter command or the described DB parameters API operation. Now parameter groups in Aura serverless. So with Aura serverless, there are only cluster level parameter groups and there are no DB level parameter groups. And this is kind of obvious, because the Aura serverless clusters use a warm pool of database resources.

So there are no permanent DB instances, right? So the underlying DB instances keep changing as Aura scales up or scales down the instances. And we really don’t have any control over which database instances are serving our workloads right? So with Aura serverless you only have cluster level parameter groups and Aurora manages the capacity configuration options. So you can only define other options. With the DB cluster parameter group, capacity configuration is totally taken care of by Aurora. And with Aura serverless, all parameter changes are applied immediately irrelevant irrespective of the apply immediately setting that you might have on your database configuration. All right, that’s about it. Let’s continue to the next lecture.

50. Creating an Aurora database – Hands on

In this lecture. Let’s go into a quick demo and we’ll be creating our first Aurora database. So I’m here in the RDS console, simply hit the Create Database button to create your database. And we’re going to stick to the standard Create option. The engine type we need is Amazon Aurora and let’s create Aura with MySQL compatibility. So here you can choose the version I’m going to stick to the version that’s selected by default, database location can be regional or global. So if you want to create a global database, then you choose Global. Let’s stick to Regional for now. Then under Database Features, you can choose whether you want one writer with multiple readers. So this is the default option. Or you can have one writer with multiple readers with a parallel query enabled. And we have seen that you use parallel query for analytical workloads.

And if you want to create a multimaster database, then you would choose multiple writers. And for Serverless, you would choose the Serverless option. So let’s go with one writer and multiple readers. So you have one Writer instance and multiple read Replicas. Then under Templates, you can either choose the production template or dev test template. Here we are just testing. So we’ll go with the dev test template. Then further in the settings, you give your cluster an Identifier. So I’m going to leave it at default. Then provide a master username and a password. Provide a password here. Then under DB instance size, you can either use Memory Optimized or Burstable classes. I’m going to go with Burstable classes. We can choose the smallest available instance here. Okay. Then in a mulitas environment, if you want a multi AZ, then you can choose this option.

So let’s go ahead and create an Aura Replica along with Writer. When you choose the option to create an Aura Replica, what it’s going to do is it’s going to create one writer and one reader, and you can always add additional readers later on by adding more Replicas to your cluster. Then under Connectivity, you choose the VPC that you want to create this database in. So you can create a new VPC. I’m going to stick to the default one here. All right. And for additional connectivity information here you can provide the subnet group. I’m going to stick with the default. If you want to make your database publicly accessible, then you set it to yes.

Generally you would set this option to no. Then under VPC Security group, you can either choose existing one or you can create a new security group. I’m going to stick to the default one. The database port is 3306. Since we have chosen MySQL version of Aura, then under additional configuration, you have additional options. You can provide an initial database name, let’s say my Aurora DB. Then we have cluster parameter group, the database parameter group, and an option group. And here we provide a failure priority. So you provide a failure priority to each instance.

So each instance can have failure priority from Tier zero to Tier 15. And whenever there is a failover, failure will always occur to the replica with the highest priority. Tier Zero is highest priority and tier 15 is the lowest priority. And you can choose this as per your requirement. And then we have backup options. Backup retention period minimum is one day, and maximum backup retention that’s possible is about 35 days. All right, then you can enable encryption here. You can use the default Kms key or you can provide a new key here.

Then we have backtrack. So this is a point in time restore feature that allows you to restore your database to a specific point in time. And this is different from the regular pitr that we have in RDS because this is in place restore.

So you don’t really restore to a new database instance, but you restore your data with the same database instance. You can enable backtrack. We’ll see Backtrack in action a little later in this course. And here you can set the backtrack windows. You can set it up to 72 hours. Let’s say I’m going to set it to about 8 hours, okay? And then this is going to show you how much this is going to cost you. So for example, if you set it to 1 hour, then the value is going to be low. If you set it to 72, the value is going to increase.

So I’m going to keep it at about 8 hours and so it’s going to cost me about half a dollar a month. Okay, then we have monitoring. You can enable enhanced monitoring and you can set the granularity of your enhanced monitoring from about 1 second to up to 60 seconds. So if you want higher granularity like 1 second, you can set it here. Then here you choose the Im role. You can choose a default role or create one. Then here are the logs available with MySQL.

So if you select these options, then these logs will be exported to Cloud Watch logs. Then here is the role for Cloud Watch logs. So this one is the monitoring role, while this one is the role that is used to publish logs to Cloud Watch logs. Then you can enable auto minor version upgrades. You can select a maintenance window if you like. You can set deletion protection if you like. I’m going to leave it at default and simply create the database. So now the database is creating and this is going to take a while, so I’m going to pause the video here and come back when the database instance is available.

All right? All right, now you can see that the cluster as well as the database instances are available. We can see that we have one writer and one reader in two availability zones. So if you look at the if you click on the cluster name here under Connectivity and Security tab, you can see the database endpoints. So we have a Writer endpoint and a Reader endpoint.

And you can use these endpoints to connect to this database using any SQL client, like the SQL electron client that we saw in the RDS demo. And I’m not going to do that here because the process is exactly the same. And apart from the Writer and Reader endpoints, you can also create custom endpoints here. And we have already talked about this. This is the place from where you can create the custom endpoints. Okay? And under the Configuration tab, you can see that we have a cluster parameter group here.

And if you click on any of the instances, you can see that it has the parameter group or the database level parameter groups. Even the read Replica is going to have its own parameter group. Okay, so let’s go back to the databases. And if you select the database cluster and then go to the Actions menu, you can see various options here. So here you can add a read Replica within the region. Here you can add a cross region read Replica. We can create a clone here. Here you can restore to a point in time. Here you can use the backtrack option. Here you can set up your Replica auto scaling policies and so on. And we are going going to talk about all this in a while in this section. Okay, so that’s about this demo. Let’s continue to the next lecture.

51. Creating an Aurora Serverless database – Hands on

All right, in this lecture we are going to create a serverless Aura cluster. Let’s do that. So click on the Create database option. Engine type is going to be Amazon Aura. We’re going to stick to the MySQL flavor. You can choose any of the version here. And then for database location we have to choose regional. Because if you choose global, you will see that the option for serverless will go away. All right, so choose the database location regional.

Global databases do not support serverless option. Okay? Under Database Features, choose Serverless and then remaining settings will change accordingly. So we provide cluster Identifier. I’m going to stick to the default one. Provide username and password. So username means admin here and I’m going to provide a password. Then we provide the capacity settings. So here we provide the minimum and the maximum ACUs that you want to use. Minimum is one and maximum you can have is about 256. Since this is a demo, I’m just going to stick to about two ACS. Okay? And then here you can have additional scaling configuration. So you can force the scaling capacity to the specified values when the timeout is reached.

So you enable this to force the capacity scaling as soon as possible. Or you can disable this to cancel the capacity changes when a timeout is reached. Okay, if you want to learn more about this, simply click on this and it will give out more information. So what it says is when you change the capacity or a serverless tries to find a scaling point for the change, if it can’t find the scaling point, it times that you can enable for scaling to change the capacity as soon as possible. And you can disable force scaling to cancel the capacity changes when timeout period is reached. And another option here is the pause compute capacity after consecutive minutes of inactivity. So this is where you enable the automatic pause.

So you enable the option and you can set the amount of time after which the database should automatically shut down.

So by default this value is set to five minutes and you can set up to maximum 24 hours. So I’m going to leave it at the five minutes default value. Then under Connectivity, you can choose the VPC that you want to create this database in. Then under additional connectivity information, just like any other audio database, you can set up the security group options. And one more thing that’s important here is the Data API. So this is where you enable your Data API. So if you enable it, you can use the Data API to talk to the Aura serverless cluster over the API in addition to the SQL clients. Under additional configuration you have the same options like Aura.

So I’m going to write an initial database name, let’s say my serverless DB. And then we have the parameter group, you have the backup settings. Again, minimum is one-day maximum is 35 days. And you can set up encryption and deletion protection. So the deletion protection is enabled. I’m going to disable it so that allows us to delete the database without modifying it first. All right? Simply hit the create database to create it. And again, this is going to take a while for this database to create. So I’m going to pause the video here and come back when this database is ready. All right? All right, so here we have our serverless cluster ready.

You can click on the cluster name here. You see the endpoint and the port that you can use to connect to this database. Okay, if you look at the configuration, you can see you have a cluster parameter group here. There is no database level parameter group. There are no databases configurable for us because this is a serverless cluster. And you can see that the option to pause compute capacity after five minutes is enabled. So if there is no activity on the database for about five minutes, then the database will be shut down automatically and the capacity units will be dialed down to zero. All right, so that’s about the serverless aura cluster. Let’s continue to the next lecture.

52. Using Data API with Aurora Serverless database – Hands on

Now let’s look at how to use the Query Editor with the Data API on Aura Serverless database cluster. So you can access Query Editor from the left side menu. Just click on Query Editor here. It’s going to ask you which database you want to connect to. So for us, it’s the database. Two. That is the serverless cluster. That’s the only serverless database we have.

Then you prove provide your username and password here. So you can add new database credentials, or you can connect with a Secret Manager arm. So if you have credentials stored in Secrets Manager, then you can simply provide the ARN here. Or you can simply type in your credentials here. And once you type it in here and connect to your database, then these credentials will be stored in the AWS Secrets Manager. So the next time you use the Query Editor, those credentials will be picked up from the AWS Secrets Manager.

So here I’m going to simply type in the credentials that we created the database with and you can enter the name of the database and that’s optional. So I’m going to leave it at that and simply hit the Connect to Database. All right, and here we go. We are into the Query Editor and you can run any SQL queries. For example, one query is written for us here. Simply click the Run button and you can see the response of the queries. These are all the rules from the Information Schema table. So you can run any sequel queries from here, and you can also see the recent queries. All right, so that’s how easy it is to use the Query Editor within the AWS console. And this internally uses the Data API and your credentials are stored in AWS Secrets Manager. So that’s about the Query Editor. Let’s continue to the next lecture.