Custom Architecture (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Customer Microsoft SQL Server architectures for data warehousing (DW) cover a full range of configurations. Often, the configuration is predicated by the "deal" that can be made between the client and the HW Vendor, and not always on carefully analyzed, business-driven, balanced design based on DW workloads.

Best Practices

Case Studies and References

This section provides questions and issues to consider when working with your customers.

  • Determine the customer’s hardware vendor of choice.

  • Understand the customer’s data center status:

    • Availability to accommodate special HW for Data Warehouse.

    • Will the DW have to share a SAN or can it use dedicated storage?

  • Determine all connectivity requirements (e.g. connection managers, OLEDB, ODBC, JDBC, and so on).

  • Understand source database design (Star Schema, normalized).

  • Understand if the client will rely on Data Extract (e.g. BCP) versus ETL/ELT (e.g. SSIS).

  • For BCP Extracts, determine where data will land:

    • Storage platform and architecture.

    • Can extracts be multi-threaded?

  • Determine ETL/ELT requirements with regards to:

    • Data volume.

    • Data “latency” or timing (how frequently does data need to be loaded.)

    • Transformation complexity (from simple table lookups to complex business-oriented /data enrichment transforms).

  • Determine geographical proximity of SQL Server source and target systems.

  • Understand characteristics of the target (is it another SQL Server instance, or a flat file, or another RDBMS or analytical platform (e.g. SSAS)?).

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

  • Understand the DW workload in terms of data loading and query workloads.

  • Determine data latency requirements in conjunction with query/load concurrency.

  • Workload requirements may vary by user/time of day/ query type and must be governed to prevent exhausting the system for a single query.

  • Understand data security (row-level, column-level) requirements.

  • Understand High Availability/Disaster Recovery requirements.

  • Determine database backup/restore requirements in terms of frequency/storage.

  • Develop an ongoing maintenance strategy for performance monitoring and tuning.

  • Determine the technology landscape in terms of legacy RDBMS systems, ETL tools, BI tools.

  • Determine deployment strategy:

    • System Sizing Considerations

    • System Configuration

    • Software Installation/Configuration

    • Physical DW Database Design

    • Metadata Management

    • Designing the ETL system

    • Developing BI Applications

    • Developing the security model

    • Operations Management

    • Managing Growth


Following are the full URLs for the hyperlinked text:

1 Hardware 101 for SQL Server DBAs

2 Solid State Disk Drive Considerations for SQL Server

3 Benchmarks

4 TPC-H - All Results - Sorted by Database Vendor Version 2 Results

5 Storage Tope 10 Best Practices

6 Top 10 SQL Server 2005 Performance Issues for Data Warehouse and Reporting Applications

7 Disk Partition Alignment Best Practices for SQL Server

8 SQLIO Disk Subsystem Benchmark Tool

9 Strategies for Partitioning Relational Data Warehouses in Microsoft SQL Server

10 Best Practices for Data Warehousing with SQL Server 2008

11 High Performance Data Warehouse with SQL Server 2005

12 SQL Server 2008 hardware and software requirements,295582,sid99_gci1372853,00.html

13 EMC Celerra Unified Storage Platforms: Reference Architecture