SQL Server 2012 features: Weighing the benefits, limitations
A comprehensive collection of articles, videos and more, hand-picked by our editors
Microsoft has joined forces with Dell Inc. and Hewlett-Packard Co. to provide SQL Server 2012 data warehouse appliances...
that combine hardware and software technologies into integrated warehousing platforms. Each appliance contains the components necessary to store and manage data as well as deliver high-performing query processing and analytics. Not only do they include the server, memory, storage and networking hardware needed to support a comprehensive system, but also the preinstalled software to run all system- and data-related operations. In addition, the appliances are fully configured and tested before they ship.
SQL Server 2012 Parallel Data Warehouse appliances
Both Dell and HP offer SQL Server 2012 data warehouse appliances built with Parallel Data Warehouse (PDW), a massively parallel processing (MPP) data warehouse platform designed specifically for hardware appliances. With its scale-out architecture and multi-petabyte (PB) capacity, MPP distributes and parallelizes computing operations across multiple physical nodes to support high levels of query complexity and concurrence. Each node runs its own SQL Server instance and is configured with a CPU, memory and storage in order to maximize performance.
SQL Server 2012 PDW also comes with the latest version of the xVelocity in-memory analytics engine. The engine supports memory-optimized columnstore indexes that you can cluster and update, unlike columnstore indexes in SQL Server 2008 R2. Columnstore indexes store data in memory, in a columnar fashion, while taking advantage of state-of-the-art compression technologies. As a result, data is easier to access and more data can be held in memory, leading to significant query performance gains, when compared to traditional indexes.
SQL Server 2012 PDW also includes PolyBase, a new query technology that can retrieve data from both relational databases and Hadoop clusters. Hadoop is a framework for storing and processing large sets of unstructured data on commodity hardware. In the past, to integrate structured and nonstructured data, you would first need to perform MapReduce queries to retrieve data from the Hadoop clusters and then integrate that data into your relational database system. PolyBase, however, accepts standard T-SQL statements that can retrieve data from tables in a relational database and in a Hadoop cluster. PolyBase also lets client tools such as Microsoft Excel connect to PDW and retrieve Hadoop data.
SQL Server 2012 data warehouse appliances run Windows Server 2012, which allows them to take advantage of two important Windows Server features: Hyper-V and Storage Spaces. Hyper-V is a virtualization technology that eliminates the need for the appliance control rack found in earlier versions of PDW, thus helping to lower the hardware footprint and subsequently its cost. Storage Spaces lets you group a set of disks into a single virtual disk, which also helps to better use hardware and reduce costs.
HP delivers PDW through its appliance AppSystem for Microsoft SQL Server 2012 Parallel Data Warehouse, also referred to as the HP Enterprise Data Warehouse Appliance. The appliance is built with HP ProLiant Gen8 servers (DL300 series) and HP D6000 direct-attach storage. A full rack includes eight active nodes and two passive ones and supports up to 1 PB of data. However, the appliance can run up to 64 nodes across multiple racks. If your initial data needs are smaller than what a full rack provides, you can start with a quarter-rack system (two active nodes and two passive ones) and add nodes when you need them. The quarter rack supports up to 210 terabytes (TB) of data. Whichever size rack you start with, the appliance is also configured with Microsoft and HP management software and comes with a collaborative support model.
Dell's version of the PDW appliance, Dell Parallel Data Warehouse, is built with 12th-generation PowerEdge R620 servers that support Energy Smart technologies. The appliance also uses Dell PowerVault arrays that provide high-density storage and advanced redundancy services. Each rack supports three, six or nine nodes. The base unit, a three-node rack, stores up to 113 TB of data on multiple 1 TB disks, but you can double or triple the capacity by upgrading to 2 TB or 3 TB drives. A full rack (nine nodes) provides up to 1.2 PB of usable storage. Plus, you can string up to six racks together to increase computing and storage resources. Support is provided through Microsoft PDW Support, which brings Dell in as needed to collaborate on relevant issues.
Dell Quickstart Data Warehouse Appliance
Organizations that don't need the power of a PDW appliance might look to Dell's Quickstart Data Warehouse Appliance with SQL Server 2012. Like the PDW appliances, Quickstart is a single, out-of-the-box system that includes all necessary hardware and software, preinstalled and preconfigured. In addition, the appliance comes with training and 12 months of Dell support.
The Quickstart appliance includes a Dell 12th-generation PowerEdge server with a Quad Core Intel Xeon processor, 96 GB of memory and 26 hard disks. Two of the disks run Windows Server 2008 R2, two serve as hot spares and the other 22 provide for data storage and management. The appliance can store up to 5 TB of compressed data.
The server is configured with the SQL Server Data Warehouse Appliance Edition, an OEM version of SQL Server 2012. The appliance edition is a pared-down version of SQL Server Enterprise that retains several important data warehousing features, including table partitioning, the xVelocity engine, Remote BLOB Storage (RBS) and integration with Microsoft business intelligence tools such as Excel. In addition, the database engine is optimized for data warehouse query performance and data compression.
The Quickstart appliance is based on the Fast Track Data Warehouse reference architecture (RA). The RA provides a step-by-step guide for building a SQL Server 2012 data warehouse. Not only does it include instructions for how to buy and assemble the hardware, but also setup instructions for installing the software and configuring specific settings. By employing the RA to build the appliance, Dell uses pre-tested best practices based on industry standards to deliver a balanced hardware and software configuration.
Dell also delivers the Quickstart appliance with a one-year license to Dell Boomi, a data integration service that extracts data from various production applications, including on-premises data stores, Software as a Service (SaaS) platforms and cloud-based resources. With Boomi, you have centralized Web management without having to configure additional hardware or software.
The SQL Server data warehouse
Building an effective SQL Server 2012 data warehouse platform is no small task. You must first plan your hardware and software requirements and then purchase the necessary components. The hardware must include not only servers and data storage, but also switches, cables and other networking components. For the software, you need Windows Server, SQL Server and any necessary management software. Such a system can require significant resources to implement. Under the right circumstances, an appliance could prove a cost-effective strategy over the long term, especially if you must bring on personnel to build your own system. For the organization looking to implement a SQL Server data warehouse as quickly, easily and effectively as possible, the data warehouse appliances should at least be part of the discussion.
About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation.