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 |
|
craigwg
Posting Yak Master
154 Posts |
 Posted - 2009-08-10 : 12:35:36
|
| I am looking for details on Filegroups. When I research them on Google I get tons of information on how to create them. I get very little on why one would want to create them, what their advantages and disadvantages are, and what alternatives exist if any.Can anyone offer some answers, details, real life examples or links? BOL only tells how to do it.Thanks!Craig Greenwood |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-10 : 14:18:26
|
| One reason would be with a VLDB, you can backup (and restore) parts of the db by performing filegroup backups.Also, it is done to utilize multiple disk arrays. It isn't uncommon to put non-clustered indexes in a seperate filegroup. or to put user objects in a seperate filegroup from system objects.Also partitioned tables are often spread accross multiple files/groups |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-08-10 : 14:54:21
|
| You should use multiple files and groups from the start, regardless if you are experiencing performance issues, if your database is expected to be biggish. For instance anything over 100GB should be using multiple files. We somehow managed to let a database grow to 250GB with only one file. Microsoft was shocked when I was on the phone with them about a stack dump. I said, "I know, I don't know how or when it got this big, but we will be adding more files soon."Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
craigwg
Posting Yak Master
154 Posts |
Posted - 2009-08-10 : 15:04:50
|
| X002548,Thing are not running slowly, and database size is not near 100 GB. I am doing a few things with this question. Every week team members here must present something about SQL and I choose my topics based on the 70431 test which I want to pass. A few weeks ago I presented database mirroring and it ROCKED. Filegroups (and partitioning) are something that come up on the practice tests and I need to conquer them. I am creating test databases with filegroups and looking for some juicy meat in my presentation. Hence, here I am, looking for advice from those who have actually used them for practical purposes.If I am getting this it sounds like you'd want the filegroup to be ideally on a different drive than the log or mdf file. Is that correct? Or would multiple filegroups on the same drive, with the log on a different drive work just as well?Craig Greenwood |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-08-10 : 15:18:24
|
| In an ideal world, you'd break up everything into different drives: MDFs, LDFs, tempdb, backups, etc... You'd then move indexes to different filegroups, put read-only data in their own filegroup etc... You could get really fancy with it too. I'm sure this has been documented at length before, so I'd suggest doing some research.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-08-10 : 16:53:25
|
quote:
Originally posted by craigwg
X002548,Thing are not running slowly, and database size is not near 100 GB. I am doing a few things with this question. Every week team members here must present something about SQL and I choose my topics based on the 70431 test which I want to pass.
Well as long as it's really important....Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
|
|
|
|
|
Filegroups