zz14hl1 34362 (4/9/2014)
Any and all suggestions are welcome.
75 databases x 1300 tables likely rules out replication. The overhead and management would be pretty ridiculous.
I would like to know of some suggestions to have/create duplicate databases that are read only, not in recovery mode and will update each hour after the log files have been loaded into the main database.
SQL Server 2012 AlwaysOn Availability Groups. You can have a real-time read-only replica that's not in recovery mode and doesn't require you to disconnect during updates (like log shipping or something else would). But you'll have to pay for it :-)
Okay so say this isn't possible? Well you said you already load this stuff into other tables. Maybe you can just speed up whatever ETL you have and use replication on just those tables. Does the ETL take too long or is it too intensive to run hourly? Maybe it's doing a full ETL instead of only trying to process updates like a normal data warehouse, in which case you could look into doing something about that (like using timestamps on rows, unlikely on 75 x 1300 tables unless they are already there, or with Change Data Capture or something which would also be cumbersome to set up on that scale).
Long story short: I reckon they will probably have to pay for closer to real-time figures especially on that scale.