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