July 14, 2010 at 3:31 pm
Need some ideas in best practice on how to best set up my DW box. Both Staging / ODS and Dimensional DW are in the same box, and they are only there to support ETL to the final SSAS cube on a separate server. DW are typically not user accessible (for now)
I typically set up all my data files in RAID 10 groups, but I am entertaining the idea to have 2 RAID1 instead of 1 RAID10 drive, and physically split the data across (either the big tables over, or index to a different drive, or something).
I wonder if that would HELP the performance more, due to less contention... or HURT performance more due to lack of RAID0
The server I have had 16 drive bays, and we want at least RAID 1 in redundancy (so at most 8 logical drives)
so I am thinking
c: drive OS / APP (RAID 10?)
d: Staging / ODS data (RAID 10?)
e: DW data (RAID 10?)
f: Index (won't have enough bay to do index drive if i do RAID 10 on d: and e:)
g: Transaction Log (RAID 1)
h: tempDB (RAID1)
Alternatively, fold tempDB or something into C: drive as well, then I will have enough drive for index
Not sure what are the performance comparison between different configurations....
Any idea?? thanks!!!
July 14, 2010 at 6:14 pm
boxta (7/14/2010)
c: drive OS / APP (RAID 10?)d: Staging / ODS data (RAID 10?)
e: DW data (RAID 10?)
f: Index (won't have enough bay to do index drive if i do RAID 10 on d: and e:)
g: Transaction Log (RAID 1)
h: tempDB (RAID1)
Will you be able physically separate these arrays?
No need for RAID 10 on the OS array. Mirroring is adequate there.
Depending on size of disks, I would probably also consider combining D and E into one array.
I would also consider renaming the Index array to Data2. Depending on your database usage patterns it may be advisable to separate tables into different filegroups and keep indexes for those tables with the tables in the respective filegroup.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 14, 2010 at 6:18 pm
probably 300GB SAS disk, so 600G for each RAID 10 array?
ODS and DW are only about 150GB each now.. so tons of room for growth...
Won't it be advisable if I separate D and E out?? So that I know for sure Loading DW (E) from ODS (D) (for example), there will be no contention whatsoever?
And what's the best practice on file group? I am running out of drive bays (as it's direct attached)... I think it's probably more advisable to first seperate all these (log, data, tempdb, OS, etc) to its own physical location first, before i further spread it out to different file group???
But after separating all those out to its own physical location, i won't have enough disk to do multiple filegroups
thanks for any idea
July 14, 2010 at 6:25 pm
OS drives are typically separate drives for the DAS bays.
You may even want to consider using RAID 5 for the ODS and logs.
It will largely depends on usage patterns, bottlenecks and acceptable risk. As explained by our EMC rep, you get marginal performance improvement, better protection against drive failure but loss of disk space for RAID 10. If you can risk two drives going down in a RAID 5, then you may want to explore that option in order to save on drive bays and permit additional arrays.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply