Disk Separation DB installation and data files

Question

I am wondering whether it is a good idea to separate the database installation media from the actual data files.

In our current environment we have:

1. C: - Windows Server
2. D: - MS SQL Installation & DB Data Files
3. E: - Backups
4. L: - Log files

Would it be a good idea to separate out our D:

• Drive 1: MS SQL installation
• Drive 2: DB Data Files

There are similar questions already posted, but nothing specifically relating to the separation of DB software and DB data files. (similar questions ask whether it is a good idea to separate OS and DB).

Answer 1

Why do we separate things onto different disks?
Performance and resilience.

From the Disaster Recovery perspective, if you lose any one of your disks, how much damage does it do?

Lose the operating system disk? OK, that's a biggy. :-(

Lose the backup disk? Non-event; you still have a running database instance.

Lose the data disk? Ouch! But that's OK; you've got your backups ... You just need to restore them into a running SQLServer instance ...

Ah.

But you had that on the same [data] disk that just failed, taking the data with it!

Keep the installation and data separate.

Whilst it's only semantics, I'd suggest keeping a closer "association" between the Data and Logs disks; they make up all the stuff you really care about.

Move the backups off to another server altogether. (Whilst it's unlikely these days) If all these disks are local to the machine and the machine's motherboard were to fail, it wouldn't matter how many disks you had; they'd all be gone!

Answer 2

Backups and Tempdb are 2 points i think you need to be re-considering. The below is what we use in our environment.This works perfectly with all major best practises being implemented.

1. Disk 1 (C) : System files (root)
2. Disk 2 (D) : System databases (except tempdb)
3. Disk 3 (E) : User database data files (mdf,ndf)
4. Disk 4 (F) : User database log files (ldf)
5. Disk 5 (G) : Tempdb (with number of files according to the best practises)

We do not take backups locally which is actually a bad idea considering a server crash beyond recovery. We use Ola hellengren solution and backup to a Global network location where the retention is taken care.

Keeping tempdb is a separate disk will bring the best performce out of it.

Below is a snippet from the storage best practises :

Separate and prioritize your data among disks Ideally, you should place the tempdb database, content databases, Usage database, search databases, and SQL Server 2014 (SP1), SQL Server 2016, SQL Server 2017 RTM, SQL Server 2008 R2 with SP1 and SQL Server 2012 transaction logs on separate physical hard disks. The following list provides some best practices and recommendations for prioritizing data: When you prioritize data among faster disks, use the following ranking: Tempdb data files and transaction logs Database transaction log files Search databases, except for the Search administration database Database data files

Please use this link for a better understanding :

Top best practises from technet for storage : below para is from the tempdb best practise.

Consider configuration of TEMPDB database Make sure to move TEMPDB to adequate storage and pre-size after installing SQL Server. Performance may benefit if TEMPDB is placed on RAID 1+0 (dependent on TEMPDB usage). For the TEMPDB database, create 1 data file per CPU, as described in #8 below.