Pass Amazon AWS Certified Database - Specialty Exam in First Attempt Easily

Introduction

1. Purpose Built Databases

So now let's look into something called "purpose-built databases," which is to find the right database for the right job. So, in the past, there was a rule that was one size fits all, which meant that you would use a relational database and apply a few rules for modeling data, and then you would have built your application. but recently there have been new challenges. There is a lot of data. So we get more volume, velocity and variety of data have increased. We now store data in terabytes to petabytes. We get databases that serve a lot of requests per second. We're talking about millions of requests per second. We need to have very low latency—we're talking about submillisecond latencies. And we have a user base that is going to be global. And so the relational databases: they're definitely good, they're here to stay, they're evolving, and they're definitely going in the right direction. But they alone are not enough to cater to all the data needs of today and all the use cases. And so whenever you build a modern application, because we have the cloud, it's so easy to request a specific kind of database. It's extremely common to see a mix of databases in any application. The idea is that you need to find the right database for the right purpose—the right database for the right job. And this is what we're looking at. So how do we choose the right database? Well, we have a lot of managed databases on AWS to choose from, and you need to ask yourself a few questions to lead yourself to finding the right one. So, for example, is my workload read- or write-heavy, or is it balanced? What types of throughput news do you have? Is it going to change? Is it going to scale or fluctuate during the day? Do I need auto scaling? How much did I store, for how long will it grow, and how big is my average object size? How often and how is it accessed? What about data durability? Can I lose it? Do I need to keep it? Do I need eventual consistency and so on? What is the source of truth for the data? What about the malaise requirements and how many concurrent users will access it? And finally, what kind of data model do I have and how will I create data? Is it going to be joins, structures, or semi-structured? Is there a strong scheme or flexibility reporting search in the RDBMS, or is there no follow-up database? Do you have any licensing costs? Can I switch to cloud database, cloud native databases such as Aurora? So, choosing the right database is about asking all of these questions of your data and your application to get the right answer. And now in this course, obviously, we're going to learn all these kinds of purpose-built databases. So in the relational realm, we have Amazon RDS, Amazon Aurora, and Redshift, with very different purposes. Okay? and we'll see them. I won't go over the details right now because Ria will be walking through them all one by one. For key value, we have DynamoDB and its complementary caching technology called DAX. For documents of the type in MongoDB, we have something called document DB. We'll explore this in this course as well. In memory, we have elastic cache that has two flavors, radish and memcache D. For graph, we have Neptune. For search. We have elasticsearch. For time series, you have a timestream ledger; we have QLDB. And for moving data all around, between all these databases, we have database migration tools such as DMs. So this course is going to teach you all of these things, including which one is best for the job and what the drawbacks are to using some of the technologies. I hope you're excited. I will. Riaz will see you in the next lecture, not me.

Hide

Quick refresher on the basics

1. Types of data

Welcome! I'm Riaz, and we're going to start with the basics. This section will be a quick refresher on data and databases; most of you will be familiar with the information presented in this section because it is very basic; however, I will go through it quickly, and you may skip ahead to the next section if you prefer. Let's begin, shall we? Data now. Data is a collection of values or information, and a database is an organized collection of this data. We have three types of structured data structured, semi-structured, and unstructured, and to support these three types of data, we have different databases and, more often than not, We end up working with a mix of these data types when we work with databases, so we have databases that support structured as well as semistructured data. Let's look at these three types of data in a little more detail first. the structured data now. Structured data is something that's typically stored in tables with a predefined structure; we call these structures schema. So we have this table that stores data about students, and we have four columns for student ID. First Name. Last Name and Birth Date, and if we wanted to add some more characteristics or some more information about students to this table, for example, Let's say we wanted to add an email address of students to the table, then we would have to first change the schema to include an "Email Address" column, and then we could add email address information to the table. So this is the way structured data works Structured data is suitable for OLTP as well as OLAP workloads OLTP stands for Online Transactional Processing, while OLAP stands for Online Analytical Processing. Both of these types are typical of relational databases, that is. The databases that have multiple tables interconnected with certain relationships between them and the relational databases also support complex query operations that allow us to get data from multiple tables using SQL joins SQL is a structured query language that we use to query structured data from relational databases, the next type of data that is semi-structured now. This data is also organized data but it's notconstrained by a fixed schema like in the structured data we had a fixed schema so if we wanted to add an email address field there. We had to change the schema of the table first before we could add data to it but in case of semistructured data. We don't have to do that because semistructured data accommodates variable data structures, as you can see in this example here. The first record has five attributes, and the second has one. the second record. has about seven attributes, so we have variable data structures, but still the data is considered valid, and this is typical of non-relational databases. This type of data is well suited for big data applications that demand high performance and low latency, and this type of data is typically stored in simple data formats like XML and JSON. making it very easy to read as well as very easy to process. And finally, we have the third type of data that's unstructured. So something that's not structured or semistructured is unstructured. It's an organized kind of data. You can think of files sitting on your computer as an example of unstructured data. So, for example, documents, photos, videos, and messages sitting on your computer or your phone are examples of unstructured data. They are typical of non-relational databases and more so of file systems like your computer or data stores like Amazon S Three. So that's about it. In the next lecture, we'll look at relational and non-relational databases in a little more detail. So let's continue.

2. Relational databases

First, we have relational databases. Relational databases have a predefined schema, so you can very well guess that these databases work with structured data. Relational data bases enforce something that's called "Acid Compliance," and they also support joins. So you can query data across multiple tables using SQL, and I'll come to Acid Compliance in a bit. Relational databases, as they use structured data, are used with OLTP and OLAP applications, and examples are MySQL, Postgres, SQL, Maradb, Oracle, and Microsoft SQL Server. And in terms of AWS services, we have Amazon RDS and Aurora for OLTP workloads and Redshift for OLAP workloads, and we're going to discuss these databases in detail in upcoming sections. Now, relational databases are characterized by multiple tables interconnected by foreign key relationships. So we have different tables. For example, we have the students table, the department table, and the subjects table. And these tables are interconnected through certain relationships, which we call foreign key relationships. For example, we have student ID from the students table linked to student ID in the subjects table, and department ID from the students table linked to department ID in the department's table. So the department ID in the student table is a foreign key, which points to the primary key department ID in the department's table. So this is how relational database tables are interconnected and linked to each other. And relational databases also use indexes. So you can see that student ID and department ID are the primary indexes on this table. And we can also have additional indices to improve the query performance. So table indexes are used to improve the query performance. Now, by default, relational database tables are indexed by their primary keys, and secondary indexes can be added on any non-key fields to improve query performance. Now, for relational databases, it's not mandatory to create these indexes, but it's always a good idea to create them depending on the type of queries you're likely to make on the tables. All right, now let's discuss: What is Acid Compliance? Now, acid stands for atomicity, consistency, isolation, and durability. Atomicity means all or nothing, so a transaction can execute completely or not at all. What this means is that when we work with relational databases, we have multiple tables and might write to multiple tables at once. For example, we have a banking transaction, so you might want to deduct money from one account and credit it to another account. The operations of debit and credit should go together. Atomic City ensures that either both transactions are written to the table or none of them are written to the table. So we ensure that the transaction executes completely or not at all. It shouldn't happen. That money is deducted from an account and it's not credited, so it would be lying somewhere in the middle. So that shouldn't happen. So that's the purpose of it, Amica. Then we have consistency, which means once the transaction has been committed, data must conform to the given schema. Relational databases enforce a strict schema. So whatever data that we insert or write to these databases must conform to the defined schema. Then we have isolation, which requires concurrent transactions to execute separately from one another. So we could have multiple sets of transactions that are independent. Two different customers might be doing money transfers from their accounts, and they are not related to each other. So these transactions must execute independently of each other. That's the purpose of isolation. And finally, we have durability, which is the ability of the database to recover from a system failure or an outage. So the "acid compliance" refers to the compliance enforced by the database. What this means is that these are the rules that are enforced by the database solution. On the other hand, you could also have your application enforce these rules or these behaviors if your database doesn't readily support it. For example, DynamoDB is a database that doesn't inherently support asset properties unless you specifically use transactional consistency. So we'll come to that in the DynamoDB section. But just so you know, DynamoDB by itself does not enforce strict asset properties, so you can have your application enforce asset behavior. If we have two different applications accessing the same DynamoDB table, then one application might enforce asset behavior while another application might override that. But in the case of MySQL, which is a relational database, these asset properties are enforced by the database. No application will be able to override the asset behavior, and hence, these relational databases are able to enforce Acid compliance.

3. Non-relational databases

Now let's look at the non-relational databases. These databases are sometimes called "no SQL," which means not only SQL, and these are suitable for semi-structured and unstructured data. And the data is stored in denormalized form. So, unlike relational databases, where we have multiple tables storing different data and they are interconnected through foreign key relationships, we generally have a single table containing all the data. So we might have redundant data or duplicate data in it. So that's what we understand by denormalized form. And if this doesn't make a lot of sense to you, you can simply ignore that. That's not so important from the examination perspective. Non-relational databases are suited for big data applications because they use unstructured or semi-structured data; they are therefore suited for big data applications. When we talk about big data, there are three characteristics, or three Vs, that define big data. We have high volume, high velocity, and high variety. By high volume, we mean a huge amount of data. High velocity refers to the number of input-output operations that are happening per second, and high variety refers to the variable data structures, or in other words, semi-structured or unstructured data. So these databases are suited for high speed applications or in other words low latency applications, simply because they use very simple data formats like XML and JSON and they use a flexible data model. What this means is we can change the database schema on the fly, and this typically results in trading off some of the acid properties of non-relational databases. So non-relational databases do not inherently comply with asset behavior. And finally, nonrelational databases are not suitable for OLAP or analytical workloads. These are some examples of non-relational databases. We have DynamoDB, document, DB, elastic, ash, and Neptune. These are the offerings from AWS, and we are going to study these databases in detail later in this course. Now, non-relational databases do not readily support asset compliance. There are some exceptions, but inherently they do not support asset compliance. What they support is something called "base compliance." Based compliance refers to essentially available soft states. Eventually consistent. Basically, "available" means basic read-write operations are readily available whenever you want them to be. Soft state means there are no consistency guarantees, and the state of the data can change. And eventually, consistent means that data is expected to be consistent over time. So the moment you write data to the database, it may not be consistent all across the board, but over time, after a few milliseconds or so, the data will become consistent. That's what we understand being eventually consistent." These databases' use cases, of course, are those that prioritize high performance over strong consistency. So we are trading some of the asset properties to get high performance by choosing to use non-relational databases that support base compliance instead of acid compliance. Now let's quickly compare relational and non-relational databases side by side. So the relational or the SQL databases and nonrelational or NoSQL databases. SQL databases have multiple tables interconnected through foreign key relationships, whereas no SQL databases have collections or key-value pairs. For example, DynamoDB or ElastiCache use key-value pairs, while document collections enforce strong asset compliance, whereas no SQL does not enforce strict asset compliance. SQL databases have a fixed schema for structured data, and no SQL databases support flexible schema or variable data structures that refer to semistructured and unstructured data. Relational databases or SQL databases support vertical scaling, and nonrelational databases support horizontal scaling. So if you want to scale a relational database, you have to increase the size of the database instance, whereas in the case of non-relational databases, you can scale by adding more nodes to the database. That's what we understand by "scaling out," or horizontal scaling. Relational databases use SQL as the query language, while non-relational databases typically use object-based APIs to interact with the database. Relational databases are suited for both OLTP and OLAP workloads, while non-relational ones are only suited for OLTP workloads, and these are typically found in web and mobile apps. So that was a quick refresher about the basics, and now that the basics are out of the way, we are good to go and start exploring the different database services that AWS has to offer. So let's continue.

Hide

Amazon RDS and Aurora

1. Amazon RDS overview

Welcome to the section on RDS and Aurora. So RDS is a relational database service. It uses SQL, a structured query language, and there are different relational database engines that it supports. There are about six engines that you should know, and these are Postgres, SQL, MySQL, Maradib, Oracle, Microsoft, SQL Server, and Aurora. Aurora is Amazon's own offering, and the remaining ones are third-party database services. All right, RDS is a managed database service, and it's generally launched within a VPC in a private subnet. And you control access to the database using security groups. And this is especially important when you're using lambda functions because lambda functions run outside the VPC, and when you want to use the lambda functions with the VPC, you generally provide access through the security groups. All right? And then storage for the database is through EBS volumes, and it also supports storage and auto scaling; you can use a GB-2 or IO-1 volume, so general purpose or provisional IUPs are supported for backups. RDS supports automated backups with PITR, and backups do expire. If you want to retain backups for a longer duration, you can take manual snapshots, and snapshots can also be copied across regions. You also monitor using Cloud Watch. And you can also set up event subscriptions through SNS. So you can't figure out the event subscriptions within the RDS console, but it internally uses the SMS service, and RDS does support multi-AV deployments, so it's a high-availability service. All right, so why should we use RDS? So, when you self-host your database, you manage everything: you manage the hardware, you manage the operating system, and you manage the application as well. So, database application, this is everything on you. When you host your database on AWS Cloud, for example, on an easy-to-instance but not on RDS, AWS manages your hardware, but you must still manage operating system patches and your database application. But when you use RDS now, AWS manages everything. So AWS manages your hardware, it manages your software patches, and it also helps you manage your application. So database application databases are also managed by RDS. And if you want to learn more about this, you can definitely have a look at the database shared responsibility model. And this will help you understand what is within your scope and what is within AWS Cloud's scope. All right, so using RDS versus deploying your database on EC Two, RDS is a managed service, provisioning is automated, and OS patching is taken care of by AWS. It supports continuous backups, and you can restore to any specific timestamps, so it's a point-in-time restore. You have monitoring dashboards and any credits within RDS, and these are provided by the Cloud Watch service. RDS supports read replicas for improved read performance, and of course it supports multiAZ deployments for disaster recovery. RDS provides maintenance windows for upgrades. AWS does the maintenance for you, and you simply tell AWS the preferred time slot during which it can do the maintenance. Then RDS supports scaling capabilities; it supports vertical as well as horizontal scaling, and the storage is backed by EBS. You can use GP-2 or IO+volumes, and it also supports auto scaling. One thing you have to remember is that you cannot SSH into your DB instances when you use ODS. So if you had your database running on an ECQ instance, you could SSH into it. But audio doesn't allow you to SSH into the underlying DB instance.

2. RDS pricing model

Now, let's quickly look at the RDS pricing model. When creating an Arduous instance, you choose the instance type, so it could be an OnDemand or Reserved instance. You choose the engine type, so you choose which database service you need, like Postgres, SQL, MySQL, Maradb, Oracle, SQL Server, or Aurora. And then you also choose the instance class based on your resource requirements, like how much memory or CPU you need, or how much I/O capability your application requires. All right? And it does use a pay-as-you-go pricing model. You are billed by the hour. Now, let's dig a little deeper into instance types. You have two types of incidents: on demand and reserved. So on demand instances, you pay by the hour based on your compute capacity. And if you choose a reserved instance, then it will be deeply discounted. So you can reserve your instances for a period of one to three years, and you get a huge discount as compared to on-demand instances. All right. You're also designed for storage at GB per month. You're billed for backups. Or if you export your snapshots to S Three, you'll be built for that. You also pay for I/O per million requests. And similar to other AWS services, you also pay for data transfer.

3. Instance type, storage type and storage auto scaling for RDS

Now let's look at the instance classes. There are a couple of instance class types. So you have standard instances, memory-optimized instances, and vegetable performance instances. So standard instances are general-purpose instances. You'd use memory-optimized instances if you have memory-intensive workloads or if you need high performance. Smaller workloads are typically served by right burstable performance instances. So, depending on the workload, these can burst to a higher level of CPU performance on demand, while providing a baseline level of performance at other times. And this works through CPU credits. So one credit is equal to 100% CPU core utilization for 1 minute. So you get CPU credits when you underutilize your capacity or underutilize the CPU. So you accumulate the CPU credits and can use them when you need a higher level of performance. All right, then let's look at the storage types. There are a couple of storage types. So you have general-purpose storage and provisional IOPS. General-purpose storage is a cost-effective option. It uses SSDs. You choose the storage size, and you get a baseline performance of three IOPS per GB, and volumes below one TB can boost to 3000 I Ops. And again, this uses the I/O credential, and you typically use these instances with variable workloads and with simple use cases. You can use it for defense environments or for small to medium-sized databases. All right? And if you have a production workload or need high-performance storage, then you should generally choose the provisioned IOPS or PiX. All right? So Pi apps work similarly, but they do provide you with dedicated IOPS. So you choose the storage size and the required IOPS. And the Pix volumes do provide you with fast and predictable performance. So you can get up to 32,000 IOPS per DB instance. And you should typically use these options in production and when you require consistent high IOPS. All right? So for IO-intensive workloads, this is typically a good idea. And these are also well-suited for heavy workloads. And remember, you have to choose your storage size carefully because if your instance runs out of storage, it will not be available until you allocate more storage to it. To avoid such a situation, you can use the Storage Auto Scaling feature. So you just have to enable storage Auto Scaling. So when you enable storage auto-scaling, if your instance runs out of storage, it will be automatically scaled up. Storage auto-scaling means that you simply enable the storage auto-scaling feature on your database instance, and AWS handles the rest. So both the storage types (GB2 and IO1) support auto-scaling and AWS RDS dynamically scales up your storage capacity based on the current workload. Storage is scaled up automatically whenever your utilization nears the provisioned capacity, all right? And there is no downtime during this auto-scaling scaling operations. So there is absolutely no downtime. And you save costs due to overprovisioning, and you also prevent downtime due to under provisioning. So this is a win-win situation. And you should use storage or scaling when you have unpredictable workloads. And remember, you have to set the maximum storage limit, which allows you to control your costs.

4. RDS parameter groups

Now let's take a look at parameter groups and option groups. First, the parameter groups. Now, parameter groups are the configuration values, and these are specific to your database engine. So for example, if you have a MySQL database, then parameter groups will be different. And if you use Postgres SQL again, the Parameter Group configuration is going to be different. So each DB engine will have its own configuration. And these configuration parameters are available as parameter groups when you use RDS. All right? So you could think of a RDS parameter group as the RDS equivalent of configuring my CNF file on your MySQL DB. So, for example, if you had a MySQL DB instance running on your premises, you would typically edit the My CNF file to configure the database with RDS; you would do so using parameter groups. Whenever you create an instance, there is a default parameter group that gets associated with the database instance, and you cannot edit it. So if you want to make changes to the configuration, what you do is create a new parameter group. This new parameter group will inherit all the settings from the default parameter group, and you can change the configuration of this custom parameter group and then attach it to your database instances. All right, you can apply the parameter groups to any instances within your AWS region. and these are some of the examples of the configuration options. And this, of course, will vary according to the engine. So you can configure the auto-commit feature or time zone. You can use parameter groups to configure SSL or choose the default storage engine, or you can control the maximum number of connections for your database instance. These are the configuration parameters that are generally available in the configuration files of your DB engine. And in RDS, you control it through parameter groups. Now, there are some considerations when you make changes to the parameters in your parameter groups. There are two types of parameters in your Parameter Groups: static and dynamic. So when you change a dynamic parameter, the changes are applied to the instance immediately, irrespective of the Apply immediately setting. Every database instance that I created in RDS has an apply immediately setting. So whatever modifications that you do to the instance, you can either apply them immediately, which results in downtime, or you can apply them during the maintenance window, right? So when you apply changes through a maintenance window, it's like a planned downtime. And when you apply immediately, it's like an abrupt downtime, right? So irrespective of the "apply immediately" setting, when you change the dynamic parameters, they will be applied to the instance immediately. But this is not the case when you change static parameters. Now, static parameters require a manual reboot. Remember, it requires a manual reboot. And until you manually reboot an instance, the Parameter Group will show a status of "pending reboot" when you make changes to static parameters. So when your Parameter Group shows a status pending reboot," it's not going to automatically reboot during the next maintenance window. And this is super important to remember: the status of "pending reboot" does not trigger an automatic reboot during the maintenance window. You have to reboot it manually to apply static parameter changes. All right? So once you reboot, then the status will change from "pending reboot" to "in sync." So you have a parameter group in sync. If you change a parameter or a static parameter, the status will change to "pending reboot." and only after you reboot the instance manually. Whether you do it during the maintenance window or outside it, you have to reboot manually. And after the reboot, you will see that the parameter group status changes to in sync. Alright?

5. RDS option groups

Let's now look at the option groups. Now, this sounds similar to parameter groups, but there is a slight difference. Option groups are used for the configuration of optional features that are offered by different DBE engines. The configuration options that are not covered by the parameter groups are available in option groups. And the default option group is always empty. You cannot modify it. And similar to parameter groups, if you want to make changes or if you want to configure the options in the options group, you should create a new option group and attach it to your database instance. All right, so this is an example of a MySQL option group. This option is about memcache D. So this allows you to configure the memcache Dcache on top of your MySQL TB instance, all right? And when you create a new option group, it naturally derives its settings from the default option group. But anyway, the default option group is empty, so you remember that it's empty. So the new option group will also be empty when you create it. and then you can add available options to it. So here are some examples of options. SQL Server backup restore is available. In SQL Server, you can use native network encryption in Oracle. Then you have the MariahDB audit plugin in MariahDB and MySQL. Now, this is used for auditing. So if you want to audit the database activity in your MySQL database or MariaDB database, you can use the MariaDB audit plug in. So you have to configure it in the options groups. And again, McAsthd is the option in MySQL to enable memcache caching support in the MySQL database. So now let's go into a quick demo, and we will create our first RDS database. will also take a look at the parameter group and option group features available with RDS.

Hide