Microsoft SQL Server on VMware Cloud Foundation on Dell EMC VxRail

Executive Summary

Business Case

Customers wanting to deploy virtualized SQL Server infrastructure require a cost-effective, highly scalable, and an easy-to-manage solution. While the traditional ways to manage the infrastructure involves many physical server installations, storage manual configurations, and network management, VMware Cloud Foundation™ accelerates infrastructure provisioning with full stack consisting of compute, storage, networking, and management. Through automatic and reliable deployment of multiple workload domains, it increases admin productivity while reducing overall TCO to deliver a faster path to a hybrid cloud.

VMware Cloud Foundation is built on VMware’s leading hyperconverged architecture, VMware vSAN™, with all-flash performance and enterprise-class storage services including deduplication, compression and erasure coding. vSAN implements hyperconverged storage architecture, by delivering an elastic storage and simplifying the storage management. VMware Cloud foundation also delivers end-to-end security for all applications by implementing micro-segmentation, distributed firewalls, VPN (VMware NSX®); VM, hypervisor, VMware vSphere® vMotion® encryption, and AI-powered workload security and visibility (vSphere); and data-at-rest storage encryption (vSAN).

image

Figure 1. VMware Cloud Foundation on Dell EMC VxRail 

VMware Cloud Foundation on Dell EMC VxRail™ builds upon native VxRail and Cloud Foundation capabilities with unique integration features jointly engineered between Dell EMC and VMware that simplify, streamline, and automate the operations of your entire SDDC from Day 0 through Day 2 operations. The full stack integration with Cloud Foundation on VxRail allows both HCI infrastructure layer and VMware cloud software stack lifecycle to be managed as one, complete, automated, turnkey hybrid cloud experience greatly reducing risk and increasing IT operational efficiency. This solution also allows to perform cloud operations through a familiar set of tools, offering a consistent experience, with a single vendor support relationship and consistent SLAs across all your cloud workloads, public and private cloud, as well as edge deployments.

In this solution, we provide deployment procedures, design and sizing guidance, best practices for enterprise infrastructure administrators and application owners to run virtualized SQL Server workload on the Cloud Foundation platform.

Key Results

This reference architecture is a showcase of VMware Cloud Foundation on Dell EMC VxRail for operating and managing Microsoft SQL Server database workloads in a fully-integrated SDDC environment. Key results can be summarized as following:

  • VMware Cloud Foundation on Dell EMC VxRail simplifies and accelerates the necessary virtual infrastructure deployment desired for SQL Server workloads with single workflow containing all individual sub tasks.
  • The HCI platform, specifically VxRail in this solution, provides linear scalability and predictable performance capability for SQL Server TPC-E like workloads.
  • A SQL Server virtual infrastructure workload domain consisting of four VxRail P570F nodes is capable of servicing up to 10,567 TPC-E like transaction per second with 26 milliseconds (ms) transactional response time. The peak SQL Server VM IOPS was 9,438 and 5,408 with sub-millisecond response time. At the same time, the VxRail cluster peak CPU utilization is up to 70.26 percent, there is still room to service more SQL Server workload.

Note: The performance results in this solution are validated on the HCI platform of VMware Cloud Foundation on Dell EMC VxRail, which is also applied to general VMware vSAN with the similar configurations.

Audience

This solution is intended for IT administrators, SQL Server DBAs, storage experts who are involved in the early phases of planning, design, and deployment of virtualized SQL Server workloads on VMware Cloud Foundation and Dell EMC VxRail. It is assumed that the reader is familiar with the concepts and operations of Microsoft SQL Server, VMware Cloud Foundation related components and Dell EMC VxRail.

Technology Overview

• VMware Cloud Foundation - VMware vSphere - VMware vSAN - VMware NSX Data Center - VMware vRealize Suite

• Dell EMC VxRail

• Microsoft Windows Server

• Microsoft SQL Server

VMware Cloud Foundation

VxRail is the only jointly engineered HCI system with deep VMware Cloud Foundation integration. VMware Cloud Foundation is an integrated software stack that bundles compute virtualization (VMware vSphere), storage virtualization (VMware vSAN), network virtualization (VMware NSX), and cloud management and monitoring (VMware vRealize Suite) into a single platform that can be deployed on premises as a private cloud or run as a service within a public cloud. This documentation focuses on the private cloud use case. VMware Cloud Foundation helps to break down the traditional administrative silos in data centers, merging compute, storage, network provisioning, and cloud management to facilitate end-to-end support for application deployment.

VMware Cloud Foundation on Dell EMC VxRail is a solution offering that provides best-in-class serviceability and lifecycle management capabilities for customers looking to automate the deployment and management of the full VMware Software Defined Datacenter (SDDC) stack on Dell EMC VxRail:

  • VxRail integration with VCF delivers a simple and direct path to the hybrid cloud with one, complete, automated platform.
  • VCF on VxRail delivers unique integrations with Cloud Foundation to offer a seamless, automated upgrade experience.

VMware vSphere

VMware vSphere 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 VMware vSphere, customers can run, manage, connect, and secure their applications in a common operating environment, across clouds and devices.

VMware vSAN

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, and is built on industrystandard 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 the 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 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 details.

VMware NSX Data Center

VMware NSX Data Center is the network virtualization and security platform that enables the virtual cloud network, a software-defined approach to networking that extends across data centers, clouds, and application frameworks. With NSX Data Center, networking and security are brought closer to the application wherever it’s running, from virtual machines to containers to bare metal. Like the operational model of VMs, networks can be provisioned and managed independent of underlying hardware. NSX Data Center reproduces the entire network model in software, enabling any network topology—from simple to complex multitier networks—to be created and provisioned in seconds. Users can create multiple virtual networks with diverse requirements, leveraging a combination of the services offered via NSX or from a broad ecosystem of third-party integrations ranging from nextgeneration firewalls to performance management solutions to build inherently more agile and secure environments. These services can then be extended to a variety of endpoints within and across clouds.

In this solution, we use VMware NSX Data Center for vSphere to form the foundation of the software-defined data center (SDDC) and make operationalizing zero-trust security for SQL Server database applications attainable and efficient in both private and public cloud environments.

VMware vRealize Suite

vRealize Suite is a hybrid cloud management platform that helps IT enable developers to quickly build applications in any cloud with secure and consistent operations. It provides developer-friendly infrastructure (supporting VMs and containers) and a common approach to hybrid and multi-cloud, supporting major public clouds such as Amazon Web Services, Azure, and Google Cloud Platform.

VMware vRealize Suite delivers an enterprise-proven, hybrid cloud management platform (CMP) that includes the following products:

  • vRealize Automation—Self-service hybrid clouds, multi-cloud automation and governance, DevOps service delivery and Kubernetes cluster management.
  • vRealize Operations—Powered by artificial intelligence and machine learning (AI/ML), continuous performance optimization, proactive capacity and cost management, intelligent remediation and integrated compliance.
  • vRealize® Log Insight™—Real-time log management and log analysis.
  • vRealize Suite Lifecycle Manager—Automated installation, configuration, upgrade, patching, configuration management, drift remediation, health and content management for vRealize Suite.

In this solution, we deployed two vRealize Operations appliances to monitor the SQL Server virtual infrastructure workload domain and three vRealize Log Insight appliances to provide real-time log management and analysis.

Dell EMC VxRail Hyperconverged Platform

The only fully integrated, pre-configured, and pre-tested VMware hyperconverged system optimized for VMware vSAN and VMware Cloud Foundation, VxRail transforms HCI networking and simplifies VMware cloud adoption, while meeting any HCI use case - including support for many of the most demanding workloads and applications. Powered by Dell EMC PowerEdge server platforms and VxRail HCI System Software, VxRail features next-generation technology to future proof your infrastructure and enable deep integration across the VMware ecosystem. Advanced VMware hybrid cloud integration and automation simplifies deployment of a secure VxRail cloud infrastructure.

Dell EMC VxRail provides advanced automation, making it easier for you from day one forward, allowing you to further simplify IT infrastructure and operations. VxRail on next generation PowerEdge servers are refined, optimized, and robust platforms that help you simplify your entire IT lifecycle – from deployment, to management, to scaling, to maintaining.

VMware Cloud Foundation on VxRail delivers unique integrations with Cloud Foundation to offer a seamless, automated upgrade experience. All of this enables customers to experience both the HCI infrastructure layer and cloud software stack in one complete, automated lifecycle, and turnkey experience.

In this solution, we use SDDC Manager to orchestrate the deployment, configuration and lifecycle management of vCenter, NSX and vRealize Suite above the ESXi and vSAN layers of VxRail, which unifies multiple VxRail clusters as workload domains or as multi-cluster workload domains. We use VxRail Manager to deploy, configure and lifecycle manage ESXi, vSAN and hardware firmware with native process, which also monitors health of hardware components and provides remote service support.

Figure 2. VxRail Manager and SDDC Manager Integration

The only fully integrated, pre-configured, and pre-tested VMware hyperconverged system optimized for VMware vSAN and VMware Cloud Foundation, VxRail transforms HCI networking and simplifies VMware cloud adoption, while meeting any HCI use case - including support for many of the most demanding workloads and applications. Powered by Dell EMC PowerEdge server platforms and VxRail HCI System Software, VxRail features next-generation technology to future proof your infrastructure and enable deep integration across the VMware ecosystem. Advanced VMware hybrid cloud integration and automation simplifies deployment of a secure VxRail cloud infrastructure.

Dell EMC VxRail provides advanced automation, making it easier for you from day one forward, allowing you to further simplify IT infrastructure and operations. VxRail on next generation PowerEdge servers are refined, optimized, and robust platforms that help you simplify your entire IT lifecycle – from deployment, to management, to scaling, to maintaining.

VMware Cloud Foundation on VxRail delivers unique integrations with Cloud Foundation to offer a seamless, automated upgrade experience. All of this enables customers to experience both the HCI infrastructure layer and cloud software stack in one complete, automated lifecycle, and turnkey experience.

In this solution, we use SDDC Manager to orchestrate the deployment, configuration and lifecycle management of vCenter, NSX and vRealize Suite above the ESXi and vSAN layers of VxRail, which unifies multiple VxRail clusters as workload domains or as multi-cluster workload domains. We use VxRail Manager to deploy, configure and lifecycle manage ESXi, vSAN and hardware firmware with native process, which also monitors health of hardware components and provides remote service support.

Microsoft Windows Server

The latest Windows Server 2019 is the operating system that bridges onpremises environments with the cloud, adding additional layers of security while helping you modernize your applications and infrastructure. Windows Server 2019 evolve datacenter infrastructure, helping to achieve unique hybrid capabilities with identity platform built on SQL Server technologies, enhanced security to secure the operating system, and faster application innovation with container technologies. See Windows Server for more details.

Microsoft SQL Server

Microsoft SQL Server enables 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.

Note: This solution is validated based on SQL Server 2017, the result of which may also apply to the latest 2019 release.

Solution Configuration

• Architecture diagram

• Hardware resources

• Software resources

• Network configuration

• Virtual machine configuration

Architecture Diagram

In this solution, we deployed Microsoft SQL Server 2017 test environment using VMware Cloud Foundation for VxRail. We created a 4-node VxRail P570F cluster for VMware Cloud Foundation management cluster, running multiple virtual machines and appliances including:

  • A management vCenter Server appliance and two Platform Service Controllers
  • A management NSX Manager and three NSX controllers (NSX-V)
  • A VxRail Manager appliance
  • A vRealize Lifecycle Manager appliance
  • Two vRealize Operations appliances
  • Three vRealize Log Insight appliances
  • A Virtual Infrastructure (VI) workload NSX Manager
  • A VI workload vCenter Server appliance

For the SQL Server workload domain cluster, we created another 4-node VxRail P570F cluster and deployed four SQL Server virtual machines with different sizing scale described as follows:

  • Small-sized SQL Server virtual machine with 8 vCPU and 32GB memory for 100GB databases
  • Medium-sized SQL Server virtual machine with 16 vCPU and 64GB memory for 300GB databases
  • Large-sized SQL Server virtual machine with 32 vCPU and 128GB memory for 600GB databases

The SQL Server workload, powered by a TPC-E like benchmark, simulated a brokerage system of traditional OLTP workload profile. A VxRail Manager and three workload NSX-V controllers were also deployed in the workload domain cluster, as shown in Figure 3.

Figure 3. SQL Server on VMware Cloud Foundation Solution Architecture

Hardware Resources

In this solution, we used a total of eight VxRail R570F platforms each configured with two disk groups, and each disk group consists of one cachetier write-intensive SAS SSD and four capacity-tier read-intensive SAS SSDs.

Each VxRail node in the cluster had the following configuration:

Table 1. Hardware Configuration for VxRail

PROPERTY

SPECIFICATION

Server model name

8 x VxRail P570F

CPU

2 x Intel(R) Xeon(R) Platinum 8180M CPU @ 2.50GHz, 28 core each

RAM

512GB

Network adapter

2 x Broadcom BCM57414

NetXtreme-E

10Gb/25Gb RDMA

Ethernet Controller

Storage adapter

1 x Dell HBA330

Adapter

Disks

Cache - 2 x 800GB

Write Intensive SAS

SSDs

Capacity - 8 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 Cloud

Foundation on

Dell EMC

VxRail

3.8.1

A unified SDDC platform on Dell EMC VxRail that brings together VMware ESXi, vSAN, NSX and optionally, vRealize Suite components, into a natively integrated stack to deliver enterpriseready cloud infrastructure for the private and public cloud.

See BOM of VMware Cloud Foundation on

VxRail for details.

Windows

Server 2019

Standard

Operating system

Microsoft SQL

Server 2017

2017

Enterprise

Edition,

RTM-CU17

Database server platform

Benchmark

Factory for

Databases

8.1

SQL Server OLTP workload generate tool

Network Configuration

Figure 4 shows the VMware vSphere Distributed Switch configuration for the SQL Server VI workload domain of the VMware Cloud Foundation on VxRail. Two 25 GbE vmnics were used and configured with different teaming policies.

The NSX-V controllers resided on the management port group of the vDS, and the VxRail Manager was on another VxRail management port group. The SQL Server virtual machines were configured with a VM network on the vDS. vSphere vMotion, vSAN, and VXLAN VTEP for NSX-V had a dedicated port group created as shown in table 3.

Table 3. Virtual Distributed Switch Teaming Policy for 2x25 GbE Profile

Port Group

Teaming Policy

VMNIC0

VMNIC1

Management network

Route based on Physical NIC load

Active

Active[1]

VxRail

Management

Route based on the originating virtual port

Active

Standby

VM network

Route based on Physical NIC load

Active

Active

vSphere vMotion

Route based on Physical NIC load

Active

Active

vSAN

Route based on Physical NIC load

Active

Active

VXLAN VTEP

Route based on the originating virtual port

Active

Active

[1] For more details, refer to VMware Cloud Foundation on VxRail Architecture Guide.

Figure 4. Virtual Distributed Switch Configuration

Virtual Machine Configuration

The VMware Cloud Foundation management domain virtual machine configuration in this solution is described in table 4.

Table 4. Management Domain Virtual Machine Configuration

VM Role

vCPU

Memo

ry (GB)

VM

Count

Management Domain VxRail

Manager

2

8

1

Management Domain vCenter Server

16

24

1

Platform

Server

Controller

2

4

2

SDDC

Manager

4

16

1

Management Domain

NSX

Manager

4

16

1

Management Domain

NSX

Controller

4

4

3

VI

Workload Domain vCenter Server

16

24

1

VI

Workload

Domain

NSX

Manager

4

16

1

vRealize Suite

Lifecycle

Manager

4

16

1

vRealize

Operations

8

32

2

vRealize

Log Insight

8

16

3

Cloud

Builder

4

8

1

The SQL Server VI workload virtual machine configuration in this solution is described in table 5.

Table 5. SQL Server VI Workload Virtual Machine Configuration

VM

Role

v

C P

U

Me mor y

(GB

)

V

M

Co un

t

Virtual Disks

SCSI ID

(Controll er, LUN)

SCSI

Type

Smallsized

SQL

Server

VM

8

32

4

OS disk: 40

GB x 1

SCSI (0,

0)

LSI

Logic

SQL Server data disk

1 ~ 4: 40 GB x

4

SCSI (1,

0) – SCSI

(1, 3)

VMwar e

Paravirt ual

SQL Server tempdb and

log disk:

40 GB x 1

SCSI (2,

0)

VMwar e

Paravirt ual
       

OS disk: 40

GB x 1

SCSI (0,

0)

LSI

Logic
 

Medium-sized

SQL

Server

VM

1

6

64

4

SQL Server data disk

1 ~ 4: 120 GB x 4

SCSI (1,

0) – SCSI

(1, 3)

VMwar e

Paravirt ual

SQL Server tempdb and

log disk:

120 GB x 1

SCSI (2,

0)

VMwar e

Paravirt ual

Largesized

SQL

Server

VM

3

2

128

4

OS disk: 40

GB x 1

SCSI (0,

0)

LSI

Logic

SQL Server data disk

1 ~ 4: 250 GB x 4

SCSI (1,

0) – SCSI

(1, 3)

VMwar e

Paravirt ual

SQL Server tempdb and

log disk:

250 GB x 1

SCSI (2,

0)

VMwar e

Paravirt ual
 

SQL

Server

VI Workload

VxRail Manager

2

8

1

OS disk 1: 16

GB x 1

SCSI (0,

0)

LSI

Logic

OS disk 2: 16

GB x 1

SCSI (0,

1)

LSI

Logic

SQL

Server

VI

Workload

NSX

Controller

4

4

3

OS disk: 28

GB x 1

SCSI (0,

0)

LSI

Logic

 

Solution Validation

Test Overview

This solution simulated a TPC-E like database workload for Microsoft SQL Server 2017 running on VMware Cloud Foundation powered by Dell EMC VxRail. It demonstrated the deployment agility, performance scalability, and management simplicity for SQL Server database workloads.

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 vSphere 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 vSphere 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 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. Lightweight 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.

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.

  • Database generation tool

Benchmark Factory for Databases

Benchmark Factory for Databases (BMF) 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 workload.

Deploy VMware Cloud Foundation on VxRail

Deployment Overview

We deployed four VxRail for the VMware Cloud Foundation management cluster and another four VxRail for the VMware Cloud Foundation virtual infrastructure workload domain dedicated for SQL Server workloads. It is highly recommended to follow the steps described in VMware Cloud Foundation on Dell EMC VxRail documentation. For more details and instructions, contact VMware and Dell EMC Customer Services or your sales representative.

Figure 5 shows the management domain and VI workload domain cluster created for SQL Server in this solution:

Figure 5. Virtual Infrastructure Workload Domain Created in the SDDC Manager

SQL Server Database Sizing Test

Test Objective

This test was designed to demonstrate the performance scalability of different SQL Server instance configuration and database size running on VMware Cloud Foundation powered by VxRail.

Test Scenario

Table 6 describes three test scenarios defined in this solution. We allocated 2GB memory for the operating system and set the SQL Server instance maximum and minimum memory with the rest memory.

Table 6. Database Sizing Test Scenarios

Test Round

Test Scenario

Database size

# of VMs

VM vCPU

VM Memory

1

Small size database sizing test

100GB

4

8

32GB

(30GB allocated to SQL Server)

2

Medium size database sizing test

300GB

4

16

64GB

(62GB allocated to SQL Server)

3

Large size database sizing test

600GB

4

32

128GB

(126GB allocated to SQL Server)

Test Procedures

As we added more Benchmark factory virtual users to generate TPC-E like OLTP workload against the SQL Server instance in the sizing test, we defined that test stop criteria was the database transactional response time reached 30 milliseconds. The test procedures were described as follows:

  1. For small size database, start the TPC-E OLTP like workload with 10 virtual users against all four virtual machines. Test duration is set for 1 hour, whereas the first 15 minutes known as pre-sampling stage and the second 45 minutes known as the sampling stage. Measure the relevant performance result such as TPS (Transaction Per Second) and transactional response time.
  2. Add 10 more virtual users and perform the test described in step 1 until the transaction response time reaches above 30 milliseconds. Measure the relevant performance result and draw a sizing curve of TPS and transactional response time for SQL Server small size database workload.
  3. Perform the same test described in step 1 and step 2 for medium size database and large size database respectively.

Test Results

Figure 6 shows the TPC-E like OLTP performance result in the sizing test for small size database (100GB). The TPS result was bounded due to virtual machine CPU utilization limitation (up to 96.41 percent usage), with the best TPS reached 5,669.19 for 20 virtual users load and 14 ms transactional response time. At the same time, the response time scaled linearly as the user count grew. The maximum BMF user count to drive 30 ms transactional response time was between 40 and 50 virtual users.

User

10

20

30

40

50

TPS

5493.985

5669.187

5664.071

5653.709

5636.975

RT

7

14

21

28

34.5

VM CPU %

88.609

96.406

96.276

96.308

95.491

Figure 6. Small Size Database Sizing Test Results

Figure 7 shows the TPC-E like OLTP performance result in the sizing test for medium size database (300GB). Again, the TPS result was bounded due to virtual machine CPU utilization limitation (up to 95.96% usage). The TPS result was near-linear scaled before 50 virtual users, and the transactional response time scaled linearly as we tested up to 60 virtual users.

The best TPS result was 50 virtual users with 7,889.67 TPS and 25 milliseconds transactional response time, and the maximum BMF user count to drive 30 milliseconds transactional response time was between 50 and 60 virtual users.

User

10

20

30

40

50

60

TPS

6027.522

6785.638

7302.166

7666.771

7889.662

7555.518

RT

7

12

16

20.75

25

31.25

VM CPU %

85.652

91.511

95.083

95.961

95.765

94.729

Figure 7. Medium Size Database Sizing Test Results

Figure 8 shows the TPC-E like OLTP performance result in the sizing test for large size database (600GB). The TPS and transactional response time scaled linearly as we increased the virtual user count. The best TPS achieved in this test round was 10,567.21 with 26 ms transactional response time, and the maximum BMF user count to drive 30 milliseconds transactional response time was 80 virtual users.

User

10

20

30

40

50

60

70

80

TPS

6009

7075.417

8484.602

9232.854

9877.163

10352.904

10567.215

10510.061

RT

7

11.5

14.25

17.25

20.75

23

26

30

VM CPU %

27.548

55.345

67.095

78.817

84.26

87.518

90.904

90.649

Figure 8. Large Size Database Sizing Test Results

Table 7 shows the peak vSAN VM and backend performance in the sizing test.

Metrics

Value

Peak ESXi CPU usage

70.26%

Peak VM read IOPS

9,438

Peak VM read latency

1.091 ms

Peak VM write IOPS

5,408

Peak VM write latency

1.172 ms

Best Practices

In this solution, we validated the SQL Server TPC-E like OLTP workload and measured the performance in TPS and transactional response time metrics.

The following recommendations provide the best practices and sizing guidance to run SQL Server on VMware Cloud Foundation on VxRail.

  • Cloud management consideration:
  • Compute consideration:
    • A minimum compute resource Stock Keeping Unit (SKU) for SQL Server virtual machine can be defined as 1vCPU and 4GB memory. Take small-size database for example, we allocated 8 compute SKU for the virtual machine, and achieved the best 5,669 TPS with 4 virtual machines in total. However, the performance is bounded with the virtual machine CPU utilization. If the small-size database is desired for more TPS, simply add more compute SKU to the virtual machine. The compute resource allocation of SQL Server virtual machine can be affected by the database size, transaction load and the number of connection users.
    • Leave at least 2GB memory for operating system. It is recommended to set the identical value for SQL Server instance minimum and maximum memory to allow a fixed buffer pool size.
  • Network consideration:
  • Storage consideration:
    • Follow the generic best practices for Architecting Microsoft SQL Server on VMware vSphere.
    • For different type of database workload, create a dedicated vSAN storage policy for management and isolation purpose.
    • Consider using different “Failures to tolerate” options with RAID 5 or RAID 6 to improve space efficiency and protection SLAs for SQL Server database data disks. Use RAID 1—Mirroring for log disks.
    • Consider increasing “Number of disk stripes per object” for better object spreading if it is a bandwidth demanding database workload, for example, OLAP workloads and backups.

Conclusion

VMware Cloud Foundation on Dell EMC VxRail is the hybrid cloud platform for running enterprise SQL Server database workloads. Powered by VMware vSphere, vSAN, NSX Data Center, vRealize Suite, and Dell EMC VxRail, This platform allows IT administrators to enable fast cloud deployment, achieve better scalability for performance, ensure infrastructure and application security, monitor data center operational health, and lower TCO expenditure.

In this solution, we validated Microsoft SQL Server running on VMware Cloud Foundation powered by Dell EMC VxRail. We deployed the entire test environment with agility by using Dell EMC VxRail and VMware SDDC Manager, and validated SQL Server scalable performance with different VM configuration and database size. The test result showcased a near-linear scalability for SQL Server TPS against the virtual user count increasement under a TPC-E like benchmark, and we evaluated the maximum TPS achieved with 30 millisecond transactional response time.

Reference

About the Author

Mark Xu, Solutions Architect in the Solutions Architecture team of the Hyperconverged Infrastructure (HCI) Business Unit, wrote the original version of this paper. 

The following reviewers also contribute to the paper contents: 

  • Victor Dery, Senior Principal Engineer of VxRail Technical Marketing in Dell EMC 
  • William Leslie, Senior Manager of VxRail Product Management in Dell EMC
  • Tony Wu, Senior Solutions Architect in the Solutions Architecture team of the HCI Business Unit in VMware 
  • Chen Wei, Staff Solutions Architect in the Solutions Architecture team of the HCI Business Unit in VMware 
  • Dinara Alieva, Senior Product Marketing Manager of the HCI Business Unit in VMware 

Associated Content

home-carousel-icon From the action bar MORE button.

Filter Tags

Cloud Foundation Reference Architecture