Using Mirror database for reporting

Question

Using Mirror database for reporting

allen davidson

SSCertifiable

Points: 7042
More actions
November 23, 2010 at 4:47 am

#231801

Hi all,
I am helping with a small finance system (6 OLTP users, 4 reporting users, 10 GB DB) where the client has offloaded the reporting from the OLTP (SQL 2005) box to a reporing server (SQL2008 r2). So far they have done this using backup & restore.
I read a great artice here http://www.sqlservercentral.com/articles/Reporting+System/61688/ about using Mirroring to move data off to a seperate reporting box. BOL also talks about using mirroring to provide a reporting DB. I think I understand that the mirror will not accept connections because it is alwas in a state of Restoring, however snapshots of the Mirrror can be read.
My question relates to the strategy behind this. My plan (once the mirroring is running) is something like this
begin trans.
check for any connections to the snapshot (using a dmv e.g.sys.dm_exec_connections)
if none found
beigin
set RESTRICTED_USER (or should this be done sooner)
drop snapshot
recreate snapshot
end
commit trans
I would run this periodically throughout the day.
I would welcome any comments.
Cheers
Allen

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Another approach.

Create a database at reporting side. create views from all snapshot's tables in that database. and use that DB in your Application(reports) so there is no requirement of USER RESTRICTION.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

The problem with either solution is that you can break the client side easily.

I'd recommend that you use a set schedule so that people are aware when the db will go down. I assume you have Enterprise Edition, which you need for snapshots. The downside of snapshots is that you lose the ability to "go back" and verify a report once you've dropped the old snapshot. Can make reconciliation harder with reports.

You could also replicate information to another database instead of using mirroring. It can be more admin to setup/monitor, but it doesn't interrupt reporting. However it also means that you could have reports that don't match up from minute to minute.

You could also use SSIS to move data, if you can track changes and don't have a ton of tables.

allen davidson SSCertifiable Points: 7042 More actions · Answer 3

Hi Thanks - good idea about a set schedule. It will probably be on the hour (the client will of course want it more often).

I was going with mirroring because it seems a lot easier to set up. MS seem to suggest that mirroring & snapshots offer the plumbing for off loading reporting from a OLTP system to a reporting server but they chuck in the fact that client connections have to be managed programmatically without saying how to accomplish it. Hence my stab at using SINGLE_USER.

When you say break the client side. I guess you mean that whilst I'm dropping and recreating the snapshot the reporting DB would be unavailable however it should be a very quick process.