I want to keep several tables in two different databases mirrored. Both databases are within the same server, and both are SQL Server dbs. This mirroring only affects the data they contain, not the structure (although would be great if any table alteration was reflected as well). The data volume in those tables is low and only changes from time to time. There will be other tables in both databases that should't be mirrored. The following list contains all the options I think I have:
- Use triggers. Perhaps the best option, but as I want to mirror lots of tables this might imply more work.
- Implemente SQL Server replication. I did this in the past on another pair of dbs and was really difficult to set up and wasn't working as desired, in terms of performance. This time the conditions are completely different, so it might work.
Does anybody know if there are other options? Which one do you prefer and why?