Timeline for Looking for advice on how to integrate data from 100+ client DB's into a centralized reporting database
Current License: CC BY-SA 3.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 1, 2017 at 22:17 | answer | added | Paul Holmes | timeline score: 1 | |
| Jul 31, 2017 at 7:16 | answer | added | Gulrez Khan | timeline score: 1 | |
| Jun 29, 2017 at 23:45 | history | tweeted | twitter.com/StackDBAs/status/880572992664875009 | ||
| Jun 29, 2017 at 23:26 | comment | added | Michael Green | Yes columnstore but also in-memory, if your server spec allows you to go there. I heard Sunil Agarwal talk on this recently. MS's rule-of-thumb was about 3% degradation of OLTP for the benefit of zero latency reporting. Sadly there are no free lunches; you can create new instances to hold the reporting DB or you can create new instance to gain enough headroom to support HTAP. I'm not advocating for this pattern. It may not work for you. Just wanted to make you are aware it existed. | |
| Jun 29, 2017 at 15:50 | comment | added | bperry | Thank you for the suggestion, @MichaelGreen. Basically it looks like HTAP would allow us to use our existing databases for both OLTP and OLAP by adding non-clustered columnstore indexes (NCCI) to our tables. Report queries use the NCCI so they don't interfere with transactional operations. SQL 2016 includes HTAP support in Standard Edition (SE) but it looks like columnstore cache is limited to 32GB across the entire SQL instance. This could be an issue for us since we have dozens of databases on the same instance. microsoft.com/en-us/sql-server/sql-server-2016-editions | |
| Jun 29, 2017 at 15:41 | comment | added | bperry | @Grimaldi, would you recommend using SQL service broker to implement the event-based processing/message queues or some other messaging technology? | |
| Jun 29, 2017 at 14:35 | answer | added | David Browne - Microsoft | timeline score: 1 | |
| Jun 29, 2017 at 13:28 | comment | added | Michael Green | I'd throw HTAP into the mix. en.m.wikipedia.org/wiki/Hybrid_Transactional/… BIML and SSIS for populating the common store. | |
| Jun 28, 2017 at 21:40 | comment | added | Grimaldi | Due to your (near) real-time requirement, I would only look at event based processing with some message queue implementations (for delivery guarantees). Hope this helps | |
| Jun 28, 2017 at 21:21 | review | First posts | |||
| Jun 28, 2017 at 22:05 | |||||
| Jun 28, 2017 at 21:20 | history | asked | bperry | CC BY-SA 3.0 |