DH2i DxEnterprise 19.5 Software: Highly Available SQL Server AGs

Download the PDF

DxEnterprise for Availability Groups

DxEnterprise enables SQL Server Availability Groups to be made highly available within and between Windows and Linux nodes and across any type of infrastructure—all without relying on cumbersome and restrictive cluster orchestration technologies such as Pacemaker or Windows Server Failover Clustering (WSFC). DxEnterprise provides advanced fault detection and failover automation to minimize outages for SQL Server databases, helping customers achieve nearest-to-zero total downtime. The DxEnterprise tool is the only technology on the market that enables highly available SQL Server Availability Groups across mixed Linux and Windows nodes and mixed Linux distributions. Use this tool to make your AG deployments easier to set up and manage. This functionality requires the use of Microsoft’s SQL Server 2017 or later release.  Additionally, the creation of an availability group under a Vhost simplifies common configuration criteria.

DxEnterprise for Availability Groups also integrates seamlessly with DH2i’s network security technology to make it easy to create highly available multi-server / multi-site / multi-cloud Availability Groups. Without exposing any open ports or requiring the use of a VPN, DxEnterprise automatically creates the networking tunnels between the nodes that are required for cluster communication and Availability Group replication. These micro-tunnels then transmit TCP payloads via UDP for superior performance and security.

Add Availability Group

DxEnterprise allows the creation of an availability group within SQL Server and to manage it across Linux and/or Windows nodes.

Note: To enable Availability Group management and/or tunneling features, please contact your DH2i account representative.

The pre-requisites are as follows:

  • A DxEnterprise cluster configured. The tunneling feature must be activated if you intend to build a cross-site or cross-subnet AG.
  • A Microsoft SQL Server 2017 or later instance on each node.
  • A Vhost that does not have a Docker, Instance, or service configured.

Steps to Add an Availability Group

  1. Open DxAdmin management console on a Windows client and connect to the cluster.
  2. Create a Vhost (see the DxEnterprise Administration Guide for further details).
  3. To add an availability group, right-click on a Vhost and select Add availability group.

  1. The AGS Management window then opens.

  1. Give the availability group a unique name.
  2. Check the Tunnel box to create a tunnel between all of the nodes in the group if needed (cross-site mirroring or cross subnet mirroring).
  3. Select the Instance name on each node.
  4. Click on Authenticate for each node to supply sysadmin credentials for the instance. Once authenticated the Valid checkbox will be checked.

  1. Select the Availability Mode (Synchronous, Asynchronous or Configuration Only)
  2. Click OK to add or Close to exit without making changes.

Configure a Listener Port

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, the availability group listeners are only functional with SQL Server 2019 and later.

  1. Left click on the name of the Availability Group.
  2. Click on the pencil icon to edit the Listener Port field.
  3. Click on the pencil again and then click Yes to save changes or No to discard the changes.

Manage Availability Databases

To add or remove databases from 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 or Close to cancel.

Attach Availability Group

DxEnterprise allows the user to attach an existing availability group and manage it across Linux and/or Windows nodes. 

Note: To enable Availability Group management and/or tunneling features, please contact your DH2i account representative.

The pre-requisites are as follows:

  • All nodes in the Vhost must have Microsoft SQL Server 2017 or newer installed and are members of an availability group prior to attaching it.
  • The Vhost does not have a Docker, Instance, or service configured.
  • Stop any cluster software that previously managed the availability group. I.e. If using Pacemaker then stop the Corosync and Pacemaker services.
  • Availability Groups with a cluster type of WSFC are not eligible for attachment.

Steps to Attach an Availability Group

  1. Open DxAdmin management console on a Windows client and connect to the cluster.
  2. Create a Vhost (see the DxEnterprise Administration Guide for further details).
  3. To attach an availability group, right-click on a Vhost and select Attach availability group.

  1. The Attach Availability Group window then opens.

  1. Select the Instance name in the dropdown box.
  2. Click on Authenticate for each node to supply sysadmin credentials for the instances. Once authenticated the Valid checkbox will be checked.

  1. Click on Search for Availability Group and give it a moment to find any existing Availability Groups.

  1. Select the Availability Group to attach.
  2. Click OK to attach or Close to exit without making any changes.

Detach Availability Group

To detach an availability group but not delete it from SQL Server, right-click on the availability group and select Detach availability group. This will allow the AG to be imported again later.

Remove Availability Group

To remove the availability group from both the cluster and delete it from SQL Server, right-click on the availability group and select Remove availability group. Warning: this option will completely remove the availability group configuration from SQL Server.

Modifying AGS Credentials

If the sysadmin credentials inside of SQL Server are modified, then the sysadmin credentials used by DxEnterprise to manage the availability group will also need to be modified. To modify the credentials used by DxEnterprise, use the following method.

  1. In DxAdmin, select the availability group under the Vhost.
  2. In the availability group details pane, click on the Advanced bar.
  3. The Replica Credentials section will then be visible.

  1. Select Edit to modify the Login and/or Password for the sysadmin user(s).
  2. Click Submit to save the changes.

 

Download the PDF

 

References

DxEnterprise Admin Guide – https://dh2i.com/wp-content/uploads/DxEnterprise-v19.5-Admin-Guide.pdf

Availability groups defined – https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server?view=sql-server-ver15

How Pacemaker is setup on Linux – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-deploy-pacemaker-cluster?view=sql-server-2017