June 5, 2009 at 5:47 am
I know about files and filegroups; filegroups are containers to organize files, is basically what I read. But occasionally I read something about using Filegroups to spread your files over different disk(array)s. I think you can do that easily enough with the seperate files.
Is there more to Filegroups then making it easier to organize large number of files?
Greetz,
Hans Brouwer
June 5, 2009 at 8:53 am
They aren't really a whole lot more than that.....But...keep in mind that a db's metadata is kept on the [primary] filegroup and you may consider having all user created db objects on seperate filegroups for ez backup and restore.
There are specific scenarios where filegroups can really help you out::::
If I have tables 1-4 and I can predict the growth of these tables over the next 4 years
and then I have tables 5-8 and their growth cannot be determined.
It may be beneficial for me and my 8 table database to create 1 filegroup for each set of tables.
This way I can predict what tables belong to what files and I can set autogrowth off for files on the 1st filegroup for tables 1-4 (This helps read and write performance).
And I can set autogrowth on for the filegroup containing tables 5-8.
Organination is more than meets the eye.
June 6, 2009 at 9:10 am
Villersk, but do you set autogrow per FILE or FILEGROUP? It's clear what you say, but I still have the impression FILEGROUP is a convenience, not a neccessity.
Tnx for answering tho.
Greetz,
Hans Brouwer
June 6, 2009 at 10:11 am
Partial database availability (Enterprise edition only), read-only filegroups.
You can put files onto different drives, but you cannot specify that a table or index must be created in a specific file, just in a specific filegroup. So if you want heavily read tables on different drives than heavily written tables with the NC indexes on a 3rd drive, you have to use filegroups for that. If there are multiple files in a filegroup, SQL just uses a proportional fill to allocate data across them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 6, 2009 at 1:04 pm
One of the most important advantages of filegroups comes from a DRP context.
Partial availability: (EntEdtn / DevEdtn)
The only thing of a db you need is the primary filegroup - because that one contains all catalog info. Once you have that one restored, you can restore on a filegroup basis and your database will actually be available on a per filegroup basis. (the ones you restore and set online)
So even if you're not en Ent Edn now, it may deliver you a fair chance of being prepared to scale out and have your objects already in place to be able to provide state of the art management, performance, .... if you get the occasion to migrate to a higher level system.
Downside: If your systems don't actually requier it now, it will only be you as a dev/dba trying to implement best practises to the top, that will have some level of satisfaction seeing your systems are prepared for the future.
Anybody else will just find it another boring thing a dba comes up with.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 7, 2009 at 8:47 am
Tnx for answering, it's clear now. Very instructive, both your posts.
Greetz,
Hans Brouwer
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply