Showing posts with label separate. Show all posts
Showing posts with label separate. Show all posts

Monday, March 19, 2012

Filegroups and Joins

Hi,
I read this tip in a article:
"For very large joins, consider placing the tables to be joined in
separate physical files in the same filegroup"
How can I force SQL Server to create a table in filegroup on a particular
file? As far as I know, I can tell SQL Server to create table on desired
filegroup, but if that filegroup contains more than one file, is it possible
to put table on a particular one?
Thanks in advance.
LeilaThat's correct. You cannot direct a table or index to a particular file -
only to a filegroup. What the author probably meant to say was to place the
tables into separate filegroups on different drives.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leila" <leilas@.hotpop.com> wrote in message
news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
Hi,
I read this tip in a article:
"For very large joins, consider placing the tables to be joined in
separate physical files in the same filegroup"
How can I force SQL Server to create a table in filegroup on a particular
file? As far as I know, I can tell SQL Server to create table on desired
filegroup, but if that filegroup contains more than one file, is it possible
to put table on a particular one?
Thanks in advance.
Leila|||Leila
CREATE DATABASE test
GO
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
"Leila" <leilas@.hotpop.com> wrote in message
news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I read this tip in a article:
> "For very large joins, consider placing the tables to be joined in
> separate physical files in the same filegroup"
> How can I force SQL Server to create a table in filegroup on a particular
> file? As far as I know, I can tell SQL Server to create table on desired
> filegroup, but if that filegroup contains more than one file, is it
possible
> to put table on a particular one?
> Thanks in advance.
> Leila
>|||Thanks Uri,
But you have placed two tables on two different filegroups. The author
mentioned that the tables can be placed in two different files, on the SAME
filegroup!
Leila
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> Leila
> CREATE DATABASE test
> GO
> ALTER DATABASE test
> ADD FILEGROUP ww_Group
> GO
> ALTER DATABASE test
> ADD FILE
> ( NAME = ww,
> FILENAME = 'D:\wwdat1.ndf',
> SIZE = 5MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 5MB)
> TO FILEGROUP ww_Group
> create table test..test(id int identity) on [primary]
> create table test..test_GR(id int identity) on ww_Group
>
>
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> > I read this tip in a article:
> > "For very large joins, consider placing the tables to be joined in
> > separate physical files in the same filegroup"
> >
> > How can I force SQL Server to create a table in filegroup on a
particular
> > file? As far as I know, I can tell SQL Server to create table on desired
> > filegroup, but if that filegroup contains more than one file, is it
> possible
> > to put table on a particular one?
> >
> > Thanks in advance.
> > Leila
> >
> >
>|||The author was wrong. You cannot place an object on a particular file, only
a particular filegroup.
The space for the object is then allocated more or less evenly across all
files in that filegroup.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leila" <leilas@.hotpop.com> wrote in message
news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> Thanks Uri,
> But you have placed two tables on two different filegroups. The author
> mentioned that the tables can be placed in two different files, on the
> SAME
> filegroup!
> Leila
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
>> Leila
>> CREATE DATABASE test
>> GO
>> ALTER DATABASE test
>> ADD FILEGROUP ww_Group
>> GO
>> ALTER DATABASE test
>> ADD FILE
>> ( NAME = ww,
>> FILENAME = 'D:\wwdat1.ndf',
>> SIZE = 5MB,
>> MAXSIZE = 100MB,
>> FILEGROWTH = 5MB)
>> TO FILEGROUP ww_Group
>> create table test..test(id int identity) on [primary]
>> create table test..test_GR(id int identity) on ww_Group
>>
>>
>>
>> "Leila" <leilas@.hotpop.com> wrote in message
>> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
>> > Hi,
>> > I read this tip in a article:
>> > "For very large joins, consider placing the tables to be joined in
>> > separate physical files in the same filegroup"
>> >
>> > How can I force SQL Server to create a table in filegroup on a
> particular
>> > file? As far as I know, I can tell SQL Server to create table on
>> > desired
>> > filegroup, but if that filegroup contains more than one file, is it
>> possible
>> > to put table on a particular one?
>> >
>> > Thanks in advance.
>> > Leila
>> >
>> >
>>
>|||Thanks Kalen,
What about log files? If I have more than one log file for my DB, are the
logged information written in multiple files or the log files are filled one
by one?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> The author was wrong. You cannot place an object on a particular file,
only
> a particular filegroup.
> The space for the object is then allocated more or less evenly across all
> files in that filegroup.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Leila" <leilas@.hotpop.com> wrote in message
> news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > Thanks Uri,
> > But you have placed two tables on two different filegroups. The author
> > mentioned that the tables can be placed in two different files, on the
> > SAME
> > filegroup!
> >
> > Leila
> >
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> >> Leila
> >> CREATE DATABASE test
> >> GO
> >> ALTER DATABASE test
> >> ADD FILEGROUP ww_Group
> >> GO
> >> ALTER DATABASE test
> >> ADD FILE
> >> ( NAME = ww,
> >> FILENAME = 'D:\wwdat1.ndf',
> >> SIZE = 5MB,
> >> MAXSIZE = 100MB,
> >> FILEGROWTH = 5MB)
> >> TO FILEGROUP ww_Group
> >>
> >> create table test..test(id int identity) on [primary]
> >> create table test..test_GR(id int identity) on ww_Group
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Leila" <leilas@.hotpop.com> wrote in message
> >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> >> > Hi,
> >> > I read this tip in a article:
> >> > "For very large joins, consider placing the tables to be joined in
> >> > separate physical files in the same filegroup"
> >> >
> >> > How can I force SQL Server to create a table in filegroup on a
> > particular
> >> > file? As far as I know, I can tell SQL Server to create table on
> >> > desired
> >> > filegroup, but if that filegroup contains more than one file, is it
> >> possible
> >> > to put table on a particular one?
> >> >
> >> > Thanks in advance.
> >> > Leila
> >> >
> >> >
> >>
> >>
> >
> >
>|||One by one. SQL Server "rattles through the log files like a snake", where the tail of the snake is
cut off when you backup the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <leilas@.hotpop.com> wrote in message news:OHTagZ3tEHA.948@.tk2msftngp13.phx.gbl...
> Thanks Kalen,
> What about log files? If I have more than one log file for my DB, are the
> logged information written in multiple files or the log files are filled one
> by one?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> > The author was wrong. You cannot place an object on a particular file,
> only
> > a particular filegroup.
> > The space for the object is then allocated more or less evenly across all
> > files in that filegroup.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Leila" <leilas@.hotpop.com> wrote in message
> > news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > > Thanks Uri,
> > > But you have placed two tables on two different filegroups. The author
> > > mentioned that the tables can be placed in two different files, on the
> > > SAME
> > > filegroup!
> > >
> > > Leila
> > >
> > >
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> Leila
> > >> CREATE DATABASE test
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILEGROUP ww_Group
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILE
> > >> ( NAME = ww,
> > >> FILENAME = 'D:\wwdat1.ndf',
> > >> SIZE = 5MB,
> > >> MAXSIZE = 100MB,
> > >> FILEGROWTH = 5MB)
> > >> TO FILEGROUP ww_Group
> > >>
> > >> create table test..test(id int identity) on [primary]
> > >> create table test..test_GR(id int identity) on ww_Group
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> > Hi,
> > >> > I read this tip in a article:
> > >> > "For very large joins, consider placing the tables to be joined in
> > >> > separate physical files in the same filegroup"
> > >> >
> > >> > How can I force SQL Server to create a table in filegroup on a
> > > particular
> > >> > file? As far as I know, I can tell SQL Server to create table on
> > >> > desired
> > >> > filegroup, but if that filegroup contains more than one file, is it
> > >> possible
> > >> > to put table on a particular one?
> > >> >
> > >> > Thanks in advance.
> > >> > Leila
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>|||Love the analogy, Tibor! ;-)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23vZOx3tEHA.1356@.TK2MSFTNGP11.phx.gbl...
One by one. SQL Server "rattles through the log files like a snake", where
the tail of the snake is
cut off when you backup the log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leila" <leilas@.hotpop.com> wrote in message
news:OHTagZ3tEHA.948@.tk2msftngp13.phx.gbl...
> Thanks Kalen,
> What about log files? If I have more than one log file for my DB, are the
> logged information written in multiple files or the log files are filled
one
> by one?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uxnXIyutEHA.2000@.TK2MSFTNGP14.phx.gbl...
> > The author was wrong. You cannot place an object on a particular file,
> only
> > a particular filegroup.
> > The space for the object is then allocated more or less evenly across
all
> > files in that filegroup.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Leila" <leilas@.hotpop.com> wrote in message
> > news:eU0MPdutEHA.1176@.TK2MSFTNGP11.phx.gbl...
> > > Thanks Uri,
> > > But you have placed two tables on two different filegroups. The author
> > > mentioned that the tables can be placed in two different files, on the
> > > SAME
> > > filegroup!
> > >
> > > Leila
> > >
> > >
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:OJzgHHrtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> Leila
> > >> CREATE DATABASE test
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILEGROUP ww_Group
> > >> GO
> > >> ALTER DATABASE test
> > >> ADD FILE
> > >> ( NAME = ww,
> > >> FILENAME = 'D:\wwdat1.ndf',
> > >> SIZE = 5MB,
> > >> MAXSIZE = 100MB,
> > >> FILEGROWTH = 5MB)
> > >> TO FILEGROUP ww_Group
> > >>
> > >> create table test..test(id int identity) on [primary]
> > >> create table test..test_GR(id int identity) on ww_Group
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Leila" <leilas@.hotpop.com> wrote in message
> > >> news:%23xlA77qtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > >> > Hi,
> > >> > I read this tip in a article:
> > >> > "For very large joins, consider placing the tables to be joined in
> > >> > separate physical files in the same filegroup"
> > >> >
> > >> > How can I force SQL Server to create a table in filegroup on a
> > > particular
> > >> > file? As far as I know, I can tell SQL Server to create table on
> > >> > desired
> > >> > filegroup, but if that filegroup contains more than one file, is it
> > >> possible
> > >> > to put table on a particular one?
> > >> >
> > >> > Thanks in advance.
> > >> > Leila
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>

Filegroups and BLOBs

Hi,
As books online mentions, BLOBs are not stored in row by default but they
are out of the row. I'm wondered that if they are separate from searchable
data(rows), how placing them into other filegroup can help performance?
Basically does it?
For example:
CREATE TABLE Table1 (
C1 int,
C2 ...
...
Logo Image) ON FG1 TextImage_On FG2
Is it recommended to keep BLOBs in other filegroup for performance?
Thanks,
Leila
In addition to what I mentioned in your other post placing data in separate
filegroups does nothing for performance in and of itself. If the filegroup
is on a separate drive array and the current one is overburdened it may
help. But only if you have I/O issues now.
Andrew J. Kelly SQL MVP
"Leila" <leilas@.hotpop.com> wrote in message
news:%23DEbknApEHA.2032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As books online mentions, BLOBs are not stored in row by default but they
> are out of the row. I'm wondered that if they are separate from searchable
> data(rows), how placing them into other filegroup can help performance?
> Basically does it?
> For example:
> CREATE TABLE Table1 (
> C1 int,
> C2 ...
> ...
> Logo Image) ON FG1 TextImage_On FG2
> Is it recommended to keep BLOBs in other filegroup for performance?
> Thanks,
> Leila
>
>

Filegroups and BLOBs

Hi,
As books online mentions, BLOBs are not stored in row by default but they
are out of the row. I'm wondered that if they are separate from searchable
data(rows), how placing them into other filegroup can help performance?
Basically does it?
For example:
CREATE TABLE Table1 (
C1 int,
C2 ...
...
Logo Image) ON FG1 TextImage_On FG2
Is it recommended to keep BLOBs in other filegroup for performance?
Thanks,
LeilaIn addition to what I mentioned in your other post placing data in separate
filegroups does nothing for performance in and of itself. If the filegroup
is on a separate drive array and the current one is overburdened it may
help. But only if you have I/O issues now.
--
Andrew J. Kelly SQL MVP
"Leila" <leilas@.hotpop.com> wrote in message
news:%23DEbknApEHA.2032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As books online mentions, BLOBs are not stored in row by default but they
> are out of the row. I'm wondered that if they are separate from searchable
> data(rows), how placing them into other filegroup can help performance?
> Basically does it?
> For example:
> CREATE TABLE Table1 (
> C1 int,
> C2 ...
> ...
> Logo Image) ON FG1 TextImage_On FG2
> Is it recommended to keep BLOBs in other filegroup for performance?
> Thanks,
> Leila
>
>

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

Friday, March 9, 2012

FileGroup

Hi,
There is a table which is about 50GB is size.
I am thinking of placing the database of this table into a separate .ndf on a separate drive on the server.
Is it possible to place this particular table into a searate drive to increase the performance even more?
Basically my question is: is it possible to place tables into separate .ndf files?
ThanksYes, Microsoft did a great whitepaper about exactly this thought a few years ago. I'm not sure if they made it public or if it was "restricted publication" due to containing customer specific details.

The general gist of the idea is to create specific filegroups for specific devices, then put the SQL objects on those filegroups.

One area that often helps more than moving specific tables to a given drive is to segregate data pages (either the heap or the clustered index) onto one set of spindles (aka drive) and the indicies onto a different filegroup. This allows parallel IO to occur for even single table changes (assuming you have enough CPUs and IO channels)!

-PatP|||I think I am now confused.
Could you please elaborate further and do you know of a link regarding this issue?
Thanks|||We can create two filegroups or more while creating database. The following SQL Statements maybe help you:

CREATE DATABASE database_name
[ ON
[ PRIMARY ] [ <filespec> [ ,...n ]
[ , <filegroup> [ ,...n ] ]
[ LOG ON { <filespec> [ ,...n ] } ]
]
[ COLLATE collation_name ]
[ WITH <external_access_option> ]
]

Here in the block <filespec>,you have to give the file path,then you can locate the files into different disk.

Then referencing the following SQL statements:

CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ ; ]

So you store datum in different disk.

Good luck!|||Yes, this is what I was after.
This seems to be a very powerfull stuff.
Thank you

Wednesday, March 7, 2012

File Task Moving with variables...

I have a 'file system task' moving files from one server to another for processing. I have defined the path and filename as separate variables. When I attempt to pass them together into the task I receive an error.

Source Path & Filename:

@.[User:: DataSourceFolder]+"\\"+ @.[User::CD_PaidClaimSource]

Outputs:

\\umrdwh2\FTP\CLAIMDAILY.DATA

Destination Path Now: (Filename not specified per another thread.)

@.[User::WorkingFolder]

Original Destination Path: (Which did not work.)

@.[User::WorkingFolder]+"\\"+ @.[User::CD_PaidClaimSource]

I receive validation errors:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

How do I overcome this without hard coding the path in the task?


You should be editing the file connection managers (source and destination) via variable expressions, not the file system task, I believe.|||

CaptainMyCaptain wrote:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

That error is showing you the value of your variable, not it's name. Not sure how that could have happened, but hopefully it helps you figure out what is wrong.
|||

JayH wrote:

CaptainMyCaptain wrote:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\\umrdwh2\FTP\CLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0

That error is showing you the value of your variable, not it's name. Not sure how that could have happened, but hopefully it helps you figure out what is wrong.

Oh, I think I get it. Did you specify that expression as the Source Path in the File System Task? I didn't think the UI allowed that. If so, you need to create a new variable, set it EvaluateAsExpression=true and put the expression there. Then reference that variable from your File System Task's SourceVariable property.
|||An example to illustrate what we're saying:

http://www.ssistalk.com/file_system_task.jpg|||

The picture helped immensely, thank you. I was trying to establish the source and destination on the expressions screen as well as the general screen which was causing some of the error. Once I removed the destination and source from the expressions screen it worked better.

I am having another issue now, however. Following the syntax in the picture exactly I am getting an error because the filename is being considered a part of the folder path in addition to the filename. I have as an expression:

My expression (like your picture):

"\\\\Apsw9026g1\\Admin\\Logs\\CLAIMDAILY.DATA"

Which outputs:

\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA

But this is the error on execution:

Information: 0xC002F30E at CLAIMDAILY Move, File System Task: File or directory "\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA" was deleted.

Error: 0xC002F304 at CLAIMDAILY Move, File System Task: An error occurred with the following error message: "Could not find a part of the path '\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA'.".

Why is it duplicating the filename as part of the path? Did I miss a setting?

Roger

|||Try deleting the file system task and starting over.|||

Deleting everything variable, task, container, etc and recreating all of it results in the same error. Ideas? The funny part is the system loves the 'Source'; there are no errors, and it is the same syntax. It is only the 'destination' that is having trouble.

Rog

|||

CaptainMyCaptain wrote:

The picture helped immensely, thank you. I was trying to establish the source and destination on the expressions screen as well as the general screen which was causing some of the error. Once I removed the destination and source from the expressions screen it worked better.

I am having another issue now, however. Following the syntax in the picture exactly I am getting an error because the filename is being considered a part of the folder path in addition to the filename. I have as an expression:

My expression (like your picture):

"\\\\Apsw9026g1\\Admin\\Logs\\CLAIMDAILY.DATA"

Which outputs:

\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA

But this is the error on execution:

Information: 0xC002F30E at CLAIMDAILY Move, File System Task: File or directory "\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA" was deleted.

Error: 0xC002F304 at CLAIMDAILY Move, File System Task: An error occurred with the following error message: "Could not find a part of the path '\\Apsw9026g1\Admin\Logs\CLAIMDAILY.DATA\CLAIMDAILY.DATA'.".

Why is it duplicating the filename as part of the path? Did I miss a setting?

Roger

Roger,

I have an example that uses the file system task to move/rename files in case you are interested: http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html

If I recall correctly, when using variables in the file system task, IsDestiantionPathVariable and IsSourcePathVarible, and depending on the operation used; some times the task expect only the path that points to the file and some times it expects the path AND the file name. In your case it looks like it only expects the path; so remove the file name from the expression. Do not assume both variables need path and file name.

|||

That was the answer. Thank you all for helping this greenhorn to get his job done!

Roger