Migrating a 40TB SQL Server Database • Hacker News Board

Re: Migrating a 40TB SQL Server Database

#61

Post by atwebb » Thu, Jul 30, 2020, 7:27 PM UTC

malisper wrote: post #43

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…

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.

Re: Migrating a 40TB SQL Server Database

#62

Post by bob1029 » Thu, Jul 30, 2020, 7:34 PM UTC

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…

Logging to a database is probably a mistake if you haven't thought about how you'd use that data after you write it to disk. If you have good ideas about how you'd want to use that data, then it is probably a fantastic idea.

We log to SQL so that we can instantly obtain a full list of log entries that pertain to a specific user action trace id. We can go from a collection of user actions over a larger business process and then for each action we can pull all of the log entries that were generated. All of this is exposed through a nice web interface with full text search capability over the log messages. Without logging to SQL, we would have a hell of a time building something similar.

In order to keep this from exploding out of control, we have a strict 90 day expiration policy on all persisted business state and log entries. Our log table indicies are:

- User Action Trace Id (16 bytes)

- Timestamp (64 bits)

- Message (Variable - FTS)

We store all log entries in a single SQLite database (logs.db) contained in each environment. These are queried over HTTP from centralized management tools.

Re: Migrating a 40TB SQL Server Database

#63

Post by j88439h84 » Thu, Jul 30, 2020, 7:44 PM UTC

jeffbee wrote: post #60

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…

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.

What is the use case for logs?

Re: Migrating a 40TB SQL Server Database

#64

Post by polskibus » Thu, Jul 30, 2020, 7:47 PM UTC

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'?

Re: Migrating a 40TB SQL Server Database

#65

Post by jeffbee » Thu, Jul 30, 2020, 8:02 PM UTC

j88439h84 wrote: post #63

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.

What is the use case for logs?

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

Re: Migrating a 40TB SQL Server Database

#66

Post by jrbancel » Thu, Jul 30, 2020, 8:05 PM UTC

jeffbee wrote: post #60

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…

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.

Re: Migrating a 40TB SQL Server Database

#67

Post by nickpeterson » Thu, Jul 30, 2020, 8:09 PM UTC

jeffbee wrote: post #65

j88439h84 wrote: post #63

Earlier quoted context omitted.

What is the use case for logs?

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

Re: Migrating a 40TB SQL Server Database

#68

Post by Shog9 » Thu, Jul 30, 2020, 8:10 PM UTC

oneplane wrote: post #55

Shog9 wrote: post #18

Earlier quoted context omitted.

Curious what use-cases you're envisioning here. Having worked with the system described in the past, it's quite flexible: data can be easily pulled out into more specialized systems for aggregation or trend analysis, while the raw logs remain quickly accessible over long enough periods of time to allow for digging into everything from support cases involving a single person to monitoring distributed attacks. Access c…

It's not that the system itself isn't functional at all, but think about cost, flexibility (i.e. the migration as posted) and support. If you have to move that much data at once because your RDBMS doesn't work in any other way you're going to be in trouble, even if you get the 50k costing SSDs. All of the things SQL server does can be done with not-SQL-server things, but you suddenly gain the capability to take shard…

I'm sure there are better options. OTOH... I was at SO for about 9 years, and the system described existed for all of it - predating GraphQL, predating even ElasticSearch. There's stuff built on top of it that was never envisioned when it was created, and stuff that wouldn't have been possible without it.

At this point, moving to something else would likely be a far more costly investment just in terms of requirements analysis than the migration described in the blog post. Would it pay off? Maybe! But for a critical bit of infrastructure, that's something you put an awful lot of careful thought and research into before you venture to do anything... And meanwhile, that infrastructure has to be kept running.

Re: Migrating a 40TB SQL Server Database

#69

Post by bryan_w » Thu, Jul 30, 2020, 8:11 PM UTC

tutfbhuf wrote: post #6

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.

Agreed, it's questionable why a company needs to save raw traffic logs for years.

You say that but people were pretty upset that Google couldn't confirm if anybody exploited the G+ security bug past the last two weeks that it was discovered.

Re: Migrating a 40TB SQL Server Database

#70

Post by [deleted] » Thu, Jul 30, 2020, 8:11 PM UTC

jeffbee wrote: post #60

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…

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.

[deleted]