Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSD + HDD Server, where I install os, sql binaries, mdf, ldf and tempDB?


SSD + HDD Server, where I install os, sql binaries, mdf, ldf and tempDB?

Author
Message
js.cat
js.cat
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 27
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.
vlad_n200
vlad_n200
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 83
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
js.cat
js.cat
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 27
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,
vlad_n200
vlad_n200
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 83
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
Oliiii
Oliiii
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 777
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.
Abu Dina
Abu Dina
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 3323
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search