We are doing Ecom - Application. scenario: we have two databases A,B. A- is active database - interacts with website users B- Replication/repository database for A. i want the replication in which if i add/update a record in Database-A then must be added/updated to Database-B, means at any point of time we should able to make B database to Active. And also, We are planning to delete transactions data in database A as day to day orders data is increasing and it is directly interacting with the website users. In this scenario should not delete transaction in Database B as it should behave as a repository database. please suggest me the right approach...thank in advance.
Seems to me that your plan to delete transactions from database A means that your requirement to be able to use database B in a disaster scenario cannot be met. When database B becomes active, the deleted transactions (from database A) will be visible again to your users. Perhaps you could replicate the delete transactions but use a custom stored procedure for tables where you don't want deletes to occur. In this stored procedure, you could insert the deleted records into another table (or flag the record as logically deleted...which might need other changes in your application).