Scale-Out Querying with Analysis Services Using SAN Snapshots

This SQL Server Best Practices white paper describes the use of virtual copy SAN snapshots in a load-balanced scalable querying environment for Microsoft SQL Server 2005 Analysis Services to increase the number of concurrent queries to Analysis Services servers with a rapid response time per query. Load-balanced querying distributes the load of all queries among the available servers. The use of the virtual copy SAN snapshots eliminates the need to copy data between an Analysis Services processing server and one or more dedicated Analysis Services query servers. This paper supplements the Scale-Out Querying with Analysis Services white paper.

Table of Contents

Introduction
Architecture Overview
Test Objective
Test Platform
 Servers
 SAN
 Client
 Network Load Balancer
Configuring the 3PAR Virtual Copy Snapshots
Analysis Services Workload and Test Harness
 SQL Server Relational Data Warehouse Database
 Analysis Services database
 Query Workload
 Query Execution Harness
 Processing Workload
Test Methodology
 Baseline Query Test
 Baseline Query and Processing Test
 Scale-Out Test 1
 Scale-Out Test 2
 Scale-Out Test 3
 Scale-Out Test 4
 Scale-Out and Processing Test
Test Results and Observations
 Results
 Observations
Snapshot Considerations
Hardware Vendor Links
Conclusion

Read more...