asked
Pull delta records
Hi, 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 Regards Karthik
Change Tracking or Change Data Capture would be my approach to save the additional coding for last modified date. If they don't work for you then yes you will need to look at adding something on the row which details when it was last changed. Replication or mirroring wont give you what you want.
SQL Server 2008 R2 rules out the Always On Availability Group option, as they weren't introduced until SQL Server 2012... ...of course, given SQL2008R2 is out of mainstream support and only has two years of extended support to run, you could use this as a driver to upgrade...
Thanks for the Reply Thomas. Since we are not in the position to upgrade the version and also our DBA is forcing us to proceed the 4th approach (LastModifiedDate). He says if we go for Replication approach, it will have huge production impact and leads I/O problem. What would be the best solution? So here we cannot do Always On. Next is Replication or Mirroring or LastModifiedDate column??? Regards karthik
18 People are following this question.
