File group design

Hello!
I'm thinking about one thing about file group design and backup and restore.
The best practise is to put your user tables in a file group other than the
PRIMARY file group.
I know that in a file group restore scenario the PRIMARY file group always
has to be restored.
Consider a database with two file groups; the PRIMARY file group for the
system tables and another file group for the user tables.
What benifits does this design give you over having just the PRIMARY
filegroup?
(You could restore the PRIMARY file group with the system tables fast, but
what is that good for if you don't have the data?)
Best regards
Ola Hallengren
http://ola.hallengren.com
It is more useful when you have multiple filegroups or files. Even with one
filegroup you can still restore at the file level. The idea is that you
separate the data into multiple filegroups by some logic such as tables used
together in one and the others in another etc. But in any case just because
you only have 1 FG today does not mean you can't or won't add one tomorrow.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:91DA1CD7-41C8-4A14-82D0-F9B3063E659A@.microsoft.com...
> Hello!
> I'm thinking about one thing about file group design and backup and
> restore.
> The best practise is to put your user tables in a file group other than
> the
> PRIMARY file group.
> I know that in a file group restore scenario the PRIMARY file group always
> has to be restored.
> Consider a database with two file groups; the PRIMARY file group for the
> system tables and another file group for the user tables.
> What benifits does this design give you over having just the PRIMARY
> filegroup?
> (You could restore the PRIMARY file group with the system tables fast, but
> what is that good for if you don't have the data?)
> Best regards
> Ola Hallengren
> http://ola.hallengren.com
|||Thanks, Andrew.

>The idea is that you separate the data into multiple filegroups by some logic such as tables used together in one and the others in another etc.
I've heard about placing indexes and blobs in their own file groups. As I
understand it this would be a performance optimization, that from a restore
point of view would make things a bit more complex. What is your view on this?
/Ola
"Andrew J. Kelly" wrote:

> It is more useful when you have multiple filegroups or files. Even with one
> filegroup you can still restore at the file level. The idea is that you
> separate the data into multiple filegroups by some logic such as tables used
> together in one and the others in another etc. But in any case just because
> you only have 1 FG today does not mean you can't or won't add one tomorrow.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:91DA1CD7-41C8-4A14-82D0-F9B3063E659A@.microsoft.com...
>
|||Placing Indexes and blobs in their own FG will do nothing for performance if
they are on the same physical array. It is not the FG that gives the
performance but the ability to store them physically on another array. FG's
just happen to suite that purpose in allowing you to do that. The technique
of having Multiple filegroups and restoring at that level is mostly for very
large dbs. If you have TB's of data you may not be able to back them all up
at once so FG's give you some flexibility there. They have the advantage of
allowing partial restores as well. But for an online partial restore to be
effective you need to have your db objects separated into FG's such that if
one FG is offline the others are still useful to other parts of the app or
other apps. But even if you can't segregate the data such that the others
are still online and useful you can still save a lot of time by doing a
partial restore on VLDB's. If you have the tables and it's associated
indexes segregated such that for any given object all its components are on
one FG but yet have other objects on other FG's you can make partial
restores easier. In any case you need to apply logs after the piecemeal
restore but if the transaction in the logs are not associated with the
objects in that FG they don't need to be applied and SQL Server can optimize
the restore so to speak.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:3C6E488C-7668-412B-A8E7-5F5938BF4140@.microsoft.com...[vbcol=seagreen]
> Thanks, Andrew.
>
> I've heard about placing indexes and blobs in their own file groups. As I
> understand it this would be a performance optimization, that from a
> restore
> point of view would make things a bit more complex. What is your view on
> this?
> /Ola
>
> "Andrew J. Kelly" wrote: