Microsoft Official Course: Planning SQL Server Business Intelligence Infrastructure

Microsoft Official Course
Module 2
Planning SQL Server Business

Intelligence Infrastructure
Module Overview
Considerations for BI Infrastructure

• Planning Data Warehouse Hardware
Lesson 1: Considerations for BI Infrastructure
Infrastructure Planning in a BI Project

System Sizing Considerations
BI Workloads
Typical Server Topologies for a BI Solution
Scaling Out a BI Solution
• Planning for High Availability
Infrastructure Planning in a BI Project
Business Requirements
Technical
Data
Architecture Reporting and
Warehouse
and Analysis
and ETL
Infrastructure Design
Design
Design
Monitoring and Optimizing
Operations and Maintenance

System Sizing Considerations
Data Volume Analysis/Report Complexity
Number of Users Availability Requirements

BI Workloads
ETL
• Control flow tasks
Data Models • Data query and insert
• Processing • Network data transfer
• Aggregation storage • In-memory data pipeline
• Multidimensional on disk • SSIS Catalog or MSDB I/O
• Tabular in memory
• Query execution
Operations and
DW Maintenance
• OS activity
Reporting • Logging
• SQL Server Agent Jobs
• Client requests • SSIS packages
• Data source queries • Indexes
• Report rendering • Backups
• Caching
• Snapshot execution
• Subscription processing
• Report Server Catalog I/O
Typical Server Topologies for a BI Solution
DW
Single Server Architecture

Distributed Architecture
Few Servers Many
Hardware costs
Software license costs
Configuration complexity
Scalability & Performance
Flexibility
Scaling Out a BI Solution
Data Warehouse Analysis Services
Integration Services Reporting Services

Planning for High Availability
Data Warehouse Analysis Services

• AlwaysOn Failover Cluster • AlwaysOn Failover Cluster
• RAID Storage
Reporting Services
Integration Services • NLB Report Servers
• AlwaysOn Availability Group • AlwaysOn Availability Group
Or
• AlwaysOn Failover Cluster
Lesson 2: Planning Data Warehouse Hardware
SQL Server Fast Track Reference Architectures

Core-Balanced System Architecture
Demonstration: Calculating Maximum
Consumption Rate
Determining Processor and Memory Requirements
Determining Storage Requirements
• Considerations for Storage Hardware
SQL Server Fast Track Reference Architectures
• Pre-tested and approved

hardware specifications and
guidance
• Available from multiple
hardware vendors in
partnership with Microsoft
• Support for a range of data
warehouse sizes
• Tools provided to calculate
required specification
Core-Balanced System Architecture
Per-Core MCR = 200 MB/s 2 x FC Port per processor

Total MCR = 1600 MB/s Max I/O Rate = 2000 MB/s
Server Storage Enclosure

..
Storage
Processors
4-Spindle RAID 10 Disk Groups

SQL Server
Fiber Switch
Storage Enclosure
Storage
Windows Server Processors
4-Spindle RAID 10 Disk Groups

Quad
Dual Port
Core
FC HBA
CPU
Quad
Storage Enclosure
Dual Port
Core FC HBA
CPU Storage
Processors
Dual Port
FC HBA 4-Spindle RAID 10 Disk Groups
Max I/O Rate = 2000 MB/s Max I/O Rate = 1800 MB/s
Demonstration: Calculating Maximum
Consumption Rate
In this demonstration, you will see how to:

• Create tables for benchmark queries
• Execute a query to retrieve I/O statistics
• Calculate MCR from the I/O statistics
Determining Processor and Memory
Requirements
Estimating CPU Requirements:

• Determine core MCR
• Apply formula to estimate required
number of cores:
((Average query size in MB/ MCR) x Concurrent users) / Target response time
• Spread cores across CPUs based on the

number of storage arrays
Estimating RAM Requirements:

• Use a minimum of 4 GB per core
(or 64 - 128 GB per socket)
• Target 20% of data volume
Determining Storage Requirements
Data Warehouse
• Determine initial data volume
• Number of fact table rows x row size
• Use 100 bytes per row as an estimate if unknown
• Add 30-40% for dimensions and indexes
• Project data growth
• Number of new fact rows per month
• Factor in compression
• Typically 3:1
Other storage requirements
• Configuration databases
• Log files
• TempDB
• Staging tables
• Backups
• Analysis Services models
Considerations for Storage Hardware
Use more smaller disks instead of fewer larger disks
• Use the fastest disks you can afford

• Consider solid state disks―especially for random I/O
• Use RAID 10, or minimally RAID 5

• Consider a dedicated storage area network for
manageability and extensibility
• Balance I/O across enclosures, storage processors, and disk
groups
Lab: Planning SQL Server Business Intelligence
Infrastructure
Exercise 1: Planning Server Topology

• Exercise 2: Planning Data Warehouse Hardware
Logon Information
Start 20467B-MIA-DC and 20467B-MIA-SQLBI, and then log on to
20467B-MIA-SQLBI as ADVENTUREWORKS\Student with the password Pa$$w0rd.
Estimated Time:60 Minutes.

Lab Scenario
You are planning a BI solution for Adventure Works

Cycles, and have been asked to specify the
hardware required for the solution. You have
interviewed business stakeholders in order to
understand the business requirements that the
solution must support, and now you must design a
SQL Server-based solution that provides the right
balance of functionality, performance, and cost.
At this time, you have been informed that you

should not consider using SharePoint Server in your
planned solution.
Lab Review
Review BI Topology.vsdx in the D:\Labfiles\Lab02\

Solution folder. How do the various suggested
topologies (on multiple pages named A to F)
compare to the topology you designed in the lab?
• Review DW Hardware Spec.xlsx in the D:\Labfiles\
Lab02\Solution folder. How does the hardware
specification in this workbook compare to the
specification you created in the lab?
Module Review and Takeaways
• In a growing number of organizations,

virtualization has become a core platform for
infrastructure. Hyper-V in Windows Server 2012
together with enterprise operations and
management software such as Microsoft System
Center 2012 has enabled IT departments to
benefit from simpler provisioning, management,
mobility, and recoverability of services.
• What components of a BI infrastructure would you
consider virtualizing, and why?

Microsoft Official Course: Planning SQL Server Business Intelligence Infrastructure

Uploaded by

Document Informationclick to expand document information

Document Informationclick to expand document information

Copyright:

Available Formats

Microsoft Official Course: Planning SQL Server Business Intelligence Infrastructure

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Microsoft Official Course: Planning SQL Server Business Intelligence Infrastructure

Uploaded by

Copyright:

Available Formats

Microsoft Official Course

Planning SQL Server Business

Considerations for BI Infrastructure

Infrastructure Planning in a BI Project

Monitoring and Optimizing

Operations and Maintenance

Data Volume Analysis/Report Complexity

Number of Users Availability Requirements

Single Server Architecture

Few Servers Many

Data Warehouse Analysis Services

Integration Services Reporting Services

Data Warehouse Analysis Services

SQL Server Fast Track Reference Architectures

• Pre-tested and approved

Per-Core MCR = 200 MB/s 2 x FC Port per processor

Server Storage Enclosure

4-Spindle RAID 10 Disk Groups

4-Spindle RAID 10 Disk Groups

In this demonstration, you will see how to:

Estimating CPU Requirements:

• Spread cores across CPUs based on the

Estimating RAM Requirements:

Use more smaller disks instead of fewer larger disks

• Use the fastest disks you can afford

• Use RAID 10, or minimally RAID 5

Exercise 1: Planning Server Topology

Estimated Time:60 Minutes.

You are planning a BI solution for Adventure Works

At this time, you have been informed that you

Review BI Topology.vsdx in the D:\Labfiles\Lab02\

• In a growing number of organizations,

You might also like