Pass Your Microsoft Azure Data DP-900 Exam Easy!

Microsoft DP-900 Microsoft Azure Data Fundamentals exam dumps vce, practice test questions, study guide & video training course to study and pass quickly and easily. Microsoft DP-900 Microsoft Azure Data Fundamentals exam dumps & practice test questions and answers. You need avanset vce exam simulator in order to study the Microsoft Azure Data DP-900 certification exam dumps & Microsoft Azure Data DP-900 practice test questions in vce format.

Relational Database Concepts

1. Introduction to Relational DBs

So we're moving on from the core concepts, and now we're talking specifically about relational databases. Relational databases cover around one quarter of the exam requirements. So let's get started talking about the various relational databases that Microsoft Azure offers. There are four that are relevant to this discussion. a SQL Server in a virtual machine, a SQL managed instance, an Azure SQL Database, and AzureDatabase, a managed database For MySQL, Postgres, SQL, or MariaDB, we're going to talk about each of these. So first off, we'll talk about SQL Server in a VM. What? This is the SQL Server software that you might even have running on a Windows Server or a Linux Server in your own environment, running inside a virtualized environment in Azure. So it's basically the same software installed in a Windows or Linux virtual machine in Azure. This gives you guaranteed compatibility because if you're running SQL Server 2019 on your premises, the same software is running in Azure. But this means that you manage everything. Microsoft doesn't help you at all with keeping your SQL Server up to date, with keeping your operating system up to date, with any OS upgrades or software upgrades, the backups, setting up replication, deployments, and changes; all of that is managed by you, which is no different than when you're regularly running it in your own environment. Now, the benefit of this is that first of all, the compatibility is guaranteed, and second of all, if you have that specialised knowledge that you know how to get every single drop or ounce of performance out of a Windows or Linux machine, then you can translate that knowledge into the cloud. So you have access to exactly how much memory is taken up by the SQL database, where the files are stored, the partition sizes, and everything like that. There's no limitation. And so you're not stuck with an arbitrary, like, four-terabyte limit as you might in a SQL database. You're paying for the server. You're paying for the licensing. But you can have unlimited database instances and unlimited databases in those instances, just like you're running in your own environment. Okay? So there's the benefit of that. In Azure Portal, you simply search for SQL Server and you'll see all the different versions. So the images are already created and provided. You can see SQL Server 2012. SQL Server 2017 SQL Server 2019. Now, SQL Server 2019 is a cross-platform version. It runs on both Linux and Windows. And so, you can see all the different versions here. They even have a free SQL Server, like a developer license. SQL Server also supports migrating your SQL licence into the cloud so that you're already paying for it in your own environment and not having to pay double for it in the cloud, et cetera. The next type of instance that Azure offers is called a SQL Managed Instance. So this is sort of a baby step away from SQLServer and a VM It is nearly 100% compatible; then, in a moment, we will discuss SQL Database. It is a fully managed service. So you're not managing hardware, operating systems, bug fixes, and things like that. You get to choose how many cores that you want (from 40 to 80, which is quite a powerful number) and how much storage memory you want on that machine. And so on for 32 to eight terabytes of storage, et cetera. Going into the Azure Portal, you can just search manage instance," and you can see this. I don't see this used a lot, to be honest. In real life, it doesn't appear that much on the test; you're not going to find a lot of clients. I think this exists primarily because there is somebody who might want to move to the cloud but wants Azure to take over management of that hardware and the SQL Server software. But they're not quite ready to run into the Azure SQL Database, and we'll talk about that right now. So Azure SQL Database is not 100% compatible, but it's still 98% compatible. Some of those really obscure features that maybe only a very small handful of people use are not available, like the full text indexing and maybe the control of the files and things like that. So there are some things that aren't compatible, but generally you can migrate your application from a SQL Server to an Azure SQL Database with no changes. Most of the time, you can do it with that. There are so many options, and we're not going to get too deep into provisioning SQL databases in this course. I do have a DP 200 course that goes deeper into that, as well as AzureDatabase administration and other options. But, in general, you have a plethora of provisioning options—there's even a serverless database, which means you only pay when the database is in use and it costs you nothing if the database is present but not in use. You can pay for performance or pay for hardware. Those are the DTU versus V Core options. Again, choose the number of cores and how much data storage you have for the data tables, and it can be fairly cheap, as low as $5 a month. You're not going to be able to run SQLServer in a VM for $5 a month. You're not going to be able to manage a sequel instance for $5. But this is where you start to get the advantages of the cloud. It's very simple to go into the Azure Marketplace and search Azure SQL," and finally, as I mentioned, there are other types of Azure Database that are not based on the SQL Server engine. So far, we've covered SQL Server in aVMs, SQL managed instances, and Azure SQL Database. All of those have a type of SQL Server supporting them behind the scenes. But that's not the only relational database in town. And Microsoft actually offers three other database engines to choose from. So you've got MySQL, PostgreSQL, and MariaDB. So let's say you have existing legacy applications that rely on one of those databases. It might be risky or add an additional amount of work for you to convert those databases into SQL Server. And so Microsoft is saying, you know what? We're going to offer managed versions of those databases for you so you can migrate your applications to the cloud and not have to modify them. And so if you've got WordPress, and WordPress runs MySQL in the background, Microsoft offers MySQL. Now, you can still install MySQL in a VM if that's what you want, or you can have MySQL as a web app even.But this is a third type of MySQL that you can have as a managed database. So basically, it's designed to be compatible with your existing applications. You go into the Azure marketplace, and you can search for these things. Microsoft publishes the database for those three relational databases.

2. Relational Data Structures

So now we know what services Azure offers for relational databases. But let's talk about what relational databases are and how relational data is stored. Relational databases are one of the oldest types of databases still in use today, and they've been around since, I believe, the beginning of time, and are essentially stored in the table metaphor. So it's sort of rows and columns. It's also called a column or data format, similar to an Excel spreadsheet where you have tabs and each tab has rows and columns. Those are tables with rows and columns. Now in order to create a regional database, you have to specify what's called the schema in advance. And so you define all of the names of the columns; you give them a name; you tell what format to expect, if it's strings or numbers, or if there are a lot of different formats, like decimals and integers. So you all define that in advance. And all of the data that gets inserted into relational data is then judged against the schema. And you're going to see error messages if you're missing data or if you are trying to insert data in a format that is incorrect. Okay, so what is a table? So a table in a relational database is intended to store just one type of data. All of the rows of a table should be of the same type. As an example, if you make a table, you should not include tables of cars, people, places, and orders in the same table. The goal is to separate out data types so that only data related to each other of the same type is in the same table. So, I believe we have employee tables, order tables, and product tables in the slide. Those are all separate tables. There's also a best practise to say that every table must have what's called a primary key, sometimes called the index. In SQL Server, this specifies the physical way that the data is stored on the disk. So, if you have a sequential index that starts with 12345, the data is simply appended to the table. If your index represents something like an employee ID or an order number, and those can come in in random orders, then you're going to have data that's inserted in between other data. Now this is why you so commonly see the ID field; some people even invent IDs. So you have an order ID, which makes some sense, but then the order details table has an order details ID. Maybe you have a product ID, which makes sense, but then the product inventory has a product inventory ID. So, I won't say lazy, but there is a convention for using the anID field as the primary key, a number. It makes the most sense. Computers can handle numbers and sort them very quickly. Here's an example. Assume you have a company that keeps their employees in an employee table. Now, the employee ID is an integer, so it's always going to be a number without decimal places, and that is the primary key. A primary key means that no two employees can have the same employee ID. So if you have Bob and you have Alice, they must have different employee IDs. Otherwise, one of these employees cannot be inserted into this table. The first name and last name—those are strings. Make sense. A phone number is a string, but not everyone has a phone number. I've worked in companies where I sat at a desk that had a phone, but the phone wasn't installed; the phone didn't have a number. No one could reach me by phone. Well, then in the company directory, my phone number would be blank. And so, that's a valid state. First name and last name are not marked as knowledge, so they are required fields. Next up, we see a department ID that is a link or a relationship to another table. So there must be a table for departments that has each of the company's departments stored in it. As an employee, you can only belong to one department, and so the departmentID is stored in the employee table. Now, notice that the department is not astringent, and notice that there's not a lot of other department-related information. It doesn't have the ID and the name of two separate fields. So one of the principles is called normalization. One of the principles of databases is that if you're going to put the ID in there, then you're going to have to go to another table to look up the information about that department. Same with the manager. Most employees, except for the CEO, have a manager. And so you can set up a hierarchical relationship. This is a foreign key to the same table. So it's called a recursive key. So you have the employee, and all employees must have a manager. And so you put the manager in there, and that's how you get an organisational hierarchical structure for that data. Finally, we have a date of birth, which is a string, and we've set that as being a unique key. If you click on that, you might realize, wait a second, this doesn't make sense. a date of birth. Two people can share the same date of birth. There's nothing unique about it. And so this is a bad and I did this on purposea bad scheme and design in terms of making a column thatis not always unique and trying to say that it's unique. Next up, we'll talk about indexes. So another concept of tables is indexing. Like I said, the primary key is the physical description of how data is going to be stored on disk. But you can nominate other columns as indexes. Indexes are used to speed up queries. So, if you want to look up employees by their manager, for example, you should create an index on the manager column, which will make it easier to look up employees by their manager. If you don't put an index there, then you'll have to look up every employee from one to the end of the table to see all the people who are part of the same manager. If you create an index, the index is stored separately. It sorts the data by manager, and then you only have to find where the manager starts, and then you can stop looking once the manager has changed. The next interesting thing is called a view. The view is like a table. So you can query a view as opposed to a table, but the view itself doesn't contain data. So a view requires a table to get its data from. It's almost like having a simplified view of a data. So you can create a view, and here's an example: you can create a view that is the view of managers. And so if you want to run a query to see all employees and the manager's first and last name in a single query, this is the type of query that you can create. Select statements from the inner join can be seen joining the same table to itself. You can create a view based on this, and then you can create simplified queries just on the manager's view anyways, hopefully getting the concept of what relational databases are: tables, rows, columns, indexes, and views. Those are the key elements of that. Later in this course, we'll talk more about sequel language and things like that.

3. Azure Relational DB Options

So let's take a look at relational databases inside of Azure, specifically. Now, within Microsoft Azure and within cloud computing in general, there are three categories of services. You may have heard of these terms, or maybe not. One is called "Infrastructure as a Service," and it's abbreviated as IaaS or IAS. Infrastructure as a Service (IaaS) is when a cloud computing provider provides you with something similar to what you already have in your environment. So they give you a virtual machine similar to a Windows server or a Linux server. They give you a network. They give you a firewall. Those things are infrastructure, and they're just offering it to you. The service part is a rental. You're paying by the hour, by the month, for access to virtual machines, virtual network storage, etc. platform as a service, abbreviated as PaaS. PaaS is when the cloud provider, in this case, Azure, is in control of the hardware. And you don't even have access to the behind-the-scenes the scenes'hardware.You don't even know how many CPUs are involved, what kind of disc it's running on, or things like that. Basically, it's abstracted for you. Platforms as a Service include services such as Azure SQL Database. We'll see in a second that you must then create an Azure App. Service is another example. It's not quite ready for the end user to use. You have to actually make it into something. It's a platform. And finally, the most familiar one is SaaS (Software as a Service). And this is where you can take something provided by Azure, and they are providing it to you, and you can use it. And there's no platform. You're not adding to it. You're not building up software. So, for instance, the Azure Portal itself is SaaS. Right? It's a ready-to-use software application running in the cloud. So let's relate that to our database discussion. So we talked earlier about SQL Server in a VM, and we saw that you can actually go into the Azure Marketplace and choose from any number of operating systems, any number of SQL Server versions, some of them going quite far back up to 2012, 17, 1619, et cetera. Again. It's cross platform.Some of the most recent versions are on Windows and Linux. You can go back as far as 2008 if you need to have an older version of SQL Server running. And then there are also the different tiers, like the SQL Servers offered in an enterprise tier, the standard tier, the free tier, and even the web tier. So, depending on your needs and the features that SQL Server offers, you can choose. As previously stated, the benefit of using your database in SQL Server and a VM is that it is guaranteed to be identical. So you can migrate from your on-premises SQL Server into a VM, and you're guaranteed not to have to make any coding changes. There's no retraining. You use the same tools. You use SQL Server Management Studio. However, you deployed code into your old environment. It's the same way you deploy into your new environment, really. You're just changing the connection string and making sure that the security settings and the firewalls are set up. So that's the advantage of SQL Server in a VM, and again, that's effectively infrastructure as a service effectively.Moving a little higher up the chain, we now have Platform as a service. That's an Azure SQL database. As we saw before, you can just go into the Marketplace and choose SQL Database. The SQL Database does have a SQL Server Engine, so it is mostly compatible. Again, I don't know the exact percentage, but it's really only the lowest level code, like I said, full text indexing or import XML statements or some of those types of things that are very specific, changing the collision to a different language, et cetera. As a result, SQL Server Engineered is used to work from the cloud. So if you're looking for more of a cloud,first set up, you're running off of the oldlegacy system into a cloud native environment. SQL State has prepared the foundation for you. It is easy to scale, so you can scale up and scale down as your needs require. So expanding into larger plans doesn't even bring the server down. So if you want to grow from four CPUs to eight CPUs, you do that, and the users don't even notice. And, because your SQL Database has cloud-specific tools, there are advisers who monitor your data usage and will make recommendations on new indexes to add, indexes to drop, and so on. There's a difference in SQL databases. You can either get a single database, where you basically specify the number of CPUs in the memory for that database, or you can have what's called an elastic database, which gives you a pool of resources. And many databases can draw from the same set of resources. Elastic databases are great when you do have spikes in traffic, but they don't affect all your databases at the same time. And it allows you to have a larger pool of resources available to handle those spikes without having to overpay for them. Whereas if you have single databases, you're provisioning the size in advance, and so generally you're going to want to overprovision for those individual databases to handle spikes in traffic. So the advantage of Azure SQL databases is again that it's mostly compatible, you can scale easily, and it's a cloud approach, so a lot of people are really attracted to that. We talked a little bit about SQL DataWarehouse and sign ups analytics is now called. And basically this is big-data analytics, right? So this is a database that's designed for large amounts of data. One of the downsides to relational databases in general—we'll talk about this when we get into the non-relational section What's more is that when you're talking about running queries on terabytes of data, sometimes it takes forever. I had an 800GB database that was trying to run a complex query with some joins and group buys and such. It can take 45 minutes and a timeout. SQL data warehouse designed for big data Well, it's an evolution. The signups part took the SQLData warehouse and added to it. And so Microsoft's calling it evolution. You can have it preprovisioned, where you've got a data warehouse sitting there with your data, or there is a serverless option that's something that's being added to a lot of things. It is specifically designed for your reports, and so it can handle those queries that sometimes Sequel Server chokes on. It handles that. Finally, we talked about the Azure database for My SQL for PostgreSQL or MariaDB, and it's just a managed option that they're providing. You can keep your existing solution—WordPress or whatever else uses those databases—and move them to the cloud without having to manage them yourself. SQL managed instance we talked about ina couple of videos ago as well. Again, I don't think it's very popular; it's not common in my view. But if you do want to migrate to the cloud and you don't want to be responsible for running server patches, you'll need to keep up with security releases. Let Microsoft handle that aspect of it. But you have what is basically SQL Server ina VM that is being managed by Microsoft. It's called a managed instance.

Manage Relational Databases

1. Create an Azure SQL Database

So we're still talking about relational databases. Specifically, we're talking about managing relational databases, and the first topic in terms of that is how to create a database known as Provisioning and Deployment. Now, the DP 900 exam is still a foundation-level exam, which means it's quite theoretical and you're really just understanding concepts here. But in order to explain the creation of databases, I am going to switch over to the Azure Portal, and then I'll just quickly take you through the creation of an AzureSQL database so that you can see the basic concepts. Now keep in mind that for the exam, you do not need to know how to create an Azure SQL Database for the exam.You're not going to be asked to do that, and you're not going to be asked for details of that. But I think it's a good learning process to go through the creation of one just so that we get a sense of how to create a database. So I switched over to the portal. That's ched over to the We really haven't gone through the creation of a free account or how to get yourself an account. But like I said for this video, let's just view it in action, and you can learn something from that experience. So I'm in the Azure Portal. This is the home screen, and there's an acriative resource link right on the home screen. I can go down to the database category and see a limited set of SQL manage instances for the databases. At the top, the sequel database is in there: SQL Data Warehouse, now called Signs Analytics. The three non-SQL server relational databases are in there as well. And we even have Cosmos DB, which is a nonrelational database we'll talk about in the next section. So let's go into the SQL database and create it for the first time. SQL Database now has a wizard-style interface. You've got these four tabs and a review screen. You choose where you want the subscription to be created. You do need to place it somewhere. So again, this is almost a folder structure or an organisational structure within your portal. so that all your resources for this database are all in the same resource group. Now, there are two types of databases: relational databases and SQL databases. There is the server, and then there's the database name on that server. So, if you're familiar with SQL Server running in Windows or Linux, you have your server as a machine with your database running on it. So let's create a new server. We have to give it a name. I like to give servers my initials with their name. So I'm going to say AZ at SJDNUDB, and you'll see it's fully qualified. It has database windows. Net. I need a user ID and password. Passwords must match. We do need to find a physicallocation for that which region we want. And I'm going to accept the default. So we've sort of set up in this little blade here. It isn't a virtual machine, but it is a representation of a server with a fully qualified name, an administrator, user, and password. I'm going to say, "Okay, and we do need to create a database." On top of that, I'm going to call that DB1, which is just a name that I can give it. I'm not going to put it in a pool. Now, I can choose the type of server that the server is running on. and it can be quite complicated. Microsoft actually gives us two different ways of choosing servers. One is classified as a basic standard premium metaphor. And so I can say a standard server has 20 DTUs. DTUs are a relative performance unit, so we know that ten is half as powerful as 2040, is twice as powerful as 2100, is five times as powerful as 20, etc. So we can have this all in one metric, or we can choose the server and the memory sort of separately. So we can say we need a six-core server and we need 200 GB for storing data. And we can select them individually or as a package with the basic standard premium option. That's going to be up to you. Now, you can see the pricing on this. A six-core 200 gigabyte server is $1,480 a month, which is just over $1,000. so it is quite pricey. I'm going to go back to a basic, standard premium. We can get started with the fundamentals for as little as $5 US per month. Right? That's pretty cool. Now this is charged by the minute, so if I just start this up for a few minutes and shut it down, it's not going to cost me all that much. Now, servers, they used to be just public in terms ofit had a public URL and it had a firewall. Now you can actually get your server attached to a virtual network. And that means that there is basically no public endpoint that nobody can access, even if they have the credentials. I do have to create. If I make this available to the public, which I intend to do, other Azure services, as well as my own IP address, will be able to access it. There are ways of getting sample data in there, starting up as blank, or uploading backups. And whether we want advanced security tagging, I can say review, and I can see all of the requirements that I've selected and hit create. And if I do that, and we thought we did it in less than 5 minutes, I'll have a server, a sequel database server in the cloud, for which I'll be charged by the minute. So I'm going to do that to see how long it takes. We've created the server. Let's look at that. That's probably not the one. It took around 51 seconds to create the server and then the database on top of that. Remember, we're using some sample data—50 seconds, so less than 2 minutes to have a basic SQL database at our disposal. Our deployment is complete. I should be able to click this, and it will take me there. So this is the server aspect, and we can sort of see the server only has one database on it. We are charged at the database level. So if we were to add a second database, we would basically incur another $5 monthly charge. The purpose of this is just to show you how easy it is to create it. We are given a server name fully qualified. Because I set this up to be public and I whitelisted my own IP address, I can use SQL Server Management Studio at this point to manage the server. So I have SQL Server Management Studio running on my local machine. I can fill in the information that I set for the name of the server, the administrator user, and the administrator password, and if I say connect, it connects to it. Again, I am whitelisted. No one else can connect to this. and I can see DB One. DB One has got sample data, and it's the North Wind database. I believe we can see customer products and sales orders, and this is all manageable from SQL Management Studio. So it works, looks, and acts just like a regular SQL Server. But it is an Azure SQL Database in the cloud, and this is going to cost me about $5 a month if I need to keep the server running.

2. Use ARM Templates to Manage SQL Databases

Alright, so in the last video, we saw that within about 2 minutes, we can have a sequel database created. It probably took us about 5 minutes to run through the portal and the wizard, and it actually took 2 minutes for Azure to create it. As you can see here, it's still running. So at this point, for $6 a month, it's still charging me. But let's show you another way of doing a deployment. So what you might not know is that when you actually do something through the portal, it's actually translating your request into an API that is sent through what's called a Rest API. And there's a text format or a JSON format that is used to translate my commands into Azure, and that's called Azure Resource Manager, or Arm. So you've probably heard of these Arm templates. So arm templates are basically what I'm going to show you here. Now I can actually see the Arm templates that were created for the request I just made. So I'm going to go back up to the resource group level. Remember I said a resource group is a collection of resources? Now it's showing me two of them. If I were to unclick "show hidden," it would show me three, which is the master database. If you're at all familiar with SQL Server, there is a master database as well as the user-generated database. And we can go into the deployment section here at the resource group level. And we can see, looking back in time, the deployments that I would have done. So if this is the only deployment I did, it took a minute and 43 seconds. I can actually go into that deployment, and I can see the scripts. So by clicking on "template," it's taking me to the actual template that was created at the time that I went through the wizard. So you can see, there are five resources that were created. One is a storage account; there is a sequel server; a couple of policies; an IP address; or a private endpoint. Under the server, there's a database, et cetera. So there are a bunch of resources in here. So let's go into the ARM template. Really briefly, and I apologise if it's hard to read, I can actually download this as a file, and we can look at this in a text editor. But I think my point here is that because this is DP 900, you do not have to know how this all technically works. You have to know that the concept is that whatever I did in the portal got translated into this set of commands. It's a JSON template. and this is passed to an API that executed that command. So we can see things like Isaid, storage accounts, SQL servers, policies, and so on in here. There's also a separate file for parameters, so we can see the test user that I created. Now, it's important to note that it does not pass in the password. None of these Arm template resources will store your password for you. That's a basic security feature right there. So I could download this to my computer, modify this text file, and redeploy it again to get another server. So let's say I did have the need to create 20 copies of the database that I just created. For whatever reason, running through the portal 20 times is not only time-consuming, it's also liable to have an error. Humans make mistakes. But I can basically take the template and the parameters that Azure created for me, modify them to my own needs, redeploy this template, and get 19 or 20 copies of this database. So this is an example of what you could use templated deployment for. In order to run this Arm template, you need to execute some programming commands, and that will pass the template into Azure to execute. Now, you can store these. Let's say this SQL Server is so great for you that you can store it in a library, and maybe in a few months you'll come back to it and know exactly what you did to create the SQL database. So Arm templates are another way besides the Portal to create resources, including SQL databases.

Go to testing centre with ease on our mind when you use Microsoft Azure Data DP-900 vce exam dumps, practice test questions and answers. Microsoft DP-900 Microsoft Azure Data Fundamentals certification practice test questions and answers, study guide, exam dumps and video training course in vce format to help you study with ease. Prepare with confidence and study using Microsoft Azure Data DP-900 exam dumps & practice test questions and answers vce from ExamCollection.