Create Compute Policies to force Anti-Affinity

To avoid creating a single point of failure, it’s important to ensure that VMs hosting clustered SQL Server workload are not running on the same host. If your SDDC is configured as a stretched cluster spanning multiple AZs in the AWS infrastructure, Compute Policies also should enforce the placement of VMs into different AZs.

NOTE : Affinity policies in VMware Cloud on AWS SDDC are not the same as vSphere DRS affinity rules created on-premises. These can be used in many of the same ways but have significant operational differences. A compute policy applies to all hosts in an SDDC and cannot typically be enforced in the same way that a DRS must policy is enforced.

The following high-level steps are required to implement the described compute policies:

vSphere tags should be applied to all hosts in the SDDC, one tag per an AZ (if stretched cluster is used)
vSphere tags should be applied to all VMs, one tag per SQL Server availability group and one tag per each node

NOTE: For SDDCs residing in the same AZ creation of host-based tags are not required

Step 1. Create vSphere Categories and Tags

To create vSphere tags for each AZs:

Navigate to the Menu, then Tags & Custom Attributes

Select Tags, then Categories

Proceed with creating new categories as indicated below:

Fault Domain to be used with host tags for AZs

SQL Server Always On to be used with VMs tags for SQL Server Always On deployments

NOTE: Assuming the given VM host databases residing in different AGs, selecting Many tags allows assignment of multiple tags within the same category to one VM.

Continue creating the required vSphere tags using the previously created categories. Create a tag for each AZ using, as a best practice, the AZ name that can be found on the Host Summary page. In this case, the following tags have been created:

us-west-2b
us-west-2c

NOTE: This step should be executed only once. Host-based tags can be reused for each consecutive deployment.

Continue adding tags for each Always On AG. It is recommended to use the availability group name for your tag. Each VM host must be tagged individually with a unique node tag, along with a separate tag for the Always On AG. Here, the following tags have been created:

SQL-VMC-AG1-DB1-Node1
SQL-VMC-AG1-DB1-Node2
SQL-VMC-AG1-DB1

Step 2 - Assign created tags to required objects

First, assign tags to all hosts in the SDDC to reflect their AZ membership. Here, us-west-2b is assigned to all hosts in the AZ us-west-2b and the tag us-west-2c to all host located in the AZ us-west-2c.

Navigate to your vSphere HA cluster in the SDDC on VMware Cloud on AWS, highlight the cluster name and navigate to the HOST tab. Use the Show/Hide Column picker to add the Fault Domain column:

Highlight a host, select the Tags & Custom Attributes menu, choose Assign Tag:

Select the tag corresponding to the host’s fault domain:

Continue assigning tags to all host in the cluster.

Confirm all tags are assigned using the Summary page of the host view:

Now assign tags to all VMs hosting databases in AG. In total, two tags should be assigned to each VM: a node identifier to map to an AZ and a cluster identifier to be used for a VM anti-affinity policy (to separate VMs between hosts within one AZ).

Select the vSphere folder, in which all VMs hosting SQL Server workloads are located:

Highlight and right-click a VM to assign tags. Alternatively, navigate to the Summary page, locate the Tags widget, and use Assign Tag:

NOTE: Tag assignment can be automated with PowerCLI.

Step 3 – Create Compute Policies

Once tags are assigned to all required objects, move forward with the creation of Compute Policies.

Navigate to the Menu, then Policies and Profiles. Select Compute Policies:

Start with creating VM-Host affinity policies. Two are required.

Click Add and populate the required fields:

Name: SQL Always On Node 1 – AZ 1
Policy type: VM – Host affinity (VM should run on the specified hosts)
VM tag: Specify Category (SQL Server Always On) and Tag (SQL-VMC-AG1-DB1-Node1)
Host tag: Specify Category (Fault Domain) and Tag (us-west-2b)

Click Create to add the new policy. Proceed with creating the policy for Node2:

NOTE: If the number of nodes and respective VMs in one availability group is more than two, distribute the VMs evenly between AZs.

In addition, it’s necessary to create a policy separating VMs hosting availability groups within one AZ to ensure that in each AZ, all SQL Server nodes will run on separate hosts. This can be achieved using the previously added tag with cluster name.

Add the new policy and specify the following settings:

Name: name of the policy (SQL Always On Separate VM)
Policy type: VM – VM anti-affinity
VM tag: Category SQL Server Always On, Tag SQL-VMC-AG1-DB1

Step 4 – Verify the configuration

Once all policies have been created, check that they are assigned to VMs. To do this, highlight a VM and navigate to the Summary page. Scroll to the Compute Policies widget and verify that the required policies are assigned. Each VM should have two policies assigned:

SQL Always On Node 1 – AZ1: the VM will preferably be running only on the hosts located in the same AZ.
SQL Always On Separate VMs: VMs hosting SQL Server workloads and running within the same AZ will preferably run on different hosts.

NOTE: If an SDDC hosting SQL Server AGs is based in a single AZ only, a VM anti-affinity policy must be created to redistribute VMs between hosts.

NOTE: Distributing nodes of SQL Server AGs between AZs may increase replication latency and the execution time of queries.