carloscastilla - Fotolia
Since the release of SQL Server 2014, a number of vendors have announced plans to deliver appliances built around SQL Server 2014 and other data-related technologies. Each appliance provides preconfigured hardware and software aimed at supporting high-performing, data-driven applications.
Several of the appliances are based on the Microsoft Analytics Platform System (APS), a data warehousing platform that integrates structured and non-structured data. Three companies currently offer an APS appliance: Dell, Quanta and HP.
Dell is also teaming up with Fusion-io to deliver the Acceleration Appliance for Databases, a flash-based, platform-agnostic product optimized for enterprise applications such as SQL Server 2014. NEC and HGST have joined forces to offer another flashed-based appliance, the PCIe SSD Appliance for Microsoft SQL Server, which is aimed squarely at SQL Server data-related workloads. Let's look at what these emerging appliances have to offer.
Microsoft Analytics Platform System
When Microsoft released the first appliance update for version 2 of SQL Server Parallel Data Warehouse, it changed the name of the PDW appliance to the Analytics Platform System, although that version of SQL Server appears to have retained the PDW moniker, at least informally.
Despite the confusing labeling, SQL Server PDW continues to support a massively parallel processing (MPP) architecture that distributes and parallelizes computing operations across multiple physical nodes. The MPP technology maximizes query performance within the newly dubbed APS appliance and supports high levels of query complexity and concurrence. In addition, the appliance takes advantage of SQL Server's in-memory columnstore features, which can improve query performance even further.
To support the addition of unstructured data, the appliance includes Microsoft's HDInsight, a Hadoop distribution based on the Hortonworks Data Platform. Hadoop is a software framework for managing and analyzing large sets of unstructured data on commodity hardware. The APS appliance also comes with PolyBase, a tool that enables T-SQL queries to access both PDW databases and the HDInsight data platform.
The APS appliance is built on Windows Server 2012. The operating system provides directory management through Active Directory, virtualization through Hyper-V, and high availability through Failover Clustering and Clustered Shared Volumes, or CSVs. In addition, SQL Server can take advantage of Windows Storage Spaces, which provide virtual drives for pooling storage resources.
Also included with the APS appliance is the hardware necessary to host the software and its data. The hardware is made up of a set of commodity servers, drives, storage devices and networking components that can be scaled out to support up to 6 petabytes of raw storage.
Not surprisingly, vendors selling the appliances outfit them with their own hardware. The Dell appliance is based on Dell's PowerEdge R60 servers, Quanta uses its Quad-enclosure servers and HP uses HP ProLiant GenB servers. The appliances are delivered fully preconfigured and tested, so they're ready to deploy when they arrive.
NEC PCIe SSD Appliance for Microsoft SQL Server
The NEC PCIe SSD Appliance for Microsoft SQL Server incorporates the power of the NEC Express 5800 scalable enterprise server series to support large-scale online transaction processing (OLTP) and business intelligence (BI) operations. The Express 5800 server has a 4U form factor (four rack units in size) and supports up to four Intel Xeon E7 processors, for a total of 24 physical cores. The server also comes with 16 available PCI-Express 3.0 I/O slots and 64 available DDR3 memory slots.
But the servers aren't solely responsible for delivering high-performing data processing. Included with the appliance is HGST FlashMAX II PCIe server-mounted flash storage. The FlashMAX II is a multi-level cell flash unit, which means each memory unit can store more than a single bit of information. The unit also incorporates a hardware RAID mechanism optimized for flash memory. With the FlashMAX II devices, a server can hold up to 8.8 terabytes (TB) of flash storage and deliver logical scan rates of 8.2 GBps.
The NEC appliances are built according to best practice configurations, as outlined in the SQL Server Fast Track Data Warehouse (FTDW) reference architecture. The FTDW defines a core-balanced architecture that maximizes SQL Server data processing against component hardware throughput. The NEC appliance uses the FTDW configurations to balance the CPU cores against the I/O channels and storage sequential I/O capacities.
Dell Acceleration Appliance for Databases
NEC isn't the only vendor introducing flash-based appliances. Dell's Acceleration Appliance for Databases incorporates flash storage technology from Fusion-io. What sets Dell apart from NEC is that the Dell appliance is not built around a specific platform. Rather, its focus is on the "enterprise application," which can include a wide range of database products, including MySQL, Sybase, Oracle Database, SAP HANA, MongoDB, Apache Cassandra and, of course, SQL Server 2014.
The Dell appliance uses the Dell PowerEdge R720, a 2U rack server that can support up to 12 TB of flash storage, 40 GB of bandwidth and 2.5 million input/output operations per second (IOPS). The flash storage is provided through Fusion ioMemory devices and uses Adaptive Flashback to protect data. Unlike many flash storage devices, which rely on RAID for their storage configurations, Adaptive Flashback handles failures at the block level, rather than device, simplifying management and causing less disruption to business operations should failure occur.
The PowerEdge servers, when combined with the Fusion-io flash storage, can speed up the performance of data-driven operations significantly, while reducing latency and I/O bottlenecks. The appliance is available as a standalone or high-availability product and provides a choice of networking options, including fibre channel and Infiniband.
SQL Server 2014 appliances
The APS appliance is clearly aimed at big data operations that can support petabytes of data. The appliance specifically targets BI and data analytics by providing the MPP architecture, which can distribute and parallelize processing-intense computing operations, and by incorporating unstructured data into its architecture.
The NEC PCIe SSD Appliance for Microsoft SQL Server focuses on delivering a flash-based product for SQL Server 2014 data-driven applications. It cannot handle nearly as much data as an APS appliance, but for OLTP workloads and smaller warehousing needs, the flash storage is a game-changer.
The same goes for the Dell Acceleration Appliance for Databases. The flash storage can make some operations unbelievably fast, but it's not built to handle petabytes of data. In addition, the appliance is not specifically fine-tuned according to FTDW best practices. Instead, the focus is on handling different data systems efficiently, which can translate to increased flexibility in the long term.
If SQL Server appliances are on your radar, you now have several options for handling your data workloads. Keep in mind, though, that it's a changing market, so more could be coming at any time. And be sure to do your homework if you're considering a SQL Server appliance. They have many benefits, but come with hefty price tags, and you certainly don't want to purchase one only to discover it's something that doesn't fit your needs a few months down the road.
Check out these third-party tools for handling bottlenecks in SQL Server
Learn why SQL Server 2016 features are improving cloud, security and analytics support
Keep these four things in mind when you upgrade to SQL Server 2014
Navigate the upgrade path from SQL Server 2005 to SQL Server 2014
Learn how to use backup and restore to upgrade to SQL Server 2014 SP1
Test your knowledge of SQL Server 2016's new features