When was the last time you deployed code and then found an issue with the version of a stored procedure, a setting, or even a service pack? While we data folk don’t generally have as much trouble with environment issues are our developer counterparts, it can still be a big deal. Spinning up a VM may not be such a big deal anymore; however, most of us still have to request one and wait. What if you could take care of the OS and the SQL Server and not worry about setting up another development environment? Our guest today is Andrew Pruski and he talks to us about how he is using containers to support his environments and the flexibility it provides to his and his co-workers.
While the Linux containers seem to get lots of love, one unique thing about Andrew’s setup is he is running Windows containers with older versions of SQL Server. What is cool to me is there are tools out there that can help us folks running windows get up and running without having to wait on our infrastructure to upgrade to Windows server 2016. If you are using containers, I would love to hear about it. Use the hastag #sqlpodcast and let us know!
“Containers getting more and more popular with more diverse software that you can run in them.”
Listen to Learn
- What containers are and how SQL Server can live in them.
- The benefits of containers
- How Andrew gets containers to run on Windows
- Resources for more information
Andrew Pruski is a SQL Server DBA with over 5 years in database development/administration. Originally from Great Britain, he now works in Ireland. He is a regular speaker at SQLSaturday events.
Carlos: Andrew, welcome to the show.
Andrew: Hi guys! Thanks for having me.
Carlos: Yes, so one of the reasons I reached out to you is ultimately our topic today is containers. And you had put out a series of posts on your blog about containers so I wanted to have you on because I think containers are going to impact database administrators here in the coming months if not years. I heard the analogy, and most of what we’ve been hearing from the containers kind of come from the Linux side but it seems like the Windows side is starting to speed up there as well. The analogy that I heard was that containers are to environments much like VMs were to Servers. And just kind of carving them up and making them their own little islands if you will so that you can then move them around much easier. That is what kind of the container idea brings to a specific application, and in this case we are talking about SQL Server in the container. Is that like a fair representation or understanding of containers? I guess why won’t you start as off and take us through kind of that definition of what containers are and how you’ve been using them?
Andrew: Yeah, definitely we’ll say that’s a good analogy. Their own horizon especially the Linux has been out for long as more established technology but the Windows side is coming up fast. And in, I’ll just say, months or years we’re going to be looking at containers getting more and more popular. And more and more diverse with the amount of software that you can run in them and especially with Microsoft announcing support SQL Server with their containers. It’s going to be an interesting time in next year to see what happens. I’ve been looking at containers as opposed to VMs is if you have a lot of different apps that you want to run in an environment. Maybe you have a VM with only apps running but you have the same app that you want to run multiple instances of then containers can definitely be the way forward for you.
Carlos: Right, so it’s kind of that encapsulation of an application that I can then move the application around.
Andrew: Yup, definitely this apps will give you the portability features very very good. I mean, with the SQL Server I think the main advantage that you’re going to get with containers is the ability to spin up an instance at a very very short period of time. So if you need it quick throw away instances into SQL Server, do some development, or testing on containers they’re definitely be the way forward.
Carlos: Right, so talk to us about how you’ve started integrating containers into your environment?
Andrew: My QA and Dev Departments have a couple of old servers sitting in the backroom somewhere that they’ve installed hypervisor on and they’ve got access to it. They’ve constantly building DMs, testing their apps and then blowing away the VM, and they do this every single time. Each time they do that they’re also installing a local instance of SQL Server. Now, the servers they’re running on exactly high spec, we’re talking 72K disks, CPUs that are five years old, probably older so the install time for these VMs was taking up to an hour. They did installed SQL and then start restoring all that databases from baseline kept in TFS and this process wasn’t exactly reliable either. When it did fail, they look, they have tuning to error logs and what not. They’re looking at this process and always thinking, you know, there has to be a better way but what to do. I mean this process has been in place for awhile I didn’t know there are other options to give them. Until probably about this time last year I start reading about containers and what options are there. I started to look around and got into companies that are out there, Docker being the main prevalent one. I started researching into containers and how SQL can integrate with those. What I basically did was I went off and had a word with a couple of my system admins and ask if there was a spare box lying around that I could provision, wiped it, installed an OS on it and then got the containers up and running. The idea is instead of these guys building their VMs, deploying their apps and then installing SQL Server. Because they deploy their apps to their VMs but then they run, the older apps they contact to container that spun up on a separate host, our container host. And the benefits that we saw were, well, we went from an install time of 40 minutes to an hour to spinning up container in about two minutes. That’s a new instance of SQL Server with all our databases available. Bear in mind they are small databases but the same database that we’re using for the install. And so from 40 minutes to about 2 minutes, that’s a huge saving.
Carlos: Right, now does that require an application change. You mentioned instead of pointing them to the database they could point it to the container. Does that mean that the application, instead of giving them a Port 33, excuse me, 1443, 1433, yeah and server name that you’re giving them a container name now. There’s another layer there?
Andrew: No, so what we did was one of the prerequisites I have was I didn’t want them to change that process too much. All I want to do is remove the install of SQL Server and have everything else exactly the same. What we did was I’ve written a partial script that goes off and creates the container from calling the Docker daemon. And then it creates a bunch of aliases, SQL client aliases on the application service because all our applications use DNS entries to contact the apps, to contact the server so we don’t need, so instead of using DNS the client alias overwrites DNS entry with the new container port number, so the app doesn’t know anything about it. The app still thinks it’s talking to an instance of SQL when really it’s talking to a container.
Carlos: Got you.
Steve: Ok, so then to take an example like what I do is I work on a Database Health Monitor Application and part of that is testing it on lots of different versions of SQL Server everything from 2005 all the way through 2016, and different installs from Express, to Standard, to Enterprise. And to do that I have a number of VMs that I’ve spun up and then I also have stacked instances on each of those VMs. So it sounds like if I was using containers instead of having all those VMs and stacked instances I could just have a container that I spin up with that test environment. Do what I needed to do and then turn it back off again. Am I following this correctly?
Andrew: That’s exactly, yes. Instead of having your instance there you could literally just kick off, say a little script that you’ve got to say create me a new container from a saved image that I’ve got especially for this one test purpose and off you go.
Carlos: So again, the components there are, so there’s a host server, right? That host server is going to have an operating system and then I’m installing the containers on top of that. And my application could be a container and my database is a container and they can talk to each other.
Andrew: Yes, absolutely, so instead of having another layer with a hypervisor there. So instead of having the hypervisor the container is running on it that you have base OS and then you have your container daemon running that would create your containers for you which basically is the service that you call to say spin me up this container, spin me up a container.
Carlos: Very cool! And so we talked about Docker being kind of the leading technology there. It seems like as we’ve mentioned all the love was kind of going to the Linux world but you’re doing all of these in a Windows environment.
Andrew: I am, so one of the things that trip me up at the start of the year was great. So we get in some support for containers on Windows which is actually brilliant. I think Microsoft has announced that partnership with Docker way back in 2014. So we’ve been all sitting on how it’s going, come on then when it’s going to happen. But it start to progress in the recent SQL Windows Server 2016 that the community technology preview, and Windows 10 as well if you’re inside of track preview which have the Enterprise Edition you can run containers on Windows 10. And so yeah, as you said, Linux was getting well of, and we’re finally start to see some of that come true now. We’re getting the same functionality that these guys have been playing for awhile.
Carlos: Right, so now what are the apps or components that you’re using on the Windows side to get that done because it seem like you’re really just talking about from an operating system perspective it seem like Windows Server 2016 was the operating system where that finally arrived.
Andrew: Yeah, so that’s the operating system that Microsoft announced first supported feature. It is actually an in-built feature that you can switch on when you install the operating system so it’s just like anything else like .Net and things like and you’re away. You could be up and running with it in less than 10 minutes.
Steve: So when you say less than 10 minutes is that less than 10 minutes for someone who is a pro with Docker or less than 10 minutes for someone who’s just getting started.
Andrew: Well, it literally is enable the feature run. I think it’s two scripts and you’ve got your service up and running so it’s very very simple.
Steve: Ok, so then today I know if I’m working with VM to sort of make my own environment. I remote desktop to that VM, I do whatever I needed to with it and then I could shut that VM down later. But with containers then is it similar that you can connect to that container and then you have a choice of saving what you’ve done or aborting it and going back to that original state.
Andrew: Yeah, so what you can do is you initially connect to the Docker engine, Docker daemon, and you can create your own base images from templates so you have the base OS which I think Windows core on 2016. And then you can get SQL image, download that and spin up your SQL container. Then what I did was install a lot of databases, shut that container down and save that as a new image that I could then create new containers from, so instead of having to restore databases each time and to each container when I spin the container up those databases there are already in the state where I wanted them in.
Carlos: Well, that seems like a slightly or I guess I’m not sure that I quite understand that either because the databases, I thought what the databases were not actually part of the container. Now maybe the data on that database, I thought they were separate like I can move my container around and those database files are not part of the container.
Andrew: Yup, ok the portability feature of the containers is something that I hasn’t actually played with that much so I’m looking at getting into. What I’ve been mainly concern with is I want an instance of SQL up and running very quickly. The software that I’m working with which I mentioned a bit called Windox is I don’t think you get that portability functionality because it is obviously dependent on the I/O subsystem. One thing I have tried is that with the Windows containers, the ones that support on Windows 2016, the database and the SQL instance are all wrap up in the container which is I/O independent. I need to double check that I’m afraid guys but you can basically push your image up to the Docker hub, save it there and then pull it down unto a different server and the databases will all be there.
Carlos: Right, now again that’s what I’ve been seeing from these demos, all these part of what they’re talking about. It seems like from an application perspective it’s a little easier to understand and grasp. But from the database perspective it seems like there’s a little bit of leap there still.
Andrew: Yes still, again very very new, I mean obviously that’s stateless so doesn’t really matter where they live and then not depend on the I/O subsystem. But of course with SQL Server, so depend on the I/O subsystem getting you head around how it actually all working in the background.
Carlos: Right, and you also mentioned your Win Docker tool and one of the things you like about it, the reason you’re using it besides the fact we’re on Windows was it helps support previous versions of SQL Server.
Andrew: Yes, so that was another thing that sort of trip me up from the start as well when it comes to this new software that’s been available, new functionalities available on Windows Server is what you’ve got at in the Docker hub at the moment is Express Editions of 2014 and 2016 and then full edition of the next edition of SQL Server. Which I think all the features are available that are available on Windows Core, so it’s pretty fully pledge database engine there. But again it’s a higher version of SQL. I wouldn’t say anyone is using particularly right now. There’s no point in getting my QA and Dev people testing on the higher version that I have in production. We’re running Windows Server 2012 R2 with SQL Server 2012. As far as I know we have no plans to upgrade in the next year anyway so what I did is I have to go out and start looking for it. Is there a software solution out there that will give me SQL 2012 running containers? I eventually found, yup, this company called Windox who have basically built a custom port of the open source software that Dockers put in the market which allows earlier versions of SQL server to running containers on earlier versions of Windows Server. I think it is 2008 upwards, both cases. I mean, I was pretty tough of that because it was exactly what I needed so I went off and attached to them and they’ve got a free Community Edition which I went to grab to ask them. Touch my system admins and grab my old beat up server, installed Windows Server 2012, followed the Windox installation instructions which again was pretty simple. They’ve even simplified it more since I had the first run of it. It’s basically a partial script to get the engine up and running, and once you’ve done that you’re good to go. That has allowed us to get SQL Server 2012 in containers on Windows Server 2012 so I got my Dev and QA Teams now working containers down the same level, and patch level as my production environment.
Carlos: So what’s the difference between the Community Edition and in the paid version?
Andrew: It’s basically the number of containers that you can have.
Carlos: Ok, so what’s the Community Edition limit you to?
Andrew: I think it limits you to two. Literally it’s just a soft gateway to show you how the functionality works and what you can do.
Carlos: Got you.
Steve: Ok, so in that solution then, if you’re downloading the container with the SQL Server version to match what you’re using in production. How is the licensing then done on that? Are you then having to put your own license codes in for SQL Server?
Andrew: We’ve used the Developer Edition so we neatly side step all of that.
Steve: Oh, perfect.
Andrew: I think the way it’s going to work with say like Standard. I think it would be nuts to go with Enterprise in the container at the moment but I think it would work the same way as how you would license your host and you can build from there.
Carlos: Yeah, I think that’s a great way to make that adoption and to get comfortable with it is to using containers in those lower environments because they do tend to move. You want to spin them up, you want to take them down. You want to shake and bake them a little bit more than your production environments obviously, and that’s what actually containers are really really good at as well. That’s an interesting approach.
Andrew: Definitely treat the containers as throw away object. If you go out and use this one don’t spend time restoring databases into it, fixing things. Blow it away, run a script, build new container.
Carlos: Right, so now what happens if you wanted to add a new database to the container? What’s that like?
Andrew: It handles and works exactly like a normal instance of SQL through Management Studio. You don’t get the agents so that’s the only thing you will see. But you create a database and if you wanted to then replace your base image, you stop that container, blow away your original image and save that as new image. And you’ve got an updated image with your new database. I mean that’s part of one of the reasons as well that we went for containers instead of deploying to VMs. Because the guys we’re deploying our databases from base lines back up files basically kept in TFS, and then applying a load of updates to each and every database to get it to the level of production. Whereas what we’re doing is now is every week we have a certain amount of releases and at the end of the week I deploy all our releases to our base image, update that image, blow the old one away, save a new one. And then next week can all blow the containers away, build new ones and in a couple of minutes and they’ve all got up to date containers exactly the same as those environment.
Carlos: Got you.
Steve: So then for that development environment you’re then able to get those containers, sound like up to date a lot quicker than if you had like even standalone SQL Server Developer Edition installed in every developers machine. You are able to just grab that latest package or latest container, and then everyone is on the same baseline rather than each developer maybe getting out of sync somewhere.
Andrew: That’s it, yeah, so we can say to them shut down, you’ve all got your Dev VMs pointing to the containers. Your containers are now out of date, blow these away, build new ones, and you’ve got, every single one of you has got a replica of the exactly the same image which is up to date with production.
Steve: Yeah, I could see how that alone would just save a massive amount of time.
Carlos: And confusion.
Andrew: The deployment worked on my VM but it’s now working on yours. Why is that? Because there is a mismatch there which we’ve now managed to get rid of.
Carlos: Yeah, exactly. How many times have you heard that? Right, well works online.
Steve: Yeah, that’s one of the standard developer lines I thought.
Carlos: So I guess we’ve talked a little about some of the pieces that you’ve used. I guess anything, any other advice as far as getting up and running.
Andrew: I mean it is very simple to get it up and running. Bear in mind that this is brand new technology, we’ve had a few issues with integrating it into our own processes. I think the bit of advice I would give to anyone looking to go with containers is don’t go with containers just because you want to use containers. Have a look at your businesses processes and practices and see how they’re going to fit in there. Don’t try and work containers into them if it’s not going to be right because they are great technology to work with but you really need a business case for them there.
Carlos: Ok. But I guess I’m going have to say that I think particularly that scenario that you just provided that almost everybody has that circumstance with their lower environments. Well, I guess assuming their development shops, right. If you’re installing SharePoint for example and that’s your application then maybe that wouldn’t necessarily apply. But I think most development environments where you could make a case for that.
Andrew: Yeah, agreed. I would suggest have a look at and see what they can do for you. I’m very guilty of which technology I want to use and I want to play with it. But I really have to put out business case to get this in. It’s been working pretty well. I mean, we’ve had some issues to the fact that sometimes the containers are up and running and it’s new and it has crashed, and then we have to redeploy and do things a lot. But we get into the habit of the throw away objects. Get rid of them, rebuild and you’ve got another one straight there.
Steve: Alright, so then I mean, sounds like containers have been around with Linux for much longer than they have with Windows, obviously because this is brand new Windows. But are there any real conceptual differences between containers in Linux versus containers in Windows other than the obvious operating system differences?
Andrew: I won’t say there are any conceptual differences. I think the main thing is that, as you said, they’ve been around on Linux for a lot longer so that technology is a little bit more mature and some of the features. I think some of the networking stuff isn’t available on the Windows side yet but it’s really just a case of just watch this base I think.
Steve: So then on the networking side, I know if I spin up VM with hypervisor it’s doing this virtualized network interface for that VM. Is there something similar for containers where they’re getting a virtual network interface or they’re using the network card of directly of the OS?
Andrew: I think they used the network card. I’m not too sure I’m afraid actually there. The way I’ve been working with the containers as each one of them, each SQL instance listens on its own specialize port that you can specify when you create the container, so you can map the host port to the container port and when you’re connect them locally or connect on host port. When you connect them remotely you’ll get your individual pullage specify when you create the container. So that’s the one thing we did when we’re building the containers and the aliases that I mentioned is that we use aliases because you can’t use say like host because you can’t specify port. But with aliases you can say connect to this server on this port and that takes them straight into SQL instance.
Carlos: Right, so that mapping actually happens in the container where you can specify when the incoming port is this send it here.
Andrew: Yes. So it’s just a little switch when you specify say, run, create a container from this image, this port to this port, off you go. It does mean there are differences when you connect say, if you’re on the VM where the host is running, where the containers are running. You’ll be connecting all different ports as you were if you were outside of that VM.
Carlos: Ok, so then for someone like me who has not used containers before but it sound interesting and I want to go and give it a try. Is there any advice that you would just recommend to watch out for or be aware of on that first time giving it a try?
Steve: Well, I think if I was going to do that, just to jump in here Andrew, I think also obviously on the Windows side, right, you have Windows the Community Edition to going to get started. And then I would jump to Andrew’s blog and start there.
Andrew: I mean there are blogs there I’ve written from Part 1 to Part 4 that will take you through initial setup and then playing around a little bit. And then actually working with the Docker hub as well which will allow you to upload and pull repositories down and things like that.
Steve: And that’s side is dbafromthecold.wordpress.com. Is that right?
Andrew: That’s it, yes.
Carlos: Yeah, and we’ll have that available in our show notes as well which will be available at sqldatapartners.com/containers.
Steve: Perfect. Well, I think that wraps up the questions I’ve got about getting started.
Carlos: Ok, awesome. Well, I mean so great conversation, great, you know, interesting technology. Thank you for coming walking us through some of these use cases that you’re using it for. I think particularly on the Windows side, because admittedly that’s where I feel more comfortable. I’m interested in trying Windox and kicking the tires there, and see how it goes.
Steve: Yeah, thanks for having me guys. There are a couple of things I’ll come back to on especially about the network adapter. Those are interesting questions although I wasn’t too sure. And also I do also want to double check the upload and download of containers into the Docker hub and see if it will actually just keep the actual states of the SQL instance or it will take the databases with you. So I can come back to them if you would like to.
Carlos: Alright, yeah, that would be great.
Steve: Or I’ll have to keep an eye on your blog and see if it shows up there I supposed.
Carlos: So shall we go ahead and do SQL Family?
Steve: Yeah, let’s do that, so how did you get started first using SQL Server?
Andrew: I started using, well the first job I got was this local government coming out of university, and it was writing Crystal Reports. I didn’t have any idea with the backend was. I was just writing SQL queries. I thought it was Oracle at first, it was actually SQL Server. I was doing research on, you know, I was writing, and things were running slowly and I start doing a lot of googling around how to speed things up for. So basically how to do certain things with SQL I didn’t know. I eventually stumbled at sqlservercentral.com. I’ve found this huge wealth of information about this whole entire industry of professions that I’ve had absolutely no idea about. And just started peak my curiosity from there and eventually decided that I wanted to become a DBA.
Carlos: Very cool. How then did you get your first job as a DBA?
Andrew: I think completely out of luck. I was very very inexperienced. I was applying for jobs and eventually got into with a place called the United Kingdom Hydrographic Office. The interviewer there gave me a chance and gave me my first job as a DBA. That was about 5½ years ago now.
Carlos: Very cool, ok. Now if you could change one thing about SQL Server what would it be?
Steve: I want to have dark theme for Management Studio but it’s not going to happening anytime soon