Importance of location of installation of Microsoft SQL Server

I have a server with cheap slow disk and an expensive fast disk.

I want to use the expensive disk for all the things where it's important that it's fast, such as my databases.

To save money, I want to use the slow disk for anything where it doesn't make much difference whether it's fast or slow, such as backups.

Now, my question is, should I install my Microsoft SQL Server on the slow or the fast disk?

(To be clear, I'll place my databases on the fast disk no matter what, so my question only pertains to the location of the installation itself)

Solution 1:

This is kind of opinion-ish, but I would put the SQL Server binaries on the slow disk. It's quite common to put the binaries on the OS disk (although some people hate that), or on a slower disk.

You definitely want to remember to put your system databases, especially tempdb, on the faster disk, however. In fact, it's also common to put tempdb by itself.

This is in line with a couple of articles I found that might be useful to you.

There's also transaction log backups to think of, and I'm torn on this because you want the LDFs on the faster disk and you also want backups on a different disk from where the databases live, but it would be better if they were on a faster disk. You'll need to make a judgment call, but I would probably back up to the slower disk and complain about it. ;)

Solution 2:

I'd like to follow up on the pretty good answer Katherine Villyard already put up.

It somewhat depends on the intended usage of your database.
If you're expecting a lot of write operations, go ahead and put your .mdf and .ndf files on the faster disk.

If however your database is either one that is generally quite static (serving web content for example). And the queries don't vary much, chances are you'll get a large amount of the queries in your memory, or even cached on the application side. At which point you are better off using the faster disk for your .ldf, tempdb and backups.

Likewise, if you're expecting a lot of large queries, such as for an OLAP database, you're better off storing your .mdf, tempdb on the faster disk. And putting the .ldf on your slower disks as it won't often be part of the bottleneck.

In any case, don't bother with putting the binaries on the fast disk, we generally put those on a slow (not the system if it can be avoided) disk.
Also, don't get hung up on trying to get both the .ldf and .mdf files on the fast disk, generally they're separated whenever possible.

So in summary, review your load to see what will be your most likely bottleneck.

Solution 3:

You have things backwards. I know it's counter-intuitive, but you want the backups (especially including transaction log backups) on the fast disk, and the mdf/ldf files (with the notable exception of tempdb) on the slow disk.

You can think of it as if Sql Server keeps two representations of your data. The MDF+LDF files represent the current state of the database, while the backup (including transaction log backups since the last full backup) represent what you need to restore the current state of the database in the event of a failure. You want to keep these two representations separated from each other, so an event that destroys one representation will not also damage the other representation.

It turns out the Sql Server performance tends to depend a LOT more on how fast you can write transaction log files and their backups over how fast you can access mdf files. This means you need to strongly consider putting backups on the fast drive (ideally you would add a small SSD to the server that you can use for ldf files, to give them speed while still preserving separation from your backups). Unfortunately this leaves the slow drive for your MDF files, but again: it won't matter as much as you think.

It's worth noting the above assumes that you have sufficient RAM, that you follow typical workloads, and that you plan to use Full recovery mode, rather than simple. Additionally, the operating the system and installed Sql Server program itself can be placed on the slow drive, though of course you probably want as much as you have space for to live on the fast drive.