How to use Azure SQL Databases | Azure Fundamentals

Key Takeaways

what's going on everybody welcome back to another video today we're going to be taking a look at SQL databases in [Music] Azure by now I think you all know how much I love SQL I think is one of the best skills for any data professional to have but using it in the cloud is a little bit different than using it on your local computer so in this lesson we're going to see how you can use a SQL database in Azure without further Ado let's jump on my screen and take a look all right so the first thing that we're going to do is we're going to come right in here into databases under the resources now we have a lot of different options in here and there's a ton of different databases that you can choose from and it kind of depends on what your company does I'm only going to be showing you the SQL databases but other popular ones are things like using my SQL or postrace sqle with flexible servers as well as things like Azure Cosmos DB they all have different use cases and they all have different ways uh that they are implemented but by far the most common the one that I've used the most in my career is sequ databases so let's come right in here and what we need to do is we need to create a SQL database and let's click on create SQL database and let's actually create it and then we'll see how we can use it so what we're going to do is come right down here to subscription we have to select our Resource Group which you should have already created let's create a database name let's call this Alex the analyst DB for database now we have to select a server but we haven't created a server so we need to create a new server and again again I'm going to call this uh we'll do ataa for Alex the analyst I'll call This Server it looks like this needs to be lowercase let's do ATA server and then we'll do a YT at the end have to make it unique there we go all right we're overcoming some hurdles here next we have to choose an authentication method we can use the Microsoft entra only authentication SQL and the entra authentication or just SQL authentication now what that means is is if you come in here and set your uh Microsoft entra admin you can set it as yourself and that does help if you're already signed into Azure you're going to be using it within Azure this can be very helpful it's actually kind of the default um method let's get out of here this is the default method if you come down here though you can also create an admin login and a password both of these have their you know place and sometimes you need to use both um and so just choose the one the authentication method that you want for that server for us I think we're just going to stick with the uh entra admin we can always change that if we want to so let's go ahead and we're going to select ourselves here going to select that and there we go let's go ahead and click okay and that should create our server and there it goes and now we need to finish creating our SQL database so do you want to use a SQL elastic pool if you look at this tool tip right here basically helps you manage your resources but we're not going to be looking at the elastic pools for our workload environment we're just going to choose development production is going to be a lot faster because if it's in a production environment you're going to want better speed better compute all these different things development is going to be a little bit slower we can also choose a cheaper database or a more budget friendly database so we don't have to uh choose what it gives us we can come in here and we can Define this so maybe you want to have a provision tier instead of serverless which I don't really recommend uh serverless is uh quite nice for scalability but we're just going to keep it at is but if you want to come in here and change some of this configuration you're free to do that uh just don't you know if you don't know what it is I wouldn't mess with it let's come back to the create SQL databases and then we have our backup storage redundency you can either do locally zone or Geo we're just going to stick with our local let's go ahead to review and create it's going to tell us our cost which is very very very low um if you don't even have the free $200 that they're giving you which will be uh which can be used for this it's going to cost you like a dollar uh for you know what we're going to be doing or maybe even like 10 cents uh I'm being honest let's go ahead and create this it's going to take a little bit of time to set up all these servers and all the databases and all those things and then once it is done we'll take a look all right so our deployment is complete you can come in here and look at some of the details we created SQL databases the server SQL Server SQL server and so all these things are ready to go let's go ahead and click on go to Resource and let's exit out of this all this information is just an overview of our SQL database now we have down here some of the more important things that we're going to be taking a look at we're not going to be diving into all of them because uh I'm just going to show you the most common way now we have configure access connect to application and start developing now in the real world when people are actually using these SQL databases and when they are getting in here and setting everything up you can do this in a few different ways one is you can connect to a mySQL database and this is a very common practice where they connect it to a database management system it could be my SQL workbench or a ton of others that are out there and you can do that by configuring it and you're going to get some of that information you're going to plug it in and connect it what we're going to be looking at is not that option although uh that is something that happens often I'm going to show you azure's tool for this and it's going to be open Azure data studio so we're going to open up Azure data Studio we're going to click on this right here and you're going to need to download the Azure data studio now I already have this so I'm going to come down here and go to Azure data studio and this should resemble a few different things it should resemble a little bit of Visual Studio code and or should resemble something like Microsoft SQL Server it's kind of a combination of both you have a search you have some notebooks that you can use different projects an Explorer Source control extensions it has a ton of stuff and so this was really popular when I was using Azure everybody used this um as well as sometimes we connected to mySQL databases or uh Microsoft SQL Server datab bases and just use those database Management systems but often times we would have everything in Azure data studio so let's come right up here we are going to connect now we have to specify our server name let's go back really quickly we're going to come right over here we're going to go back to uh this right here we need to select our server so this is our server it's ATA server YT uh we could even come in here into the server and we can just uh copy this if we want to but we're going to get that we have our server we have our Windows authentication type and so it can either be a SQL login a Windows authentication but we chose the Microsoft entra ID now right here is recognizing the analy Builder outlook.com that was for the course that I have on analyst builder for AWS and Azure we need to add in our Alex the analyst outlook.com so let's come in here and we need to sign into our account so let's go ahead and sign in and there you go your account was added successfully let's go back and there we go now we're signed in and we need to select our database now it's not popping up the database right away which should be called like Alex the analyst DB or something like that let's go ahead and try to connect and see what happens it looks like we're getting an error here I think let's actually come back here I think our server name is actually this one right here I just chose the actual server but this is the connection that we actually need to make so uh I'm actually quite certain about that let's go ahead and click on this and now it's saying our connection was denied since Deni I public network access is set to yes so this is something I was waiting to see because we need to configure this just a little bit so let's come over here and we need to go to configure access so we're going to select configure and it says public network access is disabled but we can enable this and then what we can do is we can add in our IP address we're going to add in your client IP V4 address and all we have to do is click save so now it's going to update and it's going to say okay you can access this let's not you know get too crazy and too wild here now we can go back and we're going to select this and we already have that selected so now you can see that we have our two databases we have the master which is the one that you're going to get and then we have the one that we actually created so all of that to show that you do have to configure a few things make sure you're doing it properly and now we can come down here and we can connect to it and so now right in here and come into our tables we don't have any tables but we can come into these tables and Views and all of these different things now we can actually use this so now we are connected to our resource we're connected to our server and we can actually access the databases create them do all of our quering all of our uh things that we need to do with our data and we have all these options on the left hand side now this isn't an Azure data Studio tutorial uh but there's tons of stuff that you can do in here so if you've used something Azure data Studio or if you've used Microsoft SQL Server this should seem really familiar you should feel right at home now I want you to be able to actually use this I don't just want this to be something pretty so I need to show you one other thing that you need to do let's go ahead and try to create a table here right down here we have this script create new table we can keep it new table just with the one column uh it doesn't really matter let's just say this is ready to go let's go ahead and publish these changes then we're going to come down here and we're going to update our database now we can come over here and we have a new table so we can actually uh open this up we'll select the top 1,000 and of course we don't have any data in it but uh we have a working table so now this table is being stored on a server in a cloud and this is great so if you've ever used something like Microsoft SLE server you have all these tables and databases and all these things you're working with that's how it's actually used in the real world except you'd probably see get ton more tables you have access to a bunch of different servers for different clients and different uh data that worked perfect and what you can now do is let's do crln just get a new query window available I'm going to paste in here just like this um we're already selecting our database we don't have to say use this database go I'm just you know that's what I'm used to so I'm going to keep it in there uh for any you know if you have put this in a story procedure or something like that I don't want to fail out but this is just a super simple table um and we're going to go ahead and run this looks like that should be done let's refresh or actually refresh this this table and we have this product so let's open this up and now we have data in here this are little under uh underlined in red sometimes if you do control control alt R it'll refresh it or maybe it's control shift R that's okay it'll get rid of it eventually it just doesn't recognize it uh yet but it will um so anyways we have our data in here and now we can write regular queries and so uh this isn't a SQL lesson I'm not going to show you how to write SQL but I have hundreds of other lessons and courses on how to learn SQL and so this is uh kind of the nuts and bolts of how you set everything up and this is how people actually use it so this shouldn't be too intimidating if you know how to use MySQL workbench or Microsoft SQL server and so uh that is really awesome now if we come back here we're just going to take a look at a few more things this is just within our server but we don't want to look um at our server we want to go back to our database we're within our SQL database right here we were to looked at a little bit of configuration and aure data Studio again remember if you need to uh connect it to my SQL or something like that now just within the SQL database there are a few other things that you can do one they have something called a query editor um where you can come in here and you can query off of let's say you have tables you can query off of in here I can assure you that almost nobody ever uses this almost ever uh this is not really something that people use it's there to kind of test connections sometimes so if you're just setting up a new server or a new so if you're just setting up a new server or new database or whatever it is you can kind of check to make sure it's working but you won't use this in your real work uh that it's just not doesn't make sense now let's come over here and take a look at this left- hand side there are some interesting things that I want to show you one is this Power Platform when you start working with a large amount of data you have a server and a database set up and you're using it you have tons of real data in there you're like okay now it's time to connect this well you can use things like powerbi power apps and Power automate all these things just kind of automatically integrate into it there's also Integrations but just knowing how you know these are actually used you most likely won't use them but just knowing kind of what these are and how they work you most likely won't use these too much for people like you and I powerbi is something that we'll probably use quite a bit and we most likely won't use monitoring too much but I will say I've had to come into the monitoring quite a bit over my years to debug a bunch of stuff so if a store procedure is failing if the database is failing and you know you need to figure it out you can come into the logs if you need to see how much compute how many resources you're using you can look at the metrics so there are some reasons to come in here typically though this is more it related this isn't as much of what a data analyst will typically do unless you work like I said in it um where they monitor a lot of those things to keep cost down and keep things running and you know going smoothly so I hope that that was helpful I really appreciate you guys watching if you have not already be sure to check out my full AWS and Azure course on analyst builder.com and and if you like this video be sure to like And subscribe below I will see you in the next video [Music]