Qual é a melhor maneira de arquivar tudo menos o ano atual e particionar a tabela ao mesmo tempo

Tarefa

Arquive todos, exceto um período contínuo de 13 meses, de um grupo de tabelas grandes. Os dados arquivados devem ser armazenados em outro banco de dados.

• O banco de dados está no modo de recuperação simples
• As tabelas têm 50 mil linhas a vários bilhões e, em alguns casos, ocupam centenas de GB cada.
• As tabelas atualmente não estão particionadas
• Cada tabela tem um índice clusterizado em uma coluna de data cada vez maior
• Cada tabela possui adicionalmente um índice não clusterizado
• Todas as alterações de dados nas tabelas são inserções
• O objetivo é minimizar o tempo de inatividade do banco de dados primário.
• O servidor é 2008 R2 Enterprise

A tabela "arquivo" terá cerca de 1,1 bilhão de linhas, a tabela "ao vivo" cerca de 400 milhões. Obviamente, a tabela de arquivo aumentará com o tempo, mas espero que a tabela ao vivo também aumente razoavelmente rápido. Digamos 50% nos próximos anos, pelo menos.

Eu tinha pensado nos bancos de dados estendidos do Azure, mas infelizmente estamos no 2008 R2 e provavelmente ficaremos lá por um tempo.

Plano atual

• Criar um novo banco de dados
• Crie novas tabelas particionadas por mês (usando a data modificada) no novo banco de dados.
• Mova os dados dos 12 a 13 meses mais recentes para as tabelas particionadas.
• Faça uma troca de renomeação dos dois bancos de dados
• Exclua os dados movidos do banco de dados agora "arquivado".
• Particione cada uma das tabelas no banco de dados "arquivo".
• Use trocas de partição para arquivar os dados no futuro.
  • Percebo que terei que trocar os dados a serem arquivados, copiar essa tabela para o banco de dados de arquivo e, em seguida, trocá-la na tabela de arquivo. Isso é aceitável.

Problema: estou tentando mover os dados para as tabelas particionadas iniciais (na verdade, ainda estou fazendo uma prova de conceito nela). Estou tentando usar o TF 610 (conforme o Guia de desempenho de carregamento de dados ) e uma INSERT...SELECTinstrução para mover os dados inicialmente pensando que seriam minimamente registrados. Infelizmente, toda vez que tento, é totalmente logado.

Neste ponto, estou pensando que minha melhor aposta pode ser mover os dados usando um pacote SSIS. Estou tentando evitar isso, pois estou trabalhando com 200 tabelas e tudo o que posso fazer por script, posso gerar e executar facilmente.

Há algo que estou perdendo em meu plano geral e o SSIS é minha melhor aposta para mover os dados rapidamente e com uso mínimo do log (preocupações de espaço)?

Código de demonstração sem dados

-- 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

Mover código

USE Audit_New
GO
DBCC TRACEON(610);

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