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