January 26, 2012 at 4:39 am
I have two databases in a stock trading company that will use replication to sync data. The propose method will be Transactional Replication at 1min intervals.
MAIN- Main Database (Located at Head Office)
This database contains all trading and admin and performance data.
SUB - Trading Database (Located at Johannesburg Stock Exchange)
This database contains only live trades that are currently in the market.
I will use replication to sync data from SUB to MAIN with replication. Everyday 100s of trade data records are inserted on SUB on daily basis. This data need to be sync to MAIN where it will be processed for admin and performance statistics. After a Trade has been closed, all its data needs to be removed from Thor. This means that SUB will contain limited data, which will ensure very small high speed trading database.
Here is my question:
I will delete this "old" data on SUB after I have synced it to MAIN. But because I'm using the Transactional method of Replication, I fear when I sync again the log will state that I deleted data on SUB and thus delete it on MAIN.
SO how can I remove the data on SUB and not worry about it being deleted on MAIN due to the Transactional method.
I was thinking towards something in the line:
Temporarily Disable Logging to Transaction Log
Executing the DELETE statements but ensuring that it doesn't get written to the log.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
January 26, 2012 at 10:21 am
You can specify in your Transactional replication to ignore delete command.
Another way is to modify the Stored proc (Replication SP in your MAIN where you are replicating to) to do nothing when a delete happen.
-Roy
January 26, 2012 at 1:17 pm
Wait ... so how do I that?
PS: I realised my previous response was epicly idiotic.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
January 31, 2012 at 10:00 am
You could also put the delete statements in a procedure on SUB, and then, publish the execution of the stored procedure. On MAIN, write the procedure so that it does not delete the records in question.
So you have usp_DeleteOldRecords on both Main and Sub. In SUB, it actually deletes the records. In MAIN, it can do nothing, log that it ran to a table, whatever, but not delete. You replicate the execution of the procedure.
When you then run the proc in SUB, the deletes happen, but what comes across in replication is only "execute usp_DeleteOldRecords", not the actual deletes. The MAIN version of usp_DeleteOldRecords runs, but since the proc is different in MAIN, your records are not deleted.
February 2, 2012 at 9:45 am
Rofty (1/26/2012)
Wait ... so how do I that?PS: I realised my previous response was epicly idiotic.
Simplest way is to Alter the sp_msdel_tablename stored procedure on the subscriber and add a return statement right at the top.
Alternatively, run this in a transaction sp_dropsubscription with a specific ariticle name,
sp_addsubscription .... @sync_type = N'none'.....@del_cmd =N'NONE'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply