Up next in 10
SQL Server Containers by Bob Ward || SQL Server Virtual Conference
12K views
Oct 30, 2023
Containers have become a popular and modern method for deploying and managing applications at scale. SQL Server, one of the most popular database platforms in the world, supports containerized images to open up new ways to deploy and manage databases. Come learn in this session how and why SQL Server containers can help you become more efficient, empower DevOps scenarios, and reduce time for operations like deployment and patching. About Speaker Bob Ward is the Principal Architect at Microsoft - 27 yrs and counting :) Conference Website: https://www.2020twenty.net/sql-server-virtual-conference C# Corner - Community of Software and Data Developers https://www.c-sharpcorner.com #SQL #containers #docker #conference #sqlserver
View Video Transcript
0:00
Okay, let's get going. Welcome everyone. This is pretty rapid stuff if you're just watching
0:08
back-to-back sessions, right? You're jumping around to all sorts of amazing sessions and
0:12
SQL servers and huge call out to Simon for putting this together. I know so many of you
0:17
are dying to see people speak in person, but it's so amazing people like Simon would put
0:21
together a virtual event so that we can continue to empower you with the knowledge about everything
0:27
SQL. And so when Simon and I were talking about what should we do with the, what should Bob do at
0:31
this conference? Because I've spoken on a lot of topics over the years, as you saw on there. It's
0:36
actually now 27 years, Simon. Can you believe that? 27 and a half years. Anyways, I thought SQL
0:42
Server Containers would be a perfect topic for this conference. I know several of you on this
0:45
event that are watching and listening are developers. So let's talk about it. But first
0:50
of all, I want to make sure you are set the stage and have the resources you need. Look at this
0:54
screen at this link right here, aka msbobwartms, which I'll show you again at the very end in resources
1:00
the deck is already there. Everything I'm showing you right now in a slide deck
1:03
you can go to that website and see this deck. It's under a folder called SQL Server Containers
1:08
Let me also show you another resource. Let me pull this over here to the screen so you can see it
1:14
This is called SQL Workshops. This is a free GitHub site and if you scroll down to the SQL 19 workshop
1:22
Again, everything free on GitHub here, completely for your use. If you scroll in here, the scripts I'm going to be used that I'm going to use today are right here
1:31
You're going to see me go through using PowerShell certain steps on how to run SQL Server containers
1:38
So if you're like, hey, I'm watching Bob doing this. I need to get these scripts. They're already for you out there
1:43
Okay, and I'll give you these resources again at the end of today's session. Okay, let's go back and let's start talking about containers
1:50
So first of all, I kind of want to set the stage a little bit on a few basics of containers
1:56
The deck that's out there has some hidden slides of things I'm not going to show you because I only have like 45 minutes, you know, to talk about this topic
2:03
But here's just a couple of basics because I'm going to talk more about the SQL Server containers
2:08
So first of all, I always like to use this slide to explain my version of what containers are
2:13
And the number one thing I emphasize is right here. Sorry, is right here
2:17
That containers are really a process run in an isolated manner. It could be multiple processes, actually
2:24
But many containers are just a single process. You'll see that with SQL Server
2:28
So it all starts with something called an image, though. An image being a layered snapshot, a layered snapshot of files, the minimum files you need to run this process structured in a file system
2:41
So these images are really cool. And you're going to see how SQL Server provides several images for your needs to run SQL Server containers
2:48
Now, when you have these images, a container itself is going to be the runtime instance of the image and a something called a container runtime
2:58
And today's example is I'm going to use Docker is used to go run those processes, these instances of these images
3:05
And you're going to learn today in SQL Server some of these other concepts like what's a writable layer and a read-only shared image layer, et cetera, et cetera, et cetera
3:14
So I just want to make sure you understood that. A lot of people I've heard say things like containers are lightweight virtual machines, and there's all these things out there and what they are
3:22
And look at the top of the right-hand screen here. A virtual machine emulates hardware and loads the entire operating system
3:28
A container is a process run in isolation but cooperates with the host kernel
3:33
So you're going to see that actually in the real world, containers complement virtual machines
3:38
A lot of people run containers in virtualized environments, and you'll actually see that in example today
3:44
Now, there's a couple of things about containers I want to tell you. Number one, under the covers
3:48
I'm an under the covers person. I don't know about you. I like to see how things work. It's like, how did Docker, one of the most popular container runtimes, do this
3:54
Is there some magic to Docker? Well, as it turns out, not really
3:58
There's actually an open source piece of code called libcontainer, also called runc, based on this OCI, open container interface
4:07
Libcontainer contains all the source code to do containers, to run containers
4:11
And in Linux, these are the concepts from the Linux kernel that are used to make this happen
4:17
Like a namespace is used for isolation or the union file system to layered file systems
4:22
That's how we do things like sharing read-only file system images and do writable layers
4:27
or control groups, allowing us to do things like governance and accounting or limits of processes
4:33
So as it turns out, you too could write a container runtime if you wanted to
4:37
You could follow this open container interface, but you could just use concepts from the operating system yourself
4:43
And these concepts are used in places outside containers. Now, the last concept I want you to understand, it's really important because this comes up all the time and people ask me
4:51
how do Linux and Windows containers work? How does Linux containers run on Windows
4:55
So first of all, a Linux container image on a Linux host, that could be a virtual machine, could be bare metal
5:02
It's a native Linux process. You could go into the Linux system and see the SQL Server process run on the Linux kernel itself if you're running container image
5:11
Okay. That's important from a performance perspective that you understand how SQL Server performs as a container versus running SQL, just say, in the VM itself
5:20
Okay. What about a Linux image on Windows? Well, for the longest time, it required an entire virtual machine running on Windows to make this happen
5:29
But now there are two technologies, one's called Linux Containers on Windows and WSL, Windows Subsystem for Linux
5:36
They support the concept of a virtualized process, but it's still virtualized
5:41
So, for example, if you're going to try to compare SQL Server performance on Windows as a SQL Server Windows program with a SQL Server container Linux image
5:50
that wouldn't be fair because there is still a virtualized process involved and a layer there that could not be fair to compare performance
5:58
But if you ran the SQL server image on Linux and a SQL server Linux container image on Linux, it would be the same because it's a native Linux process
6:07
Now, on Windows, by the way, which today we don't support SQL server Windows containers
6:12
We're still kind of trying to see whether people really want that. We don't see a lot of demand for it, but just so you let you know, a Windows image on a Windows host can run in isolation as an actual native Windows process, but we also support something called a Hyper-V isolation
6:26
And then today, at least not yet, it's not possible to run a Windows image on a Linux host
6:31
So I put this together. I've talked about containers for the last several years. This answers a lot of questions people have about actually how does this work under the covers
6:39
Okay, let's go talk more about SQL Server containers now. So first of all, why do you do this
6:45
Like, what is the big deal about this container thing? And let's talk about these a little bit. Number one, portability
6:50
And I look at it this way from this perspective. You have developers in your organization
6:55
and you have a developer server for SQL Server you all share. DBAs can't stand that
7:01
Sorry, developers. You're always wrecking the SQL Server. You're changing the configuration. You're running something
7:06
You guys are all messing it up because it's shared. Well, go start running containers
7:11
Container images are portable anywhere you can run the container runtime. So for example
7:15
the SQL server container image for Linux runs the same and is portable on Windows, Mac OS
7:22
and Linux itself That pretty cool So if you got Mac developers Windows developers all try to run SQL server just give them container images to run on their laptops And we talk about how you can use a consistent image of SQL Server along with scripts and other things to give them a complete package to do development testing in their environment
7:41
In fact, quick story. So I'm at an event several years ago. You may have heard of it called SQL Bits. And someone in the audience saw me presenting like this and said, hey, I'm not feeling the Mac love here
7:52
Bob, you're a Windows guy. You're running a Windows laptop. You're doing everything on Windows. What about Mac people
7:56
I don't want to run Windows. I'm like, I got you covered. In fact, I did a challenge for him
8:00
I said, within five minutes, you can run SQL Server on Mac and a native tool to connect
8:06
to SQL on Mac in five minutes with no Windows software. And he's like, I don't believe you
8:10
And I said, well, feel free to get on Twitter and say I was wrong. And so the next day, somebody pointed out to me on Twitter that he said I was wrong
8:16
And I was really disappointed. I'm like, man, I really screwed up here. He said he actually did it in three minutes
8:21
And what he did is he ran the he saw Docker on Mac OS. He pulled down the SQL Server image on Mac OS and installed Azure Data Studio on Mac OS, which there's a native Apple Mac version for
8:34
And in three minutes, he was up and running. So don't believe that you can't do things now
8:38
It's all just Windows based. SQL Server supports these environments all over the place
8:41
Now, why is it lightweight? Look at this example right here. Because the fact that containers in their image only need the files they need to run the container image, not the entire operating system like a virtual machine, it reduces the footprint of running multiple SQL Server containers
8:57
In fact, in this diagram right here, it could be a virtual machine that's hosting all three of these
9:02
This is the new way to do, quite frankly, multiple instances of SQL Server in a more condensed way to compress how you install SQL
9:12
So and then finally, it's much more faster to deploy this. There's no patching required
9:17
I know you're thinking that's crazy, but I'll show you how and a lot less downtime
9:21
Now, if you're DevOps and we'll explain exactly how that works in a second. Now, if you're a DevOps person and you get the slide deck
9:27
go click on this link for this video. At the Build Conference in May of 2020
9:31
I did a demonstration of how to use Azure DevOps with SQL containers
9:35
to automate the development production lifestyle. I think you'll love to see that demonstration
9:40
You can go through and see exactly how I did it, how I built Azure DevOps project, use SQL containers to make simple GitHub changes
9:46
that automated the entire process. SQL Server we believe is a big part of
9:51
the DevOps lifecycle because we now support containers. Okay, so let's move on for a second here and talk about the images of SQL Server. How do you find these things? So first of all, the Docker Hub and Red Hat Container Catalog are great places to see where we show up as container images, all the different flavors, okay
10:10
but we keep them in the Microsoft Container Registry because that is owned by Microsoft
10:15
and we ensure the security of these images in that environment. Now, what do we provide
10:21
For SQL 2017, this is where we first started with Linux containers. Every single SQL 17 build
10:28
the G-A-R-T-M of SQL 17, each cumulative update, each security update that we have
10:34
all have separate images, and they're all cumulative. So you can go right to SQL 17
10:38
cumulative update, whatever, and there's no need to apply the general availability and apply another version like you would do in, say, Windows
10:47
And it includes the SQL engine and tools packages. In SQL 19, we now have up the game
10:52
We provide different versions of Ubuntu per your needs and different versions of Red Hat now-based images
10:59
But again, we also have general availability, cumulative update, and GDR packages
11:03
So you're thinking to yourself, what about that faster deployment thing? What did he mean by that
11:08
Check this out. SQL Server now, SQL Server is pre-installed in a container. When you see me run this thing, it's all ready to go. There's no installation required. You just run the container and you've got SQL installed and up and running
11:22
and the tools are pre-installed. And then the other part that's amazing, this is the SQL Server engine on Linux
11:28
So database and applications are completely compatible. You're just connecting to the SQL image
11:32
like you would SQL Server Linux. And I've talked before in many different other sessions
11:37
that SQL Linux engine is the same engine that we run on SQL Server on Windows
11:41
So don't think that all of a sudden now I run a SQL Server container and I'm gonna make all these changes to my code
11:46
No, just take your code, take your management studio tool, Azure Data Studio
11:51
You'll point it to SQL Server container and you're ready to go. So those are the different engines of SQL Server
11:56
And this is why you're going to want to use containers. This pre-installed concept, I've seen a lot of people now in the industry that are doing demonstrations for SQL Server speakers are now completely using containers in their laptop because it's so fast and easy to get this up and running
12:11
Okay, how do you do this? And this is an example using Docker. And we're going to see this as a demonstration in a second
12:17
But I thought important to kind of walk through the pieces of this. So first of all, when you run a Docker container, you can pass environment variables to the process that's going to run the container
12:29
In this case, it's SQL Server. So we have documented for and there's a link for this
12:34
We documented what are environment variables that are possible for the SQL container
12:38
Now, two of these that are required are accepting the EULA, the license agreement and the SA password
12:45
Because right now today, even though we've started support active directory authentication, in order to get this running, you must apply an SA password
12:52
Now, you can also use other environment variables. Like if you want to enable SQL Server Agent, you can do that at the same time
12:57
You're going to see as well, I'm going to encourage you that if you want to make configuration changes to SQL to the instance like memory, you could do it through the same concept when you run the container
13:08
The next is the port. So SQL listens natively on port 1433
13:12
And if you were to run several SQL programs that all tried to listen on that port, they couldn't do it
13:16
You'd get an error. So therefore you can map ports. So I typically do this no matter
13:21
if I'm running one container or five containers on a VM or a computer. Now I will connect to port 1401
13:27
for this specific container to get to the specific container instance that I just ran
13:32
The volume becomes very important. And we'll talk a little more about that later, but this is where you're gonna be storing
13:38
your databases and files to ensure something called persisted storage because if the container
13:44
went away, you would lose your databases if you didn't do this. And we'll talk more exactly what
13:47
that means. But this name SQL volume, Docker is going to map that to a known directory on your
13:55
host or your VM system where these are going to be stored. But everything inside the container is
14:00
going to be in this directory, which for Linux is where we store typically the system and user
14:04
databases. Now I'm going to use the host name parameter here because the host name is going to
14:09
map to ad app server name. That's kind of convenient, especially when you're doing things like link
14:13
servers or distributed transactions. Now the name parameter is simply a convenience to use with say
14:19
like the Docker clients. When I use other Docker commands, I can reference this container by a known
14:25
name that I use versus like a GUID that would be the default for what Docker provides. Dash D is an
14:30
interesting one. This says run the process for container in the background. Now, if you didn't
14:35
do this, it would be an interesting exercise if you ran this without this on your own when you try
14:40
these labs out. Because what's going to happen is the SQL server process is going to run as a
14:45
foreground process, and it's going to dump out the error log on your screen, which SQL is designed
14:49
to do as an engine. So we put this in the background so that you don't dump that information out
14:54
but it could be an interesting exercise for you to debug SQL if you had a problem running the just take this off and see in the error log entries if there was a problem And then finally the last piece of information is what is the image to run this container
15:07
For SQL Server, notice the nomenclature here. And then we have something called a tag
15:11
You'll notice here the tag says MS SQL Server and then some long thing
15:16
which is like the version, the cumulative update, and what operating system is part of this
15:20
And again, I pointed you to the Docker Hub and Red Hot Container Catalog where we list out all the different instances of the images you would want to use
15:29
So in this case, when I run this container, I'm going to be in a pre-installed version of SQL
15:34
That is SQL 19 based on cumulative update 6 based on Ubuntu 18.04
15:40
So that's the specific need I had in this case. So that's how the basics of how to get going and to run a container
15:47
Now, in a second here, I'm going to show you a couple of things. And before we do a demonstration, I'll stop and I'll see here whether there are some questions
15:54
How do you actually update a container? How do you update SQL Server in a container
15:58
You now have installed this container, but you're not sure what to do. Well, first of all, just interact with it like a SQL Server
16:05
Use your normal T-SQL, an app to modify data. It's just a SQL engine. We're going to talk about persistent storage in a second so your databases are all around
16:12
And if you want to do a backup restore, I'm going to show you an example where you can take a database backup file
16:18
and copy the file into the container file system and then restore it within that file system
16:24
And then as we talked about, and I'm gonna show you, we're gonna use something called a volume
16:28
to make sure your changes are not lost if the container gets removed. So that's just interacting with like changing tables
16:34
creating tables, doing normal SQL operations. What about configuration changes? That gets a little more interesting
16:40
Number one, I recommend you as much as possible use environment variables
16:45
which are typically stored in what's called MS SQL comp file. There's a link to how to do this
16:49
for a lot of different configuration needs, whether that be memory limits or whatever it may be
16:54
You could modify the container directly with SP configure and alter server configuration
16:59
known T-SQL commands. But remember, once you do that, you kind of lost the whole concept
17:04
of containers being consistent. So what I recommend more than anything else
17:08
is build a new image, a customized image based on SQL using scripts to use these kinds of T-SQL commands
17:16
to configure the container per your needs. And I'll show you an example of that a little bit later
17:21
So, and that's one of the huge promises, I think, of containers is this consistency concept
17:26
where you can customize things consistently the way you need it. So, for example, if you wanted to have a SQL server
17:31
for all your developers where max degree of parallelism is set to four or whatever you want it to be
17:36
you might build a custom image with a script that's going to do that. And the developers use that image
17:41
You know it's always the same across all your development, your development group. Okay, let's stop for a second before we do a demo and see what kind of
17:50
comments we have here. Can you create an instance name in Docker? Server name, instance name? Oh
17:56
that's a great question. So there's no concept of named instances in SQL on Linux. We don't have
18:02
that concept. And in fact, containers are the way to do multi-instance operations. So we'll show you
18:08
a little example, talk more about that in a second. But that's the way you're going to normally use
18:11
containers is create several containers and each of them now represent an instance
18:16
And then the way you can refer to these as names, by the way, is use host name that parameter I showed you
18:21
or also use the Docker client name. So you can refer to them outside the container and inside the container per a
18:27
certain name, but there's no concept of a named instance in Linux, but that's okay because containers give you this multi-instance concept
18:35
Okay. Let's flip over and do a demo. Let's let's do some fun. Let's do it this way by using visual studio code
18:41
Okay. What I've got is I've got Visual Studio Code pointing to the GitHub repo with all the scripts I told you about
18:47
And up here at the top window is the first script to go learn how to run a container
18:53
Notice the syntax looks very similar to how I showed you in the slide
18:58
And I'll point out a couple things. I'm going to make sure that the name represents the specific SQL Server CU image I'm using
19:06
which is CU6 for SQL 19, cumulative update six, but all the parameters look the same
19:12
And here's the name of my volume, SQL 19 volume. That's gonna become important here in a second
19:17
So I'm going to, at the bottom down here, use the terminal window to kick off and run this
19:22
So let's do this. Let's go type in step one, and we're gonna run our container
19:27
Now, container images are pulled down from the internet into your environment
19:32
And there's all sorts of documentation how to do that offline if you need it on your servers. I have pre-pulled these because if I didn't
19:38
it would try to pull it from the internet. I would slow down our demonstration today. Notice how quickly this is run because I've already pre-pulled the image
19:45
I'm now running and guess what? I've already SQL's installed and running. That's how quick it can be once the image is actually pulled
19:51
The next step I'm going to do is I'm simply going to go copy a backup database
19:55
Notice here this syntax here. I'm going to use the Docker copy command here
20:00
See that? I'm going to take the local database backup, and I'm going to copy it into the container that I've just started and into this directory
20:09
var optima SQL. Let me scroll over so you can see that. Var optima SQL. What is that doing
20:14
That's taking the backup file on my local drive on my Windows laptop, copying it into the
20:18
container's file system so it can be used. That file system is backed up by this volume concept
20:25
which we'll talk a little bit more later about how does that work internally. So I know that my
20:29
backup I've copied in is persistent. So let's go run a script like this to go actually copy it in
20:35
So let's run step two. I've got these step by step for you. So you can see step one, step two
20:41
step three. It's already done. So step three requires a restore. Well, how do I do that
20:46
This is interesting. I'm going to do something really crazy. I'm going to use Docker to run
20:51
another program, another process in that container image. What is that container process? SQL command
20:57
because we install the tools, pre-install the tools inside your container image
21:02
So now I can actually go there and run this command inside the container
21:06
isolated environment as a separate program now as from SQL Server. But since it's part of this container
21:13
they're both in the same namespace. So they know how to talk to each other. So here I am simply running this complete restore command
21:20
because this is where I put the backup itself. So let's go run that
21:24
three, oops. And this is now going to do a simple restore
21:34
And this backup is based on SQL 16. So it has to get upgraded, but the engine knows how to automatically do that
21:39
since it's SQL 19. And again, this is no different than if I connected outside the container
21:43
which I'll show you an example how to do that in the next step. But I wanted to see you different ways
21:47
how you can interact with SQL. I'm going to run a program inside the container. It's out and it's ready to go
21:53
Anyway, this is an example of how to query it. Now I'm running SQL command as a Windows program on my laptop connected in the container
22:01
How do I connect? Well, I already told you. I said use port 1401 and this is the local host because it's just running locally on my
22:08
actual laptop itself. So I'm running first of all a query here to just select data from the database and then
22:13
I want to get the version. I want to get the version to show you how version updates and patching are going to work
22:18
So let's go run this one. And you see here I got results that are flashing across my screen And I can see now the version of SQL Server I running right here CU6 Right You can see I running running on Ubuntu 18
22:37
So what have I done? I run the container. The image is already pre-pulled. I run the container, and in seconds I'm up and running
22:43
I copied a backup into the file system that's part of the container image itself
22:48
Docker knows how to interact with that. I then ran a restore command using SQL command in the container, and I just ran a simple query
22:54
against it. I could have taken Management Studio on my laptop and done the same thing as these queries right here
22:59
But for purpose of automating this, I'm just showing you SQL command. I could use Azure Data Studio as well
23:04
That's as easy as it gets to start SQL Server and start running it
23:08
We're going to see in a second here, after I show a few other slides, how do I update this
23:12
How do I patch SQL Server? Because I said no patching required. What's the magic of all that
23:17
We'll show you in a second how that works. Okay, back to our deck here
23:21
Let's go back over and see if there's any other questions we've got. Okay
23:26
So is containers mainly for DevOps or can they be used for production workloads? Wow, what a great question
23:31
Checks in the mail. So I showed you just a basic sphere of using Visual Studio Code and I talked about DevOps
23:37
But remember now, the concept of DevOps is that it is now a production kind of thing, right
23:42
But I also talked about how containers can be used in development environments. I'm going to end today's session with a slide to show you that containers for SQL are ready for primetime for production
23:50
So we'll explain more about that later in the talk. Okay, now let's move on and talk about what I just kind of mentioned about multi-instance
23:59
So number one, whenever you run multiple instance of SQL Server, a lot of people like to
24:03
affinitize CPUs or use resource governor, those kind of things. You can use different container runtime options like CPU set or weights and quotas with Docker
24:14
Docker supports that. Or you can use SQL commands to do it yourself
24:18
I recommend you use SQL commands. Use the power of the SQL engine that we've already provided to
24:24
affinitize or control how SQL is running from a CPU perspective. Again, one way to do that might be
24:30
to use like a customized image of SQL. This thing is memory. Same thing. Remember I talked about how
24:35
you would use environment variables? Like I would use the memory limit MB option as an environment
24:41
variable to restrict the memory for a container, but you can also use Mac server memory, fully
24:45
supported, you would again maybe potentially use a customized image of SQL with a script that does
24:50
that for each instance that you're going to run. Why do I say this? Because a SQL server container
24:56
instance, if you have multiple, are all going to use the maximum resources that are provided
25:01
in the host, whether that's a VM or bare metal. And then finally, networking is interesting
25:08
By using the host name, you automatically have some things set up for you. And like a Docker
25:14
environment provides things like bridge networks to allow you to communicate between these instances
25:20
But there is the ability to use what's called a user-defined bridge network to do name resolution
25:25
Very helpful for things like replication and DTC. I actually have an example of that. I'm not going
25:29
to go through that example, but in the workshop, I'll show you just briefly where you can get the
25:33
scripts to see how that actually works. Okay, what about this idea of patching? Like I said
25:39
you don't have to patch your SQL server anymore, and you're like, I don't believe you. What do you mean. Because think about today in Windows. Today, you install SQL Server 19 general availability
25:47
Now you get a cumulative update that comes along from Microsoft and you want to put on that update
25:51
Well, what do you do? You install the cumulative update. And then the next cumulative update comes
25:55
along and then you go install that one. So what you're doing is patching the software
26:00
We don't do that with containers. You just use this switch method. So first of all
26:04
consider this scenario. You've got what's called a volume, which I've kind of showed you how you
26:07
might use when you run a container. This is persisted storage of your databases. Because
26:12
each container image has their own private file system. And if they were removed, you'd lose it
26:16
But if you use a volume, you're redirected to this known space. So let's say the SQL 17 CU8
26:22
container is up and running, pointing to these databases. And you'd like to move to the latest
26:27
cumulative update. Well, how do you do it? So number one, what you're going to do is you're going to stop the container for CU8. The databases are still here, but you're going to effectively
26:36
shut down SQL Server. And then very quickly, what you're going to turn around and just run
26:40
the latest container see you based on a new image. And what you're going to do is point it to the same volume
26:46
with the same port and everything else that I showed you. And now within seconds, you've updated SQL Server
26:54
That's your patch. It's called the switch method. But you're asking yourself, well, that's really interesting
26:59
Can I go back? Cumulative updates are compatible between each other. So guess what you can do
27:04
If you don't like the results, you could shut down this container and then restart this container
27:09
And now you've rolled back. that's how fast it can be, or you can switch back and forth
27:13
So when I show this in action, which I will in a second here, people are amazed and don't think I'm like lying to them, but it's true
27:19
And one of the ways you can get away with this is the fact that I'm using a volume where the system databases and user databases are
27:25
and I'm using cumulative updates between each other. Now, you can upgrade based on containers
27:30
Like I could start SQL 19 and point them to these databases here
27:35
and now I've done an upgrade, which will take longer. but because major versions are not compatible between each other
27:41
I can't go back at that point. So there'd be no way to switch back to SQL 17
27:46
but it does provide a way to actually do a very simple upgrade in line. Now, again, this is just the SQL server software
27:52
If you wanted to take databases and go between them, you can take a database from SQL 17 and apply it to the SQL 19
27:59
and we would upgrade that automatically. Of course, you can't restore that backwards. But again, this is just talking about the instances themselves
28:06
Okay. So let's talk more about these volumes because it makes it's really important for you to understand, you know, what do I mean by these volumes and databases going away or not going away
28:16
So assume for a second there's a SQL Server container where the databases themselves are kind of contained in the private file system of that container
28:26
But since I've used a volume on host storage, again, the container runtime will take care of all that for you
28:33
Since I've got this host volume, the files are actually pointing in this host storage directly
28:38
Now, I've got a SQL Server 2 container that's not started yet right now pointing to a separate set of files
28:44
Okay, let's say, for example, I stop SQL 1. I stop SQL 1 right here
28:50
And then I start SQL 1 again. No difference. I just started I'm just starting and stopping SQL server. So there's no differences that are happening here
28:57
OK, now let's say in the case of SQL two, I stop SQL one or the case of SQL one
29:05
I stop SQL one, but I then remove the container. So what happens
29:09
I stop the container. I remove it. And notice here that when I do that, the files are also removed as well
29:17
Now, again, this is a case where if I go back here, so let me go to SQL 2 and show you that behavior
29:25
So let me go back here, back to this. Again, this is an example where the container is not using persisted volume
29:32
I'm sorry, I did not explain this clearly. This is SQL run running a container where the host is keeping the container runtime file system
29:39
but it's not persisted because I'm not using this volume concept. So when I stop the container and remove it, I actually do remove all the files because this host storage is backing up the container temporarily
29:51
But if I go do this concept where I store the databases on a host storage volume now, okay, host storage volume, and then I go store
29:59
up and remove the container, what happens? The databases are still there
30:04
This is why using volumes are so critical to using in containers
30:09
Now, if I go back and look to scenario, and then I stop SQL 1 and start SQL 2
30:14
you're going to see what the behavior looks like, just slightly different here. Ah, look at that
30:19
I can point SQL 2 to the same volume. That's what I showed you in the switch scenario
30:24
Because I'll use host storage volumes, notice that the databases in the case of host storage
30:29
are not stored over here. These are the temporary file system spaces
30:33
for SQL 1 and SQL 2, not the host storage volume space. Even though the host is storing them
30:38
think of that as temporary storage spaces only for the lifetime of the container
30:42
So for SQL as a product with persistent needed storage, I always wanna use this host storage concept
30:49
Okay, so let's now go look at what this looks, let's over, based on our time right now
30:55
let me just briefly talk about this, but I also wanna get quickly into the demonstration. to show you how to go do an update. So I mentioned about this idea of customizing images, right
31:05
That maybe you want to customize with scripts and so forth. So you might ask yourself, like, how do I do something like that? So for Docker, if you use that runtime, there's something called
31:13
a Docker file, which is a declarative file where I can say, I'd like to build my own image. And
31:17
Docker supports that. You can build an image, then run the image. So I can go into the Docker file
31:22
and say, I'd like to use this as the base of my image. And I want you to, when you build this image
31:27
to copy a backup file into the image itself, to the snapshot
31:31
And then the program I want you to run is SQL Server as it's customized. So when this is done and you build this new image
31:38
you can call this whatever image you want. In that image will be the SQL Server software
31:42
plus your backup. Then somebody could actually just run restore at that point once the container is started
31:48
What if you wanted a more script type method? I call this the venue method because my colleague venue from Microsoft
31:53
is the one that showed me how to do this. So here is a Docker file where I'm doing the same thing as before
31:58
But what I'm going to do is I'm going to copy a series of scripts into the image now
32:02
And that script is going to be what's going to be run. Not SQL Server. The script is going to eventually run SQL Server
32:08
So what happens first is this. The entry point script will run two programs
32:14
It'll run this shell script and SQL Server asynchronously. Okay, that's what that amperset means
32:20
Then the db init script will wait for SQL to start and then run, because SQL command is installed
32:26
inside the container a SQL script. The SQL script can be whatever you want
32:31
In the example that I have in our workshop, I show you how to actually run a replication environment
32:36
transactional replication, using this method with scripts that build the publisher, subscriber, et cetera
32:42
But this script could be any T-SQL set of commands. It could be SP configure. It could be creating databases
32:47
It could be creating tables. So think now of using this method to build a customized image for your developers
32:52
on a certain version of SQL with specific SQL configuration options that you want
32:57
and with all the SQL commands you'd like them to use. You could provide them a complete shell database
33:02
for example, based on exactly the schema you wanted them to test on
33:08
Okay, and then there's ways, by the way, in this same process to add other packages
33:12
that we have for SQL Linux, like Polybase, machine learning services, et cetera
33:18
Okay, so let's stop for a second and look at our comments and go back to the actual example
33:23
I'm gonna finish with here. Okay, so let's see here. What is the presentation software website that he's using
33:31
I'm not sure what you mean by that. I'm just using PowerPoint. And of course, Simon is providing our environment here
33:38
that we're using for StreamYard. What do you recommend for separation of the files
33:42
or for the backups? Yeah, great question. Same concept will apply for production environments
33:47
And you have the ability for containers to have separate volumes. So you can have separate volumes to store different files
33:53
in different locations depending where you want that host to go. And the containers certainly support that
33:58
Where do you get a link to the sessions that this session provided I gave you at the start of the presentation where this talk is but the last slide I have also has a resource to that How do I start using C Sharp
34:11
I'm going to let Simon answer that for you. And then does the SQL Docker version
34:14
have the same function as a regular SQL server like Polybase? Great question
34:19
The SQL server container images we have right now are the same SQL server engine as SQL for Linux
34:25
So whatever Linux supports for SQL, we support that. And I just showed you in the very last slide there that the SQL Server container images contain the engine and the tools, but there's ways to install the other packages with your container image, including things like PolyBase
34:41
All right, let's go do another demonstration here. Let's go back to Visual Studio Code
34:47
So here is the script to patch SQL Server. I'm going to run it first, and then I'm going to talk about it
34:52
So let's go run step five. And what is it doing? So first of all, it's stopping the current container
35:01
It's running another one. Look at that. Same port, same host volume storage, different host name, different name, and wait for it
35:11
different image. This is the CU8 image. So I'm stopping the CU6 image
35:16
I'm starting with a new image with CU8. So the CU6 container is not removed, but just stopped
35:24
The CU8 now starts up, points to the same databases, and since we're compatible, we handle everything for you in between
35:31
So this is running, and it's already finished. So what are we doing in step six
35:35
Step six, and I won't do this for you, but it's an example of how to run a different container
35:40
Let's say that I'm running this CU8 container that's patching SQL, but I want to run a different one
35:45
Well, how do I do that? This is a general availability release of SQL 19
35:50
Notice a couple things that are different. a different port and a different volume name
35:54
I want to separate a different instance of SQL here than the one I'm trying to patch
35:59
This is an example of how to run a second instance. This is a command just to show you
36:04
what are the containers running in my environment? Let's go run that one. Step seven
36:09
This is interesting when I run this. When I run this, I'm going to see a couple of things here
36:13
I'm going to see, first of all, I'm going to see the CUS8 image
36:18
which has now been running, But notice that the CU6 image was exited
36:22
It just means that the container was stopped, not removed. So the reason why I didn't remove it is I might want to switch back
36:29
I could go down here and inspect volumes. Let's go run a query and see what we get here
36:33
Let's go run step nine. Again, you can run some of these other steps if you'd like to after the fact
36:39
This is just running a query. And notice it's running against that. But when it's finished, what do we see
36:44
We see now that we've updated to CU8 right here with the same SQL server, the same data, the same database
36:51
That's how quick it is to patch SQL server. So, and again, I didn't call it patching
36:55
I'd call it switching or updating. And so if I wanted to roll back, I would simply do this
37:00
I would stop the current CU8 container, not remove it, and then start back the CU6 one that I didn't remove, but I just stopped
37:07
So let's go do that. So we're in step 10. And now I'm in the process of rolling back
37:14
Now, if you're asking yourself, like, do I get to see things like error logs with containers and things like that
37:19
Look at step 11. Step 11 says go run like I did SQL command before
37:24
Go run another process that is in the same namespace as this container, but use the bash shell and make it interactive
37:32
So look at this. This is kind of fun to do. Let's run step 11 here. Now I am put with a bash shell prompt. But, Bob, you're running PowerShell
37:41
No, I'm actually put forth a program that Docker supports running in my namespace as a bash shell
37:47
Now I'm running in the container itself. So I could say var opt msql log, that directory, and I could do this
37:56
I could look at the error log. Looks pretty familiar, right? That's the same error log as YouTube and SQL server
38:03
This is the error log of the SQL container I just rolled back to So it looks like recovery is complete It done So I now can do something like this I can go back and run step 14
38:16
Well, I'm in the Bash shell still. So let's exit out of there. I'm back to PowerShell now. And now I'm running my query again
38:24
And when the same database I ran a query again, so it's consistent. And now I'm running at CU6
38:30
Simple rollback of what I did. I updated my container. I patched it to CU8
38:35
I stopped it. I started CU6 pointing to the same set of databases
38:39
because cumulative updates in SQL Server in the same major version are compatible with each other
38:45
That's as easy as it gets. And that's really an amazing way to be able to patch
38:50
or update the SQL Server version of the software. Okay, let's look at some other questions here
38:55
before I go back. How do you create Docker container for SSAS, IS, and RS
39:00
possibly in Linux? So it's a great question. We do not support native versions of ysis services or reporting services for Linux today
39:09
We do support SSIS, though, the DTE exec program, and we have some documentation about how you can create a container image for that
39:18
Are you running an admin mode with Azure Data Studio PowerShell terminal? I'm just running the terminal on my local laptop, so I'm not running any special mode there
39:27
I'm just using actual Visual Studio code. But great question. I could have done this in Azure Data Studio
39:33
I could have used Azure Data Studio as my environment, and ran a terminal inside it
39:37
and ran queries in Azure Data Studio as well. That would be a great suggestion for the future
39:41
I do like using Visual Studio code for just raw developers, but Azure Data Studio is a great environment where it
39:47
supports a similar environment just like this. It does support PowerShell notebooks
39:52
Another way to do this is to take these PowerShell scripts, put them in notebooks
39:55
and then run them from that perspective. Let's go back over to our PowerPoint presentation
40:01
We've got six minutes left, a little bit pressed for time, but that's okay. If you think about it, let's just stop and think what I've done, right
40:07
I've talked about the power of containers, why you want to do it. I've talked the basics about how to get these things up and running
40:14
I showed you some examples of how simple it is to do that. And then I showed you this idea that's pretty cool of running multiple SQL instances as containers
40:21
but even this amazing story of how to go patch a SQL cumulative update without truly patching the software
40:27
In fact, that is the world you're in with containers now. Don't patch anything. Just run a different container image and point it to the persistent volume storage that you already have in place
40:36
OK, so this question was asked earlier. Are containers ready for primetime
40:42
Remember, a container for a SQL Linux image is just a SQL Linux process run in an isolated manner
40:48
So I have seen this stated before when I was on the road, you know, the last year
40:52
Oh, I was on the road last year, maybe the year before. But just in general, I've seen this from the community
40:58
Containers aren't as fast as SQL Server. I'm like, that's not true. If you're running SQL Server on Linux as a Linux image
41:05
remember that first slide I kind of showed you earlier, it's just a Linux native process
41:09
In the book that I wrote, SQL Server 2019 Revealed, I show you a test of using the popular open source tool, HammerDB
41:16
A TPC-H query runs 100% the same on a SQL Server on Linux
41:21
as a SQL Server container on Linux. Again, if you're running in a SQL Server Linux container
41:27
on a Windows machine, that's not fair to compare that to SQL Server on Windows
41:31
because there is a slight virtualized process there. Now, it may be fine for your needs
41:36
but I would not run that as a production workload. I would make sure that if I need to run SQL Server containers
41:41
in production, that would need to be on a Linux system, a VM or a bare metal machine
41:46
But for development purposes, it's perfectly fine. Now, SQL Server 19, we run our containers as non-root now
41:53
This is something that we needed as a requirement to run environments like OpenShift in Kubernetes
41:57
So if you have concerns that our container images run from root permissions, they don't anymore
42:02
That's a change we made in 19. And then I always get a question about licensing
42:05
How does licensing work for containers? It's the same as virtual machines. In fact we have a specific section in our licensing guide for SQL 17 and 19 where we talk about exactly how containers are licensed for how you purchase SQL Server But keep in mind this if you run developer version
42:21
of SQL Server with say Ubuntu, everything's free. So you as a developer can be out there
42:25
developing for free on your MacBook, on Linux or Windows with no charge at all using SQL Server
42:31
Ubuntu-based images for developer edition, and that's the default. So when you run a SQL Server
42:37
container, the default is developer edition. You have environment variables to specify
42:41
if you want to run in production, that it's enterprise edition. Now, consider
42:45
this about production workloads. Most production workloads for containers are going to gravitate towards Kubernetes
42:51
which is a scaling environment to run containers. In fact, our big data cluster
42:55
solution, we have a SQL 19, deploy some 50 plus containers, including SQL server in multiple instances
43:01
to actually make that solution happen. We just recently announced Active Directory Authentication Support
43:06
for SQL containers. So if that was something that was blocking you from using SQL containers
43:11
we've removed that limitation and we have documentation how that works. And here's just a couple examples of customers
43:16
I've worked with that are running in production today. DV01, one of our early adopters of SQL Linux
43:21
and Balzano, which happens to be running big data clusters in Kubernetes today
43:26
And it is an example of the power of SQL could be ready for you for containers for production
43:32
Okay, quick takeaways. They're portable, they're lightweight, they're consistent and very efficient
43:36
Think about this idea. Maybe containers for you can become database containers. Maybe it's no longer thinking in terms of it's a SQL server thing now. You build a container image with your database, with your scripts, with the SQL server image you want, with everything you need in it. And you just give it to a developer and say, hey, there's your database. Go start using that now. And not worry about all the intricacies of the SQL instance itself
44:00
Now, we talked about deploying. We talked about how they support DevOps. We understand how they work now, this new way of patching
44:07
And then I mentioned exactly that I think they're suitable for production environments. Keep it in mind, a SQL Server Linux container is the same process as running SQL Server
44:15
on Linux itself. It's just run in an isolated way. Here's some resources for you
44:19
Shameless plug for books, right? There's a book on Linux I've written in 2018
44:23
And so, in 2019, I wrote a book. and there's an incomplete chapter of how SQL containers work
44:29
including a lot of stuff I showed you today with more that you can go take a look at that. Here's all the references
44:34
This is where the deck is. This is where you go get all the demos I just did. More demos here, more demos from the book
44:40
and then great talks you can take a look at. Here's a great talk that Vin, you did
44:44
at Ignite a couple of years ago on how to do containers with DevOps. And then here's the video link from build 2020
44:51
where you can see me show you exactly how to use Azure DevOps with SQL Server
44:57
And then here's a great book, just in general to look and see how to get up and running on Docker
45:01
It's a great just general resources on how to use containers itself. I really appreciate your time today
45:07
Let's see if there's any other questions here. Have some documentation for AD authentication containers
45:12
Yes, Wilson, once you get this deck, if you go back here and look, I've got a specific link for you
45:17
You can click on right here and it shows you exactly how to go do that
45:21
and what about Podman from Red Hat? We certainly support the Red Hat environments
45:27
We prefer that what people use with Red Hat today is like an OpenShift environment
45:32
I don't know specifically all the details about using Podman, but just to let you know
45:36
our images are compatible with running Docker as a client, but they're also compatible
45:41
running other container runtimes because we support SQL Server running in Kubernetes
45:46
And in many of those environments don't use Docker, they use other type of environments. So, hey, thanks so much, Simon, for your time today
45:52
That was certainly a fast-paced look at SQL on containers. But I think hopefully everybody here got the idea of the power
45:59
where they can provide and the value they can be for both developers and every professional out there that wants to use SQL
46:05
in this new way to deploy and run SQL Server
#Enterprise Technology