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,
gsswho6Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Preston CooperDatabase AdministratorCommented:
Are you running a transactional database or a warehouse/analytics database (OLTP vs OLAP)?
Preston CooperDatabase AdministratorCommented:
Separate Drives (partitions need to be aligned and 64k allocation block sizes).  RAID 10 if you can, RAID 1 for logs if you can't. RAID 5 for data if you must or if OLAP database.

Partition Alignment Article 1024 KB starting offset, and a 64 KB block size https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx http://www.questiondriven.com/2014/07/24/partition-alignment-for-sql-server-or-exchange/  http://www.midnightdba.com/Jen/2014/04/decree-set-your-partition-offset-and-block-size-make-sql-server-faster/
C: drive for OS  (100GB – 128GB)  (This drive does not need to be 64K block size)
E: At least 1 drive for SQL Server install and system tables
F: At least 1 drive for mdf files
G: At least 1 drive for Tempdb
L: At least 1 drive for log ldf files
Z: At least 1 drive for Backups

http://sqlmag.com/storage/sql-server-storage-best-practices
http://www.questiondriven.com/2015/06/12/microsoft-sql-server-dba-setup-checklist-and-recommendations/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gsswho6Author Commented:
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Preston CooperDatabase AdministratorCommented:
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.
gsswho6Author Commented:
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 CooperDatabase AdministratorCommented:
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)  
gsswho6Author Commented:
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?
Preston CooperDatabase AdministratorCommented:
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 CooperDatabase AdministratorCommented:
You might want to consider moving the G: drive to RAID 1 and moving the F: drive to your RAID 5.
gsswho6Author Commented:
Z: is for the database

D: was just SQL install.
gsswho6Author Commented:
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 CooperDatabase AdministratorCommented:
Try to keep the tempdb and LOGS on separate physical drives and that will be as good as you can do.
gsswho6Author Commented:
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
Preston CooperDatabase AdministratorCommented:
Consider RAID 10 instead of RAID 5 for your last array.  Think about the performance vs space you need.
Vitor MontalvãoMSSQL Senior EngineerCommented:
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).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.