This reference architecture describes how to deploy a Microsoft SQL Server container on PowerFlex and make the best use of Linux, Red Hat OpenShift, and Dell PowerFlex infrastructure to implement a reliable transaction processing system to meet current business demands.

This solution also highlights the scalability that can be obtained when pairing Microsoft SQL Server database workloads with Dell PowerFlex software-defined storage together. PowerFlex gives the ability to quickly scale multiple SQL Server instances with the ease to unleash the true database performance. Red Hat provides the OpenShift Container Platform that helps to deploy and manages database pods with the persistent storage that is backed by PowerFlex CSI driver.

The PowerFlex software-defined infrastructure along with its CSI driver plays key role in storing mission-critical data. It helps to provision, manage, and scale the storage to meet organizational requirements. This reference architecture also provides the best practices for PowerFlex, SQL Server, and OpenShift to optimize database performance.

This document presents a reference architecture for Red Hat OpenShift 4.10 on Dell PowerFlex infrastructure. 

This document also provides a brief introduction to Red Hat OpenShift Container Platform, PowerFlex CSI driver, and Microsoft SQL Server on containers.

Note: The contents of this document are valid for the described software and hardware versions. For information about updated configurations for newer software and hardware versions, contact your Dell Technologies sales representative. 

The following table provides definitions for some of the terms that are used in this document.

Table 1.      Terminology 

This reference architecture is intended for SQL Server database administrators, DevOps system engineers, partners, and members of Dell and partner professional service community who are looking for deploying SQL Server database on Dell PowerFlex integrated rack on a containerized environment that is orchestrated by OpenShift.

The reader of this document must have a working knowledge of the following technologies:

• Dell PowerFlex integrated rack
• Red Hat Linux
• SQL Server database administration
• Red Hat OpenShift Container Platform

Dell Technologies and the authors of this document welcome your feedback on the solution and the solution documentation. Contact the Dell Technologies Solutions team by email. 

Author: Anup Bharti

Note: For links to additional documentation for this solution, see Dell Technologies Solutions Info Hub for SQL Server

PowerFlex family 

PowerFlex is a software-defined infrastructure platform that reduces operational and infrastructure complexity. PowerFlex empowers organizations to move faster by delivering flexibility, elasticity, and simplicity with extraordinary predictable performance for mission-critical workloads, while also providing resiliency at scale. The PowerFlex family provides a foundation that combines compute and high-performance storage resources in a managed, unified fabric. 

PowerFlex delivers transformational value

Delivers stringent SLAs effortlessly

PowerFlex uses software to unlock the full potential of rapid advances in industry-standard hardware and deliver extreme SLA outcomes. PowerFlex aggregates resources across a broad set of nodes, unlocking massive input, output, and throughput performance while minimizing the latency. Its self-balancing architecture eliminates any hotspots and ensures consistency and simplicity over time. The system can be scaled while linearly scaling performance from a minimum of four nodes to thousands of nodes, on-demand and without any disruption. And with its self-healing architecture, PowerFlex can handle outages, upgrades, and maintenance minimizing the downtime resulting in 99.9999* percent availability.

Note: *Workload performance claims based on internal Dell testing. (REF-0000470)

Flexible and dynamic architecture

PowerFlex delivers transformational agility that enables organizations to rapidly respond to changing business needs. PowerFlex offers flexibility to mix and match the storage, compute, and hyperconverged nodes in a dynamic deployment, allowing you to scale storage and compute resources together or independently, one node at a time as per your requirements.

Shared platform for heterogeneous workloads

The platform can support a broad range of operating environments simultaneously such as, bare-metal operating systems, hypervisors, and container platforms with a unified underlying infrastructure platform and management. You can also support heterogeneous workloads with varying requirements on a flexible shared infrastructure platform and modernize your application architectures on your schedule. 

Extensive automation for predictability and simpler workflows

PowerFlex offers full-stack IT Operations Management (ITOM) and Life Cycle Management (LCM) with PowerFlex Manager. It provides extensive automation capabilities with PowerFlex Manager REST APIs and custom Ansible modules to integrate with your infrastructure, application, and DevOps workflows. PowerFlex Manager enables automated deployments and expansions with minimal firsthand time for the IT team, letting them focus on other strategic initiatives. 

Broad ecosystem of workload solutions

PowerFlex is designed to deliver superior outcomes at any scale for the most-demanding mission-critical environments. It is optimized for a wide range of validated workload solutions ranging from traditional relational databases and modern cloud-native NoSQL databases to throughput-intensive analytics workloads. 

Figure 1.   PowerFlex Portfolio

PowerFlex software components 

Software is the key factor of success in the PowerFlex offering. PowerFlex software components provide software-defined storage services. The software components help to simplify the infrastructure management and orchestration with comprehensive ITOM and LCM capabilities that span compute and storage infrastructure, from BIOS and firmware to nodes, software, and networking. 

PowerFlex

PowerFlex is the software foundation of the PowerFlex platform. It delivers high performance, highly resilient block storage service that can scale to thousands of nodes.

PowerFlex Manager

PowerFlex Manager is the software component in PowerFlex family that enables ITOM automation and LCM capabilities while enabling flexible APIs and extensive automation.

PowerFlex consumption options

PowerFlex is available in multiple consumption options to help customers meet their project and data center requirements. The PowerFlex appliance and PowerFlex rack provide customers the flexibility to choose a deployment option to meet their exact requirements.

PowerFlex rack

The PowerFlex rack is a fully engineered system, with integrated networking that enables the customers to simplify deployments and accelerate time to value. 

PowerFlex appliance

The PowerFlex appliance allows customers the flexibility and savings to choose their own compatible networking. The PowerFlex appliance offers customers a smaller starting point of four nodes, while enabling them to use their existing network infrastructure. 

With PowerFlex, the customers deploy to match their initial needs and expand with massive scale potential, without having to compromise on performance and resiliency.

PowerFlex is available through APEX custom solutions by the APEX Flex on Demand and APEX Datacenter Utility for customers looking to adopt consumption-based OPEX models. 

APEX Flex on Demand 

APEX Flex on Demand allows you to pay for technology as you use it and provides immediate access to buffer capacity. Your payment adjusts to match your usage.

APEX Datacenter Utility

APEX Datacenter Utility is the leading product portfolio from Dell Technologies that provides support to manage your data center and its operations. It provides a simple and single invoice for monthly payments that are based on a predictable rate and vary based on your usage.

Red Hat OpenShift Container Platform (OCP or “OpenShift”) is an enterprise-class open-source container platform that is based on the Kubernetes open-source project. It supports both virtualized and bare-metal deployment models and includes some specialized components including Operator Hub and Operator Life-cycle manager. OpenShift Container Platform is a traditional Kubernetes platform. The Kubernetes core components are installed on a Linux based platform (Red Hat CoreOS). Some elements that make this distribution unique, include the use of Podman and CRIO as the preferred container engine, a unique application delivery platform of Operator Life-cycle Manager (OLM), and implementation of Cluster API (CAPI) for bare-metal bootstrapping.

OpenShift clusters built in data centers may be installed using installer provisioned infrastructure (IPI) or user provisioned infrastructure (UPI). The IPI automates the deployment and creates the required infrastructure such as nodes and application load balancers. This document uses the UPI method of installation and relies on a repository to assist with infrastructure setup.

Red Hat OpenShift natively offers ephemeral storage support for containers, where the data is cleared when the container is restarted. Even when persistent storage volumes are used, they are stored as files in the local server file system, where if the server is offline, then the data is lost. This creates challenges with the pod portability and data resiliency. Container Storage Interface (CSI) APIs were developed with native Kubernetes solutions to provide a standard for provisioning and managing on-demand block and file persistent volumes for containerized applications. The persistent volumes are created outside the host operating system, such as on the PowerFlex system, making the volume data available across the infrastructure without regard to the availability of a single server.

Dell storage solutions provide unique CSI plugins that allow customers to provision persistent PowerFlex storage volumes for container-based applications for both development and production scale. The combination of the Red Hat OpenShift cluster and PowerFlex CSI plug-in enables easy provisioning of persistent storage for the containers.

For more information about the PowerFlex container storage modules, see Dell CSI Driver Documentation.

Microsoft first introduced SQL Server containers on Linux with the release SQL Server 2017 and is expanding these capabilities with SQL Server 2019. Microsoft has made it even easier to adopt SQL Server in containers by enabling new high availability (HA) scenarios. Microsoft SQL Server 2019 delivers breakthrough mission-critical capabilities with in-memory performance and operational analytics that are built in while comprehensive security features help protect your data at-rest and data in-motion, and now support for integrated big data. 

A few use cases for deploying Microsoft SQL server on OCP cluster are:

• Using database containers for local dev/test environment in DevOps pipelines.
• Running OLTP workload for a production environment.
• Providing database as a service in a SaaS like environment.

SQL Server on OpenShift Container Platform (OCP) offers many advantages for DevOps because of its consistent, isolated, and reliable behavior across environments, ease of use, and manageability. Applications can be built on top of SQL Server containers and run without being affected by the rest of the environment. This isolation makes SQL Server in containers ideal for production deployment scenarios and DevOps processes.

For more information about Microsoft SQL server features and new capabilities, see SQL Server 2019 Features.

In this solution, bare-metal servers are used to deploy PowerFlex and the Red Hat OpenShift container environment. Microsoft SQL Server 2019 pods are deployed on the PowerFlex hyperconverged environment.

This solution can also be deployed on any of the other supported PowerFlex deployment models.

The following diagram shows the logical architecture of the containerized SQL Server instances that is deployed on eight PowerFlex hyperconverged nodes with Red Hat OpenShift cluster. It also illustrates the OpenShift cluster that has three control planes and eight worker nodes. Same PowerFlex cluster nodes are acting as worker nodes in the setup. There are three deployments that are involved in this architecture: 

• PowerFlex deployment
• OpenShift Cluster deployment 
• SQL Server pod/container deployment

Figure 2.   Logical architecture of SQL Server 2019 with OpenShift containers

PowerFlex deployment

The PowerFlex cluster is built from eight bare-metal nodes that are configured in the hyperconverged deployment model. PowerFlex cluster leverages its storage data server (SDS) component to provide resilient storage layout. 

Each node is populated with ten solid state drives (SSDs) of size 960 GB. These total 64 disks from eight nodes are aggregated to make one resilient storage pool and the usable storage volumes are carved out as required. SDSs are optimized for rebuild, rebalance, and I/O parallelism. The user data layout among SDS components is managed through storage pools, protection domains, and Volumes. PowerFlex storage layout for this reference architecture is as follows:

• Single Protection Domain (domain 1) is allocated from the SSDs in the eight PowerFlex nodes. 
• Single Storage pool (SP-SSD-1) is created on a new protection domain and then persistent client volumes are created from this Storage Pool. 

All the SQL Server pod volumes are provisioned by the PowerFlex software from the storage pool as requested by the PowerFlex CSI driver. For more information about PowerFlex recommendations, see PowerFlex Best Practices.

For more information about the configuration of PowerFlex nodes and VM configuration, see Appendix Configuration details.

OpenShift cluster deployment

This section outlines the basic constructs of an OCP deployment on PowerFlex family.

The OpenShift cluster contains the following important elements: 

• Helper node
• Bootstrap node
• Control plane node
• Worker node

Helper node

The Helper node is the intermediate host that helps to deploy and manage the OCP cluster. It is a Linux host with OpenShift binaries that are installed to deploy and manage the cluster. In this guide, the helper node is seen as the service node and is deployed on to a separate VM.

All CLI-based commands are run from the service node. This guide uses Red Hat Enterprise Linux 8 minimal ISO for operating system installation. The service node is configured to host the OpenShift installation binaries and to generate the ignition file to deploy all the nodes of the cluster.

Bootstrap node

The bootstrap node is the first node to be booted to the custom ISO. The purpose of the bootstrap node is to configure the control plane and install the control plane components on the active nodes. This configuration is achieved by creating a temporary OCP cluster for deploying the control plane API and the control plane nodes. Once the master nodes are up and the bootstrapping process is complete, the bootstrap node is used for the OCP installation process. In this architecture, the bootstrap node is deployed on the VM. 

Control plane nodes

Control plane nodes create, schedule, and manage all aspects of the OCP cluster. control plane nodes can be configured to allow for pods to be scheduled on them, that is, for them to serve as worker nodes. This solution does not schedule workloads on control planes. The following is a list of the other main services that the control plane nodes are needed for:

• API interface
• Authentication or authorization
• Container scheduling
• Controller management
• Configuration database and etcd

Kubernetes manages the containers using a declarative model where the required state of the cluster is maintained. The control plane (Master nodes/API) continuously monitors the state. Controllers check for the current and the required state in loops. When a configuration change is detected, the control plane responds by updating the state of the resource. For example, to change any kubelet on OpenShift nodes a machine configuration YAML resource file is applied to the cluster. Once the control plane realizes that the required state of the nodes no longer matches the running state, it corrects the state by implementing the change that is applied using the machine configuration file.

Three control plane nodes are hosted on separate VMs to avoid using the resources from the PowerFlex HCI nodes. Red Hat Enterprise Linux Core operating system (RHCOS) is the default operating system on all the control plane machines.

Worker nodes

In the OpenShift Container Platform 4.9, you have the option of using the Red Hat Enterprise Linux (RHEL) machines as a compute machine that is also known as worker machines, in a user-provisioned infrastructure installation method of cluster deployment.

This reference architecture uses the PowerFlex HCI nodes as worker nodes that are deployed with the Red Hat Enterprise Linux operating system. Eight worker nodes are used to host the SQL Server 2019 workload.

Following are some of the pre-requisites before adding the Red Hat Enterprise Linux based compute or worker nodes to the cluster:

• Must have a valid OpenShift container platform subscription with the Red Hat account.
• Base operating system on the worker nodes should be RHEL 8.4.
• Separate RHEL 7 machine is required to run an Ansible playbook and add the worker node to the cluster.
• Must have OpenShift CLI installed on the RHEL 7 machine.
• Ensure that the RHEL 7 machine can access all the worker nodes and the helper nodes. 
• Enable the password less SSH to all the worker nodes and the helper node.

For more information about adding an RHEL worker node in the OCP, see adding RHEL compute nodes to a cluster.

This reference architecture follows the user-provisioned method of cluster deployment. Every node, component is deployed manually by the user and not with the installer based provisioned installer. The following table shows the different nodes that are deployed in the cluster:

Table 2.      OpenShift cluster node details

Note: For more information about the configuration of OpenShift nodes and VM configuration, see Appendix Configuration details

PowerFlex CSI deployment 

The PowerFlex CSI driver is used to provide persistent storage to OCP through the PowerFlex storage system. The CSI driver installs the custom resource definition (CRD) in the storage.dell.com API group and requires a few input parameters to be provided such as: the PowerFlex end point (gateway details) and the system name to connect to the PowerFlex cluster. A secret key is created in the namespace to connect to the end point. This secret key stores the credentials to connect to the PowerFlex cluster. Upon successful deployment of the CRD. 

The following table shows the storage controller and CSI pods that are deployed in the cluster:

Table 3.      Controller and CSI pod details

Each CSI pod facilitates the storage provisioning and the file system creation on the worker nodes. CSI uses the storage classes to dynamically provision the volumes. A storage class is an object that describes the policy for the quality-of-service levels. These policies define the provisioner reclaim policy and driver details. The PowerFlex CSI provides YAML files to deploy the storage classes. EXT4 and XFS are the two standard storage class templates by PowerFlex CSI. It is recommended by Microsoft to use XFS for database volumes. Hence, the XFS storage class is deployed to provision the volumes with the XFS file system. 

Table 4.       CSI storage class

The CSI driver for Dell PowerFlex can be deployed by using the provided Helm v3 charts and installation scripts on both Kubernetes and OpenShift platforms. For more information about the PowerFlex CSI installation using Helm charts, see PowerFlex CSI Documentation.

SQL Server pod/container deployment

This reference architecture deploys the SQL Server as a stateful application. SQL pods are deployed across eight worker nodes. Each node hosts two SQL Server pods. Each pod is hosting a single container image of the SQL Server 2019 instances and the data is stored on the persistent volume provisioned by the PowerFlex CSI plug-in. The controller verifies if the current state matches the wanted deployment state, and creates a ReplicaSet if necessary, and then creates the pods. 

The following table shows the deployment of the SQL server pods on the OCP cluster:

 Note: The names may be different in your environment.

Table 5.      SQL Server pod details

SQL server ConfigMap

This solution uses the ConfigMap object to pass the SQL Server startup parameters. This ConfigMap file provides a few important parameters for each SQL Server pod. It defines the default data and log directory path to the pod. This is an important Kubernetes object used to pass the required information to the application deployed.

The ConfigMap is like the secret key, but it is designed to support working more conveniently with strings that do not contain sensitive information.

To get an optimal database performance it is a good practice to design the database layout. The SQL Server Database files are mostly categorized as random and sequential files. Storing all the files in one partition/drive is a sign of bad database design. To fully use the SDS storage pool capacity and I/O performance, database and log files are separated across different volumes for better I/O throughput.

The following figure shows the SQL server pod database layout. The layout includes database files; transaction log files and temp files distributed across different volumes.

Figure 3.   SQL server Pod database layout

As a best practice, The TempDB files are moved from their default location to another persistent volume to optimize the database performance. Preallocating space for TempDB and assigning a large file size accommodates most typical workloads and results in  optimized performance. Also, increasing the number of tempdb files reduces the database contention. This reference architecture recommends configuring 16 tempdb files to improve the database performance.

The following table shows both the volume size and file systems used for this solution to store a sample database:

Table 6.      Pod storage details

The following diagram shows the network architecture configured for this solution using the PowerFlex best practices:

Figure 4.   HCI network configuration

• Two Dell S5048F switches are configured with VLT to provide fault tolerance and enable connectivity with other switches. Storage nodes, Compute nodes, Management, and other teamed networks use the Link Aggregation Control Protocol (LACP).
• Two dual-port 25 Gb Mellanox NICs on each server provide 4 x 25 Gb ports.
• SDS and SDC data communication use dedicated 2 x 25 G uplinks in bond topology.
• Application management and PowerFlex management use another dedicated 2 x 25 G uplink in bond topology.

For more information, see PowerFlex rack architecture.

This section describes the tests that are done to validate the SQL Server 2019 performance on an OpenShift platform that is hosted on the PowerFlex rack. The HammerDB benchmark tool is used for database performance tests. HammerDB is a benchmarking tool and load testing software for popular databases. It focuses on schema creation, data loading, and simulating any transactional and analytic workloads. Results of these workload tests further derive meaningful information from the environment such as hardware performance comparison and Software configurations. For more information, see HammerDB official site.

To validate the performance of the SQL Server 2019 on a containerized environment, an OLTP workload is simulated by using the HammerDB tool. OLTP workload typically runs a specific set of queries (read and write) against the database. These set of queries are called database transactions and is measured in TPM (Transaction per minute).

For this reference architecture, a sample database is populated with 100 GB of data on each SQL Server pod and multiple virtual user tests are run for a fixed interval of time against each pod to get optimal TPM numbers. The lab setup is configured as described in the logical architecture section of this document. The following figure shows the test methodology:

Figure 5.   HammerDB load testing

The HammerDB tool is installed on dedicated VMs. These VMs are hosted on separate ESXi servers so that the CPU resources are not consumed from the worker nodes. A real-life scenario is stimulated where the clients are not running on the same nodes as the actual database. Therefore, the load is generated in a client/server model as shown in Figure 5, and the resources of the worker node are dedicated to the SQL Server pods only. 

Note: In this reference architecture, we are testing with dedicated VMs running on VMware vSphere. Alternatively, the OCP worker nodes can be deployed on another virtualization platform or bare metal servers. 

Each VM is running two HammerDB test tools (instances), each configured to a stand-alone SQL Server pod. Therefore, each VM is pointing to 2 SQL Server pods that are hosted on a dedicated worker node. A dedicated 25G connection is used between the ESXi host and the worker nodes for the client network. Multiple runs were performed by simulating the number of virtual users against the sample database that is populated on all the pods. 

Following two scenarios have been tested using the above methodology:

• Scale out test – Maximum number of pods in the cluster.
• Scale up test – Increasing the CPU/memory resources of the pods in the cluster.

More information is available in the best practices section to tune the pods and SQL Server for the above tests.

Scale out test

The objective of this test is to check the scale out performance of the SQL Server pods across the OCP cluster. Four pods containing 16 CPUs per pod are deployed on each node scaling to 32 (eight nodes * four pods per node) pods across eight worker nodes maximizing the compute capacity of the worker nodes. Thirty-two HammerDB instances generate the OLTP load simultaneously by running the Benchmark. One to one mapping is considered between HammerDB instance and SQL pods to simulate a real-life client/server scenario.

The following graph shows the TPM performance for 32 pods running on an OpenShift cluster containing three control plane and eight worker nodes:

Figure 6.   Scale out test graph

As seen from the above test results, approximately 8.2 million TPMs are achieved with sub millisecond read and write latency.

Scale up test

This test focuses on scaling up SQL Server pods with high CPU resources. Two pods containing 32 CPUs per pod are deployed across each node. A total of 16 (eight nodes * two pods per node) pods are deployed across eight worker nodes maximizing the available compute capacity. Sixteen HammerDB instances generate the OLTP workload simultaneously. SQL Server pods and HammerDB instances are mapped in a 1:1 ratio depicting a client/server scenario.

The following chart shows the TPM performance for 16 pods of 32 CPUs each on an OpenShift cluster containing three control plane and eight worker nodes:

Figure 7.   Scale up test graph

As seen from the above test results, the maximum transactions achieved are 7.5 million TPMs under sub millisecond read and write latency.

This section describes the best practices derived from this reference architecture and the standard practices used to deploy the PowerFlex cluster. Most of the PowerFlex best practices are standard performance recommendations whereas SQL Server, Linux, and OpenShift best practices are documented after running multiple tests with various parameters and settings.

PowerFlex recommends the following best practices to optimize the performance:

• Enable Jumbo frames for Windows VM, Linux VM, or at ESXi host for the network interfaces.
• If a node running an SDS has aggregated links to the switch and is running Linux, the default hash is configured to xmit_hash_policy=layer2+3. Change the default hash to xmit_hash_policy=layer3+4 mode to evenly distribute the package across bonded slaves.
• Use the miimon=100 bonding option on a Linux machine. This option directs Linux to verify the status of each physical link every 100 milliseconds.
• Enable secured network protocol options only (for example, HTTPS and SSH).
• Separate management and control traffic from production application traffic. VLANs can be used to separate the traffic.
• Ensure that each node have at least two separate network connections for redundancy, regardless of throughput requirements.

For more information about networking practices, see Dell PowerFlex: Networking Best Practices and Design Considerations

• Configure a high-performance profile for MDM, SDS, and SDC.
• Disable Read Flash Cache and Read RAM cache for all the flash clusters.
• Ensure that the per device queue length value is set to 256 per host for improving the I/O concurrency.
• Ensure that the customize power plan is set to High Performance.
• Ensure to change SDS threads to 12 when the CPUs have 12 or more physical cores. For the current reference architecture SDS threads are set to 12.
• It is recommended to use the Noop I/O scheduler for all the SSD devices discovered on the SDC hosts.

For more information, see the PowerFlex tuning guide.

Use the following Linux operating system configuration settings to optimize the SQL server database performance:

File system recommendations

• SQL Server supports both EXT4 and XFS file systems to host the database, transaction logs, and additional files such as checkpoint files for in-memory OLTP in SQL Server. Microsoft recommends using XFS file system for hosting the SQL Server data and transaction log files.
• Dell PowerFlex CSI helps to implement the above recommendation by applying a different file system storage class. Both EXT4 and XFS storage class options are available to implement. By defining the correct storage class in the manifest file CSI creates the respective file system based on EXT4 or XFS persistent volume on the SQL server pod.
• Set open file limit to maximum of 60000 on all the worker nodes deployed with RHEL operating system.

Disk settings recommendations

• Modify the max_sector_kb to 1024 for all database volume.

     For example, echo 1024 > /sys/block/sdc/queue/max_sectors_kb

• Modify the nr_requests to 1024 for all database volumes.

For example, echo 1024 > /sys/block/sdc/queue/max_sectors_kb/nr_requests

• Set the disk readahead settings to 4096 or more for high throughput storage.
• Microsoft recommends the following sysctl changes to optimize the disk throughput:

kernel.sched_min_granularity_ns = 15000000
kernel.sched_wakeup_granularity_ns = 2000000
vm.dirty_ratio = 80
vm.dirty_background_ratio = 3
vm.swappiness = 1

Kernel settings for high performance

Microsoft recommends tuning a few kernel parameters to boost the database performance as follows:

• Disable NUMA balancing to allow the SQL Server to operate at maximum efficiency on NUMA node.

kernel.numa_balancing=0

• Enable transparent huge pages (THP) to get the most consistent performance from the database. This is the default setting with the latest Linux builds.

vm.transparent_hugepages=madvise

• Change the virtual address space settings for the SQL server deployment. Change the value to at least 262144 for optimal performance.

                        vm.max_map_count=262144

For more information, see Microsoft performance best practices.

Using OpenShift node tuning operator

This reference architecture uses the node tuning operator to push the recommended Linux settings to the worker nodes. For more information about the tuning operator, see Using the node tuning operator.

Perform the following high-level steps to create your own tuned CRs to be used with tuning operator:

1. Access the default CR set on the cluster in the YAML format with the following command: -

$ oc get Tuned/default -o yaml -n openshift-cluster-node-tuning-operator

2. Modify the YAML file with the Kernel and disk settings recommendations in the profile data section. This guide implements the new CR from the default CR and only modifies the OpenShift worker nodes tuned daemons. Control plane node profiles use the default CR.

For information about the custom CR used in this solution, see Appendix B OpenShift Tuned settings.

3. Apply the newly modified CR with the following command and wait for OpenShift to rollout the changes on each worker node.

      $ oc apply -f custom-cr.yaml

4. OpenShift identifies the custom tuned with the kind object that is mentioned in the YAML file and implements the custom changes.
5. Run the following command to identify the tuned pod running on the node:

      $ oc get pod -n openshift-cluster-node-tuning-operator

6. Run the sysctl command against the pod to verify the parameter changes.

$ oc exec $tuned_pod -n openshift-cluster-node-tuning-   operator – sysctl vm.max_map_count

Note: The above recommendations need to be applied to the Red Hat Linux container image of the Microsoft SQL Server.

Microsoft recommends the following best practices to optimize the performance:

• Store the database into four or more datafiles and store it on the separate database volumes.
• Store the database transaction logs on different volumes.
• Move the tempdb files from its default location to its own PowerFlex volume to use the PowerFlex capacity and power.
• Default values of tempdb files are set to 8. Create additional tempdb files as per the number of cores assigned to the SQL pod.
• Set 85 percent of the SQL Server memory limit with mssql-conf tool.
• Ensure to set the resource limits in SQL server deployment YAML file to get the guaranteed QOS (Quality of Service) from the OpenShift cluster. For more information, see Configure Quality of Service for Pods | Kubernetes.

Apart from the above recommended practices, following best practices are recommended by HammerDB to run the OLTP benchmark.

• Configure max degree of parallelism to its default value. SQL uses all core efficiently.
• Configure max worker threads to 800.
• Keep the initial datafile size sufficiently large to ensure that the files are not continuously growing at the expense of performance.
• Auto growth settings should be configured correctly so that the file grows sufficiently without any performance impact.

For more information about the HammerDB best practices, see HammerDB documentation.

The solution in this document provides a reference architecture to deploy the Microsoft SQL Server in a containerized environment on PowerFlex in an HCI configuration with Red Hat OpenShift platform. This document also describes the best practices to configure a Microsoft SQL Server on a containerized environment to meet performance, resiliency, and scale.

This solution demonstrates how PowerFlex can be used to deliver high database performance and scale. The benchmark results in the Test Results section demonstrate the TPM performance capability of Microsoft SQL Server 2019 on PowerFlex. The document also shows how the SQL Server pods can be scaled across the PowerFlex cluster effortlessly.

The following Dell Technologies documentation provides additional information. Access to these documents depends on your login credentials. If you do not have access to a document, contact your Dell Technologies representative. 

• PowerFlex family technical white papers
• Dell PowerFlex – Deploying Microsoft SQL Server 2019 on RHEL VM

The following Microsoft documentation provides additional information:

• Deploy and connect to SQL Server containers
• Configure and customize SQL Server Docker containers

The following HammerDB documentation provides additional information:

• HammerDB Best practices for SQL Server Performance and scalability

Hardware and software details

The following table summarizes the hardware and software resources that were used to carry out the performance test:

Table 7.      Hardware and software details

Following is a sample deployment YAML file:

apiVersion: apps/v1

kind: Deployment

metadata:

  name: sql3

spec:

  replicas: 1

  selector:

     matchLabels:

       app: mssql

  template:

    metadata:

      labels:

        app: mssql

    spec:

      terminationGracePeriodSeconds: 30

      hostname: sql3

      securityContext:

        fsGroup: 10001

      nodeSelector:

        host: node1

      containers:

      - name: sql3

        command:

            - /bin/bash

            - -c

            - cp /var/opt/config/mssql.conf /var/opt/mssql/mssql.conf && /opt/mssql/bin/sqlservr

        image:  mcr.microsoft.com/mssql/rhel/server:2019-latest

        resources:

         limits:

           memory: 32Gi

           cpu: 16000m

        ports:

        - containerPort: 1433

        env:

        - name: MSSQL_PID

          value: "Developer"

        - name: ACCEPT_EULA

          value: "Y"

        - name: SA_PASSWORD

          valueFrom:

            secretKeyRef:

              name: mssql

              key: SA_PASSWORD

        volumeMounts:

        - name: mssqldb

          mountPath: /var/opt/mssql/data

        - name: mssqllogs

          mountPath: /var/opt/mssqlogs/logs

        - name: mssql-config-volume

          mountPath: /var/opt/config

        - name: mssqltemp

          mountPath: /var/opt/mssqltemp/tempdb

      volumes:

      - name: mssqldb

        persistentVolumeClaim:

          claimName: data-sql3

      - name: mssqllogs

        persistentVolumeClaim:

          claimName: data-logs3

      - name: mssqltemp

        persistentVolumeClaim:

          claimName: data-temp3

      - name: mssql-config-volume

        configMap:

         name: mssql-config

---

kind: PersistentVolumeClaim

apiVersion: v1

metadata:

 name: data-sql3

 namespace: vxflexos

spec:

 accessModes:

 - ReadWriteOnce

 volumeMode: Filesystem

 resources:

   requests:

     storage: 300Gi

 storageClassName: vxflexos-xfs

---

kind: PersistentVolumeClaim

apiVersion: v1

metadata:

 name: data-logs3

 namespace: vxflexos

spec:

 accessModes:

 - ReadWriteOnce

 volumeMode: Filesystem

 resources:

   requests:

     storage: 200Gi

 storageClassName: vxflexos-xfs

---

kind: PersistentVolumeClaim

apiVersion: v1

metadata:

 name: data-temp3

 namespace: vxflexos

spec:

 accessModes:

 - ReadWriteOnce

 volumeMode: Filesystem

 resources:

   requests:

     storage: 300Gi

 storageClassName: vxflexos-xfs

---

apiVersion: v1

kind: Service

metadata:

  name: sql3

spec:

  selector:

    app: mssql

  ports:

    - protocol: TCP

      port: 1433

      targetPort: 1433

      nodePort: 30008

  type: NodePort

Following is a sample tuned profile:

Name:         custom-mssql

Namespace:    openshift-cluster-node-tuning-operator

Labels:       <none>

Annotations:  <none>

API Version:  tuned.openshift.io/v1

Kind:         Tuned

Metadata:

  Creation Timestamp:  2022-03-31T16:24:00Z

  Generation:          1

  Managed Fields:

    API Version:  tuned.openshift.io/v1

    Fields Type:  FieldsV1

    fieldsV1:

      f:metadata:

        f:annotations:

          .:

          f:kubectl.kubernetes.io/last-applied-configuration:

      f:spec:

        .:

        f:profile:

        f:recommend:

      f:status:

    Manager:         kubectl-client-side-apply

    Operation:       Update

    Time:            2022-03-31T16:24:00Z

  Resource Version:  15735959

  UID:               a75b34e1-2549-4d23-80e2-f9ff92d8c6d8

Spec:

  Profile:

    Data:  [main]

summary=Optimize systems to run SQL server on OpenShift nodes

include=openshift-node

[sysctl]

vm.swappiness=1

vm.dirty_background_ratio=3

vm.dirty_ratio=80

vm.dirty_expire_centisecs=500

vm.dirty_writeback_centisecs=100

vm.transparent_hugepages=always

vm.max_map_count=1600000

kernel.numa_balancing=0

kernel.sched_min_granularity_ns = 15000000

kernel.sched_wakeup_granularity_ns = 2000000

[disk]

readahead=4096

    Name:  custom-mssql

  Recommend:

    Match:

      Label:  node-role.kubernetes.io/worker

    Operand:

      Debug:   false

    Priority:  10

    Profile:   custom-mssql

Status:

Events:  <none>

Following is a sample MSSQL configuration file:

kind: ConfigMap

apiVersion: v1

metadata:

  name: mssql-config

data:

  mssql.conf:

[EULA]

accepteula = Y

accepteulaml = Y

[coredump]

captureminiandfull = true

coredumptype = full

[hadr]

hadrenabled = 1

[language]

lcid = 1033

[filelocation]

defaultdatadir = /var/opt/mssql/data

defaultlogdir = /var/opt/mssql/log

[control]

WriteThrough=1

AlternativeWriteThrough=1

[traceflag]

traceflag0=652

traceflag1=834

traceflag2=3468