Guest

Unified Computing

Microsoft SQL Server 2012 with AlwaysOn Using Cisco UCS C-Series Servers and Fusion ioMemory

  • Viewing Options

  • PDF (2.2 MB)
  • Feedback

 

July 2014

 

 

 

 

 

 

 

 

 

 


What You Will Learn................................................................................................................................................................... 3

Objectives.................................................................................................................................................................................... 3

Audience....................................................................................................................................................................................... 3

Purpose of This Document...................................................................................................................................................... 3

Solution Overview...................................................................................................................................................................... 4

Cisco Unified Computing System...................................................................................................................................... 4

Cisco UCS C240 M3 Rack Servers............................................................................................................................... 5

Why Cisco UCS C240 M3 Rack Servers?.................................................................................................................... 6

Cisco UCS VIC 1225............................................................................................................................................................. 6

Cisco Nexus 5548UP Switch............................................................................................................................................... 7

Fusion-io.................................................................................................................................................................................. 7

Hardware and Software Resources................................................................................................................................... 8

Hardware Requirements................................................................................................................................................. 8

Client Hardware Requirements..................................................................................................................................... 8

Software Requirements................................................................................................................................................... 8

Environmental Configuration and Settings................................................................................................................. 9

Cisco UCS C240 M3 Server BIOS Settings................................................................................................................. 9

Storage Layout......................................................................................................................................................................... 11

RAID 1.................................................................................................................................................................................... 11

RAID 10.................................................................................................................................................................................. 12

Network Design....................................................................................................................................................................... 12

Microsoft SQL Server AlwaysOn Availability Group Design........................................................................................ 13

Case Studies............................................................................................................................................................................. 15

Performance Results and Analysis.................................................................................................................................. 15

SQL Server 2012 AlwaysOn on Cisco UCS C240 M3: Fusion-io Drives on Both Primary and Secondary Servers  15

Throughput in IOPS (Transfers per Second)............................................................................................................. 17

Microsoft SQL Server CPU and Network Utilization................................................................................................ 17

Latency............................................................................................................................................................................. 18

Microsoft SQL Server 2012 AlwaysOn on Cisco UCS C240 M3: Fusion-io Drives with SSD Drives on Secondary Server     18

Performance Study of Fusion-io with SSD Drives................................................................................................... 21

Failover Validation............................................................................................................................................................... 23

Conclusion................................................................................................................................................................................. 24

For More Information.............................................................................................................................................................. 25


What You Will Learn

Microsoft SQL Server addresses the challenge faced by businesses today to respond to a global audience around the clock. Businesses need deployment ease and low total cost of ownership (TCO) in addition to the capability to meet high availability criteria to consolidate their data centers and remain competitive in the market.

Among the many features that ship with Microsoft SQL Server 2012, failover clustering and database mirroring are commonly implemented for mission-critical, data-based applications. However, the implementation of these technologies can present challenges such as the potential for an unusable secondary node when implementing failover clustering in an active-active configuration and complexities in applications requiring multiple-database access to real-time data when implementing database mirroring in synchronous mode. Additionally, disaster recovery can become a challenge when implementing failover clustering and database mirroring together in the same solution.

The Microsoft SQL Server 2012 AlwaysOn feature supports both high availability and disaster recovery, offering a solution that addresses these challenges. It also supports the use of a secondary node to offload reporting and backup requirements, making Microsoft SQL Server even more reliable and providing even more detailed control.

The AlwaysOn feature lets you create up to four readable secondary replicas and use database mirroring for the data transfer, which can help reduce the amount of time and resources needed to implement high availability. The tasks that can be offloaded to the secondary replica include reporting, database backup, and database snapshot operations. Further, the AlwaysOn feature uses familiar easy-to-use tools for management and monitoring tasks, thereby lowering the operating costs.

This document demonstrates ways to reduce planned and unplanned downtime, increase application availability, and provide data protection using Microsoft SQL Server 2012 with AlwaysOn for high availability and disaster recovery solutions using Cisco UCS® C-Series Rack Servers with Fusion ioMemory cards.

Objectives

This document serves as a reference architecture that illustrates the benefits of using a Cisco and Fusion-io solution with Microsoft SQL Server 2012 to provide a robust, resilient, and efficient infrastructure that can meet the demanding needs of businesses today. This document assumes that the user is familiar with the Cisco Unified Computing System™ (Cisco UCS), Cisco Nexus® Family, Fusion-io, and Microsoft SQL Server 2012 product technologies.

Audience

This document is intended for solution architects, sales engineers, field engineers, and design consultants involved in planning, designing, and deploying Microsoft SQL Server 2012 on Cisco UCS with Fusion-io Infrastructure. It assumes that the reader has an architectural understanding of the base configuration and implementation knowledge of Microsoft SQL Server 2012 AlwaysOn, Cisco UCS, and Fusion-io technology.

Purpose of This Document

This document evaluates the maximum performance of the Microsoft SQL Server 2012 AlwaysOn feature on Cisco UCS C240 M3 Rack Servers with Fusion-io and solid-state disk (SSD) drives. Customers can use this evaluation to help make value-based decisions in choosing Cisco UCS and Fusion-io technologies to implement reliable, cost-effective, and highly available SQL Server 2012 solutions.


Solution Overview

Figure 1 shows the physical layout of the test environment. Cisco UCS C240 M3 servers are directly connected to the fabric interconnect using 10-Gbps unified network fabric. Cisco UCS Manager Release 2.2 supports an option to connect Cisco UCS C-Series Rack Servers directly to the Cisco UCS fabric interconnects. This option enables Cisco UCS Manager to manage Cisco UCS C-Series servers using a single cable for both management traffic and data traffic. The Cisco UCS 6248UP 48-Port Fabric Interconnect is a one-rack-unit (1RU) interconnect; it supports 10 Gigabit Ethernet, Cisco Data Center Ethernet, 32 fixed ports of Fibre Channel, and Fibre Channel over Ethernet (FCoE) and provides throughput of more than 1 terabit per second (Tbps) with low latency. The fabric interconnect creates a unified network fabric throughout the network and provides uniform access to both network and storage resources. Fabric interconnects are deployed in pairs for high availability. The fabric interconnects are connected to two Cisco Nexus 5548 UP Switches upstream.

Cisco Unified Computing System

Cisco UCS is a next-generation data center platform that unites computing, networking, and storage access. The platform, optimized for virtual environments, is designed using open industry-standard technologies. It aims to reduce TCO and increase business agility. The system integrates a low-latency, lossless, 10 Gigabit Ethernet unified network fabric with enterprise-class x86-architecture servers. It is an integrated, scalable, multichassis platform in which all the resources participate in a unified management domain.

The main components of Cisco UCS are as follows:

   Computing: The solution is an entirely new class of computing system that incorporates blade and rack servers based on the Intel® Xeon® processor 5500, 5600, and E5-2600 series. Selected Cisco UCS blade and rack servers offer the patented Cisco® Extended Memory Technology to support applications with large data sets and allow more virtual machines per server.


   Networking: The solution is integrated on a low-latency, lossless, 10-Gbps unified network fabric. This network foundation consolidates LANs, SANs, and high-performance computing networks, which are separate networks today. The unified fabric lowers costs by reducing the number of network adapters, switches, and cables needed, and by decreasing the power and cooling requirements.

   Virtualization: The solution unleashes the full potential of virtualization by enhancing the scalability, performance, and operation control of virtual environments. Cisco security, policy enforcement, and diagnostic features are now extended to virtualized environments to support changing business and IT requirements.

   Storage access: The solution provides consolidated access to both SAN and network-attached storage (NAS) over the unified fabric. By unifying storage access, Cisco UCS can access storage over Ethernet, Fibre Channel, FCoE, and Small Computer System Interface over IP (iSCSI) protocols. This capability provides customers with choice of storage access and investment protection. In addition, server administrators can preassign storage access policies for system connectivity to the storage resources, helping simplify storage connectivity and increase productivity.

   Management: The solution uniquely integrates all the components in the system. This single entity can be effectively managed using Cisco UCS Manager. Cisco UCS Manager has an intuitive GUI, a command-line interface (CLI), and a robust API to manage all system configuration and operations.

Cisco UCS is designed to deliver:

    Reduced TCO and increased business agility

    Increased IT staff productivity through just-in-time provisioning and mobility support

   A cohesive, integrated system that unifies the technology at the data center, with the system managed, serviced, and tested as a whole

   Scalability through a design for hundreds of discrete servers and thousands of virtual machines and the capability to scale I/O bandwidth to match demand

   Industry standards supported by a partner ecosystem of industry leaders

Cisco UCS C240 M3 Rack Servers

Building on the success of the Cisco UCS C-Series M2 rack servers, the enterprise-class Cisco UCS C240 M3 Rack Server (Figure 2) enhances the capabilities of the Cisco UCS portfolio in a 2RU form factor. With the addition of the Intel Xeon processor E5-2600 product family, it delivers significant performance and efficiency gains.

Figure 2.      Cisco UCS C240 M3 Rack Server

The Cisco UCS C240 M3 also offers up to 768 GB of RAM, 24 hard drives or SSD drives, and two 4 Gigabit Ethernet LAN interfaces built into the motherboard. These features together provide outstanding density and performance in a compact package.

The Cisco UCS C240 M3 balances simplicity, performance, and density for production-level virtualization and other mainstream data center workloads. This 2-socket server provides substantial throughput and scalability and extends the capabilities of Cisco UCS. It uses the latest Intel Xeon processor E-2690 series multicore processors to deliver enhanced performance and efficiency. These processors adjust the server performance according to application needs using double-data-rate 3 (DDR3) memory technology, with memory scalable up to 768 GB for demanding virtualization and large-data-set applications. Alternatively, these servers can provide a more cost-effective memory footprint for less demanding workloads.

Why Cisco UCS C240 M3 Rack Servers?

The Cisco UCS C240 M3 offers:

   Outstanding server performance and expandability: The Cisco UCS C240 M3 offers up to two Intel Xeon processor E5-2600 or E5-2600 v2 processors, 24 DIMM slots, 24 disk drives, and four 1 Gigabit Ethernet LAN-on-motherboard (LOM) ports to provide exceptional levels of internal memory and storage expandability and exceptional performance.

   Flexible deployment and easy management: The Cisco UCS C240 M3 can operate in standalone environments or as part of Cisco UCS for greater flexibility and computing density in a small footprint. In a multiple-rack environment, because of the ease of data migration between racks, the complexity of rack management is greatly reduced.

   Workload scalability: The Cisco UCS C240 M3 servers along with four Fusion-io drives, each with a capacity of 3 terabytes (TB) for x8 PCI Express (PCIe) slots, allow organizations to scale to meet growing workload demands, greater spikes in traffic, and infrastructure consolidation.

Cisco UCS VIC 1225

The Cisco UCS VIC 1225 is an 8-port 10 Gigabit Ethernet and FCoE-capable mezzanine card designed exclusively for Cisco UCS.

The card enables a policy-based, stateless, agile server infrastructure that can present up to 256 PCIe standards-compliant interfaces to the host, which can be dynamically configured as either network interface cards (NICs) or host bus adapters (HBAs). In addition, the Cisco UCS VIC 1225 supports Cisco Data Center Virtual Machine Fabric Extender (VM-FEX) technology, which extends the Cisco UCS fabric interconnect ports to virtual machines, simplifying server virtualization deployment (Figure 3).

Figure 3.      Cisco UCS VIC 1225 Architecture

http://www.vmguru.nl/wordpress/wp-content/uploads/2012/08/VIC1280.jpg

Cisco Nexus 5548UP Switch

The Cisco Nexus 5548UP (Figure 4) is a 1RU 1 and 10 Gigabit Ethernet switch that offers up to 960 Gbps of throughput and scales up to 48 ports. It offers 32 x 1 and 10 Gigabit Ethernet fixed Enhanced Small Form-Factor Pluggable (SFP+) Ethernet and FCoE or 1/2/4/8-Gbps native Fibre Channel unified ports and three expansion slots. These slots have a combination of Ethernet and FCoE and native Fibre Channel ports.

Figure 4.      Cisco Nexus 5548UP Switch

http://www.cisco.com/content/dam/en/us/products/switches/ps9441/ps9670/ps11681/nexus_5548up_switch_large.jpg

Fusion-io

The Fusion ioMemory platform combines the virtual storage layer (VSL) and Fusion ioMemory modules into a Fusion ioMemory platform that enhances the capabilities of enterprise applications and databases. This platform uses flash-memory technology to significantly increase data center efficiency, providing enterprise-class performance, reliability, availability, and manageability. Some of the important features of Fusion-io technology are:

   Consistent, low-latency performance: The Fusion ioMemory platform provides consistent, low-latency access for mixed workloads. It supports 15-microsecond access latency, 3-GBps bandwidth, over 700,000 read I/O operations per second (IOPS), and over 1,100,000 write IOPS. The sophisticated Fusion ioMemory architecture allows nearly symmetrical read and write performance, with best-in-class low-queue-depth performance.

   Industry-leading capacity: The Fusion-io drive is available in 365-GB, 785-GB, 1205-GB, 2.4-TB, and 3-TB capacities.

 


Cisco UCS 3-TB Fusion-io adapter specifications and performance details are as follows:

    3-TB multilevel cell (MLC) flash memory capacity

    1.5-GBps bandwidth (1-MB read operations)

    1.1-GBps bandwidth (1-MB write operations)

    143,000 IOPS (512-byte random read operations)

    535,000 IOPS (512-byte random write operations)

    136,000 IOPS (4000 random read operations)

    242,000 IOPS (4000 random read operations)

    15 microseconds of write latency and 68 microseconds of read latency

    Hardware supported: All Cisco UCS M3 blade servers

    Software supported: Cisco UCS Manager 2.1

Hardware and Software Resources

The following section provides details about supported hardware and software.

Hardware Requirements

   2 x Cisco UCS C240 M3 Rack Servers (Intel Xeon processor E5-2697 at 2.7 GHz) with 64 GB of memory

   1 x Cisco UCS VIC 1225

   2 x Cisco UCS 6248UP 48-Port Fabric Interconnects

   2 x Cisco Nexus 5548 Switches

   4 x 3-TB Fusion ioMemory

   4 x 800-GB SSD drives

Client Hardware Requirements

   1 x Cisco UCS C210 M2 Rack Server

Software Requirements

   Cisco UCS Release 2.2(1b) firmware

   Cisco UCS Release 1.4(5g) firmware

   Microsoft Windows Server 2012 Datacenter Edition

   Microsoft Windows Server 2012 Enterprise Edition

   Microsoft SQL Server 2012 Enterprise Edition

   HammerDB


Environmental Configuration and Settings

This solution was validated with the configuration listed in Table 1.

Table 1.       Specifications for Performance Testing

Component

Model, Quantity, and Size

Server

Cisco UCS C240 M3

CPU

2 Intel Xeon processor E5-2697 CPUs

RAM

64 GB (8 x 8 GB)

Fusion-io

4 x Fusion-io drive MLC (3 TB)

RAID

Software RAID 1

SSD drive

4 x 800 GB

RAID

RAID 10

Microsoft SQL Server 2012

2 x Microsoft SQL Server instances

Availability replica synchronization mode

Synchronous-commit mode

Microsoft SQL Server memory settings

  Minimum: 16,000 MB
  Maximum: 58,982 MB

Online transaction processing (OLTP) database

1.5 TB

HammerDB Configuration

Ramp-up time in minutes

5

Test duration in minutes

25

User delays in milliseconds (ms)

500

Repeat delays in ms

500

Iterations

1

Cisco UCS C240 M3 Server BIOS Settings

The BIOS tests and initializes the hardware components of a system and boots the operating system from a storage device. A typical computational system has several BIOS settings that control the system’s behavior. Some of these settings are directly related to the performance of the system.

Figure 5 shows the BIOS settings for high performance.

Figure 5.      BIOS Settings for High Performance


Storage Layout

This section describes two storage layouts. This document discusses two case studies to demonstrate the performance of:

   Fusion-io drives on both primary and secondary Microsoft SQL database servers configured with RAID 1

   Fusion-io drives on primary and SDD drives on secondary Microsoft SQL database servers configured with RAID 10

RAID 1

below shows the storage architecture for the Fusion-io drive tests. RAID 1 was configured with two Fusion-io drives to achieve fault tolerance, redundancy, and high performance.


RAID 10

Figure 7 shows the storage architecture for the SSD drive tests. RAID 10 was configured using four SSD drives to achieve fault tolerance, redundancy, and better performance.

Network Design

Figure 8 shows the network connectivity from the Cisco UCS C240 M3 server to the fabric interconnect. Cisco UCS Manager 2.2 supports the existing rack server integration and management option through a shared LOM, using two separate cables for data traffic and management traffic. Two 10 Gigabit Ethernet SFP cables are connected to Fabric Interconnect A and Fabric Interconnect B for separate data and management traffic.

Microsoft SQL Server AlwaysOn Availability Group Design

The Microsoft SQL Server AlwaysOn feature is a new integrated, flexible, and costefficient high-availability and disaster-recovery solution. It can provide data and hardware redundancy within and across data centers and improve application failover time to increase the availability of your missioncritical applications.

The AlwaysOn availability group feature is based on the Microsoft Windows Server Failover Clustering (WSFC) service; the difference is that on each node, a Microsoft SQL Server instance is installed and active. All Microsoft SQL Server nodes participating to an AlwaysOn Availability Group are members of a failover cluster.

An AlwaysOn Availability Group is composed of replicas (a group of one or more databases). There is one primary replica and one to four secondary replicas (eight for Microsoft SQL Server 2014). A secondary replica is a copy of the database from the primary replica when the database is modified (changes are replicated on all secondary replicas). An AlwaysOn Availability Group is a failover cluster resource group (containing at least the virtual node name [VNN] and virtual IP address cluster resources on which the clients will connect).

Figure 9 shows a logical view of the Microsoft SQL Server AlwaysOn feature in a Microsoft Windows cluster environment that was incorporated into the Microsoft SQL Server AlwaysOn architecture.

The availability group was configured with synchronous-commit mode, Transactions are not completed until they are committed to all databases in the synchronous availability group.

The public interface is configured with the IP address that will be used to communicate with clients over the network. The private interface is used for communication with other cluster nodes. This is the interface used for the heartbeat.

Table 2 and Table 3 provide detailed information about Microsoft SQL Server AlwaysOn availability groups and availability replica configurations.

Table 2.       Microsoft SQL Server AlwaysOn Availability Group Configuration Details  

Availability Group Configuration

Microsoft SQL Server availability group

Member (Instance)

Default Role

Database

Database Configuration

Database Size

Memory Settings

Availability group database

  win2012-sql\sql01
  win2012-sql\sql02
  Primary
  Secondary

 OLTP workloads

Database and log are on the same logical unit number (LUN)

1.5 TB

  Minimum: 15.6 GB
  Maximum: 57.6 GB

Table 3.       SQL Server AlwaysOn Availability Replica Configuration Details

Availability Replica Configuration

Microsoft SQL Server availability group

Service Instance

Initial Role

Automatic Failover

Synchronous Commit

Allow Readable Secondary

Replication Network

Availability group database

  win2012-sql\sql01
  win2012-sql\sql02
  Primary
  Secondary
  Yes
  Yes
  Yes
  Yes
  Yes
  Yes

Public network

 

For more information about Microsoft SQL Server AlwaysOn, visit http://msdn.microsoft.com/en-in/library/ff877884.aspx.

Case Studies

The Cisco UCS C240 M3 server, Fusion-io, and Microsoft SQL Server AlwaysOn solution achieves single-site availability and optimum resource allocation for better performance. This type of configuration optimizes hardware efficiency. The secondary server functions as a readable mirror and failover database. The readable mirror offloads tasks such as reporting and backup to improve the performance of both the live database queries and secondary server.

To demonstrate the added value of Cisco UCS with Fusion-io, two case studies are considered. In the first case, primary and secondary Cisco UCS servers both have Fusion-io drives. In the second case, the primary Cisco UCS server has Fusion-io drives, and the secondary server has SSD drives that are used for storing replicated data.

   Case study 1—Microsoft SQL Server 2012 AlwaysOn using Fusion-io with Fusion-io drives: In this case study, both the primary and secondary Microsoft SQL database servers were run on Fusion-io drives configured with RAID 1. HammerDB was used to generate load for a fixed duration, and the database performance was monitored and recorded using the Microsoft Windows performance counters.

   Case study 2—Microsoft SQL Server 2012 AlwaysOn using Fusion-io with SSD drives: In this case study, the primary Microsoft SQL database server was run on Fusion-io drives configured with RAID 1, and the secondary Microsoft SQL database server was run on SSD drives configured with RAID 10. HammerDB was used to generate load for a fixed duration, and the database performance was monitored and recorded using the Microsoft Windows performance counters.

Upon analyzing the performance characteristics of these two use cases, the added value gained by using Cisco UCS with Fusion-io drives in both the primary and secondary servers becomes clear.

Performance Results and Analysis

This section provides the performance and comparison metrics for the two case studies discussed in this document. The Fusion-io drives on both the primary and secondary servers are compared with the Fusion-io and SSD drives on the primary and secondary servers respectively, with a limited workload. The workloads shown here are typical OLTP workloads, mimicking real-world transactional workloads. The ramifications of the workloads seen in the enterprise-level transaction processing are beyond the scope of this document. Also, the resource utilization shown here is not the result of any limitations of the Cisco UCS infrastructure or Fusion-io, but is the result of the workload characteristics.

SQL Server 2012 AlwaysOn on Cisco UCS C240 M3: Fusion-io Drives on Both Primary and Secondary Servers

Table 4 shows the configuration parameters for Fusion-io drives on both primary and secondary Microsoft SQL servers.

Table 4.       Configuration Parameters for Fusion-io Drives on Both Primary and Secondary Microsoft SQL Database Servers

Configuration Parameters

OLTP database

1.5 TB

HammerDB

Workload tool

User load

125

Replication

Synchronous

Host 1

Cisco UCS C240 M3 server

Intel Xeon processor E5-2697 v2 with 64 GB of memory

Fusion ioDrive2

2 x 1.5 TB

OS

Microsoft Windows 2012

Software RAID policy

RAID 1

Microsoft SQL Server 2012

AlwaysOn enabled

Host 2

Cisco UCS C240 M3 server

Intel Xeon processor E5-2697 v2 with 64 GB of memory

Fusion ioDrive2

2 x 1.5 TB

OS

Microsoft Windows 2012

Software RAID policy

RAID 1

Microsoft SQL Server 2012

AlwaysOn enabled

 

Microsoft SQL Server performance varies from environment to environment according to on the complexity of the deployment and the components used in the architecture.

The performance aspect of AlwaysOn availability groups is crucial to maintaining the service-level agreement (SLA) for your mission-critical databases. Understanding how AlwaysOn availability groups send logs to secondary replicas can help you estimate the recovery time objective (RTO) and recovery point objective (RPO) of your AlwaysOn implementation and identify bottlenecks in poorly performing availability groups or replicas.

The following are the major performance counters that measure user experience:

   Throughput in IOPS (Transfers per Second)

   Throughput in transactions per second (TPS)

   Microsoft SQL Server CPU utilization

   Latency    

   Disk utilization (percent)

   Network bandwidth utilization


Throughput in IOPS (Transfers per Second)

Figure 10 shows the number of disk read and write operations per second, the average number of disk transfers per second (IOPS), and the number of disk transactions per second (TPS) for the primary and secondary Microsoft SQL Servers with the AlwaysOn feature enabled. The test achieved 26,166 TPS on the primary server and 20,606 TPS on the secondary server. During the test, the average total IOPS were 16,298 on the primary server and 14,034 on the secondary server.

Microsoft SQL Server CPU and Network Utilization

Figure 11 shows the CPU and network utilization on the primary and secondary Microsoft SQL Servers. During the test, the average CPU utilization was 52.26 percent on the primary server and 5.72 percent on the secondary server. The average number of bytes sent and received was 49.29 MBps on the primary server and 21.68 MBps on the secondary server.

Latency

Figure 12 shows the average latency observed during the tests on the primary and secondary Microsoft SQL Servers. The tests achieved an average read latency of 0.24 ms and an average write latency of 0.41 ms on the primary server, and an average read latency of 0.49 ms and an average write latency of 0.05 ms on the secondary server.

Figure 12.    Read/Write Latency for Microsoft SQL Servers

Microsoft SQL Server 2012 AlwaysOn on Cisco UCS C240 M3: Fusion-io Drives with SSD Drives on Secondary Server

Latency is crucial to Microsoft SQL Server databases and SSD drives, with traditional drive interfaces depending on RAID controllers that create a performance bottleneck. Heavy loads result in deep queuing and high latencies, and server CPU cycles are wasted managing thousands of threads. RAID controller technology tends to be reactive rather than proactive and works best when a built-up deep queue exists: that is, storage performance is at its best when queuing and latency are poor. Fusion ioMemory eliminates the RAID controller bottleneck, giving applications direct and simultaneous access to the flash memory by performing management through software running on the host (VSL). The result is shorter queues and more efficient use of server CPU cycles, which are spent processing requests instead of managing the waiting threads. With Fusion ioMemory, transaction response times are fast under all workloads as evidenced by Fusion-io’s exceptionally low write latency, resulting in predictable and consistent high performance.


Table 5 shows the configuration parameters for Fusion-io drives on the primary Microsoft SQL Server and SSD drives on the secondary Microsoft SQL Server.

Table 5.       Configuration Parameters for Fusion-io Drives on Primary and SSD Drives on Secondary Microsoft SQL Database Server

Configuration Parameters

OLTP database

1.5 TB

HammerDB

Workload tool

User load

60, 125, and 250

Replication

Synchronous

Host 1

Cisco UCS C240 M3 server

Intel Xeon processor E5-2697 v2 with 64 GB of memory

Fusion-io drive

2 x 1.5 TB

OS

Microsoft Windows 2012

Software RAID policy

RAID 1

Microsoft SQL Server 2012

AlwaysOn enabled

Host 2

Cisco UCS C240 M3 server

Intel Xeon processor E5-2697 v2 with 64 GB of memory

SSD drives

4 x SSD 800 GB

OS

Microsoft Windows 2012

Software RAID policy

RAID 10

Microsoft SQL Server 2012

AlwaysOn enabled

 


Figure 13 shows the setup and configuration details for the Fusion-io and SSD drive performance analysis.

 

Microsoft SQL Server high performance is determined by a combination of high IOPS and low latency. As the stress on the system increases when an application is run, the system continues to deliver high IOPS, but at a higher level of latency. This increase in latency can seriously hinder the real-time performance of the system.


This relationship between IOPS and latency is the reason to note the benchmark results. Although some SSD drives claim high IOPS, they typically are tuned for one specific block size. Fusion ioMemory supports a range of block sizes that is critical for real-world performance. Fusion ioMemory outperforms SSD drives where it really matters: to improve application performance. HammerDB was used in the test performance analysis. Other tools are fairly straightforward and can be used to perform a particular type of I/O operation on one or many test files to measure IOPS and latency. The following section demonstrates the test setup and shows how Fusion ioMemory outperforms SSD drives with OLTP workloads on a Microsoft SQL Server 2012 database.

Performance Study of Fusion-io with SSD Drives

For the performance study, the results were captured with Fusion-io drives on both the Microsoft SQL Servers and Fusion-io drives on the primary Microsoft SQL Server, and SDD drives on the secondary Microsoft SQL server. HammerDB was used to create an OLTP database with a size of 1.5 TB, and load was generated by simulating 125 virtual users.

Figure 14 shows total IOPS and TPS for Microsoft SQL Servers having Fusion-io with Fusion-io drives, and Fusion-io with SSD drives. Fusion-io with Fusion-io drives delivered considerably better performance when compared to Fusion-io with SSD drives. The tests achieved 9 percent more IOPS and 12 percent more TPS on Microsoft SQL Servers using Fusion-io with Fusion-io drives.

Figure 15 shows the average read and write latency for Fusion-io with Fusion-io drives and for Fusion-io with SSD drives. The average read and write latency of Fusion-io with Fusion-io drives was low compared to Fusion-io with SSD drives.

Figure 15.    Read/Write Latency for Fusion-io with Fusion-io Drives and for Fusion-io with SSD Drives

With Fusion-io drives on the secondary server, the tests achieved read latency of 0.49 ms and write latency of 0.05 ms, but with SSD drives on the secondary server, the read latency was 4.50 ms and the write latency was 19.35 ms. The results clearly show that the read and write latency of the secondary Microsoft SQL Server with SSD drives is very high compared to that for Fusion-io drives on the secondary Microsoft SQL Server.

Having Fusion-io drives on both the primary and secondary Microsoft SQL Servers provides better performance and lower latency compared to the Fusion-io and SSD drives combination.

From the test results, it is evident that Fusion-io drives provide the necessary I/O performance that today’s applications demand. They accelerate the path between the application and the storage, unlocking the true potential of flash-memory media and providing real performance improvements that translate to measurable business value.


Failover Validation

To emulate a complete database failure, a manual failover of the node 1 database host (primary server) was performed, and the database was checked to verify that the failover completed successfully. Additional checks were performed to verify that the schema and application structures for the primary replica on the primary node and the secondary readable replica on the secondary node were consistent after failover. Figure 16 shows the successful failover of the primary Microsoft SQL Server to the secondary Microsoft SQL Server.

Figure 16.    Successful Failover

 

The AgTest tool was used to capture the true end-to-end statistics for the client observed failover time. A failover time of 10.53 seconds was achieved (Figure 17).

 

To emulate a complete node failure, the node 1 host (primary server) was powered off. The time required for the node 1 host to fail over to the secondary host was measured while checking the row counts to verify that the failover was completed successfully. Additional checks were performed to verify that the schema and application structures for the primary replica on the primary node and the secondary readable replica on the secondary node were consistent after failover.

Conclusion

The Cisco UCS C240 M3 Rack Server offers high performance and internal storage from a single vendor known for its long history of innovation in architecture, technology, partnerships, and services. The Cisco UCS C240 M3 is designed to meet the needs of organizations with increasing data and storage demands. The enterprise-class Cisco UCS C240 M3 server is equipped with Intel Xeon processor E5-2600 technology, which delivers high performance, energy efficiency, and flexibility. Cisco engineers designed the Cisco UCS C240 M3 with the Cisco UCS VIC 1225 to handle a broad range of applications, including workgroup collaboration, virtualization, consolidation, massive data infrastructure, and Server Message Block (SMB) databases.

The Cisco UCS C240 M3 Rack Server with its robust stack of technological offerings uses optimal performance for computing and networking and provides internal storage in one cohesive system to meet the challenges of virtualization technologies and the growing demands on businesses.


The combination of the Cisco UCS C240 M3 server, Fusion ioMemory platform, and Microsoft SQL Server 2012 database dramatically improves performance capabilities. The solution delivers fast access to information that enables organizations to capture important insights to guide business decisions. By deploying Cisco UCS servers, Fusion-io technology, and Microsoft SQL Server 2012 together, companies can use this enterprise-class, high-performance, low-latency, reliable, and scalable solution to increase customer and user loyalty and trust, gain competitive advantage, and lower operating costs.

The performance study discussed here showed that Microsoft SQL Server 2012 on the Cisco UCS C240 M3 Rack Server with Fusion-io offers improved application performance and operation efficiency. The Microsoft SQL Server 2012 AlwaysOn feature with 10-Gbps network access connectivity provides faster failover and shorter recovery times.

For More Information

   http://www.cisco.com/c/en/us/products/index.html

   http://www.fusionio.com/

   http://technet.microsoft.com/en-us/sqlserver/ff898410

   http://msdn.microsoft.com/en-in/library/ff877884.aspx