SQL Server on Linux, Kubernetes and Containers

SQL Server on Linux, Kubernetes and Containers


(upbeat music) Hello and welcome. My name is Sanjay Soni, here’s a Microlearning readiness video. So Bob, I’ve heard it’s
very hard to understand, anything about containers or Kubernetes. That is such a myth. (both laugh) Actually containers are a lot more simple, than people think they are, but I wanna tell you one quick secret, you just said SQL 17
today, and SQL 19 tomorrow, I’ve been sneaking in
some 19 stuff on you, when you didn’t realize that, because of the great story of SQL Linux and containers running about, so when I showed you containers today, I might even just run it on 19, just to prove to you I can do it, right? But it runs on SQL 17, so, yeah, people just, they over complicate containers,
which is understandable. It’s new technology to SQL people, it’s been around for a little bit, it turns out we’re talk about Kubernetes, that’s been around a little bit too, but it’s really new for us as a system. And one of the reasons why is that, Linux is really the operating system that kind of started
the container movement, and so we just started SQL
on Linux and SQL 17, right? But, you take a look at
this slide right here, this kind of gives you an idea, my overview about why containers and why it’s different a little bit. So, first of all, just
look on the left hand side, just think about why you would
even do something like this. First of all, container images, that’s how you start using
an image are very portable. Okay, I’m a sneaker net guide, do you even know what that is? Sneaker net is an old school thing where if the network went down,
we would just take diskettes and walk around in the room
and just hand it around, Simple solution, simple solution. So imagine taking sequel
server on a USB stick, anywhere to any conference in the world, popping it in your computer. And I’m just trying to run SQL, whether on Mac, Windows or Linux, I can literally take this
USB stick to a Mac user, give it to them, and I’ve run
SQL server now so portable, because these images are consistent and we really kind of
focused on using Docker as a container system
and anywhere Docker runs, which is on these popular systems, right? Linux, Mac and Windows and in the cloud, You can run SQL image,
it’s the same image. That’s one thing. Number two, it’s lightweight,
so it doesn’t replace VM’s Yes. That is the question. When do they use VM’s vs Containers? It’s a fair question. Think about a customer
that runs a VM farm today with sequel server, right? The VM’s themselves are great. They provide isolation to
all the sequel instances, but they’re pretty heavy. You have to install the
entire guest operating system in the SQL server in order to run SQL. Well as it turns out, you
might not need everything in the operating system itself to run SQL. That’s possible. But you still have to put
the whole lesson there. Take your VM farm, right? Take maybe running less VM’s but put a bunch of containers in the VM. So don’t think in terms of the containers replacing VM’s, they’re
going to supplement them and make them more efficient. You can run containers on
bare metal Linux systems. But it’s often you’re
going to see them like in Cooper Nettie scenarios
to run multiple containers inside a virtual machine. I see a compressed containers in the VM. Now why is that? Why are they lightweight? Because it turns out what they are is just a set of processes. So if you run multiple
sequel server containers, which is by the way, that’s
how you do multi instance, for SQL on Linux, you
run multiple containers. When you do that, each of the SQL processes running with what’s called their own namespace. It’s an isolation model
that the Kernel provides, right? In Linux. but they’re not VM, they’re
separate SQL processes. They don’t know about each other, so they’re isolated. But you still can communicate with them because you got to connect
the SQL and so forth. But that really what it comes down to it’s reduced disk
footprint, CPU and memory, because of the fact that I can condense and build multiple of these containers in one virtual machine. So that’s the second thing, right? The third is consistency. This is one of the stories and you look at the thing
down here about DevOps, right? That’s a big, hot topic on
in the industry out there. My colleague Vern Hughes, he just has some amazing
demos about CICD and DevOps. You can look him up on the internet. He’s the container got everything. I’ll do something to
him inside the office. He’s like Bob, how come
you’re not using a container like he pushes me to do everything with containers, and I love it, because it stretches me and how containers could be useful. The thing about the consistencies. So you got these SQL images out there that have SQL server, different CU’s, in 17 and then the 19 previews. That’s a consistent image of SQL server. Now, you could combine
that with a SQL database, or a set of scripts. So consider this scenario; You’ve got a Dev server with SQL, that you share with a bunch of Dev’s. You’re the DBA that they ask
you to manage the Dev server. And they’re constantly tearing it up, like they’re changing
configurations on it, and you never can get a consistency of all this development
on this Dev SQL server. So forget that whirl with containers now. You just give Dev a
container image of SQL, plus a serious of database scripts with your Skeema, and say “go test all that”. Dev on that. And everybody can do it at their desktop, or their server. So you have a consistent story now of using things, and this is very common
in the DevOps world, now we’re just putting SQL into that fold. Efficiency. So I’m gonna tease you and say let’s talk about that later, but I’m going to give you an amazing patch experience for SQL, which I’m kind of hinting
to on this right-hand side, how you can switch containers, you can upgrade a patch
SQL in new different ways. So I’m gonna tease you and tell you, I’m gonna do that one a little bit later. I see, of course. Let’s touch up on, what’s
so special in terms of containers within SQL server. Yeah, so, containers have
been in here for a while, and people use container images for varied different reasons. If you think about SQL
server, what we’ve done. Cause what you do, you
start with an image, that is a binary file, and then when you start an image, you’re running a container. That’s the difference, or that’s how you can think of these technologies. So, what we have, is we
have something quite new from Microsoft, called a Microsoft Container Registry. All the products, all the
people inside Microsoft, as a company, are publishing their images in this container registry. Now Docker Hub, and Red
Hat Container Catalog, are very popular places for people to find containers. So we syndicate from there. So, you’re gonna find all
our images on this registry, in Microsoft, but then you can go out
and Docker and Red Hat, and see ours as well. And we’ll just redirect
you to this registry. So that’s where you go get these things, it’s like pulling a package for Linux, you go get your image from
these public registries. For SQL ’17 we have Ubuntu based images and then, just like the Linux experience, we have an RTM version and
then every cumulative update, or GDR, we have a separate
image for you to run. That’s gonna become important
when I show you this patching methodology. Right now, that packaging includes the MS SQL server tools
and the server itself, as one bundled thing you get together. And then for ’19 we’ve actually outed now redhead images in Ubuntu, and it’s the same thing. SQL server and tools. But, what’s the big deal about this? Like what is the real big promise of why I would use a container for SQL server? It’s pre-installed. So SQL servers already installed for you. I think that’s crazy and amazing. You show the deployment Linux experience, and you sat there and
go “I don’t believe you, I don’t believe you did it”. Well you wait till you see
the container experience. SQL server pre-installs it so we can start the engine in a matter of seconds. So you don’t do any
installation of SQL server now, everything’s for you, all the binaries all laid out for you, and then you just configure it later. And then, remember I mentioned
these other packages, IS, or extensibility, machine learning. There’s techniques where you can build your own custom image, and then add in those
packages as you want. And then again, just
like I talked about Linux and Windows, database
and app compatibility, it is still SQL server. It’s nothing special, it’s not the weird version of SQL. Right now, we’re focusing
on SQL Linux containers but I told you the Linux was compatible, now think of just running
SQL Linux in a different way, that’s the way you should
think of SQL server containers. Alright, so let’s keep going,
in terms of more details behind the scenes. Well, I mentioned about this magic about patching and upgrading. So I told you what a container is, like why you might wanna use it. I talked about how SQL publishes it, it’s pre-installed. So I’ve done this demonstration now, and I’ll show you in a slide, and then I’ll show you a demo of it, why people just think that this is crazy. So when you install SQL server
databases with containers, to make sure that they’re persistent, you put in something called a volume, which means the databases
are on a persisted file on your host system. Now, what you see here in this diagram is the dark color is a
container that’s running and the lighter colors are containers that are installed but are idle, like they’re not running. So I’ve got CU8, that’s one
of our CU’s we just did. It’s running, and it’s using SQL server. Now my goal is this,
I would like to update the latest cumulative
update of SQL server. That’s my goal. Now, this is weird. You don’t go and patch that container. Like you can’t even do it. What you do is, you’re gonna switch to a new container,
pointing your same databases because we’re compatible
between cumulative updates. So here’s what it looks like. You stop this container. Now the link for the database is gone, but the databases are still there. You start with the latest CU, and I just did an update, in the matter of like a minute. I have updated SQL server. That’s easier and faster. It’s faster, easier, and it’s consistent cause I went and tested
my latest CU really well. Now, here’s your next question, I’ll prompt you for the next question. ’19 No, not ’19, actually
before I go to that one, what if you found a problem with this? Oh yeah. Here’s what you do. You shut down that container, and you just point and
you start up the old one to the same databases. And you just rolled back
in a matter of minutes. And I could sit there
and switch back and forth So it’s just a beautiful
story of how you can now update SQL server. Now, you did ask about
’19 so it’s possible I could shut down this container, I could start up ’19 to
the same set of databases and now I’ve simply
upgraded SQL server ’19 with all my databases
in a matter of minutes. And same thing for the rollback too? That’s not there, as you know, you cannot actually take databases and switch between major versions that go down-level. But right now it is a really great, efficient, upgrade story. (mumbling) So, let’s talk a little
bit about Kubernetes, and then I’d like to show a demo of that amazing patch story. So, have you heard or Kubernetes, or have you kind of maybe
heard of the marketplace. Containers, Kubernetes, what’s next? Yeah, so I was just at a major event, where people were asking
me about containers, and Kubernetes. One of the questions I got was, “how do I do containers in production? Like what would I do?” Remember the story I just showed you about running container. It’s one thing to run
like single container, or two. You can just do that in your VM, and you can manage it and so forth. What if I could do hundreds of containers? What if I want high
availability for containers? How would I do that? Along comes this open-source project, which is now a full-blown product, called Kubernetes. Out there in major different cloud and private cloud offerings, like Azure, Kubernetes
service, or OpenShift. And we fully embrace it. And in fact, even in SQL ’17 we supported this functionality. Now Buck is going to do this
amazing story for you tomorrow, about what is Kubernetes?
What is a node and a pod? All these different things. So I won’t spend time doing that but I’ll show you a possible scenario that even on SQL ’17
you can use Kubernetes. Flip back to the high availability
message we talked about. We talked about fail of a
cluster instance in Linux, we talked about availability groups. So, what if you would like
to do SQL server failover, without clustering software. Like you wouldn’t have to install anything like Pacemaker software. Kubernetes has a built-in model that supports that. And here’s how it works; Kubernetes understands
the health of these things called nodes, and pods, and containers. We’re talking about containers, and we’re talking about taking containers and running them at scale in a big infrastructure called Kubernetes. So this is an example where
SQL server’s, and containers, run in something called a pod. That’s cool and interesting. So, what about that? Kubernetes also provide something called persistent volume, so my databases will survive any of these kinds of scenarios. The other thing it provides is something called a load balancer service. Think of that as like a virtual IP. Because what I want to do is I want to point my app to that load balancer so no matter where SQL server
lives in this environment, I’ll always be connected
to the same in-point. So here’s what happens, Kubernetes says “Uh oh,
there’s problem with the pod, there’s a health problem, what do I do?” What I do is, I start up
a SQL server in a new pod, and on the same node. In this case, just think of nodes as VM’s. That’s an easy way to think about it. Couple of VM’s but Kubernetes
orchestrating this. Now, notice my load
balancer got automatically redirected to my new pod. So I had to reconnect, I
had a small brief period of reconnection, but SQL server was
started up, I reconnected, and the same databases
are persisted because this is shared storage. But what if the node has a problem? No problem. Kubernetes will start
up a new pod with SQL on a new node, I’m still pointed to the same set of databases
so it’s a shared model, and everything just works. This is built-in availability
for SQL server, right? Through Kubernetes without
clustering software and using this Kubernetes pod concept. It’s pretty cool. So, this is really cool. So I’m going to interrupt for a moment because I have a live question. So what is the name of the
Docker champion you mentioned? So, you know, when you talk about Docker and so forth, as a technology. I don’t know if I remembered a champion, I’m not sure what that question means. Remember that Docker
itself, as a technology, runs across Operating Systems,
or in Cloud environments, that’s the container technology. Kubernetes is a technology
that orchestrates all these containers that runs, for example, an Azure Kubernetes service, which is a service from Microsoft. Or Red has an OpenShift, or you could even just use kubeadm and build your own Kubernetes
service, so no problem. Alright, well that’s the
shared storage answer, what about availability group? Couple things; one is you want that replica thing, you still want that, and I didn’t show that
in that first picture. The other thing is, I told you that, the health of these nodes
and pods are dictated based on the health of the pod or node, not in SQL. And, remember, SQL has
health protection built-in to fail over clustering,
and availability groups. So we come around in
’19, it’s in preview now, and we build on that technology. We’re integrating availability
groups in with Kubernetes in a new way. So we can make sure Kubernetes
understands when should SQL failover, if there’s a SQL problem. So we introduce this
concept called an operator, that runs in a container, and does something amazing like this. It actually spins up,
and configures for you, a complete AG with primary
and secondary replicas and it includes containers
for the health of SQL, called AG Agents. You take your app, and you point it to, through a load balancer, to the primary. That’s what you normally do, right? But you want that secondary
reading so you point your reporting app,
through a load balancer, to the secondaries. Now, actually in a pretty
short amount of time, on Kubernetes you have a fully-functional AG system, high availability
with SQL server. That’s pretty cool. Your primary app to your primaries, reporting after your secondaries. But what happens if there’s a problem? A failover? So if a problem happens on this primary, through the technology
of availability groups, we’ll work with Kubernetes, we’ll actually failover
to one of the secondaries, the load balancer redirected
to that new primary now, makes sense. And your old primary, which had a problem, could come back up as secondary and then the load balancer
redirected to the secondary. I see. So failover plus load balancing. Failover, load balancing, integrated with availability groups. No clustering software installed. Kubernetes provides that infrastructure within that environment. And so, you know Sonjay,
for your audience today, if you thought about new
technologies that they might take a look at, it’s
containers and Kubernetes. That’s kind of a direction for us, and you’re gonna see tomorrow,
when Buck and Anna get in, and start talking about
what’s going in ’19, we’re gonna base a lot
of things based on this. SONJAY: Alright, can we see a live demo? Yeah, let’s see it. I mean you got to see this updating thing, kind of blows people’s minds. And this demo is a little
bit hard to show, I heard. It’s not that bad, it’s
just hard to follow, I’m using Azure Data Studio again because I just love this big
orchestrated environment. I’m showing the example right here of a command to run a SQL container. I’m not going to go through
all the syntax cause that’s in the workshop. So I’m just gonna actually
go run one of these. We’ll show you at the very end here in the resources page,
I’ve got a thing for that but it’s called SQL workshop. SONJAY: Forward slash SQL workshop? BOB: Actually just go search SQL workshops on your search engine and you’ll find it, and all the stuff’s there, right? So I’m gonna go run one of these things, let me go log in here for a second. I just ran SQL server, I just installed SQL server. That was it. Remember the whole Linux thing I just did, I just did it right there. SONJAY: That’s the magic of containers. BOB: That is the magic of containers. Now, you’re saying to yourself,
” I don’t believe you. You didn’t really do it” let’s
go use Azure Data Studio, let’s try to connect to it. So I’ll connect to it, I’ll put in a port number
matched to it, and I’m done. There we go, I Just
connected to a container running SQL Linux. I’m ready to go. SONJAY: So previous demo that you showed there were a couple of more
steps but this step’s even easier. BOB: Can you believe that?
Is that just like insane? Okay, so let’s do something else. Let’s actually show the
database compatibility. So this one, let me just make
sure I have my demos here, this one I’m gonna copy my back up into the container. And step three is to do a restore, and I’m going to use the
same restore commands I just used in SQL server. In fact, let’s go back here. So you believe me. Here’s my restore, this is running a SQL command with a restore syntax. You see this restore database
syntax over here, right? Let me run that. And this is gonna start
restoring that database. So now, if we go over here and look at our explorer window, let me get this up a little bit so you can see it. Here we go. Whoops, I lost my context down here. Let me get that back. There we go. Let’s get this, let’s just go full screen. I lost a little bit of typing down there, I dunno what happened with that so let’s go back up here. SONJAY: It’s a live demo. [Bob} Hey, it is live. Uh yeah, I dunno why that won’t refresh. SONJAY: Start again maybe. BOB: Yeah, let’s blow that away. I mean it’s easy enough to connect back to a terminal again. Actually, you’ll see how it works. Yeah, that’s still not
displaying right, let’s see. It’s okay, I know how
to run these commands so I’ll run it very easily. But let me go show you here, so I’m running in that container. Let me refresh this. Now I come down here cause remember there’s WideWorldImporters,
just restored it. So I showed you the
power of that doing it. But remember I told you I wanted to update this thing? Let’s go back a directory. So if I go here, to sqlcontainerupdate, here is the update
script itself right here. What I’m gonna do is I’m
gonna stop the one container and then run a new one
using the latest update. I don’t think I’m going to
be able to see this in here. Let’s do this, let’s
restart Azure Data Studio, it’s not a problem. Actually we can just do it over here, with this one. We already had this one going. So what we’ll do is we’ll
go out of this terminal. This is live demos. I’m just doing a secured shell into here. No problem, I’m back
into the same container. Let’s go to to this directory here, we got time, looking at my clock here. I’m just going to where I put my demos. So this is, in fact
I’ll go back over here. I don’t have it listed for you right here, that’s okay. So this is that command to
shut down the container, running the current CU, go to the latest container by starting up the new one, and I’m just going to do an update. So let me go run it. Step 1, update. Put in your password. So this is gonna go run. Now, what it’s gonna do is
it’s gonna shut down the one and start a new one up. This is the command, in
fact I’ll go really brave over here and I’ll try to connect to it. Can I do it? Let’s try. So I’m gonna go bwcontainers, port 1401. That’s the port number. SQL log in; sa. (mumbles) Put in my password. No. Oh yeah, sorry. It didn’t like that password. And now, here’s the thing, we have to do a little small
upgrade script step here, so it takes about a minute to actually go through this process. So I might get a failure there. I dunno, that should’ve
been the right password. SONJAY: Maybe caps lock? BOB: Yeah maybe. Yeah I dunno why. I might have used a different
password for that one. Let me go back here and see. I actually can connect
through SQL commands so. (mumbles) Sorry, I got to put in the
actual right host name here. So this is the server
name; localhost 1401. And I put the password in. I dunno what I did. Just hold on one second here. I’m trying to make it more
secure, yeah, exactly. Let’s see, what did I do here? Oh. Sorry, there’s my password,
I used the wrong password. Too many passwords I’m managing, right? It’s okay, we’ll connect to it over here. Go back over here and do it. So bwcontainers, 1401. That’s because I was
using a SQL ’17 system and that’s why I was using that password, so no problem. We’ll just
put that password here, remember it, and go. And there I am, I just updated. Here’s the deal, I’m on the latest CU, so within a minute; I shut down one container,
pointed same databases, started a new container
with the latest CU, and I’m now connected, no problem. So that actually takes less time than that but it took me a while
to remember my password. Welcome to the new normal
of patching SQL server, I showed this at a major
conference and everybody said, “I don’t believe you, I don’t
believe you actually did what you did”. So I’ve got the workshops out
there, you can try it yourself and see it. And then I’ve got the
workshops to roll it back. I could run a script where I shut down the current container
with the latest bits, come back and start up the old container, which is still on my system but just idle. And I can just go back and
forth, within a minute, I’m installing and patching SQL server. It’s kind of crazy. Fantastic, so I have one more question, last question, live question
for the audience here. BOB: Do it. Will a SQL server for Linux be available on the Linux subsystem. The Linux subsystem for
windows are you talking about? Yeah, right now, it doesn’t fully support running that system. We’re certainly taking a look at it, the only official support
is distributions for SQL. Our Redhead, Ubuntu. We’ll probably take a look
at that in the future. I think we need to find
out from our community; is that something that
we really need to be able to do in the future. I see. And one more; is the launchpad feature
available in SQL server for Linux? Yeah, so I showed you that diagram of the launchpad, which is
Machine Learning Services model, right? So in SQL ’19 we’re supporting SQL Server Machine Learning Services. And so we’ve instrumented
that same concept of using a launchpad, similar
to SQL server on windows. And you can even do it with containers. Awesome. Thank you so much. Did you like the container stuff? It’s kind of crazy, isn’t it? Of course. I mean so many thanks for verifying all these
great things today, and thank you for your time, of course. No problem. And these are the
resources, you know you have some good resources there. Yeah, you just wanna get
started on this maybe. We showed you the SQL ’17
you can go there to do it. If you wanna do SQL Linux
here are some shortcuts, that you can run; first,
Linux or containers. And, again, our workshops are out there. Everything I showed you
today is in the workshop. You can just download through GitHub, run these things on any environment. The cool thing about containers, go to Docker for Windows,
if you’re a windows person. I used a Linux VM, you can just install Docker for Windows, and run
all my demos if you want to. And then I did a shameless
plug here, I wrote a book. So in the book, I do
cover all these examples of Linux and containers,
and even Kubernetes, and here’s the shortcuts
to go get the book. From Apress Media, and from Amazon. SONJAY: And of course folks
can follow you on twitter, Bob. BOB: Yeah, @BobMS, I’m not great about it but I do like to talk
about our technology, and so you can go out there, and I’m also on Linkedin as well, where I talk and post things, going on with SQL server. Fantastic. Thank you so much. You’re welcome. Thank you for watching this video. Learning more about
this, and other topics, at Azure.com/SQLDW

Leave a Reply

Your email address will not be published. Required fields are marked *