Move data from a table to another without losing data

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:

• Created a partition function and a partition scheme.

• Created an empty table with the same structure on that partition scheme.

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

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.

StackExchange Database Administrators Q#212668, answer score: 9