asked
Sql server database replication Model
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.
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
Sounds like a case for transactional replication with don't replicate delete commands
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).
20 People are following this question.
