Build Next Gen Apps VMware Cloud on AWS

Microsoft SQL Server Scalability with VMware Cloud on AWS

Introduction
Microsoft SQL Server is commonly run on virtualized infrastructure using VMware vSphere. SQL Server is typically highly scalable on the vSphere platform. The goal of this proof of concept is to validate scalability for SQL server and consider the at impact of vSphere features like vMotion on transactional performance when it is running on the VMware Cloud on AWS.

Proof of Concept for Microsoft SQL Server with VMware Cloud on AWS:
Tests were run on the VMware Cloud on AWS, to show that MS SQL Server is functional and runs well in that public cloud environment. The VMware Cloud on AWS is powered by VMware Cloud Foundation which is the unified SDDC platform and it is the integrated cloud infrastructure platform for a hybrid cloud. With the new public cloud infrastructure, available with VMware Cloud on AWS, we wanted to see how SQL Server scales for OLTP like workloads. This proof of concept also was used to look at the behavior of vMotion on SQL Server transactional performance.

HammerDB for OLTP:
HammerDB for SQL Server is a commonly used tool for testing OLTP database performance and was used to gauge transactional performance for SQL server running in virtual machines of two different sizes running in a VMware Cloud on AWS cluster. The two virtual machines used for SQL server were sized from a compute standpoint as follows:

  • Medium Sized SQL server virtual machine that fits within a NUMA node of the physical ESX host in the cluster with 16 vCPU and 240 GB RAM.
  • Large Sized SQL server virtual machine that extends across NUMA nodes of the physical ESX host in the cluster with twice the capacity of the medium host with 32 vCPU and 480 GB RAM.

    • HammerDB Database Build:
    The size of the HammerDB OLTP database is dependent on the number of warehouses. An appropriate number of warehouses was used as shown below to create a 250 GB database. This database will be common for the two different sizes of the SQL servers.

    HammerDB Testing: (Following VMware Best Practices for SQL Server running on vSphere)

  • The SAP HANA virtual machine was initially sized with 16 vCPUs and 240 GB of RAM. This size fits within a NUMA boundary of the physical host.
  • The hard disks for the SQL server are sized per best practices with dedicated disks for data, logs and backups.
  • Multiple Para-virtualized SCSI adapters are used to create separate high performance channels for the different disk types
  • The 250 GB database is generated using multiple users.
  • Multiple HammerDB clients are used to generate enough load to create an 80% CPU load on the SQL Server. The maximum transactions per minute generated is captured.
  • The SQL server virtual machine was shut down and resized to double the CPU and memory.
  • The SQL Server was restarted with the new size and the tests were repeated with the maximum TPM captured.
  • During the peak load of 80% CPU utilization a vMotion of the virtual machine was performed and its effect on transactional performance was captured.

    • Results:
    Medium Sized SQL Server:
    Multiple HammerDB clients were used to generate transactional load on the SQL server 1 Million transactions per minute was achieved with the SQL server at close to 80% CPU utilization.

    Large Sized SQL Server:
    Multiple HammerDB clients were used to generate transactional load on the SQL server 2 Million transactions per minute was achieved with the SQL server at close to 80% CPU utilization.

    Effect on TPM during vMotion:
    For the large SQL server at the culmination of testing when the SQL server was at 80% CPU utilization, a vMotion was initiated. There is an instantaneous drop in TPM as shown but the system recovers quickly and gets back to its prime performance in a few seconds as expected.

    Conclusion:
    This proof of concept with HammerDB and MS SQL Server running on VMware Cloud on AWS has validated the following:

  • VMware Cloud on AWS is a great platform for Microsoft SQL Server workloads
  • SQL Server workloads scale linearly for transactional performance across different sizes of SQL server
  • The Proof of concept achieved 2 Million Transactions per minute on HammerDB with no tuning.
  • Microsoft SQL Server on VMware Cloud on AWS performs consistently even during potentially impactful vMotion operations.
  • When utilizing VMware Best Practices for SQL Server the performance on the VMware Cloud on AWS compared favorably to other typical platforms and common functions behaved as expected.

    • To look at a video demo of the proof of concept, please visit https://youtu.be/P8uxAJYS0hg

    Related Articles