什么是存档除当年之外的所有内容并同时对表进行分区的最佳方法

任务

从一组大表中归档除滚动 13 个月之外的所有时间段。归档数据必须存储在另一个数据库中。

• 数据库处于简单恢复模式
• 这些表有 5000 万行到数十亿行，在某些情况下每个占用数百 GB。
• 表当前未分区
• 每个表在不断增加的日期列上都有一个聚集索引
• 每个表还有一个非聚集索引
• 对表的所有数据更改都是插入
• 目标是最大程度地减少主数据库的停机时间。
• 服务器是 2008 R2 Enterprise

“存档”表将有大约 11 亿行，“实时”表大约有 4 亿行。显然存档表会随着时间的推移而增加，但我希望实时表也会迅速增加。至少在未来几年内达到 50%。

我曾考虑过 Azure 延伸数据库，但不幸的是，我们处于 2008 R2 并且可能会在那里停留一段时间。

当前计划

• 创建一个新数据库
• 在新数据库中创建按月分区的新表（使用修改后的日期）。
• 将最近 12-13 个月的数据移动到分区表中。
• 对两个数据库进行重命名交换
• 从现在的“存档”数据库中删除移动的数据。
• 对“归档”数据库中的每个表进行分区。
• 将来使用分区交换来归档数据。
  • 我确实意识到我必须换出要存档的数据，将该表复制到存档数据库，然后将其换入存档表。这是可以接受的。

问题： 我正在尝试将数据移动到初始分区表中（实际上我仍在对其进行概念验证）。我正在尝试使用 TF 610（根据Data Loading Performance Guide）和一个INSERT...SELECT声明来移动数据，最初认为它会被最小化记录。不幸的是，每次我尝试它都已完全记录。

在这一点上，我认为我最好的选择可能是使用 SSIS 包移动数据。我试图避免这种情况，因为我正在处理 200 个表以及我可以通过脚本执行的任何操作，我可以轻松地生成和运行。

我的总体计划中是否遗漏了什么，SSIS 是我快速移动数据并最大限度地减少日志使用（空间问题）的最佳选择吗？

没有数据的演示代码

-- Existing structure
USE [Audit]
GO

CREATE TABLE [dbo].[AuditTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
); 
-- ~1.4 bill rows, ~20% in the last year

CREATE CLUSTERED INDEX [AuditTable_Modified] ON [dbo].[AuditTable]
(   [Modified] ASC   )
GO


-- New DB & Code
USE Audit_New
GO

CREATE PARTITION FUNCTION ThirteenMonthPartFunction (datetime)
AS RANGE RIGHT FOR VALUES ('20150701', '20150801', '20150901', '20151001', '20151101', '20151201', 
                            '20160101', '20160201', '20160301', '20160401', '20160501', '20160601', 
                            '20160701') 

CREATE PARTITION SCHEME ThirteenMonthPartScheme AS PARTITION ThirteenMonthPartFunction
ALL TO ( [PRIMARY] );

CREATE TABLE [dbo].[AuditTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
) ON ThirteenMonthPartScheme (Modified)
GO

CREATE CLUSTERED INDEX [AuditTable_Modified] ON [dbo].[AuditTable]
(
    [Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ThirteenMonthPartScheme (Modified)
GO

CREATE NONCLUSTERED INDEX [AuditTable_Col1_Col2_Col3_Col4_Modified] ON [dbo].[AuditTable]
(
    [Col1] ASC,
    [Col2] ASC,
    [Col3] ASC,
    [Col4] ASC,
    [Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ThirteenMonthPartScheme (Modified)
GO

移动代码

USE Audit_New
GO
DBCC TRACEON(610);

INSERT INTO AuditTable
SELECT * FROM Audit.dbo.AuditTable
WHERE Modified >= '6/1/2015'
ORDER BY Modified