Best performance approach to history mechanism?
- by Royi Namir
We are going to create History Mechanism for our changes in DB (DART in pic) via Triggers.
we have 600 tables.
Each record that will be changed - the trigger will insert the deleted one into XXX.
regarding to the XXX :
option 1 : clone each table in "Dart" DB and each table now will have a "sister table"
e.g. :
Table1 will have Table1_History
problems :
we will have 1200 tables
programmer can do mistakes by working on wrong tables...
option 2 : make a new DB (DART_2005 in pic) and the history tables will be there
option 3 : use linked server which stores the Db which will contain the history tables.
question :
1) which option gives the best performance ( I guess 3 is not - but is it 1 or 2 or same ?)
2) Does option 2 is acting like "linked server" ( in queries we will need to select from both DB's...)
3) What is the best practice approach ?
