January 24, 2005 at 11:08 pm
There is an application that transforms data from a text file into several tables in a SQL database. Since the amount of data is large there is a mirror database of the production database. The mirror database would not have the data stored in the production database tables. So lets suppose the production database is S1 then a mirror database in terms of schema is present in S2.
The application transforms data from the text file into the respective tables in S2. At a scheduled time we need to migrate this data into the S1. After successful migration, data in S2 tables needs to be deleted.
One solution proposed was replication using Merged publication with S1 being the publisher and S2 being the subscriber. But this scenario fails when synchronization happens after deletion of data in the mirror database. The synchronization deleted the data in the production database.
So is there any way in which we can make the publisher ignore deletions made at the subscriber end?
If there is no way, then is there a method to achieve the desired functionality?
January 26, 2005 at 2:50 am
I don't think replication is an ideal solution. You have a data transformation /import issue, not a data replication issue.
You do not say how large your databases are? Why do you want to import data to S1 via S2 only to ultimately delete it from S2? Is someone checking it before it's imported to S2?
What about availability? Does S2 have to be available 24/7?
I would either import the data directly to S1. Alternatively, I would import to S2 and then create a DTS package to export from S2, import to S1 and delete from S2.
How are you importing to S1? You could also import to S2 in exactly the same way as the import to S1, but change the server/database parameters.
I hope that helps.
Claire
January 26, 2005 at 7:47 am
I'm interested in what happens after the data is deleted at the subscriber? Does resynchronization need to happen?
Anyway, to solve the original issue, you could synchronize, then disable the merge delete trigger at the subscriber before deleting the subscriber data. Alternatively you could just drop the publication after synchronizing, then delete the subscriber data.
Rgds,
Paul Ibison (SQL Server MVP)
Paul Ibison
Paul.Ibison@replicationanswers.com
January 27, 2005 at 1:58 am
Thanks for the replies. Let me try and clear certain doubts that the original question has created.
1) Why need two servers S1 and S2.
Well S1 is a production database and several applications access the tables in this database. The application which is writing data to S2 needs to use cursors to perform row by row (typical no of records per table is 100000) procesing based on certain business rules. The business does not allow cursors to run on the production database (S1) so we need a mirror database which is S2. S2 need not have data of S1 but it need unique identity seeds of tables present in the S1 database.
2) Why not use DTS to import data from S2?
Well the problem is the data in S2 has to be inserted as it is including the identity values. But the DBA of S1 prohibits turning identiy off during any operation including bulk operation.
3) Why delete data in S2?
Since we have a huge amountof data per transformation typically in the range of 100000 we need to delete these once the data is successflly migrated to S1.
Hope this answers the questions raised.
January 27, 2005 at 7:51 am
Why don't you just change the replication triggers on s2 to only fire for inserts and updates, but not for deletes. This way a delete won't be recorded as a record which needs to be merged.
M
[font="Verdana"]Markus Bohse[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply