July 6, 2010 at 2:55 pm
Does SQL 2005 have the ability to allocate database space across more than one LUN? What if I had 4 Terabytes of SAN storage (2 allocations of 2 Terabytes each); do you know how to configure SQL 2005 to spread storage across both LUNs?
We are considering putting our docs directly into the database, and this is one of our research items that we wanted to check.
July 6, 2010 at 5:15 pm
SQL doesn't have any way of directly addressing the SAN.
You would need to create multiple file groups, at least 2 so you have one on each LUN. And you would need to make sure that the drives presented to the server are actually on the 2 LUNs. Then you would need to create the tables so they are on the various file groups.
In general its easier to manage distribution of the database across spindles from within the SAN than from within SQL.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 6, 2010 at 7:08 pm
Leo, I believe you don't have to use a different filegroup to add a file on a different LUN. You can have multiple files per database - with each file on a different LUN in the same filegroup.
Or, you can split the storage across different filegroups which may or may not be on a different LUN.
With your method, you control what goes in each filegroup. One option here is to have non clustered indexes on the second filegroup and everything else on the primary. Or, you could separate out the images (documents) onto a separate filegroup (LUN).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 6, 2010 at 7:54 pm
Jeffrey Williams-493691 (7/6/2010)
Leo, I believe you don't have to use a different filegroup to add a file on a different LUN. You can have multiple files per database - with each file on a different LUN in the same filegroup.
Hi Jeff, you are correct, you can do it this way. The only issue you have is that my reading of BOL is that SQL uses the files within the file group sequentially, so there is still no real way to control "spreading the load" across LUNs by having multiple files in a file group. You also still come back to needing multiple file groups and putting Data, Text, Image, indexes into seperate file groups as this is the level SQL gives the administrator to determine the data location.
Having said that, using multiple files within a file group, and assining these to different LUNs is still a good idea, particularly to try improve read times.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 7, 2010 at 1:43 pm
Nope - the only files that are written to sequentially are the log files. If you have multiple files in a single filegroup, they will be written to using a proportional fill algorithm. If all files are the same size, this will be equal across all files. If you have one file that is larger than the other(s) - that file will be written to more.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2010 at 4:30 pm
Jeffrey Williams-493691 (7/7/2010)
Nope - the only files that are written to sequentially are the log files. If you have multiple files in a single filegroup, they will be written to using a proportional fill algorithm. If all files are the same size, this will be equal across all files. If you have one file that is larger than the other(s) - that file will be written to more.
just to clarify, as it is proportional fill any file that has MORE empty space in it will be written to more, this will not necessarily be the largest file
---------------------------------------------------------------------
July 7, 2010 at 4:46 pm
Actually I remember going over this rercently and then when I replied to the post my brain search algorithim crashed and all it could find was the bit about log files being sequential.
A sure sign I'm working too hard.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 7, 2010 at 7:44 pm
george sibbald (7/7/2010)
Jeffrey Williams-493691 (7/7/2010)
Nope - the only files that are written to sequentially are the log files. If you have multiple files in a single filegroup, they will be written to using a proportional fill algorithm. If all files are the same size, this will be equal across all files. If you have one file that is larger than the other(s) - that file will be written to more.just to clarify, as it is proportional fill any file that has MORE empty space in it will be written to more, this will not necessarily be the largest file
Yeah - I wasn't clear enough on that. Thanks for the clarification.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply