Microsoft Workloads on AWS

Containerize SQL Server workloads with Amazon EKS and DH2i

In this blog post, we will show you a simple and effective way to deploy Microsoft SQL Server Always On availability group containers in Amazon Elastic Kubernetes Service (Amazon EKS) with DH2i.

Business-critical workloads running on SQL Server make high availability a key consideration when deploying on AWS. The challenge is that Kubernetes has a default inherited bottleneck with failover speed. It requires nodes to be unreachable for at least five minutes before rescheduling workloads. With business-critical SQL Server workloads and stringent uptime requirements, striving for five nines of availability makes this level of failover latency unacceptable.

Containerizing SQL Server workloads enables high density and allows many internal enterprise workloads to share a common resource pool (memory, CPU, and storage). It therefore reduces unused capacity and improves the efficiency of your infrastructure use.

Deploying SQL Server to Amazon EKS is a growing trend among organizations looking for significant enhancements in scalability, speed to deployment, and overall management experience of their containerized applications. Running SQL Server in containers with DH2i culminates in a cross-platform, high availability solution providing fast installation, fully automatic failover, and reduced licensing costs.

Prerequisites

Solution overview

As shown in Figure 1, using DH2i’s DxEnterprise Smart High Availability Clustering software with the included DxOperator tool, we will create an availability group (AG) in Amazon EKS that spans at least two AWS Availability Zones (AZs). The deployed AG will use synchronous replication between AZs for the AG primary and two replicas.

Figure 1 – SQL Server Always On availability group running in Amazon EKS

Figure 1 – SQL Server Always On availability group running in Amazon EKS

SQL Server is installed when DxOperator is deployed. DxOperator allows you to build a SQL Server AG in less than three minutes using only five commands. DH2i’s technology not only streamlines deployment and configuration of your SQL Server AG in Kubernetes, but DxEnterprise (DxE) also allows you to achieve near zero downtime for SQL Server Kubernetes deployments.

DxE provides minimum downtime with fully automatic database-level failover for SQL Server AGs in Kubernetes. This functionality reduces your failover windows to seconds, readying your organization to take even the most critical SQL Server workloads to Amazon EKS.

DH2i’s high availability (HA) clustering software is smart with its integrated software defined perimeter (SDP) feature set. DxE comes with the capability to create application level zero trust network access (ZTNA) tunnels between your AG replicas. DH2i’s security software is differentiated in the SDP market through significant qualities and features:

  • DH2i does not see your data because the technology stays out of the data path entirely.
  • Replication throughput is boosted by up to 40% with proprietary data transport technology.
  • SDP microtunnel endpoints are highly available in the case of an outage.

Download the DxE trial software to follow the deployment steps in the next section.

Deployment steps

Let’s start by reviewing the cluster we created for this blog post. In Figure 2, there are three worker nodes in our Amazon EKS cluster. The nodes are created in three different AZs.

Figure 2 - List of Amazon EKS worker nodes on Amazon  EC2

Figure 2 – List of Amazon EKS worker nodes on Amazon  EC2

We can also use the kubectl commands, get all and get nodes, to view the nodes of your starting Amazon EKS cluster, as shown in Figure 3:

Figure 3 List of Amazon EKS nodes using kubectl commands

Step 1 – Install DxOperator

Start by installing DxOperator on the Amazon EKS cluster. DxOperator is installed by applying a YAML configuration file acquired from DH2i. This file can be downloaded using the command:

curl -O dxoperator.yaml https://dxoperator.dh2i.com/dxesqlag/files/v1.yaml

NOTE: Making changes to this complex file is an advanced topic beyond the scope of this blog post. Reach out to DH2i if you’d like learn more about the content of dxoperator.yaml.

After downloading the installation YAML, it can be applied to your Amazon EKS cluster using the following command, as shown in Figure 4:

kubectl apply -f .\dxoperator.yaml

Figure 4 – Install DxOperator using kubectl command

Figure 4 – Install DxOperator using kubectl command

Step 2 – Edit the configuration of DxOperator

Once DxOperator has finished installing, download the configuration file: dxesql.yaml. Edit configuration settings and other parameters for the DxE and SQL Server deployment, as shown in Figure 5:

Figure 5 - Edit DxOperator configuration using Notepad

Figure 5 – Edit DxOperator configuration using Notepad

Reference the DxOperator QuickStart Guide for the configuration settings utilized in this cluster. For this example, we changed three parameters to new values. These are the parameters that DxOperator users will want to customize the most often when working through their own SQL Server AG Kubernetes projects:

         synchronousReplicas: 3

         asynchronousReplicas: 0

         availabilityGroupOptions: “CONTAINED”

Step 3 – Create Kubernetes secrets

This step involves applying sensitive information, including passwords, in the form of Kubernetes secrets. Prior to taking this step, we advise following the AWS best practices for securing Kubernetes secrets in Amazon EKS, including the use of envelope encryption with AWS Key Management Service (AWS KMS).

Create a cluster secret for DxE using your passkey and license code with the following command:

         kubectl create secret generic dxe --from-literal=DX_PASSKEY=<pass> --from-literal=DX_LICENSE=<license_key>

This command will create a new secret resource in Kubernetes called dxe. It will contain two named values, both of which are required to set up DxE:

·        The cluster passkey used to log in with DxAdmin: DX_PASSKEY=<passkey>

·        The license key used to activate DxE: DX_LICENSE=<license_key>

Create the mssql secret with following command prompt, as shown in Figure 6.

          kubectl create secret generic mssql --from-literal=MSSQL_SA_PASSWORD=<pass>

This command will create a new secret resource in Kubernetes: mssql. It will contain a single named value required to set up SQL Server:

          MSSQL_SA_PASSWORD=<password>

This sets the initial System Administrator (sa) password for the SQL Server instances.

Figure 6 - Create dxe and mssql secrets using kubectl commands with DxEnterprise license key and sa password

Figure 6 – Create dxe and mssql secrets using kubectl commands with DxEnterprise license key and sa password

Step 4 – Deploy SQL Server containers in pods

We can now deploy DxE and SQL Server containers in the pods with the following command:

        kubectl apply -f .\dxesql.yml

This command applies the custom resource to Kubernetes, instructing the operator to go ahead and create the cluster as described so far.

You can use the command kubectl get all to view the cluster status, as shown in Figure 7:

Figure 7 - Deploy SQL Server container to Amazon EKS and view cluster status

Figure 7 – Deploy SQL Server container to Amazon EKS and view cluster status

Figure 8 shows that this process took approximately 90 seconds for the SQL Server AG in Amazon EKS to reach a ready and running state.

Figure 8 – Using the command: kubectl get all to verify that container deployment is complete

Figure 8 – Using the command: kubectl get all to verify that container deployment is complete

Step 5 – Verify connectivity to the cluster

Once the container deployment in the cluster has been completed, you can copy the external IP from one of the containers and connect to the AG using SQL Server Management Studio (SSMS), as shown in Figure 9:

Figure 9 - Login to cluster on SQL Server Management Studio using external IP from a deployed container

Figure 9 – Login to cluster on SQL Server Management Studio using external IP from a deployed container

Once the databases have synced, you have officially created your highly available SQL Server AG in Amazon EKS. In this example, we created a three-node, highly available AG in Amazon EKS that spans three different AWS AZs. Figure 10 shows the three-node cluster and status:

Figure 10 - SQL Server Always On availability group dashboard

Figure 10 – SQL Server Always On availability group dashboard

You can also connect to the cluster using DH2i’s DxAdmin, an equivalent management tool that provides the same level of visibility and control over your cluster as SSMS.

Step 6 – Manual failover validation

Let’s test the resilience of the SQL Server AG running Amazon EKS by performing a manual failover test. To do that, we will create a shell on DXESQL-0 and run a dxcli command, which is installed as part of DxE, to manually failover from DXESQL-0 to DXESQL-1. As shown in Figure 11, check the active availability group server using DxAdmin administration console:

Figure 11 –View of the AG in the DxAdmin administration console shows it active on DXESQL-0

Figure 11 –View of the AG in the DxAdmin administration console shows it active on DXESQL-0

Create a command shell on DXESQL-0:

         kubectl exec -it -c dxe dxesql-0 – bash.

Instruct DxE to do a manual failover and make DXESQL-1 the new primary:

        dxcli vhost-start-node VHOST1 DXESQL-1

During the failover process using the DxAdmin administration console, we can watch the AG stop on its current primary and restarted on the new primary, eventually showing as active on the new primary, as shown in Figure 12:

Figure 12 – The DxAdmin administration console now displays the AG active on the new primary, DXESQL-1

Figure 12 – The DxAdmin administration console now displays the AG active on the new primary, DXESQL-1

To verify the failover, in SSMS connect to DXESQL-1 and verify DXESQL-1 is the new primary, as shown in Figure 13:

Figure 13 – SQL Server Always On availability group dashboard

Figure 13 – SQL Server Always On availability group dashboard

Cleanup

To avoid incurring future charges, delete any resources you created as part of this blog post:

  •  Amazon EKS cluster with 3 worker nodes.
  •  Any client machine created in AWS.

Conclusion

In this blog post, we demonstrated how to run SQL Server in Kubernetes containers with DH2i, culminating in a cross-platform, high availability solution that provides fast installation, fully automatic failover, and reduced costs. DxEnterprise offers:

  • The exclusive capability of fully automatic failover for SQL Server AGs in Kubernetes.
  • Sidecar container deployment for application-clusterware  isolation.
  • The ability to securely cluster any mix of cloud, virtual, or physical infrastructure.

Want to give Amazon EKS or DxEnterprise, which includes DxOperator, a try? Amazon EKS and other AWS services are available for trial on the AWS Free Tier. A free trial of DxEnterprise is available from DH2i Free License.

Interested in checking out additional demo and blog post content? See these SQL Server AG Kubernetes deployment posts:

Deploy SQL Server Always on High Availability mirroring data in Kubernetes with automatic failover

Get Started with SQL Server AGs across Windows, Linux and Container replicas,  “Data Exposed”

Always on Availability Groups for SQL Server containers on Kubernetes – The DH2i’s DxOperator way!!

AWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.

Yogi Barot

Yogi Barot

Yogi is Principal Solutions Architect who has 22 years of experience working with different Microsoft technologies, her specialty is in SQL Server and different database technologies. Yogi has in depth AWS knowledge and expertise in running Microsoft workload on AWS.

Don Boxley

Don Boxley

Don Boxley is a DH2i Co-founder and CEO. He has more than 20 years in management positions for leading technology companies. Boxley earned his MBA from the Johnson School of Management, Cornell University.