Host Your MySQL Blog Database On Azure In Only Minutes!

hi I'm Nick centino and I'm a principal software engineering manager at Microsoft in this video we're going to look at hooking up my SQL in Azure to our Blazer blog engine if you're new to this series if you want to get started with the first video I'll link it right up here that's going to introduce you to this Blazer blog engine from Steven gizel personally for me I was migrating away from WordPress I was sick and tired of it and I figured Blazer would be an awesome fit and Steven had a perfect solution for me if that sounds interesting just a reminder to subscribe to the channel and check out that pin comment for my courses on dome Trin but with that said let's jump over to visual studio and see what we're going to get started with okay so I have the app settings Json file pulled up here and we can see on line 24 and 25 we have the persistence provider and then the connection string now at the time of recording this video and this might be different in the future depending on when you're watching this these are the default options that Steven's provided with his Blazer blog engine so we have a SQL light database and it's going to be in memory this way you can literally just press play when you get this repository cloned down and you should be able to run locally with a blog engine but what we're going to do is switch this over to be a MySQL instance and we're going to have a connection string that points over to Azure so in my current setup which I can run locally it's going to even point at my production database with all of that out of the way I just wanted to show you where we're going to be tweaking this stuff we're going to jump over to the Azure portal and get set up with a new database all right so once you're in your Azure portal so yes you will need an Azure subscription and an account created what you're going to want to do is search for my SQL up at the top here so you can see if I go type mysq even I can see Azure database for my SQL servers so um when I click on that I can go create a new one and then it wants us to pick between a flexible server or a WordPress and MySQL flexible server so of course we're not going with the WordPress option but if you go into Advance create you can see that there's a bit of a wizard to walk through I already have one set up but I'm just going to show you some of the options so you can see what to tweak and tune but I'm not going to prescribe what you need you will need to consider what you need for your own situations but we'll walk through these options and that way you can at least be familiar with some of the things that I was doing to start things off you're going to want to pick your subscription so I have a dedicated one for my Dev leader resources and then a resource Group from within that so you'll pick one so I would go RG Dev leader for mine you can see that it's already starting to calculate some of the cost for you right so uh yes for a lot of this stuff there is going to be a cost associated with it you can find other hosting platforms if you don't want to use Azure um I know that on AWS when I was using AWS before there's potentially some other options that might be cheaper to start with you could look outside of both Azure and AWS there's plenty of options available online that's sort of outside of the scope of this this is purely for going through Azure so next up we'll go give our server a name here so you could call it like testb or whatever you want to name it for your actual setup um and then it says that the server day must be available as well so you're going to have to figure out what is going to work for you you can pick a region for me I I am located in the US I think most of my viewer base is also located in the US uh technically I'm on the west coast but for me I just figured in the central us would be a good option but consider where your resources are and how you want this to work and then a MySQL version so 8 is the latest one at the time of recording this video again if you're in the future watching this video maybe they have different versions available but I personally would just want to pick the latest unless there was some other strong reason for not doing that before we move on this is just a quick reminder that I do have a course on C refactoring available on dome train refactoring is one of the most critical skills that you can learn as a software engineer and this helps you continue to build upon applications that already exist making sure that they can scale and have extensibility I walk you through a bunch of various techniques and give you some examples that we walk through together to see how we can apply these techniques to refactor the code check out the pin comment and the links in the description to get this course now back to the video now for the workload type um again this is really going to depend on your needs like for me like having for development or hobby projects my blog doesn't get like millions of uh requests or visitors per day right so I don't really need a lot for small to medium-sized databases if you look at the cost already right so um going from development or hobby projects from like 20 bucks a month this is in Canadian by the way uh my account I am in the US but my account was created when I was still in Canada so I should have mentioned that that isn't Canadian dollars but going from development up to Small Med medium size it's basically uh what was that so like uh $20 to 200 so like 10x right uh it's a big difference if you go to tier one business it's already even greater so uh basically uh my assumption is that if you're watching this you're probably just setting up a hobby blog or you don't get millions of visitors or something like that so this is probably a great option for you yes there is a cost right it's going to be 22 bucks Canadian at this point right here and a lot of cloud computing there's going to be cost but I'm not here again to to go tell you the the most optimal for your needs this is just in the Azure portal availability Zone uh I just put no preference when I was setting this up because I don't have a need to to specify that high availability is interesting right I think if we go to check this off you can see that the cost has uh gone up um and then this is giving you a warning here right High availability is not supported with the compute tier choice so you're going to have to go up in the compute tier so I think if we went to small or medium size and then did High availability it works but again uh it's going to add some additional cost um so you can see high availability is uh$ 24454 if we scroll down that's not um by the way when you see that cost that's not just like showing you some additional it's like a net new charge on top so if you notice I go to the estimated total for high availability it's a lot of money so again I'm just showing you this it's probably not something you need if you're watching this tutorial but uh just so that you can see uh what's there so again I'm going to go back to development or hobby projects uh authentic ation method this is for you to decide I ended up going with mySQL authentication only so we can go ahead and get a username and password set up so good type administrator can I just get away with something like this for now put a password in there right okay so we would get set up with these basic settings and then we want to go over to the next page here so this is something that I think is important especially when you're trying to test things out but when we talk about network connectivity if you're running all of your stuff in Azure you can get away with private access but I think if you're trying to test things out and you're trying to look at what's a like you know connect from your development machine especially if you're watching this and trying to run what I'm going to be doing locally you will want Public Access you're going to need to provide your own IP address there um so you can see uh we can allow Public Access allow public access to this resource through the internet using a public IP address and if we scroll down a little bit here so what you'd want to do is add your own IP address in here so you can go to what's my ip.com and I think right here even it should list your current IP address mine will be blocked out and then uh that way again for development purposes and testing things out you can connect from your own machine to this resource that means you can run the Blazer blog engine locally to try it out and be pointing at your production database if we go over to the security tab there's data encryption for me I just left mine as default I didn't change any settings here not something I needed to do we could jump over to tags I generally don't do much with the tags on any of the services that I'm using in Azure just not at the scale where that's necessary for me and then review and create we can go see all of the settings before we press go on this right so just to have a quick check I have my subscription in my Resource Group I gave it a name we can see my uh admin login name where I picked it to run I didn't pick an availability Zone I just said no preference uh High availability is off it's not available in this basic tier anyway we picked the latest MySQL version uh there's a backup retention period in 7 days so uh I Believe by default cuz mine is currently doing this once a day there's a backup and it keeps a a back up for 7 days so this is uh by default uh as far as I know so just a heads up something that you might want to go double check once you've created your resource I don't have accelerated logs on uh storage Auto growth is turned on so something to be uh mindful of I have had in the past this setting uh caused me a bit of a headache um and I've had to reach out to Azure support but with my blog this has not been the case it's a very small footprint anyway and then I do not have Geo redundancy turned on as well uh if we scroll down to the networking part connectivity Public Access yes especially for debugging if you have everything set up and you're ready to go production wise you can go back to just being private um within Azure because if you don't need to connect from the outside and you're only using Azure resources you should be good to go so uh otherwise I think we're set up here and then we would go ahead and press the create button so I have a validation failed up at the top here so I'd have to go correct something uh so if I go back to the basics uh you can see that this server name was not available so maybe if I pick something else here that works I go to review and create once again and now it's okay so there's no validation errors again we can see our total cost down here so if you're comfortable with that you want to get Rocket and rolling you can go ahead and press create now I already have a database made I'm not going to go create another one because I don't want to forget to go delete it and have some extra charge that I don't need to pay for so with that said I'm going to jump over to my resource and then we can see how we can leverage this in the Net Solution okay so with our database created I've just gone over to the overview tab here I scroll down a little bit you can see that mine's been running here everything looks all and well there are going to be some things you can check out up at the top here right so you can see your configuration your version so this is all the stuff that you've already set up so it's just if you're curious what the things are you can see that they're listed up at the top there but this is a good spot if you're ever curious and you want to get a high level overview of what's going on you can check out this page it's just the overview tab if we go back over to the networking tab again if you did forget to set up your own IP address to be able to access this stuff to make sure that you can go debug it you can go ahead and set that up in the firewall rules down here so give it a name you can see that I have light sale here this is still from when uh I needed to be able to export my WordPress information from Amazon Amazon light sale I needed to be able to have connectivity from there over to this database and then I have my home set up here I live in lywood so I have my home IP set up so that I can go debug this and connect from my house to this resource so again if you forgot to set that up you can go over to the networking tab make sure Public Access is on and then you can go ahead and configure a firewall rule for your own IP if we go down to databases we'll have a quick check here you can see that uh mine looks a little bit different than yours probably does I called my database in here production uh right so you can call it whatever you want that's when we configured it in the creation process I also have a second one here because as I mentioned I was migrating from WordPress in Amazon Amazon light sale so I just wanted to make sure that I could clone all of the tables over in a database on This Server so that's the bitnami WordPress one so I have a snapshot of everything in my old database before I got set up here so you probably will not have this that's okay and then this one that says production for me is whatever you named your database now what we're going to do is try to get the connection string from here and put it into our application so if I go over to connect you can see that I have uh my connection details here um if you want to use something like MySQL workbench if I go to expand this you can see that it has the steps to go configure this right so uh in your case you're going to have a different host name maybe if youve already changed your Porter your user name right so this information will be uh different for you but uh if you want to connect through my SQL workbench you can go ahead and do that locally if you're not familiar with what that is it's just a tool that you can run locally and then you can have a user interface to go run queries examine your database and stuff like that I personally use that for all of my own development even so uh super handy tool if you're working with my SQL okay and then the last thing we're going to look at and this is what we're going to be leveraging is that if you want to make sure we have a connection string to use with blazer this is where you want to expand the connect from your app part and then we can go make sure that we pick the ado.net one so right up here you can see that we get this connection string so we can go ahead and copy that what you're going to want to do is replace that inside the text that we have back in visual studio so jumping back over to visual studio you'll want to put this to mySQL here and the title case part is important uh it's currently not set up to be case insensitive in the Blazer blog engine by the time you're watching this that might be different but at the time of recording this uh it needs to be this casing that you see here now with your clipboard you're going to want to paste over that connection string it's going to format a little bit wonky but just uh let's hold out for one sec you can see that when I when I pasted it it put it across multiple lines if I just press undo once what we'll see is that this needs to be inside of quotes um I'm going to remove the quotes on all of this stuff here you can probably Escape them I believe so if you put slash like that uh should probably work if you wanted to do it this way so maybe we can try that out slash right the port you could go escape all this stuff that's up to you so I will let you decide on how you want to do that but you're going to want to make sure that this uh doesn't have any weird formatting issues okay so I'm going to remove the quotes so you can see that as well uh there's no spaces in any of these settings so I don't need that my username does not have spaces uh and then my password uh also does not have spaces so you can go ahead and put that into here and then for me my database name was production this is going to be whatever you had uh and then the SSL mode uh I don't have a path for a certificate so this is what mine mostly looks like then of course we get rid of that semicolon at the end there and essentially you should have your information put in here now the thing that you're going to want to keep in mind is that this is if you look on my setup I have an app settings development. Json in the solution Explorer you don't want to commit this connection string to your repository because if you have your password in there that's not what you want you don't want to have your secrets put into an app settings Json file when you go to deploy this in Azure and we'll look at this in a follow-up video we will configure these settings in the Azure portal so when it's deployed you have the correct settings again this is just to get you set up and running locally but if you did this properly and all you had to change was your uh persistence provider and your connection string at this point we should be able to press play so if I go run this now and we pull up the console window here we can see that it is running on uh port 50001 for https we should be able to go copy that if I go open it up in a browser here we should see well yours is going to look different than mine of course but we should see that you are able to load this there should not be errors coming up if you're having exceptions you're going to want to make sure that you go back and check your settings do remember the casing needs to be a capital M lowercase y capital S and then ql our lowercase as well that really matters at the time of recording this from there you should be able to go to your admin log in if you're in debug for me I'm already logged in but you should be able to create a new blog post change things up here save it and it will go to your database in the cloud so to wrap things up we looked at getting a mySQL database set up in Azure we looked at being able to connect locally to it and then from there you should be able to go run create blog posts or even create short codes and other things that will get saved into your database if you thought thought that was useful remember to subscribe to the channel and you can check out this video next when it's ready to continue on with this tutorial