Pull delta records


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

more ▼

asked Sep 13, 2017 at 06:18 AM in Default

avatar image

11 1 7

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...

Sep 13, 2017 at 11:15 AM ThomasRushton ♦♦

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

Sep 13, 2017 at 12:01 PM pjkarthikmca

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.

Sep 13, 2017 at 01:40 PM anthony.green
(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 13, 2017 at 06:18 AM

Seen: 49 times

Last Updated: Sep 13, 2017 at 01:40 PM

Copyright 2018 Redgate Software. Privacy Policy