DH2i DxEnterprise 20.0 Software: MSSQL Availability Groups with DxCLI Quick Start Guide
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:
- Two 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.
- 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
- Connect to the first server (node) via SSH or RDP.
- Open Command Prompt or Terminal session with elevated privileges.
- Windows only: change directory to C:\Program Files\DH2i\bin
- 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
- 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
- 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|25022”
Join the Second Node and Add to Availability Group
- 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|35022”
- 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
- 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
- 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