1

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.

1 Answer 1

0

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.

2
  • For example Sch1 has T1 and Sch2 has T2 , T3 . once i insert a record in T1 i need to transfer some fields to T2 and some fields to T3.
    – sebeid
    Jul 26, 2016 at 14:54
  • I can keep it on primary but i want to avoid the performance issue will caused by triggers
    – sebeid
    Jul 26, 2016 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.