In our SQL Server DB, we have about some 800+ tables and there are 40 - 50 tables are business critical tables. MIS team needs to generate reports based on those 50 business tables.
Those 50 tables gets updated frequently. MIS team requires those delta records (update/inserted/deleted)
What would be the best solution?
We have few approches here 1.Always On 2.Replication 3.Mirroring 4.Introducing new column (LastModifiedDate & creating index) in those 50 tables and pulling those records periodically and populating it to MIS environment.
There will be huge code change for the new column LastModifiedDate approach.
Based on those 50 tables, we have huge number of stored procedures which it has Insert/Update
statements. In those stored procedures, we need to do code change for LastModifiedDate.
What would be the best solution from the above approches?
Pls let us know if any other approach to do. Note: We are using SQL Server 2008 R2
asked Sep 13, 2017 at 06:18 AM in Default