Database Archive Data

I have a production database thats approximately 50 GB in
size with SQL Server 2000 Enterprise Edition. I truncate
the non-essential tables nightly. Does Microsoft
recommend partitioning tables for core active data in the
tables thats access by the user (Active), the other part
of the partition as data that is presently not used by
the application (Non-Active).
What archiving technique does Microsoft recommend?
Thank You,
MikeThere is no general rule of thumb or official recommendation for things like
this. There are so many things that are app or design specific that can
affect this decision. The choice to partition / archive / truncate etc. is
really dependant on what you need to do with this data. If you almost never
have to access it again then it is usually not a bad idea to archive it to
files or another database. If your current operation and hardware can
support it either way, you may choose to just leave it where it is. 50GB is
not that large of a database these days and the sheer size alone is really
no indication that you should do one thing over another.
--
Andrew J. Kelly SQL MVP
"MIke" <anonymous@.discussions.microsoft.com> wrote in message
news:67aa01c482d0$c6706680$a601280a@.phx.gbl...
> I have a production database thats approximately 50 GB in
> size with SQL Server 2000 Enterprise Edition. I truncate
> the non-essential tables nightly. Does Microsoft
> recommend partitioning tables for core active data in the
> tables thats access by the user (Active), the other part
> of the partition as data that is presently not used by
> the application (Non-Active).
> What archiving technique does Microsoft recommend?
> Thank You,
> Mike

data warehouse truncate question

have an SSIS project and I have a package that drops and creates the
database, creates tables, then I have a package that loads the data into the
data warehouse. Now, I got the request in to only load the data and not drop
and create the database every time the package runs due to the package is
going to run every 5-10 minutes. Now I never created an SSIS package until
now, so how can I accomplish in only loading the tables every time its ran
instead of dropping and creating the database every time the package is ran?
so in a nutshell.
I need a package to load the data only if the data does not exsist and not
drop and create the database every time.
any suggestions on how this can be done?
Two methods come to mind.
1) Simple insert where not exists statements for each table. Can be VERY
inefficient, but easy to construct.
2) Build system that tracks the last-inserted key value for each table.
Reference the appropriate key in the where clause of each insert statement.
If you need to also track modified data, then it gets MUCH more complicated
because you need triggers on every base table to insert the modified row's
PKs into a refresh holding table which would also be reference for the
warehouse update statements.
TheSQLGuru
President
Indicium Resources, Inc.
"Mike" <igotyourdotnet@.gmail.com> wrote in message
news:%23%23HSmywaHHA.1300@.TK2MSFTNGP02.phx.gbl...
> have an SSIS project and I have a package that drops and creates the
> database, creates tables, then I have a package that loads the data into
> the
> data warehouse. Now, I got the request in to only load the data and not
> drop
> and create the database every time the package runs due to the package is
> going to run every 5-10 minutes. Now I never created an SSIS package until
> now, so how can I accomplish in only loading the tables every time its ran
> instead of dropping and creating the database every time the package is
> ran?
> so in a nutshell.
> I need a package to load the data only if the data does not exsist and not
> drop and create the database every time.
> any suggestions on how this can be done?
>
>

data warehouse truncate question

have an SSIS project and I have a package that drops and creates the
database, creates tables, then I have a package that loads the data into the
data warehouse. Now, I got the request in to only load the data and not drop
and create the database every time the package runs due to the package is
going to run every 5-10 minutes. Now I never created an SSIS package until
now, so how can I accomplish in only loading the tables every time its ran
instead of dropping and creating the database every time the package is ran?
so in a nutshell.
I need a package to load the data only if the data does not exsist and not
drop and create the database every time.
any suggestions on how this can be done?Two methods come to mind.
1) Simple insert where not exists statements for each table. Can be VERY
inefficient, but easy to construct.
2) Build system that tracks the last-inserted key value for each table.
Reference the appropriate key in the where clause of each insert statement.
If you need to also track modified data, then it gets MUCH more complicated
because you need triggers on every base table to insert the modified row's
PKs into a refresh holding table which would also be reference for the
warehouse update statements.
TheSQLGuru
President
Indicium Resources, Inc.
"Mike" <igotyourdotnet@.gmail.com> wrote in message
news:%23%23HSmywaHHA.1300@.TK2MSFTNGP02.phx.gbl...
> have an SSIS project and I have a package that drops and creates the
> database, creates tables, then I have a package that loads the data into
> the
> data warehouse. Now, I got the request in to only load the data and not
> drop
> and create the database every time the package runs due to the package is
> going to run every 5-10 minutes. Now I never created an SSIS package until
> now, so how can I accomplish in only loading the tables every time its ran
> instead of dropping and creating the database every time the package is
> ran?
> so in a nutshell.
> I need a package to load the data only if the data does not exsist and not
> drop and create the database every time.
> any suggestions on how this can be done?
>
>