Trigger on replicated DB

Question

I have database db1 with schema sch1. I want to create another schema sch2 which will be used by a different application. The tables in sch2 will be populated from tables in sch1. My approach is to create triggers on insert, update and delete to perform this task.

My questions are

1. Is this a good way to do this or is there a better way?

2. I don’t want to affect the performance on db1 so I want to replicate it to a different server and create the triggers on the subscriber DB, so the replicated server will have Sch1 and Sch2 on it. Can I do that?

This is on SQL Server 2012 Standard Edition.

Note: tables in sch2 have different names and different column names. Also more than one table in sch2 may get populated from data from sch1.

Answer 1

It looks like replication is what you need here. There are different flavors of replication (merge, transactional and snapshot) that are suitable in different situations. In this case, it looks like transactional replication is what you need, unless changes on the secondary copy have to be propagated to the original copy of the data.

I fail to understand the role of the second schema. If you need both schemas at the primary database, then trigger could be a way to keep the two schemas in sync. If you need the second copy of the tables only at the second server, you can replicate the tables from sch1 to sch2 without issues.