Reference 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.

Microsoft SQL Server Fast Track was created to provide specific reference architecture, along with a set of best practices, to eliminate efforts associated with designing a SQL Server data warehouse. Extensive research into balancing CPU/Memory/Disk Storage with respects to SMP was done to determine the best configuration for data warehousing (DW)-related workloads.

Fast Track is not a different version of SQL Server; instead it is a strict hardware configuration. Guidelines for loading data, minimizing fragmentation, and so on are also provided.

Fast Track provides a far more cost-effective approach to implementing larger Data Warehouses (<50 TB) in SMP SQL Server than if the clients had to design/manage the configuration themselves.

Best Practices

The following section provides some advice and references for best practices.

Case Studies and References

The following sample can be used for reference.

Questions and Considerations

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

  • Understand Client’s query mix or the characteristics of their queries to ensure that the Fast-track architecture applies. FT enjoys large-type queries versus non-clustered index queries.

  • Determine Client’s hardware vendor of choice and contact that vendor regarding their Fast Track hardware solutions.

  • Understand the client’s Data Warehouse Maturity Level and Strategy.

  • Understand data loading requirements (volumes, latency) and match them to recommended best practices for FT.

  • Determine data growth maintenance strategy:

    • Managing fragmentation

    • Monitoring/Managing indexes

    • Backup/Restore Policies

  • What is the query workload? Do most queries tend to perform large table-scans or are they more restricted to a few rows? This will determine partitioning/indexing strategies.

  • What is the concurrency workload? Can data be loaded on a quiesced system, or must loads run while queries are in flight?

  • Consider data growth and scale up/scale out requirements for the future. Ensure that the current design does not inhibit rapid data growth.

  • If data are loading concurrently with queries, what partition switching strategies should be used to minimize performance impact?


Following are the full URLs for the hyperlinked text:

1 An Introduction to Fast Track Data Warehouse Architectures

2 MS BI / Data Warehousing Hardware Estimation Tools

3 Estimating the Size of a Clustered Index

4 HP Fast Track Solutions for Microsoft SQL Server Track