Provisioning SQL Server 2025 Databases to Kubernetes using Pure Storage Snapshots | Data Exposed

Key Takeaways

Learn how you can use pure storage snapshot technology to rapidly clone your databases in Kubernetes this week in data exposed. Hi, I'm Anna Hoffman. Welcome to this episode of Data Exposed. Today we have Pure Storage joining us again. and Pure Storage was on before uh a different person uh Anthony uh not Andrew uh and Dan and Jay was with us and and now we're back. So Dan and Jay welcome back welcome Andrew and what are we going to talk about today? >> Thank you Anna glad to be on data exposed again. Last time you're right we had Anthony uh from Pure Storage and he showed how SQL servers rest interface can improve backup management and mobility. Well, Pure Storage and SQL Server team have had a long-term partnership and has delivered significant benefits for our customers, DBAs, developers by combining the high performance storage that Andrew's team builds and with powerful database platform that comes from SQL Server. Now pure storage is all flash solutions are engineered to optimize and run SQL server workloads and that's how we work with Andrew uh on a regular basis throughout the development of SQL Server 2025 to offer fast query performance reduced latency simplified management all of the above and plus we know and I work with one of our customers who's using pure storage and they want high availability and reliability and they go with SQL server running on pure storage uh they get the benefits of faster backups and restores. So today the partnership has really helped many customers and and we have Andrew here to demystify how to provision uh SQL server on Kubernetes in a quick environment and show that you can move from a production grid environment into a dev test environment uh and and don't have to be worried about knowing all the indepth details of Kubernetes there. Um, so I think I I'm excited to hear and see what Andrew has to show today. >> Awesome. Cool. Yeah. So am I. Let's get right into it. >> Yes. Hi there. Both. Really excited to be here. Um, this is a question that comes up a lot when I'm talking to people is how do I get a copy of my database into an environment where I can easily spin up instances of SQL Server fully configured ready to go. And Kubernetes is absolutely fantastic at doing that. So what I want to run through today is cloning a database from say a traditional SQL instance to say a Kubernetes cluster. So we have our traditional SQL instance here running on prem and for the demo it's going to be SQL on Linux but we have say a volume here and then we have our Kubernetes cluster in our environment as well. And what we can do using pure technology is take a snapshot of the volume or volumes that the database runs on, create a persistent volume in our Kubernetes cluster and then overwrite that persistent volume. And then once we've got that, we then have the database files on that persistent volume and we can reference that in a SQL Server stateful set and deploy into our cluster. So it's a really nice easy way of getting multiple copies of a database into well a copy of a database into a Kubernetes cluster and then we can snap clone snap clone as much as we want in that Kubernetes cluster. >> Awesome. I love it. A question for you Andrew like as I'm doing this snapshot is there anything I need to be concerned about on my source SQL? >> No absolutely not. It's these are crash consistent snapshots. So we're just taking a snapshot of the volumes as they are at a point in time. the source SQL instance stays up, stays transacting, is completely unaffected by what we're doing here. So, it's a complete online clone [snorts] if you'd like. So, the production SQL instance, you say, stays online whilst we take this snapshot and then provision it into Kubernetes. >> Awesome. Cool. Is it fast? >> Nice and fast. Nice and quick. There's no data migration moving around here because SQL uh sorry pure storage snapshots are metadata only based. So all we're doing essentially is moving pointers around on an array. >> Having said that, what we're going to do in the demo coming up is actually migrate a snapshot from one flash array to another flash array. So we're keeping test and dev separate. At that point, there is some data migration, but you can stage that. take a snapshot, let it seat over, and then take multiple snapshots throughout before you come to actually provision. So, you're just sending deltas across. >> Nice. Awesome. Sounds great. I'd love to see it in action. >> Okay, so let's have a look. Let's jump into my lab. And here we go. We're in Visual Studio Code. Let's have a look at our server first. So, we've got a nice SQL instance here with our database wide world importers. And this is the one that we're going to take a snapshot of and then provision that database into our Kubernetes cluster. So if I jump into Visual Studio Code, let's have a look at the steps. We've got take a snapshot of the protection group. Now a protection group on a flash array is just a logical entity where we can drop multiple volumes in. And then we take a snapshot of that protection group, not the individual volumes. And that's say how we get a database that's spread across multiple volumes in a consistent state that we can present up to our Kubernetes cluster. But but for this demonstration, just so we can keep it nice and quick, we're just going to have the database on one volume that we're going to send over. So we're going to take the snapshot, create a PVC and corresponding persistent volume in Kubernetes, overwrite the volume with that replicated snapshot, and then deploy SQL Server to Kubernetes referencing that PVC and the corresponding PV. and then we'll have a look at the database in SQL Server and Kubernetes. So if I scroll down, first thing to do, let's make sure I can talk to my cluster. There we go. Standard cluster we're running version 1.34. And this is why I don't type in demos. There we go. I'm going to make sure I haven't got anything running already. Nope. All good. And I'm going to import our Pure Storage PowerShell module. So because we want to automate this type of thing, we're going to we can do everything that in the flash array guey, but we want to do this with scripts. So we're going to use our pure storage PowerShell module which is available in the PowerShell gallery. Simple matter of install module, Pure Storage PowerShell SDK2 and you have it. Okay, so let's set some variables. We have our source flash array IP, our target flash IP. And we're going to snap and replicate across target SQL instance where our database lives the protection group where our volumes live in because remember we snap the protection group not the individual volumes and then we're going to grab that individual volume where our database actually lives. So 3 2 1 nice and simple okay and then let's just have a look at that database again on the SQL instance. So I'm just going to use evoke SQL command. As I said, we have wide world importers all in this location. Var opt msql data. We're going to need to replicate that in our stateful set when we deploy. Going to connect it to my flash rates. And just to point out, yeah, I'm importing credential files here just because as you've already seen my typing in demos, it's not great. So, it's it basically prevents us from all having to watch me fumble around trying to add a username and password in. So 3 2 1 connect in and two. Good. So I've got my connection to my target SQL instance. I've got my connection to my flash arrays. Now I can take a snapshot of the protection group that has all my volumes in. So 3 2 1 boom. Okay. This is a metadata only operation. So no data was moved around here. All I've essentially said is this snapshot points at those blocks on the array. But I've also said replicate now which is true which means send that snapshot immediately over to the target flash array. So if we have a look at that now we can have a look using our pure commander get volume snapshot on my target flash array and filter by my source volume. And there we go. We've got the volume there. Now as I said data is moving for you. It's a metadata only operation on the source array but of course we're copying across to another array. So we can monitor that by getting the status of the replicated snapshot. Now this is wide wield import so it's very small. So I'd expect yes there we go progress one basically means 100% we are migrated over. So okay we've got our snapshot replicated over to our target flash array. We can now go and create a PVC in Kubernetes to host our clone database. And this is just nice and simple. What we're doing here is we're going to create two. One to host our system databases and one to host our user databases. So standard persistent volume claim for SQL going to call it MSQL data read write once. And the storage size has to match the size of the volume that we took the snapshot of. And then the second one is our MSQL system. I'm keeping that fairly small. Nice 10 gig. So if we create that, this is all backed by our portworks CSI offering which allows me to go down into the flash array and dynamically create persistent volumes from a persistent volume claim. And if we check that, I should now have my PVC and my persistent volumes. And there we go. Yeah, I've zoomed in a little bit here, so it's a little bit messy, but basically we've got everything that we need. So, we're ready to overwrite. So, we're going to grab the Portworks volume name created just by a little code here. And let's confirm what that is. Nope. I'm good. And let's confirm what the name of that PVC volume is on the flash range. The volumes do kind of match up, but they're not exact. So, one, two, three, and there we go. PX. Okay. So, it's pretty much the same just with a little identifier at the front here. All right. So now we have our snapshot that's been replicated to our target flash array. We have the persistent volume that we've created dynamically using port works. We can overwrite that persistent volume with the snapshot. So using this we can say new PFA volume on my target flash array the px volume name that we just created and then overwriting with our snapshot and of course the keyword overwrite is true. 3 2 1. And again, that's a near as makes no difference instantaneous operation because we've just moved metadata pointers around on the array that what we've essentially done was say that volume was pointing at those blocks. It is now pointing at these blocks on the array. Okay, so we've done a bit of headwork here. We can now go ahead and deploy SQL Server. And the key thing here is we're going to use SQL Server in a stateful set, but we're going to reference the two persistent volume claims that we've created. So our MSQL system and the MSQL data which then maps back to that volume we've overwritten with our snapshot 3 2 1. Got a couple of things in there. A service so we can connect in and an SA password. But if everything's gone okay, we should now see SQL Server up and running. Excellent stuff running on one of our nodes. Okay, here's the big question. is our data there in opt MSQL data and there we have it. We have our MDF, we have our NDF and we have the LDF. So the last thing to do is go ahead and attach it. But we need to grab that target IP, the the load balance IP so we can connect applications that are running from outside if sorry we can connect applications running in our cluster from outside of our cluster. Last thing to do just a nice create database for attach statement. 3 2 1 and this takes a couple of seconds just because it's grabbing them grabbing all the files and bringing it into SQL. And finally to confirm >> once this is done like now I'm a developer and I can now get to developing with this SQL server instance. >> Now it's there the database is there ready to go. And if we want to jump into management studio, connect in, we have the copy of our database ready to go running in a staple set in Kubernetes that is a clone a copy of the database from our source our production SQL instance. >> Awesome. >> Of course, we can then snap clone snap clone and spin up multiple stateful sets with the database. We basically have given the ability to provision ondemand SQL instances with copies of databases ready to go. >> Wow. So here I'm in Kubernetes and you're saying I can create take another snapshot and go into another uh another instance. Is that what you would call it? >> Yep. You can take that snapshot that we migrate over. You can take a snapshot of the exist the persistent volume that we created. Say if you did a load of data cleansing got stripped out a load of data that you didn't need. You could then take a new snapshot, override another one and provision and provision and so on and so on. As many as you'd want. So yes, nice and quick, nice and easy, really scriptable. So really automatable as well. >> Awesome. And then you can give access to a developer or whoever it is that's going to be doing updates on this. Is that kind of the thinking? >> 100%. So you'd have a little script server so you could sort out the security map users, things like that. 100%. >> Awesome. Cool. And then one more question for you that came into my head was like uh this works obviously today you're showing it for SQL Server 2025. What about previous versions of SQL Server? >> Yes, would work for uh previous versions um as long as they as long as they run on Linux. So I think we're talking 2017 and above. This that leads on to the question of can you do this for Windows Windows instances of SQL Server. Now the snap and the snap and the overwrite for the persistent volume doesn't really work because we're looking at NTFS file system going to XFS or ext4 and I really have tried running certain programs to try and wipe it and just grab the database files. Unfortunately doesn't really work. It can be done but it's not very automatable. The easiest way to do it is to use things like availability groups. create a availability group including your production SQL instance and an instance running in Kubernetes and seed the database across. So you use a clusterless AG auto seed the database across and once you've got the database into Kubernetes you can then snap clone to your heart's content. >> Oh awesome. Gotcha. Gotcha. Makes a lot of sense and and thanks for sharing that tip. I'm sure our users will appreciate it. Um, as we wrap wrap up, uh, Danj DJ data, any any other thoughts or or or questions for Andrew or feedback? >> No, I think this was, uh, really exciting, Andrew, to see and and that availability group tip is going to be very useful. Um, is this coming at Ignite? Uh, what where can we see more about what you've been doing? uh and >> um you can definitely check out my blog where I've got um a couple of articles on how to migrate using uh clusterless AGS into Kubernetes. So one for a standalone SQL instance and of course there's another article that hey what about if my SQL instance is already in availability group? Well at that point you can use a distributed availability group to migrate in. So I've got a couple of articles there that you can go and check out and they'll run you through the steps. >> Cool. Awesome. And can we find you at Ignite if you're there? Yes, 100%. I'll be there Ignite. Uh I'll be on the Pure booth or the Microsoft community booth. I'll be flowing between the two. Please come and talk to me. >> Awesome. Cool. We can't wait to see you next week if this gets posted when we think it's going to get posted. Um Andrew Dan J, thanks so much for coming on the show. I learned a lot. Really appreciate you talking us through it, talking us through the tips and tricks. Uh for our viewers, if you like this episode, go ahead, leave us a comment. Let us know what you think. We'll put some links in the description, including to some of Andrew's blog post. You can get started. And we hope to see you next time on Data Exposed.