By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,070 Members | 1,238 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,070 IT Pros & Developers. It's quick & easy.

Transaction Replication & Data archiving on SQL server 2000

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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....

<ra********@yahoo.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.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

Jul 23 '05 #2

P: n/a
Raj
Well I need any changes to existing records ( column updates) also be
propogated to secondary automatically,without 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....

Jul 23 '05 #3

P: n/a

<ra********@yahoo.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
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.

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

Please advice ASAP

Regards,
Raj

Jul 23 '05 #4

P: n/a
Thanks Greg .. I will Edit the replicaiton SP

Raj

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.