Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSD + HDD Server, where I install os, sql binaries, mdf, ldf and tempDB? Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 6:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:53 AM
Points: 2, Visits: 25
Hello, my company has a new Server:

64 GB RAM
2 x Intel 320 SSD 120GB RAID1
2 x SATA3 1TB RAID 1

We're going to use this server for our SQL Server 2012 production database only. It's an small 10GB database but high workload.
I would like to know wich is the best placement for OS, SQL Server 2012 binaries, MDF, LDF as TEMPDB in this Hardware.

Thanks.
Post #1444352
Posted Tuesday, April 23, 2013 3:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 13, 2014 1:19 AM
Points: 4, Visits: 75
Hello,
I would recommend the following scenario:
• Restore the production database on the testing server and simulate the similar workload, make sure that tempdb and a copy of the production database located on the same drive
• In the Windows Performance monitor create ~24 hours trace and capture Disk reads/sec and Disk writes /sec counters, save results as tsv (for example). Open the trace in the excel file and calculate average and stdev values for both counters. Let’s assume you have avg+stdev ~ 300 Reads/sec and ~300 Writes/sec
• Now you can calculate the storage.
Use the formula: (#Reads + #Writes* Penalty)/ # physical drives = drive throughput (I assume ~120 IOp/sec on SATA ; ~ 200 IOp/sec on SSD, difficult to find the SSD parameters)
RAID penalty on RAID 1 =2
On the 1st RAID 1, based on SSD (300 Reads/sec + 300 Writes/sec*2) / #physical drives =200 => need ~4.5 drives in RAID 1 , in real configuration =>6 drives
On the 2st RAID 1, based on SATA (300 Reads/sec + 300 Writes/sec*2) / #physical drives =120 => need minimum 7 drives in RAID 1 (8 drives)
Afterwards on Production measure the actual data with the performance monitor – use Avg Disk Sec/read and AVG Disk sec/write and check the 50 ms threshold
Both RAID 1 arrays , based on 2 physical drives, have a very limited capacity , around 100 reads/sec and 150 writes/sec.
On one of the arrays you have to install the OS and SQL server binaries, one the second the database files. Maybe you can combine the database logs and OS on SSD drives and database files on SATA, but first of all you have to estimate the load.

Best Regards,
Vladimir
Post #1445323
Posted Wednesday, April 24, 2013 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:53 AM
Points: 2, Visits: 25
Thank you very much Vladimir,
I will try to do all of this work. But which is the "theoretical best placement" for the files, according I already have the OS installed on the HDD.
I think the best I can do is:

HDD:
C:\ OS, SQL Sever binaries and other soft.
D:\ TempDB (in a different logical drive to avoid an uncontrolled growth crashes the OS)

SSD:
S:\ MDF and LDF. Formatted at 8Kb or 64kb?

What do you think? Or is better to keep MDF alone on the SSD, and put LDF on another logical drive E:\ in HDD (because of almost same sequential speed of SSD, and no volatility)?

Thank you again,
Post #1445897
Posted Thursday, April 25, 2013 8:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 13, 2014 1:19 AM
Points: 4, Visits: 75
Hello,
It's not easy because you have very limited options; the theory is simple - separate the sequential and random I/O, 64 KB formatting for MDF and default formatting for LDF.
I would recommend to split the MDF and LDF files of all databases including tempdb, it sounds me more important than separated location of the tempdb database.
C:\ OS, SQL Server binaries
D:\ MDF files + 64 KB formatting (not sure about the formatting, because I haven't used before the Windows logical partitions with different formatting options. The OS is already installed, may be better to try OS and LDF on SSD with 8 KB formatting and MDF files on the entire HDD drive with 64 KB formatting ). To avoid an uncontrolled growth of tempdb database you can restrict the size of the database and control the database size on the daily basis.

S:\ LDF files +8KB formatting. (I still have some concerns regarding the OLTP databases on SSD solutions. During the last month I have tested the performance of EMC VNX machine which has the mix of SSD and normal drives.I was very impressed with the reading performance of the storage, but I was not happy with the writing performance, maybe because of the preinstalled RAID6; finally we have asked the EMC support to help us to reconfigure the storage. )

And do the load test on your system

How about the backup strategy and backup locations? Are you planning to use the external or local location?

Thanks,
Vladimir

Post #1446537
Posted Friday, April 26, 2013 7:57 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:21 AM
Points: 206, Visits: 764
I would say put the DATA and LOG on the SSD (including tempdb), os on C:\ and binaries on D:\.

Backup can be done to the HDD or SSD depending on how fast it has to be done (HDD should be good enough unless you need 100MB/s+ backup speed).

SSD have incredible perf, you should not waste it by creating a bottleneck with DATA or LOG on a HDD.
Post #1447011
Posted Friday, April 26, 2013 8:21 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
js.cat (4/19/2013)
Hello, my company has a new Server:

64 GB RAM
2 x Intel 320 SSD 120GB RAID1
2 x SATA3 1TB RAID 1

We're going to use this server for our SQL Server 2012 production database only. It's an small 10GB database but high workload.
I would like to know wich is the best placement for OS, SQL Server 2012 binaries, MDF, LDF as TEMPDB in this Hardware.

Thanks.


If high work load means lots of writes to disk do you really want to use SSD for that?! I wouldn't


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1447027
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse