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.
An Introduction to Fast Track Data Warehouse Architectures 1
The Fast Track reference architecture (RA) is VERY SPECIFIC and must be followed to the letter. Be wary of anybody trying to second-guess the RA. If changes of any kind are made (for example, different drives, more CPU’s, and so on), performance will be impacted.
The FT architecture is designed to provide sequential I/O as much as possible; therefore, indexing should be used very judiciously to avoid introducing too much random I/O.
Case Studies and References
The following sample can be used for reference.
- HP Fast Track architectures and customer examples: HP Fast Track Solutions for Microsoft SQL Server4
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?
Appendix
Following are the full URLs for the hyperlinked text:
1 An Introduction to Fast Track Data Warehouse Architectures https://msdn.microsoft.com/en-us/library/dd459146%28SQL.100%29.aspx
2 MS BI / Data Warehousing Hardware Estimation Tools http://siddhumehta.blogspot.com/2010/07/ms-bi-data-warehousing-hardware.html
3 Estimating the Size of a Clustered Index http://msdn.microsoft.com/en-us/library/ms178085.aspx
4 HP Fast Track Solutions for Microsoft SQL Server http://h71028.www7.hp.com/enterprise/cache/503252-0-0-0-121.html?jumpid=ex_r2858_w1/en/large/tsg/solutions_microsoft_Fast Track