Production data is accumulating, I would like to archive old data.
I want the archive database has the whole and complete data for READ ONLY
(historical reporting, audit checking, etc).
Then production database becomes lightweight and able to perform update
efficiently.
Without replication, what do you suggest? Thanks for any help
I suggest a DTS task that runs over night and copies all-day new rows to the
backup database, so you can query and update the production database
efficiently. Also I suggest another server to host the backup database.
Hope this help.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Production data is accumulating, I would like to archive old data.
> I want the archive database has the whole and complete data for READ ONLY
> (historical reporting, audit checking, etc).
> Then production database becomes lightweight and able to perform update
> efficiently.
> Without replication, what do you suggest? Thanks for any help
>
|||Thanks for suggestion, but could DTS determine those existing rows that is
modified?
"Rodrigo Daz" <rdiazconcha@.hotmail.com.anti-spam.com> glsD:umoLiKXTGHA.1492@.TK2MSFTNGP10.phx.g bl...
I suggest a DTS task that runs over night and copies all-day new rows to the
backup database, so you can query and update the production database
efficiently. Also I suggest another server to host the backup database.
Hope this help.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Production data is accumulating, I would like to archive old data.
> I want the archive database has the whole and complete data for READ ONLY
> (historical reporting, audit checking, etc).
> Then production database becomes lightweight and able to perform update
> efficiently.
> Without replication, what do you suggest? Thanks for any help
>
|||No it could not, you need to flag these updated rows maybe with a column
with datetime datatype. Another solution that comes to my mind is using an
update/insert trigger to insert those rows to the backup database... in
this case you won't need DTS.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%231GaKwXTGHA.4616@.TK2MSFTNGP10.phx.gbl...
> Thanks for suggestion, but could DTS determine those existing rows that is
> modified?
>
>
> "Rodrigo Daz" <rdiazconcha@.hotmail.com.anti-spam.com>
> glsD:umoLiKXTGHA.1492@.TK2MSFTNGP10.phx.g bl...
> I suggest a DTS task that runs over night and copies all-day new rows to
> the
> backup database, so you can query and update the production database
> efficiently. Also I suggest another server to host the backup database.
> Hope this help.
> "Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
> news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
>
Showing posts with label archive. Show all posts
Showing posts with label archive. Show all posts
Wednesday, March 7, 2012
Data Archiving
Production data is accumulating, I would like to archive old data.
I want the archive database has the whole and complete data for READ ONLY
(historical reporting, audit checking, etc).
Then production database becomes lightweight and able to perform update
efficiently.
Without replication, what do you suggest? Thanks for any helpI suggest a DTS task that runs over night and copies all-day new rows to the
backup database, so you can query and update the production database
efficiently. Also I suggest another server to host the backup database.
Hope this help.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Production data is accumulating, I would like to archive old data.
> I want the archive database has the whole and complete data for READ ONLY
> (historical reporting, audit checking, etc).
> Then production database becomes lightweight and able to perform update
> efficiently.
> Without replication, what do you suggest? Thanks for any help
>|||Thanks for suggestion, but could DTS determine those existing rows that is
modified?
"Rodrigo Díaz" <rdiazconcha@.hotmail.com.anti-spam.com> ¼¶¼g©ó¶l¥ó·s»D:umoLiKXTGHA.1492@.TK2MSFTNGP10.phx.gbl...
I suggest a DTS task that runs over night and copies all-day new rows to the
backup database, so you can query and update the production database
efficiently. Also I suggest another server to host the backup database.
Hope this help.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Production data is accumulating, I would like to archive old data.
> I want the archive database has the whole and complete data for READ ONLY
> (historical reporting, audit checking, etc).
> Then production database becomes lightweight and able to perform update
> efficiently.
> Without replication, what do you suggest? Thanks for any help
>|||No it could not, you need to flag these updated rows maybe with a column
with datetime datatype. Another solution that comes to my mind is using an
update/insert trigger to insert those rows to the backup database... in
this case you won't need DTS.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%231GaKwXTGHA.4616@.TK2MSFTNGP10.phx.gbl...
> Thanks for suggestion, but could DTS determine those existing rows that is
> modified?
>
>
> "Rodrigo Díaz" <rdiazconcha@.hotmail.com.anti-spam.com>
> ¼¶¼g©ó¶l¥ó·s»D:umoLiKXTGHA.1492@.TK2MSFTNGP10.phx.gbl...
> I suggest a DTS task that runs over night and copies all-day new rows to
> the
> backup database, so you can query and update the production database
> efficiently. Also I suggest another server to host the backup database.
> Hope this help.
> "Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
> news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
>> Production data is accumulating, I would like to archive old data.
>> I want the archive database has the whole and complete data for READ ONLY
>> (historical reporting, audit checking, etc).
>> Then production database becomes lightweight and able to perform update
>> efficiently.
>> Without replication, what do you suggest? Thanks for any help
>
I want the archive database has the whole and complete data for READ ONLY
(historical reporting, audit checking, etc).
Then production database becomes lightweight and able to perform update
efficiently.
Without replication, what do you suggest? Thanks for any helpI suggest a DTS task that runs over night and copies all-day new rows to the
backup database, so you can query and update the production database
efficiently. Also I suggest another server to host the backup database.
Hope this help.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Production data is accumulating, I would like to archive old data.
> I want the archive database has the whole and complete data for READ ONLY
> (historical reporting, audit checking, etc).
> Then production database becomes lightweight and able to perform update
> efficiently.
> Without replication, what do you suggest? Thanks for any help
>|||Thanks for suggestion, but could DTS determine those existing rows that is
modified?
"Rodrigo Díaz" <rdiazconcha@.hotmail.com.anti-spam.com> ¼¶¼g©ó¶l¥ó·s»D:umoLiKXTGHA.1492@.TK2MSFTNGP10.phx.gbl...
I suggest a DTS task that runs over night and copies all-day new rows to the
backup database, so you can query and update the production database
efficiently. Also I suggest another server to host the backup database.
Hope this help.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Production data is accumulating, I would like to archive old data.
> I want the archive database has the whole and complete data for READ ONLY
> (historical reporting, audit checking, etc).
> Then production database becomes lightweight and able to perform update
> efficiently.
> Without replication, what do you suggest? Thanks for any help
>|||No it could not, you need to flag these updated rows maybe with a column
with datetime datatype. Another solution that comes to my mind is using an
update/insert trigger to insert those rows to the backup database... in
this case you won't need DTS.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%231GaKwXTGHA.4616@.TK2MSFTNGP10.phx.gbl...
> Thanks for suggestion, but could DTS determine those existing rows that is
> modified?
>
>
> "Rodrigo Díaz" <rdiazconcha@.hotmail.com.anti-spam.com>
> ¼¶¼g©ó¶l¥ó·s»D:umoLiKXTGHA.1492@.TK2MSFTNGP10.phx.gbl...
> I suggest a DTS task that runs over night and copies all-day new rows to
> the
> backup database, so you can query and update the production database
> efficiently. Also I suggest another server to host the backup database.
> Hope this help.
> "Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
> news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
>> Production data is accumulating, I would like to archive old data.
>> I want the archive database has the whole and complete data for READ ONLY
>> (historical reporting, audit checking, etc).
>> Then production database becomes lightweight and able to perform update
>> efficiently.
>> Without replication, what do you suggest? Thanks for any help
>
Data Archiving
Hi ,
I need to archive my production database to a new Server....
Is it possible to move data using INSERT INTO ServerName.DBName.dbo.TableName from the current Database Server!!!
Do I need to create a linked server to do this...or shoud I go for DTS..
Thanks
Cheriyan.You have too many options :
1.- Backup and restore in the new server.
2.- Create a linked server and generate a script to the all the insert..into your other tables.
3.- Use DTS.
If this is a one-time move, because a hardware upgrade, not a task that should be somewhat performed periodically, then take option 1.
Reagrds,
I need to archive my production database to a new Server....
Is it possible to move data using INSERT INTO ServerName.DBName.dbo.TableName from the current Database Server!!!
Do I need to create a linked server to do this...or shoud I go for DTS..
Thanks
Cheriyan.You have too many options :
1.- Backup and restore in the new server.
2.- Create a linked server and generate a script to the all the insert..into your other tables.
3.- Use DTS.
If this is a one-time move, because a hardware upgrade, not a task that should be somewhat performed periodically, then take option 1.
Reagrds,
Data Archiving
If I have a huge database, I use the data archiving to archieve to table. eg. I have table A, a huge table, I archive some of the old data to table B. Let said my data is some transaction data. When I would like to retrieve it to generate a report, a historical report for my yearly transactions. Let said I've split the March 2003 data from table A into table B. which mean only April to July transactions remain in table A. But now I want to retrieve the data from Jannuary till now on, to generate a half year report. How should I do it?? Should I just using a SQL query to select data from table A where date from Jan to July, then the system will automatically look back for the data that have been archived into table B and retrieve it for me?? Or I should use 2 seperate queries to select it from table A (Apr-July) and table B (Jan-Mar)?esupport,
you could create a view like the following:
create View MyData as
select * from MyOldData
union all
select * from MyActualData
go
and to query from this view instead of querying from thge tables.|||If using View instead of querying from table, will this affect my performance or my database size? will it slowing down my system performance? Because normally my database will be quite big in size. 2-3 GB. Is this the optimum solution to solve the problem?|||Your other alternative is to maintain your current stragey (if it's working) and possibly create an aggregate table. This will only work in some circumstances, it depends on the needs of your business.
For instance if table A and B contains individual transactions (lets say 100,000 a day or something). However your reports only ever report at a daily level, then once a day summerise the data:
select date, sum(sales)
from table A
group by date
Load that summerised data into your aggregate table and you should be good to go..
This would cut down the table size of the table you are reporting from by n rows a day and still maintain all your transactions in your other table...
This is sort of moving into the data warehousing concepts and it may not work for you, but I'd just thought I'd suggest it..
Good luck.|||Thanks for the suggestions. It's help a lot.
BTW, is there any tools or built-in command to archive data from my production database into a history archive database in SQL Server 2000?|||Depending on how complex you want to get it might be possible to get away with just a stored procedure or 2... Otherwise the DTS tool might come in handy and if you want to get really serious, then Analysis Server if you have the licence and is appropriate etc...
Have Fun..|||If I'm using the UNION ALL suggested by kukuk
create View MyData as
select * from MyCurrentData
union all
select * from MyHistoryData
go
Let said I've archived my database till March into table MyHistoryData. Now I would like to retrieve some data for reporting purpose. I want to retrieve data from June to July, how would be the performance? How would the query performing? Will it "search" through all the data in table MyCurrentData and MyHistoryData?
How's the performing comparison by using a single query "SELECT * FROM MyCurrentData" compare with using UNION ALL as the example given above to retrieve data from 2 tables?|||Hi,
I think, BOL will answer your questions.
Look there for "Using Partitioned Views"|||Another question from me.
What is the size of the database table after achiving?
Will it become smaller or same size?
Let said my database before archive is 2GB, after archive, willl it become smaller? less than 2GB?|||Any ideas on back-end processing?
Lets say I'm doing retrieving data history for reporting. In the mean time, I would like to continue with my front-end processing, such as daily transactions?
In my current system, while I'm doing my data retrieve, I cannot do anything before the retrieving process finished. One of the issue I think is because of the size of my database and the technic that I'm using to retrieve the data is bad. Hope to hear more advices.
you could create a view like the following:
create View MyData as
select * from MyOldData
union all
select * from MyActualData
go
and to query from this view instead of querying from thge tables.|||If using View instead of querying from table, will this affect my performance or my database size? will it slowing down my system performance? Because normally my database will be quite big in size. 2-3 GB. Is this the optimum solution to solve the problem?|||Your other alternative is to maintain your current stragey (if it's working) and possibly create an aggregate table. This will only work in some circumstances, it depends on the needs of your business.
For instance if table A and B contains individual transactions (lets say 100,000 a day or something). However your reports only ever report at a daily level, then once a day summerise the data:
select date, sum(sales)
from table A
group by date
Load that summerised data into your aggregate table and you should be good to go..
This would cut down the table size of the table you are reporting from by n rows a day and still maintain all your transactions in your other table...
This is sort of moving into the data warehousing concepts and it may not work for you, but I'd just thought I'd suggest it..
Good luck.|||Thanks for the suggestions. It's help a lot.
BTW, is there any tools or built-in command to archive data from my production database into a history archive database in SQL Server 2000?|||Depending on how complex you want to get it might be possible to get away with just a stored procedure or 2... Otherwise the DTS tool might come in handy and if you want to get really serious, then Analysis Server if you have the licence and is appropriate etc...
Have Fun..|||If I'm using the UNION ALL suggested by kukuk
create View MyData as
select * from MyCurrentData
union all
select * from MyHistoryData
go
Let said I've archived my database till March into table MyHistoryData. Now I would like to retrieve some data for reporting purpose. I want to retrieve data from June to July, how would be the performance? How would the query performing? Will it "search" through all the data in table MyCurrentData and MyHistoryData?
How's the performing comparison by using a single query "SELECT * FROM MyCurrentData" compare with using UNION ALL as the example given above to retrieve data from 2 tables?|||Hi,
I think, BOL will answer your questions.
Look there for "Using Partitioned Views"|||Another question from me.
What is the size of the database table after achiving?
Will it become smaller or same size?
Let said my database before archive is 2GB, after archive, willl it become smaller? less than 2GB?|||Any ideas on back-end processing?
Lets say I'm doing retrieving data history for reporting. In the mean time, I would like to continue with my front-end processing, such as daily transactions?
In my current system, while I'm doing my data retrieve, I cannot do anything before the retrieving process finished. One of the issue I think is because of the size of my database and the technic that I'm using to retrieve the data is bad. Hope to hear more advices.
Data Archiving
Production data is accumulating, I would like to archive old data.
I want the archive database has the whole and complete data for READ ONLY
(historical reporting, audit checking, etc).
Then production database becomes lightweight and able to perform update
efficiently.
Without replication, what do you suggest? Thanks for any helpI suggest a DTS task that runs over night and copies all-day new rows to the
backup database, so you can query and update the production database
efficiently. Also I suggest another server to host the backup database.
Hope this help.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Production data is accumulating, I would like to archive old data.
> I want the archive database has the whole and complete data for READ ONLY
> (historical reporting, audit checking, etc).
> Then production database becomes lightweight and able to perform update
> efficiently.
> Without replication, what do you suggest? Thanks for any help
>|||Thanks for suggestion, but could DTS determine those existing rows that is
modified?
"Rodrigo Daz" <rdiazconcha@.hotmail.com.anti-spam.com> glsD:umoLiK
XTGHA.1492@.TK2MSFTNGP10.phx.gbl...
I suggest a DTS task that runs over night and copies all-day new rows to the
backup database, so you can query and update the production database
efficiently. Also I suggest another server to host the backup database.
Hope this help.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Production data is accumulating, I would like to archive old data.
> I want the archive database has the whole and complete data for READ ONLY
> (historical reporting, audit checking, etc).
> Then production database becomes lightweight and able to perform update
> efficiently.
> Without replication, what do you suggest? Thanks for any help
>|||No it could not, you need to flag these updated rows maybe with a column
with datetime datatype. Another solution that comes to my mind is using an
update/insert trigger to insert those rows to the backup database... in
this case you won't need DTS.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%231GaKwXTGHA.4616@.TK2MSFTNGP10.phx.gbl...
> Thanks for suggestion, but could DTS determine those existing rows that is
> modified?
>
>
> "Rodrigo Daz" <rdiazconcha@.hotmail.com.anti-spam.com>
> glsD:umoLiKXTGHA.1492@.TK2MSFTNGP10.phx.gbl...
> I suggest a DTS task that runs over night and copies all-day new rows to
> the
> backup database, so you can query and update the production database
> efficiently. Also I suggest another server to host the backup database.
> Hope this help.
> "Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
> news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
>
I want the archive database has the whole and complete data for READ ONLY
(historical reporting, audit checking, etc).
Then production database becomes lightweight and able to perform update
efficiently.
Without replication, what do you suggest? Thanks for any helpI suggest a DTS task that runs over night and copies all-day new rows to the
backup database, so you can query and update the production database
efficiently. Also I suggest another server to host the backup database.
Hope this help.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Production data is accumulating, I would like to archive old data.
> I want the archive database has the whole and complete data for READ ONLY
> (historical reporting, audit checking, etc).
> Then production database becomes lightweight and able to perform update
> efficiently.
> Without replication, what do you suggest? Thanks for any help
>|||Thanks for suggestion, but could DTS determine those existing rows that is
modified?
"Rodrigo Daz" <rdiazconcha@.hotmail.com.anti-spam.com> glsD:umoLiK
XTGHA.1492@.TK2MSFTNGP10.phx.gbl...
I suggest a DTS task that runs over night and copies all-day new rows to the
backup database, so you can query and update the production database
efficiently. Also I suggest another server to host the backup database.
Hope this help.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Production data is accumulating, I would like to archive old data.
> I want the archive database has the whole and complete data for READ ONLY
> (historical reporting, audit checking, etc).
> Then production database becomes lightweight and able to perform update
> efficiently.
> Without replication, what do you suggest? Thanks for any help
>|||No it could not, you need to flag these updated rows maybe with a column
with datetime datatype. Another solution that comes to my mind is using an
update/insert trigger to insert those rows to the backup database... in
this case you won't need DTS.
"Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
news:%231GaKwXTGHA.4616@.TK2MSFTNGP10.phx.gbl...
> Thanks for suggestion, but could DTS determine those existing rows that is
> modified?
>
>
> "Rodrigo Daz" <rdiazconcha@.hotmail.com.anti-spam.com>
> glsD:umoLiKXTGHA.1492@.TK2MSFTNGP10.phx.gbl...
> I suggest a DTS task that runs over night and copies all-day new rows to
> the
> backup database, so you can query and update the production database
> efficiently. Also I suggest another server to host the backup database.
> Hope this help.
> "Need Help" <DONTSPAM@.DONTSPAM.com> wrote in message
> news:%23rz8ERWTGHA.5656@.TK2MSFTNGP11.phx.gbl...
>
Data archive
Hello, everyone:
What does "data archive" mean in SQL Server? Is it same thing as archiving in Oracle?
Thanks.
ZYTGo check books online
Archiving and Restoring Databases
An archive file for a Microsoft SQL Server 2000 Analysis Services database contains the contents of the directory named the same as the database. For example, the archive file for the FoodMart 2000 sample database stores the contents of the FoodMart 2000 directory. The default path of this directory is:
C:\Program Files\Microsoft Analysis Services\Data\FoodMart 2000
Although archiving can mean many things...what does it mean to you in Oracle?
What does "data archive" mean in SQL Server? Is it same thing as archiving in Oracle?
Thanks.
ZYTGo check books online
Archiving and Restoring Databases
An archive file for a Microsoft SQL Server 2000 Analysis Services database contains the contents of the directory named the same as the database. For example, the archive file for the FoodMart 2000 sample database stores the contents of the FoodMart 2000 directory. The default path of this directory is:
C:\Program Files\Microsoft Analysis Services\Data\FoodMart 2000
Although archiving can mean many things...what does it mean to you in Oracle?
Subscribe to:
Posts (Atom)