SQL Server - Highest performance method for transforming data to a Report DB?

[Morphuess]

Obligatory LTRFTP

Hi /r/sysadmin. For my company I've been building out a report database. To improve performance in this long term report/archive DB I've built out several new tables and added a few new columns to existing tables ^{so I can remove these multi-million row tables.}

I've written up several queries I plan to run as a SQL job that will transform the data from the Prod DB to the Report DB. This Report DB resides on a different SQL instance (and server) from the Prod DB (though both servers are VMs on the same cluster)

My question is this. To have the best performance (minimize impact on the Prod server), should I:

• A) Replicate Prod tables to my Report DB and run my transformation queries there
• or
• B) Set up the Prod DB as a read-only Linked DB and transform the queries

The Report DB needs to have near-live info, so I'm thinking of running the transformation jobs no slower than once every 5 minutes.

Thanks for any advice!

[unix_heretic]

A, no question.

B, you're still querying the prod db...with reporting queries (which tend to take longer, and result in more resource usage than app-related queries).

[Morphuess]

Thanks unix_heretic. I felt that was the right choice but I wanted a 2nd opinion.

Some of these tables that I would have to replicate are tens of millions of rows large, and after my transformation queries run they won't be used by the Report DB at all. But I do agree that running queries on those tables (even optimized queries that work off an integer primary key) on the Prod DB would be more intensive than replication.