Migrating a 40TB SQL Server Database • Hacker News Board

Re: Migrating a 40TB SQL Server Database

#41

Post by Klinky » Thu, Jul 30, 2020, 5:17 PM UTC

PaywallBuster wrote: post #22

ThePadawan wrote: post #9

Earlier quoted context omitted.

Because you've already paid the licensing cost for the SQL database and have salaried DBAs taking care of it. And building an alternative solution (including HA, backups etc.) would cost more than $0.

> I fully expected the process to take months, and it did — after hitting delays in getting new servers the entire project took about 11 months. This doesn't sound like it cost $0

At the time of the decision was made(4.5~ years ago) it probably was $0. Now it's worth the cost to migrate because the value of keeping that data has been proven worthwhile by the cheapest initial solution.

Re: Migrating a 40TB SQL Server Database

#42

Post by BrentOzar » Thu, Jul 30, 2020, 5:19 PM UTC

dswalter wrote: post #37

According to their pricing page, a 50TB SSD from Nimbus costs $12,500. That would have been worth the expense in DBA time alone, right? Edit: added the link. https://nimbusdata.com/products/exadrive/pricing/

> That would have been worth the expense in DBA time alone, right?

You're assuming the DBA did nothing else during the project's timeline, and that's not likely the case. A lot of work in projects like this is just letting the batch job run while you keep an eye on it.

Re: Migrating a 40TB SQL Server Database

#43

Post by malisper » Thu, Jul 30, 2020, 5:22 PM UTC

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.

Re: Migrating a 40TB SQL Server Database

#44

Post by bluedino » Thu, Jul 30, 2020, 5:23 PM UTC

Hah. My first thought was, 'what kind of idiot lets a file get to be 44TB... Then I realized who it was. Nice to see that even StackOverflow had an issue like this.

Re: Migrating a 40TB SQL Server Database

#45

Post by pbronez » Thu, Jul 30, 2020, 5:26 PM UTC

hogFeast wrote: post #31

posix_me_less wrote: post #16

Earlier quoted context omitted.

Because everything gets substantially slower and more difficult, see 11 months of busy work motivated by queries being slow, described in TFA. SQL is great for querying data, but you don't need to store the whole history in a single database. ACID is a red herring. These are traffic logs, not some precious global state that has to handle ACID transactions. The better alternative is to dump the data into many big file…

Then use partitioning (which the OP appears to be doing)? If you need to do some kind of full table scan, what exactly is going to be faster about noSQL? With SQL, your data is presumably going to be non-contiguous so you are going to get slower seeks on disk...but (I believe, although I am not 100% familiar) this can happen with noSQL if you add an index (which are often BTree...so the same as SQL under the hood) an…

> If you need to do some kind of full table scan, what exactly is going to be faster about noSQL?

The real architectural benefit we got from noSQL (remember - "Not Only SQL") was dynamic horizontal scalability. Once you separate Compute and Storage then you can make choices based on your specific workload.

> You are reading from disk, that is your limit.

Separating Compute and Storage works around this. Instead of putting all your logs in a monolithic SQL database, stream them into object storage. When you want to run a query, you can complete it arbitrarily fast by spinning up more compute nodes. Now you're using Massively Parallel Processing and that disk limit stops mattering as much. Lots of systems do this... AWS EMR, Snowflake, Presto, etc etc

So you get a much simpler storage system and faster queries.

You may appear to pay more for storage in this approach, but that's mostly a cloud vs on prem accounting question.

Re: Migrating a 40TB SQL Server Database

#46

Post by pc86 » Thu, Jul 30, 2020, 5:27 PM UTC

BrentOzar wrote: post #15

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…

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

Re: Migrating a 40TB SQL Server Database

#47

Post by [deleted] » Thu, Jul 30, 2020, 5:36 PM UTC

BrentOzar wrote: post #34

jto1218 wrote: post #27

> There were lots of reasons this needed to be done, one being tech debt. We realized that the original daily table structure wasn’t ideal. If we needed to query something over several days or months, it was terrible — lots of UNION ALLs or loops to crawl through days or even months at a time was slow. Maybe i'm missing it, but there doesn't seem to be any discussion of the result. _How_ much faster was it after the…

> Maybe i'm missing it, but there doesn't seem to be any discussion of the result. Sometimes you have to stop writing after 9,000 words. ;-)

[deleted]

Re: Migrating a 40TB SQL Server Database

#48

Post by user5994461 » Thu, Jul 30, 2020, 5:37 PM UTC

This is all the reasons why logs must have a limited retention period (usually 1 to 3 months).

Then nobody has to waste 11 months of their life trying to retain and migrate TB of old logs that nobody needs, with no storage to do so and no backup.

Re: Migrating a 40TB SQL Server Database

#49

Post by Shog9 » Thu, Jul 30, 2020, 5:42 PM UTC

dsr_ wrote: post #35

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.

Right here at the beginning: "Our HAProxy1 logs aka traffic logs, are currently stored on two SQL Servers, one in New York, and one in Colorado. While we store a minimal summary of the traffic data, we have a lot of it. At the beginning of 2019, we had about 4.5 years of data, totaling about 38TB. " and I asked myself, why the hell would you want this data? Logs serve two purposes: - find a fault and fix it - audit w…

> unless, of course it's a bug that ... is similarly rare...

I'll give an example: a bug in the revision ordering logic that could be triggered by two edits being submitted almost simultaneously where one of the editors had made another edit within the past few minutes. The bug resulted in rows being interleaved between the two revisions and the post being rendered as a combination of two unrelated edits.

Almost impossible to reproduce. Affected a low double-digit number of posts out of millions edited over the course of several years.

Was able to finally nail down the scenario by pulling logs for all editors involved in each of the events over the years and identifying the common factor.

(You can argue this is a crazy way of storing revisions, but that's beside the point; at this scale even very unlikely bugs do show up with some frequency and it is nice to be able to investigate them)

Re: Migrating a 40TB SQL Server Database

#50

Post by user5994461 » Thu, Jul 30, 2020, 5:49 PM UTC

amichal wrote: post #40

polygotdomain wrote: post #32

I think some of the posts here miss a little bit of the context as to why things like this happen in the first place. It's only in the last handful of years that a stack for logging has really become mainstream. Chances are a lot of these types of logging solutions predate that and used whatever persistence technology was readily available. Writing to files on web servers can be a pain, and these logs will have to be…

Totally agree. I would be curious if anyone who is saying why didn't you use XYZ has actually migrated 500 billion rows of queryable log data from one tech to the other. For everyone complaining about PII. The article shows a 6 year log retention which fits with many regulatory requirements for data retention. We forget that a lot changes in 6 years on what makes sense to do. ELK stack according to elastics history p…

I've done with a hundred billion rows. It's not too difficult but it takes a lot of time. Note that elasticsearch scales really easy and really well, I have an article on a small setup many years ago for 12 TB in a small company https://thehftguy.com/2016/09/12/250-gbday-of-logs-with-gray...

The challenge is, a migration would require a lot of hardware resources (storage mainly) to setup a new cluster and the poster couldn't get one damn hard drive to work with, so any migration would have been a death march from the start.

Knowing that and that the original system was out of disk and had no backup of any sort. I would personally question the effort to try to keep all these old logs.