Archive Database

Hi
I have some questions about archive database in the real word case (now we have only 4 gig of data and start to consider archieve database for the last year)
1)Is it correct to archive just only transaction data? No need for some table such as Customers, Vendors
2)Do we use DTS for this mechanism
3)What is the real practice that you do in normal work
Any suggestion welcome because we have no experience about this. If it is not in this group, please recommend me the right group
Best Regards
James JarupanJames,
archiving of historical data may indeed be done using DTS. The data could be
copied (appended) to a separate database then removed from the current
database, both using ExecuteSQL tasks. If you require the archive data to be
available to users along with current data, you can union it in report
queries. If you want a more sophisticated solution you can use distributed
partitioned views to make the union results updatable.
Personally, I have a bit flag on each record which designates if the record
is live or not. However my databases are in the order of 100MB and we have
very few records which might be archived.
HTH,
Paul Ibison|||you are on the right track.
DTS is a great tool for this.
this is the exact direction we are taking (DTS calling a bunch of sprocs) if
it makes you feel better.
You can write stored procedures if you wish instead
etc etc etc.
if you need more info, let me know.
Cheers
Greg Jackson
PDX, Oregon|||Sorry to interrupt you. I also want to know more about archiving a database
in a real world.
When archiving a database, should we backup data in a large flat table? or
keep data in their table (if database have 20 tables, store data in 20
tables with the same structure)?
Also what format of the file is the best for archiving? SQL database file?
compress or not?
Thanks in advance.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> ¦b¶l¥ó
news:%23px$568TEHA.2580@.TK2MSFTNGP12.phx.gbl ¤¤¼¶¼g...
> you are on the right track.
> DTS is a great tool for this.
> this is the exact direction we are taking (DTS calling a bunch of sprocs)
if
> it makes you feel better.
>
> You can write stored procedures if you wish instead
> etc etc etc.
>
> if you need more info, let me know.
>
> Cheers
>
> Greg Jackson
> PDX, Oregon
>|||it depends on what you want to do with it....
"IF" you want to access the archived data for "Reporting", etc then you may
want to "Transform" it into a more flattened or "Denormalized" structure
(standard for OLAP environments).
taking data from a production OLTP system, Flattening it and then putting it
into an OLAP system is very common, and is referred to as "Extract Transform
Load" or "ETL" operations.
you can find a ton of info on this in Books On Line (BOL) or on Google.
cheers
Greg Jackson
PDX, Oregon|||In fact, I just want to move the historical data out from the production
server because these data are too old for the daily operation and the space
of HD is limited. Also no further operation on these old data.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> ¦b¶l¥ó
news:OT1nyWjUEHA.716@.TK2MSFTNGP11.phx.gbl ¤¤¼¶¼g...
> it depends on what you want to do with it....
> "IF" you want to access the archived data for "Reporting", etc then you
may
> want to "Transform" it into a more flattened or "Denormalized" structure
> (standard for OLAP environments).
> taking data from a production OLTP system, Flattening it and then putting
it
> into an OLAP system is very common, and is referred to as "Extract
Transform
> Load" or "ETL" operations.
> you can find a ton of info on this in Books On Line (BOL) or on Google.
>
> cheers
> Greg Jackson
> PDX, Oregon
>

Archive Database

Hi
I have some questions about archive database in the real word case (now we have only 4 gig of data and start to consider archieve database for the last year).
1)Is it correct to archive just only transaction data? No need for some table such as Customers, Vendors?
2)Do we use DTS for this mechanism?
3)What is the real practice that you do in normal work?
Any suggestion welcome because we have no experience about this. If it is not in this group, please recommend me the right group.
Best Regards,
James Jarupan
James,
archiving of historical data may indeed be done using DTS. The data could be
copied (appended) to a separate database then removed from the current
database, both using ExecuteSQL tasks. If you require the archive data to be
available to users along with current data, you can union it in report
queries. If you want a more sophisticated solution you can use distributed
partitioned views to make the union results updatable.
Personally, I have a bit flag on each record which designates if the record
is live or not. However my databases are in the order of 100MB and we have
very few records which might be archived.
HTH,
Paul Ibison
|||you are on the right track.
DTS is a great tool for this.
this is the exact direction we are taking (DTS calling a bunch of sprocs) if
it makes you feel better.
You can write stored procedures if you wish instead
etc etc etc.
if you need more info, let me know.
Cheers
Greg Jackson
PDX, Oregon
|||Sorry to interrupt you. I also want to know more about archiving a database
in a real world.
When archiving a database, should we backup data in a large flat table? or
keep data in their table (if database have 20 tables, store data in 20
tables with the same structure)?
Also what format of the file is the best for archiving? SQL database file?
compress or not?
Thanks in advance.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> bl
news:%23px$568TEHA.2580@.TK2MSFTNGP12.phx.gbl g...
> you are on the right track.
> DTS is a great tool for this.
> this is the exact direction we are taking (DTS calling a bunch of sprocs)
if
> it makes you feel better.
>
> You can write stored procedures if you wish instead
> etc etc etc.
>
> if you need more info, let me know.
>
> Cheers
>
> Greg Jackson
> PDX, Oregon
>
|||it depends on what you want to do with it....
"IF" you want to access the archived data for "Reporting", etc then you may
want to "Transform" it into a more flattened or "Denormalized" structure
(standard for OLAP environments).
taking data from a production OLTP system, Flattening it and then putting it
into an OLAP system is very common, and is referred to as "Extract Transform
Load" or "ETL" operations.
you can find a ton of info on this in Books On Line (BOL) or on Google.
cheers
Greg Jackson
PDX, Oregon
|||In fact, I just want to move the historical data out from the production
server because these data are too old for the daily operation and the space
of HD is limited. Also no further operation on these old data.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> bl
news:OT1nyWjUEHA.716@.TK2MSFTNGP11.phx.gbl g...
> it depends on what you want to do with it....
> "IF" you want to access the archived data for "Reporting", etc then you
may
> want to "Transform" it into a more flattened or "Denormalized" structure
> (standard for OLAP environments).
> taking data from a production OLTP system, Flattening it and then putting
it
> into an OLAP system is very common, and is referred to as "Extract
Transform
> Load" or "ETL" operations.
> you can find a ton of info on this in Books On Line (BOL) or on Google.
>
> cheers
> Greg Jackson
> PDX, Oregon
>
sql

Archive Database

Hi
I have some questions about archive database in the real word case (now we h
ave only 4 gig of data and start to consider archieve database for the last
year).
1)Is it correct to archive just only transaction data? No need for some tabl
e such as Customers, Vendors?
2)Do we use DTS for this mechanism?
3)What is the real practice that you do in normal work?
Any suggestion welcome because we have no experience about this. If it is no
t in this group, please recommend me the right group.
Best Regards,
James JarupanJames,
archiving of historical data may indeed be done using DTS. The data could be
copied (appended) to a separate database then removed from the current
database, both using ExecuteSQL tasks. If you require the archive data to be
available to users along with current data, you can union it in report
queries. If you want a more sophisticated solution you can use distributed
partitioned views to make the union results updatable.
Personally, I have a bit flag on each record which designates if the record
is live or not. However my databases are in the order of 100MB and we have
very few records which might be archived.
HTH,
Paul Ibison|||you are on the right track.
DTS is a great tool for this.
this is the exact direction we are taking (DTS calling a bunch of sprocs) if
it makes you feel better.
You can write stored procedures if you wish instead
etc etc etc.
if you need more info, let me know.
Cheers
Greg Jackson
PDX, Oregon|||Sorry to interrupt you. I also want to know more about archiving a database
in a real world.
When archiving a database, should we backup data in a large flat table? or
keep data in their table (if database have 20 tables, store data in 20
tables with the same structure)?
Also what format of the file is the best for archiving? SQL database file?
compress or not?
Thanks in advance.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> bl
news:%23px$568TEHA.2580@.TK2MSFTNGP12.phx.gbl g...
> you are on the right track.
> DTS is a great tool for this.
> this is the exact direction we are taking (DTS calling a bunch of sprocs)
if
> it makes you feel better.
>
> You can write stored procedures if you wish instead
> etc etc etc.
>
> if you need more info, let me know.
>
> Cheers
>
> Greg Jackson
> PDX, Oregon
>|||it depends on what you want to do with it....
"IF" you want to access the archived data for "Reporting", etc then you may
want to "Transform" it into a more flattened or "Denormalized" structure
(standard for OLAP environments).
taking data from a production OLTP system, Flattening it and then putting it
into an OLAP system is very common, and is referred to as "Extract Transform
Load" or "ETL" operations.
you can find a ton of info on this in Books On Line (BOL) or on Google.
cheers
Greg Jackson
PDX, Oregon|||In fact, I just want to move the historical data out from the production
server because these data are too old for the daily operation and the space
of HD is limited. Also no further operation on these old data.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> bl
news:OT1nyWjUEHA.716@.TK2MSFTNGP11.phx.gbl g...
> it depends on what you want to do with it....
> "IF" you want to access the archived data for "Reporting", etc then you
may
> want to "Transform" it into a more flattened or "Denormalized" structure
> (standard for OLAP environments).
> taking data from a production OLTP system, Flattening it and then putting
it
> into an OLAP system is very common, and is referred to as "Extract
Transform
> Load" or "ETL" operations.
> you can find a ton of info on this in Books On Line (BOL) or on Google.
>
> cheers
> Greg Jackson
> PDX, Oregon
>

append backups and other mtn plan stuff

Hello:
I am editing a backup maintenance plan. For some reason, the plan is doing
an append backup. I realize that the word "Append" appears in the window.
But, it is grayed out. So, I cannot edit it and choose "Full". Any ideas o
n
how to fix this so that it will do a full backup rather than an Append?
Also, what is the difference between "Target server connection" and "Local
server
connection" when configuring maintenance plans?
Upon reviewing the .bak files in Windows Explorer where the maintenance
plans are placing database backups, I noticed the letter "A" in the Attribut
e
column. Does this mean that the backup job was an "Append" job?
I temporarily disabled my maintenance plans and re-enabled them this AM. In
SQL Server Agent, I noticed that the phrase ".Subplan" appears not at the en
d
of the name of my jobs. Why is that, and is that "bad"?
Thanks!
childofthe1980sHi
"childofthe1980s" wrote:

> Hello:
You don't give the version you are using, have you applied SP2 and the
hotfixes?

> I am editing a backup maintenance plan. For some reason, the plan is doin
g
> an append backup. I realize that the word "Append" appears in the window.
> But, it is grayed out. So, I cannot edit it and choose "Full". Any ideas
on
> how to fix this so that it will do a full backup rather than an Append?
The append/overwrite option is applicable when you choose the file/files to
backup to rather than the directory option.
You can create a maintenance cleanup task to remove unwanted files.

> Also, what is the difference between "Target server connection" and "Local
> server
> connection" when configuring maintenance plans?
A new connection must have been created with this name look at the manage
connections option in the maintenance plan designer to see the properties of
each one.

> Upon reviewing the .bak files in Windows Explorer where the maintenance
> plans are placing database backups, I noticed the letter "A" in the Attrib
ute
> column. Does this mean that the backup job was an "Append" job?
No the A is the archive file attribute.

> I temporarily disabled my maintenance plans and re-enabled them this AM.
In
> SQL Server Agent, I noticed that the phrase ".Subplan" appears not at the
end
> of the name of my jobs. Why is that, and is that "bad"?
Not sure but it will only be part of the job description.

> Thanks!
> childofthe1980s
HTH
John

append backups and other mtn plan stuff

Hello:
I am editing a backup maintenance plan. For some reason, the plan is doing
an append backup. I realize that the word "Append" appears in the window.
But, it is grayed out. So, I cannot edit it and choose "Full". Any ideas on
how to fix this so that it will do a full backup rather than an Append?
Also, what is the difference between "Target server connection" and "Local
server
connection" when configuring maintenance plans?
Upon reviewing the .bak files in Windows Explorer where the maintenance
plans are placing database backups, I noticed the letter "A" in the Attribute
column. Does this mean that the backup job was an "Append" job?
I temporarily disabled my maintenance plans and re-enabled them this AM. In
SQL Server Agent, I noticed that the phrase ".Subplan" appears not at the end
of the name of my jobs. Why is that, and is that "bad"?
Thanks!
childofthe1980sHi
"childofthe1980s" wrote:
> Hello:
You don't give the version you are using, have you applied SP2 and the
hotfixes?
> I am editing a backup maintenance plan. For some reason, the plan is doing
> an append backup. I realize that the word "Append" appears in the window.
> But, it is grayed out. So, I cannot edit it and choose "Full". Any ideas on
> how to fix this so that it will do a full backup rather than an Append?
The append/overwrite option is applicable when you choose the file/files to
backup to rather than the directory option.
You can create a maintenance cleanup task to remove unwanted files.
> Also, what is the difference between "Target server connection" and "Local
> server
> connection" when configuring maintenance plans?
A new connection must have been created with this name look at the manage
connections option in the maintenance plan designer to see the properties of
each one.
> Upon reviewing the .bak files in Windows Explorer where the maintenance
> plans are placing database backups, I noticed the letter "A" in the Attribute
> column. Does this mean that the backup job was an "Append" job?
No the A is the archive file attribute.
> I temporarily disabled my maintenance plans and re-enabled them this AM. In
> SQL Server Agent, I noticed that the phrase ".Subplan" appears not at the end
> of the name of my jobs. Why is that, and is that "bad"?
Not sure but it will only be part of the job description.
> Thanks!
> childofthe1980s
HTH
John