June 11, 2016 at 10:57 am
Hi,
Could you please share your guidance on the following SQL exercise?
Objective: Using SQL Server 2014 Enterprise Edition, I plan to implement a 17TB db on a single Windows Server 2012, over 5 drives 4TB each.
Challenge: As far as I can tell, in order to spread the db over several drives, I have the following two options;
1)Use the Windows OS Spanned Volume functionality, essentially the OS layer reports the 5 - 4TB drives as a single drive 20TB drive to applications such as SQL server
-Or-
2)Use the SQL's 2014 FILEGROUP functionality, thus, assigning tables or indexes to different FILEGROUPs and then placing the files for those FILEGROUPs on different physical drives
Which of the above options would you recommend? are there other Windows & MS SQL options to reach the objective?
Regards,
-r
June 13, 2016 at 9:49 am
The first question i would like to know is why do you want to spread the db across multiple volumes? If you are thinking you would gain peformance that is not always true.
That being said, i have a 20 TB which is spread across many different types of volumes. And the reason i have the way it is because there are some tables that doesn't need to be on the fastest storage, so created a new filegroup and added bunch of files to that filegroup and the files are placed on slower drive. The other reason why i split into so many files is because to get more IOPS out of the SAN, this is a very unique type of SAN which had set threshold on IOPS on a given volume, so to get more IOPS i had to create more files, with each file on dedicated.
Not sure if this applies to your case .
June 13, 2016 at 1:49 pm
I really like what UDP shared. Definitely performance focused. I get the sense from the original questions that indexes and tables will be going on different drives. If that is the end goal, just be careful with that. Managing growth and the drives in the future could be problematic. If the IOPS on the drives is going to be the same then I would be hesitant to move partitions to specific drives (via filegroup or whatever).
Although not guaranteed to help performance I have found that on lower versions of SQL that I can help it with evenly spreading data across drives by using multiple files in a filegroup...not multiple filegroups for a database.
June 13, 2016 at 1:57 pm
Couple thoughts on this one, leaving aside what curious_sqldba stated.
First, are the disks Direct Attached Storage (DAS) or something else? If they're DAS, why wouldn't you be using a RAID controller to stripe the drives?
Which bring me to my next point, if you're planning to have a 20TB volume, using 5x 4TB drives, you have no physical redundancy. What happens when one of the drives fails? If you're using OS striping, the entire volume goes down, if you use Filegroups everything in the affected Filegroup(s) goes down (or the entire DB goes down if the Transaction Log is on the failed drive.)
On the purely physical side of the setup, I'd look at something that's going to give you redundancy, either RAID10, or at least RAID-5 or -6.
June 14, 2016 at 11:37 pm
Hi curious_sqldba,
I am spreading the db, not for performance gain. Since the disks that I have are 5TB in size, then I will need x number of drives to accommodate for the physical size of the db.
thanks,
-r
June 15, 2016 at 12:44 am
Many thanks UDP Broadcaster,
After reading your response, it reminded me that a viable alternative is to use RAID 0 (stripping) or JBOD.
Cheers,
-r
June 15, 2016 at 5:45 am
raf.figueroa (6/15/2016)
Many thanks UDP Broadcaster,After reading your response, it reminded me that a viable alternative is to use RAID 0 (stripping) or JBOD.
Cheers,
-r
I think you might have misunderstood me. I would not consider or suggest RAID-0 or JBOD for a database. If one disk fails, your *ONLY* recourse to get the database back up and running will be to restore from a backup.
Look into RAID levels that offer redundancy (RAID-5, RAID-6, RAID-10) Yes, your initial costs will go up (you'll need additional drives, the RAID controller price, etc,) but now you begin to get a safety net in the event of a drive failure.
JasonA
June 15, 2016 at 10:15 am
raf.figueroa (6/14/2016)
Hi curious_sqldba,I am spreading the db, not for performance gain. Since the disks that I have are 5TB in size, then I will need x number of drives to accommodate for the physical size of the db.
thanks,
-r
If that's the case striping at OS level is your best bet, having multiple FG's can be a maintenance headache.
June 15, 2016 at 10:37 am
My vote is never, never combine the drives unless this is a LUN from some storage system. You can have multiple files in one filegroup or multiple filegroups, but manage this yourself. Especially if you have 17TB in a db and only 20TB in storage. At some point you'll add another drive and it's easier to get a new file or filegroup added.
Now, on the decision for files or filegroups, the advice from curious_sqldba is good. Think performance here and the access to data v the types of drives.
DO NOT ignore what Jasona.work notes. You need redundancy, unless you are moving backups (full and log) to other drives. BTW, you do have space for backups somewhere separate from data, right? Make sure you plan for this.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply