Mixed Workloads for Microsoft SQL Server and VMware Horizon

Executive Summary

Business Case

Customers today wanting to deploy mixed application workloads require a cost-effective, highly scalable, and an easy-to-manage solution. Multiple applications need to be refreshed and published at will and should not involve multiple levels of IT administration. Most importantly, the infrastructure itself must be able to scale with the application consolidation yet still provide enterprise-class performance.

Hyperconverged infrastructure (HCI) makes it easier to plan for multiple enterprise workloads consolidation within several clusters. It helps combine hardware silos, reduce deployment complexity and management difficulty, and save capital and operational expenditures.

The Dell EMC vSAN Ready Nodes are pre-configured building blocks, jointly tested and certified hyperconverged solution with VMware. Based on trusted and proven PowerEdge Servers, Dell EMC vSAN Ready Nodes offer powerful processors, high core counts, maximum memory densities, lots of fast internal storage and innovative modular network interface card (NIC) technology. Powered by VMware vSAN™ and VMware vSphere_®, the Ready Nodes provide a fast, easy way to extend your VMware environment. To scale up, simply add flash devices to existing hosts for increased performance. Add hard drives or flash devices to increase capacity. To scale out, just add more hosts with hybrid or all_‑flash devices. Customers can select from a broad set of configuration options at a variety of price and scale points to achieve exceptional scalability of running enterprise mission-critical mixed workloads.

Figure 1 Enterprise Mixed Workloads Running on Dell EMC vSAN Ready Nodes

In this solution, we provide design guidance and best practices for enterprise infrastructure administrators and application owners to run mixed workloads on vSAN platform. We simulate a mixed-usage production environment including the following workloads to explore the performance capability, consistency, and resiliency.

• SQL Server TPC-E like OLTP workload

• SQL Server TPC-H like OLAP workload

• Virtual Desktop Infrastructure workload

• Database backup/restore jobs

• File service workload

Solution Overview

This reference architecture is a showcase of deploying and managing mixed workloads on Dell EMC PowerEdge R740xd for vSAN Ready Nodes within VMware vSphere environment:

• We measure the performance of running both Microsoft SQL Server and VMware Horizon concurrently in a 4-node vSAN cluster.

• We simulate a real-world production mixed workload environment for database workloads, VDI, backups and file services and demonstrate the performance consistency.

• We use Dell EMC Data Domain Virtual Edition to provide backup media and file share service.

Key Results

The key result of this reference architecture can be summarized as follows

• VMware vSAN delivers predictable and consistent performance for Microsoft SQL Server and VMware Horizon mixed workloads with minimal impact.

• A four-node vSAN cluster, powered by Dell EMC PowerEdge R740xd for vSAN Ready Nodes with four-diskgroup configuration, is capable of hosting two large SQL Server OLTP instances, two large SQL Server OLAP instances, and over 160 VDI desktops and infrastructure VMs, including sustaining backup and file service operations. At the same time, The ESXi peak CPU utilization is up to 71%, there are still resources for running more desktops or SQL Server instances as demanded.

• The vSAN storage policy based management provides management simplicity and application flexibility to run mixed workloads on vSAN.

• Dell EMC Data Domain Virtual Edition provide database backup agility and fast CIFS file service.

• The key performance results are summarized as following in Figure 2.

Figure 2 Key Performance Results

Audience

This solution is intended for IT administrators, SQL Server DBAs, VDI solution architects and storage experts who are involved in the early phases of planning, design, and deployment of virtualized enterprise mixed workloads on vSAN. It is assumed that the reader is familiar with the concepts and operations of Microsoft SQL Server, Dell EMC vSAN Ready Nodes, VMware vSphere products and Horizon technologies.

Technology Overview

This chapter introduces the technology components as listed below:

• VMware vSphere 6.7 U3

• VMware vSAN 6.7 U3

• Microsoft SQL Server 2017

• VMware Horizon 7

• Dell EMC vSAN Ready Nodes

• Dell EMC Data Domain Virtual Edition

VMware vSphere 6.7 U3

VMware vSphere 6.7 is the next-generation infrastructure for next-generation applications. It provides a powerful, flexible, and secure foundation for business agility that accelerates the digital transformation to cloud computing and promotes success in the digital economy. vSphere 6.7 supports both existing and next-generation applications through its:

• Simplified customer experience for automation and management at scale

• Comprehensive built-in security for protecting data, infrastructure, and access

• Universal application platform for running any application anywhere

With vSphere 6.7, customers can run, manage, connect, and secure their applications in a common operating environment, across clouds and devices.

VMware vSAN 6.7 U3

VMware vSAN is the industry-leading software powering VMware’s software defined storage and HCI solution. vSAN helps customers evolve their data center without risk, control IT costs and scale to tomorrow’s business needs. vSAN, native to the market-leading hypervisor, delivers flash-optimized, secure storage for all of your critical vSphere workloads. vSAN is built on industry-standard x86 servers and components that help lower TCO in comparison to traditional storage. It delivers the agility to easily scale IT and offers the industry’s first native HCI encryption.

In vSAN 6.7 U3 release, it provides performance improvements and availability SLAs on all-flash configurations with deduplication enabled. Latency sensitive applications have better performance in terms of predictable I/O latencies and increased sequential I/O throughput. Rebuild times on disk and node failures are shorter, which provides better availability SLAs.

The 6.7 U3 release also support cloud native storage that provides comprehensive data management for stateful applications. With Cloud Native Storage, vSphere persistent storage integrates with Kubernetes.

vSAN 6.7 U3 simplifies day-1 and day-2 operations, and customers can quickly deploy and extend cloud infrastructure and minimize maintenance disruptions. Stateful containers orchestrated by Kubernetes can leverage storage exposed by vSphere (vSAN, VMFS, NFS) while using standard Kubernetes volume, persistent volume, and dynamic provisioning primitives.

See VMware vSAN 6.7 Update 3 Release Notes for more information.

Microsoft SQL Server 2017

Microsoft SQL Server 2017 enable users to build modern applications using the language of your choice, on-premises and in the cloud, now on Windows, Linux and Docker containers. SQL Server 2017 focuses heavily on developer enablement with Microsoft support for SQL Server on Linux—it can be deployed in container platforms such as Docker, Open Shift, and Kubernetes. Microsoft has added adaptive query processing, which introduces new techniques for adapting SQL Server to specific application workload characteristics. Additionally, organizations gain flexibility when deploying the database, and supporting mixed workload environments.

VMware Horizon 7

Horizon 7 enables IT to centrally manage images to streamline management, reduce costs, and maintain compliance. With Horizon 7, virtualized or hosted desktops and applications can be delivered through a single platform to end users. These desktop and application services—including RDS hosted apps, packaged apps with ThinApp, SaaS apps, and even virtualized apps from Citrix—can all be accessed from one unified workspace to provide end users with all of the resources they want, at the speed they expect, with the e-fficiency business demands.

Drawing on the best of mobile and cloud, Horizon 7 radically transforms virtual desktop infrastructure (VDI), giving you unprecedented simplicity, security, speed, and scale—all at lower costs. Horizon 7 helps you get up and running up to 30x faster while cutting costs over traditional solutions by as much as 50%.

• Just-in-time desktops—leverage Instant Clone Technology coupled with App Volumes to dramatically accelerate the delivery of user-customized and fully personalized desktops. Dramatically reduce infrastructure requirements while enhancing security by delivering a brand-new personalized desktop and application services to end users every time they log in.

• VMware App Volumes—provides real-time application delivery and management.

• VMware User Environment Manager™—offers personalization and dynamic policy configuration across any virtual, physical, and cloud-based environment.

• Horizon Smart Policies—deliver a real-time, policy-based system that provides contextual, fine-grained control. IT can now intelligently enable or deactivate client features based on user device, location, and more.

• Blast Extreme—purpose-built and optimized for the mobile cloud, this new additional display technology is built on industry-standard H.264, delivering a high-performance graphics experience accessible on billions of devices including ultra-low-cost PCs.

Dell EMC vSAN Ready Nodes

Dell EMC vSAN Ready Nodes are built on Dell EMC PowerEdge servers that have been pre-configured, tested and certified to run VMware vSAN. Each Ready Node includes just the right amount of CPU, memory, network I/O controllers, HDDs and SSDs that are suited for VMware vSAN. Dell EMC offers unique vSAN chassis. Each model boasts an identity module that self-identifies the chassis as a vSAN Ready Node to streamline deployment and updates.

• Reduce deployment risks with certified configurations - Dell EMC engineering tests and validates vSAN Ready Nodes with VMware configurations for accelerating vSAN deployment. Dell EMC and VMware have collaborated on vSAN for many years, putting the technology through thousands of hours of testing.

• Improve storage efficiency - Dell EMC vSAN Ready Nodes improve storage efficiency while helping reduce capital expense (CAPEX) with server_‑side economics, affordable flash and grow_‑as_‑you_‑go scaling. Reducing the time and effort it takes to deploy and manage compute and storage infrastructure can also reduce operational expense (OPEX).

• Scale quickly and manage simply - Dell EMC vSAN Ready Nodes enable easy deployment with

factory_‑installed, pre_‑configured and pre_‑tested configurations which can add up to a solution that scales quickly to meet growing needs.

In this solution, we validated mixed workload on Dell EMC PowerEdge R740xd for vSAN Ready Nodes to deliver a perfect balance between storage scalability and performance. The 2U two-socket platform is ideal for software defined storage, databases workloads and virtual desktop infrastructure. See R740xd Spec Sheet for more details.

Dell EMC Data Domain Virtual Edition

Dell EMC Data Domain Virtual Edition (DD VE) is a software-only protection storage appliance: virtual deduplication appliance that provides data protection for entry, enterprise and service provider environments. Like any Data Domain system, DD VE is always paired with backup software.

DD VE runs the Data Domain Operating System (DD OS), and provides the DD OS command line interface (CLI) and the Data Domain System Manager graphical user interface (GUI) for performing all system operations.

Data Domain Virtual Edition maintains the core Data Domain features that differentiate it as the industry-leading protection storage. This includes high-speed, variable length deduplication for a 10 - 30x reduction in storage requirements, unparalleled data integrity to ensure reliable recovery, and seamless integration with leading backup and archiving applications.

DD VE also comes with DD Boost, which speeds backups by 50%, DD Encryption for enhanced security of data, and DD Replicator, which enables network efficient replication for faster time-to-DR readiness. For more information about the features and capabilities of Data Domain systems (both physical and virtual), see the EMC Data Domain Operating System Administration Guide .

Solution Configuration

This section introduces the resources and configurations:

• Architecture diagram

• Hardware resources

• Software resources

• Network configuration

• vSAN storage policy

• Mixed workload virtual machine configuration

• Mixed workload database workloads configuration

Architecture Diagram

In this solution, we created a 4-node vSAN cluster with PowerEdge R740xd servers for mixed workload validation. We deployed a vCenter Server appliance and vRealize Operations appliance as management and monitoring virtual machine. We also deployed Microsoft SQL Server 2017 and VMware Horizon 7 together in the same environment, and generated a mixed-workload environment including the following workloads on top of a vSAN cluster, as described in Figure 3.

• Two OLTP brokerage systems

• Two OLAP decision support systems

• A virtual desktop infrastructure

• Backup and restore service

• File service

The workload of the brokerage system is powered by TPC-E like benchmark, simulating a traditional OLTP database profile on vSAN. The workload of decision support system is powered by TPC-H like benchmark, simulating business intelligence database to provide the results for business analysis. For each workload type, we deployed two 600 GB databases with standalone SQL Server configuration.

The workload of the virtual desktop infrastructure is simulated by Login VSI performance testing tool . Login VSI generates many synthetic users to test, and to protect, the performance and scalability of new and existing VDI deployments. We provisioned 160 instant clone desktop VMs with Windows 10 operating system.

The backup and restore service is powered by Dell EMC Microsoft application agent plugin and Dell EMC DD VE with DD Boost. The file service is powered by a CIFS share provided by Dell EMC DD VE.

All the workloads described consist of a mixed-workload environment for multiple purpose, hybrid function and different block size running on the same vSAN cluster.

Figure 3 Mixed Workloads on Dell EMC vSAN Ready Nodes Architecture

Hardware Resources

In this solution, we used four Dell EMC PowerEdge R740xd servers each configured with four disk groups, and each disk group consists of one cache-tier write-intensive SAS SSD and four capacity-tier read-intensive SAS SSDs. We configured the pass-through mode for the capacity-tier storage controller, which is a preferred mode for vSAN with complete control of the local SSDs attached to the storage controller.

Each vSAN node in the cluster has the following configuration:

Table 1 Hardware Configuration for vSAN Cluster

PROPERTY	SPECIFICATION
Server model name	4 x Dell EMC PowerEdge R740xd
CPU	2 x Intel(R) Xeon(R) Platinum 8168 CPU @ 2.70GHz, 24 core each
RAM	768GB
Network adapter	4 x Intel(R) Ethernet Controller X710 for 10GbE SFP+
Storage adapter	1 x Dell HBA330 Adapter
Disks	Cache - 4 x 800GB Write Intensive SAS SSDs Capacity - 16 x 3.84TB Read Intensive SAS SSDs

Software Resources

Table 2 shows the software resources used in this solution.

Table 2 Software Resources

SOFTWARE	VERSION	PURPOSE
VMware vSphere and vSAN	6.7 U3 - 14320388	vSphere Cluster to host virtual machines and provide vSAN Cluster. vSAN is a software-defined storage solution for hyperconverged infrastructure.
VMware vCenter Server	6.7 U3 - 14367737	Centralized platform for managing VMware vSphere environments
Microsoft Windows Server	2016, x64, Standard Edition	Operating system for SQL Server
Microsoft SQL Server 2017	2017 Enterprise Edition, RTM-CU16	Database server platform
Benchmark Factory for Databases	8.1	SQL Server OLTP workload generate tool
VMware Horizon	7.8	Virtual desktop infrastructure software
Login VSI	4.1.32.1	Industry-standard software for VDI load testing
Data Domain Virtual Edition	6.2.0.10-615548	Software only data protection storage appliance
Microsoft application agent for Data Domain Boost with SQL Server	4.7	Database backup and restore plugin within SQL Server Management Studio

Network Configuration

As shown in Figure 4, we created a VMware vSphere Distributed Switch™ to act as a single virtual switch across all associated nodes for the vSAN cluster. Two of the 10 GbE NICs on each of the four nodes were configured for vSAN traffic. The NICs were trunked together as a Link Aggregation Group (LAG) for bandwidth and redundancy.

Another two 10GbE NICs were mapped to uplink2 and uplink3 on VDS for VM traffic and vMotion/Management traffic. For the VM traffic of both SQL Server and VDI workloads, uplink2 was configured as active and uplink3 was in standby. The vMotion/Management traffic use uplink3 as active and uplink2 was in standby. All the networks are configured on different VLAN IDs.

Figure 4. Virtual Distributed Switch Configuration for Mixed Workloads

vSAN Storage Policy Configuration

In this solution, we created a different storage policy for the mixed workloads. Table 3 shows the detailed configuration.

Table 3 vSAN Storage Policy Configuration for Mixed Workloads

Feature	Value	Description
Failure to Tolerate	1 failure – RAID-1 (Mirroring)	Defines the number of disks, host or fault domain failures a storage object can tolerate. It is adopted by all the workload VMs.
Number of disk stripes per object	4 is adopted for SQL Server OLAP data disk and DD VE data disk. The rest of VM is configured with 1.	The number of capacity disks across which each replica of a storage object is striped.
Checksum	Enabled	Checksum is calculated by default to prevent from Exchange data corruption.

Mixed-Workload Virtual Machine Configuration

Table 4 describes the mixed workloads virtual machine configuration in this solution. We created two monster VMs each for both SQL Server OLTP and OLAP workloads. For VDI workloads, we deployed the infrastructure VMs and 160 desktop VMs. We also deployed DD VE appliance with three 200GB virtual disks as storage pool for backup and file service.

Table 4 Virtual Machine Configuration

VM ROLE	VCPU	MEMO RY (GB)	VM COUNT	VIRTUAL DISKS	SCSI ID (CONTROLLE R, LUN)	SCSI TYPE	VSAN NODE
SQL Server OLTP VM	32	128	2	OS disk: 40 GB x 1	SCSI (0, 0)	LSI Logic	1 and 2
				SQL Server data disk 1 ~ 4: 250 GB x 4	SCSI (1, 0) ~ SCSI (1, 3)	VMware Paravirtual
				SQL Server tempdb and log disk: 250 GB x 1	SCSI (2, 0)	VMware Paravirtual
SQL Server OLAP VM	48	192	2	OS disk: 40 GB x 1	SCSI (0, 0)	LSI Logic	3 and 4
				SQL Server data disk 1 ~ 4: 120 GB x 8	SCSI (1, 0) ~ SCSI (1, 7)	VMware Paravirtual
				SQL Server tempdb and log disk: 300 GB x 1	SCSI (2, 0)	VMware Paravirtual
VDI Active Directory VM	2	8	1	OS disk: 40 GB x 1	SCSI (0, 0)	LSI Logic	1
VDI Composer database VM	4	16	1	OS disk: 100 GB x 1	SCSI (0, 0)	LSI Logic	2
VDI Composer server VM	2	10	1	OS disk: 100 GB x 1	SCSI (0, 0)	LSI Logic	3
VDI Connection server VM	2	10	1	OS disk: 100 GB x 1	SCSI (0, 0)	LSI Logic	4
VDI Windows template VM	2	4	160	OS disk: 30 GB x 1	SCSI (0, 0)	LSI Logic	DRS enabled
Login VSI Management Console VM	4	8	1	OS disk: 220 GB x 1	SCSI (0, 0)	LSI Logic	1
Login VSI launcher VM	4	8	8	OS disk: 100 GB x 1	SCSI (0, 0)	LSI Logic	1,2,3,4
BMF Client VM	2	8	4	OS disk: 100 GB x 1	SCSI (0, 0)	LSI Logic	1,2,3,4
DD VE appliance	2	8	1	OS disk1: 250 GB x 1	SCSI (0, 0)	VMware Paravirtual	3
				OS disk1: 10 GB x 1	SCSI (0, 1)	VMware Paravirtual
				Data disk 1 ~ 3: 200GB x 3	SCSI (1, 0) ~ SCSI (1, 2)	VMware Paravirtual

Table 5 lists vSphere Cluster configuration. We enabled VMware vSphere High Availability (vSphere HA) and Distributed Resource Scheduler (vSphere DRS) features in VMware Cluster.

Note that the DRS only applies to VDI desktop VMs. For the rest of the VMs, such as SQL Server OLTP and OLAP VMs, we do not want those VM to be migrated so frequently in a high workload or resource contention situation.

Therefore, the VM overrides configuration is adopted to deactivate DRS on those virtual machines, while the vSphere HA functions in case failure happens.

Table 5. vSphere Cluster Configuration

CLUSTER FEATURE

SETTINGS

DESCRIPTION

vSphere HA

Enabled

vSphere HA provides uniform, cost-effective failover protection against hardware and operating system outages within the mixed workload environment.

vSphere DRS

Fully Automated (for VDI Desktop VMs Only)

DRS automatically places VDI Desktop virtual machines onto hosts at VM power-on, and those virtual machines are automatically migrated from one host to another to optimize resource utilization.

For the rest of the VMs, we created VM overrides to deactivate vSphere DRS but allow vSphere HA to function properly.

We used a virtual machine image to provision desktop sessions in the Horizon View environment for an instant clone with Login VSI. We used optimization tools according to VMware OS Optimization Tool . See Table 6 for details. Table 6. Horizon Virtual Machine Template Configuration

ATTRIBUTE	LOGIN VSI IMAGE
Desktop Operating System	Windows 10 Enterprise 2016 LTSB 64-bit
Virtual machine hardware version	15
vCPU	2
Memory	4GB
Video RAM	4MB
3D graphics	Disabled
Virtual network adapter	VMXNET3
Virtual disk size	30GB
SCSI controller	VMware Paravirtual
Applications	Microsoft Office 2013 Internet Explorer 11 Adobe Reader 11 Adobe Flash Player 11 Doro PDF 1.82
VMware Tools version	10346
VMware View Agent	7.8.0 - 12599301
Number deployed	160

Mixed Workload Database Configuration

We provisioned two 600 GB OLTP databases and another two 600 GB OLAP databases with the Benchmark Factory tool. Table 7 shows the SQL Server database configuration.

Table 7. SQL Server Database Configuration

SQL SERVER WORKLOAD	ITEM	TEST CONFIGURATION
OLTP (TPC-E like)	SQL Server memory allocation	120 GB
	Database size	2 x 600 GB per instance
	Workload profile	Industry brokerage system, TPC-E like OLTP workload, 90/10 read/write ratio, 8KB majority block size. Simulated 50 virtual users/connections.
OLAP (TPC-H like)	SQL Server memory allocation	160 GB
	Database size	2 x 600 GB per instance
	Workload profile	Decision support system, TPC-H like OLAP workload, sequential read intensive, block size is multiple of 8KB, can be up to 512KB. Simulated 22 T-SQL analysis queries

Solution Validation

Test Overview

This solution simulated a real-world like mixed workloads for Microsoft SQL Server 2017 and VMware Horizon View 7 running on a 4-node vSAN cluster. It demonstrated the capability of running multiple industry common workloads together with management simplicity and performance consistency.

Testing Tools

We used the following monitoring tools and benchmark tools in the solution testing:

• Monitoring tools vSAN Performance Service vSAN Performance Service is used to monitor the performance of the vSAN environment, using the web client.

The performance service collects and analyzes performance statistics and displays the data in a graphical format. You can use the performance charts to manage your workload and determine the root cause of problems.

vSAN Health Check vSAN Health Check delivers a simplified troubleshooting and monitoring experience of all things related to vSAN. Through the web client, it offers multiple health checks specifically for vSAN including cluster, hardware compatibility, data, limits, physical disks. It is used to check the vSAN health before the mixed-workload environment deployment. VMware vRealize_® Operations™ vSphere and vSAN 6.7 and later releases include vRealize Operations within vCenter . This new feature allows vSphere customers to see a subset of intelligence offered up by vRealize Operations through a single vCenter user interface. Light-weight purpose-built dashboards are included for both vSphere and vSAN. It is easy to deploy, provides multi-cluster visibility, and does not require any additional licensing. Figure 4 shows the vRealize Operations Manager console.

Figure 5. vRealize Operations Manager Console

ESXTOP

ESXTOP is a command-line tool that can be used to collect data and provide real-time information about the resource usage of a vSphere environment such as CPU, disk, memory, and network usage. We measure the ESXi Server performance by this tool.

vSANTOP

vSANTOP provides real-time performance data for vSAN since 6.7 U3 release. vSANTOP is built with an awareness of vSAN architecture to retrieve focused metrics at a granular interval. This utility is focused on monitoring vSAN performance metrics at an individual host level.

Windows Performance Monitor

Windows Performance Monitor is a Windows tool that enables users to capture statistics about CPU, memory, and disk utilization from operating system levels. It also provides counters for monitoring SQL Server and Exchange performance and status.

• Mixed-workload generation tool Benchmark Factory for Databases

Benchmark Factory for Databases is a database workload generation tool that can conduct industry-standard benchmark testing and scalability testing. With Benchmark Factory for databases, you can make changes to your database environment, and mitigate the risks of planned database changes. We used this tool to generate SQL Server database TPC-E like OLTP and TPC-H like OLAP workload.

Login VSI 4.1.32.1

Login Virtual Session Indexer (Login VSI) is the industry standard load-simulation testing tool for measuring the performance and scalability of centralized Windows desktop environments, such as VDI and hosted applications. Login VSI is used for simulating a typical user activity using the virtual desktop. Login VSI is 100% vendor independent and works with standardized user workloads. This standardization makes all conclusions that are based on Login VSI test data objective, verifiable, and repeatable.

In this solution, we used the Login VSI in Benchmark mode with 8 launchers (20 sessions per launcher) to measure VDI performance in terms of Login VSI baseline performance score. The baseline is the measurement of the response time for specific operations performed in the desktop workload, measured in milliseconds (ms). A lower Login VSI baseline score is better because it reflects that the desktops can respond with less time. There are two values in particular that are important to note:

o VSIbase: A score reflecting the response time of specific operations performed in the desktop workload when there is little or no stress on the system. A low baseline indicates a better user experience, resulting in applications responding faster in the environment.

o VSImax: The maximum number of desktop sessions attainable on the host before experiencing degradation in host and desktop performance.

Login VSI has several different workload templates depending on the type of user to be simulated. Each workload differs in application operations and in the number of operations executed simultaneously. In the tests the workload type is ‘Knowledge Worker * 2vCPU’. The medium-level Knowledge Worker workload was selected for because it is the closest analog to the average desktop user in our customer deployments. Table 8 shows parameter of Knowledge worker workload setting include CPU usage, disk reads, disk writes and applications.

Table 8. Knowledge Worker Parameter

PARAMETER	KNOWLEDGE WORKER SETTING
Apps open	5-9
CPU usage	100%
Disk reads	100%
Disk writes	100%
IOPS	8.5
Memory	1.5 GB
CPU	2 vCPU

Microsoft application agent for Data Domain Boost with SQL Server

The Microsoft application agent for Data Domain Boost with SQL Server module, as shown in Figure 5, integrates with the SQL Virtual Device Interface. The interface enables the user to configure the module by using a plug-in in SSMS. The plug-in is similar to the SQL native backup and restore Graphical User Interface (GUI). Therefore, the database administrators (DBAs) can use the Microsoft native tools to backup and restore the SQL Server databases.

Figure 6. Microsoft application agent plugin for Data Domain Boost with SQL Server

Mixed Workload Performance Test

Test Objective

This test was designed to demonstrate the minimal impact and consistent performance of running SQL Server and VDI mixed workloads on vSAN.

Test Scenario

We designed four test scenarios as follows:

• Baseline test (scenario 1): Performance test of SQL Server OLTP workload

• Database workloads test (scenario 2): Performance test of SQL Server OLTP and OLAP workload

• Database and VDI workload test (scenario 3): Add VDI workload based on database workloads test

• Database and VDI workload test advanced (scenario 4): Add additional backup and file service job

Test Procedures

The testing procedures were described as follows:

1. Perform SQL Server TPC-E like test with two virtual machines, and get OLTP baseline test result. The user workload set for each SQL Server OLTP instance is 40. Test duration is set for 1 hour, whereas the first 15 minutes known as pre-sampling stage and the last 45 minutes known as the sampling stage.

2. Perform both SQL Server TPC-E like and TPC-H like test together, each with two virtual machines, and get database workload test result. User workload set for each SQL Server OLTP instance is 40 and 22 TPC-H like queries in total is run by each SQL Server OLAP instance. The test duration for OLTP is the same as defined in step 1, and the test duration for OLAP is the completion time of 22 queries.

3. Based on step 2, start Login VSI knowledge worker workload on the desktop VMs one by one. Measure both database workload performance results and VSI scores in the Login VSI test report. The test duration for OLTP and OLAP is the same as defined in step 2, and the test duration for VDI is set to 1 hour, whereas the first 48 minutes known as the logon time for all the desktop, and the last 12 minutes known as stable time (all the desktop will be online and perform user operation during this period).

4. Real-world like mixed-workload test: Based on step 3, add backup job and file copy job in the performance test. Measure the performance result like TPS, IOPS/bandwidth, response time, and ESXi processor utilization.

Test Results

Figure 6 shows the OLTP performance result defined in all four scenarios. The first column was the OLTP baseline test for two SQL Server instances, the aggregated TPS result was up to 4,235 while the transaction response time was 18.5 ms.

After we added the OLAP workload in scenario 2 (OLTP + OLAP test), it generated over 2.6 GB/s bandwidth, together with 3,927 TPS. This introduced 7.3% performance impact against the OLTP workload, and the response time was 20 ms.

Figure 7. SQL Server OLTP Performance

Figure 8. SQL Server OLAP Performance

In scenario 3 (OLTP + OLAP + VDI), we further added the VDI workload into the test. The VDI test logged in 160 desktop VMs one by one in every 18 seconds, and simulated the end-user behavior like document operation, browser surfing, photo viewer, and others. The Login VSI knowledge worker workload stressed the system smoothly, which resulted in higher compute and storage usage. The OLTP performance result was 3,883 TPS with 20.5 ms transaction response time, and the OLAP performance was 2.60 GB/s with an average query completion time of 238.72 seconds as shown in Figure 7.

Figure 8 showed the Login VSI test result for scenario 3. Firstly, there was no single stuck session during the test, which means all the 160 desktop VMs were running smoothly with SQL Server OLTP and OLAP workloads. Secondly, the VSI score could be interpreted as follows:

• VSIbase: scores 625 ms, which gives the best response time during the test.

• VSImax v4.1 average: scores 747 ms, which is the average desktop response time during the test.

• VSImax v4.1 threshold: scores 1626 ms, which determines the threshold that the environment is saturated.

It was obvious that the VSImax average was well below the threshold, which means the system is still capable of adding more workload.

Figure 9. Scenarios 3 Login VSI Test Result

In scenario 4 (OLTP + OLAP + VDI + backup + file service), we added the backup job for each SQL Server OLTP instance, and simulated file download and upload operation from the CIFS share folder created by DDVE on vSAN datastore.

On the SQL Server side, the aggregated OLTP TPS was 3,797 with transaction response time at 21 ms; the aggregated OLAP bandwidth was 2.49 GB/s with an average query completion time 249.95 seconds. The backup bandwidth was 691.22 MB/s in total and the average backup duration was 26 minutes and 58 seconds. The file download and upload bandwidth reached 268.53MB/s in total, as shown in table 9.

Table 9. Scenario 4 SQL Server Result

Operation	Test Item	Bandwidth (MB/s)	Duration
Backup	OLTP instance 1	352.25	27 minutes 22 seconds
Backup	OLTP instance 2	338.97	26 minutes 24 seconds
File Copy	File Download	129.24	60 minutes
File Copy	File Upload	139.29	60 minutes

On the VDI side, the VSIbase score increased slightly to 648 ms according to Figure 9, which indicated the best desktop VMs response time. VSImax average was 753 ms, and the threshold was 1,648ms. The overall Login VSI result was very good in the mixed workload test.

Figure 10. Scenario 4 Login VSI Test Result

The peak ESXi utilization reached 71.16% as shown in table 10, with up to 302K read IOPS and 23,917 write IOPS or 3.99GB/s read and 931.44MB/s equivalent mixed workload bandwidth with different kinds of block size. The vSAN backend read latency was 1.828 ms and the write latency was 1.12 ms.

Table 10. Mixed Workload Test Result

Metrics	Baseline Test	OLTP + OLAP	OLTP + OLAP + VDI	OLTP + OLAP + VDI + Backup + File Service
Peak ESXi CPU usage	38.04%	47.19%	69.28%	71.16%
Peak vSAN backend Read IOPS	31,355	330,007	285,526	302,339
Peak vSAN backend Read bandwidth	1,023.51 MB/s	3.96 GB/s	3.49 GB/s	3.99 GB/s
Peak vSAN backend Read latency	0.254	1.865	1.953	1.828
Peak vSAN backend Write IOPS	6,868	17,728	19,538	23,917
Peak vSAN backend Write bandwidth	102.09 MB/s	873.46 MB/s	821.72 MB/s	931.44 MB/s
Peak vSAN backend Write latency	0.642 ms	0.76 ms	0.838 ms	1.12 ms

Best Practices

The following best practices and guidance are recommended for running Microsoft SQL Server and VMware Horizon mixed workload on vSAN:

• Follow the generic best practices for SQL Server and Horizon on VMware vSphere:

▪ For SQL Server best practices, visit Architecting Microsoft SQL Server on VMware vSphere .

▪ For Horizon best practices, visit VMware Horizon 6 with View Performance and Best Practices .

• vSphere HA and DRS considerations:

▪ HA and DRS are recommended to enable for mixed workloads environment for failure protection and resource balance.

▪ Adopt VM Override rules to deactivate DRS operations to avoid frequent migration of virtual machines with large compute resource allocation during workloads contention.

• vSAN SPBM considerations:

▪ For each type of mixed workload, create a dedicated vSAN storage policy for management and isolation purposes.

▪ Use “IOPS limit for object” to limit certain mixed workload impacts against another.

▪ Consider different “Failures to tolerate” options with RAID 5 or RAID 6 to improve space efficiency and protection SLAs.

▪ Consider increasing “Number of disk stripes per object” for better object spread if it is a bandwidth-demanding workload, for example, OLAP workloads and backups.

See Troubleshooting vSAN Performance as additional resources for performance optimization on vSAN.

Conclusion

Summary

Dell EMC PowerEdge R740xd for vSAN Ready Nodes is ideal to service most user workloads. By running mixed workloads on top of vSAN, IT administrators can easily achieve better scalability for performance, reduced impact for workload consistency, improved resiliency for data protection, and optimized expenditure for total TCO.

In this solution, we validated mixed workloads running on vSAN with Microsoft SQL Server 2017 and VMware Horizon 7, which demonstrated the feasibility and flexibility of consolidating enterprise mission-critical database application and VDI infrastructure with minimized deployment and operational overhead.

We validated a mixture of SQL Server OLTP databases performance with TPC-E like workload, OLAP databases performance with TPC-H like workload, VMware Horizon VDI workload with Login VSI benchmark, and backup operations and file services powered by DDVE. The test result showed a predictable and consistent performance with minimized impact, with up to 330K vSAN IOPS or 3.99 GB/s peak vSAN bandwidth while keeping sub-millisecond write latency and less than 2-millisecond read latency.

Mixed Workload on VMware vSAN All-Flash

Reference

• StorageHub

• VMware vSAN