0

Currently we have two SQL Server instances, one is used for online processing (client tables..) and another instance for reporting purposes.

What we actually do for sync is a SQL Server Job that run on daily basis which extracts the data from the online system, and loads it into the reporting instance. This is done using stored procedures (incremental inserts based on date inserted).

This method has proven to be a bad idea. So we decided to change the way the data is getting loaded.

I've been searching for while now and I've found many solution, one of them is CDC change data capture

Currently CDC has proven that it can be useful (can be used also for fraud detection). But it seems that it has a huge impact on the performance of the tables.

Based on Stackoverflow users and also on articles, it is hard to make a decision, especially when we talk about a high transaction table.

So what is the most effective way to make extraction/loading of data in SQL Server?

Another requirement is that there is another job that run each 15 min to check client status updates which make the use of snapshot replication difficult

Version used is SQL Server 2008

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Yazid
  • 108
  • 3
  • 12
  • You should upgrade to a more recent version of SQL Server. 2008 will no longer be supported in a few months. – Gordon Linoff Feb 03 '19 at 14:11
  • How big are the tables? Since it's for reporting you can also consider snapshot replication. – Greg Feb 03 '19 at 14:14
  • @gordon yes it is on the roadmap that we will upgrade to Sql server 2016 – Yazid Feb 03 '19 at 14:15
  • @greg it is about 20M records per table – Yazid Feb 03 '19 at 14:16
  • Snapshot replication should be fine then. – Greg Feb 03 '19 at 14:17
  • Snapshot replication is not suitable in our case. Since the data is not static and it get updated. – Yazid Feb 03 '19 at 14:22
  • @Yazid, I think Greg is suggesting a daily schedule to refresh from the replication snapshot rather than incremental changes. CDC may be viable with attention to detail and storage that can support the increased log IOPS. See [this answer](https://stackoverflow.com/questions/4364947/is-change-data-capture-performance-loss-restricted-to-cdc-enabled-tables) if you haven't already done so. – Dan Guzman Feb 03 '19 at 14:49
  • @Dan yes correct. But there is some other requirement that i didn't mention that make this solution not suitable such us client status updates and this kind of events. So is those options the only available ? – Yazid Feb 03 '19 at 15:01
  • 1
    If you have other reasons that preclude the use if CDC, you should add that to your question along with other things you've tried. Right now, you question is too broad to answer effectively. Another solution is log shipping. – Dan Guzman Feb 03 '19 at 15:08
  • Thanks @Dan for pointing to this. I've updated the description. – Yazid Feb 03 '19 at 15:19
  • you can look for mirroring your database and use that for reporting, you can do near real time reporting – Siva Feb 13 '19 at 22:05

0 Answers0