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

11–20 of 126 posts

Re: Migrating a 40TB SQL Server Database

#11

Post by oneplane »

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.

Re: Migrating a 40TB SQL Server Database

#12

Post by abhishekjha »

posix_me_less wrote: post #4

> "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." Seems to me rather storing traffic logs in an SQL database is a very bad idea, especially if you have 40TB of it. Anybody can explain why SQL database makes sense for large time series d…

>storing traffic logs in an SQL database is a very bad idea, especially if you have 40TB of it

I get this question asked a lot of times in the interview.

Why is it a bad idea to store logs in a SQL database? It has the ACID properties baked in and it is pretty reliable. What is the better alternative? I guess I have never worked on anything that would require keeping track of 40TB of data because of which the drawbacks aren't really obvious to me.

Re: Migrating a 40TB SQL Server Database

#14

Post by Semaphor »

oneplane wrote: post #10
tutfbhuf wrote: post #8

Earlier quoted context omitted.

> Why not just dump it into linear files? I guess because they want to query them, but in this case I would prefer the ELK stack (Elasticsearch + Logstash + Kibana).

But even then, normal files work great with (all non-Microsoft) software that is actually intended to work with this type of data at this scale. You could do it on one machine with Pandas DataFrames or on multiple machines with Spark DataFrames. You could keep warm logs in standard log files and put everything else in Parquet. You could use a parallel database. Heck, you could use any of the cloud offerings to do it.…

> But even then, normal files work great with (all non-Microsoft) software

Even Microsoft has a tool [0] that allows you to query IIS logfiles with SQL

[0]: https://www.microsoft.com/technet/scriptcenter/tools/logpars...

Re: Migrating a 40TB SQL Server Database

#15

Post by BrentOzar »

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.

Re: Migrating a 40TB SQL Server Database

#16

Post by posix_me_less »

abhishekjha wrote: post #12
posix_me_less wrote: post #4

> "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." Seems to me rather storing traffic logs in an SQL database is a very bad idea, especially if you have 40TB of it. Anybody can explain why SQL database makes sense for large time series d…

>storing traffic logs in an SQL database is a very bad idea, especially if you have 40TB of it I get this question asked a lot of times in the interview. Why is it a bad idea to store logs in a SQL database? It has the ACID properties baked in and it is pretty reliable. What is the better alternative? I guess I have never worked on anything that would require keeping track of 40TB of data because of which the drawbac…

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 files sorted by time. When you need to do some SQL kungfu on a part of the data, store that part into SQL database.

Re: Migrating a 40TB SQL Server Database

#17

Post by BrentOzar »

posix_me_less wrote: post #4

> "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." Seems to me rather storing traffic logs in an SQL database is a very bad idea, especially if you have 40TB of it. Anybody can explain why SQL database makes sense for large time series d…

> Anybody can explain why SQL database makes sense for large time series data?

Let's say, just to say, that you have effectively free licensing for Microsoft SQL Server (because you already own the licenses in question), and you have staff who know that platform well.

Sometimes it's easier to use the tools you already have rather than go acquire a new platform to achieve a goal.

As with anytime you choose a persistence layer, you're accumulating technical debt the instant you make the choice. Over time, if better persistence layers become available, you have to make a judgment call about when it makes sense to change the code, versus when it makes sense to do maintenance work on the persistence layer.

Re: Migrating a 40TB SQL Server Database

#18

Post by Shog9 »

oneplane wrote: post #10
tutfbhuf wrote: post #8

Earlier quoted context omitted.

> Why not just dump it into linear files? I guess because they want to query them, but in this case I would prefer the ELK stack (Elasticsearch + Logstash + Kibana).

But even then, normal files work great with (all non-Microsoft) software that is actually intended to work with this type of data at this scale. You could do it on one machine with Pandas DataFrames or on multiple machines with Spark DataFrames. You could keep warm logs in standard log files and put everything else in Parquet. You could use a parallel database. Heck, you could use any of the cloud offerings to do it.…

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 can be controlled and monitored with reasonable granularity, and training new folks to use it is as easy as teaching some basic Select queries.

Not gonna suggest it's the most efficient system (if nothing else, SQLServer wastes a TON of disk space), but it minimizes complexity while deftly avoiding the choice between throwing away information when it's still needed and keeping everything in flat files which are too unwieldy to be used.

Re: Migrating a 40TB SQL Server Database

#19

Post by hansitomani »

Wow.

I really wana know if they even compress the log data in there database.

40 TB of logs in a postgresql, like that can't be efficient?

I would probably just have a scaling blob store and would store it away. That has to be much faster, simpler and cheaper.

Re: Migrating a 40TB SQL Server Database

#20

Post by valuearb »

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.

Yea, it seems like this project should have started with a cost/benefit analysis. Like what is this data used for, who is using it, how often and how to quantify the benefits the users derive from it.

Not sure which the data couldn’t just be compressed into daily summaries in the DB, while the raw logs are stored (Compressed) in the file system in case you ever had to go back.

Post reply on HN