Hp COMPAQ PROLIANT 8500 Scaling Out for Performance with Compaq ProLiant Servers and Microsoft SQL Server 2000
February 2001
Compaq Computer
Corporation
ISSG Technology
Communications
ONTENTS
C
Introduction........................3
The Compaq and Microsoft
Partnership......................... 3
Scaling Out Versus
Scaling Up ..........................3
Benchmarking Scale-Out
Architecture........................4
The DISA Architecture......... 5
Components of Scale-Out
Architecture........................6
Compaq ProLiant 8500
Servers................................6
Microsoft SQL Server 2000....7
ServerNet II.......................... 7
Scale-Out Database Design . 8
The Partition Column ............8
Data Locality........................9
Data Dependent Routing ....... 9
Control Over Query Text .....10
Further Design Constraints .. 10
Conclusion........................ 10
TC010201TB
T
ECHNOLOGY
.
.
.
.
.
Scaling Out for Performance
.
.
.
.
.
.
.
with Compaq ProLiant Servers
.
.
.
.
.
.
.
and Microsoft SQL Server 2000
.
.
.
.
.
.
.
.
.
.
Reliable and fast database access is the cornerstone for modern business applications that
.
.
.
.
manage information in e-commerce systems. As the use of the Internet and e-commerce
.
.
.
increases, organizations must implement systems that can be cost-effectively expanded to
.
.
.
meet rapid growth. Increasingly, traditional database solutions cannot meet the demand
.
.
.
for enormous increases in performance and the availability requirements of 24-hour e-
.
.
.
.
commerce environments.
.
.
.
.
Recent performance benchmarking results attained by Compaq using a multinode
.
.
.
.
configuration of Compaq ProLiant 8500 servers running Microsoft SQL Server 2000
.
.
.
confirm Compaq as a leading provider of cost-effective scale-out solutions for high-
.
.
.
performance database systems operating on industry-standard servers.
.
.
.
.
.
This technology brief describes high-performance database systems Compaq developed
.
.
.
to meet the scalability and availability requirements of today’s demanding environments.
.
.
.
.
It provides both technical and benchmarking data illustrating the technologies and
.
.
.
performance gains for high performance scale-out database servers. It also discusses the
.
.
.
applicability of scale-out database solutions to particular computing environments.
.
.
.
.
.
This brief is written with the assumption that the reader understands the basics of
.
.
.
database design.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Please direct comments regarding this communication to the ISSG Technology Communications Group
1
at TechCom@compaq.com
.
B
RIEF
TC010201TB
Scaling Out for Performance with Compaq
ECHNOLOGY BRIEF
T
.
.
.
OTICE
N
.
.
.
.
.
The information in this publication is subject to change without notice and is provided “AS IS”
.
.
.
WITHOUT WARRANTY OF ANY KIND. THE ENTIRE RISK ARISING OUT OF T HE USE
.
.
.
OF THIS INFORMATION REMAINS WITH RECIPIENT. IN NO EVENT SHALL COMPAQ
.
.
.
BE LIABLE FOR ANY DIRECT, CONSEQUENTIAL, INCIDENTAL, SPECIAL, PUNITIVE
.
.
.
OR OTHER DAMAGES WHATSOEVER (INCLUDING WITHOUT LIMITATION,
.
.
.
DAMAGES FOR LOSS OF BUSINESS PROFITS, BUSINESS INTERRUPTION OR LOSS OF
.
.
.
BUSINESS INFORMATION), EVEN IF COMPAQ HAS BEEN ADVISED OF THE
.
.
.
POSSIBILITY OF SUCH DAMAGES.
.
.
.
.
The limited warranties for Compaq products are exclusively set forth in the documentation
.
.
.
accompanying such products. Nothing herein should be construed as constituting a further or
.
.
.
additional warranty.
.
.
.
.
.
This publication does not constitute an endorsement of the product or products that were tested.
.
.
.
The configuration or configurations tested or described may or may not be the only available
.
.
.
solution. This test is not a determination of product quality or correctness, nor does it ensure
.
.
.
compliance with any federal state or local requirements.
.
.
.
.
Compaq, Compaq Insight Manager, LTE, ProLiant, and ROMPaq are registered with the United
.
.
.
States Patent and Trademark Office.
.
.
.
.
.
Microsoft, Windows, Windows NT, Windows NT Advanced Server, SQL Server for Windows NT
.
.
.
are trademarks and/or registered trademarks of Microsoft Corporation.
.
.
.
.
NetWare and Novell are registered trademarks and IntranetWare, NDS, and Novell Directory
.
.
.
Services are trademarks of Novell, Inc.
.
.
.
.
.
Pentium is a registered trademark of Intel Corporation.
.
.
.
.
Other product names mentioned herein may be trademarks and/or registered trademarks of their
.
.
.
respective companies.
.
.
.
.
.
.
.
.
.
©2001 Compaq Computer Corporation. All rights reserved. Printed in the U.S.A.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Scaling Out for Performance with Compaq ProLiant Servers
.
.
.
.
.
and Microsoft SQL Server 2000
.
.
.
First Edition (February 2001)
.
.
.
TC010201TB
.
.
.
.
.
.
.
.
.
2
ProLiant Servers and Microsoft SQL Server 2000
TC010201TB
Scaling Out for Performance with Compaq
ECHNOLOGY BRIEF
T
.
.
NTRODUCTION
I
.
.
.
.
.
The Internet and e-commerce have driven demands on database system performance and availability
.
.
.
beyond levels that could have been reasonably predicted ten or even five years ago. By removing
.
.
.
many of the traditional physical constraints on business commerce transactions, the Internet has
.
.
.
pushed the limits of peak performance for enterprise servers trying to meet intense e-commerce and
.
.
.
data processing needs. As a result, organizations must design thei r data processing systems to easily
.
.
.
accommodate exponential growth.
.
.
.
.
Cost-effective scalability is a critical component in planning and deploying database systems.
.
.
.
Previously, customers chose between scaling up for increased database performance and scaling out
.
.
.
for increased availability. Now, Compaq and Microsoft have developed a scale-out database
.
.
.
.
solution to increase both performance and availability.
.
.
.
.
This technology brief de fines scalability, distinguishes between scaling out and scaling up, and sets
.
.
.
some customer expectations for deploying superior and cost-effective scale-out database systems
.
.
.
designed in accordance with the Compaq Distributed Internet Server Array (DISA) architecture.
.
.
.
.
.
.
.
.
HE COMPAQ AND MICROSOFT PARTNERSHIP
T
.
.
.
.
Compaq’s business strategy includes partnering with other hardware and software vendo rs to meet
.
.
.
each customer’s specific needs with the best possible solutions. Compaq and Microsoft share a
.
.
.
strategic partnership that brings to market one of the most cost-effective scale-out database
.
.
.
solutions. This solution benefits directly from the unique relationship between Compaq technologies
.
.
.
and Microsoft database management products.
.
.
.
.
.
Results of multiple database benchmarking tests have demonstrated unprecedented performance of
.
.
.
this solution, which coordinates the management of large amounts of data partitioned across a group
.
.
.
of otherwise autonomous servers. Tests of multinode clusters of Compaq ProLiant 8500 servers and
.
.
.
Microsoft SQL Server 2000 confirm the advantages and applicability of this scale-out database
.
.
.
solution to real-world e-commerce businesses. The results confirm Compaq’s leadership in
.
.
.
delivering maximum performance with industry-standard server, storage, and interconnect
.
.
.
technologies.
.
.
.
.
.
.
.
CALING OUT VERSUS SCALING UP
S
.
.
.
.
.
Scalability is a system’s ability to maintain desired performance levels as the system grows, for
.
.
.
example, when a database system expands to accommodate increased traffic on an e-commerce
.
.
.
website. A database system can be scaled either up or out (Figure 1.) Servers are scaled up by the
.
.
.
addition of hardware such as processors, memory, or disk arrays to increase the maximum sustained
.
.
.
performance, while scaling out refers to adding servers to a cluster.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
3
ProLiant Servers and Microsoft SQL Server 2000
TC010201TB
y
Scaling Out for Performance with Compaq
ECHNOLOGY BRIEF
T
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Figure 1: Scaling up and scaling out
.
.
.
.
.
.
Scaling out improves performance by balancing the workload across multiple servers with multiple
.
.
.
processors, thereby increasing the overall availability of the database system. However, the benefits
.
.
.
derived by scaling out for performance do not apply to all environments. Scaling out large
.
.
.
configurations (more than 8 nodes) is an economical and practical approach to yield record-breaking
.
.
.
performance. Smaller configurations, on the other hand, typically will not exhibit performance gains
.
.
.
significant enough to outweigh increased management requirements and costs.
.
.
.
.
Additionally, database environments that do not easily partition into member tables along a partition
.
.
.
column and those that allow direct client access to data would not benefit from the scale-out
.
.
.
architecture described here. Also, the scope of client queries must also be limited. Frequent user-
.
.
.
defined, large-scope queries and query flexibility would detract from the performance derived in a
.
.
.
scale-out architecture by prolonging server response times.
.
.
.
.
.
.
.
.
ENCHMARKING SCALE-OUT ARCHITECTURE
B
.
.
.
.
Whether the overall need is for prepackaged systems or tailored solutions, Compaq ProLiant servers
.
.
.
provide top performance, scalability, failover, availability, and cost effectiveness for industry-
.
.
.
standard fault-tolerant database systems. A scale-out database architecture using ProLiant 8500
.
.
.
servers has repeatedly achieved new peaks of performance, effectively redefining industry
.
.
.
expectations for speed and cost effectiveness:
.
.
.
.
.
• On October 6, 2000, Compaq published a record-breaking TPC-C benchmark result of
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
4
505,302.77 tpmC and a price:performance of $20.68/tpmC. The benchmark ran using a 24-node
configuration of Compaq ProLiant 8500 servers, supporting 432,000 users with eight 700MHz/2-MB Pentium III Xeon processors per node, for a total of 192 processors. The total
system cost was $10,445,169. The availability date for this configuration was November 30,
2000.
• On July 25, 2000, Compaq published a TPC-C benchmark result of 262,243.60 tpmC and a
price:performance of $20.24/tpmC. This benchmark ran using a 12-node configuration of
ProLiant 8500 servers with a total of 96 processors and a total system cost of $5,305,571. The
availability date for this configuration was September 30, 2000.
• Also on July 25, 2000, Compaq published a TPC-C benchmark result of 179,658.00 tpmC and a
price:performance of $19.75/tpmC. This benchmark ran using an 8-node configuration of
ProLiant 8500 servers with a total of 64 processors and a total system cost of $3,546,582. The
availability date for this configuration was September 30, 2000.
Results of these benchmarks tests are summarized in Figure 2.
More CPUs
Faster CPUs
More memor
ProLiant Servers and Microsoft SQL Server 2000
SSccaallee--uupp
More Nodes
SSccaallee--oouutt
TC010201TB
Scaling Out for Performance with Compaq
ECHNOLOGY BRIEF
T
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Figure 2: TPC-C benchmark results for the Compaq ProLiant 8500 server
.
.
.
.
.
.
.
.
These benchmark tests implemented the DISA architecture with ProLiant servers interconnected with
.
.
.
ServerNet II full fabric 12-port switches supporting throughput of 125 MB/sec per connection. Other
.
.
.
important features in these configurations included a Compaq StorageWorks 4214R storage system,
.
.
.
Compaq Smart Array 5304 controllers, and Integrated SCSI RAID controllers.
.
.
.
.
.
Compaq industry-standard servers satisfy the need for affordable growth at high performance levels.
.
.
.
Compaq is the only system vendor with the demonstrated ability to deliver scale-out database
.
.
.
environments at consistent price points, giving customers the flexibility to start small and grow
.
.
.
quickly and cost-effectively as their businesses expand. For more information on current Compaq
.
.
.
TPC-C benchmar ks, refer to http://www.compaq.com/products/servers/benchmarks/index.html
.
.
.
.
.
.
.
HE
T
.
.
.
.
.
Compaq’s DISA architecture is key to achieving such record-breaking benchmark results, as it
.
.
.
establishes the structure of database system configurations. DISA is most applicable to a high traffic
.
.
.
business for which high performance and data availability for a large number of clients are critical.
.
.
.
.
DISA uses Internet Protocol (IP) load-balancing technologies that are essential to high-performance,
.
.
.
scale-out database enterprises. It emphasizes:
.
.
.
.
.
• The use of multiple application servers.
.
.
.
• Centralization of data resources on highly available servers.
.
.
.
.
• Redundancy at potential single points of failure.
.
.
.
.
.
As shown in Figure 3, DISA consists of a core application stack (clients, load balancing, application
.
.
.
servers, and data resources) and two global components (security and management.) Load balancing
.
.
.
and application servers, such as Microsoft Commerce Server 2000, help maintain a seamless
.
.
.
interface for customer transactions and present a single system image to clients. Client requests are
.
.
.
distributed across multiple application servers. The data resource layer stores and manages
.
.
.
application data across a cluster of server nodes.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
5
DISA A
ProLiant 8500 TPC-C Benchmarks
600,000
500,000
400,000
300,000
200,000
100,000
Performance (tpm/C)
RCHITECTURE
179,658
tpm/C
0
8-node 12-node 24-node
ProLiant Servers and Microsoft SQL Server 2000
$25.00
$20.00
$15.00
262,243
tpm/C
505,302
tpm/C
$10.00
$5.00
$0.00
(per tpm/C)
Price Performance
.
TC010201TB
Scaling Out for Performance with Compaq
ECHNOLOGY BRIEF
T
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Figure 3: DISA architecture
.
.
.
.
.
.
.
DISA architecture provides availability, scalability, and a level of management that extends the
.
.
.
capacity of a single multiprocessor server in hosting processor-intensive applications, while
.
.
.
accommodating hardware or software failures without significant downtime. More information about
.
.
.
Compaq DISA architecture is available at http://www.compaq.com/solutions/internet/disa.html
.
.
.
.
.
.
.
.
OMPONENTS OF SCALE-OUT ARCHITECTURE
C
.
.
.
.
Scalability is the cornerstone of system design based on DISA. Components that comprise the
.
.
.
multiple tiers of scale-out systems include Compaq ProLiant 8500 servers, Microsoft SQL Server
.
.
.
2000, and Compaq ServerNet II technology.
.
.
.
.
.
.
.
Compaq ProLiant 8500 Servers
.
.
.
.
Compaq’s scale-out solution is based on Compaq ProLiant 8500 servers, Microsoft’s own platform
.
.
.
of choice for Microsoft Datacenter independent software vendor (ISV) certification. ProLiant 8500
.
.
.
servers deliver outstanding performance and scalability, supporting up to eight Pentium III Xeon
.
.
.
processors. With new disk drive technology and a new integrated array controller for operating
.
.
.
system and boot support, ProLiant 8500 servers provide maximum external storage capacity. High-
.
.
.
availability features include PCI Hot Plug slots, re dundant hot-pluggable power supplies and fans,
.
.
.
redundant processor power modules, and redundant network interface controllers.
.
.
.
.
.
The superior performance of Compaq ProLiant 8500 servers owes much to the Profusion chipset
.
.
.
codeveloped by Compaq and Corollary. The Profusion chipset uses a five-point crossbar switch to
.
.
.
connect dual 100-MHz processor buses, dual 100-MHz memory buses, and a dedicated 100-MHz
.
.
.
I/O bus. This nonblocking design allows simultaneous read and write accesses from all five buses.
.
.
.
Compaq ProLiant 8500 servers, with support for up to 16 GB of two-way, cache-line interleaved
.
.
.
SDRAM, deliver superior perfo rmance running under Mic rosoft Windows NT Server, Micr osoft
.
.
.
Windows 2000, Novell NetWare, SCO UnixWare, and Red Hat and TurboLinux Server
.
.
.
distributions of Linux.
.
.
.
.
.
In addition to System Interconnect Status Indicators and front panel LEDs that help improve
.
.
.
serviceability and further reduce downtime, ProLiant 8500 servers include Compaq configuration
6
ProLiant Servers and Microsoft SQL Server 2000
.
TC010201TB
Scaling Out for Performance with Compaq
ECHNOLOGY BRIEF
T
.
.
and management tools such a s Smar tStart, Compaq Insight Manager, Remot e Insight Lights-Out
.
.
.
Edition, Rack Builder Pro, Info Messenger, and activeAnswers.
.
.
.
.
.
To further increase availability and reduce downtime, Compaq offers a unique range of hardware-
.
.
.
and software-based server management tools provided as standard on all ProLiant 8500 servers:
.
.
.
.
.
• Automatic server recovery following critical hardware or software errors.
.
.
.
• System partition utilities, which assist in the diagnosis, configuration, a nd upgrading of server
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
7
components.
• Compaq Insight Manager, which provides fault protection by monitoring more than 1,000
management parameter s .
• Asynchronous Insight Management, which provides remote dial-up access to Compaq Insight
Manager.
• Remote-Flash Redundant ROM, which allows administrators to upgrade system ROMs from
a single point of execution, locally or over a network, individually or in batches for multinode
clusters.
Microsoft SQL Server 2000
Microsoft SQL Server 2000 is an essential part of Compaq’s scale-out database architecture.
Compared to earlier releases, SQL Server 2000 improves exponentially upon the performance and
scalability of symmetric multiprocessing systems through significant optimization, speed, and cost
effectiveness.
SQL Server 2000 maintains high performance as processing loads grow by dividing the logical
workload among distributed partitions. It also provides unified views of member tables,
unprecedented XML support, relational database access via HTTP, and query optimizations for
support of SQL statements typical of websites.
SQL Server 2000 supports multiple instances of its database engine on a single server through the
use of a spare failover node. This significant new feature improves upon failover support by using
the spare node as a full processing partner in the server cluster to maintain failover readiness.
Connections from applications to multiple installations are all handled in the same manner, with
each instance able to use unique system and user databases. Multiple instances on each node manage
their own disk resources, thereby avoiding the contention that limits scalability in shared disk
clusters with common disk resources.
The performance improvements and ability to accommodate database processing growth
requirements make Microsoft SQL Server 2000 a cornerstone of a successful scale-out system. More
information is available at the Microsoft website at http://www.microsoft.com/sql
ServerNet II
Heterogeneous computing environments evolved around centralized systems using traditional
networking technologies such as Ethernet and TCP/IP. Traditional network technologies lack the
flexibility and performance to satisfy the intense communication needs of relational database
environments and e-commerce enterprises.
ServerNet II is the most complete industry-standard implementation of the Virtual Interface (VI)
Architecture specification, providing significantly higher performance than gigabit Ethernet and
TCP/IP in handling messaging traffic in a cluster. VI Architecture was codeveloped in 1997 by
Compaq and other industry leaders in response to the need for efficient, distributed server-to-server
messaging using common hardware and software interface standards. Operating system vendor
support for ServerNet II now includes Windows 2000 Data Center, Windows 2000 Advanced Data
Server, Linux, and SCO UnixWare 7.1.
ProLiant Servers and Microsoft SQL Server 2000
.
TC010201TB
Scaling Out for Performance with Compaq
ECHNOLOGY BRIEF
T
.
.
ServerNet II uses industry-standard 64-bit, 66-MHz PCI components that contain several features of
.
.
.
the VI Architecture to move data efficiently. The basic hardware components for a cluster include
.
.
.
.
ServerNet cables and ServerNet II PCI adapters. Multiple ServerNet II switches (Figure 4) facilitate
.
.
.
increased fault tolerance and aggregate bandwidth by supporting added nodes and data paths as
.
.
.
needed. ServerNet II hardware also provides high reliability through self-checking and hardware
.
.
.
routing protocol to ensure reliable data transfer.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Figure 4. Six-node cluster with redundant ServerNet II switches
.
.
.
.
.
.
.
.
The efficient CPU utilization, low latency, and high bandwidth of ServerNet II all enhance
.
.
.
messaging efficiency within heterogeneous cluster environments, making it the optimum
.
.
.
interconnect for scale-out database systems. For more information about ServerNet II, see the white
.
.
.
paper Compaq ServerNet II SAN Interconnect for Scalable Computing Clusters at
.
.
.
http://www.compaq.com/support/techpubs/whitepapers/tc000602wp.html
.
.
.
.
.
.
.
CALE-OUT DATABASE DESIGN
S
.
.
.
.
.
In accordance with DISA, a proper scale-out solution is run in a three-tier environment, consisting of
.
.
.
a client tier, an application tier, and a database server tier. The partitioning of the database is
.
.
.
transparent to client queries that access the data through the application tier, addressing the
.
.
.
distributed data as a single entity. This is accomplished using a partition column. While the partition
.
.
.
column is required in this solution, sc ale-out system design finds further enhancement through:
.
.
.
.
.
• Data locality.
.
.
.
• Data-dependent routing logic.
.
.
.
.
• Control over query text and scope.
.
.
.
.
.
.
.
The Partition Column
.
.
.
.
A database is divided into multiple member tables across server nodes, though each member table
.
.
.
must retain the format and column attributes of the original. The partition column within each
.
.
.
member table makes this distribution of data possible, while distributed partition views maintain the
.
.
.
seamless picture of the database member tables for client requests at the application layer. Figure 5
.
.
.
shows the relationship between nodes and the data owned by each.
.
.
.
.
.
8
ProLiant Servers and Microsoft SQL Server 2000
.
TC010201TB
Scaling Out for Performance with Compaq
ProLiant Servers and Microsoft SQL Server 2000
_KP
C
USTNAME
CustDate
_QZ
USTNAME
_AJ
C
USTNAME
CustDate
ECHNOLOGY BRIEF
T
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Figure 5: Member tables and the partition column
.
.
.
.
.
.
UNION statements make the partitioned tables across the servers appear as one, pulling all chosen
.
.
.
data into a single view by referencing the partition column common on each table. Each node
.
.
.
therefore has a single common logical view of the tables across all other nodes.
.
.
.
.
.
.
SELECT statements access data on other servers and merge with local resident data in response to
.
.
.
an SQL query. Linked server definitions on each member server help distribute queries to each of
.
.
.
the other member servers. Queries against the virtual table created by the distributed partitioned
.
.
.
view may then run on any of the member servers because the location of the actual data is
.
.
.
transparent to the query. A scale-out solution would be less applicable to data environments unable
.
.
.
to partition easily into member tables with common constraints.
.
.
.
.
.
.
.
Data Locality
.
.
.
.
A well-defined partition column is an integral part of the relationship model between member tables
.
.
.
partitioned upon it. Data locality implies that most of the data needed to process a particular query
.
.
.
resides on the node executing the query. This optimizes performance and use of server node
.
.
.
resources by placing data on the same node with any other data on which it is dependent. When a
.
.
.
query runs on a particular node, all the data needed to process it will also be located on that node.
.
.
.
.
.
.
.
Data Dependent Routing
.
.
.
.
.
Data dependent routing sees that queries are efficiently routed to the location of the data they need
.
.
.
to access. The application level acts as an abstraction layer (Figure 6), containing the routing logic
.
.
.
necessary for locating required data. The application tier is important in this architecture, as it
.
.
.
examines the text of a query submitted by a client and routes that query to the server node that can
.
.
.
most optimally process it, based upon the data being requested and the partition column used at the
.
.
.
server tier.
.
.
.
.
If the application tier did not act as an abstraction layer, the client could connect to a node that does
.
.
.
not contain the data being requested in the query, resulting in longer query response times. This
.
.
.
scale-out solution would therefore be less beneficial in environments without a proper application
.
.
.
tier, such as an environment in which clients access databases directly.
.
.
.
.
.
SQL Server 2000 further optimizes query performance by minimizing the amount of data
.
.
.
communicated between servers. To do this it uses a CHECK statement to define constraints and
.
.
.
confirm the value of member table keys. This prevents any overlap or duplication of data across
.
.
.
member tables and ensures the retrieval of only that data required by the SQL query.
.
.
.
.
.
9
SQL Server 1 SQL Server 2 SQL Server 3
C
CustDate
TC010201TB
Scaling Out for Performance with Compaq
ECHNOLOGY BRIEF
T
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Figure 6: Data-dependent routing
.
.
.
.
.
.
.
.
.
.
Control Over Query Text
.
.
.
.
Because the data is partitioned across the server nodes, a scale-out solution must include control over
.
.
.
the types of queries submitted. Queries requesting data should use the partition column to ensure the
.
.
.
proper query routing and optimal performance on the server nodes. A query that does not use the
.
.
.
partition column will use more resources on the server nodes, resulting in longer response times.
.
.
.
.
.
In a scale-out architecture, effective queries should limit the scope of the requested data. Large scope
.
.
.
queries, such as those in data warehouse or data mining environments, tend to require large amounts
.
.
.
of data from multiple nodes and lead to higher use of server node resources. Such environments do
.
.
.
not benefit from this type of scale-out architecture.
.
.
.
.
.
Likewise, for environments requiring frequent user-defined queries and query flexibility, a scale-out
.
.
.
solution as described here is not the optimal approach. Ad-hoc queries tend to perform poorly in a
.
.
.
scale-out environment. However, this limitation of scale-out solutions does not preclude flexibility in
.
.
.
the types of queries or user-defined queries.
.
.
.
.
.
.
Further Design Constraints
.
.
.
.
.
The architecture described here addresses the needs of an environment that must accommodate high
.
.
.
flows of transaction queries and provide immediate response for users. Further details about database
.
.
.
design constraints in scale-out architecture are available in documentation from Microsoft.
.
.
.
.
.
.
.
ONCLUSION
C
.
.
.
.
.
Multiple benchmarks have revealed the benefits of scale-out database design to database
.
.
.
environments requiring superior performance in response to ever increasing client traffic and
.
.
.
demand. The advantages of scale-out design are most applicable to particular environments, most
.
.
.
notably those designed in accordance with Compaq’s DISA architecture.
.
.
.
.
Scaling out leverages the high-performance and availability features of powerful ProLiant 8500
.
.
.
servers. Microsoft SQL Server 2000 helps promote scale-out architecture by way of its high-
.
.
.
performance code, memory utilization, and ability to scale-out as hardware scales out. Compaq and
.
.
.
Microsoft have realized the goal of a highly scalable distributed database solution that offers
.
.
.
.
superior performance for today’s demanding database environments.
.
.
.
.
.
.
.
10
Client Tier Application Tier
ProLiant Servers and Microsoft SQL Server 2000
Data Resources/
Server Tier
Loading...