Skip to main content
Version: v22.0

SQL Server Availability Groups with Mixed Operating Systems

Introduction​

This quick start guide describes how to set up and configure MSSQL availability groups (AG) in DxEnterprise using mixed operating systems; Windows and Linux. Using this guide, the user will add a Vhost, create an AG, add databases and add an AG listener.

In the below steps, the first replica in the AG is a Windows server, the second and third replicas are Linux servers. DxEnterprise is both hardware and OS agnostic, so the command examples below can be run from any node against any other node with any supported OS.

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 (1 Windows and 2 Linux) 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 AGS features 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​

info

All of the below command examples use the built-in SQL Server SA credentials. However, you can supply alternate credentials, as long as they meet the minimum requirements as described in the following KB article: Minimum SQL Server Security Privileges for Managing Availability Groups.

  1. Connect to the first node (Windows) via RDP.

  2. Open a Command Prompt as an administrator.

  3. Change directory to the DH2i bin folder.

cd %ProgramFiles%\DH2i\bin
  1. 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 Server 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.
note

In the below command example, "vhost1" is the name of the new Vhost and "*127.0.0.1" tells DxEnterprise to not bind an IP address to the Vhost. You can choose to assign an IP address to the Vhost and/or you can choose to create an optional AG listener as described in the Add an AG Listener section below.

dxcli cluster-add-vhost "vhost1" "*127.0.0.1" "<replica_name>"
  1. 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 "<strong_password>"
  1. 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

In the below command example, "vhost1" is the name of the Vhost created in step #4 above, "ags1" is the name of the new AG to create and "5022" is the default endpoint replication port for AGs. If port 5022 is already in use, then supply an alternate port number.

dxcli add-ags "vhost1" "ags1" "<replica_name>|<instance_name>|sa|<encrypted_password>|5022|synchronous_commit"
info

For default instances, supply the instance name of "MSSQLSERVER".

Add the Second Node to the Availability Group​

Add the second node (Linux) 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" "<replica_name>|MSSQLSERVER|sa|<encrypted_password>|5022|synchronous_commit"

Add the Third Node to the Availability Group​

Add the third node (Linux) 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.

Synchronous Replica
dxcli add-ags-node "vhost1" "ags1" "<replica_name>|MSSQLSERVER|sa|<encrypted_password>|5022|synchronous_commit"
Asynchronous Replica
dxcli add-ags-node "vhost1" "ags1" "<replica_name>|MSSQLSERVER|sa|<encrypted_password>|5022|asynchronous_commit"
Configuration Only
dxcli add-ags-node "vhost1" "ags1" "<replica_name>|MSSQLSERVER|sa|<encrypted_password>|5022|configuration_only"

Add Availability Group Databases​

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

For more information about installing SQL Server command-line tools (sqlcmd), see the following Microsoft doc.

sqlcmd -S localhost -U SA -P <strong_password> -Q "create database db1"
  1. Add databases to the availability group from the primary replica using the command dxcli add-ags-databases.
dxcli add-ags-databases "vhost1" "ags1" "db1"
  1. Verify the details of the availability group using the command dxcli get-ags-detail.
dxcli get-ags-detail "vhost1" "ags1"

Optional: Add an AG Listener​

Add an AG 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​