Run SQL Server in Docker!

if you run sql server on your local computer when you're developing your applications it may be worth looking into running that sql server instance in a docker container running it in a docker container gives you a few advantages a few of the main ones are one you can quickly spin that server up and down this is really nice when you're making changes and modifications to your data structures if you do make a mistake it is much easier to revert or fix that on a local copy than it would be to try to fix that in a dev or test environment where it's going to have a much larger impact two it allows you to try out new versions much quicker so for example if you want to try out the new beta version or the latest preview version it's very easy to quickly spin that up and down and three it's cross platform so you can run it on windows linux and mac now with the new m1 and m2 chips on mac there may be some caveats but this may not work quite right so if you're running an m1 or m2 mac you may have to try a few things out to get this to work and i'll show you a quick demo of how you can download the image run that image as a container and also run that container with volumes so that you can persist your data let's get started the first thing we want to do is go to the microsoft sql server docker hub page this page will show you all of the available images that you can download and how to use them for this example i'm going to use the 2019 latest image so i will copy this line then i'll go into git bash paste that in there and i'll pull this image so i've pulled this image once before so if you are downloading this you may see a slightly different output than what you see here once that download has completed we can now run the image here is a command to run that image this command was taken directly from the docker hub page so we'll run docker run and we'll pass in to accept the end user license agreement we'll pass in the sysadmin password we will tell it to run on port 1433 we will run it in detached mode we'll give it a name of sql server and then we'll pass it to image which is the image that we just downloaded so that container started up so i'll go over to docker desktop and here's the image and here is the running container so now let's go connect to sql server i'm going to do that using azure data studio azure data studio is a nice lightweight database management tool that's based off of vs code so it's very similar to vs code and it's very lightweight so to connect to it for the server we will put in localhost we'll change the authentication type to sql login the username is sa then i'll put in the password that i used and connect so you can see here i'm connected to localhost server by expand databases there's none in there so let's go ahead and create one really quick i will create a database called youtube demo then i will create a table on that called person it will have an id primary key field a first name and a last name and on that id i'll make sure to make that an identity field and then i'll insert a couple rows into this table i will add jeff test and john doe then i will select everything out of that table you can see here we have those two rows in there so that means now that we have a sql server instance running in docker we're able to connect to it we can modify the database add tables add data to it this right here will work for a lot of people you can spin up and spin down the server very quickly you can use it to run local tests to run apis against local data and things like that so any modifications that you make to this sql server will stay there as long as the container is not deleted if the container is deleted everything will be lost that's inside of this and if you restart the container you're just going to have an empty sql server again so if you want your data to persist even if the container is deleted we can do that by passing in volumes when we run the container so here i'll show that so if i stop this container and i restart it go back into azure data studio i can still select everything out of that table that data is still there however if i go in here and delete the container and reconnect now that data is gone that table doesn't exist and there's nothing here anymore so what we can do is when we run the container we can pass in some more arguments to tell it where the different volumes are for the different folders that sql server needs to save our information so here's a new command to run this docker container it's the same as before with a few extra things so i'm going to pass in three volumes all the volumes will be on my local c drive at c slash docker volumes slash sql data then inside of that there will be three folders data log and secrets and so the way that these volume commands work is you tell it the volume on your local machine and where it should map to inside the container so you'll see the data maps to var slash opt slash ms sql slash data and then a similar path for the logs and a similar path for the secrets so i'll go ahead and run this command to restart our container with our volumes attached and then i'll go back into azure data studio i will recreate the database will recreate the tables i will re-add the data and you can see if i select everything from the person table there's now data in our youtube persistent database now if i go into docker desktop if i stop and remove that container and i'll remove the old one as well and then i will restart the container with the same command that i used before passing in the volumes now you can see that it's running if i go into data studio and rerun this query you can see that there is still data in that table which is great because we've removed the container restarted it and there's still data there so i hope that was a good demo of how you can run sql server in a docker container on your local computer if you like this demo please consider liking and subscribing if you didn't like it then let me know why down below maybe next time i can do a little bit better thanks for watching and i'll catch you later

Channel: ScriptBytes

Views: 48,739

Keywords:

Id: fFpDf5si_Hw

Length: 5min 57sec (357 seconds)

Published: Sat Aug 13 2022