DH2i DxEnterprise 19.5 Software: MSSQL Availability Groups for Linux on Azure Quickstart Guide

Download the PDF

MSSQL Availability Groups for Linux on Azure

This quick start guide describes how to set up and configure Microsoft SQL Server Availability Groups running on Linux on Azure using DxEnterprise. Using this guide, the user will install SQL server, configure DxEnterprise, and create a SQL Availability Group across two servers (nodes) using DxEnterprise software.

Create DxEnterprise on Azure VM Image

DH2i offers Azure VM images that have DxEnterprise pre-installed. These images can be found on the Azure Marketplace by searching for “DxEnterprise for Availability Groups.” Go through the setup process and create two Linux VMs using one of these offers.

Install and Configure SQL Server

Install SQL Server on both virtual machines before continuing to the next section. For information on installing Microsoft SQL Server on Linux, please reference Microsoft documentation.

Setup DxEnterprise and Availability Group on the First Node

  1. Connect to the first node via SSH.
  2. Run the setup.sh script to configure DxEnterprise, start the services, and activate the license.

    Syntax

    bash /opt/dh2i/bin/setup.sh

  3. DxEnterprise uses Virtual Hosts (Vhosts) to provide failover support and high availability. A Vhost virtualizes the network name and IP address associated to a particular SQL instance, and instead of using the network name and IP address of a physical server, a Vhost is created and assigned a unique name/IP-address pair. Clients access the databases associated with an instance via the Vhost name or IP address; they do not need to know which node is running the SQL instance. To add a Vhost, use the sudo dxcli cluster-add-vhost command.

    Syntax

    dxcli cluster-add-vhost <vhost> <vips> <nodes> [autofailback] [priority[1-5]]

    Parameters

    Name Description Required
    vhost The name of the Vhost. True
    vips The virtual IP(s) for the Vhost (comma-separated list for multiples). The use of a loopback address (127.0.0.1) is supported, but must be preceded by an asterisk (*). True
    nodes The nodes(s) to add to the Vhost (comma-separated list of multiples). True
    autofailback Set autofailback option, or leave blank if autofailback is not desired. False
    priority The priority order of failover between Vhosts (1 is the highest and 5 is the lowest). False

    Example

    sudo dxcli cluster-add-vhost vhost1 *127.0.0.1 dxemssql1

  4. Use the sudo dxcli encrypt-text command to encrypt the SQL Server sysadmin password. The encrypted password will be used in the next step for the creation of the availability group.

    Syntax

    dxcli encrypt-text <value>

    Parameters

    Name Description Required
    value The text to be encrypted. True

    Example

    dxcli encrypt-text p@ssw0rd

  5. To add an availability group to the Vhost, use the sudo dxcli add-ags command. The SQL Server sysadmin password must be encrypted using the command in the previous step.

    Syntax

    dxcli add-ags <vhost> <ags_name> <node_name|instance_name|sql_login|sql_pass|mirror_port|availability_mode|[tunnel_port]>

    Parameters

    Name Description Required
    vhost The name of the Vhost. True
    ags_name The name of the availability group. True
    node_name The name of the node. True
    sql_login A SQL server sysadmin user. True
    sql_pass The password for the SQL Server sysadmin user (encrypted using “dxcli encrypt-text”) True
    mirror_port The mirroring port to use for the availability group (default is 5022). True
    availability_mode synchronous_commit, asynchronous_commit or configuration only. True
    tunnel_port The port to be used for the tunnel connections (for cross availability zone, cross-region). This port should be unique per node. False

    Example

    sudo dxcli add-ags vhost1 ags1 “dxemssql1|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit|25022”

  6. Set a One-Time PassKey (OTPK) for the DxEnterprise cluster using the sudo dxcli set-otpk command.

    Syntax

    dxcli set-otpk [ttl] [otpk]

    Parameters

    Name Description Required
    ttl The time to live. False
    otpk The One-Time PassKey in base64 False

    Example

    sudo dxcli set-otpk

Join the Second Node and Add to Availability Group

  1. Connect to the second node via SSH.
  2. Run the setup.sh script to configure DxEnterprise, start the services, and activate the license.

    Syntax

    bash /opt/dh2i/bin/setup.sh

  3. Join the second node to the first node in the DxEnterprise cluster using the sudo dxcli join-cluster command. When prompted to join via NAT proxy, select Yes. When prompted to enter the NAT proxy, use the default value of match.dh2i.com. When prompted for the passkey, use the OTPK that was generated in step 6 of the previous section.

    Syntax

    dxcli join-cluster

    Parameters

    Interactive only

    Example

    sudo dxcli join-cluster

  4. Add the second node to the existing availability group and Vhost using the “sudo dxcli add-ags-node” command. The SQL Server sysadmin password must be encrypted using the “sudo dxcli encrypt-text” command.

    Syntax

    dxcli add-ags-node <vhost> <ags_name> <node_name|instance_name|sql_login|sql_pass|mirror_port|availability_mode|[tunnel_port]>

    Parameters

    Name Description Required
    vhost The name of the Vhost. True
    ags_name The name of the availability group. True
    node_name The name of the node. True
    sql_login A SQL server sysadmin user. True
    sql_pass The password for the SQL Server sysadmin user (encrypted using “dxcli encrypt-text”) True
    mirror_port The mirroring port to use for the availability group (default is 5022). True
    availability_mode synchronous_commit, asynchronous_commit or configuration only. True
    tunnel_port The port to be used for the tunnel connections (for cross availability zone, cross-region). This port should be unique per node. False

    Example

    sudo dxcli add-ags-node vhost1 ags1 “dxemssql2|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit|35022”

  5. Add databases to the availability group using the sudo dxcli add-ags-databases command The databases will be automatically replicated to the other node in the availability group.

    Syntax

    dxcli add-ags-databases <vhost> <ags_name> <db_name>

    Parameters

    Name Description Required
    vhost The name of the Vhost. True
    ags_name The name of the availability group. True
    db_name The name of the SQL Server database (comma-separated list of multiples). True

    Example

    sudo dxcli add-ags-databases vhost1 ags1 db1,db2,db3

  6. Verify the details of the availability group using the sudo dxcli get-ags-detail command.

    Syntax

    dxcli get-ags-detail <vhost> <ags_name>

    Parameters

    Name Description Required
    vhost The name of the Vhost. True
    ags_name The name of the availability group. True

    Example

    sudo dxcli get-ags-detail vhost1 ags1

  7. Optional: Add a listener using the sudo add-ags-listener command (SQL Server 2019 and later only). SQL Server listeners allow a client to connect to a replica of the availability group without having to know the SQL Server instance name. An availability group listener consists of a DNS listener name (Vhost name), listener port designation, and one or more IP addresses (Vhost IP address).

    NOTE: To connect to an availability group listener via Kerberos authentication, please reference Microsoft documentation.

    Syntax

    dxcli add-ags-listener <vhost> <ags_name> <port>

    Parameters

    Name Description Required
    vhost The name of the Vhost. True
    ags_name The name of the availability group. True
    port The port number for the listener. True

    Example

    dxcli add-ags-listener vhost1 ags1 42002

References

Download the PDF