Dell PowerEdge R510 User Manual
Dell SMB Reference Configuration for
Microsoft® SQL Server® 2008 R2 Fast
Track Data Warehouse with the Dell
BOOMi Integration Capability
A Dell Technical White Paper
Dell | Database Solutions Engineering
Dell Product Group
August 2011
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse
THIS WHITE PAPER IS FOR INFORMATIONAL PURPOSES ONLY, AND MAY CONTAIN TYPOGRAPHICAL
ERRORS AND TECHNICAL INACCURACIES. THE CONTENT IS PROVIDED AS IS, WITHOUT EXPRESS OR
IMPLIED WARRANTIES OF ANY KIND.
© 2011 Dell Inc. All rights reserved. Reproduction of this material in any manner whatsoever without
the express written permission of Dell Inc. is strictly forbidden. For more information, contact Dell.
Dell, the DELL logo, the DELL badge, and PowerEdge are trademarks of Dell Inc. Microsoft, Windows,
and SQL Server are either trademarks or registered trademarks of Microsoft Corporation in the United
States and/or other countries. Intel and Xeon are either trademarks or registered trademarks of Intel
Corporation in the United States and/or other countries. Other trademarks and trade names may be
used in this document to refer to either the entities claiming the marks and names or their products.
Dell Inc. disclaims any proprietary interest in trademarks and trade names other than its own.
October 2011
Page ii
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse
Contents
Introduction ................................................................................................................ 3
Audience and Scope .................................................................................................... 3
Microsoft SQL Server Fast Track Data Warehouse ................................................................... 3
Dell Fast Track Data Warehouse Reference Architecture .......................................................... 4
Hardware Component Architecture ................................................................................. 4
Dell PowerEdge R510 Server ....................................................................................... 5
Processors ............................................................................................................. 5
Memory ................................................................................................................ 5
Internal Storage Controller (PERC H700) Settings ................................................................ 5
Stripe element size .................................................................................................. 6
Read policy ............................................................................................................ 6
RAID configuration ................................................................................................... 7
Application Configuration ............................................................................................. 7
Windows Server 2008 R2 SP1 ....................................................................................... 7
SQL Server Configuration ........................................................................................... 8
Internal Storage System ............................................................................................ 8
Performance Benchmarking ............................................................................................. 9
Baseline Hardware Characterization using Synthetic I/O ....................................................... 9
Fast Track Workload Evaluation ................................................................................... 11
Calculating MCR .................................................................................................... 11
Calculating BCR .................................................................................................... 12
Populating Your Data Warehouse .................................................................................. 12
Building an Integration Process .................................................................................... 13
Configuring Integration Automation ............................................................................ 16
Deploy an Integration Process ...................................................................................... 17
Addressing Enterprise-Class Integration ....................................................................... 18
Reviewing Overall Integration Performance .................................................................. 18
Reviewing Individual Process Executions ...................................................................... 18
Exploring Atoms .................................................................................................... 19
Subscribing to Process Alerts .................................................................................... 19
Conclusion ................................................................................................................ 20
References ................................................................................................................ 21
Appendix .................................................................................................................. 22
Page 1
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse
Summary of Microsoft SQL Server Fast Track results ........................................................... 22
Tables
Table 1. Tested Dell Fast Track Reference Architecture Component Details .............................. 4
Table 2. Dell Fast Track Reference Architecture Solution Details ........................................... 5
Table 3. Mount Point Naming and the Storage Enclosure Mapping ........................................... 8
Figures
Figure 1. Proposed Dell Fast Track Reference Architecture.................................................... 4
Figure 2. Virtual Disk Settings ....................................................................................... 6
Figure 3. Internal Storage Controller Settings .................................................................... 7
Figure 4. RAID Configuration ......................................................................................... 7
Figure 5. Storage System Components ............................................................................. 9
Figure 6. SQLIO Line Rate Test from Cache (Small File) ...................................................... 10
Figure 7. SQLIO Real Rate Test from Disk (Large File) ........................................................ 11
Figure 8. Dell Boomi ................................................................................................. 13
Figure 9. Dell Boomi Build Environment Real-Time Integration Testing ................................... 16
Figure 10. Dell Boomi – Process Execution View .............................................................. 18
Page 2
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse
Introduction
Data Warehousing is used for integrating, storing and analyzing data in order to perform trend analysis,
business intelligence reporting and various types of predictive analysis. With today’s never ending data
growth and complexity, it’s becoming a tedious job for customers to balance capacity and performance
within the data warehouse system. Growing data volumes and loading challenges, OLAP query
complexity, and number of users are causing response times to increase. IT executives are looking for
solutions that offer lower cost, easier management, and better performance.
There are many challenges in designing a database configuration for OLAP workloads. One is ensuring
an optimal balance of I/O, storage, memory and processing power.
Dell™ and Microsoft® jointly developed guidelines and design principles to assist customers in designing
and implementing a balanced configuration specifically for Microsoft SQL Server® data warehouse
workloads to achieve “out-of-box” scalable performance.
Another challenge is data integration and cleansing, especially where data is coming from multiple
sources. Based on direct input from customers, Dell has incorporated Dell Boomi, an efficient cloudbased on-demand application and data integration service as part of the balanced infrastructure.
This whitepaper describes the architecture design principles needed to achieve a balanced
configuration for the Dell PowerEdge™ R510 server using the Microsoft Fast Track Data Warehouse 3.0
guidelines.
Audience and Scope
This whitepaper is intended for customers, partners, solution architects, database administrators,
storage administrators, and business intelligence users who are evaluating, planning, and deploying an
optimally balanced data warehouse solution. The scope of this whitepaper is limited to the data
warehouse. Analytics tools and reporting services that use the data warehouse are outside the scope of
this whitepaper.
Microsoft SQL Server Fast Track Data Warehouse
In order to overcome the limitations of traditional data warehouse systems, Microsoft has come up with
a cost effective solution that optimally balances the hardware and software capabilities of the system.
It provides an easy to deploy data warehouse infrastructure by mainly focusing on storage tuning and
database layout. Fast Track Data Warehouse (FTDW) its implementing data warehouse solutions
differently. As most data warehousing queries scan large volumes of data, FTDW designs are optimized
for sequential scans/reads. These proven methodologies yield performance much better than that of
traditional data warehousing systems. Based on this fact, DELL has made a deep study on the FTDW
architecture and come up with a reference guide that helps customers implement FTDW on DELL
hardware.
Page 3
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse
Component
Details
Server
PowerEdge R510 (BIOS: 1.6.3)
CPU
(1) Intel® Xeon® CPU X5675 @3.07GHz (HT Enabled)
Number of cores per socket
6
Total Number of CPU Cores
6
Memory
48GB RAM (3 * 16 DDR3 DIMMs @1066MHz)
Internal Hard Drives
12 x 600GB 15K RPM Serial-Attach SCSI 6Gbps 3.5in Hotplug Hard Drive
Operating System
Microsoft Windows® 2008 R2 SP1 Enterprise Edition
Database Software
Microsoft SQL Server 2008 R2 Enterprise Edition
Dell Fast Track Data Warehouse Reference
Architecture
In order to optimize data warehouse stack component performance, we must properly tune each layer.
The following sections explain the tuning of selected hardware and software.
Hardware Component Architecture
Redundant and robust tests have been conducted on DELL’s PowerEdge servers to determine best
practices and guidelines for building a balanced FTDW system.
Figure 1. Proposed Dell Fast Track Reference Architecture
Configuration availability may be further enhanced by configuring database clustering using multiple
servers.
Table 1. Tested Dell Fast Track Reference Architecture Component Details
Page 4
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse
Solution Description
Configuration ID
Dell Fast Track 3.0 Configuration PowerEdge R510
7068273
Dell Fast Track 3.0 Configuration PowerEdge R510
with Dell Boomi
7068280
Table 2. Dell Fast Track Reference Architecture Solution Details
Dell PowerEdge R510 Server
The Dell PowerEdge R510 server is a 2-socket, 2U high-capacity, multi-purpose rack server offering an
excellent balance of internal storage, redundancy, and value in a compact chassis.
The PowerEdge R510 server was developed with a purposeful design, energy-optimized options, the
performance of the Intel Xeon processor 5500 and 5600 series, DDR3 memory, and enterprise-class
manageability. For more technical specifications of R510 Server, please refer to the Power Edge R510
Technical Guide, a link to which is provided in the References section of this document.
Processors
The Microsoft Fast Track 3.0 reference guide describes how to achieve a balance between components
such as storage, memory, and processors. In order to balance the available internal storage and
memory for the Dell PowerEdge R510, a single Intel Xeon X5675 Six core processor operating at
3.07GHz speed was used.
Note: For environments that require high processing capabilities, a second socket can be populated.
Memory
For FTDW architecture, Microsoft recommends using 8GB of memory per processor core. With enough
memory installed on the system, the large-scale queries involving Hash joins and sorting operations will
benefit from SQL Server offloading operations from the Tempdb to Memory. Selection of Memory DIMMS
will also play a critical role in the performance of the entire stack. In our test configuration, we have
configured the database server with 48GB of RAM running at 1066 MHz speed. Refer to the Microsoft
Fast track 3.0 Reference Guide for detailed recommendations on system memory configuration.
Internal Storage Controller (PERC H700) Settings
The Dell PERC H700 is an enterprise level SAS 2.0 RAID controller that provides disk management
capabilities, high available, and security features in addition to improved performance of up to 6GB/s
throughput. Figure 2 shows the management console accessible through the BIOS utility.
Page 5
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse
Figure 2. Virtual Disk Settings
Stripe element size
By default, the PERC H700 creates virtual disks with a segment size of 64KB. During the Fast Track
validation testing we utilized stripe element sizes of 64KB and 1MB to compare and contrast any
performance improvements. For most workloads, 64KB default size will provide an adequate stripe
element size. We recommend testing various sizes depending on the workload characteristics of your
configuration.
Read policy
The default setting for read policy on the PERC H700 is “adaptive read ahead”. During testing,
however, we observed that changing the setting to “No read ahead” improved the overall performance
by 4%; we attribute the improvement to unnecessary read ahead during large sequential I/O requests.
Adaptive read ahead typically improves performance in small random workloads.
Page 6
Loading...