Transcript for:
Introduction to Databases and DBMS

so we want to start databases today are there dbas in the call do we have some dbas in with us any DBA DBA database administrators so we are going to discuss databases and datab is it's um whole wall on its own it's not something we can give you in-depth knowledge about databases but I'm going to try as much as possible to give an overview so that you understand what we are talking about uh that's why I was asking if there's a DBA because database administrators that's another field I'm sure you guys have heard about dbas you go and you do in-depth database studies and how you everything works right most likely in um your working environment you're not going to be uh um working in depth like inside the database as um Cloud Engineers or as Architects most often you would be uh required for suggesting the ads databases that they can use but most of the type of database um most sometimes the configurations the developers that are developing the applications that would already know what kind of database they can use all right even though sometimes they think that they know but the application um down the line would say okay we need to switch to a different database and when you get to a point called performance testing they testing different um databases that would be most compatible with um the application and they can switch okay that's said when we are creating our bank accounts or we are registering in a new city on a new country we are giving information when you're creating your accounts on social media platforms you're giving information they're always asking you from information your name your date of birth your um uh AG your what is it um uh post Light Side your zip Cod box zip code thank you your zip code and your city and all that so all that is data which you are supplying to the organization that you're subscribing to or you're creating an account for in like um a bank and when I collect all this information from you that information is refined and stored somewhere where the information is tau is referred to as a database I'm sure that's very straightforward any equation so far so a database can be defined as an organized collection of structured information that is stored and access electronically is an organized collection of structured information so that information is what you generally refer to as your data which is stor and so typically in computer systems all right so when you when the organization gets your data they refine it and they store that data they need to be a way for them to access that information which is tored in so the different software programs that presents or gives the uh administrator or whoever is storing that data and access to that data gives it an interface to be able to manipulate that data add information to that data to where it's stored remove information from it is known as a dbms a database management system e so database is controlled by a database management system commonly referred to as dbms so this dbns is the software that provides an interface or users or applications to interact with the data so it is going to be VI the dbms that database administrators are going to retrieve data we can update data we can manage we can organize the data in the database so most of the times when people just say a database It's a combination of the database itself which is the structured uh uh data which they collected from you and they stored and the dbms which presents the interface for users and applications to be able to reach these data which has been stored in um on electronic systems does it make sense yes great so the database the dbms with the dbms you can uh manipulate can perform tax related to data stol manipulation you can use the dbms or the interface which the dbms presents to you to manage users in the database so security you can use it for uh backup and recovery so the different dbms or database management system have different methods or different um ways in which all the different task are are done all right so generally there are two types of dbms so from a very high level can you go to the prev pages give me a minute please are we good one second yeah we are good with with this one so there on a very high level there are two main types of database Management Systems so they refer to as relational tbms relational database Management systems and nonn relational so the relational database Management systems are commonly known as um what s SQL databases and the know relational database Management systems are known as no SQL so when you come across the SQL and non-sql terminologies when it comes to database when they are talking about SQL they are referring to relational databases and they're talking about no SQL they're referring to non relational databases so for the relational database there are different types of relational database Management Systems so an example would be marad DB my equal postgress Oracle and an example of non relational databases would be something like Cassandra mongodb and in AWS Dynamo DB so this is not an exhaustive S of relational and non relational database Management Systems okay um PA please is one sec please the example of the relational databas is like the one after my I can say I want to start with P postal okay thank you even the one on the um no relational the last one I can't see I don't know if it's dying I can't see Dynamo Dynamo DB Dynamo all thank you there are so many types of no databases there are document DBS outside uh out there the graph uh databases and all the different types of databases ads has tried to have their own service which is giving you a manage um equivalent of the these databases okay so if we look at relational databases in relational database it is in its name it is a database that stores information based on the relationship within the data okay so in relational database the most important characteristics of of of of relational databases is that data in is St in in in tables and that table builds a relationship between the different attributes of the data um in the database okay for relational databases there is a concept of schema which is if I have a set of data how do I want to how do I want this data to be stored in my database so this schema is defined is basically a schema is basically a structure of how we are going to store our data for example we have um um um you students in the model batch for GJ Tech and if you want to get information for all the students in this batch we can decide that okay the schema or the structure of the data is going to be the batch that your your your your the batch name that you belong to the the age of the students um the city of the students um the zip code and um all this information is defined before the data is entered into the database so that structure which is referred as as to the database schema is defined before data is added into the database as you keep adding data into that database it is very very complex almost impossible to alter the schema of the data so if we are having the schema for for for The Bash for this bash we Define all the information we need that information is is basically or that the structure of that information is basically decided before we start putting data into the database okay so once data is in the database for us to add more attributes or more um details or attributes to that data it becomes very very very a tedious task because we are trying to alter the schema so for relational database you need to take note in relational databases data is always entered into tables so that table the structure of this table is the schema of the database and that schema is defined before we enter data or data is put into the database okay so relational databases the store information in tables and the table defines the structure of the data that is if you have a problem my writing please you tell me your structure of the data is known as the database schema Ro can you please come back to the thank you for [Music] [Music] [Music] so in relational databases the data is stored in tables so you can have multiple T tables in in in in your database and from those tables you can build relationships between the data in one table and the data in another table so so you can establish the relationship you can look at the data which you have establish the relationship that that that exists between the data in one one one table and the data in another table okay so each for each database [Music] table there is there is a primary key so in our demo we going to see a primary key so basically if we want to put data in a table assume that we putting information concerning you students in this bash we need to know have a way of identifying each information which we put for example the information that that is about TAA should be unique from the information point for shant for pal for Victor for Leonard for Ev so there is a key referred to as a primary key that uniquely identify each set of information which we are putting in the key for example what do I mean by this if we have a table in our database and let me say this is um stud the name the name um location or City I want information concerning your ZIP code um what other information can we take we have the name City ZIP code and let's say the subjects which each student is taking so this is a a form of relation of of of of of a table in a relational database okay so I can have a student called Victor it can be multiple Victors so another Victor that lives in Texas and the second Victor lives in Maryland and they can have uh different zip codes you could also have Victor that's living in the same zip code in the same city and you can have these different students basically taking the same subjects okay so in this case we having entries into our relational database table but we need to find a way to uniquely identify each data in our table so in that case I can add a column and I will say this is will be the student ID and for every student they would have a unique identification so to three in this case I'm using just student ID 1 student ID 2 student ID 3 and will see that we can have multiple Victors living in Texas or having the same zit code offering exactly the same subjects but they must have they they all have unique student IDs so the student ID in this case on on our table is what will be referred to our our primary key so for each database table and a relational database you must have a primary key which uniquely identify each data [Music] set is that clear please so please um you have to set this primary Keys yourself you need to set that yourself before when you're defining the schema you need to set the primary Keys yourself because once that is done or once data is entered into the database you cannot have you need to have a way to distinguish so example that let's say you have 500 students we have a th000 students in this in this course all right you need to have a way to if uniquely identify each student in that database so the primary key is what we uni uniquely identify each student and based on the primary key I can query the data in in this database that for for Shanta or for Victor of for TA of for Mara okay so that I do not have to go through all the data in the database so you guys in the US I think you have had is it called your security nor social no two people Social Security no no two people can have the same social security number that social security number in whatever if they using a database that will be your your the primary key on that database because that is the information that uniquely identifies you you can have two people with the same name living in the same seat code driving the same car working in the same company all that other attributes do not you need to identify that information so the primary key must be defined for every schema before you start putting data into the database um is the is a key autogenerated if you're adding users to the database you can autogenerate it for the simple simple database which we are going to create it for the simple table that which we are going to create during our our demo you would see how we we autogenerating it okay we'll set the primary key we'll Define it and we'll tell the primary key how to uniquely uh increment itself any question yes S I mean the primary is to identify and no uni no repetition identify uni data with no no repetition right yes yes yes the question I wanted to find out is um you see like the examplar table you've just uh shown us um yes so I wanted to ask yes I wanted to ask um a table like this or a database like this is it designed by a particular um let's say an architect or some some sort of and then and then you got you have a database uh Personnel now all the job is just to input and then everything Auto generates and since everything is linked I don't know if that makes sense please please repe your question okay so what I was trying to ask is um as a database Personnel it's your job just to input the information um while the database is designed by an expert like the architecture is done by an expert so um in most cases this database information would be put in by application so there will be an application for example if you if you're if you're creating a bank account most nowadays you just need to go download the app for that bank and you're putting in that information there right so that app is the application once you're key putting in that information the app is writing that information into a database okay so in most cases a a a um application will be writing but before that application is putting information into the database you need this the schema of the database need to be set okay so the developer of that application would would know how the application would write information into the database and they would come out with the schema right okay that's they would come out they would come out with the schema as a cloud engineer depending on your environment in some environments you would never be faced with stuffs like this some environments they expecting you to be a jack of all trade in my environment for example we had to get to the point that we we tell customers that we are not dbas we need the indp database stuff please HBA sure so but but but the basic stuffs in the database you should you should understand you should understand a little bit of SQL what it's doing how to create databases how to update information in the database how to create this this because um you might be faced in an environment mostly small companies where they would Define the schema they would tell you how they want the schema to look like for example a table like this and you need to go write the SQL script that would create you a schema like that okay sure really answer my question stop Prof good thank you so there are other type of keys that are very common with um to your hands it up I think we already responded to your question there are other types of keys in relational databases there what we call foreign Keys the composite keys and a bunch of different keys so at strongly advise you to do a little bit of of of of of inp research when it comes to to databases okay so data in relational database is manipulated using a a query language known as SQL for and sqs stands for structured query language okay I'm just look at my time see how we doing so all relational database uh uh query data updates data using SQL so so no see databases or non- relational databases This Tall unstructured semi structure data so the data is not presented in table of form so once you hear tables in a database we mostly referring to to relational databases and a common oh dat data in no SQL is Ally represented using key value pairs okay so most of the data in in um uh that that is um stored in non relational bases mostly in key value pairs AG of documents in Json format so you can see this is an example non relation uh uh uh data representing somebody Street state ZIP code rather than having this information in in in a taba form for relational databases they are having it in in a document form Json format and for each information we have key key and values do you understand what I mean by key value pays here yes sir good so for example we want um the name of of of the person we we getting the information for so we have a first name first name here would represent the key and the value would actually represent what we need which is which is Jane okay and the big requirement for for for data in this format is each key in the document has to be unique all right so just as we had primary keys for relational databases that uniquely identify the information we need to ensure that the keys in our key value pairs are also unique hey Prof just to to get some clarity this kind of relationship please it in um sorry we probably seen it in AWS as well you know where we have to type in something that says name and in the second field we putting a specific unique name or something yes that the kind of thing we're talking about here so are mostly key value pairs it's very common in AWS but the the the console the user interface beautifies is for you but if you using API to actually query it from your CLI you would see that most of that information comes back to you in Json format gotcha that's why you if you remember when you're configuring your CI they ask you your output that format that you were putting there is how the the thing would given back to you with default being Json say please page 106 thank you so [Music] having looked at the different um database Management systems for example Marb um uh postgress and all the like those are the DBS systems for relational databases so you can decide to have that those relational databases you can decide to manage them on your on your own by spinning up a insute instance and and launching or installing all the software for your database or you can use AWS and use manage resources for these databases so in AWS the service that gives you the ability to use databases or manage databases is what we is referred to as RDS so AWS relational database service so let's back up for a minute and look at it this way if we want a database and we not using um uh managed database from a aw what will happen we would need to for example if you're on premises buy the hardware for this uh um database that the database will run on set up all the networking in your envir in in in in your own premises Network environment install the the the the uh database software and manage that going forward so you see that if you're not using uh if you're using a self-managed database system it comes with a lot of admin overhead okay in that case you need to to think of backups the information that is being stored in your database how do I ensure that if there is a problem in my environment I can recover so you need to think and set up backup and Disaster Recovery strategies and you also have a problem with with scaling all right so let's say that we are setting up our your environment on premises I'm not talking about AWS yet you need to go buy Hardware that this what once you buy Hardware it already comes bundled with storage it B bundled with CPU it comes bonded with with uh what is it memory and all these different resources for your your uh uh database server it's static so if I come and you're running your applications now and you've already you're using your database and they the the the the um application is running out of database uh resources it becomes a problem for you to scale so you either over proficient resources at the beginning of your project or you would have a resource bonck because you underestimated so if we switch from using self-hosted or self-managed databases and we are using manage resources from AWS all the different aspects are already taken care of so in in in AWS we can easily scale our database as the need required s so if we start up using a database of let's say 10 gigabyt of memory or 50 gabt of storage and over time we see that we need more storage you can easily scale this different resources when using a manage service from AWS all the admin overhead for selfhosted database you would need to patch the the the server on which your database is running you would need to upgrade your your your your database you will need to ensure that you have the right licensing all this is taken away from you all this admin overhead is taken away from you once you you switch from using um self hoset databases and start using manage databases from from from from AWS any question um hello sir yes I want know does AWS support no relational database or is just database AWS supports non relational databases an example is Dynamo DB would that fall under the RDS no that falls under no SQL Al the AWS manage service is no SQL this is a database this is one of a no one of the no secet databases for AWS Dynamo DB they also have something called key spaces which is basically Cassandra Amazon key spaces which is basically candra so each of these databases AWS what AWS does is they try to offer an equivalent a manage equivalent so if your um um um application on premises is already running on something like postgress which you're managing the postgress yourself you can easily switch and start using a manag postgress from AWS and you forget all the about the uh the admin overhead managing the database managing the server on which that is running because AWS is taking all that away from you you can easily configure uh uh uh dis Disaster Recovery scenarios okay because with AWS you can deploy your your your your databases in a highly available setup you can even use multi you can use m M ads and or multi region setup when once you're when you're creating databases in in in AWS so these are all advantages that would that triggers customers to switch from using their own managed uh uh databases their own self-hosted uh databases to manage databases okay so whether it's um SQL or no SQL database it all comes under the relational database service no I'm talking about relational relational databases Al s is for relational okay yes sir thank you so AWS has the RDS service and with the AWS RDS service you can create the different types of relational databases so the common databases that you know postgress Maria DB uh MySQL Microsoft SQL Server they are all offered but AWS also has its own proprietary dat relational database developed by AWS known as Amazon or Aurora so with our is you can mem DB postgress and you can also use AWS database engine known as AWS Aurora so with ALDS once you want to create a database in AWS you need to decide on the database instance yes P yes sir um can you go one more step back the other page yes so you said uh you can create databases using the standard engine and also you can create database using the um AWS developed engine known as or so what's the difference between these two ways of creating database no it's it's the database engine so if you want you want to use a relational database you need to decide the type of relational database which you which you would use right if it's MyQ it's Mario is progress for example but if you're not in AWS you do not have the opportunity to use an AWS optimized database engine which is referred to as Aurora so if you're using um um um relational databases and you are in AWS then you get the the the the the opportunity or the the possibility to use an AWS optimized product okay okay so once we are creating the databases as we will see in the demo you can decide what type of database engine to use is it you standard my SQL Maria DB post because all of this is still based on the open- source my Marb and postgress datab engin that you that you using you're using outside AWS that's why you can easily migrate applications using this databases into AWS you will just be a is just doing the heavy lifting for you managing for for uh heavy lifting of the admin overhead for managing the servers uh patching and stuffs like that okay but you it's it's it's exactly the same product but in AWS you have the the the opportunity you know to use an AWS optimized product which is Aurora okay yeah so a is AWS optimized um database it's a relational database in AWS and it's optimize ads has spent a lot of money money to optimize the performance of Aurora so Aurora would outperformance of this this these databases so you can use the standard post or you can use an the Aurora version of procress okay yeah so a yes followup question I was just asking cost wise is it that it looks like the Aur is more expensive a little bit costly okay but most most environments most customers um comfort using or I think I have just one customer which is using which I have seen that they using is just the standard um postgress so if your application is working fine and you do not have any performance issues then that's fine most companies try to use Aurora because it comes with um some some performance Performance Tuning additional features okay all right thank you sorry sir that was what I was going to ask but you you just answered it because um what will make a database administrator who has written his code and doesn't want to kind of migrated to Aurora for example simply because it was you know he has the option to still put it in my SQL or yes so you can you most most most people tend to go to Aurora when they are facing some performance issues because how Aurora handles your data how Aurora commits your data to memory how produces your data for queries it's a little bit faster than the standard um um engines a has built that with a little bit of uh um additional to to basically entice you to use it but if you're already using Maria Deb outside of the cloud and you're migrating into the cloud and you want an equivalent Mario DB but now without the heavy lifting for managing the server that the Mario DB engine is running on for having the opportunity to ensure that you have a Dr scenario for having the uh uh additional feature of of backup and retention because that's a big deal for on premises environments you need to write scripts that are going to copy your data and back them up for AWS you can just click a button and AWS does that for you so those are the advantages for moving into the cloud and using these managed services okay okay so you can can directly just move and you're using a standard Maria or if you're saying oh my application is using the open source Maria but I think I I need more performance from here you can move to Aurora the Aurora offering of Maria DB okay and pay a little bit more great one more step behind please thank you okay [Music] so a database instance is just like when we doing E2 you remember that you needed to select an instance type based on the capacity which you want all right you I want an instance type of of of 500 gigabytes I want an instance type of uh of of uh uh 8 gigb of memory 20 GB of memory 100 GB of memory but based on your requirements you select the instance Ty for your D database so for your for your server that is exactly the same thing with RDS all the s is all based on E2 instances so in this case it's referred to as DB instances the database instance but it's just like if you to self-host your database you need to you need a server for the database engine to run on so out the database instance is the basic building block of uh Amazon LDS for so on the database or the on the on the database instance you can then create multiple databases does that make sense so the database instance is not the database on the instance we can create databases instance okay the instance is just like an E2 instance it's the server that would host our database in our databases okay so you would decide on the database instance so we say considering the resource requirements of the database you will decide on the database instant size so let's look at the deployment strategies for LDS okay once you want to create your LDS and AWS you need to decide on how do I want my database to be created so there are two main or three main uh um deployment strategies in uh uh in RDS there's something called multi a multi easy deployment no multi easy instance deployment sorry multi easy instance deployment and a multi easy cluster deployment so from its name MTI e it means we are telling AWS that for this databas instance that we want to create please we want it in multiple a if you are creating your database using multi-az instance deployment how AWS Architects your database in the background is completely different from when you decide that I want a multi-az cluster deployment so with a multi instance deployment the database instance is created in two availability zones however you in this case you having a passive active deployment what does that mean it means that AWS creates your database and you connect to one of the database which is active there is a standby database instance managed by AWS and you connected or transferred to the standby instance only in the case where you have a data an easy failure so for example if I have two a's so this will be my easy one and I have easy2 remember that this is this will be sub okay and I'm having a multi-az instance deployment AWS put my database instance in one easy and puts another database instance in the second a however we having an active instance and here a passive instance what does do this mean it means that the passive instance is just for data for for Disaster Recovery during write and read uh uh uh queries when Once when data is written and queried from the database all your database queries are using the active database instance does it make sense yes yes so we have a primary instance and a standby so in a multi- eing instance employment uh deployment a you select the two avilability zones that you want your instance to be deployed into a puts the primary instance in one and puts a secondary in the secondary or the standby instance in the other EAS however data is [Music] synchronously there is synr data replication the between instances what does this mean it means that the primary instance database instance which is what your application is talking to this is what your application is reading from so all database queries are going to the primary instance okay and once we have right or cruit operations delete updates AWS synchronously updates that information in the standby instance so that if we have a disaster scenario and your primary instance is down AWS automatically fils over your data your applications to the standby instance and because of the syncronous DAT replication your standby instance would already have up to-date data the key information here is in a multi- instance deployment you're paying for two databases instances which you're not using which you cannot use so even if you want to use the standby AWS only makes that available to you when the primary instance is down however in a multi easy cluster depl before I go to to multi e cluster deployment this is very important so let's write it down in multi e instance deployment [Music] data is written [Music] [Music] [Music] in multi easy instance deployment C operations in the database is on the primary database instance very cool M are we together the what does scw stand for please stand uh Cru stands for create read update delete so you create like you can create a database read from the database update information the database delete information from the database but basically database operations yes but this is a very common acronym in the wall of databases [Music] [Music] standby instance it's mainly for Disaster Recovery over scenarios data is synchronously replicated between DB instances hold on was that too fast little thank you for multi easy cluster deployments AWS creates three database instances by default you can it's configurable so we have three DB instances and for the 3db instances you have one write and two read so we have one writer instance and two reader instances so if you for example um a company that is what is it um for example a news uh organization they are pick hours where you're having a lot of R Traffic right so if you're using your database in a multi multi a instance deployment at that pck hour all your write and read uh operations are happening on the on the primary database instance but if you are using a multi multi easy cluster deployment now you have a writer instance and you have reader reader endpoints so you can configure your application to direct all right traffic to the writer inside and all read traffic to the reader instances so this drastically improves performance depending on the application do you see that yeah so in the in this case all the different end points or all the different instances are available for for use so AWS creates the writer instance in one a two reader instances on the two in two different AES and all right operations can be done directed to the writer instance the writer instance supports be both read write operations so you can write and read using the writer instance and point but the reader instances support only read operations okay so you can configure your application that oh all right trap uh traffic or all right traffic um cage should go to the right and in end point re query should go to reader instance end point and this basically this drastically impr improves the performance of your application so how does fail over take place for multi- a cluster deployment so if we have a problem with one of the A's and it happens to be the a where the writer instance is in AWS automatically PR m one of the reader instances to become the new writer are we together it's a quiet day in class today are we good so for multi a cluster deployments like I said there are three database instances by default placed in Three A's and all right operations the right endpoint supports both read right operations but the reader instances have a read reader endpoint which you can Direct Read traffic to the read these reader end points how does fail over happen in this in this scenario so if there is a problem with one of the as's and it is the a where our writer instance is in basically what does that mean it means we cannot perform read update delete operations create update delete operations again on the database okay because those are all right information uh right right right uh um calls okay so in that case we cannot access our database for right operations what does AWS does Dos promotes one of the reader instances in the background to become the new writer instance okay in a multi easy cluster deployment data is semi synchronously replicated between the instances why semi because if you have two reader instances or three reader instances in this architecture right operations go to the right and point the writer instance replicates that info that data to the reader instances so let me put it this way so I have my instance mind you this are instances in subt in different AES hope it makes sense three different AES because of space I'm not drawing all those things so let's just read the one and this is reader tool so all right operations come to the right instance right and once data is wrting onto the database in the right in right instance a is replicates that information to the reader instances why because if there is a d uh scenario one of the reader instances should have most the upto-date information about the data in the database so replication here is semisynchronous synchronous is that clear me a minute what does this mean it means that once a right operation happens and the data is replicated to the reader instances that data is committed once one of the reader instances acknowledges re of that information so if you have five reader instances it's not waiting for all the five reader instances to basically send uh information back to the writer inance that yes I got the information which you sent to me is waiting for just one confirmation once one of the read instances confirms receip of the the data the data is then committed so the reader instance during fair over the reader instance who which got the last update from the writer instance will be the instance that will be promoted to the new writer does that make sense yes sir so Mao is asking that when one of the reader instances replaces a fied writer instance will another reader instance will SP up to restore the original number of reader instances making it two again yes AWS does that in the background so in the background AWS will check what's happening why is why do a writer F then if it's fixed that writer which that instance that was a writer becomes a new WR a new reader so it doesn't go back to become a writer does it make sense yes sir good so it basically doesn't terminate it or anything like that it just it carries on after it's been F over in the background AWS is trying to figure out what the problem is so it might be a connectivity issue on their own side it might be for whatever reason that your writer instance is not available the reader instance that got the last update from the writer is promoted to become the new writer then once the writer the old writer becomes available it is it joins the reader pool than you quick question sir so sir just following up then to take it a little further you did said if for example the the um the failure due to maybe an AZ being blown away or something happened to that AZ does AWS put this in a different a and continue to do the failover promot of if you if you enabled multiple ACS yes if not then it's going to put that instance in the same a so you're going to have in two instances in one in one of the a available azs just like load balancing if you remember if you have multiple A's it has the intelligence to put the instance in the different A's but if you enable just a few A's in order to reach the desired capacity is just going to keep adding the instance in the same EAS uh okay quick question sir yes um what's um is this the same as like in terms of the disaster recovery strategy is it the same is it what we mean by pilot light what we mean by pilot light pilot light um Disaster Recovery strategy yes something like that oh okay I thought the pilot light will be something like the standby you spoke about earlier rather than this one right yeah but but remember that this in this case it's also um ensuring hi ability because um your databases are in different AES and once an e is down AWS is trying to ensure that the new your your your your databases available however the P reader was was not as available for all cruit operations makes sense yes sir sorry the first reader was not available you said yes the first reader because once you have you have reader endpoints and writer endpoints the writer endpoint supports all cruit operations for the database but the r endpoint does not yeah okay okay yeah so let's talk about addas backup and and read replicas then we can take a break then come back and we do our our handson so RDS one of the advantages of using r s for databases is the automatic the backup and restore uh functionality that AWS gives you so if you create your database right each time you're you're deleting a database you're going to see that in our demo AWS ask you that should they create a snapshot of that database you need to acknowledge that oh I do not want a snapshot so with LDS there there are two types of backups we have what we call manual backups and automatic backups so once you create your database by default AWS is creating automatic backups of that database every day or every five days or every hours is configurable okay so if a problem happens with your database then you can always just go to the console and you look at the automatic backups and you can promote that snapshot that awso automatically took for you to a new database does it make sense yes sir good so there are two types automatic backups which AWS does for you or manual snap so as a company you might decide that oh I want to take a snapshot of my database every Tuesday or every Friday and this manual snapshot this this this runs and creates um a manual snapshot of the database for you the difference between automatic backups and manual backups is AWS manages the life cycles of the snapshots or the automatic backups so after it ranges between Z to to 30 to 35 days is configurable so you can tell a that oh your automatic backups please after 10 days delete them but the manual backups they will stay in your account until you delete it and AWS charges you a small fee for the storage Associated to that database okay yes sir are you going to write something on that one oh okay I thought we got it no we have it just for posterity I'm trying to rush up so I we have just 15 minutes before we can take a break oh okay sorry so all these backups so we have automatic backups and manual backup so with this AWS automatically create snapshot of our data of our database so here the user figes the backup strategy yes and manages the life cycle of backup of the created snapshots snapshots after the retention period period so the retention period is what you will tell AWS that oh for the snapshots you create please return return them just for 15 days keep them just for 20 or 30 days once that retention period is over it cleans that away from your account however for the manual snapshots if you do not delete them they would stay there forever so there is also the concept of read replicas we just go back one slide just a picture that's okay please what's the what did you write after retention is that retention period retention period yes thank you Prof so what are read replicas already is so if you look at um E2 we had what we call vertical and horizontal scaling you remember yes sir so we can scale our can add more instances when we want for example utilization is is too much as we saw but that ability or that feature is not available for databases so because we do not have horizontal scaling AWS has basically um a concept of read replicas so in the database which you create you can create what we call R replicas of the database so the read replica is very different from the reader instances so you can create from the from the database you create read replicas and direct more read traffic to these read replicas okay so at any point in time you can promote a read replicate to a standalone database does it make sense yes sir no it's not really just a difference between the replicas read replicas and the read instances please just one more time so read reader instances are instances that are part of your your uh database deployment okay so in that deployment you will have a right instance you have a read instance but you have a constraint when it comes to databases we cannot AWS cannot elastically scale the database so the database inst which you selected when you creating the database that stays the the the that's that those resources are constant so AWS cannot elastically add more database instances and remove database instances when the resources for your databases being DEET is depleted does it make sense yes sir in in that case for you to be able to elastic to scale out your database there is the concept of read replicas so a read replica is a separate entity from a reader instance okay okay yeah is it a replic of the reader or the writer of the database itself so it's if it's a cluster it's a replica of that cluster oh but it is going to be serving only read workloads okay until you promote the read replica to a standalone database okay what do what do you mean I'm sorry so to create you can create an a different database instance from the r red so what I mean here is assume that you do not have anything concerning R replicas you're having your database cluster you have a writer instance you have reader instances then we create a read replica from from the from the database from the uh this our cluster and it's seven R workloads for example so let's say our business is in um um America then we discovered that oh we're having traffic from customer in in in Australia we want a new database to be able to serve traffic closer to Australia we can create a r replica from the database then promote that R replica carry that R replica to um um Australian region and create a new DB from there okay so create a a a standalone instance basically means in this case we a full flesh database from the r replica does it make sense yes thank you so um are you saying that the the r replica is like a copy of of the original um yes database okay yes I'm going to SC out of database deployment beyond the constraints of the database instance what does this mean it means that if I I created my initial database with two replicas of uh three um instances right two readers all this cluster has a set amount of resources set amount of CPU set amount of of of of of of memory okay and if my read application has consumed all that information but I need more resources because databases don't elastically scale like normal these two instances for for me to be able to add additional resources in for this database I can create more read replicas okay so I can create a read replica from the database and the once you when when you're creating a read replica from the database you can decide to change the instance type of the read replica so if you remember when when you're creating a database all the database instances in the cluster in in original database cluster can be of the same type but if I'm creating a read replica from that clust I can change the instance type of my re replica okay yes um so um is there another use case for the r replica that is separate from just creating it to turn it into a standalone um database yes I just said that it could self read more read traffic beyond the two reader instances so if my two reader instances my original deployments still cannot handle all the workload I can create more R replicas and I'm directing more R traffic to these R replicas okay if there is a problem I can promote my read replica itself to a standalone database I can move my read replica to another region create a stand little database there okay so so if I have my primary database instance here and this is my primary database cluster I can create a read replica but the replication between your primary and your read replica is as asynchronous okay so data is not syn synchronously replicated between your primary and your um uh uh R replica so in databases there is a concept of what we call strongly consistent and eventually consistent so strongly consistent means the data must be immediately propagated to all instances in our cluster eventual consistency means yes the data has been reaching to the primary but it takes some time before that data is updated in all the different environments okay all the different data B notes any question please slide please I'm sir could you please clarify the synchronous again ASN synchronous means data replication is immediate asychronous means data verication is there's some lagging okay so if I'm writing information in my primary instance and I'm trying to read that in uh uh uh uh information from the reader endpoints if the replication is synchronous then AWS guarantees you that the there's a very very tiny or microc seconds between right and when that data is replicated to your reader end points but if it's asychronous it means that the time you write data to the primary instance it can take a couple of seconds before that information is in the reader end point so you application should be able to to handle that so if your application is the type of application that cannot handle uh uh or that cannot support missing data then you have to be aware when you're using read replicas for um um basically scaling out uh your database architecture does it make sense yes sir so sir can you give me one one use case for this uh solution because um a simple use case I think that should help me cement this [Music] knowledge for the read replicas for the read replicas yes so it could be also for disaster recovery so let's say your business is very data conscious and they want to ensure that if something happens to the whole region of um um wherever your business is currently operating they want to be ensure that we have data available in another region so you can create um a r replica from your database and another region but in mind you there's eventual consistency meaning that once data is reading your primary database it takes some time before the data is replicated to the read replica however this comes into play because if there is a Dr situation if there's a disaster situation and all the aces in your primary region are down or they destroyed at least you would have um your data in the read replica with some seconds or minutes of lost data which is acceptable in the case of in DRC disaster scenario if it's not a critical app I guess well if even if it is a critical app then that that's better than losing everything in the primary region right true true true yeah I have a question please yes okay so um you mentioned that um one of the r replicas can be promoted to become a standalone database in another region so it let's assume that new Standalone databas is in South Africa and customers start writing to it will that information new information written to it be replicated in the us where we actually where we originally have that uh that that days before so if you if you if it is a Dr scenario it means that your us region is down right it means it's not there so your new reder has you will promote that to a new database and it functions now as a new database if you want to set up uh another Dr scario then you need to go create another read replica from the newly promoted stand alone database so that the asynchronous replication continues to take place make sense yes it does thank you yes m is there a scenario where you can have like two different databases that are like active and uh you can you can sort of direct traffic to sort of um write write data into each of them separately whilst synchronizing them and just making sure that both data are sort of intact all the time yeah there is a what you call a multi master I think d d DB supposed that you have a multi multi multimaster database cluster so you can have both both and once data is written in one of the um uh uh uh primary then it handles the synchronous replication between them so that also happen some datab cluster deployments you have a multimaster so you have two databases in two clusters in different regions and those two clusters can you can write in in in um you can write to one of the writer endpoints then it handles the replication between them so most of this scenarios would be once you once you're taking thinking about what the what is it the um coverage of the application I don't know how how wide on the wall your application is being used it could be mostly for disaster scenarios most companies would deploy that in but in my experience I haven't actually set up a multimaster um environment we have environments like that and we are having rcas and we are having um uh um reader instances which are being promoted and stuff like that I've not experienced a complete region from AWS down maybe when we face that then they will tell us we want more to master all right thank you okay at this juncture let's take a a break back in what 15 then we can handle over go our hands on thanks BR yeah so for the demo today we are creating a my SEC database we would um access our data base and perform some cruit operations in our database so the goal for our de handson today is to ensure that you're able to access the database a privately hosted database so the database will be created in private subnets then we are going to use a Bastian host to be able to reach this database so like I said there are so many tools out there for managing databases for accessing databases and PJ admin is one um what we're going to use today is DB Microsoft equ workbench is another so um you would basically use a tool which you feel comfortable with okay so that's not something that your company or something uh um dictates for you the the goal is for you to get the job done so I use dbam I also use PG admin sometimes I use psql which is a CLI version so you're running the commands directly in the um your terminal so for today we were going to do it from DB so let's start so we want to create our database or want to create our VPC using VPC and more I want a new VPC not the vpcs which you have having just so I don't trouble shoot okay so let's go to VPC and more let's call the VPC RDS demo no space like I said um most of the things which we've already about creating ppcs and stuffs as we go ahead we'll be eliminating them from the Run books because if not we you going to have 100 200 pages of wrong book at some point okay but during each session just pay attention will do it but I also expect that at this juncture you're able to do this on your own so if we say we need two VPC we need a VPC with two subnets in two Aces with four subnets in two Aces you should be able to get that done however if you face challenges that's why we are here okay Prince are you here Anita yes I'm here good so I want no gway no S3 gway um two submits can you screw up number of availability zones too that's fine no IPv6 we're using the same um C range that AWS gives us okay are we good yes sir then we can hit create no VPS is available once the VPC is available we need to create a Bastian host if you remember run book from yesterday basan host needs to se in where do you put a basan host Public Sub public subn the public subn so before we create a basan host we need the security group for the basan host so let's create a security group so so security groups either on the VPC or on the subet sorry under VPC or under ec2 so you just go to VPC go look uh drop down menu look for security group and we create a security group let's call it basan SG by SG so your description is to all s from from your home all s into Bastian whatever then you ensure you're picking the right VPC for this Security [Music] Group T you back I guess is not t my microphone might not be working oh he might be sleeping yeah I'm yeah I have problem on the internet okay so let's create our security group for our Bastian host just hold on just go up a little bit let me see something okay so ensure please ensure that this Security Group is in the VPC you just created if not you won't find it when we are about launching this basan h very important where's my pen I need a red pen so ensure that here you have the right PPC selected then we need to add an inbound room please add an inbound room SSH from everywhere like I said you would never use z z in your environment okay yes sir so do not get into your company and start opening security groups to Z Z when you we are done I think you must have come covered control tower control tower has what we call guard R and they have all this alerting once you create there environments that it's even forbidden in your playground in your test account to create a security group and open it to the Internet so there is a rule that is monitoring the whole if there are 50 accounts in the organization is monitoring every account and minute you create an account a security group wherever and you open it to the internet somebody gets an alert quick question sir why Why didn't he um named the security group as um rods SG in I'm using this I'm using this for the basan host oh okay so security basan can we create a security group once the security group is created then we can launch our Bastian host in the public subnet in one of the public Subnet in our environment let's be first place except somebody is facing a problem so quick question sir sure why did we do it this way why did we do it through the VPC and not through the ec2 through the Bastion host itself why did we do what this where creating the security group I mean I see that um either way works it's fine okay I all right sir a quick question was there an outbound rule when we were creating that security by default it it would keep all outbound so all outbound would be uh enabled so anything so that anything that comes in can go out can we use the KE of yesterday yes you can use mind you I want the um what is it called P keep yeah M was at people are actually following you just going the assumption is at this juncture you should be able to please if you if you're not able to create vpcs and basan hes at this point please take some time I understand our schedules please take some time to go through those videos and practice that okay trust me you can sit here 500 times and watching what we do or and you still don't get it playing with the console is what actually enables you to master some of those things you will sit in an interview and they ask you a question and you will just be visualizing the console and that's how you're answering the questions yes sir yeah use is there somebody facing in any issue any issue and if somebody facing a problem Lea do you have to uh show something to someone if not then we can create our Bastian host can I see your network settings good yeah so in the network settings please ensure we are using the VPC for today ads demo VPC it's named by lunard you Ensure it is in the public subnet anyone is fine you ensure that auto assigned public IP is enabled we select the security group we just created y so I think that should be all that should be all is everybody here hold on making sure so ensure you're using the right VPC ensure your VPC by by default is going to pick a Public Sub a private subnet ensure you change that to a public subnet so we can reach the the the Bastian host ensure Auto assigned public IP is enabled ensure you're attaching the right Security Group to the basan host hold on hold on are we good hold on hold on okay who is asking us to hold pleas leard um D okay this we need to be fast this is not where we okay good Miriam are you here all good yes sir I am yes all good thank you great let's create Bas host now the the phone begins so um ALDS has a concept of DB softed groups mine we cannot cover all the concepts of RDS in two hours so some of the things we'll be mentioning them as we go RDS has a concept of d sub groups which is basically you telling AWS that each time you're creating my cluster please use the subnets to place my database instances the instances we talked about okay so we need a a database subr DB subnet group in order to create a database so we go to the auds console and we create a database subnet group please let me see your account yeah you see how many instances are are you running are you having there I'm just running two two that's three you did not destroy the instances from yesterday no I never wanted to destroy okay let me just stop stop it you like to pay please let's go to Aras console Okay so we want to go to subnet groups so we scroll down here we have subnet groups so we want to create a database subnet group so in order to create a database subnet group we give the subgrade group and name so I can call it RDS DB subn group it shows will be unable to modify it like if you make err yes we just give a description so for provisioning ads for creating our RDS creating databases whatever and we want to choose this VPC where we would create the subnet group are we here is everybody here shant so we choosing a VPC we need to select the VPC which we just created so we want to create our database in a private subnet so we want to use the private subnets for the subnet group make sense yes sir I'm not able to see the VPC we just created though it's supposed to be there just look M are you good no I'm not able to see the name I put on that VC refesh I'm able to see only one okay let me refresh I'll check your availabil maybe have changed from nor reg to region sorry oh that is yeah right B hold on good I hate to lose you at this early now good now good sir is that not good or now good now good uh good so we can continue yes let's create our subnet group so you need to go to add subnets and you need to select the EES which you enabled for your VPC so the best way for you to do this is to actually look at the subnets that you have and you see where AWS placed those subnets which it created automatically so I would leave this and I just open a new tab and VPC you have some latency yeah yeah yeah yeah us H1 it's it's normally 1 a on one B but verify subit I thought we were supposed to create three subnets not no they are four there two public and there two private okay my public is 1 a 1 B the private would also be 1 a 1 B yeah yeah one B so that enables the availability zones but you need to now select the subnets unfortunately you need to look at the subnet ID here it doesn't bring the name so look at the subnets in 1 a and one b and ensure that you're picking the private subnets please be Keen here most people can get it you know pardon so we should ensure we picking the the private sub private subnet so Le hold on I want you to click here so that I can reorganizes your naming here just click here okay nine no wait is I want you to click here you're clicking somewhere else yeah that's it that's it no you're clicking somewhere else so where do you want me to click yeah where I have you have my pen yeah yeah good that's what I want sorry how many uh are we supposed to choose two two okay thank you please ensure you're selecting the private subnet do not select a private and a public subnet 34 so I your private submits here are 34 so you have one BB and db34 so one b and hold on stay here are we all together yes sir see yes sir please ensure that you're having private sness if not we are going to do a lot of debuging or you would not be able to continue at some point we're selecting just the private no no public no public because we want our databases to stay in the private subnet that's you so we don't so you you could still put them in a public though right I mean it's not yeah you could still put them on the public okay then let's go and we create our sub group once we have a softare group then we can create our database still in the RDS console just hit on create database so you see the different engine um how is it call again dbms which we're talking about so there's MySQL there's Aurora MySQL there's Aurora postgress there's postgress there's Maria DB can you scroll down a little bit there's Oracle there IBM db2 there Microsoft SQL Server so whatever database type that application needs these are the dbm stems offered by AWS that AWS supports so AWS at this moment doesn't support all the relational databases that exist out there so these are the irational databases that it supports okay okay so another case where you would have a selfhosted database in AWS which basically means you're spinning up an in instance and installing the the database engine and stuff like that is when AWS doesn't have an offering for what you need so we want select my SQL as our engine type scroll down my SQ so my SQL Community the engine version looks good let's scroll down on the template I want free tier are we here yes are we here yes I'm here good let's scroll down I keep repeating because I'm expecting whoever is facing a problem to speak up scroll down we want to give a database identifier so basically this is a name that is going to attach to your database so you can call it whatever you want so just say demo DB let's scroll down we want to create credentials for our database so you can either manage your credentials since a secret manager that's something I'm sure you've not covered you get to it when before your session is done or you self-manage it okay so if it's a self-managed database self manage credentials you can either tell a to generate a password for you or you can put in a password your own custom password so you can generate REM mind you if when you generate autogenerator password once the database is created AWS would display the passwort that was used if you do not copy it at that juncture you will need to modify the database so set manage so self manage autogenerate password please we can we chose to to just put our own password if we want yes you you can put your own password if you want okay so you then you will you will uncheck the auto generate can we put our passwords and go ahead can we go scroll down instance configurations we can leave all this as default so AWS picked uh DB instance T2 micro for our database so that's fine so go go back to the t2 mic go back up there so if you remember when we were discussing I was talking about running out of DV resources T2 micro comes with two CPUs and just one gigabyte of ram okay can see that say T3 T3 sorry T3 micro it comes with just two two virtual CPUs and one gigabyte of ram so if we are using a database like this and we are running out of resources unlike ec2s it doesn't um horizontally scale so in that case we can create replicas and read replicas and stuff like that okay sir so let's go down storage is fine we can leave our storage as default in your environment you would decide on how you want to handle storage you can increase the storage later on this is one of the advantages of using AWS because you can tell oh I want 500 gigaby they provision you 500 GB okay if it's finished or if it it's exhausted you can come back and say I need more storage and AWS will provision that for you let's go down to connectivity I want to connect this database to an ISU instance this is actually a new fature this was not here about last year so before we needed to set this up AWS is making life easier on daily basis great so we can connect to an easy to compute resource so what this does is remember I told you that you can tell your database that I want to accept connections only from the basan h and you would reference [Music] um the security group of the basan H in the security group of the database this simple check here is telling AWS that create those security groups and do all the referencing for us in the background so you will see that you'll see that happen okay so scroll down so you need to choose the is2 instance which you want to connect to this database so that ad knows which instance to attach the security group to its internet scroll down so virtual private Cloud the VPC that has a subnet Group Well if you had all the vpcs in your account if you if you had if you had created a sub group a database sub group for all those vpcs then you see all those vpcs here but because we have the databas sub group just for one VPC that single VPC is highlighted here okay so you should choose and this can go up there way choose e to instance I'm trying to find the okay this is where have use the drop down menu are we together yes sir are we together if someone is facing issues please speak up set okay so the uh the DPC with the sub group has already been selected for us so a you that after the database is created please you can change this VPC so be sure so we want to choose the subet group which we we created so scroll down you select the subnet group that you created existing subnet Group which is there sorry sorry scroll up a little bit I just want to see what you did with the connect to an ec2 computer resource what did it say it you say connect to an is resource then you will select the resource they select it yeah okay so this is what I'm saying that this would tell any to create and configure all the required security groups for that connection okay scroll down we do not want our database to be publicly available so we say no to Public Access we want to choose VPC security groups and firewall choose existing so you can leave it as choose existing but there is no we did not create a security group for this database because the the instance connect up there is going to do the job for us okay yes sir the Bastion SEC go that we created the Bastion it was for the Bastion host so that we can reach the basan host but now that we are connecting the database and that basan host AWS is going to configure the other security groups for us so basically what AWS is going to do is it's going to create two security groups attach one of the security groups to the bastain H and attach one of the security groups to the RDS and reference the security group and tell the security group of the SD of RDS that please accept connections from the security group of the basan H very similar to what we did yesterday if you remember yesterday we created two security groups for the app server the basan H and we doing that referencing so it's the same thing here but now we are telling AWS to do it for us so I'm assuming that from yesterday you understood what we did and AWS is going to do it for us now are we good yes sir just like we we creating vpcs now we are telling it to do everything for us why we always start with the hard way like I said is because once you start working there's going to be a lot of issues so they will always ping you some of you will get jobs with as experienced Engineers they would come to you that this thing is not working so the had way basically shows you the concepts and how things are linking up so you can find yourself find your way when you are face with such situation can we go together can we go are we together so we we create new securing group no just choose existing but there's no existing just leave it like that all right yeah I'm good sir good yeah I'm good good great so scroll down certificate Authority this is okay we go scroll down database Au authentication how do we want our users to authenticate into the database so there are different ways of ensuring authentication you can use am database authentication you can use password and keos authentication so this is using what we call um um IDP um ID provider yes thir party provider thir party provider for the keos authentication and stuff like that but you can use I am for I am users and roles or you can also just create the um standard normal way you name username and password and we authenticate so let's use it password authentication okay that the same like the password we created yes that's the password you created this is why you're telling here want password authentication so that's it password that will authenticate you into this database so you can keep the other settings as default so enhance monitoring this is once you check this box AWS does a lot of creates a lot of monitoring stuff for you in the background okay so so Prof if we had allowed an automatic generation of password mhm would this be where it tells us what the password is no it would tell you when the password when it creates the database so it's going to display it okay it's going to display it and you have to copy it at that point if you close that window then you will need to modify the database again before before you get a new password got you so at this juncture I think everything is configured any body lagging behind so we we check the box of enable enhance monitoring right no do not no it's going to cost you money do not do not do not this is SK start going to pick monitoring and send things to cloudwatch cloudwatch is one of the the most expensive AWS products so additional configurations we don't do anything there no we don't do anything there okay so just go up go up get open the additional configuration let's discuss some features there so database options with not so parameter groups if you remember there are some configurations which you can tune your database so it's basically with parameter groups you can tell it how what connections how many connections the database can support and stuffs like that um these are options which you would this is where you would do that configuration but for every database it already keeps default for all these values so you would can tune that here so um to be honest when I started working I did not know what parameter groups were so I actually was pulled into a call so that was I I did not even get a ticket and I had time for myself to figure I was pulled into a call and about 11 yeah 11 people in that call and we talking about parameter groups and I had no idea what we were talking about okay so basically is where you configure the different features for your settings for your your database okay please once we we are done with databases which is I think three or four classes I strongly encourage you to read DWS Bible what is that the name of a book The AWS documentation okay and play with the console you were lucky they didn't ask you to share your screen no I was lucky they didn't ask me to share my screen well peace let's go all right what going on we haded backup nothing nothing so everything so this is the backup which you talked about so enables automatic backups AWS will create this automatic backups for you if you do not want you uncheck it and no automatic backup will created but there is no scenario that you would find yourself in a project creating a database without backup it is a no-brainer okay yes sir so let's go so we talked about backup during our class we so this is where you tell AWS to create those automatic backups and the backup retention how many days do you want AWS to keep those backups so it ranges from 1 to 35 so you can tell I can say 10 days what this basically means is AWS will keep creating our backups every day and once the backup is back of the snapshot is 10 days old on the 11th day the it it it's deleted you understand so there's that this rolling window or or or or face with backup so once a backup gets it's 10 years old it's deleted the next one gets deleted just like that so you are always able to roll back if you have a situation to roll back your database to 10 10 days before or something like that okay yeah is there an option where you can archive archive it instead of um deleting them you can there you would create your manual snapshots we talked about manual snapshots when you create manual snapshots it stays in your account forever you need to go delete them with automatic backups AWS would remove them after Max 35 days okay sir let's go scroll down so the backup window is basically you're telling them how do I want my backup so here you say one day it means every day a backup is created and stuff like that so just go through this thing and you see the different options so encryptions backup replication do I want to replicate the backup that a creates to another region for a d scenario the encryption do I want to encrypt the backup so encryption is for you to encrypt the backup you need keys keys in WS is managed by another service called KMS okay I don't think we covered that already Okay so let's continue so Prof if you um if you delete your database that the backup or the Snapchat stay for a few days before um the retention period is up the backup is a separate entity once it's created it stays there so the retention window is is up before it's deleted got it okay thank you so let's go so if you enabled enhanced uh monitoring there then you'll see this lock exports so you're telling AWS that generate this locks and put them in cloudwatch locks then you start reading your credit card let's go maintenance window and all this stuff so at this juncture we can then create our database Pro for the encryption is it's a right so uh do we need to take note of the KMS ID key no we don't need AWS is managing everything in the background when you do KMS there's what we call a manage encryption and customer manage encryption customer manage encryption means I want to use my own key so AWS doesn't control the encryption so you provide the key you create your key in KMS you can also bring your own key so I want to encrypt my data using an accord which I'm bringing into AWS so it's a whole different topic which we don't want to go into right now so just leave the default okay yes sir click on the hide addons from 30 days I don't want them is somebody doing marketing here they suggesting add-ons for the for your demo for your database please let's go so if you created with um um what is it what was it autogenerate credentials right now you can get those credentials so there is this popup window that says view credential details here if you close this you would not be able to get those credentials again so you need to so it's then you copy your your password into your cbard I closed them oh did you did you generate that password yourself this is AWS generation oh okay sorry yeah I allow AWS to generate it and I close say pop up you've not copied this password I copied it [Music] already but once you close that you can find it again you will need to modify the database and that's a pain that's just time time we do not have let's go so to access this database today please I would like us to use DB so D is just another database tool which it's just another tool which you can use to create to access your database T do we download that now yes you need to download that and install in your local okay I'm I'm looking for the blink give me a minute you spell the DB Bar D then B ruin so this is the link for db. chat okay CH chat chat chat chat where's the chart at the bottom good so I sent it for Mark users I give you a simple command to if you have home rule if you have broom please mark users just use this to install D through install cast daver community that should bring D to your machine for uh Windows users please go through this so go to download wait wait wait wait wait scroll down please go back up go up up up up go to download click here yes and windows and there's a Windows installer you can click on the Windows installer they should download uh the binary into to your local then you can then run the binary and install deer m is it um binary downloaded to to your local you're using Windows right yeah so that's great Mark guys if somebody use is done with it please just thumbs up let so I know um it's downloaded but I'm looking for it so Brew install should already put that thing in your applications I got it it's in my applications that's why I love Mark should I select all stuff to be honest I haven't done this so let's all look at it don't worry prop we W hold it against you for using a mic select everything all right good it's Community audition so you know ping I'll select everything next next and install any issues with the installation if somebody has an issue please indicate so we don't leave you behind at this stage quick question sure so I can is it okay to call this uh software a dbms since it's an interface between the user and the database no this is what will basically another bridge between you and the dbms this is just a tool for you to access the dbms okay okay [Music] damn if you if you're ready please just thumb thumb up so I know those that are ready with the D already hopefully you have uh resources on your local for this these two CPU and memory and um space in your hard drive T are you good4 I'm done are you using Mar or Windows Windows okay you already have your DB running yeah good it means you have latency issues that's mine I posted the screen who's this scre window know is still experiencing problems who is facing problems Victor you good yeah I'm good um running great so it's pretty what is it where is my Eraser good here so in order for us to establish a connection to DEA you see this icon here that is like um what is it socket socket right so this is what you use to establish a new connection so we we we have a uh my Square database so we click on that to create a new connection then you select the database uh dbms which you're trying to connect to so in this case it is my SQL so you can see the different databases that DB supports so if you're using Oracle you're using SQL light if you're using postgress whatever can you just scroll down so some are great or some will not have all the features because this is the Community Edition if you want everything there there was an option for pro paid in paid licenses so you pay the pay okay I don't need it maybe you do Leonard how did you get to that page so is your DB running yes it's already just Just Launch it yes most likely by double clicking yes it's open yeah there's a pop no and here you you click on this blow thing here this is how you establish a new connection okay is everybody here if spam are we good if you're not here please indicate did you select SQL Server is that what you selected SQL Server we selected my SQL my SQL is the database which engine which we use so you need to select the right database engine if you created a SQL Server then you will select SQL Server if you created postgress then you will select post yeah it's because I saw him select seel on his own page why are you it was right there close this connection close this pleas what what is selected was SQL he he selected SQL that's why I was asking SQL is different from my SQL please click on this I already selected um SQL go back up let me see like okay this is it just go back up please I selected this one yes good and we want to establish connection to this database hold up the Bon who I have a popup that is showing me something say connection settings just click x on it then this one will pop off okay on no it will go did you read what the connect the thing was saying yes I did it was asking me for a password you wrote um all those things I've closed it I don't remember so are you here no I'm not seeing this different options did you click on this blue uh thing here yes yes I did and then I selected my SQL yes then you should be here and it popped up again asking for connection setting yes we'll set we we will give the connection setting very soon click on next finish or next next next um she she was ahead she was somewhere around here oh yes so here we we have to do two connection settings we have to give the connection to the database and we have to give the connection via the tunnel we are the basan host you remember so let's start with the connection for the database so in the server host you need to go back to the database which you created and it's going to give you a connection string go back to the database which you created click on it scroll down scroll down here endpoint and Port there's an endpoint here the endpoint which we're talking about during a work l so this is it so click copy that clip that endpoint into your clipboard and you paste it in the host on D com together now uh are we together bu yeah yes go to SSH I think for the database you can leave uh this open but I think there's a default database is it my SQL or something like that you can I think you can leave that open for password pass give the password which you copy the autogenerated password we need it here are we supposed to select SS we supposed to go we will configure SSH because we are going through the basan holes oh so we have to do the M po mean mean SSH takes you to the basan H from the basan h then we need to go to the database right yeah it's selected main right there that's what I'm yeah the main is for the database connectivity so username is not root you remember admin on the database when you created it gave you a username called admin was it cap a no was it's small a you can go back to the data on the console you will see that there should be there a small a so go to configuration the configuration tab so we should look for scroll down Master username let's it right here admin so it's small a go ahead let's go ahead go back to our D and uh that's okay that's okay bro I'm sorry can you start from beginning on this on this page can I do what can you start from beginning from uh from this page yes to get a connection you click on this how is this called Mubarak it's called Mubarak oh I'm sorry yeah that's funny how the the password what you need to you need to copy in here you need to click um a you need to click on this to establish a new connection so each time you have a new cluster and you want to establish a new connection to a cluster you need to come and create a new connection here okay so once you create you you tell dbv you want a new connection it's going to give you a page where you will select the type of connection to what database are you trying to connect to is it MyQ is it postgress is it um whatever okay then you will select the database dbms at that point do you have that page then once you select the dbms then you click on next it should take you to this page so we are on the main we are trying to configure the the connectivity so we have the host because it is postgress dbv already knows the part that postgress Isen listening on which is 3306 sorry D already knows not DPMS MH and but you need to tell um DB the host you need to tell it the username the credentials for authenticating to that database instance okay but we are going through the basted H so we would move from the main to SS sorry pause pause Prof you said post grb how did he know it was postgress it's already built into it we didn't tell it you took my SQL oh gotta okay that's what you told deiv so if your database was postgress then at that point you will select postgress okay okay I'm looking for my pen I can't find it I see my see yeah on the right top right hand so that's the oh so on This Server host what would I put there server host you need to connect uh copy the connection string from the database once the database is cre yes once the database is created if you go to the main there's what we call endpoint and Port there's a connection string there that's the host connectivity and security here can you see um um I got got it yeah please go back to D we are really late yes let's let's now we want to set set up our SS connection to the basan host so we go to SS Papa was asking how did you verify the username and password on the so the password is the password you gave when you were creating the the instance the username is what DB no U the database uh [Music] um created when he was creating the instance when you were creating uh the D instance it showed you that the admin username was admin you at that juncture you could change it to whatever you want if you want it to be Shanta you change it if you want it to be Kinsley you change it but we left it to be un mean then you gave it a password or you told the AWS to autogenerate a password for you which if it autogenerated a password it should have copied the password if not you have to modify the database before you get a new password L can you go back once so that she sees the page where she's supposed to copy it I think she may have missed that it's not there still there on the top top right no it's not there once it's close it's gone but really which one is this one then connection not closed there that's it that's it open it I finally saw mine is it open m open it Con you will see it I saw my why should I open mine okay why should you open yours yeah oh no she wanted to see just open so she can see what it looks like okay you're going to delete this at the end of today so yes after can delete no money is it only money you protect it's not your fish money okay I got it prot where money come from so mind you if you hit X yeah that's it okay so so let's go back so we want to set up SSH so it's it's saying that use SSH tunnel let's take on use SS [Music] Tel so we want to host host here is a public IP of the basan hose so the basan hose you remember we told it that we want it to have a public IP or to assign public IP so copy the public IP of your basan host to this place yeah that's right en sure you're using the right basan H I see you have so many he has too many well all of them are stopped um just one so we want the public IP of our basan h IP yeah pleas go take the public IP to the point the host the for the s h take the copy the public IP to your cboard and let's go back to D I already copy so in the host SL IP put the IP there so the port which we're using for S is 32 it already knows that how are we authenticating is it password and username no click on that and you would see the options for Keepers so click here so it's looking public key so public key means that the the key which you created the public key is already in the instance so we need to give it the private key this private key was downloaded to your local so you need to give it a p for DB to look for that private key in your local machine okay so browse it's in your downloads will go to your downloads B so you so you go to your downloads and you look for basan P I think you what's called what we called it oh my once it's done you click on open so D would populate the path to that key are we together if somebody is having issues please stop me so we won't come back here are we together just go ahead yes yes so at this juncture you can test the connection give me a minute I want to test to see if my tunnel is working so click on test tunnel connection H yes so you remember remember each time wait each time you're creating you're trying to SS into a new server this always pops up on your command line telling you that is about to add a key to the authorized key file so you need to tell it yes are you sure you want to continue the connecting yes okay okay do it again I instantiate SSS tunnel exhausted available authentication methods that's strange says time out expired then when you click on time out now when you click on okay show St um I should out connection anybody with success mine is still instantiating mine is good mine is good too it says uh the authentic fingerprint are you sure you want to continue connecting permanently added oh refused it refused hey um go back go back to if should put the username I don't I think I just oh yeah I just the username yeah go back to SSH go back to TV thank you very much I forgot to put the username it's connected so go back here where is it username so if is2 D user aha that's why thank you I think I'm I'm I'm exhausted how do you get the host IP it's the public IP of the basan H connected yep connected right away so this is telling us that D can go can reach your Bastian host okay so because we have our we can ensure that now we can reach the basan host now let's reach the database so go back to in or you you can just click on finish but wait wait wait wait oh it's already done so just confirm it's okay what were you going to say sir I haven't clicked on finish yet if you go back to main is also an option for you to test that connection so if you go back to the main tab there's an option for you to test the connection it's going to try to test to see if you can reach the database but at this juncture can you click on this just click click on the connection here on this forward arrow click on it then it's going to try to instantiate the connection you can download or you can canel it's just telling you that you wants to update some drivers yeah that's that's what it's saying the driver so download right download should I download It Go cancel download do whatever you cancel you come back download download I downloaded and it's okay it's happy now so now we are connected to our database yeah so you can see that by default database instance comes with default databases inside so in a in a database instance can you click on databases to see the yes so there's a disa database inside call CIS can you click on users so there's an some def default users is the admin user which is inside the admin user that we are connected as okay so actually most times in um your company environment you as a cloud engineer you will be the one managing access to the database okay yeah so they would they would tell you to need access to the database remember that there is a user in the database and the user we connected to this we connected with to this database is the admin users okay so that that those credentials you never pass that to a developer a developer needs access to this database you would come and uh create the user inside using sqs you can use SQL scripts to create users you can use s to also SE segregate or SE uh uh permissions within the database for for example if I have 10 databases I can create a user and I give that grander user access to just two of those databases I can gr that user just read access to two I can grant that user in database user management basically okay so where are we to create the users basically yeah you would pass you run SQL scripts and users will be created we can do that in a a workshop or something or I'll give you guys as um small demo and you do it in your group or give you the necessary articles for that so you can guide you click on that Arrow next to admin it shows a folder that says Grant so now we are connected to our database at this Jun I just want access you hey hey hey one minute please please please they recording please give me a minute yeah they're recording somebody EV what's the issue denied access like it says access denied for user admin at um did you now there could be so many reasons okay it could be that your password you're using is not does not match what is there are you sure you're using the right password I think so it's the password I autogenerated did you test the Bastian host connection and it was successful I did it should connected good so we can reach the Bastian host so it means that we cannot enter the database and the issue could be your credentials I would take a look at your situation later okay let's just continue so at this Jun I want us to just create some sample database inside the our DB inside our So Pro back to the last question that I think somebody asked the DB Now is acting as not as the dbms no it's not acting as a dbms it's the dbms is a system that is giving us all these database users inside here right now d dbis o is a bridge for us to reach here okay and what's that called what is that one called what that dbms that's giving us this information for my that's the database management system okay okay so DB is just a tool which you're using to get here okay we can we can use it DVA just gives us a nice graphical user interface for us to interact with it if you're connecting to this using uh your terminal you're still using the dbms so it's granting you the same um interface for you to interact with the databases in this database okay the databases in the your database instance okay so if we are using database from AWS do we still go through this round this is a database from AWS oh I don't understand what you mean by if we using okay for example we're using my now M and uh I think you said at the beginning that AWS offer the same Services too this is this is this is the database that is offered by AWS so if you're using if you're trying to connect to a selfhosted my SQL dbv is still going to ask you all those credentials in credentials to connect to need the host to connect to it so de doesn't care where the database is okay okay I got you awesome awesome that experience it in my mind as well D is just a a tool for us to access the database so there are other tools you can Google search there's something called PG admin PG admin is one of them there's another tool called myo workbench so there are so many tools out there for managing interacting with database or SQL Studio I think SQL Server Studio something like that so I like dbv because it I'm comfortable with it it gives me all the necessary things I want some of the tools do not like PG ad me and micro workbench I do not know if it supports as many databases as D so what if I don't like d and then you look for what you're comfortable with for what you want and then who will teach me how to use that YouTube there's YouTube thank you very much that's the different problem okay so at this juncture we want to create our um database some databases inside our cluster inside our DB instance so by default every database instance has a default database inside if this was um yes P I thought you done I wanted to say I need walking me through the download because I saw my the download my password okay we back distracted when I did not get my password so get back to you so let's just finish up so for the sake of the recording then we can today sat today contct figure out other issues so I'm trying to collect my thought anyway let's go ahead but let's create a database inside our custom so I already shared with you a sample simple sqs statements that we want to run here but I would also share them in the chart as we go one statement at a time it can now exhausted available conation methods is still facing that issue because we forgot to give a name you can yeah is it the username you me the username of the um basan H so what should I do then just write is to no just give me a minute um um Mula please click here right click on this connection right click here so edit connection edit connection this is it re connection yeah yes go to SSH I want to show him what he needs to do then this is what you need to do username easy to user who ask the question are you good no no no no what he say are you the one that said you were lost yes user no I said my problem my problem is the the exhausted uh let me let me check the message my my own is exhausted available authentication methods do you have the username here which is admin right is it2 user yeah I did I did I have it we'll take a look yours at the end okay I'm very sure you misconfigured somewhere so let's continue you can just cancel we want to run our dat create our database so click on the database string on the connection string then we click on here SQL so this would click right up there yes this would open um a window for us to pass our SQL script you see that then you can run the script which I gave you so create database this is an SQL statement basically create database and we giving it a database name okay yeah please just go to YouTube and do about their videos there bunch of them basic SQL yeah 10 minutes one hour two hours videos as you want pardon just the first line just the first line we need to do it once one line at a time say did you share it okay it's in the chat that's what I was looking for there's a semicolon there's a colum no semicolon at the end of end of that statement please ensure you copy it if not this SQL syntax is broken and you have an error so once you have that then you can click on this which is basically telling it to run something in right here so we have created a database inside our database instance so you can go back to this databases where you have databases and see if you have a new database there so remember we had the default which is called sis now we've created a new one we should be able to see it right click and refresh refresh right here refresh refresh refresh is it down okay yeah oh that's it do you refresh the whole page or no you just go out and refresh come like out click refresh so we can see that we have a new database inside and we want to create tables in the database so I'm going to pass the new script for the for this database sorry prop I missed something if you don't mind sure that syntax you put there how there is a semicolon at the end of it Ure it is there I know but how did you get to that part of your screen it doesn't let me open up a okay um what I'm saying is I understand your question give me a minute click on the this the connection screen for the database here once you highlight that here then there is something here called SQL on it yes yes yes yes and then we do open script so it would open you a window give you an area for you to pass your scripts your SQL script got you I so you paste the SQL script and you click on play the play icon there gotcha basically means run it all right once it run this is an SQL statements that tells uh this relational database to create a new database inside yeah okay okay now what where is the play icon the orange thing that looks like a triangle on the left yes yeah I still don't see my database I don't know where to refresh click on the connection string itself just right click and it would be popup menu and you look you can see where I click you can see where I click outside where this space and you see refresh refresh oh okay I got it now so let's run the new the second SQL script it's in the chart so we are trying to create a database table in this database so for that to happen we need to select the database that we want to use so we do a select it clicking on it this one right yes that's what we want on ited then you can right click and let me see you can either right let me see if there's option for right click no it's not there once you select that please click on the SQL script yeah um yeah right yes yes that will always open you new script there's an option there new script it gives you a new window for you to pass in your new script can you please point to the play um icon please this is it here any see this yeah thank you so I should paste it right so paste your SQL script inside the window which gave you mind you I need the last semicolon if not everything fails so give me a moment let me just try to explain what this is so this is defining the schema of our new database okay remember I said that for all relational databases you define the structure of your database table before you start putting data into it okay so this is a create data based table and the schema says I want a column for student ID student name student City student location and create created ad and updated ad so basically when was this database created and when was it updated when was that record created and when was it updated so I am also saying that the student ID in this database is the primary key remember we talked about primary key and this here is what tells this relational data datase that the student ID is the primary key I'm also saying that we want this primary key to be unique and the type of primary key is an integer do you people know what an integer is whole number yes number good so we are telling the type of the of the data is an integer I I want you to autoincrement the primary key so by default is going to increment it by one so each time I'm adding more data onto my database it increments is unique sub uh student ID by one so at every time t as you add students onto the database it's adding it's incrementing the student iding as you reduce it it's going to do do the reverse make sense so that's how we ensure that the primary key or the primary key is unique this is a very simple SQL script so if you want to more details and in depth there's a whole yeah I'm sure you on sqs and databases databases is a very broad topic we can't we cannot cover databases all of it in in in two weeks or three weeks okay can you run the scrip statement should we run it okay yes you can run it [Music] so if you look behind at theend bottom is telling you the statistics okay so I want to put in data into the database so the statement SQL what is it insert is the command that tells relational database to insert the data into the database so it's the good thing about SQL is like it's like English you're basically writing it as you you thinking so create database give it a database name insert into you'll remember that we call the database model student info okay so we want to insert into the table called Model student info the following data so the student name student City student location student student location plz what is it zip code sorry P is for me the values the values I'm saying Franchesca Franchesca it's in columns okay so the the the First Column which the student name will correspond to Value Franchesca the student City will correspond to T the uh ZIP code will correspond to whatever number we giving here and remember that we Define this in the schema we said that if you look at the schema from the beginning on mul um screen we are saying that the schema of the student ID should be an integer the student name should be a variable character which Maximum 25 and it should not be zero the student City should be the same not zero the student location should be an integer not zero and all that okay so this is the schema which you're defining before you start putting data into it once we have data into a relational database altering the schema becomes a very big problem there are methods of doing it you can migrate once you're doing database migration using an AWS service called database migration service you can alter the schema but always ensure that the schema confirms to you your needed data from the onet okay so let's run the part of our quick question before you run just quick so if I at this point now if I wanted to change that plz to zip code is this where I do it or later I do it later this is where you do it but but remember that if you if you do it in the schema then you need to go back to the insert statement and also change it there all right because it needs to correspond to the schema you understand what I mean y so I don't want to troubleshoot you Mr Victor no I'm not going to change it I just I'm just asking so so let's let's run this did you run uh no um sorry did you already run that I think you did you did wait you did already okay yeah yes so refresh I want to see and open the database J Tech model by students go to tables open the table so columns so you're seeing all the different schema defined here okay so can we run the last part what was that so if this was a postgress it actually going to display a name there say schemas for you to go see and you will look you will find the schema name something like that mine came up with an error well I'm sure your script has a problem uh the insert script it says you can't find a database mine too okay you need to select the database let's go back go back to um database JJ Tech model student info select that select the table and I want you to highlight this table model students info so from the UI this is how we we do it if you if you were running this from the CLI yeah there is already um a command that will tell it where to do that so select that before you run before you you you you create the SQL window to do that to run the new script thank you yeah yes new script do we have that yes sir so now all what we've done we want to see it yes with another SQL script I shared it in the chat is really annoy pardon sorry that was me talking a lot I said this is really nice so basically this tell statement is saying that select from so select star means everything once you do a little bit of programming star always means everything so I'm saying select everything from student model info so why we are we we are still highlighting students model students info you can run a new script with the select from uh SQL quer query statement I strongly advise you guys to do look for basic SQL tutorials it would really help you and make you stress free once you start facing some things like this things like this on the job I just wanted to say it looks like it's like in a form of a hierarchy the first yes was for okay we are putting things we are creating a table inside that table we inside we are creating a database inside the database we're creating a table inside a table now we are putting data okay it's really coming together bro can you run the script now yep so now we have the table you see it yes so this table confirms to the schema which we defined from the beginning student Ida which is our key student name student CI student locations created and all the stuff and ID blank pardon is it oh I beg your pardon it's not it's one two 34 yes it's 1 2 3 4 because we said it's an integer and it should increment Auto increment it as a primary key there right so that's the primary key then I think they have even put a key on it it does it it they put the key on it it okay so the one two three at the beginning of it basically is telling you the data type it's saying that one to three is an integer the student name ABC it's um string City ABC string student location one two three basically stuff like that okay yeah we are done I was used to with the asss interface Microsoft interface but this just going commands all the stuff this is good I like it this is very good so at this jure we can you can kill your database if you if you want to just go to the DAT data RDS console and delete the database cluster everything would go then you kill your E2 instance the Bastian host which we created then I think everything that cost money is gone all right but if you want to play with sqs and other stuff you can leave it play with it so you get comfortable it with it yeah strongly advise once you're done you remove that okay because um I do not know if RDS is free tier I'm not so sure even though they wrot it there but some of you guys might they told you the cost of this database I think is about 15 per month so you might say that okay 15 is okay for you to learn so you can keep it if you want to keep it and learn so please run me through the the cleaning up so it's database just go to RDS select the cluster and you delete it it will delete everything plus the database and everything which we've created okay can you go to um LDS [Music] please yeah yeah I'm there I'm there so by default AWS will be creating backups so you need to uncheck so that it doesn't create all those backups because we it's not important data in here right so I want to delete the database to go to actions I can stop temporarily right you can stop temporarily but for for RDS after 7 Days AWS automatically restarts this database so it's not like is two instances that will stay stop forever after 7 days it will kickart it would restart your money starts to roll so you can just go and you click on delete please click on delete I want to show for the sake of the recording okay I don't want to delete yes delete so you see by default they telling you that create final snapshot because they're assuming that you have important data here and you are deleting it maybe you forgot to take a backup of it so because we know that there's no important data here you can uncheck this you see retain automatic backups the automatic backups that it's been creating you can check uncheck that please do that okay because you've decided to do all that a is going to tell you that you should sign a known what is it w tell him to acknowledge that upon this thing all of those things are not available again so you have to acknowledge it then it tells you to type delete me then you can delete then delete the database okay deleted without unchecking them if you delete without unche checking it during the database deletion AWS is going to create a snapshot of the database and keep and it's going to retain the automatic backups so the automatic the automatic backup that it has been taking which is in your account remember that we configured a retention period so that backup will still stay there to when that retention period expires okay is it possible is it possible to delete the the backup to prevent yes it is possible it's possible to delete backups wrong okay you can you know how to delete your is two instances so I think we can finish can I stop the recording yes sir I think we're done here um if I delete the snapshot does it delete the backups the snapshot is different from the backup which backup are you talking about there's a snapshot it takes each time it's trying to delete your database so that's different from the automatic backups that it has been taking but because this database is new we do not have any automatic backup already so that is it even showing the date that you restart after the seven days if you want to stop it so Pro can I share my screen give me a minute