Dell SSDs, RAID 10 and SQL Server

[iaind]

Piling ram into it could give better performance.... Or it might not, it entirely depends on the characteristics of the application. It won't do any harm sure, but that's too easy to think when you're spending someone else's money

 

[SkyTerran]

Many trolls in this thread

Intel's non-Sandforce 25nm consumer SSDs are perfectly capable of handling enterprise DBs. Buy as many as there are SATA ports on your server. When partitioning the drives leave 25% off to increase speed and lifetime (e.g. only create a 90GB partition on a 120GB drive). Put them in RAID1. I use separate RAID1 arrays for the OS installation, the transaction log and backups, that's not optimal for the DB performance, but simplifies management and disaster recovery. While SQL server writes a backup file it takes tons of locks, SSDs allow to decrease the backup time and performance impact.

 

[HungryHippos]

Yep, definitely trust your Enterprise class systems on Consume grade hardware. Good luck!

 

[Steviepunk]

I never claimed to be an expert on the subject, so didn't expect to know the ins and outs of it all, though most places I'd looked suggested that if you can have your DBs run from RAM then it was a good idea to do it. Some interesting information though, I'll be having a further read about some of this though, quite a lot to it all.
As for the server itself, I'm not looking into that now anyway, too much else on my desk at the moment to deal with that.

 

[SkyTerran]

Yep, definitely trust your Enterprise class systems on Consume grade hardware. Good luck!

And that's the reason all enterprise to this day runs nothing but IBM mainframes ...

 

[iaind]

I never claimed to be an expert on the subject, so didn't expect to know the ins and outs of it all, though most places I'd looked suggested that if you can have your DBs run from RAM then it was a good idea to do it. Some interesting information though, I'll be having a further read about some of this though, quite a lot to it all.
As for the server itself, I'm not looking into that now anyway, too much else on my desk at the moment to deal with that.

Really, it's overkill 99.9% of the time to have them 100% in memory. How much of the data is actually active most of the time? You've been given enough pointers to go away and properly figure out the right way to scale this - you can either take it, do your research and do it properly or you can just throw specifications at it and hope for the best.

And that's the reason all enterprise to this day runs nothing but IBM mainframes ...

And you had the audacity to claim others were trolling

 

[Skidilliplop]

I never claimed to be an expert on the subject, so didn't expect to know the ins and outs of it all, though most places I'd looked suggested that if you can have your DBs run from RAM then it was a good idea to do it. Some interesting information though, I'll be having a further read about some of this though, quite a lot to it all.
As for the server itself, I'm not looking into that now anyway, too much else on my desk at the moment to deal with that.

Sticking the DBs in RAM doesn't fix anything. Yes the parts of them held in RAM are going to be faster but heed the following:
SQL doesn't load entire databases into RAM because the RAM is there, if data isn't being hit hard it doesn't always remain in RAM. It's up to SQL how it manages this, don't assume it'll treat it as a dedicated RAM Disk.
You STILL need a disk subsystem that can keep up and write back the changes made in RAM to storage, else if the server crashes you lose everything that's in still held RAM and hasn't been written back to disk. This is why RAID cards come with battery backup for the cache. If as little as 256MB of cache is worth protecting...how bad would it be to run 100+GB in un-backed volatile memory?

 

[iaind]

Sticking the DBs in RAM doesn't fix anything. Yes the parts of them held in RAM are going to be faster but heed the following:
SQL doesn't load entire databases into RAM because the RAM is there, if data isn't being hit hard it doesn't always remain in RAM. It's up to SQL how it manages this, don't assume it'll treat it as a dedicated RAM Disk.
You STILL need a disk subsystem that can keep up and write back the changes made in RAM to storage, else if the server crashes you lose everything that's in still held RAM and hasn't been written back to disk. This is why RAID cards come with battery backup for the cache. If as little as 256MB of cache is worth protecting...how bad would it be to run 100+GB in un-backed volatile memory?

Exactly what I was going to try to explain. Memory will help with reads after the first subsequent read, but not much else. How much that actually will happen is dependent on the DB and absolutely wont be the entire DB. The stats are all available to be able to forecast the optimal memory usage.

This all starts to go out the window is SSAS coexists on the same box, however...but hopefully thats not the case and there are ways to tune that too

 

[Steviepunk]

Really, it's overkill 99.9% of the time to have them 100% in memory.

Wasn't saying I didn't believe you, was just saying why I had thought the RAM helped.

anyway, as I said, although I'm not need to spec the server now, I'm still interested SQL Server, so will do some more reading on the best ways to set it up.

 

[dmsims]

Current server is short of Ram

RAID5 on a SQL server ???

Have you spent some time looking at what you app actually does (hopefully you won't be starting at record 1 and doing sequential reads until you find a matching records(s) - that never happens)