Skip to main content
Version: v23.0

SQL Server Availability Groups with DxCLI Quick Start Guide

Introduction​

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

For Docker or Kubernetes deployments, see the dedicated quick start guides for those products:

info

This guide covers the deployment of three AG replicas. When deploying availability groups, please be aware of Quorum Considerations for SQL Server Availability Groups.

Prerequisites​

  • Three nodes (virtual or physical) with DxEnterprise installed and joined together into a cluster. For information about installing and configuring DxEnterprise, please see:

    For cloud platforms with DxEnterprise pre-installed, please see the Azure or AWS installation guides.

  • Microsoft SQL Server (MSSQL) 2019 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. A fully featured Developer Edition is available free for non-production use. To purchase DxEnterprise software for production workloads, visit the DH2i Store.

Create an Availability Group on the First Node​

  1. Connect to the first server (node) via SSH or RDP.

  2. Open a shell.

    Ensure the user is logged in as root.

    sudo -i

  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 command dxcli cluster-add-vhost.

    dxcli cluster-add-vhost vhost1 "" dxenode1

  4. Encrypt the SQL Server sysadmin password for DxEnterprise using the command dxcli encrypt-text. The encrypted password will be used to create the availability group in the next step.

    dxcli encrypt-text p@ssw0rd

  5. Add an availability group to the Vhost using the command dxcli add-ags. The SQL Server sysadmin password must be encrypted using the command dxcli encrypt-text from the previous step.

    note

    Omit the tunnel port parameter if tunneling is not enabled for your DxEnterprise license for all add-ags and add-ags-node commands.

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

Add the Second and Third Nodes to the Availability Group​

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

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

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

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

Add Availability Group Databases and Create an AG Listener​

  1. If a database is not already available, create it using sqlcmd on the primary replica.

    sqlcmd -S localhost -U SA -P p@ssw0rd -Q "create database db1"

  2. Add databases to the availability group from the primary using the command dxcli add-ags-databases.

    dxcli add-ags-databases vhost1 ags1 db1

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

    dxcli get-ags-detail vhost1 ags1

  4. Optional: Add a listener using the command add-ags-listener. 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).

    caution

    The listener port is not functional with SQL versions prior to SQL Server 2017 CU22 and SQL Server 2019 CU7.

    tip

    To connect to an availability group listener via Kerberos authentication, please reference Microsoft - Connect to an Always On availability group listener, Listeners and Kerberos (SPNs).

    dxcli add-ags-listener vhost1 ags1 14033

Additional Information​