MS SQL表分区实现数据归档的相关问题及最佳实践咨询

问题1：设置活跃数据分区仅备份该部分的可行性与合理性 #

• 方案完全可行，也适配你的业务场景。SQL Server 2016及以上版本支持文件组级别的部分备份，你可以把活跃数据分区放在独立的读写文件组，静态旧数据放在只读归档文件组，首次全量备份完成后，日常仅需备份读写文件组，只读文件组只要没有变更、完成一次全备后不需要重复备份。
• 合理性非常高：你的场景中90%以上的备份耗时都来自冷数据的重复备份，拆分后备份耗时直接和活跃数据量正相关，同时只读文件组还能减少锁竞争、提升查询性能，收益明确。唯一需要注意的是恢复时要确保归档文件组的备份可用，建议每3-6个月对归档文件组做一次校验性全备即可。

问题2：自动维护最近6个月为单个分区的实现方案 #

• 可以实现，不需要每日调整，按月调整分区边界即可满足需求。核心实现逻辑如下：
  1. 按日期列创建分区函数，设置边界值为DATEADD(month, -6, GETDATE())，小于该日期的数据归为归档分区，大于等于的归为活跃分区
  2. 用SQL Server代理作业按月执行分区拆分合并操作：每个月1号执行一次，把已经超出6个月范围的数据切换到归档分区，同时拆分新的边界值。操作前要提前预分配分区对应的文件组空间，避免拆分时IO阻塞。
• 不建议每日调整分区边界，过于频繁的合并拆分操作会产生不必要的IO开销，按月调整完全可以满足需求，性能损耗可以忽略。升级到SQL Server 2019后，还可以用自动分区剪裁特性进一步降低维护成本。

问题3：独立归档表与分区方案的优劣对比 #

没有绝对最优方案，根据业务查询场景选择即可：

• 如果绝大部分查询仅访问最近6个月的活跃数据，极少跨时间范围查询新旧数据：独立归档表方案更优。旧数据迁移到归档表后设置为只读，日常备份完全不需要包含归档表，查询时不需要做分区剪裁，性能更好，维护成本也更低。如果有偶尔的旧数据查询需求，用视图关联两张表即可满足。
• 如果经常有跨时间范围的查询需要同时访问新旧数据：表分区方案更优。分区对上层业务透明，不需要修改现有查询逻辑，只要按日期做分区剪裁就能获得不错的查询性能，维护成本比双表方案略高但可接受。

该场景的行业最佳实践建议 #

1. 优先做冷热数据分层：不管选分区还是独立归档表，第一步先把冷热数据拆分到不同的文件组，归档文件组设为只读，先解决备份耗时的核心痛点
2. 升级到SQL Server 2019后可以开启增量备份+文件组备份的组合策略，进一步缩短日常备份耗时，1.3TB的表如果活跃数据仅为200-300GB，日常备份耗时可以控制在2小时以内
3. 不管用哪种方案，都建议提前在测试环境做一次数据切换演练，确保没有业务逻辑兼容问题，同时验证备份恢复的可用性

内容的提问来源于stack exchange，提问作者SettusBlake