Skip to main content
Version: Archive

SQL Server High Availability Instances for Linux Quick Start Guide

Introduction

This quick start guide describes how to set up MSSQL HA instance using DxEnterprise on Linux. Using this guide, the user will configure storage for a DxEnterprise Vhost, create the Vhost, and add a SQL instance to the Vhost.

Prerequisites

  • DxEnterprise installed on at least two VMs or bare-metal servers and joined into a cluster. For installation instructions, please reference the quick start guides for Ubuntu or RHEL/CentOS.

  • Microsoft SQL Server installed on each VM. For information about installing SQL Server on Linux, please reference: Microsoft - SQL Server Linux Setup.

  • An additional VM for iSCSI storage.

Configure the iSCSI Initiator

DxEnterprise supports any storage that is SCSI-3 Persistent Reservation compliant. These steps will guide the user through configuring an iSCSI Initiator so its storage will be presented to the servers (nodes) in the DxEnterprise cluster.

danger

Please ensure proper presentation of storage to nodes. If there is storage that is not managed by this DxEnterprise cluster presented to the nodes, it is possible to overwrite or corrupt the data held on that storage.

  1. Enable the iSCSI initiator.

    systemctl enable iscsi
  1. Open the service configuration file with a text editor such as nano or vim.

    vim /usr/lib/systemd/system/iscsi.service 
  2. Modify the lines ExecStart=/sbin/iscsiadm -m node --loginall=automatic and ExecReload=-/sbin/iscsiadm -m node --loginall=automatic, replacing --loginall=automatic with -l in both lines.

tip

Obtain the IP address of the iSCSI initiator using a command such as ip addr before proceeding to the next section.

Configure iSCSI Target for All Nodes

Run the following commands on every node participating in the DxEnterprise cluster:

  1. Discover the storage, replacing <target> with the IP of the initiator.

    iscsiadm -m discovery -t sendtargets -p <target_ip>
  2. Log into the target.

    iscsiadm -m node -login
  3. Locate the new storage device's path.

    iscsiadm -m session -P3
    info

    The attached disk should be visible at or near the bottom of the output. For example:

    • Attached scsi disk sdb State: running

Configure the Disks in DxEnterprise

DxEnterprise leverages native file system (NTFS/ext4/xfs) and shared storage technology to coordinate access to a pool of disk resources in the cluster. Managing a disk puts that disk under DxEnterprise control.

  1. After the shared disks are configured on each node, manage the disks using DxEnterprise. To manage a disk, use the command dxcli add-disk.

    tip

    The command dxcli get-disks can be used to look up a disk_id of interest.

    Example

    dxcli add-disk 50842140-be32-d3a7-45d4-3999bf3ad3a8 "Cluster Disk 1"
  2. Create a volume on the managed disk using the command dxcli create-volume.

    Example

    dxcli create-volume 50842140-be32-d3a7-45d4-3999bf3ad3a8,1073741824
  3. Format a volume on the managed disk using the command dxcli format-volume.

    tip

    The command dxcli get-disk-detail <disk_label> may be used to look up a volume ID of interest.

    Example

    dxcli format-volume 3409ed39-60c1-4f49-8186-dfface26e2a1 EXT4 Volume1 4096 quick_format:true compression:false
  4. Assign a mount point to the volume using the command dxcli set-mountpoint.

    Example

    dxcli set-mountpoint 3409ed39-60c1-4f49-8186-dfface26e2a1 /mnt/volume1

Configure the Vhost

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, file share, and/or service. Instead of using the network name and IP address of a physical server, a Vhost is created and assigned a unique name/virtual 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.

  1. To add a Vhost to the DxEnterprise cluster, use the command dxcli cluster-add-vhost.

    caution

    When a Vhost is configured, the user will need to specify at least one node to participate in the Vhost. It is recommended to create A and PTR records for each Vhost in DNS for resolution as well as add Vhost entries to each node’s local hosts file.

    Example

    dxcli cluster-add-vhost vhost1 192.168.1.10 dxemssql1,dxemssql2
  2. Assign the disks to the Vhost using the command dxcli vhost-set-diskgroup.

    info

    A diskgroup is a logical set of disks that are added to a Vhost. When a disk is added to a Vhost diskgroup, the disk will be set online on the active node and offline on all other nodes. If there is a failure of a disk within the diskgroup, the Vhost will failover onto the next available node in the cluster.

    Example

    dxcli vhost-set-diskgroup VHOST1 50842140-be32-d3a7-45d4-3999bf3ad3a8
  3. OPTIONAL: Encrypt the SQL Server sysadmin password for DxEnterprise using the dxcli encrypt-text. The encrypted password will be used to add the SQL instance in the next step.

    Example

    dxcli encrypt-text p@ssw0rd
  4. Add a SQL instance to the Vhost using the command dxcli add-instance.

    info

    Assigning a SQL Server instance to a Vhost creates a managed instance. When a SQL Server instance is added to a Vhost, DxEnterprise virtualizes the network name and IP address associated with the SQL Server instance creating a Virtual SQL Server instance. Clients can then access the Virtual SQL Server instance via the Vhost\instance name.

    Example

    dxcli add-instance vhost1\mssqlserver 1433 /mnt/volume1/data /mnt/volume1/log sa 6pnFaDrRS+W/F+dkRuPKAA==

Additional Information