Azure SQL Database tutorial with an end result of a working PowerApps sql app

and Today Show we're going to an azure sequel database tutorial the idea is that you know I want to build a database so my power apps peeps can use it and so we're gonna walk through all the pieces you need to do but this tutorial is going to upon you whether you're a Azure person a sequel person or just bored on the internet looking for something fun to watch but first here's our intro hi my name is Shane Young with power apps nine-one-one those guys in today's show we're gonna look at how to use as your sequel database alright so as your sequel database to this service is this opportunity for us to get a online sequel database and you know almost free I mean you can get it as cheap as like five dollars a month up to you know any price you want to spend but for a lot of my customers are using power apps you know they're realizing that sharepoint excel those aren't the long term scalable solutions and maybe c.d.s is the end end goal but they're not quite ready to kind of bite off all that either then what you can do is you can use an azure sequel database as your back-end and then you get a lot of the scalability we get awake some some of the delegation issues that we have and it's just gonna build us a more robust app infrastructure at a pretty low cost now if you're thinking yourself whoa I am NOT a sequel DBA I'm not a technical person no worries even though I've done all this more times than ever care to admit this morning I spent hours trying to break this down into consumable pieces so that someone like yourself who's not a super technical person can do it if you are super technical great this will go faster for you but either way I'm not going to wow you with how smart I am I'm not going to try and get really technical nerdy instead we're going to just focus on here the pieces here's what we're doing here's why we're doing it so you can get your data into database out there and then start building power-ups from there cool all right I'm pretty excited to do this so let's just switch over to my desktop and so my desktop I'm gonna use a virtual machine and the reason I'm using this virtual machine is so that way I can kind of go through all the process with you again so you're not getting kind of brought in in the middle and honestly the first thing we're going to do before we jump out to the azure portal as we're going to start downloading the sequel server management studio and the reason for that is you know in some of my customers it takes like a half an hour I get this portion done so if you can kind of get this going while you're watching the rest of the video it'll leave you know faster place for you get started all right so let's do this I went to being I typed in sequel server management studio 2017 if you have an older version of sequel server management studio you may or may not be able to use it well as we go through this you'll see there are some cool tricks that the newer versions of sequel server management studio have so that's why I've been using it so I'm gonna click on this first link here and I'm gonna say download it okay so it took me about a minute to install it right I cut all that out so you have to watch me drink my tea while I was doing it so it's quick on this and so then after a few seconds I'm gonna get the user account control pop-up right so it's good I still have this desktop lock down you may or may not get this but I'm gonna say yes and then we'll go ahead and minimize Chrome while we do all this so it says a click here to install and so the nice thing is is there's not a lot to it it's just going to install it's not give us a bunch of options and stuff I will tell you that with first couple of times I did this I had pending other updates I had to reboot a couple times to kind of get those clean so if you have other Windows updates they may drive you to reboot either as this starts or before it finishes so either way just something to watch out for so I'll pause the video here they'll want this runs I'm guessing it's gonna take me a few right five or seven minutes as well so I'll be right back okay so if my math is right that took me about four and a half minutes so we'll hit close I did not have to restart and just keep in mind also if you're doing this at home well all that's happening why are you waiting on that to finish you can continue on cuz we don't need it yet but I know that was one of the things I want to get it knocked out of the way at the beginning of the video so you can definitely keep going wall that's installing you know all the process okay and also keep in mind I install sequel server management studio on my local desktop I'm gonna call app top it's not on a server or anything like that it was all on my local machine here on my case about local vm but yeah same difference alright so then now that we've got that running let's jump over and log into Azure some go over here I'm gonna open a new tab and when we go to portal dot azure calm alright and so then it's gonna ask me to pick an account unless I use different counts now if you don't have an azure account today you can still sign into all of this and when you sign in what will happen is it'll let you into Azure and then we go to create stuff I'll show you where you get a screen that you actually have to give them some credit card info and so here you can see I have two-factor authentication set up so it is asking me to authenticate and I pick up my phone real quick and press the button alright I hit approve and bammo whammo it's putting that nice little thing there right as yours all of those places well my Microsoft accounts really important to me I have lots of stuff off of it so I use two-factor authentication in my app on my phone that just says hey do you really want to let this person well again from there and I did so I let it happen I will tell Google it cannot save my password so now within the Azure portal if you go over here and you click on sequel databases you can see that I have a big ol button for create sequel database if you don't if you have a different screen and I'll pop that up right right now real quick if you have that screen then you have to walk through there and create a as your sequel or sorry as your account now the good news is your first month you get up to $200 free so you can try all this out without committing to anything the bad news is is you will have to put in a credit card to get access to a sure well that's what they do that for two reasons once make sure they charge you but to to verify you're a real person you're not a bad guy trying to spin up a counter you know go attack some servers or something and then you know spin it down real quick so just keep that in mind you do have to go through that or better yet hopefully you've got a corporate account where it's all set up and it's no big deal for you okay anyway we're gonna try John here and so what I'm gonna do is I'm gonna hit the Add button up here and so now we're just gonna walk through the process of creating all this now I also will just put out the caveat right I am NOT a sequel expert I know a lot about sequel and I've done a bunch of sequel over the years but I'm not an expert so you know if you're looking for expert tuning settings and things and the absolute most perfect way to do it might not be the video for you this video is really just going to help you get one set up so that you can start playing with it we'll worry about all the fanciness you could do the database in a different day okay so for database name we're gonna call this video customer video employees I think because that's what the database will be Ads going to ask you what subscription so if you just set one up it would show up if you have different subscriptions tied to your corporate account you can choose from there so I'm gonna use that one for resource group I am going to create a new one I'm gonna call this demo sequel and so resource groups are just a logical grouping of resources I know I'm using the words to describe words but I always do but what that means is like when we create this all these components we're about to create we're going to put them in this resource group called demo underscore sequel and so what happens then is I can manage that group holistically so for example I can set security on that group I can look at spend how much is that group costing me and I can delete that whole group after the fact and this is just because it's very easy and Azure to get a whole bunch of pieces to your solution but if I have them all in that one group but oh my god I'm done with that thing I delete the group no more running at my credit card bills okay so in a production environment I might have like production resource group dev resource group demo resource group and then I give different permissions allocate different resources different budgets there and this gives me a way to control it so that's what that is all right for select source we are going to choose a blank database if you had a sequel backup and you're trying to move out here you could choose that or if you wanted to use the good old adventure works that we've been using for demos for thousand years they give you that one as well someone choose a point database now it's like hey wait a minute before I can create your database I need to create you a database server so let's walk through that so I'm click on this I so I already have a server but we're going to create a new one some say create a server and now you have to give it a name and it has to be a global unique name because Microsoft's gonna create a DNS entry for this so we can connect to it later so we're gonna call this for us Shane's Cal's demo sequel right because that is a universally unique name right you will not be able to use that name though so don't you know you need to put your own name there and you can see a school checker is confirming that that name is truly global unique so that I can have it so it'll be mine ha ha you didn't get it before me sir admin login so I'm gonna make that Shane this is familiar with sequel right this is the equivalent when you set up a sa account when you create that first admin account that the sequel can use this is not Windows off this will be sequel off listens I need to give it a password I will use this password it's my clipboard right now and then oh ok so there you have it a password it liked also location be careful a lot of my customers this defaults to Australia unless you're from down under you probably don't want to use the Azure data center in Australia so for me it says hey East us is probably best in reality East us is pretty busy and so I live close enough that north central is actually the best data center for me but your mileage may vary just make sure you're choosing a data center that is somewhere close to you you know geographically and we know about what part of the world you're in alright so do all that swill say select so that went and provisioned us in Azure early as your sequel server to host this database that we're creating all right do we want to use sequel elastic pools no we're not trying to manage resources or any of that we do not want to memorize that password and so then it says what pricing tier do you want so it's going here and talk about this for a second so there's three different pricing tiers and basically with pricing tiers you know if each tier is cheaper or more expensive than next but with the different tiers what you're going to see is that there's a maximum size right so if I go the basic you know I can only have a database up to two gigs and I can only have five dtu's long story short dthe uses how much processing power how much any transactions can this database really handle so this would work this would work great for my video but probably not for anything you were actually coming out here to build for production go here standard so you can see the max database size and then by default it starts at ten dtu's and then you can adjust this slider to increase it for workloads and you can see my price went from fifteen dollars a month to 75 when I went up to fifty dtu's so the thing I tell you here the nice thing is is you can adjust this later you can come back in we're not gonna get into it you can look at the performance the database and see if it needed to be increased so don't be afraid you know especially trying to learn to either start with basic or start with a very small standard I'm gonna start with a basic just to save me some money premium it gets into more advanced deeper storage but not a big deal so you can see for five dollars a month yeah that's right I'm spending five dollars on you five dollars a month I'm gonna get me a database made so let's say apply the last question is what collation do you want for this database yeah the default collation is gonna work just fine I'm not gonna have the nerdy conversation with you what it is why it may or may not matter we're gonna take the default prayer thumbs up and say yay we'll put it to our dashboard so we can find it easier later and now we're gonna hit create and so it's validating that everything we did that it validated along the way it's still valid and so then now it's gonna make us a database server in a database so we'll hit pause here for a second while this runs I'm guessing two or three minutes here also and the way you can tell if it when it's done well either you'll see it here but you see the little bell over here to see the little bar guan doo-doo-doo that tells me that it's still thinking and doing some of my customers are real bad about hitting refresh then they lose you know all this nice stuff there have here so just let the screen sit here maybe go check on SMS or whatever or I get a drink like I'm going to so see you in a second okay so a little less than two minutes because I was patient the screen just refreshed automatically and brought me right in here and you can see the database so here you notice the DTU percentage right that's the thing you look out later to see if maybe that's your performance problem we're not going there we don't care we've got a database in Azure so let's now switch back over to our friend SMS and dive into connecting to this guy we're moving right along which is probably not true cuz it's been a long video already alright so let's start and then we're gonna type in sequel and then there's our sequel server management studio 17 okay they took a couple minutes to ram now probably took 30 45 seconds to load the first time that was not normal once it kind of gets in the groove of things so alright so server name so this is the name we made up over here and so because I always forget it you can see that right there on the database screen it says there's a server name you typed in Shane you dummy so let's go right there put that in first sir burning and then we're gonna change this authentication to sequel server authentication Shane was my username and then I think that my password was this ok so then after I hit connect there you can see that it says wait a minute what's going on here is that you're being blocked at the firewall right wait I don't wanna talk to my IT folks know it's not your firewall it's the azure database firewall so your database by default doesn't let anyone connect to it it's a stroke of genius what's even more genius though is if we sign in here so let's do that put in my name and then we'll put in my password I will hide it from you people though I know you're a bunch of looky-loos and so don't get another one of the sign-in requests and so I had to prove there and so then after a second it's gonna bring me back to this screen and so what's gonna happen is it's gonna do two things for me when it's gonna figure out what my IP address is so there's my current internet facing IP address I might bore that out too I don't know what you people do with that but there's my IP address and then it's gonna say you can either add just your IP or you can add a whole subnet range if you needed to add your whole network at work or something like that but the great thing is is when I click OK it updates Asher for me so we just open a hole in the firewall just for my local IP address to get to this database making the security top notch so I don't know why but that one feature like blows my mind listen if we expand to our databases over here you can see that we have video employees and so now we have what we wanted right we have an actual sequel server database running way out there in the magical cloud but we can now get in here manage it now I realize for a lot of your sequel management studio is new and scary no big deal all the things you have to do I'm gonna hold your hand we're going together here okay so the first one we're going to do is we need to take and I want to take an Excel spreadsheet and I'm gonna push the data from that because that's what I'm used to working with Excel spreadsheets right so we're gonna push the data from that into Azure so we didn't take advantage of it let me grab my spreadsheet alright so here it is or drag it over and so you can see that in my spreadsheet here I just have a normal Excel table right but what it the easier thing to do here is it turns out that getting the data from Excel a sequel is a lot easier if you're not using Excel if you take this excel and turn it into a CSV right a comma separated values the good news is that's real easy to do so I'm going to go right here I'm gonna say I want to do a file and a new workbook so there's my new blank workbook and then I'm going to switch back over to this guy I'm gonna highlight all the data I'm a right-click and say copy I'm gonna switch back to this one and so I'm gonna do a right-click and I'm not gonna do a normal pace I'm gonna say I wanna do a paste special and I just want to paste the values right I don't a comma separated value file you can't have formatting or column sizes or tables or me that we just want to or formulas we just need a bunch of text so with all my text I can expand this out you can check it out no big deal this is the data I want it though so now that I've got that I'm gonna say file and then save as and then I'm gonna throw to my desktop for a moment here so save as desktop and we will call this video data and so then save as type I'm gonna change this to I want the CSV file option right the one down here the comma delimited CSV so say save and Excel like wait a minute you're gonna lose all the cool things they do for you that's okay Excel I don't want your cool things so we're gonna close this or and close this I don't want to save any changes that guy either and then I'm going to grab the file for a second alright and we're gonna put this on the desktop of my virtual machine right my virtual machine doesn't have Excel so I had to do that kind of on my machine I cheated a little but what I want you to do after you make the CSV is open the CSV but not with Excel this time I'm gonna open it with notepad say ok and the reason I want you to do that is sometimes Excel is a little booger and Excel will actually end up messing with your data a little bit more than you want so I open it up here I just do a visual check does that look like valid comma separated data yes there's no weird spaces right there should mean tabs in there because if your data is not clean then this next part of importing is really sketchy so make sure it's clean looks good to me so we'll close this out so now what I want you to do is open the sequel import wizard sometimes you start and under recently expanded here I've got sequel 27 teen import and export data 32 bit why they don't have a 64 bit I don't know I don't care this is one we're going to use so we're gonna start this up we're gonna say next and what do you want to use for a data source and so hit the drop-down and I'm gonna do a flat file source now you're probably smart person you're like wait Shane why you just use that excel because that Excel one is a pain in your butt and I spent like 45 minutes trying to make it work in a consistent repeatable way so I could show you guys and there wasn't a consistent repeatable way it is usable I got to work for me but the CSV much more straightforward so that's why we're doing it this way so I'm going to a flat file source file name on the browse and securely on my desktop is a CSV file right change this there you go video data okay so then here you can kind of look through and see what the columns is it sees how the data does so does that look right it does you can get into advanced and you can tune like different column settings and stuff you're probably not there right there's a lot of really cool stuff that data experts can do you and me we're probably not ready for that if you're watching this video to learn how to do this you probably want to mess with me that but maybe do alright so we gonna say next so analysis well what's your destination and so for the destination we're going to use the Microsoft olee DB provider for sequel server there's other sequel ones you could probably take advantage of but I'm not going to I'm gonna go back over here I'm going to copy this again because I'm too lazy to type it in and so for my server name is that remember we want to use sequel authentication and so in my case it was Shane and then that password that I screwed up the first time I typed it in so I like that boom boom boom and so then for database now we could let it create us a database but we're going to take advantage of the video employees database that we already created because that's what I want to do in the video so say video employees say next okay so then just double-check your source right so that's the CSV and then that is the database we did write several times when I was kind of going through iterations of this I would accidentally write it use its own database names and then create a new databases I got mad confused don't let that happen all right so there's also an edit mappings here we're not going to mess with any of this but there are options available to you if you need to take advantage of them but we don't we're not gonna doing that and then we're also you can do a preview but once again your data looks good and clean alright so say next we're gonna run this package immediately so say next and let's say finish cross my fingers that it works this time sexist now I don't tell you I really am nervous was nervous they're not with this data cut it probably worked it all out but depending on what your data is what's in there what your tables look like you could be in for a little bit of a process getting this to work but for us yay it worked all right so we will close that and now if we minimize this and open our sequel management studio again go right here and expand out tables we'll see there's a table called video data right click on that table let's say select top to or select top thousand rows and so after a few seconds it will show you all the data you brought in from Excel or the first thousand rows have you had more than a thousand but there is all of my data right from my Excel workbook publish out to add your sequel all right we're almost there all right put on your brave face you gotta do something that's way above any of our pay grades right but I just couldn't find an easy way to show you how to do this so we're gonna have to do something a little complicated but that's right I have faith you can do this so what it has to happen is an order for your power apps app to work your database has to have a primary key in order if your database to have a primary key the only real easy way to do it at this point is to run a little bit of sequel the good news is if you go down the description I will just cut and paste this so you can cut and paste it as well but you're gonna have to do this okay so to run this what we're going to do is I'm going to right click on my table and then I'm going to change my and right-click on my database and then I'm going to say new query so this opens up this query builder window alright and so in the query window what I need you to do is you're going to grab this little chunk of code and you're going to paste it in and then we're going to fix it up ok so we're going to say we want to alter a table and the name of the table is DB ODOT in my case it is video data right so whatever name it says right here that table you just created that's what needs to go right here so DB Oh video data ad this is gonna add a column named my ID you can name your something different a lot of times I make it ID whatever you want but you need to have this column so leave this alone let's gonna make it an integer it's gonna say it can't be null and then it's gonna put this constraint on it and this constraint is what's going to turn it into a primary key so that as your as you add your database and power apps will be friendly with each other ok so then like that so all you had to fix was this table name got it got it once you've done that press this execute button commands completed successfully sure that was the mean scary thing I needed you to do it is done right so now if we go back over to this tab right remember our result if you had execute again you can see that now we have our key over here whoo-hoo so we are set great news you never have to come back in a sequel again you have now created a database set it up it's got all the right pieces and the puzzle you need so now we get to go do the thing that we love to do and that is build ourselves a power app right so we will minimize this and I will open up my power apps window just like so and so then here in my power apps window what are we going to do we're going to go down here to apps we're going to say create an app and then over here we have the little option and so then what you're going to need to do is you're going to say you want to do a new connection like we need to connect that database a new connection scroll down the list you should see sequel server way down here we want to connect directly right what do we want to connect directly with though we want to paste in that same key we had earlier right so I'm gonna go back over here when I copy this guy one more time we'll paste that in boom so Shane's cals demo sequel a sequel database name so what did we name our database out I don't remember right but let's just remember remember how we find this stuff so video employees so I'm gonna do a video employees like so username with Shane and then the password was this and so will say create and so the noun says what a what do you want I so we're going to choose our video data table now before we hit connect there though let's switch back over here for one second if you forget your password right there's a good chance you will I do it all the time what you can do if you need to reset that password you click on so I'm in my database I'd click on the server name this is gonna take me to the settings for my add your sequel database and so then there's a reset password here so then my count names always gonna be Shane but I can reset the password and make it you know I love fluffy bunnies or whatever it is you want to do all right so handy old trick because I forget the password a lot all right so we chose our table we're gonna hit connect and so then now power apps is gonna do that thing that it does so magically for us it is going to create a app for us a three screen app you know with display the data edit the data add records delete records all of those fun things that we need I'll say skip to this and so we can then take advantage of you know all the other tips and tricks and things you've worn along the way the power apps so one that acute might if this plus is not here right what that means is that yes as you're connected to your database it's showing you your data but it means you can't add edit or delete and the reason for that this drove me crazy the first time it happened to me is because you don't have that primary key so if for whatever reason after you create the app this isn't here something went wrong with the portion where you had to create the primary key or you're using a table that you don't mean to whatever it is this plus and all functionality goes with it only shows up for the primary key that's a pro tip save you some time and I think that does it for today right that was everything I wanted to show you to kind of get us started so now we have sequel data connections using Azure sequel in our wheelhouse if you want to use an on-prem sequel server right there is a separate video that you can check out below that talks about how you use the data gateway to connect to that so this is it we've got sequel fully in our repertoire we can now take advantage of it and one of the great things about it is sequel is faster it's more scalable and it doesn't have all those delegation issues we keep having with SharePoint cool cool all right well I think that sums it up it's like 75 degrees outside so I don't go outside and enjoy the weather you do the same and thanks have a great day hey it's me again if you got a second click the subscribe button that always keeps me making more videos or if you want to work together need some help getting your power apps going hit me up with power apps nine-one-one always happy to work together or finally if you're always looking for more videos that's probably what it is check out the power apps playlist over here and you know enjoy that alright thanks and have a great day

Channel: Shane Young

Views: 54,083

Keywords: Azure SQL Database tutorial, PowerApps Azure SQL, Azure SQL powerapps, shane young powerapps, powerapps911, sql azure tutorial for beginners, sql azure, sql management studio 2017 tutorial, powerapps sql server, powerapps azure, excel to sql, excel to sql server, excel to sql server data import, getting started with azure sql, getting started with azure sql database, powerapps azure sql database, powerapps tutorial, powerapps sql, power apps sql, power apps

Id: CO6xfbjnYwc

Length: 27min 48sec (1668 seconds)

Published: Fri Apr 13 2018