Get started with SQL Server AGs across Windows, Linux and Container Replicas | Data Exposed

Key Takeaways

Did you know that you can create always on availability groups across SQL Server running on Windows, Linux, and Kubernetes on containers? Learn all about it and see a really cool demo including how few commands and how easy this actually is to get set up. Learn more this week on Data Exposed. Hi, I'm Anna Hoffman and welcome to this episode of Data Exposed. Today we have two very special guests joining us. First we have Amit from the SQL product group and we also have OG from DX Enterprise. Thanks folks so much for coming on the show. We're going to be talking about hybrid. Now in the last SQL AMA, maybe some of you tuned in and there was a lot of excitement around always on availability groups across Windows, Linux, and containers. I personally was like, wait, what? You can do this? Um, so bring on OG and Amit to tell us all about how this is possible and show us a really amazing demo. So OG, why don't you kick us off? Tell us a little bit about what this is and how it works. Absolutely. So, here we have a a short demo. I'll walk everybody through how you can get your Windows node join into a Linux node and then in turn will join to the container pod of Kubernetes. So, what we have for the setup goals, I have my laptop here on prem in the office. Uh, and I'll be joining it into a SQL running on Linux, a VM in Azure, and then also join into the AKS. So, SQL Server running inside a container on Azure Kubernetes service. So, let's start that. All right. so OJ, what you're basically saying is we'll have one single always-on availability group created where one replica is running Windows on your laptop, second replica is running Azure VM which is running on Linux distribution, and the third one is running in AKS container on Azure again. And all three of this will be part of one single cluster. And when I do a right-click failover, the database will just move from Windows to Linux, Linux to container as part of the same availability group. That is correct. That is correct. You already have a full cross-site cross-platform cross-distro cross-environment thing or AG. So. Awesome. Let's get rolling. See, Anna, I did tell you that in the AMA, this is something that is really exciting. Well, here we go. So, this is a short demo. I will walk through it and we will try to explain it along the way here. Okay? All right. So, this is our small demo script. So, we're setting up obviously your AKS cluster. Um setting it up so there's a various things that you'll do. And the first, we will we'll get our Windows node which is my laptop here um up and running inside AG. So, we set up a V host. Uh think of it as a logical container where you can put applications under. So, SQL AG is an app that we're going to put under a virtual host. Okay. And to add an AG into a virtual host, simply giving uh, system uh, parameters the node name, the SQL Server instance name, uh, and, uh, obviously credentials to get to it. And, um, the DX Enterprise solution will go out and pre- uh, prepare and put this SQL instance inside, uh, the V host. And start the AGs up. So, here is our GUI that we can kind of show that what we just did. The two command, you got a V host started, and then we have AG created, uh, running on my laptop. So, we have a singleton AG at this point, right, running on my, uh, Windows, uh, machine. So, next, uh, we'll go ahead and, um, join our second, uh, VM uh, running Linux inside a Azure. So, uh, activate the, uh, the software. So, again, a single command. And then, we'll, um, add it, or join it into the cluster, the existing cluster, which is, uh, uh, the Windows node, right? So, we're joining everybody into a single quorum, uh, group. A single command, and, um, again, out to the matchmaking service, uh, and with the passkey for, uh, the group, uh, this Azure, uh, Azure Linux node will now be able to join into the Windows node. And the join successful. And of course, the next part, the Windows cluster will then add, uh, this, uh, new, uh, VM running Linux, uh, in Azure, um, into the, uh, AG quorum. So, once again, the add AGs node, and pass in some of the parameters needed, the instance name, uh, credentials, and the availability group. Um, and once again, looks like we already got that done. You'll see right there we have two node now running a SQL AG and they are participating in a single AG form. So, we have the primary running on the Windows node and the secondary running uh on the Linux node inside Azure. Cross-site, uh cross-environment, no VPN between the the two nodes by the way. So, uh everything uh done done through the uh tunnel uh part of the X Enterprise. So, next we're obviously going to add the uh um node we call node any any any that uh instance of running inside another VM or physical machine or container is a node to our uh view. Um so, that will go ahead and prep this container this SQL container um that'd be part of the AG form. So, uh with the uh the value parameter or or YAML is nothing more than say, "Hey, how am I going to deploy this particular AKS cluster? How many replicas or how many containers or how many pods I want inside AKS?" So, uh in this case, we only say like I only want a single uh pod, single container that running SQL Server and the X Enterprise um on the side uh side car uh the model uh or side by side model. So, So, so the best part about this uh just just to uh reiterate, what you're saying is while that command is running, that just went ahead, deployed a SQL Server container, which also contained the DXE packages for HA in one single pod on AKS with that just one single command. And I just want to let everyone know that this entire stack is fully supported. So, you can deploy this using Helm, use the X Enterprise with uh SQL Server Always On Availability Groups running. So, we're just adding the third node right now, which is a container running on AKS. Yes. And you can have as many containers you want to really. Uh that's that's up to you, right? You can have a full AKS stack running, say three pods, the containers. Um and they can also join to the cluster. I think just a a clarification uh for if AG quorum, I believe you can have up to five synchronous replicas and three async in in our single three of three, right? Three async and a single config only. So, nine total. Wow. So, you can have as many replica and as you want. And as you've seen, uh all three node because nodes, right? Uh one running on my Windows lap the laptop here on prem join into uh a Linux uh VM running in Azure in region I believe it's West US 2 and then the AKS was deployed in West uh US 3. So, they're really three separate locations that joining into get together into a single AG quorum. And of course Awesome. we have an AG uh it's it's it's only useful if you actually have database replicated. So, let me go ahead just add a a database into the AG at this point. So, without opening a single firewall, without setting a single VPN between the laptop and the Azure network, we were able to set this entire thing up in what? Less than six commands all in all? And that's just something to think about, right? With AG, you have to prep the certificates, you have to do all the prepping. And here the tool uh do it for you. Right. And all of this by the way is right now certificate based endpoint authentication that is working. So, you didn't have to create a single T-SQL command to create the certificates. All of this was taken care in the back end through these utilities. And whatever OJ is showing through UI is pretty much possible through CLI as well in case if you want to go ahead and you know, automate this. Absolutely. In fact, a lot of our customers are automating this via the deployment script. So, the entire thing can be scripted. And you see right there failover happening. You just any anywhere. And this is the true power here. So, we keep saying the SQL Server engine is the same. You run it on Linux, you run it on Windows, you run it on containers. And this is exactly what proves it as well. As you can see here, we were just able to fail over from a Windows laptop to a Azure VM running Ubuntu, which is which is a Linux distribution. Fantastic. And the database are all synchronized. And um again, you can go from any anywhere, right? Any node, any any distro, anywhere. Uh primary can be reside anywhere that you like. Uh so, this this really give you the power to move data around um on any distro, on any any platform. Uh the What if I delete the container? What happens then, OJ? So, Well, obviously then the X Enterprise is a clustering solution, right? It's monitoring the apps that putting under its management. Um it's monitoring the health of each of the app that that is attached to it. Uh and of course if you are going to delete a pod, you're essentially having a failure in your stack. Uh and I I believe very shortly here we'll see that. So right now uh if we look at the demo, it's currently primary on the container. All right, running on AKS. So uh if we simulate a a failure of the system uh so we have wiped out the pod. Say bye-bye to the pod. Uh then let's see what happened. In practice, it will fail over. So let's see if that actually does Like the whole of the whole clustering solution is about failing over. You're monitoring the health of your application. If something's wrong going on with it, there's a fail over. And there you go. There you go. Failover is happening now. seeing that the the the primary is down, uh whether it's container or not, it's just that that it it participant inside the quorum and it's down. Therefore, it failed over and the top node on the list is obviously the laptop. So it will fail over from container to Windows node, just like that. And then Fantastic. And you can see right there uh AKS did its job to bring the container back. Uh and it rejoined the cluster, rejoined uh the DX Enterprise quorum, rejoined the AG quorum, and resynced. So everybody's back to normal. And just like that. And and just to specify this uh here, Anna, this entire thing is supported. So all the clustering technology and stack that we have here is from DX Enterprise and DX Enterprise supports that. And the SQL Server component, the Always On Availability Group, that's the entire stack that is supported by Microsoft. So and this currently is a is a production ready fully supported deployment that customers can go with. That's correct. That is correct. And obviously, we are the engine for the HA engine for the AG stack that run across multiple distro, multiple site. Yep, that's it from our side. so amazing. How many commands was that? How many commands was that? Yeah, and to how easy it was. And like I said before, It's really amazing. You can make it like a you you can really script it the entire thing that I can be done via CLI. So, we can switch between CLI and UI there just going to show all right, you can do it in the UI or in the CLI. But, technically speaking, when it comes to deployment, right? You want to script it automated. So, put inside a script, execute the CLI command or the cube the CTL command, then you get your entire AKS and or mixed environment cluster up and running. And customers can get started with the developer versions that's available for SQL Server as well as for DX Enterprise. So, nothing holds you back. You can get started with it. With that, that's all from our side. Awesome. Thanks so much, Amit and OJ. And Amit, you kind of you kind of answered the last question that I was going to say is like, "Hey, can people get started with this?" And the answer is yes. There's developer versions. They can go get started today. Um so again, OJ and Amit, thanks so much for coming on the show. I learned a lot. I'm kind of blown away by one that this is possible and two how how few commands you can do it in. Uh so really great to have you on the show. If you're watching this episode, go ahead and give it a like. Leave us a comment and let us know what you think. We'll put some links in the description for you to go learn more and learn how to get started with this. And we hope to see you next time on Data Exposed.