RAMdisk for a virtualized SQL 2008 Enterprise server

Hi everyone,

We're currently virtualizing our database server and the idea is to use a ramdisk to help optimizing its performance. What we have is an instance of MS SQL 2008R2 Enterprise edition, hosted on a 2008R2 server, virtualized into the VMware ESXi environment.

We are looking for suggestions as to what solutions are available and most suitable for our set-up. I have been give the following advice, but no tips for an actual product:

Firstly the software should be an industry level app, no desktop stuff. In practice what you looking for must be designed to run on a server

Secondly, it should be able to control the properties/settings of the actual disk and the way it transfers the data further.

It is also about the size of the database. In reality you should make it happen so that the database is loaded into the RAMdisk during the server startup. You run it from the RAMdisk and only synchronize it every let’s say 60 seconds with the physical drive (in the background). Logs should in this case be on the physical drive and the database should be in the “full recovery mode”. So even if something breaks, then after reboot the database will repair itself in accordance with the saved log, to the point of the last transaction.


We would really appreciate your help on this.

Thanks.
Martin AndelSenior IT AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
Martin AndelSenior IT AnalystAuthor Commented:
Do you use it on a virtualized or a physical server? And for what purpose? Database?
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
Both physical and virtual, databases, development, rendering, profile storage for terminal servers, for fast retrieval

a free trial version is available for you to try with a small ram drive.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Martin AndelSenior IT AnalystAuthor Commented:
And how customizable is it? Doest it offer any control over the disk properties and data flow towards the physical storage? This is extremely important for us as the guest OS will be hosted on purely ssd based storage and we want to use the ramdisk mainly for reduction of its wear & tear.
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
There are options to load the ramdisk at the OS start, and regularly save the ramdisk to disk.

I would recommend you download and test, and check out if it meets your requirements.

We are users of the product and we are very happy with it, it meets our requirements.
Martin AndelSenior IT AnalystAuthor Commented:
That sounds good! I've also read a lot about the solution below, as being very good performer and very customizable. Any experience with that product?

http://www.romexsoftware.com/en-us/primo-ramdisk/index.html
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
I'm afraid not.
Martin AndelSenior IT AnalystAuthor Commented:
Thanks for you help!
ste5anSenior DeveloperCommented:
The performance key with SQL Server is that the database fits into memory (hot cache).

I don't see the benefit of using a RAM-disk instead of a SSD for the physical database files. Cause the RAM must be server-local to benefit of its performance. So I don't see why you're not just giving your SQL Server enough RAM.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin AndelSenior IT AnalystAuthor Commented:
Because according to all the research I've done so far, the SQL server does so much reading and writing in small pieces, that the ssd's wear down pretty quickly. The idea behind using the ramdisk is that all most of the r&w is done in ram space and then sent as large chunks to be written to the ramdisk image on the actual ssd. My point is that I do not want to have to buy new drives every few months.
ste5anSenior DeveloperCommented:
How many data is written per day really to your database?

About reading: doesn't matter. As I said give your SQL Server enough memory to fit the database into memory.

The differences I know: Using SSDs instead of HDDs gives you faster times to a hot cache. Having the normal workload in memory, this will only affect the users at the beginning of the day. Or after a drop clean buffers. Where you can see a diffence: Using SSDs for tempdb. Cause here are physical objects created and must be written to disk. So this gives normally a good speed up. So I would guess this would be also the only area where you may see a performance gain with using RAMdisk.

Here's a last thought: Why not using an hybrid storage array with an large enough write-cache?
Martin AndelSenior IT AnalystAuthor Commented:
What do you think would be a  sufficient amount of write-cache?

And would that be the cache of the raid adapter, or a separate (ssd?) disk dedicated to acting as the cache?
Martin AndelSenior IT AnalystAuthor Commented:
Btw, do you have an actual experience of running the database on ssd's only (in production environment)?
ste5anSenior DeveloperCommented:
I would go for an out-of-the box product. The necessary amount depends imho on your workloads.
Martin AndelSenior IT AnalystAuthor Commented:
Sorry, I don't follow. Out-of-the-box product for what now? A caching solution or a ramdisk?
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
We use SSDs in our SAN with no issues, and have SSD caching solutions with no issues, of wear!
ste5anSenior DeveloperCommented:
Yup, but I'm a database guy, so maybe you need a virtualization hoshi instead. Or a storage gremlin ;)

I'm currently running SSDs in one physical system as tempdb storage. Here I have more write I/O compared to the database and log volumes. But the SSDs are still healthy (running 2 years).

And as a database guy I need to say: Setup a test system to measure it. It depends too often on your concrete workload.

But the most performance with SQL Server is gaind from hot buffers. Use SSDs speed up everything which needs large temp storage, like DW-ETL tasks.
ste5anSenior DeveloperCommented:
Out-of-the-box hybrid SAN.
Martin AndelSenior IT AnalystAuthor Commented:
With the small database throughput such as ours, there is no gain in using a ramdisk. Providing the SQl server with enough ram should do the trick, while the wear and tear of ssd based storage will be minimal anyway.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.