1

My current system, I'll call it B, gets data from an old legacy SQL database, A, via replication. I do not have any control over this legacy system other than access to the database. Replication was initially chosen to keep the two systems separate as well as to reduce any chances of performance hits on the legacy system, A, from my system, B. In B, there is a task that runs every 15 minutes that loads data from the replicated source tables and transforms it into the format I need and saves it to a SQL database that I own. This task is currently very slow as it loads up all possible data and checks for changes before it decides if any updates need to be made.

I'm creating a new system, C, that also needs to use the data from A as well as interact with B. The data is needed in a completely different format for C, so I am not able to reuse much of B, so speeding up B is not an option. Ideally, I'd like to switch B over to whatever solution chosen for C, but in the meantime, whatever is picked needs to play well with replication.

I am researching new options to get data from A as well as some way to get change notifications. Ideally, I'd love for A to send messages when there are changes but this is not possible due to the fragility of A.

I've looked into SQL Query Notifications, specifically SQLDependency and SQLTableDependency. I need to be able to see what data has changed, so SQLTableDependency might be better but its only listening when the application is running so nothing is listening when it stops. I'd like to be able to cache data instead of stitching it into the format I need when I load my website.

I've also looked into Change Tracking and Change Data Capture. Both seem like they could work in my current set up but they both seem heavier than what I need. I am also concerned about both of these running with replication. For example, if replication is re-initializing, all data is truncated and it looks like I have a ton of changes?

Am I going in the right direction given the constraints of my system? Does anyone have any other ideas of some way to get data changes? Is there some other messaging system that can be used with SQL?

Thanks!

bearDog
  • 31
  • 3
  • Gonna need a SQL Server version here. – Jacob H Jul 14 '17 at 20:26
  • Sorry! - I'm using SQL Server 2014 – bearDog Jul 14 '17 at 20:30
  • Have you ever thought of warehousing all your data into one central location from all sources and then pulling what you only need based on the conditions you mentioned above? – Isaiah3015 Jul 14 '17 at 22:54
  • Custom replication SPs: https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-articles-specify-how-changes-are-propagated . You should be able to transform data and write to an event log table about specific data changes. It is a big task though. – Alex Jul 14 '17 at 23:29

0 Answers0