asked
Can Transactional Replication summarize activity?
Non-DBA here, I come to you for advice and guidance. We are using SQL 2014. We have a high use tx table that gets many operations (non CRUD) however for reporting purposes we need to maintain a running total as if records were CRUD'd. For example create 1 Apple, add 1 Apple, remove 1 Apple, the table has 3 records, total Apples is 1. In the past we scheduled a background process to maintain summarization table on a monthly basis. So for example if on the 15th a user wanted the total to date we would grab all tx from the 1st to the 15th and add them to the total from the summarization table. This way we would only be adding 1000's of rows not 100,000's or more. But better would be to have the summarization table even more current than monthly and we cannot guarantee that a report won't be requested while the bk process is running. Is this a job for transactional replication? Could we set up tx replication to perform the math and update a table? Does the target table have to reside in a separate instance of SQL or can it reside locally with minimal impact on performance? Any suggestions or pointers are appreciated.
You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
Transactional replication, or replication at all for that matter, is not a good solution for a running total. To perform the running totals, the recommendation would be to continue to run the background process to maintain that summary table. With SQL 2014, the windowing functions have been greatly tuned and can really help make that running total query significantly faster.
Transactional replication could be a great tool for getting you real-time data from an production OLTP system to another dedicated ODS system where you could then run your aggregated queries on an instance that wouldn't impact performance across your production OLTP database. It by itself wouldn't help you with building rollups but it could be used to offload these and reporting queries from your OLTP instance.
5 People are following this question.
