Always On Availability Groups for Microsoft SQL Server provide increased availability and scalability for Microsoft SQL workloads running on IBM Cloud.

Configuring Always On Availability Groups is usually straightforward, but it can become more complicated when you introduce portable IPs from IBM Cloud Classic infrastructure. Using portable IPs for the SQL servers, cluster IPs, and Availability Groups is a best practice on IBM Cloud.

This blog will take you step-by-step through configuring your servers with secondary portable IPs and building the failover cluster. Once your failover cluster is online with portable IPs, it is easy to configure Always On Availability Clusters and create a highly available environment for your Microsoft SQL databases.

Prerequisites

You will need to provision compute infrastructure on your IBM Cloud account in order to set up the cluster. IBM Cloud virtual server instances or bare metal servers running Microsoft Windows can be ordered via your IBM Cloud account. Don’t have an IBM Cloud account yet? Sign up here.

• Active Directory Domain Controller and DNS servers
  • IBM Cloud virtual server instances or bare metal servers can be used
  • If you are using more than one IBM Cloud data center, an additional Active Directory DC and DNS server in all data centers is recommended.
• Portable Private IP subnet on each VLAN
  • Each server in the cluster will need a portable IP assigned to the private interface in the Windows Operating System, plus an IP for the Microsoft failover cluster and another for the Always On Availability Group. Subnet size will depend on the total size of the cluster.
  • For more information on ordering IPs, review the Subnets and IPs document in IBM Cloud docs.
• Microsoft Windows Server 2016 and Microsoft SQL 2017 Enterprise
  • IBM Cloud virtual server instances or bare metal servers can be used as the database servers. Bare metal servers are recommended in cases where the database is resource-intensive.

Configure portable IPs on cluster servers

The first step is to add a selected portable IP from the block to each of the cluster servers. In this example, Server A and B are in different VLANs and use different portable subnets.

Add portable IP on Server A and Server B

The portable IP needs to be added to the server as an alias IP on the private network interface. In this example, the private adapters are Teamed, so the assignment is done on the Teamed adapter.

Using PowerShell, run the following to assign an alias IP:

On Server A, enter:

netsh int ipv4 add address "Ethernet adapter PrivateNetwork-Teamed" 10.175.10.3/26

10.175.10.3/26 is the portable IP for Server A.

On Server B, enter:

netsh int ipv4 add address "Ethernet adapter PrivateNetwork-Teamed" 10.180.10.3/26

10.180.10.3/26 is the portable IP for Server B.

Disable IPv6

Ideally, you should disable IPv6 on the cluster nodes before setting up the cluster. IPv6 can be disabled at interface level and also disabled in the registry to make this a permanent change.

Under the Properties for the private Network Adapter, disable IPv6 on Server A and Server B:

To disable IPV6 permanently on all interfaces, make a registry change with the following parameters:

1. On the Windows Start menu, click Run.
2. Enter regedit.
3. Select HKEY_LOCAL_MACHINE_SYSTEM → CurrentControlSet → Services → TCPIP6 → Parameters.
4. Right-click Parameters and add a new DWORD(32-bit) value.
5. Name the value as “DisabledComponents.”
6. Modify “DisabledComponents” with value FF.
7. Reboot the server.

Switch the default IP of the cluster servers from the primary IP to a portable IP

By default, the servers’ DNS will resolve with Primary IP. However, for the cluster, we need to resolve the host with portable IPs. To switch this default behavior on servers, execute the following steps to make the portable IP as the host resolution IP on both nodes. Please note the SkipAsSource=true flag is critical for the clustering setup, so be sure to verify the flag is set correctly before moving to the next step.

On Server A:

1. Connect to Server A with its portable IP using a Remote Desktop connection.
2. Use PowerShell to execute the command to remove the Primary IP:
   • netsh interface ip delete address name="Ethernet adapter PrivateNetwork-Teamed" addr=10.x.x.x
3. Now add the Primary IP as the alias IP using the following command:
   • netsh int ipv4 add address "Ethernet adapter PrivateNetwork-Teamed" addr=10.x.x.x SkipAsSource=true
4. Verify SkipAsSource flag is true:
   • netsh int ipv4 show ipaddresses level=verbose

On Server B:

1. Connect to Server B with its portable IP using a Remote Desktop connection.
2. Use PowerShell to execute the command to remove the Primary IP:
   • netsh interface ip delete address name="Ethernet adapter PrivateNetwork-Teamed" addr=10.x.x.x
3. Now add the Primary IP as the alias IP using the following command:
   • netsh int ipv4 add address "Ethernet adapter PrivateNetwork-Teamed" addr=10.x.x.x SkipAsSource=true
4. Verify SkipAsSource flag is true:
   • netsh int ipv4 show ipaddresses level=verbose

Add cluster servers to the domain

1. Disable public uplinks on all servers.
2. Add each server to the domain.
3. Verify that the DNS servers are updated with both the primary and portable IP addresses of all cluster servers.

Configure the cluster

After you complete the portable IP configuration and add the servers to your domain, you can proceed to set up the Windows clustering between Server A and Server B. You can complete the installation and configuration using PowerCLI commands:

1. Install the failover cluster feature on both nodes. You can use the following PowerShell command for the feature installation:
   • PS C:\> Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools
2. To use the PowerShell FailoverClusters module, you must import it using the command on both nodes:
   • PS C:\> Import-Module FailoverClusters
3. Validate hosts before creating cluster (run on Server A):
   • PS C:\> Test-Cluster -Node ServerA, ServerB
4. After the successful completion of the validation, create the cluster with the following command using the virtual IP (next available portable IP from both sites):
   • PS C:\> New-Cluster -Name mssqlclust -Node ServerA, ServerB -StaticAddress 10.175.10.4, 10.180.10.4
   • This command takes care of setting up the dependency rule (OR rule) for mssqlclust to handle multi-subnet failover. Among the two virtual IPs, only one will be online (10.175.10.4 on Server A). In a failover scenario, if Server A fails, the cluster will be online with the second virtual IP (10.180.10.4) on Server B.
5. Register both VIPs in DNS. Execute the following commands in Server A:
   • PS C:\> Get-ClusterResource "Cluster Name" | set-ClusterParameter RegisterAllProvidersIP 1
   • PS C:\> Get-ClusterResource "Cluster Name" | set-ClusterParameter HostRecordTTl 300

Configure SQL server

1. Change SQL Server service to use AD domain logon.
2. Change SQL Server Network Configuration and enable Named Pipes and TCP/IP.
3. Create a Database and run a full backup.
4. Configure a file share that all SQL servers have read/write permissions on (needed for initial database sync).

Create the Always On Availability Group

Now, you can create the Always On Availability Group using the Availability Group Wizard within Microsoft SQL Server. Once your Always On Availability Group is online, be sure to test failover situations to check the cluster and database operate as expected.

Questions?

Reach out to the author of this article at kramick@us.ibm.com or review IBM Cloud Docs for more information.