数据仓库存储规划与数据库配置全解析

1. 存储容量估算

在基础设施建设中，磁盘空间的采购至关重要。完成数据库设计后，我们就可以对所需的存储容量进行估算了。具体步骤如下：
1. DDS 大小估算 ：通过计算事实表和维度表的大小来确定 DDS 的规模。以产品销售事实表为例，它包含 8 个 int 列、1 个 decimal 列、10 个 money 列和 3 个 datetime 列。根据各数据类型的字节数，可算出每行大小为 141 字节，考虑到未来可能添加的列，预留 50%的空间后变为 212 字节。若源系统显示平均每天有 500,000 笔销售，为应对增长设为 600,000 笔，计划采购两年的存储容量，那么该事实表的大小为 86GB。
2. NDS 大小估算 ：计算方法与 DDS 类似，先算出每行大小，再通过查询源系统估算行数。若有多个源系统，需考虑它们之间的重叠情况。例如，源系统 A 的产品表有 600,000 行，源系统 B 的产品表有 400,000 行，其中 250,000 行与源系统 A 重复，那么 NDS 产品表的行数为 750,000 行。
3. 阶段数据库大小估算 ：列出源系统映射中的所有源表，查询源系统确定每行大小和行数。若采用增量提取方法，统计每天插入或更新的行数；若采用“截断并重新加载”方法，则使用全量行数。同时，要考虑可能只从源系统表中获取部分列，以及为备份策略保留几天的数据。例如，销售订单项目表每天更新或插入 900,000 行，考虑两年增长调整为 110 万行，保留 5 天数据，阶段表大小为 5GB。
4. 元数据数据库大小估算 ：元数据数据库通常不大，约为 10 - 20GB，分配 50GB 足够。它存储七种元数据，包括数据定义和映射、数据结构、源系统、ETL 流程、数据质量、审计和使用元数据。
5. 总存储容量计算 ：假设 DDS 为 400GB，NDS 为 300GB，阶段数据库为 100GB，元数据数据库为 50GB，总计 850GB。考虑到索引和 RDBMS 开销，分别预留 30%和 15%的空间，得到 1233GB。再考虑 20%的估算容差，最终为 1479GB。

2. 存储相关术语

在继续之前，先明确一些数据存储中常用的术语：
- 磁盘 ：指单个 SCSI 驱动器，容量从 36GB 到 300GB 不等，转速从 10,000 RPM 到 15,000 RPM。有些人用“主轴”来指代磁盘。
- 卷 ：是根据特定 RAID 配置排列的多个磁盘。RAID（廉价磁盘冗余阵列）是一种将多个磁盘配置为一个单元的方法，通过创建物理数据冗余提高数据的可靠性。常见的 RAID 配置有 RAID 0、RAID 1、RAID 5 和 RAID 1 + 0（也称为 RAID 10）。例如，四个 146GB 的 SCSI 磁盘按 RAID 0 配置，卷容量为 584GB；按 RAID 5 配置，卷容量为 438GB；按 RAID 1 + 0 配置，卷容量为 146GB。

3. 磁盘配置规划

为了提高数据库的性能，我们需要合理规划磁盘配置：
- 数据文件分布 ：将每个数据库分散存储在多个物理卷上的多个数据文件中。尽量创建多个 RAID 5 卷（至少四个，理想情况下八个或更多），每个卷的大小在 300 - 500GB 之间，避免过小。
- 事务日志文件 ：将每个数据库的事务日志文件（LDF）放在不同的卷上，以提高数据加载性能。创建四个 RAID 1 卷用于存储事务日志，分别对应 DDS、NDS、阶段数据库和数据质量与元数据。
- TempDB ：每个 CPU 对应一个数据文件，大小可能为 10 - 20GB，具体取决于查询复杂度。理想情况下，将其放置在单独的 RAID 1 + 0 卷上，并设置较大的初始大小和自动增长功能。
- 备份卷 ：大小预计为数据磁盘的 1 - 2 倍，取决于备份策略，可位于 RAID 0 或 RAID 5 卷上。
- 文件系统卷 ：用于 ETL 临时存储，大小约为数据卷的 20% - 30%，采用 RAID 5 配置。同时要预留额外空间，因为它还可能存储其他文件。
- OLAP 卷 ：较难估算，在案例中可能是 DDS 的 2 - 5 倍，理想情况下分散在多个卷上。
- 仲裁卷 ：用于支持故障转移群集，采用 RAID 1 级别。它是群集中任何节点都可访问的驱动器，用于节点间的仲裁和存储恢复数据。

下面是一个磁盘空间计算的表格示例：
| 卷 | 所需大小 (GB) | RAID 级别 | 磁盘数量 | 实际大小 (GB) |
| — | — | — | — | — |
| 数据 1 - 6 | 1479 | 6 × RAID 5 | 6 × 4 | 6 × 399 |
| 日志 1 - 4 | 200 | 4 × RAID 1 | 4 × 2 | 4 × 133 |
| TempDB | 100 | RAID 1 + 0 | 4 × 1 | 133 |
| 仲裁 | 10 | RAID 1 | 2 × 1 | 133 |
| 备份 | 2200 | RAID 5 | 1 × 18 | 2261 |
| 文件系统 | 600 | RAID 5 | 1 × 6 | 665 |
| OLAP 1 - 4 | 1600 | RAID 5 | 4 × 5 | 4 × 532 |
| 总计 | - | - | 82 | 8246 |

4. 其他磁盘配置注意事项

• 热备用磁盘 ：需要 82 个磁盘，总可用空间为 8.2TB。机柜中有 85 个磁盘，其中 3 个作为 SAN 热备用磁盘。热备用磁盘用于自动替换故障磁盘，建议使用相同容量和 RPM 的磁盘。
• 磁盘选择 ：如果条件允许，使用大量小容量磁盘（如 73GB）比少量大容量磁盘（如 146GB）更好，因为可以将工作负载分散到更多磁盘上。同时，高转速（如 15,000 RPM）的磁盘性能更佳。
• 仲裁卷分区问题 ：不建议对 RAID 1 仲裁卷进行分区，应使用单独的小磁盘（如 36GB）专门用于仲裁。
• QA 环境 ：要为 QA 环境分配一定的磁盘空间，理想情况下与生产环境容量相同，以便进行测试和性能评估。

5. 数据库配置要点

完成数据库设计后，在 SQL Server 中创建数据库时，可参考以下要点，以 Amadeus Entertainment 案例为例：
1. 数据库命名 ：保持数据库名称简短，如 DDS、NDS、Stage 和 Meta，因为它们会作为 ETL 进程名和存储过程名的前缀。
2. 排序规则设置 ：确保所有数据仓库数据库的排序规则一致，最好遵循公司 SQL Server 安装标准。排序规则影响 SQL Server 处理字符和 Unicode 数据的方式，不一致可能导致数据转换问题。例如，使用 SQL_Latin1_General_CP1_CI_AS 可实现大小写不敏感。
3. 大小写敏感性 ：根据用户对排序顺序的要求仔细考虑大小写敏感性，不同设置会导致查询结果不同。
4. 文件组和文件布局 ：为每个数据库创建六个文件组，分布在六个不同的 RAID 5 物理磁盘上，并将事务日志文件放在指定的 RAID 1 日志磁盘上。通过管理工作室设置数据库默认位置，确保新数据库的数据和日志文件正确存放。
5. 数据文件初始大小和增长 ：根据预估的数据库大小设置数据文件的初始大小和增长增量。例如，DDS 初始大小设为 100GB，增长增量 25GB；NDS 初始大小 75GB，增长增量 15GB。将这些初始大小分配到六个文件中，增长增量设为初始大小的 20 - 25%，以减少 SQL Server 的自动增长操作。手动维护数据和日志文件大小，仅在紧急情况下使用自动增长。
6. 阶段数据库和元数据数据库设置 ：若阶段数据库每日预计加载 5 - 10GB 数据，保留五天数据，初始大小可设为 50GB，增长增量 10GB。元数据数据库预计一年 10GB，两年 20GB，初始大小设为 10GB，增长增量 5GB。
7. 日志文件大小 ：日志文件大小取决于每日加载量、恢复模式、加载方法和索引操作。DDS 和 NDS 的日志文件初始大小设为 1GB，增长增量 512MB；阶段数据库初始大小 2GB，增长增量 512MB；元数据数据库初始大小 100MB，增长增量 25MB。可通过 ETL 加载一天数据来估算所需日志空间。
8. 恢复模式选择 ：对于数据仓库的 DDS、NDS 和阶段数据库，选择简单恢复模式，因为数据更新由 ETL 进程控制，故障恢复可通过重新应用 ETL 数据实现，简单恢复模式可自动回收日志空间。若使用 ODS + DDS 架构，ODS 可考虑使用完整恢复模式，因为它可能被最终用户应用程序更新。元数据数据库也需设置为完整恢复模式，因为它会持续更新。
9. 其他配置选项 ：
- 最大并行度设为 0，让 SQL Server 利用所有可用处理器。
- 若不使用全文索引，应禁用，以节省系统内存。
- 禁用自动收缩功能，启用自动统计更新，特别是在 DDS 中，自动统计更新有助于 SQL Server 查询优化器选择最佳查询执行路径。

6. 创建 DDS 数据库示例

假设要创建 DDS 数据库，总数据文件大小为 100GB，由于有六个文件分布在六个不同物理磁盘上，每个文件初始分配 17GB。考虑到文件增长不均匀，将每个文件初始大小设为 30GB，最大大小可设为磁盘大小的 40%（如 150GB 或 170GB）。DDS 的增长增量为 25GB，平均到每个文件约 4.2GB，实际分配 5GB。以下是创建 DDS 数据库的脚本：

use master
go
if db_id ('DDS') is not null
drop database DDS;
go
create database DDS 
on primary (name = 'dds_fg1'
, filename = 'h:\disk\data1\dds_fg1.mdf'
, size = 30 GB, filegrowth = 5 GB)
, filegroup dds_fg2 (name = 'dds_fg2'
, filename = 'h:\disk\data2\dds_fg2.ndf'
, size = 30 GB, filegrowth = 5 GB)
, filegroup dds_fg3 (name = 'dds_fg3'
, filename = 'h:\disk\data3\dds_fg3.ndf'
, size = 30 GB, filegrowth = 5 GB)
, filegroup dds_fg4 (name = 'dds_fg4'
, filename = 'h:\disk\data4\dds_fg4.ndf'
, size = 30 GB, filegrowth = 5 GB)
, filegroup dds_fg5 (name = 'dds_fg5'
, filename = 'h:\disk\data5\dds_fg5.ndf'
, size = 30 GB, filegrowth = 5 GB)
, filegroup dds_fg6 (name = 'dds_fg6'
, filename = 'h:\disk\data6\dds_fg6.ndf'
, size = 30 GB, filegrowth = 5 GB)
log on (name = 'dds_log'
, filename = 'h:\disk\log1\dds_log.ldf'
, size = 1 GB, filegrowth = 512 MB)
collate SQL_Latin1_General_CP1_CI_AS
go
alter database DDS set recovery simple 
go
alter database DDS set auto_shrink off
go
alter database DDS set auto_create_statistics on
go
alter database DDS set auto_update_statistics on
go

在独立笔记本或桌面电脑上执行此脚本时，需将驱动器字母 h 替换为本地驱动器，如 c 或 d 。 h:\disk\data1 到 h:\disk\data6 和 h:\disk\log1 到 h:\disk\log4 是 SAN 中创建的六个数据卷和四个日志卷的挂载点，这些挂载点由操作系统用于识别 RAID 配置的卷。

7. 数据库配置流程总结

为了更清晰地展示在 SQL Server 中创建数据库的流程，下面用 mermaid 流程图来呈现：

graph LR
    A[开始] --> B[数据库命名]
    B --> C[设置排序规则]
    C --> D[考虑大小写敏感性]
    D --> E[规划文件组和文件布局]
    E --> F[设置数据文件初始大小和增长]
    F --> G[配置阶段数据库和元数据数据库]
    G --> H[确定日志文件大小]
    H --> I[选择恢复模式]
    I --> J[设置其他配置选项]
    J --> K[创建数据库脚本执行]
    K --> L[结束]

8. 数据库配置要点回顾与对比

为了方便大家对比和记忆各个数据库配置要点，下面整理了一个表格：
| 配置要点 | 具体说明 | 示例或注意事项 |
| — | — | — |
| 数据库命名 | 保持简短，作为 ETL 进程名和存储过程名前缀 | 如 DDS、NDS、Stage、Meta |
| 排序规则设置 | 所有数据仓库数据库排序规则一致 | 遵循公司 SQL Server 安装标准，如 SQL_Latin1_General_CP1_CI_AS |
| 大小写敏感性 | 根据用户排序要求设置 | 不同设置影响查询结果 |
| 文件组和文件布局 | 每个数据库六个文件组，分布在六个 RAID 5 物理磁盘，日志文件在 RAID 1 日志磁盘 | 通过管理工作室设置数据库默认位置 |
| 数据文件初始大小和增长 | 根据预估数据库大小设置，增长增量 20 - 25%初始大小，手动维护 | DDS 初始 100GB，增长 25GB；NDS 初始 75GB，增长 15GB |
| 阶段数据库和元数据数据库设置 | 根据每日加载量和预计大小设置 | 阶段数据库初始 50GB，增长 10GB；元数据数据库初始 10GB，增长 5GB |
| 日志文件大小 | 取决于每日加载量、恢复模式等 | DDS 和 NDS 初始 1GB，增长 512MB；阶段数据库初始 2GB，增长 512MB；元数据数据库初始 100MB，增长 25MB |
| 恢复模式选择 | DDS、NDS 和阶段数据库选简单恢复模式；ODS 和元数据数据库选完整恢复模式 | 简单恢复模式自动回收日志空间 |
| 其他配置选项 | 最大并行度 0；禁用全文索引；禁用自动收缩，启用自动统计更新 | 特别是在 DDS 中，自动统计更新有助于查询优化 |

9. 磁盘配置与数据库配置的关联

磁盘配置和数据库配置是相互关联的，下面通过一个列表来展示它们之间的联系：
1. 数据文件分布与磁盘卷 ：数据库的数据文件分布在多个物理卷上，磁盘卷的 RAID 配置和大小影响数据的存储和性能。例如，将 DDS 数据库的数据文件分散在多个 RAID 5 卷上，可提高数据加载和查询性能。
2. 日志文件与日志卷 ：数据库的日志文件存放在单独的日志卷上，不同的日志卷配置（如 RAID 1）有助于提高数据加载性能，同时日志文件的大小和增长设置也与磁盘空间的使用相关。
3. TempDB 与磁盘卷 ：TempDB 放置在单独的 RAID 1 + 0 卷上，其大小和自动增长设置依赖于磁盘性能和查询复杂度，合适的磁盘配置能确保 TempDB 高效运行。
4. 备份卷与数据磁盘 ：备份卷的大小取决于数据磁盘的大小和备份策略，合理的磁盘配置能保证数据备份的可靠性和高效性。

10. 总结与建议

在进行数据仓库的存储规划和数据库配置时，需要综合考虑多个因素，包括存储容量估算、磁盘配置、数据库配置等。以下是一些总结和建议：
1. 存储容量估算 ：准确估算各数据库的存储容量是基础，要考虑到未来的增长和各种可能的情况，如源系统数据的变化、数据备份策略等。
2. 磁盘配置 ：合理选择磁盘类型、RAID 配置和磁盘数量，将工作负载分散到多个磁盘上，以提高性能。同时，要预留足够的热备用磁盘和空间用于 QA 环境。
3. 数据库配置 ：在 SQL Server 中创建数据库时，遵循数据库命名、排序规则、文件布局等要点，确保数据库的高效运行。特别是要注意恢复模式的选择和自动统计更新等配置选项。
4. 持续监控与维护 ：创建数据库后，要定期监控数据和日志文件的使用情况，手动调整文件大小，避免过度依赖自动增长功能，以防止性能问题。

通过以上的存储规划和数据库配置方法，可以构建一个高效、可靠的数据仓库环境，满足业务对数据存储和处理的需求。希望这些内容能帮助你更好地进行数据仓库的建设和管理。