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 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 ...................................................................
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 ............
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.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse 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.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse Table 2. Dell Fast Track Reference Architecture Solution Details Solution Description Configuration ID Dell Fast Track 3.0 Configuration PowerEdge R510 7068273 Dell Fast Track 3.
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.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse Figure 3. Internal Storage Controller Settings RAID configuration One of the most critical decisions that we have to make when deploying a new storage solution is which RAID type(s) to use as that choice heavily impacts the performance of the application. We have configured the proposed Fast Track configuration using RAID 1 disk groups for database data files and database log files.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse SQL Server Configuration These startup options were added to the SQL Server Startup options: -E: This parameter increases the number of contiguous extends that are allocated to a database table in each file as it grows. This improves sequential access. -T1117: This trace flag ensures the even growth of all files in a file group when auto growth is enabled.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse 4 4 se1- v4 Data4 C:\ft\se1-v4 5 5 log1-v5 log C:\ft\log\SE1-log Figure 5 represents the storage system configuration for the proposed Fast Track reference. Figure 5.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse Figure 6.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse Figure 7.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse Calculating BCR BCR is calculated in terms of total read bandwidth from the storage hard drives and not from the buffered cache as in the MCR calculation. This is measured by running a set of standard queries specific to the data warehouse workload. The queries range from I/O intensive to CPU and memory intensive, and provide a reference to compare various configurations.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse Dell Boomi Dell Boomi is the market-leading provider of on-demand application and data integration services and the creator of AtomSphere®, the industry's #1 Integration Platform as a Service (iPaaS). AtomSphere connects providers and customers of SaaS, cloud and on-premise applications via a pure SaaS integration platform that does not require software or appliances.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse Standard Boomi integration components include: Connector connect to any application or data source Always the first and last steps of an integration workflow, the Connector enables access to another application or data source. The connector sends/receives data and converts it into a normalized XML format.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse have the option of specifying whether they wish to attempt to auto-repair bad data, or simply reject rows that are “dirty”. All validation results are routed through a “clean” or “rejected” path which allows users to explicitly handle either scenario.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse Figure 9. Dell Boomi Build Environment Real-Time Integration Testing Configuring Integration Automation Integration processes can be configured for “hands off” execution.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse Deploy an Integration Process Once your integration processes are built, they are deployed into lightweight runtime engines known as Boomi Atoms. The Boomi Atom is the "secret sauce" to Boomi's technology, and allows your integration processes to run wherever needed and as many times as needed, enabling nearly infinite scalability.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse Addressing Enterprise-Class Integration Business critical integration processes demand a different level of reliability and scalability. To ensure that key integration processes are reliable and highly available, the Enterprise Edition of AtomSphere features Molecules, an enterprise-grade version of Boomi’s patent-pending Atom™ technology.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse View the actual data that was sent or received with an external application (dependent on security permissions.) This data will be retrieved dynamically because data sets are never stored in Boomi's data center. Retry the data, which will request the Atom to retry the data sets you have selected, and pass them to a new process execution. View the link between inbound and outbound data sets.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse Conclusion The Dell Microsoft Fast Track Data Warehouse architecture provides a uniquely well-balanced data warehouse implementation solution. By following the best practices at all the layers of the stack, a balanced data warehouse environment can be achieved with a greater performance benefit than the traditional data warehouse systems.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse References Dell SQL Server Solutions www.dell.com\sql Dell Services www.dell.com\services Dell Support www.dell.com\support OLTP and OLAP http://datawarehouse4u.info/OLTP-vs-OLAP.html Microsoft Fast Track Data Warehouse and Configuration Guide Information www.microsoft.com/fasttrack http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91448AB733B1AF/Fast_Track_Configuration_Guide.
Dell R510 Reference Configuration for Microsoft SQL Server® 2008 R2 Fast Track Data Warehouse Appendix Summary of Microsoft SQL Server Fast Track results The performance of the system configuration was analyzed with RAID1 for simple, average & complex query variants with 5, 10, 20, 40 sessions. Various maximum degrees of parallelism (MAXDOP) options were tested to arrive at the optimal configuration for this workload with MAXDOP=6.
