5 ms·

This reads like the most Microsoft or most legacy-on-prem problem ever. Why on earth would you do this, and even then not take the opportunity to do it right wh

This reads like the most Microsoft or most legacy-on-prem problem ever. Why on earth would you do this, and even then not take the opportunity to do it right while you are migrating anyway? Are you mad? Well, that was my first reaction anyway. It seems to me that this is more of a 'when all you have is a hammer, everything looks like a nail' to me; if you are a vendor shop (i.e. Microsoft-only) and you are a DBA and specifically just for MSSQL, then this is probably the best way you could come up with, based on the skills, experience and tools at hand. It does make me wonder if at any point someone thought to ask for help instead of keeping this going.

BrentOzar 6y ago
> Why on earth would you do this, and even then not take the opportunity to do it right while you are migrating anyway? Because as a database administrator, you often have to solve hosting problems without telling your developers to rewrite all their code.
- pc86 6y ago
  Which speaks to much more dire organizational issues - that nobody can throw up a red flag and say "hold the eff up, we've reached a scale where this is broken and we need to address it."
  - hn_acc_2 6y ago
    I would say this is actually the norm... In most orgs the leadership is not highly technical and this is always a hard sell. The best you can hope for after a "hold the eff up" rewrite is for everything to keep working the same "but it's more hardened/scalable/modular/blah blah", and the worst you can hope for is to screw up some critical business process while the kinks are worked out. Also, there is rarely any incentive for anyone from Joe Developer all the way up to C level to even call for this in the first place.
- oneplane 6y ago
  Unless the developers are HAProxy itself and made a module for HAProxy to log in to SQL databases (which it doesn't do by default) this is not even remotely related to the developers.
  - user5994461 6y ago
    Plot twist: it does support SQL out of the box! HAProxy logs syslog messages, in a configurable format and to a destination of your choosing. All you have to do is to setup your favorite logging agent between fluentd/logstash/syslog-ng to listen on syslog and forward messages to a SQL database.
    - oneplane 6y ago
      I know ;-) Hence my lawyer-ish choice of wording (default, module). HAProxy has some real nice features, especially the 1.8+ versions. We used to have configuration management tooling reconfigure and reload it, later on it get hot-reloads which was nice and then it got consul support so we could just feed consul and it all would work like magic. Love it. My point was more about logging something straight in to a RDBMS wasn't much of a default at any point in time. Earlier on it was too expensive, and later on better systems came along. Some systems have built in text analysis and time series data specifics, but why would you worry about those when you have ElasticSearch and Kibana for most generic setups. Even before that you have syslog and a lot of log parsing libraries that worked great (well, unless you used windows event logs which has pros and a lot of cons).
polskibus 6y ago
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).

BrentOzar 6y ago

> Why on earth would you do this, and even then not take the opportunity to do it right while you are migrating anyway? Because as a database administrator, you often have to solve hosting problems without telling your developers to rewrite all their code.

pc86 6y ago
Which speaks to much more dire organizational issues - that nobody can throw up a red flag and say "hold the eff up, we've reached a scale where this is broken and we need to address it."
- hn_acc_2 6y ago
  I would say this is actually the norm... In most orgs the leadership is not highly technical and this is always a hard sell. The best you can hope for after a "hold the eff up" rewrite is for everything to keep working the same "but it's more hardened/scalable/modular/blah blah", and the worst you can hope for is to screw up some critical business process while the kinks are worked out. Also, there is rarely any incentive for anyone from Joe Developer all the way up to C level to even call for this in the first place.
oneplane 6y ago
Unless the developers are HAProxy itself and made a module for HAProxy to log in to SQL databases (which it doesn't do by default) this is not even remotely related to the developers.
- user5994461 6y ago
  Plot twist: it does support SQL out of the box! HAProxy logs syslog messages, in a configurable format and to a destination of your choosing. All you have to do is to setup your favorite logging agent between fluentd/logstash/syslog-ng to listen on syslog and forward messages to a SQL database.
  - oneplane 6y ago
    I know ;-) Hence my lawyer-ish choice of wording (default, module). HAProxy has some real nice features, especially the 1.8+ versions. We used to have configuration management tooling reconfigure and reload it, later on it get hot-reloads which was nice and then it got consul support so we could just feed consul and it all would work like magic. Love it. My point was more about logging something straight in to a RDBMS wasn't much of a default at any point in time. Earlier on it was too expensive, and later on better systems came along. Some systems have built in text analysis and time series data specifics, but why would you worry about those when you have ElasticSearch and Kibana for most generic setups. Even before that you have syslog and a lot of log parsing libraries that worked great (well, unless you used windows event logs which has pros and a lot of cons).

polskibus 6y ago

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).