Andrew Pruski: Pods & Pages: Databases in Kubernetes

This talk covers the deployment and management of databases in Kubernetes from a DBA's perspective, specifically using SQL Server as a reference point while remaining database agnostic. The speaker discusses the three main areas of responsibility for DBAs, which are availability, recoverability, and performance. He highlights the benefits of running databases on Kubernetes, such as high availability, easy scaling, and efficient patching. Live demos demonstrate deploying SQL Server in Kubernetes using stateful sets, persistent volumes, and implementing health checks. The importance of proper backup strategies and testing restore procedures is emphasized, along with the use of volume snapshots for recovery. The session concludes with a discussion on chaos engineering and the significance of testing systems for resilience.

Good afternoon. Yes, good afternoon everyone and welcome to this session pods and pages in Kubernetes. [snorts] Just a little bit about myself, my name is Andrew Prosky. I'm a principal field solutions architect at Everpure. Um before that though, I was a SQL server DBA for around 15 years for my sins and that's what we're going to be talking about today. I am going to use SQL server

as a basis for everything we do. But everything I do today and everything we talk about today is going to be database agnostic. So this applies to Postgress, MySQL, Oracle. Um I'm a Microsoft data platform MVP, Docker captain, Redgate Ambassador, VMware expert, uh general allround SQL Server nerd is what you should get from that. Um I'm originally from Swansea in Wales, but have been living in Dublin

Ireland for the last 13 years. just moved there, thought I'd do two years worth of work, get out, went over, stayed, never left. If you've never been to Dublin, highly recommend it. Um, we have questions via the app thingy, but if after today you have questions, please feel free to reach out to me. I'm always willing to talk about this stuff. My contact details are on the

slide there, dbftheold.com on Blue Sky. We don't talk about the other social media sites and my email [email protected]. I'm also on LinkedIn, Andrew Proski. You can message me there as well. Um, my blog's there as well, dbftheold.com. Posted multiple articles about running SQL Server and Kubernetes, containers, all that jazz. You're more than welcome to check that out. And then finally, my GitHub account, all the slides and

the code for the demos that we'll be doing today. I had a speaker yesterday say, um, I'm not doing any live demos because I'm not a maniac. I am a maniac. We will be doing live demos and all the code is in the repo which I will post the exact link to at the end of the session. So on to the session. We're going to explore how

databases can be deployed and operated in Kubernetes. Now I have to admit this session gave me a little bit of a headache because let's be honest this there's a whole day worth of topic here. We could talk about this all day and I can talk about it all day and I will if you let me. But I'm very aware this is the session before lunch. So we

will cap it at 40 minutes. But what I wanted to do is for this session we're going to approach it from a DBA's perspective. Have we got any DBAs in the room? We got a few. Cool. Well congratulations everyone else. Your DBAs for the next 40 minutes. And what we're going to do is we're going to approach this from a DBA perspective. We're going to have a

look at Right. This is controversial but the three main topics uh three main areas of responsibility for a DBA availability recoverability and performance. Now there are others but I believe those are the three main ones. But before we get into that before any of that why why would we want to run databases in Kubernetes? Um so let me tell you a story. Way back when 2017, I've

been doing this for a while. I was working my first job in Dublin and my head of QA came to me and said, "Andrew, we've got a problem." I sort of said, "Okay, what's the problem?" Every month they revamped re refreshed their QA environment. And that meant blowing all their machines away, rebuilding virtual machines. And those virtual machines had the full software stack and an instance of

SQL Server on them. This meant reinstalling SQL Server in all these VMs. Now, this process, it wasn't exactly the most reliable process. Took anywhere between u sorry it wasn't the most reliable process. It was prone to failure and you have to dig through all the logs for work out why it failed. And it also took a long time. It took anywhere between 45 minutes and an hour

per VM just to get SQL installed. So, we decided to blow all that away. build a host run in Docker and get rid of the SQL insource. What would happen instead now? Oh, my monitor's gone. Okay. [laughter] What would happen instead now? Instead of installing SQL, that VM would go off, hit the Docker host, build a container from a known image that would have all the databases

ready to go based on whatever branch they were working on in depth. We timed this process. The longest it took us to get an instance of SQL Server up and running in a container with all the databases and some test data ready to go was two minutes. It's a massive saving. Actually, we worked it out. Well, the head of QA worked out. Saved a month's worth of

dev time per year. However, there was a problem. We were just running Docker on a box. One day, I came into work and I was the most popular person in the office. Anyone guess why docker box had gone down? None of the QA people could do any work and so everyone's standing around my desk going, "Is it fixed yet? Is it fixed yet? Is it fixed yet?"

And as people who know me know that I'm a really calm and patient person really did a lot for my blood pressure. If I'd had Kubernetes, it wouldn't have mattered. Everything would have failed over to another node. QA could have done their work and I could have fixed that download in the background and that is where Kubernetes comes in and shines. Gives us that high availability. Now

it gives us a whole bunch of other features as well monitoring checks things like that but this is what we're talking about when it comes to running databases in Kubernetes. We get that high availability. However, I am really going to hold my hand up here and say, look, if you need a multi-terabyte database with hundreds of thousands of IOPS throughput, is this the right platform? Really think

about it. It is another platform. It brings benefits of that platform specifically for that tailored use case. What do you want to do with it? Now, I've got multiple different use cases, but we're going to assume for the rest of the session that we do want to deploy a database to Kubernetes because we want to take advantage of that high availability, the easy spin up, the easy

patching. Who here has ever tried to uninstall a CU for SQL Server on Windows, fun times, pain? No, we can just blow the existing one away, roll back to a later version in Kubernetes, roll back in seconds. And that also gives us a completely different mindset when it comes to working with databases in Kubernetes. Now us all of us as DBAs are used to treating computing data

as the same thing. We got a physical machine, we got a virtual machine. We look after it. We whisper to it. We stroke it. We patch it. We fix it when it breaks, when it's ill. You don't do that with containers. Something's wrong with a container. I'm sorry, Susan. We blow it away. We spin up a new one because do we care about the compute? Do we?

No. We care about the data. That's all we really care. As long as something can come along and access it, that's all we really care about. It's just a different mindset for me, especially when I started working with this technology. I don't care about the compute. Something comes along, access it, great. I don't care about it. Oh, it's broken. Bye. Build me a new one. Spin me

a new one. Okay. So, let's talk about the three main responsibilities of us as DBAs. Now, I say this is controversial because if you ask DBAs what is their main responsibility, what's the first thing they're going to tell you? Backups. Who said backups? Yeah, they always say backups. No. Availability. If our S in instance isn't up and running transacting, we might as well all go home. Our

first responsibility is to keep that database up and running. The second responsibility, recoverability. Notice I didn't say backups. It is how can we achieve our recovery point objective within our recovery time objective. Now backups are the main way of doing that. But there are other technologies available to us depending on what platform we are running on and we can explore those options with and I well if

I had the ability to put a massive asterisk there I would with caveats and then finally are we getting the necessary performance out of our databases on that platform where is the bottleneck there's always a bottleneck where is it. Is there something we can do about it? How does this platform compare to previous platforms? How can we compare? So that's what we're going to talk about. So

the first one, availability. Is it me or is it really warm in here? Oh, it's me. All right, fine. Okay, availability. So how are we going to deploy? What high availability options are available to us? Are we just going to be one in run one replica? Now, if we are going to run more than one replica, do we need to deploy an operator? If we're running one

replica, then we have to rely on the native Kubernetes high availability and there's tweaks that we need to make in order to ensure our SLAs's. I was kind of in a bind when I first worked with SQL server in Kubernetes because there was no operator for say availability groups. You can deploy availability groups but you don't get any automatic failover. And what's the point, right? There is

now an operator. However, if we use operators and if I started talking about operators in this session, we would start getting into database specific stuff. SQL, Postgress has got multiple operators, MySQL, Oracle, all that jazz. So operators can provide database high availability. So such as replication, automatic failover, automated backup and recovery processing. for this purposes, for this session, we're going to assume we're going to availability. And

what can we do there? So one replica. So one replica. Are we just going to deploy a single pod? Create whatever is run pod blah blah blah. No, we're going to use a controller. If we've only got one replica, what controller are we going to use? deployments stateful sets. we're going to use a stateful set. It's built for stateful applications and databases, let's be honest, are kind

of stateful. We're going to use a stateful set. They provide that stable identity and the storage for us all wrapped up in that one manifest if we can automatically generate persistent volumes from PPCs. So even though okay we can do a deployment deploy there one replica whatever I would always recommend using that stable set mainly for my own sanity with a stable pod identity but anyway always

use a stateful set services. Okay y services provide our stable networking endpoints. Um where are we connecting from? Are we connecting from just inside the cluster? Is it internal traffic? And that brings a whole load of other considerations. Are we just running databases in this Kubernetes cluster? Probably not. However, are we going to run applications and databases on the same nodes? Really, databases are by definition resource

monsters. If you let them, they will take all the resources available to to a node and suck it up, starve all the other processes. So, are we splitting out? Are we going to have a separate cluster for our applications? We're going to have a separate node pool for our applications. And do we need external access? I would recommend no. Has anyone here everyone here used Azure Kubernetes

service? What's the default when you deploy a load balancer service? Just create a load balancer service for to map to a pod. It'll expose it to the internet. Has anyone here ever exposed SQL server on its default port to the internet? I have. It's loads of fun. We'll do it later. Yeah, we need to. So these are things we need to consider. And that brings me to

the point when I mentioned the three main core responsibilities. I left one out and I've left it out deliberately because it could be a whole other thing. So we have availability, recoverability, and performance. But there's another one, a giant one looming over us. Security. Yes, databases contain sensitive information. We need to make sure they're secure. However, 40 minutes, I can't go into everything, but services, where are

we accessing from? Are we exposing endpoints of our database? Are we basically having a breach of data security? Things we need to consider. Cool. Okay. Now, when we test things, and we're going to run through some tests in the demo in a minute, but one of the most insidious ones is not the pod dying, but just the service dying. So we need to double check things. So

we can use probes to do that. First one is the startup probe. SQL server still starting readiness probe. Is SQL server accepting connections? Honestly, never really used them. You can use I'd use them when I have say startup store procedures in SQL that are configuring my instance and I want wait for them to finish. Then I can use startup and livveness pro readiness probes. But the main

one I use is a livveness probe. is the service within the pod still running because SQL server doesn't run as PID one or whatever in in the pod. It runs as a child process. So that can freeze up and die and the pod will stay fine. So I need to use a livveness probe. So should SQL should Kubernetes restart the container for me. However, bad livveness probe

can become a self-inflicted outage 100%. Uh, can we shoot ourselves in the foot with this technology? Um, yes. One, we can very much shoot ourselves in the foot with these types of things. We need to double check. We need to test. If you get anything out of this session today, the one thing you should be leaving with is, are we going to deploy? Yes. Great. Get it

up and running. And what are we going to do next? We are going to test test test and test and then we're going to test some more. That's the only thing. If you leave with nothing else. Cool. All righty. So, we got our one replica. What happens when say a node dies? Node dies and the things will move over, right? How long by default does it take

for Kubernetes to move a pod from a down node? Who said five notes? It is five. It's five. I think it's five now. It used to be. Yeah. Is that good enough? Thank you. It depends. It depends on our SLAs's typically. No. So, okay, we need Kubernetes to move those down those pods off that download node in a short time. We can do that via now we

can do it clusterwide but I'm a control freak and I like it at a more granular level. So I use tolerations within my stateful set manifest. So I use unreachable and not ready. Uh 10 seconds remember this is 10 seconds after the node reports as not ready or unreachable. So it's not exactly 10 seconds. Now that might be too aggressive. And guess what? What do we do

if we think it's too aggressive? We test test test. Shut a node down. See what happens. Cool. All right. Recoverability. I have fun time making my slides anyway. Right. Backups. Backups. Us DBAs love our backups. We we have a whole bunch of uh tools out there for our backups. I have a saying that I talk to customers with and it's you can take my backups out of

my cold dead hands, right? This is just another platform to run SQL Server on. No matter what you what things uh people tell you about this platform, oh, it's highly available. We can use snapshot. We can do this. We can do that. Back up your databases. I'm not kidding. take those backups and then get them out of that cluster as soon as possible. I usually deploy a

PV to back up to and then copy the backups off that PV however whenever I want to do it. Um I used to work a job in Bristol and when I first started they were running SQL server on a load of VMs and they were backing up locally saying great we do this because we can fast recover if some data gets deleted or corrupted whatever wonderful stuff.

First thing I did, turned the VM off. What are you going to do now? HOW YOU LIKE ME NOW? AH, so we started copying the backups off. Get them out of the cluster as possible, fast, as fast as possible. And then we need to be testing restores. Um, when I first started as a DBA, I went to London to go on a training course and a guy

running it, I guy called Paul Randall who wrote a lot of the SQL Server database engine told a story, but a bank in America, not Bank of America, a bank in America. And they were taking backups every month. They got full backup of their database every month. It was a monster database for the time. Took one full backup a month and then just transaction log backups every

15 minutes. At the end of one month, they had database corruption. They needed to do a restore. So, they restored their full backup and then had to restore thousands of log backups. Took three days. It's nearly the end of the bank. Yeah, they've been backing up, but they hadn't tested their restore strategy. Could they hit their recovery point objective within their recovery time objective with their current

backup strategy? So, we need to be testing this. Okay, cluster goes down. What do we do? Can we get access to our backups? Are they somewhere that we can restore from easily? Can we get our databases back up into an instance anywhere? Doesn't even need to be Kubernetes. Any sort of instance that our applications can access. If we are, if we can do that, great. If we

can't do that, we need to work out why. And we also need to put processes in place that if we're not available, if what who here right side side note, who here has ever installed a system, maintained a system, looked after that system, troubleshooted the system, has been the point of contact for that system on their own? Yeah. What happens when you aren't in the office? They

call you mobile. Call me on my PTO. No, we need runbooks and things like that. 100% that people can follow so that people who don't have that knowledge can restore their database. Cool. Anyway, that's enough about backups. I could rant about that all day. Cool. Okay. So, Falco, I told you I wouldn't get through an hour without saying the word snapshot. So, I work for a storage

company. Kubernetes provides the ability for volume snapshots. And I have heard people tell me that yes, these can replace native database backups. It really does depend. What type of snapshot are we talking about? Are we talking about crash consistent snapshots, just a snapshot of a volume at a point in time, or are we talking about application consistent snapshots? Crash consistent snapshots cannot replace your native SQL server

backups because you cannot do a point in time recovery with them. They are there to complement your backups. Anyone who tells you that they can replace them is telling porky pies. Application consistent snapshots are a completely different thing. Only those could be considered to replace a native SQL server backup. And again, there need to be considerations here. How portable is that snapshot? Um I work for people

who say, "Hey, we don't need to move them. Our our arrays have six nines of uptime. They never go down." And the only thing that goes through my head is single point of failure, single point of failure, single point of failure, single point of failure. Yeah, we need to be able to they need to be portable. We need to get them out of the primary system so

that if that system goes down, we can get access to them. If we can't get access to them, then they're not a I wouldn't consider them a backup. So, we need to consider how portable they Cool. All right. We have volumes. We're running our databases on them. They have things called volume reclaim policies and we have two options available to us. Well, three, but one of them

is deprecated and we'd never use it for databases anyway. Um, delete, remove the underlying storage and retain, keep the underlying storage. Um, anyone want to hazard a guess at what I would recommend? Delete. Delete. Yes. YOLO it. Nope. Seriously, this setting can be the difference between a normal operation and a very very quiet room. Seen it happen. Routain. Cool. Last one. Performance. How am I doing for

time? 20 passed. We got 25 minutes left. Thank you. Okay, cool. performance it's not rocket science. What? Get the fastest storage you possibly can. Uh, if you if it's not available, have a look at your IO profile pro. Have a look at your IO profile and see which one of your data files could possibly go on the fastest storage if it is limited. Follow the best practices

for database file layout. Exactly same. It's just another platform to run our databases on. So for SQL Server, you recommend separating out the user databases from the system databases. Data files from the log file, they have different IO characteristics. Sequential IO for the logs, random IO for the data files. Then you have things like temp DB, which is just an absolute dumpster fire of IO. Separate that

out as well. And then that also gives you the ability to take volume snapshots as well of individual databases. Maybe you want to separate out one into its own disk. So you can take an application consistent snapshot of that database whereas you can take normal backups of your other databases snapshots to complement test storage performance with realistic database workloads. Who here likes their synthetic IO tests like

disk speedometer crystal discark wonderful stuff. Absolutely continue taking them but do not just rely on them as a performance benchmark for your SQL or for your actually use ideally you would replay your production workload. SQL has the ability to do it postgress my SQL where you can capture traffic of a production database and replay it against a copy of that database anywhere you can have a look

at the different performance and then when you or if you don't have the ability to replay use a tool that generates some known benchmarks against your databases I recommend and it's in the uh readme for this session uh a tool called hammerd which allows us to run known benchmarks such as TPCC OLTP benchmarks or TPCH OLAP benchmarks. It's a really good performance tool. It's all containerized. You

control it through environment variables. So you can replay against one platform and then run exactly the same test against the database on another platform. It'll build the database for you as well if you wanted to. So you ideally have replayable workloads where you can capture transactions per minute here and And finally, utilize database engine tooling to analyze workloads. SQL has things like query store that will tell

you top resource consuming queries, regressed queries and give give you the ability to analyze execution plans 100% use that compare to the existing workloads. See what the differences are and then you can delve into why there are differences. Requesting limits. This is the noisy neighbor problem I mentioned right at the start. If you allow it to, SQL will gobble up all the memory and CPU on a

node and starve all the other processes. So, we need to be setting CPU and memory Also, we need to be aware of individual relational database quirks. And SQL has a real gotcha here. Um, as we all know, CPU memory limits or controlled by control groups, right? And memory limits aren't really a problem. CPU is an issue with SQL because set a limit for CPU in SQL and

we say limited to four and bear with me, we're on say a 96 core host just just a laugh, right? I know every whatever. Um, SQL doesn't see C four CPUs. SQL will see 96 CPUs but only be able to use four CPUs worth of them. This causes, as you may imagine, carnage when it comes to execution plans and the query optimizer. Now, there are things you

can do to mitigate that. I've talked to the Microsoft PMs about this and there is a fix coming apparently, but for now it's things like set CPU affinity and deal with it. Other things to be aware of is quality of service within Kubernetes. We have best effort. Ah, we'll give you some resources if we want. Burstable. Here's some resources. And you're allowed to burst above them. Guaranteed.

These resources are yours and yours alone. Anyone want to take a stab at which one I recommend for databases? Guaranteed. And it has other benefits as well. Things like least likely to be evicted from a node under pressure. Cool. Okay, I've got 50 minutes. Let's have a look at deploying SQL to Kubernetes. Can everyone see this at the back? Is it big enough? Oh, it is big

enough. Right. [laughter] Okay. So, I've got my cluster. There we go. Three node Kubernetes cluster up in Azure. This is just a demo cluster. Let's go ahead and deploy a storage class. So things I want to point out. Uh I'm actually just copy wrong button Andrew. Here we go. Provisioner CSR driver for Azure reclaim policy retain volume binding mode immediate and allow volume expansion. True. I don't

want to have to mess around redeploying just because I want to increase the size of a disc. Let's get them. We can confirm. Is it there at the bottom? Yep. Actually, can I make that? Is that still okay? Cool. All right. It's a lot easier for Uh, deploying a secret. Don't want to have our passwords in clear text in manifest files. We can use a secret. Lots

of fun with secrets because, as you probably know, they're not secret. There we go. Looks good. It's obstacated, as anyone working with Kubernetes will tell you, doesn't take much, and we can get at it. Secrets in in Kubernetes are encoded by default, not encrypted. So, even though they're better than nothing, they're not as secure as people can tend to think they are. Now, there are other options

out there, things like Vault, and I think you can on the API server flick a switch and encrypt your secrets by default. But just be aware, it's really easy to get at secrets. Sorry, it's just in rest. Yes. Yeah. So, there's possibly different options there. I'd recommend using vault generally and pulling out from there. Okay, so let's have a look. I've just deployed a stateful set to

Kubernetes. I'm going to scroll down and we've got our toler wrong bloody button. My zoom it skills. Uh we got our toleration set referencing our secret resources and aliveness probe. So we got our resources here and aliveness probe. Now this is a really simple livveness probe just running a simple select one testing if the SQL instance is up and available. Uh, one thing I will mention here

is this is incredibly aggressive. And the reason this is incredibly aggressive is because we're going to do a test with this in a bit. And if it was longer, we'd all be standing here going talk amongst yourselves. It'll do something in a minute. But that's why it's quite these values are quite so low. Then our volume mounts and our volume templates. Nice and simple. uh read write

once pod used to be read write once was the only option now we use readr runs pod because we have one replica accessing the discs we don't anything else trying to come and access it and I've tried to do funny things with SQL server in Kubernetes with a production pod and a standby pod that if this one dies this one will take over SQL don't like it

so it literally is just one pod on our volumes let's confirm our storage. Um the big long uh custom column stuff there is purely for display purposes, but we have our volumes, we've got our access modes, and we've got our reclaim policies. Excellent. So, we're up and running. Let's skip that and let's have a look at We should have a There we go. Q class guaranteed. Fantastic.

Because we set requests and limits to be the same values. Yeah, deploy a service. Now, I've already deployed these because I'm deploying a load balance service and get services. It can take some time to spin up a load balancer in the background and give me my external IP. So, it's already there for us. And basically, what I've just done is something I told you not to do,

and that's expose SQL Server on 1433 to So, let's grab that IP address. Confirm and create a database. Can we long enough to make me nervous and then create the database. Finally, There we go. We have our test database. So, you got SQL Server up and running in a stateful set with a load of persistent volumes behind it. We claim policy of retain. We've got some tolerations.

We've got some we've got a livveness probe and we've set a guaranteed class of service quality of service class of service. So let's go ahead and let's see if we can use volume snapshots to restore a database. Now what we're going to do is we're going to pretend that we've lost all of our backups. Our backups are gone, but we've been taking volume snapshots. So I have

them available to us in our cluster. Oh, I know I can press control L. It just does not fit in my head. I have to type clear. I can create a snapshot class using the disk CSI Azure driver. Confirm. And create my volume snapshot. And what I'm doing here is just taking a snapshot hello why not whatever [snorts] of the data file of the volume that's holding

my SQL server databases data SQL server databases data file Is that snapshot there? Now we got a ready to use of true. Fantastic. We can see it's events. Cool. So I have that snapshot and it's a simple matter now of create a PVC from that snapshot. Have I messed this up permanently or is it Where is it? Here we go. With my storage class. Ah, it goes

back. referencing read wants pod referencing requests all good. Uh excuse if I've got my PVC now. There it is. I can just deploy another staple set. And the staple set is exactly the same as the previous one. The only difference being I just reference the existing persistent volume claim. I should have the services already there so we can connect. Are my is my data file there? Fingers

crossed. Yes, there it is. So I have the data file. Everything was gone. But because I've been using snapshots, I could get some of my data back. Now it is just the data file because I took a crash consistent snapshot of that volume. SQL has the ability from SQL Server 2022 to take application consistent snapshots from within SQL and I was going to demo that to you

but I was working on it last night and when I was trying to ques the database the quies wouldn't stick and I can't work out if it's a SQL on Linux problem or a MI problem. And if I'm honest with you it's a me problem but I need to go and investigate further. So, I would do a crash consistent snapshot demo. So, we have the data there.

Um, any DBAs working with SQL? You didn't see me do this. Oh, it didn't work. Oh, I know why. Didn't refresh my IP address. Let's connect to the correct instance, shall is when I do things out of sequence, things don't work. Andrew and then there's our database now. I connected to the old database, not the new one. I've got five minutes left. Oh, all righty. I do

have a livveness probe demo, but let's jump into the rest of the slides because we've got five minutes, so we'll talk there. I ramble. I timed this this morning. It was bang on 40 minutes, but for some reason, this is one of my favorite subjects, chaos engineering. Um, it's unfortunately titled, in my opinion, mainly because if you go to anyone working in a production environment, say, "I

want to run some chaos engineering experiments," they're going to chaos engineering. Really? No. You're not going anywhere near my my safe production environment with your chaos chaos engineers. What they actually mean in my opinion is resiliency testing. Uh the definition of on the slide there is from the principles of org principle of chaos.org website and it says that chaos engineering is a discipline or methodology of experimenting

upon systems to see how they react when they encounter failure and the livveness demo there is a prime one. The recovery of data that we did as well is also a prime one. Simulating a loss of backup files, a loss of uh primary instance, loads of stuff that we can do. I highly recommend that anyone deploying to a new environment runs through resiliency testing, chaos engineering, because

what we're doing basically is testing a system to see how it reacts when it encounters failure. Does it react the way we expect it to or does it do something else? If it reacts the way we expect it to, then fantastic. We've built confidence in our system. If it doesn't react the way we expect it to, then we need to go and investigate So I mentioned right

at the start that when we when SQL first came out in there was no way to deploy well you could deploy availability groups but you couldn't get automatic failover. Now there are companies out there that will do it for you. DH2 I think could do it. Uh they have an operator now. Uh Microsoft did have an operator for it in SQL Server 2019 CTP but when it

went to RTM it somehow disappeared but recently a colleague of mine has built his own AG operator for SQL Server where we can have availability groups with automatic failover. So he was like do you want to can you test this for me? And so 100% I will test this give it to me and I you know if anyone can break it I'll break it. And these are

the tests I ran through. a pod failure. Delete a pod. Does it fail over or does it just move the pod to a new node and still come up as the primary? Shutting down a node. What happens when the node did the tolerations kick in or does the AG fail over? Have we got a conflict there? Is the pod coming up on a new node as the

primary or did the pod stay where it was? The AG failed over. What behavior do I want? Service interruption. This is where the livveness probe would come in. If we shut down the service, how long does it take that AG to realize and fail over? And then finally, and this is my favorite one, simulating storage loss. Drop the storage out of the pod. See what happens to

that SQL instance. Uh, spoiler alert, nothing good. However, by default, for some reason, AGs won't fail over a database that's lost its storage. there's a special there's a extra um setting called DB health that you need to enable. Okay, now I'm really short on time. So final demo testing SQL Server in Kubernetes. This is a recorded one, I'm afraid. But when I first demonstrated SQL Server in

Kubernetes to my manager in my in a previous job, I ran through Visual Studio Code much like we done here and went through all those tests, shutting down a node, deleting a pod, dropping the storage out of it, and he was like, "This is great, Andrew, but we need to get buy in from management here, and they're not coders. They don't want to see walls of code.

Can you jazz it up a little bit for us?" Right? Jazz it up. jazz hands. What? He said, "No, find some sort of user interface and do it through a UI." I was like, "Right, okay." So, I went off and had a look and found probably my most favorite GitHub repo. So, I went back to my manager and said, "Okay, I found a UI for you. We're

gonna test SQL Server high availability in Kubernetes with Space Invaders. This is my favorite game. It's called Cube Invaders. There's a link to it in the notes for this session. And you deploy a load of pods. They become the invaders and you play as a spaceship. And as you blow them away, see Kubernetes restarts them for you. a really cool way of testing high availability for anything

in Kubernetes. And this is how I demonstrated HA for SQL Server in Kubernetes to my managers, the sea level board at my company. You may or may not be surprised that I no longer work there. So, thank you very much. I got a whole bunch of resources for you. If you click on that QR code, it will take you to a virus that will make your phone

a web bot. No, it'll take you to the Q to get the GitHub repo. Please, if we have any questions, there are questions. Sure. Oh, looks okay. Uh I do realize if it's it's lunchtime, so please feel free to go. Of databases who move to Kubernetes, what percent shouldn't have been moved? and has managed the right answer instead. Uh percentage-wise, it's a very small percent of databases

I have actually moved to Kubernetes if I'm being 100% honest with you because you really need to think about why you want to run them in Kubernetes other than Kubernetes is cool. I want to run them in there. Uh so I would always start with right with databases start off with physical machine, virtual machine. Okay, are we running in the cloud? Are we going to deploy on

a virtual machine? What's the downside there? Does something like Azure SQL database give us everything that we need? Re-evaluate. If it doesn't, Azure managed instance, does that work over in Amazon RDS and all that and keep looking through each one, weigh the pros and the cons of each platform and go from there. A really good example and one I did for Kubernetes and SQL Server was who

here has ever deployed an application and at the start of the application it says do you want to install SQL Express? Yeah. Bane of my life these little instances of SQL Server popping up everywhere and one day and it always happens is do we have any backups for this instance? And I'm sitting there going I've never even heard of this instance. No, we don't have any backups.

So what we did is we took all the databases that were installed via SQL server express and dropped them into a Kubernetes cluster centralized they have high availability we can use cron jobs to back them up that was a perfect valid use case for it so the answer to the question is evaluate 100%. Uh, next question. Every DB types needs its own operator. When does a team's

database mix make KH the wrong choice? What point does operator sprawl beat the wind? Yep, At what point at what point are you managing the operator more than you are managing the actual database? Depends on the operator. Um, the AG operator that I've been testing works very well. It sits there in the background. I've been trying to nuke this thing like there's no tomorrow. The only time

it's actually failed is when I broke the entire cluster. Again, it's Does your team have the skills? This is operators can be complicated. Do I want to throw having to troubleshoot an operator in the mix as compared to working with databases and Kubernetes? Do I need a storage operator as well? So, I've got not only Kubernetes, SQL, the SQL operator, and the storage operator. You know, we're

getting complicated here. Now, at what point do we go, can we just build a VM again? Evaluate, test, test, test. It's I'm going to keep coming back to that because I don't want to say it depends, but it really does. Next one. After two, our demos look clean. After two years of running production database on K operational pain that nobody talks about in talks like this. [laughter]

Andrew, is there anything you haven't thought of? I don't know. I haven't thought of it. Uh performance performance issues is always a killer. Why? That's the same with any SQL platform. All of a sudden performance nose dives. What's And the only thing I can say there is what's changed in the environment. Something has changed. I I love working with customers who tell me nothing's changed in their

environment for over a year. They're like, "Really? What have you been doing?" Something has always changed. So, it's the performance profiling is generally the pain. At some point, something's going to nose dive. There's going to be a transaction drop and you need to go in and investigate why. um benchmarking beforehand getting proper performance profiles of everything before deployment and whilst test and whilst running through a PC

will help you here because you'll know what it looks like. First question I always ask people when they say there's a performance problem performance is slow tell me that that that's not a metric tell me what it actually should be and what it is now but in answer to the question yes u performance of the pain is the pain uh any more for anymore we good cool

thank you so much I will be here for the rest of the day so please come and talk to me if not please reach out and we can have a discussion enjoy the rest of your Hey, thank you.