Migrating a 40TB SQL Server Database

> "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 data? Why not just dump it into linear files?

Re: Migrating a 40TB SQL Server Database

#5

Post by posix_me_less » Thu, Jul 30, 2020, 3:54 PM UTC

jeffbee wrote: post #3

Storing web logs in a SQL database is a pretty bad plan.

Yeah, this poke my eye as well.

Re: Migrating a 40TB SQL Server Database

#6

Post by tutfbhuf » Thu, Jul 30, 2020, 3:55 PM UTC

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.

Re: Migrating a 40TB SQL Server Database

#7

Post by tyingq » Thu, Jul 30, 2020, 3:57 PM UTC

jeffbee wrote: post #3

Storing web logs in a SQL database is a pretty bad plan.

Combined with "The database was initially designed to have a single table for each day." Oy.

Re: Migrating a 40TB SQL Server Database

#8

Post by tutfbhuf » Thu, Jul 30, 2020, 3:59 PM UTC

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…

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

Re: Migrating a 40TB SQL Server Database

#9

Post by ThePadawan » Thu, Jul 30, 2020, 4:00 PM UTC

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…

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.

Re: Migrating a 40TB SQL Server Database

#10

Post by oneplane » Thu, Jul 30, 2020, 4:02 PM UTC

tutfbhuf wrote: post #8

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…

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

And you could use ElasticSearch. ElasticSearch would be fine for this.