Home > Storage > PowerStore > Databases and Data Analytics > Dell PowerStore: Microsoft SQL Server Best Practices > PowerStore Node Affinity
In some cases, SQL Server can be very storage-resource intensive. For I/O intensive SQL Server databases, multiple volumes are recommended. This use allows the flexibility to balance the workload across both nodes in a PowerStore appliance. The “ownership” of a volume by a node is known as Node Affinity.
PowerStore performs best when the system performance is balanced across both nodes in the appliance. The Node Affinity is set when the volumes are mapped to a host. By default, volume Node Affinity alternates between the two nodes in the appliance when mapping volumes to a host to balance the workload. Usually, this process sufficiently balances the load across both nodes in the appliances. Because every SQL Server workload is unique, and storage access patterns depend on database layout across one or more files, Node Affinity adjustments might be necessary to maximize overall system performance.
Overall system and individual appliance performance can be viewed in the Hardware section on the Performance tab. Scrolling down in the view will reveal performance by node.
Ideally, each node in the appliance will have equal performance. If the workload is unbalanced between the nodes, you can evaluate the Node Affinity of the workload and adjust.
Note: Node Affinity can only be adjusted on block volumes, vVols are system select only.
Volume node affinity is shown in the Volumes list under Storage. By default, this column is not displayed. To add it to the view, click the Columns selection icon next to the filter icon. Scroll down the list, and select Node Affinity.
System select node A and System select Node B are the system-assigned values. Node Affinity can be set using the PowerStore CLI (CLI) or PowerStore REST API. This example uses the CLI.
The following command displays the details of a volume named SQLData2-007:
pstcli -destination <PowerStore Manager URL/IP> -u <id> -p <password> /volume -name "SQLData2-007" show
To make changes, the volume id is required. You can either find the volume ID from the preceding command or append -select id to return only the volume ID:
pstcli -destination <PowerStore Manager URL/IP> -u <id> -p <password> /volume -name “SQLData2-007” show -select id
Note: The “id” used here is not displayed in the PowerStore Manager and so must be discovered.
After the volume ID is discovered, it can be used in the following command to set the Node Affinity. The following command sets the Node Affinity for volume a5edff43-e9a7-48bd-9b19-e46c7d87d7d2 to Preferred_Node_A. Valid values are Preferred_Node_A, Preferred_Node_B, and System_Select_At_Attach.
pstcli - destination <PowerStore Manager URL/IP> -u <id> -p <password> /volume a5edff43-e9a7-48bd-9b19-e46c7d87d7d2 set -node_affinity “Preferred_Node_A”
Note: When Node Affinity is set to Preferred_Node_A or Preferred_Node_B, it is no longer managed by PowerStore unless it is set to System_Select_At_Attach.