Showing posts with label design. Show all posts
Showing posts with label design. Show all posts

Monday, March 12, 2012

Filegroup backups/restore design problems

At the design stage of an application using SQL server 7 I assumed the
following could be done:
- Separate monthly buckets of data into separate filegroups.
- Flag current filegroups as readonly when the new month rollsover.
- design was proposed in this way to minimise backups of the entire
database
of which most will be read-only. Was I completely wrong ?
Backup:
I assumed we could backup the primary, active (current) filegroups and
the previous filegroup.
I assumed a complete db could be rebuilt using the primary and active
filegroup backup.
Is this possible ?
Is there an option on FULL DB BACKUPS to ignore read-only filegroups
and therefore facilitate restoration of a smaller db with only the
primary and active filegroups?
Archiving:
When using this approach - and imagining 2 years of filegroups -
Suppose I would drop one of the tables ?
Could you restore this archived filegroup to the current db ?
thanks
bill
bill
If you don't place the table on a separated physical disks you won't get a
perfomance benefit.
Did you consider to use a full backup database a week period and backup log
file a on an hour period?
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.c om...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
>
> thanks
> bill
|||see inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.c om...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
IF you are talking about 1 filegroup per month, you need to be aware that
the max # of filegroups per database is 256. Additionally, you would have to
name each of the partition tables differently, a single table can not span
multiple filegroups. Each filegroup can be marked as read-only... The idea
you have is correct however, rolling historical data into another filegroup
which doesn't need to be backed up as frequently is one of the primary
usages for filegroups... Maybe you roll everything up into annual
filegroups instead of monthly...
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
You may restore ANY filegroup individually... What you might do , is put
the historical filegroups on a separate raid array from the current
information. Then back the historical stuff up monthly when it changes, and
keep the backups for (maybe) 3 months.

> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
No there isn't... Your full database backup will have to specify the
filegroups you wish to backup... I would still do a REAL full database
backup occasionally.

> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
You may restore any filegroup independently, but everthing in the filegroup
will be restored... You may also do a partial restore to another database,
then pull only the tables you need back into the production database..

>
> thanks
> bill
Your thinking is sound... But before you go production make sure you do
testing to ensure your plan works as you intend...
|||Wayne,
I appreciate your comments.
As for the 1 table per filegroup - yes I am doing that and am aware of
the 256 limit. And yes my table are named differently and they do not
span filegroups - they are created dynamically in their own filegroup
and with a yyyymm extension on each.
Could I please ask you to clarify further my understanding.
You mention that you can do a partial restore but from my
reading/understanding this can only be done on a full db backup.
To recreate my db as proposed I understood that I would need to
1. restore primary filegroup
2. restore active filegroup
3. restore read-only filegroups
4. restore ALL transaction log backups since the last read-only
filegroup backup.
Is this correct ?
And can I do the following on a completely new server:
1. restore primary filegroup
2. restore active filegroup
3. restore ALL transaction log backups since the last primary/active
filegroup backup.
thanks
bill
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<uD0$2vIeEHA.592@.TK2MSFTNGP11.phx.gbl>...
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "bill k" <bkatelis@.yahoo.com> wrote in message
> news:b11556a0.0408020050.63e48688@.posting.google.c om...
> IF you are talking about 1 filegroup per month, you need to be aware that
> the max # of filegroups per database is 256. Additionally, you would have to
> name each of the partition tables differently, a single table can not span
> multiple filegroups. Each filegroup can be marked as read-only... The idea
> you have is correct however, rolling historical data into another filegroup
> which doesn't need to be backed up as frequently is one of the primary
> usages for filegroups... Maybe you roll everything up into annual
> filegroups instead of monthly...
> You may restore ANY filegroup individually... What you might do , is put
> the historical filegroups on a separate raid array from the current
> information. Then back the historical stuff up monthly when it changes, and
> keep the backups for (maybe) 3 months.
>
> No there isn't... Your full database backup will have to specify the
> filegroups you wish to backup... I would still do a REAL full database
> backup occasionally.
> You may restore any filegroup independently, but everthing in the filegroup
> will be restored... You may also do a partial restore to another database,
> then pull only the tables you need back into the production database..
>
> Your thinking is sound... But before you go production make sure you do
> testing to ensure your plan works as you intend...

Filegroup backups/restore design problems

At the design stage of an application using SQL server 7 I assumed the
following could be done:
- Separate monthly buckets of data into separate filegroups.
- Flag current filegroups as readonly when the new month rollsover.
- design was proposed in this way to minimise backups of the entire
database
of which most will be read-only. Was I completely wrong ?
Backup:
I assumed we could backup the primary, active (current) filegroups and
the previous filegroup.
I assumed a complete db could be rebuilt using the primary and active
filegroup backup.
Is this possible ?
Is there an option on FULL DB BACKUPS to ignore read-only filegroups
and therefore facilitate restoration of a smaller db with only the
primary and active filegroups?
Archiving:
When using this approach - and imagining 2 years of filegroups -
Suppose I would drop one of the tables ?
Could you restore this archived filegroup to the current db ?
thanks
billbill
If you don't place the table on a separated physical disks you won't get a
perfomance benefit.
Did you consider to use a full backup database a week period and backup log
file a on an hour period?
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.com...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
>
> thanks
> bill|||see inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.com...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
IF you are talking about 1 filegroup per month, you need to be aware that
the max # of filegroups per database is 256. Additionally, you would have to
name each of the partition tables differently, a single table can not span
multiple filegroups. Each filegroup can be marked as read-only... The idea
you have is correct however, rolling historical data into another filegroup
which doesn't need to be backed up as frequently is one of the primary
usages for filegroups... Maybe you roll everything up into annual
filegroups instead of monthly...
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
You may restore ANY filegroup individually... What you might do , is put
the historical filegroups on a separate raid array from the current
information. Then back the historical stuff up monthly when it changes, and
keep the backups for (maybe) 3 months.
> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
No there isn't... Your full database backup will have to specify the
filegroups you wish to backup... I would still do a REAL full database
backup occasionally.
> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
You may restore any filegroup independently, but everthing in the filegroup
will be restored... You may also do a partial restore to another database,
then pull only the tables you need back into the production database..
>
> thanks
> bill
Your thinking is sound... But before you go production make sure you do
testing to ensure your plan works as you intend...|||Wayne,
I appreciate your comments.
As for the 1 table per filegroup - yes I am doing that and am aware of
the 256 limit. And yes my table are named differently and they do not
span filegroups - they are created dynamically in their own filegroup
and with a yyyymm extension on each.
Could I please ask you to clarify further my understanding.
You mention that you can do a partial restore but from my
reading/understanding this can only be done on a full db backup.
To recreate my db as proposed I understood that I would need to
1. restore primary filegroup
2. restore active filegroup
3. restore read-only filegroups
4. restore ALL transaction log backups since the last read-only
filegroup backup.
Is this correct ?
And can I do the following on a completely new server:
1. restore primary filegroup
2. restore active filegroup
3. restore ALL transaction log backups since the last primary/active
filegroup backup.
thanks
bill
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<uD0$2vIeEHA.592@.TK2MSFTNGP11.phx.gbl>...
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "bill k" <bkatelis@.yahoo.com> wrote in message
> news:b11556a0.0408020050.63e48688@.posting.google.com...
> > At the design stage of an application using SQL server 7 I assumed the
> > following could be done:
> > - Separate monthly buckets of data into separate filegroups.
> > - Flag current filegroups as readonly when the new month rollsover.
> > - design was proposed in this way to minimise backups of the entire
> > database
> > of which most will be read-only. Was I completely wrong ?
> IF you are talking about 1 filegroup per month, you need to be aware that
> the max # of filegroups per database is 256. Additionally, you would have to
> name each of the partition tables differently, a single table can not span
> multiple filegroups. Each filegroup can be marked as read-only... The idea
> you have is correct however, rolling historical data into another filegroup
> which doesn't need to be backed up as frequently is one of the primary
> usages for filegroups... Maybe you roll everything up into annual
> filegroups instead of monthly...
> >
> > Backup:
> > I assumed we could backup the primary, active (current) filegroups and
> > the previous filegroup.
> > I assumed a complete db could be rebuilt using the primary and active
> > filegroup backup.
> > Is this possible ?
> You may restore ANY filegroup individually... What you might do , is put
> the historical filegroups on a separate raid array from the current
> information. Then back the historical stuff up monthly when it changes, and
> keep the backups for (maybe) 3 months.
>
> > Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> > and therefore facilitate restoration of a smaller db with only the
> > primary and active filegroups?
> No there isn't... Your full database backup will have to specify the
> filegroups you wish to backup... I would still do a REAL full database
> backup occasionally.
> >
> > Archiving:
> > When using this approach - and imagining 2 years of filegroups -
> > Suppose I would drop one of the tables ?
> > Could you restore this archived filegroup to the current db ?
> You may restore any filegroup independently, but everthing in the filegroup
> will be restored... You may also do a partial restore to another database,
> then pull only the tables you need back into the production database..
> >
> >
> > thanks
> > bill
> Your thinking is sound... But before you go production make sure you do
> testing to ensure your plan works as you intend...

Filegroup backups/restore design problems

At the design stage of an application using SQL server 7 I assumed the
following could be done:
- Separate monthly buckets of data into separate filegroups.
- Flag current filegroups as readonly when the new month rollsover.
- design was proposed in this way to minimise backups of the entire
database
of which most will be read-only. Was I completely wrong ?
Backup:
I assumed we could backup the primary, active (current) filegroups and
the previous filegroup.
I assumed a complete db could be rebuilt using the primary and active
filegroup backup.
Is this possible ?
Is there an option on FULL DB BACKUPS to ignore read-only filegroups
and therefore facilitate restoration of a smaller db with only the
primary and active filegroups?
Archiving:
When using this approach - and imagining 2 years of filegroups -
Suppose I would drop one of the tables ?
Could you restore this archived filegroup to the current db ?
thanks
billbill
If you don't place the table on a separated physical disks you won't get a
perfomance benefit.
Did you consider to use a full backup database a week period and backup log
file a on an hour period?
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.com...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
>
> thanks
> bill|||see inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.com...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
IF you are talking about 1 filegroup per month, you need to be aware that
the max # of filegroups per database is 256. Additionally, you would have to
name each of the partition tables differently, a single table can not span
multiple filegroups. Each filegroup can be marked as read-only... The idea
you have is correct however, rolling historical data into another filegroup
which doesn't need to be backed up as frequently is one of the primary
usages for filegroups... Maybe you roll everything up into annual
filegroups instead of monthly...
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
You may restore ANY filegroup individually... What you might do , is put
the historical filegroups on a separate raid array from the current
information. Then back the historical stuff up monthly when it changes, and
keep the backups for (maybe) 3 months.

> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
No there isn't... Your full database backup will have to specify the
filegroups you wish to backup... I would still do a REAL full database
backup occasionally.

> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
You may restore any filegroup independently, but everthing in the filegroup
will be restored... You may also do a partial restore to another database,
then pull only the tables you need back into the production database..

>
> thanks
> bill
Your thinking is sound... But before you go production make sure you do
testing to ensure your plan works as you intend...|||Wayne,
I appreciate your comments.
As for the 1 table per filegroup - yes I am doing that and am aware of
the 256 limit. And yes my table are named differently and they do not
span filegroups - they are created dynamically in their own filegroup
and with a yyyymm extension on each.
Could I please ask you to clarify further my understanding.
You mention that you can do a partial restore but from my
reading/understanding this can only be done on a full db backup.
To recreate my db as proposed I understood that I would need to
1. restore primary filegroup
2. restore active filegroup
3. restore read-only filegroups
4. restore ALL transaction log backups since the last read-only
filegroup backup.
Is this correct ?
And can I do the following on a completely new server:
1. restore primary filegroup
2. restore active filegroup
3. restore ALL transaction log backups since the last primary/active
filegroup backup.
thanks
bill
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<uD0$2vIeEHA.592@.
TK2MSFTNGP11.phx.gbl>...
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "bill k" <bkatelis@.yahoo.com> wrote in message
> news:b11556a0.0408020050.63e48688@.posting.google.com...
> IF you are talking about 1 filegroup per month, you need to be aware that
> the max # of filegroups per database is 256. Additionally, you would have
to
> name each of the partition tables differently, a single table can not span
> multiple filegroups. Each filegroup can be marked as read-only... The ide
a
> you have is correct however, rolling historical data into another filegrou
p
> which doesn't need to be backed up as frequently is one of the primary
> usages for filegroups... Maybe you roll everything up into annual
> filegroups instead of monthly...
> You may restore ANY filegroup individually... What you might do , is put
> the historical filegroups on a separate raid array from the current
> information. Then back the historical stuff up monthly when it changes, an
d
> keep the backups for (maybe) 3 months.
>
> No there isn't... Your full database backup will have to specify the
> filegroups you wish to backup... I would still do a REAL full database
> backup occasionally.
>
> You may restore any filegroup independently, but everthing in the filegrou
p
> will be restored... You may also do a partial restore to another database,
> then pull only the tables you need back into the production database..
>
> Your thinking is sound... But before you go production make sure you do
> testing to ensure your plan works as you intend...

Wednesday, March 7, 2012

File storage with SQL server

Hello,

i'm thinkink about design of a new WinApp.

In this app, i need to store files (xls, doc and pdf essentialy), i need to be able to open and modify it.

I want to use SQL Server 2005 to store those file to be able to use replication process.

Is it possible, is it a good idea ?

Thanks a lot

yes its possible and in most cases prefered. you would store them in an image field.

programmatically, your app would open the file via a stream and read the file to a byte array. You would then use a parameterized a parameterized query to write the bytes to the array.

http://support.microsoft.com/default.aspx/kb/317016/EN-US/

|||Thank you very much for this answer !|||I wont slag you for your inglish if you dont slag me for my typink

Sunday, February 19, 2012

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.comIt 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...
> > 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
>|||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...
> 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...
>> > 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
>>

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: