Database Program

Question

Hi

I am building a very large database to conduct research. The initial size of the database will be about 600 GB with about 2.5 billion records. Each month it will grow by about 250 MB and 1 million records. I definitely can't do this in Access so I am looking at either SQL Server or Oracle. My questions are as follows:

1. Which of these two will best meet my needs? Is there a better choice than the ones I selected?
2. What sorts of hardware can speed up calculations that are done on a large DB like the one I am developing? I have been looking getting a dedicated workstation that has a Xeon CPU and a dedicated GPU. Is this a good idea? If yes, what type of Xeon and GPU should I be looking at.

Any suggestions that can help me pick the right software and hardware would be much appreciated.

By the way, the question is being asked in the Access forum because I couldn't find one for SQL Server. Hopefully this isn't an issue for anyone.

Thank you in advance.

Answer 1

Thanks. I see how the math was done. To be honest, I'd take that with a grain of salt because there are a lot of factors which can influence the size of a database file, especially when upsizing to a server-based database. Still it's wise to be prepared.

I wish I'd thought of the SSDs too. Good idea for speed and performance.

Answer 2

Thanks for the replies. I will take a look at the SSD

the videos look great. Thanks for sharing

Answer 3

Thanks for the reply.

Here is how I got the estimate...

The sample dataset I am using came at 180 MB compressed. Once uncompressed, the size was 971 MB. This gives an uncompressed to compressed ratio of about 5.4 to 1. The data provider said the full database is about 110 GB compressed. Using the same ratio for the sample, this would make the uncompressed size of the full dataset to be to 110*5.4 = 594 GB. I rounded it up to 600 GB.

I have been using Access on the smaller sample to create my queries. Once the query development work is done then I will need to order the full dataset and also setup the DBMS and hardware.

Thanks for the suggestion re RAM and SQL Server Central forums. This is good information for me to start my research

Answer 4

Google is your friend. Do lots of searches using multiple search terms and search engines. Do your homework based on replies you get here.

.

Since you are planning to buy new hardware, wait until after you decide on the DBMS. Sometimes software works better with specific hardware configurations.

.

In general, go with more RAM.

Go with SSD drive(s).

Here is an EXTREME example of insane specs on server hardware:

https://www.youtube.com/watch?v=9MXhGABtuCA- "fastest server" 2020 12 28 Absolutely INSANE specs (ie 1400 WATT power supply, 200watts of cooling fans, 59TB windows boot drive ... )

This "LIQID" server has absolutely insane specs. The specs they mention in the video had me ROTFLMAO! A hardware nerds "wet dream". It has "dynamic" hardware technology that I have not seen since "Tandem" mainframe hardware. It allows you to hot swap elements and dynamically reassign hardware elements between CPU boxes. Cool stuff. Unfortunately the LIQUID site makes you request price quotes. After watching the video I really could use a real world price "cold shower". I think it is safe to guess that the prices on this server hardware will make passing a kidney stone seem like a stroll in the park. And I think it is safe to say this server is overkill for your needs, price and spec wise, but wow what an insane

.

I haven't seen any reviews about creating arrays with SSD's, whether it is faster or not.

https://www.youtube.com/watch?v=N0qtu5NXhuQ - 2015 example 20tb SSD RAID https://helpdeskgeek.com/reviews/hdd-raid-vs-ssd-raid-the-major-differences-you-should-know/ 

https://www.bhphotovideo.com/c/buy/ssd-arrays/ci/30905 - example of SSD RAID units sold, prices (ouch!)

(I used to have a link to an absolutely absurd demonstration of RAIDed SSDs back when they first came out. It one of those "extreme" build examples.)

https://www.youtube.com/watch?v=lzzavO5a4OQ 2017 video .

Check to see if your DBMS can take advantage of GPU. A quick google identifies there are GPU DBMS's. The massive parallelism in GPU means that these DMBS's are intended for massive databases (orders of magnitude bigger than your estimate) and for "real time" / Online processing.

https://sqream.com/blog/different-gpu-databases/ https://medium.com/@sammedkagi/gpu-accelerated-databases-459b0e476a6e 

https://www.omnisci.com/learn/gpu-database  https://www.infoworld.com/article/3327561/what-a-gpu-powered-database-can-do-for-you.html

.

I suspect that with some research you'll find that you don't need a standalone GPU on your new computer if it is dedicated as a DBMS server.

.

Answer 5

600 GB? That's huge compared to what most of us in the Access world would ever see. How did you get that estimate, by the way? Do you have an existing database or something?

I'm confidant the right version of either SQL Server or Oracle would meet your needs. Again, though, context is probably crucial to understanding which is "best". You'll not be using one of the free versions (such as SQL Server Express) , that's for sure. Check the specifications for those databases.

I'd recommend the most powerful computer you can afford. And as much RAM as you can afford. RAM will be more critical that than processor speed.

For more dedicated information, I'd recommend you visit SQL Server Central, and whatever forum(s) support Oracle.