Transaction Replication & Data archiving on SQL server 2000

[rajender_4@yahoo.com]

Hi techies


I have set up a Transaction replication from My Primary Server to
Secondary Server on Orders table.

Thousand of records gets inserted on Orders every hour which get
replicated on the secondary server. it works fine

reporting apps uses Secondory server's Orders table data for generating
reports .

The Problem :


Let say if i want to Remove older records from Orders table in the
primary serverwith out reflecting this change on the secondary server.

is there a way to PREVENT this operation /transaction to be propogated
to the secondary server.


Note : i am moving the records to another table (orders_Archive ) and
deleteing the rows from orders table . Also I need all the rows to be
present on the secondary server table.

Please advice ASAP

Regards,
Raj

[Oscar Santiesteban Jr.]

Re: Transaction Replication & Data archiving on SQL server 2000

In a case like this, why don't you stop the replication and do the
following:
1. create a linked server on the second server to the first server
2. create a stored procedure on the second server that gets data from the
1st server over to the second server.
this would be based on a query that only extracts orders from today's
date .
3. run the stored procedure as a job and run it every hour or every 2 hours
so that all records are moved to the 2nd server.

Then you can delete orders on the 1st server and it would not be deleted on
the 2nd server.

Oscar....

<rajender_4@yah oo.com> wrote in message
news:1113438382 .871152.136670@ l41g2000cwc.goo glegroups.com.. .[color=blue]
> Hi techies
>
>
> I have set up a Transaction replication from My Primary Server to
> Secondary Server on Orders table.
>
> Thousand of records gets inserted on Orders every hour which get
> replicated on the secondary server. it works fine
>
> reporting apps uses Secondory server's Orders table data for generating
> reports .
>
> The Problem :
>
>
> Let say if i want to Remove older records from Orders table in the
> primary serverwith out reflecting this change on the secondary server.
>
> is there a way to PREVENT this operation /transaction to be propogated
> to the secondary server.
>
>
> Note : i am moving the records to another table (orders_Archive ) and
> deleteing the rows from orders table . Also I need all the rows to be
> present on the secondary server table.
>
> Please advice ASAP
>
> Regards,
> Raj
>[/color]

[Raj]

Re: Transaction Replication &amp; Data archiving on SQL server 2000

Well I need any changes to existing records ( column updates) also be
propogated to secondary automatically,w ithout me writing any code to
detect the changes.... .

This is taken care in the transactional replication .

The issue of Archiving/delete a lot of record is causing a issue....

[Greg D. Moore \(Strider\)]

Re: Transaction Replication &amp; Data archiving on SQL server 2000


<rajender_4@yah oo.com> wrote in message
news:1113438382 .871152.136670@ l41g2000cwc.goo glegroups.com.. .[color=blue]
> Hi techies
> is there a way to PREVENT this operation /transaction to be propogated
> to the secondary server.
>
>
> Note : i am moving the records to another table (orders_Archive ) and
> deleteing the rows from orders table . Also I need all the rows to be
> present on the secondary server table.
>[/color]

Hand Edit the update/delete replication stored procs on the subscribing
server.

[color=blue]
> Please advice ASAP
>
> Regards,
> Raj
>[/color]

[rajender.mahato@gmail.com]

Re: Transaction Replication &amp; Data archiving on SQL server 2000

Thanks Greg .. I will Edit the replicaiton SP

Raj