Partitioning policy of relational data warehouse in SQL Server (1)

We recommend that you read and understand the following articles:

◆ Use a partition in a Microsoft SQL Server 2000 data warehouse-http://msdn.microsoft.com/library/default.asp? URL =/library/techart/PartitionsInDW.htm
◆ SQL Server 2000 step by step batch import case study-http://www.microsoft.com/technet/prodtechnol/ SQL /2000/maintain/incbulkload.mspx
◆ Kimberly L. Tripp: SQL Server 2005 Partition Table and index-
Http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm

This White Paper focuses on Relational Data Warehouses and table partitions. Its target readers are:

◆ Partition view in Microsoft SQL Server is used to implement partition developers and database administrators. These readers will benefit from the partition advantages in SQL Server 2005 and the implementation and policy of sliding windows.
◆ Developers and database administrators who plan to use partitions in the future will not be able to benefit from reading this White Paper in detail.

The database and System Administrator will benefit from the storage domain network management and optimization of I/O utilization policies.

Content list

Partition a relational data warehouse 
About Relational Data Warehouse
Benefits of partitioning
Partitioning technology in SQL Server 7.0/2000
Partitioning in SQL Server 2005
Advantages of partitioning in SQL Server 2005
Identify the Demand Parallelism in a query plan
Migrate data from SQL Server 2000 partition view to SQL Server 2005 Partition Table/Index

Factors Affecting the partition of a relational data warehouse 
Data Volume
Data Import
Index
Data aging
Data Archiving
Query performance

Sliding Window implementation 
Best practices for exchanging partitions
Technology that stores data to a cost-effective I/O subsystem

Partition policy of a relational data warehouse 
Policy I-bind a partition to its own file group
Strategy II-bind two or more partitions to the same file group
Which policy is better?

Conclusion 
Appendix A: performance value 
Batch insert Performance
Conversion Performance
Index Building Performance
Database Backup Performance
Data aging to ATA Disk

Appendix B: Platform list 
Microsoft software
Server Platform
Storage
Host bus adapter
Storage Management Software

Appendix C: Server Architecture
Appendix D: EMC CLARiiON Storage 
Topology

Appendix E: storage isolation
Configure your storage

Appendix F: script 

Partition a relational data warehouse

The following sections briefly explain the concept of a relational data warehouse, the benefits of partitioning a relational data warehouse, and migration to Microsoft®SQL Server™2005 benefits of partitioning.

About Relational Data Warehouse

A relational data warehouse provides a wide range of data sources and an architecture for building business intelligence BI solutions. In addition, relational data warehouses can be used for report applications and complex and dedicated SQL queries.

A typical Relational Data Warehouse consists of dimension tables and fact tables. A dimension table is generally smaller than a fact table and provides detailed information about attributes that explain facts. An example of a dimension is goods, store, and time. Fact tables provide descriptions of business records, such as goods sales information in all stores. Fact tables are constantly updated through the recently collected data.

The implementation of a successful relational data warehouse solution includes meticulous and long-term planning. The following lists the elements to consider when building a relational data warehouse:

◆ Data volume
◆ Data import window
◆ Index maintenance window
◆ Workload Characteristics
◆ Data aging policy
◆ Archiving and backup policies
◆ Hardware features

This document will be followed by a detailed discussion of the above elements.

A relational data warehouse can adopt the partitioning method or a huge fact table method during implementation. The selection of whether to use partitions or not depends on the elements listed above. Relational Data Warehouses can benefit from data partitioning. The following sections focus on the benefits of partitioning for relational data warehouses.