Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Migrating a 40TB SQL Server Database

tarynpivots.com

Post reply on HN
Print view View original

71–80 of 126 posts

Re: Migrating a 40TB SQL Server Database

#71

Post by hn_acc_2 »

pc86 wrote: post #46
BrentOzar wrote: post #15

Earlier quoted context omitted.

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

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

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.

Re: Migrating a 40TB SQL Server Database

#72

Post by jeffbee »

nickpeterson wrote: post #67
jeffbee wrote: post #65

Earlier quoted context omitted.

There isn't a universal one. If you don't have a concrete one in mind, you shouldn't produce the log at all.

I appreciate the zen-like nature of this advice, but I think you also know how unreasonable it is most of the time, unless by 'concrete' you allow something as vague as, "troubleshoot production issues".

Ad-hoc production troubleshooting is a reason to keep, at most, 7 days of logs. Usually you want the most recent minute or hour. Troubleshooting usually does not need collection, aggregation, and indexing because either the problem is isolated to a host or the logs of a single host, pod, or process are representative of what is happening in the rest of the fleet. Even if you want to access all logs, it's still better to leave them where they were produced and push a predicate out to every host; your log-producing fleet has far, far more compute resources than your poor little central database, no matter how big that DB is.

Re: Migrating a 40TB SQL Server Database

#73

Post by rrdharan »

jpalomaki wrote: post #57
rrdharan wrote: post #2

It seems concerning that they would be hanging onto traffic logs for that long. Seems like a massive PII / data breach liability.

Stack Overflow HAproxy logs likely don’t have too much sensitive information. These are most likely logs about visited pages with timestamps - maybe with client IP addresses. Storing them raw can make sense. You might later come up with new ways to analyze the data which your aggregation logic of choice does not support.

> maybe with client IP addresses

https://www.whitecase.com/publications/alert/court-confirms-...

Re: Migrating a 40TB SQL Server Database

#74

Post by metadata »

I think 11 months is absolutely crazy, and the context for that is that I earn for living by selling my database migration software [1]. They could have used my software to copy up to 16 tables in parallel for a mere $3k. They could have copied a few hundred tables at a time, compare source and target to make sure everything is fine, then drop and compact. As more disk space frees up, they could have moved more and more data in one go. If the data needed to be transformed much, they could have wrote views to pull the data in a proper format and creating tables on the target with that data as they go. True, spinning drives would kill performance, but my tool is easily reaching 50MB/s on SSDs and spinning drives wouldn't add months. I mean, when you have off the shelf products doing what you need, spending so much time fighting with custom scripts is and odd choice. This is really not meant to advertise, I'm just baffled by how much effort was needed for this project.

[1] https://www.spectralcore.com/fullconvert

Re: Migrating a 40TB SQL Server Database

#75

Post by blyat »

polskibus wrote: post #64
oneplane wrote: post #11

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…

How would you 'do it right'?

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

Re: Migrating a 40TB SQL Server Database

#76

Post by j88439h84 »

jeffbee wrote: post #72
nickpeterson wrote: post #67

Earlier quoted context omitted.

I appreciate the zen-like nature of this advice, but I think you also know how unreasonable it is most of the time, unless by 'concrete' you allow something as vague as, "troubleshoot production issues".

Ad-hoc production troubleshooting is a reason to keep, at most, 7 days of logs. Usually you want the most recent minute or hour. Troubleshooting usually does not need collection, aggregation, and indexing because either the problem is isolated to a host or the logs of a single host, pod, or process are representative of what is happening in the rest of the fleet. Even if you want to access all logs, it's still better…

May I ask what kind of production environments you have in mind? Are these large-scale FAANG-style deployments or something else?

Re: Migrating a 40TB SQL Server Database

#77

Post by Keyframe »

jrbancel wrote: post #66
jeffbee wrote: post #60

Earlier quoted context omitted.

I disagree with the premise of your statement. It's typical that a log will be accessed zero times. Collecting, aggregating, and indexing logs is usually a mistake made by people who aren't clear on the use case for the logs.

Absolutely, the vast majority (95%+) of logs are never read by a human. Therefore, processing it is enormously wasteful. A good architecture will write once and not touch anything until it is needed. I spent years working on system handling 50+PB/day of logs. No database or ELK can handle that, and even if it did it would be prohibitively expensive.

Where did you work? CERN?

Re: Migrating a 40TB SQL Server Database

#78

Post by elorant »

j88439h84 wrote: post #76
jeffbee wrote: post #72

Earlier quoted context omitted.

Ad-hoc production troubleshooting is a reason to keep, at most, 7 days of logs. Usually you want the most recent minute or hour. Troubleshooting usually does not need collection, aggregation, and indexing because either the problem is isolated to a host or the logs of a single host, pod, or process are representative of what is happening in the rest of the fleet. Even if you want to access all logs, it's still better…

May I ask what kind of production environments you have in mind? Are these large-scale FAANG-style deployments or something else?

It could be an e-commerce site, which depending on the case can produce a shitload of logs. Imagine having a few hundred of thousands of users daily and you record every page they view, along with heatmaps, and whatnot. In most cases those logs should never be touched by a human in raw format. You feed them in your analytics engine, and start making decisions about your conversion. And then you delete them because the goalpost is constantly moving.

Re: Migrating a 40TB SQL Server Database

#79

Post by champtar »

If they want to migrate to something else, they need to have a look at ClickHouse. When switching from Elasticsearch to ClickHouse 1.5 years ago, I reduced my storage needs by 20, gained SQL, performance, and a ton of analytics features.

In hindsight I would say that Elasticsearch is for full text queries, and if you are using it for something else (access logs) there is a good chance this is the wrong tool for the job.

Re: Migrating a 40TB SQL Server Database

#80

Post by oneplane »

polskibus wrote: post #64
oneplane wrote: post #11

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…

How would you 'do it right'?

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

Post reply on HN