Tempdb / sapdata files best practice - MS SQL 2012/2014

[#1]

Hi

We are migrating our BW system to MS SQL 2012 on windows.

Our approach:

C: OS + SQL Server Software

😧 SAPDATA files for BW ABAP & PORTAL (total 16 data files) + TEMP DB

E: Database Logs + Temp Logs
F: SWAP

Would like to know the following

- Placement of Tempdb, we are using flash drive, do we still need to place tempdb in a separate drive say T: drive only for Tempdb?

- Do we need to split BW ABAP Data files and Portal Datafiles in a separate drive? or they both can reside in the same drive?

-

I went thru the recent best practice guide, nowhere they have mentioned to place tempdb in a separate drive.

Your guidance is highly appreciated.

Thanks

[Former Member]

Hi Senthil,

Please refer to the below screenshot (source is the installation guide)

It's always advisable to have it on a different disk than data and log. Having it on same may cause issue later while replication.

Thanks,

Manas

[luisdarui]

If I would setup an On Premise SQL Server database for an Large Enterprise company, I would go for RAID10 for the data files. It offers 4x read speed against 2x of RAID5, better disk faulty tolerance (depending on which disks, it can be two instead of one). The downside is the 2x times for writing (RAID5 depends on the controller settings), but for the data files you don't write synchronously like the T-Log file, so it shouldn't be considered a "con" for RAID10, IMHO.

But if I had a choice, I would go for cloud instead of On Premise, of course!

Cheers,

Luis

[Former Member]

Thank you Luis, yes we are going for RAID 10.

I'm still wondering, how placing datafiles, tempfiles in a different drives gives good performance, when we already spread the data in LUN especially in a SSD.

Thanks

[Former Member]

Thank you Manas.

I read thru the  installation document, SAP speaks only about the LUN separation, I'm looking more about Drive separation.

Regards

Senthil

[Former Member]

Hi Senthil,

You already have the details for tempdb. For data drives I normally distribute them equally in different drives to have the best I/O.

You will also have to discuss this with your infra team as to how the drives are provisoned on the host, if they are coming from one datasource having one drive or multiple doesn't makes any difference as the actual datastore is same.

Thanks,

Manas