How to setup only a single table in db read only mirror/replication for reporting?

Question

I have a db that has a very large raw record table that is added to constantly. Every few hours we run an aggregation job and writes the roll up to what I will call an aggregation table. The end users never really touch the raw records table. I want to setup a read only secondary copy of the aggregation table only on a reporting server to let users query that. My thought is this offloads reporting and prevents reporting from slowing down data ingestion and aggregation on the source system. Both servers are VMs in the same data center, no geo redundant mirroring, etc. necessary. I just want to offload reporting activity on the aggregation table only. My source system server is SQL 2014 enterprise. I am hoping I can go with something as simple as SQL 2019 standard edition on the reporting server. Any suggestion for best solution to use for my scenario? Again I don't want to replicate the whole db.

Answer 1

Sounds like transactional replication to me.

Answer 2

You could use a solution like transactional replication which has a high overhead and can have a steep learning curve. Depending on your needs and scope I would use an SSIS ETL Job or a simple powershell script to copy the table to the new server if has to be on one. Another option would be to separate the the base data from the aggregation table by schema and and allow read only access to the reporting users to the new schema this would remove the need for a new server.

Answer 3

I agree with @tibor_karaszi@hotmail.com . The transactional replication is the solution. Since your source (Publisher) server is SQL 2014, you may not be able to use SQL 2019 as your Subscriber. Take a look at "Replication Backward Compatibility" for the details.

Answer 4

Since this is only needed every couple of hours - SSIS would be an ideal solution. In fact, you could have the SSIS process read from the raw data and generate the aggregation table on the new system instead of generating it on your production system.

If you need that table on both systems - then use a job on the primary system to build the aggregation table and another step in that job to kick of the SSIS job on the other system.

Answer 5

Hi @JasonW-5564 ,

As others mentioned, you can use transactional replication.
In transactional replication, SQL Server will deliver the data changes and schema modifications made on the publisher to the subscribers in almost real time. By default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher.
But as Guoxiong mentioned, it is not supported to use SQL Server 2019 as subscriber. You can try to use SQL Server 2017 instead as subscriber.
In addition, please refer to this thread which might help.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.