Trigger to update another database

[Guest_imported]

I have 3 SQL servers. Lets call them SQL1, SQL2, SQL3.
Replication is setup between SQL1 and SQL2. The data is very liquid and is deleted frequently for use on an outside application, we need low number of records for high performance. Response times are crucial to us for retrieval.
SQL3 is going to be our master archive.
Problems I am having, if replication is used from SQL3 to SQL2, if I delete from SQL3, SQL2s records are deleted as well. Can I prevent delete operations to not get replicated to the subscriber? If Not, how do I make a trigger or scheduled job to get new records over to SQL2 to warehouse the data?

Appreciation is advance to assistance.

D. Koenig

 

[aalmeida]

I think you need to change a little the way you manipulate you data, you could use some staging table on the source SQL1 and SQL2 where you could them keep a history of your transaction and then using DTS move regularly that data to SQL3, that implyes that you have to give a more transactional aproach to your data manipulation as well as pay a special atention to the metadata. On MSDN KB there some sample code very close to what you need.

http://msdn.microsoft.com/library/default.asp?url=/nhp/Default.asp?contentid=28000409

AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"