Step by step - Run and Connect to SQL Server in Docker

so as a developer I just want the cord I don't want to waste time setting up the bits of infrastructure for example a database that I need to support my application I just want it lay up so in this video I'm gonna show you how you can use docker to very quickly and easily spin up an instance of sequel server and connect to it and use it from your application [Music] well hello wherever you are when ever you are where mine Melbourne Australia as usual and when is it it's ten by 2019 well it's great to be back here on YouTube with you and it's great to have you here with me so thank you so as I said in today's introduction today we're going to be spinning up an instance of sequel server and docker for use in your applications so a quick video no coding but I'm one I hope you will find useful as part of your development workflow so let's not waste any more time let's get on with it okay so I'm just going to quickly go through the ingredients that you're going to need for this but you're not going to need much actually all you really need is docker running on your desktop so if you're running Windows or Mac you'll be using docker desktop if you're using Linux you'll be more likely using docker Community Edition and they're all three now optionally and I'm going to be using the S chord we're not doing any coding in this video just command line stuff but the ESCO does get a brilliant docker plugin that just allows you to see the containers you have and the images you have and all that kind of stuff rather than running and all that kind of thing so it's really useful for me doing a video on it you don't really need it I'll leave that up to you optional as well I'm calling it sequel server pools and I use sequel server so I have the management studio installed in the command-line tools installed for this video you don't actually need it because I'm going to show you how you can use the sequel command-line natively and save the container without needing any tools but that so they are optional but I'm going to show you that anyway and then a bit of a prerequisite I'm not going to go too much into the detailed theory of what docker is and how it works and all that kind of stuff if you're interested in that then you watch my previous video which is deploy a dotnet core API with docker and I cover all that stuff there other than that I think we're ready to move on okay so I'm just going to fire up a version of ocean a instance of Visual Studio chord I'm really just going to be working within the terminal here on the command line so you don't have to use Visual Studio code you can use PowerShell or your command plant or whatever the equivalent is on Linux and Mac bash shell or whatever the main reason I'm using Visual Studio code is because it has this excellent little plugin that you have to install a called docker it just gives you a great visual representation of what's happening within your system that's about it so that's why I like to use it so to get started all we're really going to do is issue a docker run command and pull down an image from the docker hub of sequel server and run it and connect into it there's not really a difference the in order to get an instance of sequel server up and running you need to pass in quite a few different flags to get it running and I'm just going to go through and explain what those are and then we're going to connect in so no surprises you type docker run and the first thing we want to pass in is actually something called an environment flag or an environment switch and need a winna pass in three but we need to in order to get the instance up and running the first one that we need to pass in is basically just accepting the Microsoft Universal licensing agreement in ula and just set that to yes we then pass on our sa password my SQL a password now sa is system administrator I can on sequel server it's the god I can't so be very careful who you give that password I would and you know in this instance were actually passing stuff in in plain text so we're you probably wouldn't want to be doing this kind of thing in a production tape scenario but for development purposes it's fine and but yes B we know that the AC account is yeah all-powerful so you generally have to pass in something relatively complex or let's say P a dollar sign dollar sign W 0 rd 2019 as the password and then we have one final environment flag to pass in this one I believe is optional and and this is really just telling the telling the container when it starts up what flavor of sequel server you want so by that I mean things like is it the expression the developer version the full version if you don't provide this flag I think it's it defaults to the developer version which we don't want we want the Express version so ms SQL head equals Express and Express is the effectively the fee Edition but I'm going to pass on our port binding again refer to my last video if you don't know what that means basically it's just saying we've got an internal port and say dock container what do we want to map externally for things to connect and so I'm the internal ports 1433 I'm just going to map it externally to the same port that's all that is and then finally we're going to just specify the image that we want to pull done we're going to use the D flag which means run it detached so all that means is when we actually do run the container instead of hogging the command prompt that we tape it and it will just kind of run in the background so make sure you get the image name right so it's MCR micro soft but compare spell at microsoft.com not mom ms SQL Server and then the version we want is everything after the call on saw in this instance it's 2017 latest and then you're gonna pull down the bin to Linux the ocean so you can obviously get Windows versions we're going to use the linux version here now just giving a quick scan over that that all looks it looks pretty much correct so hitting enter you can see that I don't have a local copy of the sequel server image I did have I did they deleted it for this video to let you see what happens and basically because I don't have a local copy of the image it will go off to docker hub and pull them live into Linux version of sequel Salwa as you can see now now again on subsequent runs it will check to see if we have a recent version of the image and we will do if we then ran this the next time so it will just use that local patience will be much much quicker so we'll just lay that finish and then it should spin up and then we're going to connect into our sequel server I'm have a bit of a look around and one specific thing in particular ok so we can see that the image has been finally downloaded we get this a looks like a good return which is kind of identifying the instance that's that's running you can see here that we've got command prompt done so we can type in stuff that is the detached flag and you can see up here in the darker plug-in that we have well first of all we have an image now in our kind of image repository the one that we pulled down it's no there and we can also see that we have a running container and that's denoted by this little green triangle you can see that this sequence set of our instance is running so what we can do now I've got Microsoft management studio installed which is the tool used to administer sequel servers I'm just going to connect into my local instance so I have sequel server Express running on natively on my desktop so that's all cool but I'm no good to additionally connect into our local doc on instance so having done this before it was available in my drop-down but if you're doing this for the first time you'll want to specify localhost followed by a comma and then port number so you can remember I specified that port mapping you need to specify the port map in here 14:33 it may because 1433 is the default well it might you may not need to put that in if actually let's try it I'll open that and let's see how we go and we're going to use sequel server authentication we're going to use the SA and then we're going to type in the passwords hope I can remember it and let's see if that works without the port allocation it does there you go I believe let's just disconnecting that if you don't know what I'm talking about I'll disconnect from that I'll reconnect I believe if you specify a port other than 1433 so you've specified port 5000 I think you would probably have to supply that in this way so comma port number so I'm just going to put that in just for completeness but it looks like you don't have to do that if you're using 1433 and you'll also notice a lot building a tick this and it doesn't remember the AC password anyway let me click remember passwords connect and yeah that what I'm just I'm learning as well I'm lining myself for your selector let's disconnect and reconnect and look I'm gonna disconnect from the other thing that's okay yes it didn't actually remember the AC password so that's obviously a security feature so we'll have to take that in again and type in password no point thinking around book it's not going to remember it no here we go we've connected now if we expand databases you'll see that all we will have is system databases which are the databases that sequel server users internally for itself that aren't all user databases now we can of course create one of three months and we'll call it test click ok and you will see that we have test database running and our docker container which is all fantastic now what about if you don't have or don't want to install something like that in Microsoft management studio and in fact if you're using Linux or Mac you don't you can't install it you have to resort to the sequel command tools so you have the sequel server just to be clear the sequel server command line tools are generally installed when you install sequel server so if you have sequel server installed you'll generally have access to them if you don't then you would need to install them separately on your local machine now you may not want to do that for various reasons so again what I'm going to show you here is how you can actually attach into a running container and basically use the command line tools within within the container so let's just minimize management studio let's bring back up our instance of Visual Studio go back over to the docker container you can see our sequence server is still running and we can actually take a darker PS to get a list of all running containers so actually just a matter of what we're seeing up here my little tool and we actually need to do that anyway because we want to get this container ID in order to connect and so just before I move on to hop showing you how you can do that you can actually in in this plugin just do attach shell and it will do exactly the spell pretty much exactly the same thing I'm just going to show you so it saves you a bit of time but I will still show you how to do it at the command prompt as well so we're going to take docker exec use the i.t flag now the IT flag just means we're going to have an interactive type session so we're going to be potentially issuing sequel command commands at this prompt and the IT flag just allows that interaction to calm we then want to type it in the container ID or just copy that and paste that in and then we need to then provide a path to what we don't actually have to do this but I'm going to do this provide a path to the toolset that we want to use so if you don't provide this you just get a vanilla command prompt which is calling me want that in this instance much it's going to specify that we want to start the shell with these command tools already running so in order to do that we specify the path to really exist opt mssql fills then SQL CMD and then we just pass on the server that we want to attach to investigate it's localhost the user ID as a and password is PA SS w0 r d 2019 and i believe actually because we're using these dollar signs that we need to encase those in single quarts so let's see if that works it does so what you actually get well it's not very obvious is this little one with our mother and well that's called an inflection that's not an inflation even all tell me above all what that that's called I think I remember but I can't recall and we can now just issue sequel commands at this command plant in the saved or secret service so imagine that we do have any other sequel server tools locally installed we have now just connected anywhere using the toolset from within the container so just just assure you are working I did previously show you this list of system databases and management studio so master model ms DB and 10 DB you can actually issue a sequel command here that will list those databases so let's just do that now so it's just select name from sys this data bases and we'll end it with a semicolon when you hit enter it doesn't do anything you think it to take go and it will then execute the sequel statement so you've put and you can see here Atlas so the databases that have gotten in fact it's actually put in test as well so it's not just the system databases is the system databases plus our test user database so I spelled it so all I wanted to cover today was spinning up an instance of sequel server in a docker container and connecting into and and using it now we didn't use it in any particularly involved way but we did attach and using sequel server management studio and we created a database and we also connect it into the container itself and man a sequel command at the command prompt now there's nothing to stop you using that container with your applications no and you would connect into it using a connection string of whatever in exactly the same way as would with a local instance of sequel server in this particular setup if you're going to run your sequel server instance in a container and you're going to run your docker API as an example in a container then we do need to do a bit more work but if you're just running the container as we are here in a running as a container and you're connecting and externally running a bit of code running locally on your machine you can connect and as we have done here using Microsoft management studio that's about it the next video I want to do is actually using something called docker compose and that is going to cover the use case where we have everything running as containers there's a bit more work involved there and they would actually connect to each other by default you have to do a bit more wiring up so that's a video for next time anyway if you found the video useful or you just enjoyed it then give it a like if you haven't subscribed please do so it's always great to have more people subscribing other than that thank you very much for joining me and I'll see you all next time

Channel: Les Jackson

Views: 30,050

Rating: 4.9552073 out of 5

Keywords: sql, sql server, sql server management studio, connect, docker, docker image, dotnetplaybook, docker run, linux, sqlcmd, les jackson, step by step, tutorial, mcr.microsoft.com/mssql/server:2017-latest-ubuntu

Id: SJAl3vOX05M

Length: 17min 14sec (1034 seconds)

Published: Sat Sep 14 2019