Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wardsan
Starting Member
48 Posts |
 Posted - 2004-06-29 : 07:31:24
|
| Is there any advantage in using multiple filegroups which are placed on SAN disks in a SQL Server database? Also, if there any advantage in having multiple files within a filegroup. I have a database of approx. 500GB and was wondering how I should configure the filegroups for this database. I assume user data and indexes should be kept in the same filegroup for recovery purposes?TIA |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-06-29 : 07:39:07
|
| Personally I'd try and split data and indexes across filegroups and keep seperate files so that they are of manageable sizes. Probably limit the files to the size of the disks in the array, though with a suitably intelligent SAN I imagine that would not make much difference.-------Moo. :) |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-06-29 : 07:39:23
|
| How are you connecting to the SAN - 1 card or more? I would put your log on a seperate drive, and try and get that connected on a seperate fibre card. The last time I spec'd something like this (also the first time :-) ) I seem to recall we were informed we could associate certain "drives" to certain cards, so we had a view that we wanted to move indexes to their own filegroup, and that after that, we could determine the usage of the fibre cards, and determine whether it might be valuable to have the log drive and the index drive using 1 card, and the data drive on the other. Sadly, I left before any of the further investigations panned out, so it was all just ideas.Why keep them together for recovery purposes ?CiaO*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-29 : 07:55:22
|
| It depends on the SAN you are using Wanderer. You want to spread the files out only if you are going to be utilizing multiple arrays in the SAN, which allow you to spread the I/O capacity out over more drives. In addition, some SANs have a "divided backplane", meaning you can find a sweet spot by having filegroups divided evenly across enclosures. In addition, depending on how cache and storage processors are managed, you can spread across multiple processors. What kind of a SAN do you have?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-06-29 : 09:11:25
|
| We had (I've moved on since then) an IBM Fastt900 and an EMC San (forget the model - it is in one of the threads here :) ).*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
wardsan
Starting Member
48 Posts |
Posted - 2004-06-29 : 10:57:28
|
| I thought it wasn't a good idea to separate data and indexes into separate filegroups because for recovery, they need to be restored as a single unit?? But how many filegroups do you think is needed and how many files within a filegroup? Limit the files to the size of a LUN?Tks |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2004-06-29 : 11:08:11
|
| I've not yet had to play with multiple files per filegroup - our's were 1 file under a file group. I am sure that some of the more experienced people may have some useful input on this. Regards recovery - depends on whether you are talking about SQL recovery, inc which case the additional files groups only impact the syntax of the restore database command, or some SAN or OS side recovery (?). *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
|
|
|
|
|
filegroups on SAN disks