Symmetric Multi-Processing (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions

  • Article

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.

Case Studies and References

Industry standards compliance examples and implementation guidelines include the following:

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

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 https://technet.microsoft.com/en-us/library/cc917690.aspx

5 Disk Partition Alignment Best Practices for SQL Server https://technet.microsoft.com/en-us/library/dd758814%28SQL.100%29.aspx

6 SQLIO Disk Subsystem Benchmark Tool https://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 https://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 https://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 https://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 https://technet.microsoft.com/en-us/library/dd459178(SQL.100).aspx