]

Category

  • Reference Architecture

Product

  • vSAN

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 allflash 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 disable 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 serverside economics, affordable flash and growasyougo 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

factoryinstalled, preconfigured and pretested 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 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 frequent in high workload or resource contention situation.

Therefore, the VM overrides configuration is adopted to disable 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 disable 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 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. 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 result 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 the 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 instances, 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

OLTP instance 2

338.97

26 minutes 24 seconds

File Copy

File Download

129.24

60 minutes

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 disable 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 purpose.

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

 

 

About the Author

Mark Xu, Solutions Architect in the Solutions Architecture team of the HCI Business Unit in VMware, wrote the original version of this paper.

 

Filter Tags

  • Reference Architecture
  • vSAN