Posts Tagged ‘FastTrack’

Fast Track Data Warehouse Reference Guide for SQL Server 2012

June 3, 2012

The newest batch of Microsoft White Papers was recently  released for SQL Server 2012. They should all be considered required reading. I know that I will read many of them several times.

Among the most important is the Fast Track Data Warehouse Reference Guide for SQL Server 2012The following comes from the article

Introduction

This document defines the component architecture and methodology for the SQL Server Fast Track Data Warehouse (FTDW) program. The result of this approach is the validation of a minimal Microsoft SQL Server database system architecture, including software and hardware, required to achieve and maintain a baseline level of out-of-box performance for many data warehousing workloads.

Audience

The target audience for this document consists of IT planners, architects, DBAs, and business intelligence (BI) users with an interest in choosing standard, proven system architectures for FTDW-conforming SQL Server workloads.

Fast Track Data Warehouse

The SQL Server Fast Track Data Warehouse initiative provides a basic methodology and concrete examples for the deployment of balanced hardware and database configuration for a data warehousing workload. For more information, see the FTDW Workload section of this document.

Balance is a measure of key system components of a SQL Server installation; storage, server, storage network, database, and operating system. Each of these components is tuned to optimal configuration. The goal is to achieve an efficient out-of-the-box balance between SQL Server data processing capability and hardware component resources. Ideally, your configuration will include minimum system hardware to satisfy storage and performance requirements for a data warehousing workload.

Fast Track

The SQL Server Fast Track brand identifies a component hardware configuration that conforms to the principles of the FTDW reference architecture (FTRA). Each FTRA is defined by a workload and a core set of configuration, validation, and database best practice guidelines. The following are key principles of the Fast Track program:

  • Workload-specific benchmarks. System design and configuration are based on real concurrent query workloads.
  • Detailed and validated hardware component specifications.
  • Component architecture balance between database capability and key hardware resources.

Value Proposition

The following principles create the foundation of the FTDW value proposition:

  • Predetermined balance across key system components. This minimizes the risk of overspending for CPU or storage resources that will never be realized at the application level.
  • Predictable out-of-the-box performance. Fast Track configurations are built to capacity that already matches the capabilities of the SQL Server application for a selected server and workload.
  • Workload-centric. Rather than being a one-size-fits-all approach to database configuration, the FTDW approach is aligned specifically with a data warehouse use case.

Methodology

Holistic Component Architecture

SQL Server FTDW reference architectures provide a practical framework for balancing the complex relationships between key components of database system architecture. Referred to generically as a stack, the component architecture is illustrated in Figure 1.

image

Figure 1: Example Fast Track database component architecture

Each component of the stack is a link in a chain of operations necessary to process data in SQL Server. Evaluating the stack as an integrated system enables benchmarking that establishes real bandwidth for each component. This ensures that individual components provide sufficient throughput to match the capabilities of the SQL Server application for the prescribed stack.

Workload Optimized Approach

Different database application workloads can require very different component architectures to achieve optimal resource balance. A classic example of this can be found in the contrast between small request, lookup-based online transaction processing (OLTP) workloads and scan-intensive, large-request, analytical data warehousing. OLTP use cases are heavily indexed to support low latency retrieval of small numbers of rows from data sets that often have little historical data volume. These types of database operations induce significant disk head movement and generate classic random I/O scan patterns. Analytical use cases, such as data warehousing, can involve much larger data requests and benefit greatly from the increased total throughput potential of sequential disk scans.

For these contrasting use cases, the implications for a balanced component stack are significant. Average, per-disk random I/O scan rates for modern SAS disk drives can be a factor of 10 times slower when compared to sequential scan rates for the same hardware. With Fast Track data warehousing workloads an emphasis is placed on achieving consistently high I/O scan rates (measured in MB/s) rather than the more traditional focus on operations per second (measured in IOPS).

The challenge of very different workloads is addressed by clearly defining the attributes of customer workloads. SQL Server Fast Track workloads comprise a qualitative list of attributes that uniquely define a common database application use case. In addition, each workload is represented by quantitative measures including standard benchmark queries. Workload-specific benchmarking is used to validate database configuration, best practices, and component hardware recommendations.

Validated SQL Server Fast Track Reference Configurations

All published Fast Track reference architectures are validated as conforming to the set of principles and guidelines provided in this reference guide. Examples of this process can be found in later sections of this document.

Advertisements

Microsoft Data Warehouse Offerings

October 6, 2011

James Serra has recently posted a lot of good information about Microsoft’s Data Warehouse offerings. He has also provided a lot of good links to more information.

MicrosoftSQL Server Reference Architecture and Appliances

MicrosoftSQL Server Parallel Data Warehouse (PDW) Explained

The article Implementing a Microsoft SQL Server Parallel Data Warehouse Using the Kimball Approach  is a great article to get a little deeper into Microsoft’s Parallel Data Warehouse as well.

This white paper explores how the Kimball approach to architecting and building data warehouse/business intelligence (DW/BI) system works with Microsoft’s Parallel Data Warehouse, and how you would incorporate this new product as the cornerstone of your DW/BI system. For readers who are not familiar with the Kimball approach, we begin with a brief overview of the approach and its key principles. We then explore the Parallel Data Warehouse (PDW) system architecture and discuss its alignment with the Kimball approach. In the last section, we identify key best practices and pitfalls to avoid when building or migrating a large data warehouse to a Microsoft SQL Server PDW system. This includes a look at how Parallel Data Warehouse can work with other Microsoft offerings such as SQL Server 2008 R2, the Microsoft Fast Track Reference Architecture for Data Warehouse, and the new Business Data Warehouse to provide a complete solution for enterprise-class data warehousing.

I have even proposed my own ideas to help with SSISETL Scalability

As a side note, I was recently was drawn into a discussion of Kimball vs. Inmon.  In my experience, most people who say they understand both really don’t. Here is a good article from Kimball which explains some of the differences.

The Kimball bus architecture and the Corporate
Information Factory: What are the fundamental differences?

Facts and Fables About Dimensional Modeling

Honestly, I believe that reading all of Kimball’s books should be a requirement before anyone who is called a Microsoft BI Professional.