The goal was to migrate the data to the new format, then when we got new servers it would be as simple as moving the drives to get the new database in place (or so we thought).
The database was going to have all of the old historical tables (i.e. the 40TBs) stored on the TrafficLogs_Archive filegroup, and we’d use the PRIMARY and TrafficLogs_Current for newer data being added.
Pulling millions of rows of data from spinny drives, and then inserting it back into new tables on those same spinny drives was slow.
We didn’t have new hardware yet and these were production SQL Servers - meaning the old database was still receiving new data and we were adding a new table everyday.
As I was moving tables to the new database, we were adding tables with hundreds of millions of rows of data to the old database.