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 ?