5 ms·

How would you 'do it right'?

blyat 6y ago
I've only done a few 30+TB moves, largest being 50TB which had similar table structure - one table per machine holding calibration readings that were constantly streamed in from a MUCH beefier RabbitMQ instance. Old CPU, not a ton of cores, spinning disks, etc. Same base problem. I think the best thing to do here would be change the order of some things around. 1. Buy the hardware first with blazing drives and roaring CPUs and get it stood up before you start trying to change your schema on a potato 2. Do his last step now: set up an AG and let SQL Server do it's thing 3. Now go start planning your ETL/schema changes and writing scripts while you wait 1-3 days (or in my case 10 as we had to suspend during business hours) for data to migrate. 4. Fail-over and promote and start collecting data on your superbox 5. If the world didn't explode, you do the same thing at your other DC 6. Now, if you absolutely must, apply and test all your schema changes in production instead of dev first but enjoy the fact that those changes are exponentially faster! You could also use log shipping, but with those restore times you'd have data loss I think. Haven't done that in years - I've been lucky enough to only have to deal with projects where we shutdown, restore, and do a full downtime migration. So much nicer...
- idlewords 6y ago
  Having step 1 of your solution be "eliminate the hardware constraints that made this job painful" is kind of a cheat. We can all do a better job on any problem if we lift those constraints.
  - LadyCailin 6y ago
    Hardware is way cheaper than 11 months salary + lost opportunity cost.
    - idlewords 6y ago
      I think we all agree on that, but the question was not whether this was an efficiently managed migration. I think we can assume OP made a similar argument up the chain (he got them to add SSD storage, at least).
oneplane 6y ago
We did it by removing all non first-party access and turning the only access into a single-owner API service. Then we started partitioning off certain calls in to a cache, i.e. when someone decides they want all possible header combinations over a time period they don't get instant results but instead get a job ID and they can either get an event trigger when the results are in or they can poll for status if that is what they like. Next, we get the cache to write to ElasticSearch and on lookup it would first check if memcache has a pointer to ElasticSearch already and if not it would spawn the job, copy the input set to ES, run the query there and add a pointer in memcache. This meant that in practise you'd retain the same functionality you had before while not having a hard dependency on the backend. Next, we scaled op the API service and the cache service to a very high number and started querying for time ranges that were known to not have pointers in memcache. Depending on the amount of spot instances we could get for the price we wanted and the saturation of the 10GbE DirectConnect we could do up to 400MiB/s, 29TiB took us about 3 days because the DirectConnect was also used to other traffic that had a higher priority and the back pressure system would reduce the API request count accordingly (using Istio). In the end the users had only two changes: 1. No more 'talking to the database', you have to use the API which started out rather restriced 2. Some analysis takes a little longer during migration due to the SQL-ES copy action; after the MSSQL stuff was all gone the queries were much faster than before

I've only done a few 30+TB moves, largest being 50TB which had similar table structure - one table per machine holding calibration readings that were constantly streamed in from a MUCH beefier RabbitMQ instance. Old CPU, not a ton of cores, spinning disks, etc. Same base problem. I think the best thing to do here would be change the order of some things around. 1. Buy the hardware first with blazing drives and roaring CPUs and get it stood up before you start trying to change your schema on a potato 2. Do his last step now: set up an AG and let SQL Server do it's thing 3. Now go start planning your ETL/schema changes and writing scripts while you wait 1-3 days (or in my case 10 as we had to suspend during business hours) for data to migrate. 4. Fail-over and promote and start collecting data on your superbox 5. If the world didn't explode, you do the same thing at your other DC 6. Now, if you absolutely must, apply and test all your schema changes in production instead of dev first but enjoy the fact that those changes are exponentially faster! You could also use log shipping, but with those restore times you'd have data loss I think. Haven't done that in years - I've been lucky enough to only have to deal with projects where we shutdown, restore, and do a full downtime migration. So much nicer...

idlewords 6y ago
Having step 1 of your solution be "eliminate the hardware constraints that made this job painful" is kind of a cheat. We can all do a better job on any problem if we lift those constraints.
- LadyCailin 6y ago
  Hardware is way cheaper than 11 months salary + lost opportunity cost.
  - idlewords 6y ago
    I think we all agree on that, but the question was not whether this was an efficiently managed migration. I think we can assume OP made a similar argument up the chain (he got them to add SSD storage, at least).

oneplane 6y ago

We did it by removing all non first-party access and turning the only access into a single-owner API service. Then we started partitioning off certain calls in to a cache, i.e. when someone decides they want all possible header combinations over a time period they don't get instant results but instead get a job ID and they can either get an event trigger when the results are in or they can poll for status if that is what they like. Next, we get the cache to write to ElasticSearch and on lookup it would first check if memcache has a pointer to ElasticSearch already and if not it would spawn the job, copy the input set to ES, run the query there and add a pointer in memcache. This meant that in practise you'd retain the same functionality you had before while not having a hard dependency on the backend. Next, we scaled op the API service and the cache service to a very high number and started querying for time ranges that were known to not have pointers in memcache. Depending on the amount of spot instances we could get for the price we wanted and the saturation of the 10GbE DirectConnect we could do up to 400MiB/s, 29TiB took us about 3 days because the DirectConnect was also used to other traffic that had a higher priority and the back pressure system would reduce the API request count accordingly (using Istio). In the end the users had only two changes: 1. No more 'talking to the database', you have to use the API which started out rather restriced 2. Some analysis takes a little longer during migration due to the SQL-ES copy action; after the MSSQL stuff was all gone the queries were much faster than before