DH2i DxEnterprise 21.0 Software: MSSQL Availability Groups with DxAdmin Quick Start Guide
MSSQL Availability Groups with DxAdmin
This quick start guide describes how to create and configure an availability group using DxEnterprise’s client UI utility, DxAdmin. Using this guide, the user will create a Vhost, create an availability group, add databases, and add a SQL listener.
The pre-requisites for availability groups are as follows:
- 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 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 the DxEnterprise for 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. DxAdmin downloads can be found at: https://clients.dh2i.com/Downloads/DxAdmin/Windows/
- 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 firstname.lastname@example.org.
Add a Virtual Host
- Open the DxAdmin management console on a Windows client and connect to the cluster.
- 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. 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.
- Virtual Host Name: Use this field to specify a Vhost name. Make sure the Vhost/IP-address pair is registered in DNS. The name entered in this field cannot include spaces.
- Virtual IPs: 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 (*).
NOTE: 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.
- Probe Port: 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).
- Member Type: Specify the member type for the Vhost. Available options are NODE or VHOST. Select NODE to create a standard Vhost.
- Priority: 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: 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 over to that node.
Note: By default the Vhost remains active on the backup node as long as it is healthy.
- Available Members: Move the DxEnterprise nodes or Vhosts on which the Vhost should be configured from the Available Members column to the Selected Members column. The first member that is selected is the primary member. 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.
- Click OK to apply the Vhost.
- Click Yes on the Confirmation Dialog. DxEnterprise will then create the Vhost.
Add an Availability Group
- Add an availability group to the Vhost by right-clicking on Vhost and selecting Add availability group.
- When the availability group window opens, configure the parameters.
- Give the availability group a unique name.
- 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.
- Select the instance name on each node.
- Select Authenticate for each node and supply sysadmin credentials for the instance. The “Valid” box will be checked when the credentials authenticate.
- Select an Availability Mode for each replica (synchronous_commit, asynchronous_commit, or configuration_only)
- Select OK to apply the availability group.
- 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 Databases
Databases added to the availability group will be replicated to the other nodes in the group. To add databases for an availability group:
- Right-click on the availability group and select Manage availability databases.
- Select the database(s) and use the arrow buttons to add or remove them from the Selected Databases.
- Click Submit to save.
Configure a Listener Port (Optional)
After the availability group has been added, there is an option to configure a listener port.
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.
- Select the Availability Group under the Vhost to bring up the availability group details.
- Click on the pencil icon next to the Listener Port field to edit the listener port for the availability group.
- Click on the pencil again to finish editing, then click Yes to save the changes.
- DH2i Support Portal
- DxEnterprise v21.0 Admin Guide
- DxEnterprise v21.0 Documentation
- Quorum Considerations for SQL Server Availability Groups
- Microsoft – Install SQL Server