Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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,
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,
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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/
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/
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.
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.
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.
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.
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)
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?
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?
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.
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
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
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
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
Consider RAID 10 instead of RAID 5 for your last array. Think about the performance vs space you need.
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).
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).
Premium Content
You need an Expert Office subscription to comment.Start Free Trial