Avatar of gsswho6
gsswho6
Flag for United States of America asked on

SQL Server 2014 setup

Hello All,

First time setting up an SQL Server... Just want to make sure I get the install directories/etc correct... My current server has 3 paritions...  C: is a raid 1 (2x240gb ssd), D: is a raid 1 (2x240gb ssd) and an E: is raid 5 (4x240gbssd)....

Do i have the below correct? Anything I should change?

Data root Directory  = E:
User database directory = E:
User database log Directory = D:
Temp DB Directory = E:
Temp DM log = D:
Backup Directory = C: (assume I can put this on the root partition as its really doing nothing else besides OS?

Just want to make sure I matched those correctly? Any changes you think I should do that would be great.

Thanks,
Microsoft SQL Server

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Preston Cooper

Are you running a transactional database or a warehouse/analytics database (OLTP vs OLAP)?
ASKER CERTIFIED SOLUTION
Preston Cooper

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
gsswho6

ASKER
I am kinda stuck with the hardware that I put in my question. Are you saying creating more than 1 partition per RAID? IF so what partitions should be on each RAID? Dumb questions but why have multiple partitions on the same array? Wouldn't it be the same thing?

Which of those partitions can be on the same array? remember i has a raid 1(2), raid 1(2) and a raid 5 (4)... Also all 8 of these drives are SSD 240gb drives.
Preston Cooper

Separate the OS from the system tables because of a potential performance problem when OS drive could run out of space.  Separate data and log files from each other for performance reasons, and in case log files grow out of control and take up all available space.  Separate tempdb from everything just in case it grows out of control and for performance reasons.    Separate backups in case of out of control growth or unexepected growth and performance reasons as well.  You get them all on separate physical drives if possible.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
gsswho6

ASKER
So what you are saying is I am short of drives? I should get 4 more and do 2 more array and have 5 in total? I am not a fan of having single drives as fault tolerance is what i want. I was asking if I could get away with the 3 arrays i currently have.
Preston Cooper

Main Worries:
You have to separate OS from SQL Server Install Directory/System tables (could be same physical drive, but separate partitions
You have to separate Data and Log files onto separate physical drives (performance and unexpected growth)
If you place tempdb on your data or log file drives you run the risk of it growing unexpectedly (which is common)  
gsswho6

ASKER
OK thanks... I think that is what I will do... ill split my 2 raid 1's into 4 paritions and keep my big raid 5 for the DB. My database is really small at 30gb so space should not be an issue... SInce my raid 5 will be so big and my database is rather small would putting the log files with the data be ok?

I was thinking the below.

C: OS Raid 1 120gb partition
D: mdf files Raid 1 120gb partition
E: TEMP DB Raid 1 120gb partition
F: BACKUPS Raid 1 120gb partition
G: LOGS Raid 5 320gb partition
Z: DATA Raid 5 400gb partition

G: and Z: I assume i did the match correctly and would get a total 720gb from a raid 5 4 drive 240gb.

That all look good to you?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Preston Cooper

For D: I'm assuming you meant SQL Server install and system databases.  I try not to use D: for a hard drive letter because it usually gets assigned to a CD drive.  Other than that you are doing to best you can with what you have.
Preston Cooper

You might want to consider moving the G: drive to RAID 1 and moving the F: drive to your RAID 5.
gsswho6

ASKER
Z: is for the database

D: was just SQL install.
Your help has saved me hundreds of hours of internet surfing.
fblack61
gsswho6

ASKER
ok... SO below looks good?

C: OS Raid 1 120gb partition
D: SQL Install Raid 1 120gb partition
E: TEMP DB Raid 1 120gb partition
F: LOGS Raid 1 120gb partition
G: Backup Raid 5 320gb partition
Z: Database Raid 5 400gb partition
Preston Cooper

Try to keep the tempdb and LOGS on separate physical drives and that will be as good as you can do.
gsswho6

ASKER
Below look good? I will try to swing getting 2 more drives so i can dedicate logs and temp db to their own array... But if I cannot then ill go with the below.. Thanks again for the input.

C: OS Raid 1 120gb partition
D: TEMP DB Raid 1 120gb partition
E: SQL Install  Raid 1 120gb partition
F: LOGS Raid 1 120gb partition
G: Backup Raid 5 320gb partition
Z: Database Raid 5 400gb partition
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Preston Cooper

Consider RAID 10 instead of RAID 5 for your last array.  Think about the performance vs space you need.
Vitor Montalvão

I would let the MSSQL installation in the C: drive. What for would you spend 120GB for SQL Server binaries? Let 200GB to C: and you'll save 40GB from E:.

Also, how many days of backups do you want to keep? Isn't logical that your backup disk is smaller than the database. You said that your database is 30GB big so even you reserve 100GB you'll have enough free space for the next years. Here you can save 300GB from Z:.
So, to keep a week of full backups it will be necessary 210GB (30GB x 7days). Even that later you'll see that you don't need to execute full backups every day and you'll may need to schedule transaction log and differential backups and if possible with compressed option on, so those 320GB will be more than enough to store more than a week of backups.

Now the logs. This depends on which Recovery Model will you use?
If Simple Recovery Model then 120GB is too much for a 30GB database. Maybe 3GB (10% of DB size) will be more than enough.
If Full Recovery Model then the size it will depend on how many transactions by seconds will be handled by your database? I would set a default of 10GB (around 30% of DB size)  and you can tune this value later. So for a 100GB disk for data you should have 30GB disk for log. So saving 90GB for F:.

For tempdb the recommend size should be twice of the largest database. So, having a 30GB database you should have 60GB for tempdb. Anyway, counting with a 100GB data disk you should set a 200GB disk for tempdb. So, D: sould be increased in 80GB.

Review (I've changed the drive letters to a more logical letters - D for Datafiles, L for Logfiles and T for TempDB):
C: OS + MSSQL binaries --- 200GB
D: Datafiles -------------------- 100GB (Raid 5)
L: Logfiles -----------------------   30GB (Raid 1 or 10)
T: TempDB --------------------- 200GB (Raid 1 or 10)
X: Backups --------------------- 300GB

This will leave you with a lot of space in your disk solution so you may increase these values with the correspondent relation (Log=30% Data; TempDB=2xData; Backups=3xData).