How to run SQL server in a Docker container

this video was brought to you by log rocket the front-end performance monitor that record videos of user sessions along with logs and network data surfacing problems and revealing the root cause of every bug tried today a log rocket comm /yt hey everybody my name is Bryce Harris and I'm a full-stack software engineer specialized in open source software and today we're gonna be talking about docker and how to set up Microsoft sequel server to work in a docker container so to get started make sure you head over to docker calm and if you don't already click get started and then download docker Desktop for Mac or for Windows whichever one you have then also make sure you have a code editor or some sort or you can just use notepad or whatever you prefer this is what I prefer for editing code and working with doctor compose files also make sure you have for this tutorial no js' installed since we're going to write a small application to test our working Microsoft sequel server so if you haven't worked with docker before really cool technology it's basically like a lightweight virtual machine that utilizes the resources of the underlying operating system so here we have the Microsoft sequel server image you can search for other images my sequel MongoDB I mean all kinds of things you can find on there and then you can see all the feature tags that it has so if you want to use Microsoft sequel server that was the 2017 version or 2019 this one will use the 2019 latest version so let's go ahead and get into it so I'm just going to go over here and create a new directory now notice CD into that directory and I'm going to open it up in Visual Studio code you Croesus here all right so let's go ahead and create a couple new files here create an index GS let me just remove this and hide this for now and we'll create a [Music] docker compose file that'll be a yam will file so you can use docker from a docker file and run a particular doc file or you can use docker compose which basically creates an orchestration for you so I'll show you here there's a particular format we want to write this in so first thing we got to do for our docker compose files we did got to declare a version so say version 3.7 which is the latest currently and that'll just dictate kind of how this the rest of the files laid out and how it's going to interpret it and then we're gonna say services so services will be like your databases and things like that and with yanil everything's based on indentation so make sure you are indenting so we'll say sequel server DB so that's just the name of the service and then we can reference that everywhere that we're using it and then we'd say container name let's say a sequel server DB nothing crazy this makes it easy to identify and then for the image if we go back over here or our website go there it is if we go back over here we can see here is the image we want to pull so now the version we want to use or the tag is the 2019 latest so I'm just going to copy that and I come back over here type : latest so this is the image we want to use so this is basically the user account this is their image and or images here I'm a sequel server and then the tag is 2019 latest so we want to use so in addition to this we need to expose some ports so type ports and then indent - and we need to expose our local port to an internal port and within the docker container so we'll do 1433 since that's the default port so you can see here our local port mapping laps to the internal port here within the container and then we need to set the environment variables and sa password if you're wondering where I'm getting this from if you scroll down here you can see requirements it'll say accept EULA with a why s a password your strong password and SAS administrator accept ulos end-user license agreement so we'll just go ahead and set those looks like this one is default value so we don't need that one sa oops so our password just set it to password which we don't want to do in production but we'll be may be ok here we'll see accept EULA and we'll type Y perfect so make sure you have docker installed so we can type dr. - B and if you installed that docker desktop I should be able to type docker compose - be perfect so we have docker installed so from here we can say docker compose PS invalid unsupported config posts Oh typo ports there we go so we typed our post PS we see we have nothing running currently and clear that and from there what we can do is type docker compose up - D for daemon so or run in the background and if this your first time installing this image you'll see a bunch of like download arrows showing it's downloading the image from docker hub docker hub is the default you can point to other private repositories or public repositories but there's a lot more configuration outside the scope of this tutorial so that's done it's pulling it from our cache now so now we can type docker compose PS and we can see the name CA command and we see an exit code exit one so that's not good so pose logs I believe it is and we can look in here and we can see the logs and we see an error unable to set system administrator password password validation fail the password does not meet the single server policy requirements so apparently passwords not good password so maybe type super-duper password I'm gonna type docker compose down so we can stop that image so now we have nothing running so now if I go and do docker compose up - D again and let's see if it's running looks like it's up let me just check it one more time make sure it's still up perfect so it looks like we're good and you can see zero zero zero as our local host port 1433 is mapping to the internal port of 1433 so now we can clear this now let's go over to our index j s file that we had created and real quick here I'm just gonna create a small program so we get started make sure you have NPM installed perfect so any version over 5.2 should be fine for this and then we just wanna go npm install - save em SQL and you could use anything it does have to be no js' whatever you want to connect to your database with perfect so clear this so now let's go grab that package we just installed which allows the interface with our database and I'm just going to set up some basic configs to connect to our database so it's a user we're just gonna use that system administrator user password what do we set a password a superduper password good enough server say localhost and you can go to this basically this packages documentation to see what all this config stuff is I'm adding here but for times sake we'll just punch it in here and I want to connect to a database called test DB which we'll need to create and I'm just going to set some quick options here since you'll get an error if you don't set this I'm gonna set the true for now perfect come down here the tech const run equals and we'll make this an asynchronous function and we'll say try and I'm gonna say console.log and we're going to open our connection so we have to say Kant's pool equals we'll say a wait let's it's a promise pass an art config no not that connect next I'll say Const and I'll d-structure this record it's set and we'll wait our basic query here oh so a sequel dot query able to do is do a string literal I'll say select all from users so we've got to create a user's table and then we just want to console.log what comes back and then we'll just close our can actually wait here instead of closing it right away a view catch air and we just want to print out the error so we can see it I'm going to do finally we want to close our connection say pool clothes and they'll say console.log connection closed so perfect then we just need to call our run so what happens if we type node index SAS Oh misspelled async here there we go so we get a connection error failed login for user so having a problem they're connecting to our database so let's go into our database and what we want to do is type Ducker execute - IT sequel server DB that's the name we gave to our docker image and it's like bash so now we're in the bash terminal for Microsoft sequel and type LS you can see all these directories so I know where the directory is so it should be opt tools bin and then sequel CMD I type s localhost - you for the user will type sa and - key say super duper password and we're in so now I should be able to type select name from sis databases no type of go and we can see our databases here we have four so you don't actually have that database that testdb that we we set up here so we need to create that database I think you just type databases - nope ok maybe not have databases ok you actually have type sis so let's go ahead and create our database so we'll say create database test DB go so now if we do select name from that to go we see we have that test DV in there perfect so now let's say use test DB we changed our database we're currently using to that so now we can say create table users say ID we'll make an int field you'll say name say he's a bar char of 50 characters and it'll say email there's another varchar' website 255 that go that should have been created it's now let's insert into our users table type insert into users values say one side bill gates V our users name and they'll say Bill Gates at microsoft.com let go one rows affected so now if we type select all from users we have one user record coming back Bill Gates alright so I know type ctrl C to exit out and control D to finally fully exit out so now if we go back and we run our nodejs script pools not defined to have a type own here you because it's defined just let me do this that pool I'll sign it there we go it was just saying it couldn't actually see pool down here because it's within these braces let's try that again perfect so connections opened we see one user record came back of one Bill Gates email Bill Gates Microsoft so now we have our doctor composed database fully running we've exposed the ports we're connecting to it and pulling data and then disconnecting so then we type docker compose EPS we see our database service is still up and all we have to do is just within the director we have our doctor docker compose file it's just type docker compose down and then we just check it again like buzz PS and it's all cleaned up so that is the tutorial that's how you can set it up you can definitely do more complicated stuff in here as well as automatically connect and reference these different variables for services but we can get into that next time so thanks for watching and take care

Channel: LogRocket

Views: 36,583

Rating: 4.8896551 out of 5

Keywords: docker, sql, programming, coding, mysql docker, sql server

Id: RAE-VcZ3u2A

Length: 17min 25sec (1045 seconds)

Published: Wed Mar 04 2020