August 11, 2011 at 8:05 am
Hello!
I recently 'inherited' a database (with a new job as a newbie DBA) on a virtual server; there are two logical drives on the machine; the C: drive for the OS and program files and the D: drive for the database files (including the log files and tempdb - I know, I know, eye roll.) Most of what I've found in searching does not refer to virtual machines and assumes access to the underlying disk structure. 🙁
I have no access to drive structure of the underlying physical machine where the virtual server resides. (We are using a commercial host.) It is a RAID array but I'm not sure what the specs are beyond that.
Is there a benefit to having multiple files in a single filegroup(or multiple filegroups) since I can't specify the storage to physically be on a different drive/driveset?
From what I'm reading (and in one case on a similar thread here), the answer is that there won't be any benefit other than the ease of moving a file or filegroup to a new drive/driveset if one becomes available, but I wanted to make sure that I am understanding correctly.
That being said, if I am going to be redesigning significant portions of the database anyway, and I know there will need to be additional hardware assigned to this database in the future, would it be wise for me to move to multiple filegroups for ease of transition to additional hardware?
I will be starting a class on SQL Server management and I've started on the textbook to get ahead. The author recommends a filegroup (containing one file) for the system objects, and an additional filegroup which will be set to default to contain all other objects. The reasoning was that (I think) it would decrease the chance for the system objects to be corrupted. This textbook is great for saying "do this" but not explaining why. Does anyone have any thoughts on whether this is a good practice or not?
Thanks so much for any replies!
August 11, 2011 at 8:17 am
In many corporate environments you don't really have access to the drives directly either, since you are connected to a SAN. Virtual or not..
However, the statement that there are NO benefits to additional file groups is incorrect.
I can think of at least one case that simply doesn't matter to me what the underlying structure is. That would be tables that contain BLOB data. My practice is to ALWAYS build those tables with TEXTIMAGE_ON a different file group than the rest of the data.
You might not gain much in terms of performance but it is still a good practice.
CEWII
August 11, 2011 at 8:43 am
Additional filegroups also comes in handy for partial backups and segregating read/write information from read only information.
So don't assume that because everything is on the same logical drive that prevents filegroups from being useful.
August 11, 2011 at 8:49 am
Ah excellent - I was wondering if my lookup tables (which change very infrequently, but are not exactly read only) also should go in a separate filegroup from the 3 or so tables that change frequently.
Also, the only thing I'm assuming at this point is that I have a lot to learn about SQL Server 😉
Thanks again!
August 11, 2011 at 8:52 am
I have found when dealing with VM's. Find a good size for the log files to cut down on the growth of the log files and that usally has a good performance increase. The only thing is when dealing with VM's everything could help or hurt depending on the enviroment. All of my servers are in VM's and sometimes it is trial and error to get it right.
Do you have the ability to get more Vdrives? If so can you have them set the raid to a different level? There is so many things that you may not have direct access to, but can have influance on what is set up!
August 11, 2011 at 10:55 am
I believe they can set up additional virtual drives.
I'm not sure about the RAID.
I know there had been discussion that if the project did explode in size (which it has the potential to - right now, it's a 'pilot' of data from 5 sites which has the potential to expand to possibly 400) that we'd move to a dedicated physical server (possibly more than one). And, I think redesigning, while not fun, will be much better with 5 sites using the system than with .. well, any more, but certainly 20 or 100 or...
Since I hopefully will be able to implement some redesign of the database, I'd like to make sure I set the groundwork in place for optimization, and also scalability if it comes to adding more (and more, and more :-)) hardware.
From what I'm reading, it would be super-fantastic to get the logs and the tempdb off the same drive and give them their own designated space. Would this be a benefit even if it's a different virtual drive if the underlying hardware stays the same?
I didn't realize that the DBA 'crash' course would include a hardware/storage crash course (and virtual server crash course), but it makes sense. (What? Big picture?)!!!
August 11, 2011 at 12:57 pm
Lisee (8/11/2011)
I believe they can set up additional virtual drives.
I'm not sure about the RAID.
I know there had been discussion that if the project did explode in size (which it has the potential to - right now, it's a 'pilot' of data from 5 sites which has the potential to expand to possibly 400) that we'd move to a dedicated physical server (possibly more than one). And, I think redesigning, while not fun, will be much better with 5 sites using the system than with .. well, any more, but certainly 20 or 100 or...
Since I hopefully will be able to implement some redesign of the database, I'd like to make sure I set the groundwork in place for optimization, and also scalability if it comes to adding more (and more, and more ) hardware.
From what I'm reading, it would be super-fantastic to get the logs and the tempdb off the same drive and give them their own designated space. Would this be a benefit even if it's a different virtual drive if the underlying hardware stays the same?
I didn't realize that the DBA 'crash' course would include a hardware/storage crash course (and virtual server crash course), but it makes sense. (What? Big picture?)!!!
As i said it well be a trial and error for you. The VM makes it real easy to change things in the enviroment. As for this "to get the logs and the tempdb off the same drive" will not be the same as a physical server. Your drives will be virtual. One thing that can help is to make sure you have a raid 10 or 1 for Vdrives for temp and log files. What is your Virtual enviroment VMware or something else? Depending on that try and find as much info on white paper testing with that Virtual enviroment and sql server.
Here is a link on VMware and SQL Server:
http://www.vmware.com/files/pdf/perf_vsphere_sql_scalability.pdf
August 11, 2011 at 1:09 pm
Thanks again for the info and for pointing me in the correct direction for more information!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply