主页 / dba / 问题 / 212668
Accepted
Mattia Nocerino
Asked: 2018-07-20 06:58:37 +0800 CST

将数据从一个表移动到另一个表而不会丢失数据

  • 772

我有一个日志表，目前保存着数百万条记录。我想在那个表上启用分区，所以我现在做的是：

  1. 创建了分区函数和分区方案。
  2. 在该分区方案上创建了一个具有相同结构的空表。
  3. 从这个时间点（我们称之为T1）将当前日志表中的数据复制到新的分区表。

接下来的步骤是将最后剩余的记录从 to 复制T1并重Tnow命名这两个表，以便应用程序开始写入新的分区表。

当然日志表是经常访问的，所以我的问题是：

如何确保在此过程中不会丢失任何数据？我可以做到让用户不会注意到任何事情，或者我是否必须在这短暂的时间内停止应用程序？或者我可以阻止该表以便应用程序继续运行吗？如果是这样，怎么做？

sql-server sql-server-2016

2 个回答

  1. Best Answer

    考虑使用您想要的分区策略创建一个新表，并在两个表的顶部添加一个视图，该视图执行全部联合。让人们使用视图，并针对基础表和视图编写触发器而不是触发器。

    插入应该发送到新表，更新应该将数据移动到新表，删除应该应用于两个表。

    然后在后台进行批量移动，一次将尽可能多的记录移动到新表中。在此过程中您仍然可能遇到并发问题，以及一些糟糕的执行计划，但它可以让您在移动发生时保持在线。

    理想情况下，您在周五下午开始该过程，以尽量减少对最终用户的影响，并尝试在周一早上之前完成。一旦它到位，您可以将视图更改为仅指向新表，然后可怕的执行计划就消失了。理想情况下。

    为避免在批量迁移数据时触发触发器，请查看触发器中已删除/插入的表中的行数，如果活动接近批处理中的行数，则跳过这些活动。

    您希望这对您的最终用户来说越透明，它需要的工作（和测试）就越多。如果您使用分区，这尤其适用：人们通常认为它会使他们的所有查询更快，但其中一些最终会慢得多。如果可以，请尝试在具有分区表的开发服务器上测试尽可能多的工作负载。

    • 9

  2. 您可能需要考虑使用较小的日志记录表和较大的存档表来执行您所说的分区：

    大约 15 年前，在我工作的医院里，我们遇到了类似的问题。当审计日志被这些报告锁定时，人们试图访问审计日志以进行报告。因此，我们通过将日志分块到另一个数据库来将报告与日志记录分开以进行报告。为了避免服务中断，我们设置了一个计时器，将部分数据分块到新表中——一次可能有 100000 条记录。在您的情况下，一旦您将数据放到新表中，您就可以按照自己的喜好进行分区。

    使用此策略，您的原始日志记录表将始终保持较小，您的存档表将增长并成为您想要分区的表。对我们来说，将当前日志记录与历史信息分开是一个巨大的好处。我想你需要自己权衡一下这种好处。但至少，当您将数据导出到存档数据库/表时，您仍然可以写入原始表而不会影响正在进行的日志记录。

    在您的情况下，进行历史报告的用户可能会注意到，因为他们有一个新表可以访问。但记录将继续在原始表上不受阻碍。

    这是代码，我很抱歉，它有点生硬——newaudit 是日志数据库，audit 是归档数据库。（当然，您希望在任何生产上线之前在开发环境中对此进行测试）：

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

相关问题