Symmetric Multi-Processing (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. |
Symmetric multi-processing (SMP) data warehousing (DW) is the most common architecture for data warehouses under 50 TB. These systems are characterized by a single instance of a RDBMS sharing all resources (CPU/Memory/Disk).
All major RDBMS vendors (IBM/Oracle/Sybase/Microsoft) provide SMP versions of their RDBMS for Data Warehouse.
As SMP-based DW implementations grow in terms of data size and query efficiency, it becomes more and more challenging to design and maintain efficient data warehouse infrastructure.
Best Practices
The following resources provide some general information and best practices.
-
Top 10 Best Practices for Building a Large Scale Relational Data Warehouse
1
-
SMP or MPP for Data Warehousing
2
-
Building a Data Warehouse: With Examples in SQL Server
3
-
Top 10 SQL Server 2005 Performance Issues for Data Warehouse and Reporting Applications
4
-
Disk Partition Alignment Best Practices for SQL Server
5
-
SQLIO Disk Subsystem Benchmark Tool
6
Case Studies and References
Industry standards compliance examples and implementation guidelines include the following:
-
Barnes and Noble, Inc.: Bookseller Gains Business Insight Across Sales Channels with New Data Warehouse
7
-
Hilton Hotels: Hilton Sets the Table for Increases in Catering Revenue with New Database Solution
8
-
Stein Mart: Department Store Chain Speeds Reporting, Saves $600,000 a Year in Technology Costs
9
-
Fast Track Data Warehouse 2.0 Architecture
10
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
-
System Sizing Considerations
Appendix
Following are the full URLs for the hyperlinked text.
1 Top 10 Best Practices for Building a Large Scale Relational Data Warehouse http://sqlcat.com/top10lists/archive/2008/02/06/top-10-best-practices-for-building-a-large-scale-relational-data-warehouse.aspx
2 SMP or MPP for Data Warehousing http://www.information-management.com/issues/20020501/5129-1.html
3 Building a Data Warehouse: With Examples in SQL Server http://books.google.com/books?id=eBacaL61sa4C&pg=PA43&lpg=PA43&dq=smp+data+warehouse&source=bl&ots=MFC5NhpPRr&sig=kEFbpu7nGWZvnM8xvib14L_sFBs&hl=en&ei=Ul0BTbjWE4bBswbwsJ3nBA&sa=X&oi=book_result&ct=result&resnum=6&ved=0CEIQ6AEwBTgK#v=onepage&q=smp%20data%20warehouse&f=false
4 Top 10 SQL Server 2005 Performance Issues for Data Warehouse and Reporting Applications http://technet.microsoft.com/en-us/library/cc917690.aspx
5 Disk Partition Alignment Best Practices for SQL Server http://technet.microsoft.com/en-us/library/dd758814%28SQL.100%29.aspx
6 SQLIO Disk Subsystem Benchmark Tool http://www.microsoft.com/downloads/en/details.aspx?familyid=9A8B005B-84E4-4F24-8D65-CB53442D9E19&displaylang=en
7 Barnes and Noble, Inc.: Bookseller Gains Business Insight Across Sales Channels with New Data Warehouse http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=48839
8 Hilton Hotels: Hilton Sets the Table for Increases in Catering Revenue with New Database Solution http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=49192
9 Stein Mart: Department Store Chain Speeds Reporting, Saves $600,000 a Year in Technology Costs http://www.microsoft.com/casestudies/Microsoft-Office-SharePoint-Server-2007/Stein-Mart/Department-Store-Chain-Speeds-Reporting-Saves-600-000-a-Year-in-Technology-Costs/4000007013
10 Fast Track Data Warehouse 2.0 Architecture http://technet.microsoft.com/en-us/library/dd459178(SQL.100).aspx