Front cover
IBM System x Business Intelligence Solutions for Small-Medium
Businesses with Microsoft SQL Server 2008
Redguides
for Business Leaders
Vinay Kulkarni
Michael Lawson
Presents scalable Microsoft BI reference configurations
designed for small-medium sized businesses
Shows that Microsoft SQL Server 2008 offers
improved performance over SQL Server 2005
Describes how the IBM System Storage DS5000 and the IBM
System x3950 M2 provide an ideal platform for BI applications

---

---

Executive summary
Business intelligence (BI) can get a lot of attention as companies realize they have a vast
amount of data that needs to be analyzed and that can be used to intelligently guide their
business decisions going forward.
The issues that companies face today include:
Data is spread out in different locations and in different formats.
Data needs to be extracted, transformed, and integrated into one common database to be
analyzed.
When analyzed, reports need to be generated that can help executives make better
business decisions.
Overall, business intelligence market opportunity is in the order of $40 to $50 billion,
according to Gartner. Gartner reports also show that BI applications remain the top
technology investment area in a survey of CIOs for the last 4 years.
IBM® has tested BI software components that ship with the Microsoft® SQL Server® product
on System x® scale-up servers. In this IBM Redguide publication, we discuss three such
tested reference configurations, as shown in Table 1. These configurations highlight the
cost-effective standard Intel® Xeon processor-based, server-based scale-up BI solutions by
IBM.
Table 1 Reference configurations using IBM System x3950 M2 servers with Intel Xeon® processors
Small
Small-Medium
Medium
Configuration
16 processor cores
24 processor cores
32 processor cores
64 GB memory
96 GB memory
128 GB memory
50 disks
63 disks
87 disks
Scaling result
1x (baseline)
1.17x
1.32x scaling 24 to 32 cores
1.54x scaling 16 to 32 cores
© Copyright IBM Corp. 2009. All rights reserved.
1

---

These reference configurations have the following characteristics:
Are tested on standard servers using Intel Xeon processors
Are tested and tuned along with Microsoft SQL Server team
Are configured using System x3950 M2 modular scale-up servers that are built on the IBM
X4 chipset, which incorporates mainframe-like RAS features
Can be easily scaled up, because adding processor, memory, and I/O resources when
needed is as easy as connecting additional servers to the first server with scalability
cables and then rebooting the single-image complex
Show how customers can reduce cube processing time
Describe the servers and storage that you need to invest in based on database size and
the needed processing speed
Are targeted at the small and medium business (SMB) market with database sizes up to
2 TB.
Introduction
Business intelligence is the accurate and timely interactive access to the most important
information in the company. It has become a valued tool for employees in nearly every job in
the modern enterprise. Integrated and accurate information about customers, products,
prices, inventories, and hundreds of other subjects has grown into an essential tool for
workers in offices, stores, and factories; for mobile employees in sales and service; for
partners; for suppliers; and for customers.
In general, executives find that, as long as access is managed properly to safeguard
confidential information, putting the correct information into the hands of each person in the
extended enterprise is a powerful way to enhance service, customer satisfaction, and
business performance. The idea behind BI is to take company historical and operational data,
process it with analytics software, and present the data in an easy-to-read and familiar format
to enterprise users. The resulting data empowers workers by aiding them in making better
business decisions to help improve the company’s bottom line.
Examples of BI include using the retail sales trends by geographical areas over the past few
years to decide which products go on the shelf, and using the results of various sales
promotions offered historically to decide on future promotions.
After a company decides to implement BI, the IT department must choose from the myriad of
Business intelligence tools that are available from many different vendors in the market. This
guide addresses the BI needs of the market with a cost-effective scale-up solution that
consists of IBM system x3950 M2 server with Intel Xeon Processor 7400 Series processors,
IBM System Storage™ DS5000 and Microsoft SQL Server BI components, specifically
Analysis Services.
IBM and Intel work closely with Microsoft to ensure that our products are optimized for SQL
Server 2008 deployments. This guide is the result of a joint collaboration between the
companies.
In this guide, we discuss the components used in the solution. We also include specific server
and storage configurations that are used to show improvements in SQL Server 2008 over
SQL Server 2005. We also describe the three reference configurations tested by IBM.
IBM System x Business Intelligence Solutions for Small-Medium Businesses with Microsoft SQL Server 2008
2

---

This guide includes the following topics:
IBM business intelligence solution architecture
SQL Server 2008 performance improvements
IBM BI reference configurations
IBM business intelligence solution architecture
Figure 1 shows a high-level overview of the IBM BI solution and how the components are
related. On the left side of the diagram, SQL Database Engine and Analysis Services are the
software components that execute on the x3950 M2 server hardware. On the right side of the
diagram, the Data Warehouse and Cube are the data repositories that reside on the DS5000
storage hardware.
IBM BI Reference Architecture
High performance
in-memory data
transfer
SQL
SQL
Database
Analysis
Data
Cube
Engine
Services
Warehouse
Process & Data Layer
Physical Layer
Fibre
Channel
Network
x3950 M2
DS5000
Server
Storage
Figure 1 IBM BI solution architecture
The following basic steps are involved in the cube building process using this reference
architecture:
1. Analysis Services requests data from the data warehouse using the database engine.
2. The database engine queries the data warehouse, which causes the data on the DS5000
storage to travel through the Fibre Channel network into the memory of the x3950 M2
server with Intel Xeon Processor 7400 series processors.
3. The database engine then passes that data directly to Analysis Services as an in-memory
data transfer.
4. Analysis Services aggregates and processes that data and stores the result in the Cube,
causing the data to travel back through the Fibre Channel Network to be stored on the
DS5000 storage.
IBM System x Business Intelligence Solutions for Small-Medium Businesses with Microsoft SQL Server 2008
3

---

We describe the key components in the architecture in the following sections.
IBM System x3950 M2 server
The IBM System x3950 M2 server is based on eX4, the fourth generation of the proven IBM
Enterprise X-Architecture® chipset. The eX4 chipset, which includes the memory controller,
provides the x3950 M2 server with the following features:
Ability to scale-up on processor and memory based on a modular “pay-as-you-grow”
design
High performance as demonstrated by leadership TPC benchmark results
Mainframe-like reliability in an x86 environment
The IBM System x3950 M2 includes the following key features:
True 2–to–16-socket scalability up to 96 cores
Superior performance with multi-core processors, Intel Xeon Processors 7400 Series and
16 MB L3 cache
Up to 1 TB of registered DIMM memory for better workload density and up to 30% less
power consumption than fully buffered DIMM technology
IBM Memory ProteXion with redundant bit-steering, which offers twice the memory
resilience of other systems
Fourth-generation snoop filter to ensure maximum efficiency across all processors
IBM Predictive Failure Analysis, on hard drives and memory as well as on processors,
power supplies, fans, and voltage regulator modules
Memory latency that is 40% lower than the nearest competition
Up to 32 registered DDR2 4 GB DIMMs are used in these configurations. These DIMMs offer
higher system performance at lower power consumption enabling faster speeds and higher
capacities needed for high-end enterprise servers like the x3950 M2.
IBM System Storage DS5000
The IBM System Storage DS5000 sets new standards for performance, scalability, reliability,
availability, and flexibility for midrange storage systems. The most powerful mid-range storage
system available from IBM, the DS5000 is the ideal platform for a database environment that
can keep pace with an organization’s business growth. Organizations can buy only the
capacity needed initially, and then dynamically upgrade and reconfigure additional capacity
and features later to meet changing business requirements, all without any system downtime.
The DS5000 delivers class-leading performance and is equally adept at supporting
transactional-applications, such as databases and OLTP; throughput-intensive applications,
such as HPC and rich media; and concurrent workloads, well-suited for consolidation and
database.
The DS5000 includes the following key features:
Flexible and unique host interface options, for example 4 or 8 Gbps Fibre Channel (FC)
and 10 Gbps iSCSI-ready interface cards that are designed to provide investment
protection and life cycle longevity to improve efficiency and to lower costs.
Field-replaceable host interface cards (HIC), two per controller. The current release
supports 4 and 8 Gbps FC HICs (16 total host ports).
Two performance and capacity models with ability to field-upgrade. The base model
DS5100 is field-upgradeable to high-end model DS5300.
IBM System x Business Intelligence Solutions for Small-Medium Businesses with Microsoft SQL Server 2008
4

---

“Pay-as-you-grow” scalability up to 256 drives for DS5100 and 448 drives for the DS5300
for the most demanding capacity requirements.
Up to 700,000 IOPs and 6,400 MBps.
Designed for a future upgrade up to 32 GB of dedicated data cache (16 GB per controller)
– Options include 4, 8, or future 16 GB of cache per controller
– Dedicated cache mirroring channels
– Persistent cache backup in the event of a power outage
– Field-upgradeable
Drive level encryption with no performance degradation. Innovative self-encrypting disk
solution for mid-market clients takes the worry out of exposing sensitive data on drives that
are returned for repair, retired, or repurposed with near zero performance impact.
End-to-end media encryption, with support for enterprise and mid-range disk and tape.
Architecture efficiently handles compute-intensive parity calculations, enabling exceptional
disk-based performance that is ideally suited for RAID 5 and RAID 6 configurations.
Support for RAID 6, 5, 3, 10, 1, and 0.
Remote Volume Mirroring and FlashCopy® premium features for Volume Shadow Copy
(VSS) supported backups and flexible DR scenarios.
Designed to support high availability with dual active, hot-swappable controllers, power
supplies and many nondisruptive firmware upgrades.
Microsoft SQL Server 2008
Microsoft SQL Server 2008 running on Microsoft Windows® Server 2008 enables
organizations to build comprehensive, enterprise-scale analytic solutions that deliver
intelligence where customers want it.
Key features include:
64-bit native DBMS
Dynamic large memory support
High Availability through Failover Clustering, Database Mirroring, Log Shipping, and
Replication
Database snapshots, a point in time, instantaneous read-only copy of a database, which
can be used for reporting and reverting back
Data compression for reduced on-disk space utilization, faster data retrieval, and faster
backups and restores
Policy-Based Management for managing SQL Server instances throughout the enterprise
All other components needed to provide a comprehensive, low-cost business intelligence
solution
SQL Server 2008 BI Components
SQL Server 2008 includes the SQL relational Database Engine, Analysis Services,
Integration Services, and Reporting Services. Together these components form a complete
data warehousing and business intelligence solution.
IBM System x Business Intelligence Solutions for Small-Medium Businesses with Microsoft SQL Server 2008
5

---

Key features include:
The Database Engine provides the services for the relational databases, which includes
the operational databases and the data warehouses.
Integration Services provides the means for extracting, transforming and loading (ETL) the
data from the operational databases into the data warehouses.
Analysis Services provides the means for creating, processing and serving OnLine
Analytical processing (OLAP) cubes.
Reporting Services provides a comprehensive reporting solution for SQL Server data
stored in relational databases and cubes.
Emulex 8Gb Fibre Channel host bus adapter
The Emulex® 8Gb Fibre Channel Dual-port HBA for IBM System x 3950 M2 host bus
adapters (HBAs) provide outstanding scalability, streamlined installation and management,
and industry-leading database support well-suited for small-to-large enterprises and
Microsoft Windows Server® 2008 and SQL Server 2008 FC storage area network (SAN)
environments.
With powerful management tools and broad System x support, the LightPulse family of
IBM-branded 4 Gbps and 8 Gbps HBAs (IBM Server Proven validation) delivers high
performance for a broad range of applications and environments. The Emulex LPE 12002
HBAs and the accompanying drivers have built-in support for Message Signaled Interrupts
(MSI-X) and NUMA I/O and take advantage of NUMA I/O improvements in Windows server
2008.
Key features include:
Exceptional performance and full-duplex data throughput
Comprehensive database capabilities with support for N-Port ID Database (NPIV)
Simplified installation and configuration using AutoPilot Installer®
Administration using HBAnyware® integrated with IBM Systems Director
SQL Server 2008 performance improvements
In this section, we show the server and storage configurations used to show improvements in
SQL Server 2008 over SQL Server 2005. Improvements were made in Analysis Services in
SQL Server 2008 to remove some of the single-threaded code and replace it with
multi-threaded code. This change allowed us to run more threads in parallel thus reducing
cube processing time.
Server configuration
Figure 2 shows the single-node, 16-core server configuration. The single-node IBM System
x3950 M2 server configured with Intel Xeon Processor 7400 Series used for these tests is
connected to the DS5000 storage controller with six direct connections from the three Emulex
HBAs to six ports on the controller.
There are seven PCIe slots on the x3950 M2 server. Slots 1, 3, and 5 are populated with the
Emulex dual-port adapters. The Microsoft multi-path bus driver is used to manage the
multiple paths. The DS3000/DS4000® DSM is used with the least queue depth policy. The
least queue depth policy compensates for uneven loads by distributing proportionately more
I/O requests to lightly loaded processing paths. Other policies available are failover only,
round robin, and weighted paths.
IBM System x Business Intelligence Solutions for Small-Medium Businesses with Microsoft SQL Server 2008
6

---

IBM x3950 M2 Server
• Four Intel Xeon quad-core X7350 processors
• 64 GB memory
• Three Emulex LPE 12002 M8 PCIe HBAs
Four Intel Xeon 2.93 GHz CPUs
CPU 1
CPU 2
CPU 3
CPU 4
IBM DS5000
• 1 controller
Scalability
• 208 disks 300GB 15K
links
IBM Hurricane 4
Memory
PCI-E
PCI-E
32
bridge
bridge
DIMMS
Figure 2 Server and storage configuration
Storage configuration
We attached 15 DS4000 EXP810 enclosures to the DS5300 dual-controller. Each EXP810
enclosure is populated with 16 300 GB 15,000 RPM drives for a total of 208 drives. The data
warehouse consisted of 14 months of data. The drives are used as follows:
Fourteen RAID-5 LUNs each of seven disks are designated to hold the 14 months of data.
Two miscellaneous LUNs are designated to hold the remaining data in the Data
Warehouse.
Six disks in each of 14 enclosures, for a total of 84 disks, are set up as one large RAID-10
LUN for the OLAP cube.
Four log LUNs are set up with four disks each.
One of the enclosures with 16 drives is set up as a RAID-5 LUN for archiving the backup of
the database.
Figure 3 shows the cabling between the DS5300, the EXP810 enclosures, and the x3950 M2
server. The cabling achieves the twin objectives of redundancy and balancing the traffic
across all the components.
Any single component (that is HBA, DS5300 controller, or cable) can fail and the configuration
will continue to operate correctly. In addition the traffic from the HBAs are divided equally
between the two DS5300 controllers and each controller can access all of the disk enclosures
with a maximum of two hops.
IBM System x Business Intelligence Solutions for Small-Medium Businesses with Microsoft SQL Server 2008
7

---

IBM BI solution x3950 M2 with DS5300 configuration
15 EXP810 enclosures
240 disks
Controller A
300 GB, 15K rpm
x3950 M2
DS5300
3 Emulex dual-port HBAs
16 cores, 64 GB
Controller B
Figure 3 Cabling diagram for the x3950 M2 server and DS5300 storage
IBM System x Business Intelligence Solutions for Small-Medium Businesses with Microsoft SQL Server 2008
8

---

Document Outline

• Go to the current abstract on ibm.com/redbooks
• • ÿ
  • ÿ
  • ÿ
  • ÿ
• ÿ

---