data archiving and replication

Hi Guys,
i am using sql 2000 transactional replication to have
reporting server.
in the oltp server tables i want to keep only one years
data (around 1o million) while in the reporting server i
will export the archived data and keep all the data so
that i can take reportfor any period.
will this make any problem for replication.like
syncronisation or anything?
pls advice me.
Right click on your publication select publication properties, go to the
articles tab, click on command. Change your delete command to NONE on your
publisher.
"biju george" <bijupg@.hotmail.com> wrote in message
news:11d3a01c410d3$b68b68b0$a401280a@.phx.gbl...
> Hi Guys,
> i am using sql 2000 transactional replication to have
> reporting server.
> in the oltp server tables i want to keep only one years
> data (around 1o million) while in the reporting server i
> will export the archived data and keep all the data so
> that i can take reportfor any period.
> will this make any problem for replication.like
> syncronisation or anything?
> pls advice me.
|||Biju,
the way you implement this depends on what type of replication you're
considering. For snapshot replication ,you could use static filters. Each
time you want to archive off some data, you modify the filter clause. Be
sure to modify the article properties, naming conflicts to "Delete data in
existing table that matches the row filter statement." - before the
synchronization, there won't be any matching data as it only exists on the
publisher. After that you remove the data on the publisher. For
transactional you could set it up as per usual and just select not to
replicate delete commands - all new records will go to the subscriber and on
the publisher old records can be deleted with this not affecting the
subscriber. Finally, just to give you some other options, you could archive
the data to another table on the publisher. On the publisher create a view
which unions the data, and then create an index on the view and replicate
the indexed view. Actually this would probably be my choice, as it maintains
the source data on the publisher.
HTH,
Paul Ibison
|||i am using transactional replication. i deleted old data
from publisher and i copied the archived data in another
table on publisher . now the data on the publisher and
subsriber are SAME,
but for reporting to get all the data i will export the
old data From the archived table at publisher to the
orginal table at subscriber using DTS.
using the indexed view will be slower in report generation
is'nt it?
>--Original Message--
>Biju,
>the way you implement this depends on what type of
replication you're
>considering. For snapshot replication ,you could use
static filters. Each
>time you want to archive off some data, you modify the
filter clause. Be
>sure to modify the article properties, naming conflicts
to "Delete data in
>existing table that matches the row filter statement." -
before the
>synchronization, there won't be any matching data as it
only exists on the
>publisher. After that you remove the data on the
publisher. For
>transactional you could set it up as per usual and just
select not to
>replicate delete commands - all new records will go to
the subscriber and on
>the publisher old records can be deleted with this not
affecting the
>subscriber. Finally, just to give you some other options,
you could archive
>the data to another table on the publisher. On the
publisher create a view
>which unions the data, and then create an index on the
view and replicate
>the indexed view. Actually this would probably be my
choice, as it maintains
>the source data on the publisher.
>HTH,
>Paul Ibison
>
>.
>
|||Hilary's reply is the way to go to continuously keep the reporting server
upto date. For his reply to work you'll need all the data in one table on
the publisher initially, then remove the 'archived' rows. If you are to
continue partitioning the data on the publisher, then you can use my
snapshot advice to append the data - no need for DTS, although it is
functionally the same methodology here. My suggestion for indexed views also
works. It wouldn't be slower for reporting, because the indexed view
amalgamates the data on the publisher and replicates it to one table on the
subscriber. However, changes to data on the publisher will be fractionally
slower as the indexed view will get updated behind the scenes.
Regards,
Paul Ibison
|||for this i have to create indexed view or only replicate
the view?
will it give more load to the publisher.?
>--Original Message--
>Hilary's reply is the way to go to continuously keep the
reporting server
>upto date. For his reply to work you'll need all the data
in one table on
>the publisher initially, then remove the 'archived' rows.
If you are to
>continue partitioning the data on the publisher, then you
can use my
>snapshot advice to append the data - no need for DTS,
although it is
>functionally the same methodology here. My suggestion for
indexed views also
>works. It wouldn't be slower for reporting, because the
indexed view
>amalgamates the data on the publisher and replicates it
to one table on the
>subscriber. However, changes to data on the publisher
will be fractionally
>slower as the indexed view will get updated behind the
scenes.
>Regards,
>Paul Ibison
>
>.
>
|||Biju,
actually I just tried this out for you and unfortunately the restrictions on indexed views are too severe for this to be a viable solution - sorry. So, that leaves you with Hilary's advice in which case you need to initially amalgamate the data on the pub
lisher, create the publication, synchronize then delete the archived data on the publisher. Or, use a linked server or snapshot replication. Also, if you want to keep the tables separate, you could use replication to copy the data to separate tables on th
e subscriber (with different names) and have a view there to amalgamate the data.
Regards,
Paul Ibison
|||already i have the archived data in a seperate table at
publisher.so i will export this data to the orginal table
at subscriber so that report will be generated from table
having complete table.
will this idea give any problem in future ?from my testing
it works fine.
pls advice me.
>--Original Message--
>Biju,
>actually I just tried this out for you and unfortunately
the restrictions on indexed views are too severe for this
to be a viable solution - sorry. So, that leaves you with
Hilary's advice in which case you need to initially
amalgamate the data on the publisher, create the
publication, synchronize then delete the archived data on
the publisher. Or, use a linked server or snapshot
replication. Also, if you want to keep the tables
separate, you could use replication to copy the data to
separate tables on the subscriber (with different names)
and have a view there to amalgamate the data.
>Regards,
>Paul Ibison
>.
>
|||Biju,
if you want to be able to report on ALL your data at the subscriber, you initially need to amalgamate the records from 2 tables - your live table and your archive table - on the publisher into one on the subscriber. The easiest way to do this is to first
do it manually (eg using linked servers). Then apply Hilary's solution to the production table, stating that the schema already exists on the subscriber and making sure to not replicate deletes.
Regards,
Paul Ibison
|||Paul,
If I understand properly, there are actually two types of deletes that would
occur:
1. Deletes relating to normal transaction management.
2. Deletes relating to archiving data. Here data is copied to the archive
table and deleted from the live table.
If deletes are not allowed to happen at the subscriber then it affects both
cases of deletes. What should actually happen is that deletes for case 1
should be replicated but not those for case 2. This is because if we did not
allow case 1 deletes to replicate to subscriber, the latter would contain
data that does not exist in publisher anymore, with all the consequences
thereof (wrong reporting at subscriber?).
I think the other solutions suggested should work, namely:
1. Publisher has tables Live and Archive.
2. Subscriber has same tables and each table gets data from the
corresponding tables from Publisher. Synchronisation should happen normally
for both tables.
3. Create a normal view on subscriber with UNION of data from both tables.
Use this view for reporting purposes.
Hoping I am not shooting off-target!
Raj Moloye
Mauritius

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

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
>

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,

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.

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

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?