3 ms·

I've done migrations of several databases with ~20TB of data before. Assuming you're data is immutable, A much easier approach than what they did is to restore

I've done migrations of several databases with ~20TB of data before. Assuming you're data is immutable, A much easier approach than what they did is to restore a backup of the database onto a new machine. Since you don't have to worry about serving production traffic you can have the machine go full throttle on performing the migration. When the migration has finished you have to copy the new data that came in during the migration into the new database which isn't too bad. When I've done this with 20TB DBs, it usually took several days to complete. That's much better than the 11 months mentioned in the blog post.

atwebb 6y ago
Yeah I only scanned through but there seem to be better tools for the job in the environment. SSIS is scriptable/has APIs so you can shred the data into n packages and run it. Also, columnstore_archive gives amazing compression if you're low on the read side and immutable data means you can load into partitions. Column diffs are a bit annoying but not unmanageable. Then again, arm chair architecting isn't ever as accurate as you assume. I do know you can move billions of rows daily using SSIS across thousands of tables.
justinclift 6y ago
Similar thought here. Was kind of surprised they didn't go with some kind of checkpoint/snapshot approach for the data sets, + stream them to the new machine, + add any newly arrived log data.
d_k_f 6y ago
I'm late to the party, but he mentioned this in the article. The new server didn't have enough space to hold both the backup and the newly restored database, so that approach didn't work. Quote: "We’re a very lean shop when it comes to hardware, and this is by far our largest chunk of data we have to store, so there wasn’t a place anywhere on the network to store a 33-38TB full backup temporarily, while it was being restored to a server." As others have mentioned in the thread, a lot of the problems seem to have been solvable by just buying/attaching more storage, even if only temporarily.

atwebb 6y ago

Yeah I only scanned through but there seem to be better tools for the job in the environment. SSIS is scriptable/has APIs so you can shred the data into n packages and run it. Also, columnstore_archive gives amazing compression if you're low on the read side and immutable data means you can load into partitions. Column diffs are a bit annoying but not unmanageable. Then again, arm chair architecting isn't ever as accurate as you assume. I do know you can move billions of rows daily using SSIS across thousands of tables.

justinclift 6y ago

Similar thought here. Was kind of surprised they didn't go with some kind of checkpoint/snapshot approach for the data sets, + stream them to the new machine, + add any newly arrived log data.

d_k_f 6y ago

I'm late to the party, but he mentioned this in the article. The new server didn't have enough space to hold both the backup and the newly restored database, so that approach didn't work. Quote: "We’re a very lean shop when it comes to hardware, and this is by far our largest chunk of data we have to store, so there wasn’t a place anywhere on the network to store a 33-38TB full backup temporarily, while it was being restored to a server." As others have mentioned in the thread, a lot of the problems seem to have been solvable by just buying/attaching more storage, even if only temporarily.