Skip to main content
Version: v22.0

SQL Server Availability Groups with DxAdmin Quick Start Guide

Introduction​

This quick start guide describes how to create and configure an availability group using DxEnterprise’s client UI utility, DxAdmin. Using this guide, you will create a Vhost, create an availability group, add databases, and add a SQL listener.

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 2017 or later installed on each server (node). Reference Microsoft documentation for instructions on installing SQL Server.

  • DxAdmin installed on a Windows machine. DxAdmin is automatically installed as part of a DxEnterprise Windows installation. To connect to a cluster via DxAdmin from outside of a cluster node, download and install the DxAdmin package on the remote machine.

  • 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.

Add a Virtual Host​

  1. Open the DxAdmin management console on a Windows client and connect to the cluster.

  2. Right-click on Virtual Hosts from the DxAdmin explorer tree and select Add a virtual host. When the Vhost Properties window appears, configure the parameters.

    info

    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.

    Add a Vhost

    1. In the Virtual Host Name box, give the Vhost a name. The name cannot include spaces.

    2. In the Virtual IPs box, specify the IP address(es) that will be associated with this Vhost. Multiple virtual IP addresses can be specified for a Vhost, delimited by a comma. The use of a loopback address (127.0.0.1) is supported but must be preceded by an asterisk (*) so it does not attempt to bind the loopback address.

      caution

      DxEnterprise will bind the virtual IP to the adapter with the same network mask. A DNS entry must exist for each Vhost/IP address. The entry must include an A record and a [PTR] record for forward and reverse lookup. Windows authentication may fail if these records do not exist. Be sure to create a DNS entry for each Vhost/IP address that you create. Dynamic DNS registration is not supported.

    3. In the Member Type box, specify the member type for the Vhost. Available options are NODE or VHOST. Select NODE to create a standard Vhost.

    4. In the Available Members box, move the nodes (or Vhosts) you want to participate in the Vhost from the Available Members column to the Selected Members column. This can be done by double-clicking each node, or selecting the nodes and clicking the right arrows.

      info

      The first member that is selected is the primary member, and the other members that are selected are backup members. You can use the up and down arrows to reorder the members in the Selected Members column.

    5. OPTIONAL: Configure any of the following optional parameters:

      Probe Port

      In the Probe Port box, specify the port(s) to use for the Internal Load Balancer health probing for cloud solutions.

      For more information on load balancers, see the AWS or Azure Load Balancer Quick Start Guide(s).

      Priority

      In the Priority box, change the priority for the Vhost.

      This setting defines the importance (from 1 to 5, 5 being most important) of the Vhost relative to other Vhost(s) running on a system. When there is a system resource (processor, memory, disk I/O, network I/O) pressure or a system failure, DxEnterprise uses this value to determine the best possible cluster member to start the Vhost.

      Auto Failback

      Select the Auto failback checkbox to enable this policy for the Vhost.

      This policy is intended to return the Vhost to its original configuration, or as close to it as possible. After the Vhost fails over to a backup node, DxEnterprise watches the health of the nodes that are higher in the list of nodes configured for that Vhost. When the health of one of these nodes is equal to or greater than the backup node where the Vhost currently resides, the Vhost will automatically attempt to fail back to that node.

      By default the Vhost remains active on the backup node as long as it is healthy.

    6. Select OK to create the Vhost.

    7. A Confirmation Dialog box will appear. Select Yes to commit the changes.

      Confirmation dialog

Add an Availability Group​

  1. Add an availability group to the Vhost by right-clicking on Vhost and selecting Add availability group.

    Add a availability group to the Vhost

  2. When the AGS Management window opens, configure the parameters.

    Availability group management window

    1. In the AGS Name box, give the availability group a unique name.

    2. In the Instance Name drop-down, select the instance you want to use on each node.

    3. Select Authenticate for each node and supply sysadmin credentials for the instance. The Valid box will be checked when the credentials authenticate successfully.

      Enter the sysadmin credentials in the SQL Server Login dialog

    4. In the Availability Mode box, select the availability mode for each replica.

      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.

    5. OPTIONAL: Check the tunnel box to create DxEnterprise tunnels between the nodes in the availability group. Tunnels allow for simplified cross-site or cross-subnet mirroring.

      note

      Your license must support tunnels to use the tunneling feature. Visit the Client Portal or run the command dxcli get-producttype to list your license features.

    6. Select OK to apply the availability group.

    7. Verify the configuration information in the confirmation dialog and select Yes to save.

The availability group has been added, and DxEnterprise will now create the availability group on each node. When this operation completes, proceed to the next section.

Manage Availability Group Databases​

Databases added to the availability group will be replicated to the other nodes in the group. To add databases for an availability group:

  1. Right-click on the availability group and select Manage availability databases.

    Manage the AG databases

  2. Select the database(s) in the Available Databases column and use the arrow buttons to add or remove them from the Selected Databases column.

    Add the availability group databases

  3. Click Submit to save.

Configure a Listener Port​

After the availability group has been added, you can configure a listener port in DxAdmin.

warning

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).

  1. Select the Availability Group under the Vhost to bring up the availability group details.

  2. Click on the pencil icon next to the Listener Port field to edit the listener port for the availability group.

  3. Click on the pencil again to finish editing, then click Yes to save the changes.

    Add a listener to the availability group

Additional Information​