Reporting Options

Hello all. I am currently doing some research into options for

setting up reporting. Right now we have a server on EE that's

getting hit a bit too hard by the reports. The budget is

currently a bit low, but we already have a second server

purchased.

For our reporting, we need data that is up to date within the last 15

minutes (less if possible). Because of the potential size of some

of our transactions, I've ruled out log shipping as being too much

downtime of the reporting data while the second server is catching up

to speed. So, I'm trying to figure out what reporting options I

have left open to me.

1) I understand that for reporting purposes, a snapshot must be taken

of the mirrored server. Why are reports not able to run directly

off the mirror live (or am I mistaken?).

2) Is it possible to mirror from EE to SE (remember, low budget for the second server)?

3) How high is the overhead when doing a snapshot every 5-15 minutes (

I would think it's machine specific, but overall is it pretty quick or

prohibitive based on how often the snapshot would be needed)?

4) Is replication perhaps a better option based on how up to date the

data has to be? Are there any other options that may be available

for near-realtime reporting?

Thanks in advance for your time.

1) Mirroring occurs at a physical level so the pages are not necessarily transactionally consistent for scans to occur. Creating a snapshot runs recovery and brings the database to a consistent state.

2) Yes, but you cannot create snapshots on SE.

3) Not very high in most cases. You can make it even less by doing a CHECKPOINT on the principal shortly before creating the snapshot on the mirror as the snapshot will have to recover from the last checkpoint. There are some DDL and long transaction cases that can make snapshot creation somewhat more burdensome.

4) Replication combined with snapshot isolation level scans to do the reads on the reporting server seems like it might also be viable.

|||In general, consider Replication as a Reporting solution and DBM as a High Availability solution. Transactional replication should satisfy all of your requirements.