Edit

Share via

Deploy SQL Server containers and availability group with DH2i DxOperator on Azure Kubernetes Service via Rancher

Applies to: SQL Server - Linux

This tutorial provides instructions on setting up SQL Server configured with Always On availability groups (AGs) in an Azure Kubernetes Service (AKS) cluster. It utilizes the DH2i DxOperator and Rancher Prime from SUSE for deployment.

Microsoft supports data movement, AG, and SQL Server components. DH2i is responsible for support of the DxEnterprise product, which includes cluster and quorum management.

Note

DxOperator is a software extension to Kubernetes that uses custom resource definitions to automate the deployment of DxEnterprise clusters. DxEnterprise then provides all of the instrumentation to create, configure, manage, and provide automatic failover for SQL Server AG workloads in Kubernetes. You can register for a free DxEnterprise software license. For more information, see the DxOperator Quick Start Guide. For support issues, contact SUSE directly.

This tutorial consists of the following steps:

  • Configure Rancher Prime on AKS
  • Install DxOperator
  • Deploy SQL Server containers and configure the always on availability groups using the DH2i DxOperator
  • Connect and manage SQL Server containers using SQL Server Management Studio (SSMS).

Prerequisites

Configure Rancher Prime on Azure Kubernetes Service

Follow the instructions provided in the Quickstart: Deploy an Azure Kubernetes Service (AKS) cluster using Azure CLI article.

After you install your Kubernetes cluster, follow the instructions in Installing Rancher on Azure Kubernetes Service from Step 4, where you access the credentials of the Kubernetes cluster and install Rancher.

Install DxOperator

  1. Sign in to the Rancher portal via the web UI.

    Screenshot of Rancher web UI.

  2. To proceed, select the Kubernetes cluster where you want to install DxOperator. In this example, choose a local cluster that also has Rancher deployed. However, you should import or add another Kubernetes cluster and use that cluster for deploying SQL Server, to avoid Rancher and SQL Server pods running on same Kubernetes cluster.

  3. From the left hand menu, Select App > Charts.

    Screenshot showing the available chart options.

  4. Search for DxOperator and select Install.

    1. Select Next and then Install.
    2. Wait for the installation to complete before proceeding.

Install SQL Server AGs using DxEnterprise DxOperator

  1. Add secrets for SQL Server and the DxEnterprise cluster passkeys.

    1. Navigate to Storage > Secrets > Create.

    2. Choose Opaque as your secret option, and enter the secret name dxe.

    3. Provide the key name as DX_PASSKEY, and value of the DxEnterprise cluster passkey.

    4. Select Add.

    5. Add the key name as DX_LICENSE, with value of the DxEnterprise license key.

    6. Select Create to finish creating the license key.

      Screenshot of the license and passkey secrets.

    7. Select Create to create a new secret, and then Opaque.

    8. Enter name of secret as mssql, set the key name as MSSQL_SA_PASSWORD with a value of a strong sa password.

    9. Select Create to finish creating the secret.

      Screenshot of the password secret.

  2. Select Apps > Charts.

  3. From the list of available charts, search for DxOperator - DxE + SQL Server AG.

    Screenshot of the search option.

  4. Install the chart.

    1. Select Install.

    2. Once you select Install, provide the AG details as shown.

      Screenshot of the availability group settings.

    3. Select Next.

    4. Check the Create Load Balancers option to allow external access.

    5. On the DxEnterprise options, check the Accept EULA option.

    6. Select dxe as cluster secret.

      Screenshot of cluster secret.

    7. On the SQL Server options, check Accept EULA.

    8. Select mssql as SQL Server secret.

    9. Select Next and Install.

    Screenshot of the cluster options.

Connect to SQL Server

Once the installation is complete from the previous steps, get the external IP address to connect using SQL Server Management Studio (SSMS).

  1. Navigate to Services, select DxEnterprisesqlag-0-lb, and note the external IP (LB) address.

  2. Use the external IP address in SSMS to connect to the AG.

    Screenshot of external IP address.

  3. In SSMS Object Explorer, expand Always On High Availability > Availability Groups.

  4. Select AG1 > Show dashboard.

    Screenshot of SSMS validation.

Simulate the failover to verify.

  1. From the Rancher web UI, select Workloads > Pods
  2. Select the DxEnterprisesqlag-0 pod
  3. Select Delete, then select Delete again when prompted

Fetch the external IP address of the secondary pod.

  1. Select Services on Rancher web UI

  2. Select DxEnterprisesqlag-1-lb

  3. Fetch the external/load balancer IP address

  4. Connect to SSMS and view the AG dashboard

  5. Observe that the role changed to Primary for DxEnterprisesqlag-1

    Screenshot of post failover.

Related content