Mover dados de uma tabela para outra sem perder dados

Considere a criação de uma nova tabela com a estratégia de particionamento desejada e adicione uma exibição em ambas as tabelas que faça uma união de todos. Faça com que as pessoas usem a visualização e escrevam gatilhos em vez de nas tabelas e visualizações subjacentes.

As inserções devem ser enviadas para a nova tabela, as atualizações devem mover os dados para a nova tabela e as exclusões devem ser aplicadas a ambas as tabelas.

Em seguida, faça movimentos em lote em segundo plano, movendo tantos registros de cada vez quanto possível para a nova tabela. Você ainda pode ter problemas de simultaneidade enquanto isso está acontecendo e alguns planos de execução terríveis, mas permite que você fique online enquanto os movimentos estão acontecendo.

Idealmente, você inicia o processo em uma tarde de sexta-feira para minimizar o efeito sobre os usuários finais e tenta fazê-lo antes da manhã de segunda-feira. Uma vez que esteja no lugar, você pode mudar a visão para apontar apenas para a nova mesa, e os terríveis planos de execução desaparecem. Idealmente.

Para evitar que os gatilhos sejam disparados quando os dados estiverem sendo migrados em lotes, observe o número de linhas nas tabelas excluídas/inseridas no gatilho e ignore as atividades se estiverem próximas ao número de linhas em seu lote.

Quanto mais transparente você quiser que isso seja para seus usuários finais, mais trabalho (e testes) será necessário. Isso vale especialmente se você estiver usando particionamento: muitas vezes as pessoas acreditam que isso tornará todas as suas consultas mais rápidas e, no entanto, algumas delas acabam muito mais lentas. Tente testar o máximo de sua carga de trabalho em um servidor de desenvolvimento com as tabelas particionadas, se puder.

Você pode querer considerar uma tabela de log menor e uma tabela de arquivo maior para fazer o particionamento de que você falou:

Aqui no hospital onde trabalho tivemos o mesmo problema há uns 15 anos. As pessoas estavam tentando acessar o log de auditoria para relatórios enquanto o log de auditoria estava sendo bloqueado por esses relatórios. Portanto, separamos o relatório do log, separando o log em outro banco de dados para fins de relatório. Para evitar falhas no serviço, configuramos um cronômetro para separar porções dos dados na nova tabela - 100.000 registros possíveis por vez. No seu caso, depois de obter os dados para sua nova tabela, você pode particionar como quiser.

Usando essa estratégia, sua tabela de log original sempre permanecerá pequena e sua tabela de arquivos aumentará e será a que você deseja particionar. Para nós, foi um benefício imenso separar o registro atual das informações históricas. Eu acho que você precisará pesar esse benefício para si mesmo. Mas, pelo menos, você ainda poderá gravar na tabela original sem afetar o registro em log que está acontecendo - quando você exporta os dados para o banco de dados/tabela de arquivo.

Os usuários que fazem relatórios históricos, no seu caso, podem perceber porque têm uma nova tabela para acessar. Mas o registro continuará na tabela original sem impedimentos.

Aqui está o código, minhas desculpas, é um pouco duro - onde newaudit é o banco de dados de log e audit é o banco de dados de arquivamento. (É claro que você gostaria de testar isso em um ambiente de desenvolvimento antes de qualquer produção entrar em vigor):

Create proc [dbo].[spExportAuditLog]
as

    set nocount on
    declare @errorvar int,@max int, @min int, @watermark int, @batch int, @beforecounter int, @aftercounter int, @AuditCounter int, @NewAuditCounter int, @errortext varchar(255)
    select @beforecounter=0
    select @aftercounter=0
    select @NewAuditCounter = count(*) from newaudit.dbo.auditlog
    print 'NewAudit AuditLog Count Before'
    print @NewAuditCounter
    print 'Audit AuditLog Count Before'
    select @AuditCounter=count(*) from [TARGETSQLSERVER].audit.dbo.auditlog
    print @AuditCounter

    select top 1 @max=ID from newaudit.dbo.auditlog where  datediff( minute, datestamp,getdate()) <= 1440 order by ID

    select @min=min(ID) from newaudit.dbo.auditlog
    Set XACT_ABORT on
    select @batch =100000
    select @watermark=@min
    while (@watermark <=@max)
    begin
            begin transaction
            insert into [TARGETSQLSERVER].audit.dbo.auditlog(ApplicationName, MessageType, Message, Comments, UserID, CustomFields, Datestamp, ServerName, MRN)
            select ApplicationName, MessageType, Message, Comments, UserID, CustomFields, Datestamp, ServerName, MRN
                         from newaudit.dbo.auditlog where ID between @watermark and @watermark+@batch and ID <= @max order by id
                         if @@ERROR <> 0 select @errorvar=@errorvar+1
            delete from newaudit.dbo.auditlog where ID between @watermark and @watermark+@batch and ID <= @max
            if @@ERROR <> 0 select @errorvar=@errorvar+1
            if @errorvar > 0 
            begin
                select @errortext= 'ROLLBACK!! Problem with error IDs between:' + CONVERT(varchar(50), @watermark) + ' and ' +CONVERT(varchar(50), @watermark+@batch)
                print @errorText
                rollback 
                select @errorvar=0
            end
            else commit
            select @watermark = @watermark+@batch +1
            waitfor delay '000:00:10'
    end

    select @NewAuditCounter = count(*) from newaudit.dbo.auditlog
    print 'NewAudit AuditLog  Count After'
    print @NewAuditCounter
    print 'Audit AuditLog Count After'
    select @AuditCounter=count(*) from [TARGETSQLSERVER].audit.dbo.auditlog
    print @AuditCounter