table/view/indexed view strategy in SQL Server

Question

We have a "reporting database" (SQL Server 2008) to performing simplified reporting off of an application database and a few other disparate database sources. We started small, so we used simple views to pull the data together in a way that novice SQL writers could make sense of it. We knew this would only work while the amount of data was small...

... well, it's growing quickly and we need to determine a better way. Most of the data in the past does not change, but we need real-time reports. Conceptually, I'd like to do something like load these views into tables every night and then combine that data with a view of today's data - perhaps with a new view of a union between the table and a view of today?

I'm relatively new to SQL Server so I'm not sure of the gotchas or the considerations I need to have in this design. Any input or suggestions are welcome!

Thanks much!

Answer 1

If the schema of the application is identical with the schema for reporting then you can use:

• database mirroring with snapshots
• log shipping
• AlwaysON Readable Secondaries (requires SQL 11)
• SAN replication (I just mention it, it costs an arm and a leg in hardware)

If your schema in the reporting server can have some divergences from the application schema (eg. more indexes) then you can consider transactional replication.

If your schema differs significantly (like an OLAP reporting style schema) then the way to go is a proper ETL process. You can achieve near-real-time ETL.

Answer 2

The concept of a reporting database and nightly updates suggests that an OLAP-style system may work as a solution for you. In the SQL Server world this technology is called SSAS (SQL Server Analysis Services.)