DH2i DxEnterprise 21.0 Software: MSSQL Availability Groups with DxCLI Quick Start Guide

Download the PDF

MSSQL Availability Groups with DxCLI

This quick start guide describes how to set up and configure Microsoft SQL Server availability groups on DxEnterprise using DxCLI. Using this guide, the user will add a Vhost, create an availability group, add databases, and add a SQL listener.

Prerequisites

The following configuration must be met before continuing:

  • Three nodes (virtual or physical) with DxEnterprise installed and joined together into a cluster are required. For information about installing and configuring DxEnterprise, see the Ubuntu, RHEL/CentOS, or Windows installation quick start guides. For cloud platforms with DxEnterprise pre-installed, view the Azure (Linux | Windows) or AWS (Linux | Windows) quick start guides.

    NOTE: This doc covers the deployment of three AG replicas. When deploying availability groups, please consider the Microsoft SQL Server quorum requirements for automatic failover described in this KB: Quorum Considerations for SQL Server Availability Groups.

  • Microsoft SQL Server (MSSQL) 2017 or later must be installed on each server (node). Reference Microsoft documentation for instructions on installing SQL Server.
  • A valid DxEnterprise license with availability group management features enabled. If tunnels will be used for availability group connections, tunneling features must also be enabled.

    NOTE: Visit the Client Portal to view license status. To enable availability group management and/or tunneling features, please contact your DH2i account representative. To obtain a license, contact sales@dh2i.com.

Create Availability Group on the First Node

  1. Connect to the first server (node) via SSH or RDP.
  2. Open Command Prompt or Terminal session with elevated privileges.
    • Windows only: change directory to C:\Program Files\DH2i\bin
  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 dxcli cluster-add-vhost command.

    Syntax

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

    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
    ilb_ports Comma-separated list of ports to use for internal load balancer probing. False

    Example

    dxcli cluster-add-vhost vhost1 *127.0.0.1 dxemssql1
  4. Use the 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 dxcli add-ags command. The SQL Server sysadmin password must be encrypted using the command in the previous step.

    NOTE: If tunneling is not enabled for your DxEnterprise license, omit the tunnel port parameter. Pay-as-you-go licenses on Azure and AWS have NAT and tunneling enabled. To view license status and features, visit the DH2i Client Portal.

    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
    instance_name The name of the instance. True
    sql_login A SQL server sysadmin user. If using Windows authentication, the credential must be supplied in UPN format (e.g. user@domain.com). 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

    dxcli add-ags vhost1 ags1 "dxemssql1|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit|15022"

Add the Second and Third Nodes to the Availability Group

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

    NOTE: If tunneling is not enabled for your DxEnterprise license, omit the tunnel port parameter

    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
    instance_name The name of the instance. True
    sql_login A SQL server sysadmin user. If using Windows authentication, the credential must be supplied in UPN format (e.g. user@domain.com) 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

    dxcli add-ags-node vhost1 ags1 "dxemssql2|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit|25022"
  2. Add the third node to the existing availability group and Vhost using the dxcli add-ags-node command. The SQL Server sysadmin password must be encrypted using the dxcli encrypt-text command.

    NOTE: If tunneling is not enabled for your DxEnterprise license, omit the tunnel port parameter

    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
    instance_name The name of the instance. True
    sql_login A SQL server sysadmin user. If using Windows authentication, the credential must be supplied in UPN format (e.g. user@domain.com) 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 1: Add the third node as an additional synchronous_commit replica

    dxcli add-ags-node vhost1 ags1 "dxemssql3|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|synchronous_commit|35022"

    Example 2: Add the third node as a configuration_only replica

    dxcli add-ags-node vhost1 ags1 "dxemssql3|mssqlserver|sa|6pnFaDrRS+W/F+dkRuPKAA==|5022|configuration_only|35022"

Add Availability Group Database(s) and Create an AG Listener

  1. Add databases to the availability group using the 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 for multiples). True

    Example

    dxcli add-ags-databases vhost1 ags1 db1,db2,db3
  2. Verify the details of the availability group using the 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

    dxcli get-ags-detail vhost1 ags1
  3. Optional: Add a listener using the 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: When the cluster type is set to “external” or “none”, availability group listeners only function with SQL Server 2019 or later. To connect to an availability group listener via Kerberos authentication, please reference Microsoft documentation.

    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