Move data from a table to another without losing data

Question

I have a log table which is currently holding millions of records. I want to enable partitioning on that table, so what I did for now is:

1. Created a partition function and a partition scheme.
2. Created an empty table with the same structure on that partition scheme.
3. Copied data from the current log table from this point in time (let's call it T1) going backwards to the new partitioned table.

The next steps would be to copy the last remaining records from T1 to Tnow and rename the two tables so that the application starts to write to the new partitioned table.

Of course the log table is frequently accessed, so my question is:

How can I make sure that I don't lose any data during this process? Can i make it so that the users don't notice anything or do I necessarily need to stop the application for this brief time? Or can I just block that table so that the application keeps running? If so, how?

Answer 1

Consider creating a new table with the partitioning strategy you want, and add a view atop both tables that does a union all. Have people use the view, and write instead-of triggers against the underlying tables & views.

Inserts should be sent to the new table, updates should move data to the new table, and deletes should be applied to both tables.

Then do batch moves in the background, moving as many records at a time as you can over to the new table. You can still have concurrency issues while this is going on, and some terrible execution plans, but it lets you stay online while the moves are happening.

Ideally, you start the process on a Friday afternoon to minimize the effect on end users, and try to get it done before Monday morning. Once it's in place, you can change the view to point to just the new table, and the terrible execution plans go away. Ideally.

To avoid the triggers firing when the data is being migrated in batches, look at the number of rows in the deleted/inserted tables in the trigger, and skip the activities if they're near to the number of rows in your batch.

The more transparent you want this to be for your end users, the more work (and testing) it's going to take. This especially holds true if you're using partitioning: very often folks believe it's going to make all their queries faster, and yet some of them end up much slower. Try to test as much of your workload on a development server with the partitioned tables if you can.

Answer 2

You may want to consider a smaller logging table and a larger archive table to do the partitioning you spoke of:

Here at the hospital where I work we had the similar issue about 15 years ago. People were trying access the audit log for reporting while the audit logging was being locked by those reports. Therefore, we separated the reporting from the logging by chunking off the log to another database for reporting purposes. To avoid gaps in service we set a timer to chunk off portions of the data to the new table--100000 possible records at a time. In your case, once you get the data to your new table, you can partition how you like.

Using this strategy your original logging table will always remain small and your archive table will grow and be the one you want to partition. For us, it was an immense benefit to separate the current logging from the historical information. I guess you'll need to weigh that benefit out for yourself. But at least, you'd still be able to write to the original table without affecting the logging that's going on--when you export the data to the archive database/table.

The users who do historical reporting, in your case, may notice because they've got a new table to go to. But logging will continue on the original table unhindered.

Here's the code, my apologies, it's a bit crusty--where newaudit is the logging database and audit is the archiving database. (Of course you'd want to test this in a dev environment before any production go-lives):

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