Snapshot reporting

Question

Snapshot reporting

cbozdag

Valued Member

Points: 71
More actions
June 15, 2009 at 4:33 am

#138607

i have 2 sql 2005 server, SQL a and SQL b , SQL a is the production server and sql b is the mirror server. i want use the db on mirror server for reporting. I create one snapshot on the mirror sql and its replace every 5 min .
DROP DATABASE XXX and
CREATE DATABASE XXX_SNP ON
( NAME = ElSystemNet, FILENAME = 'C:\MESSQLDB\XXX_SNP.ss' )
AS SNAPSHOT OF XXX;
GO
its schedule every 5 min.
one user run your report on XXX db, some times if the user run the repor its say " login failed " because the snapshot drop the old XXX db.
how can i use snapshot db as report db that refresh every 5 min ?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

My first question is, does the snapshot really need to be that current? If there are specific reports that require data to be that close to real-time, then perhaps those reports should be run against the production database, not a snapshot off the mirror database. Doing reporting that way makes sense, particularly if the most, or nearly most, current data isn't a requirement.

If you have to drop and recreate the snapshot every 5 minutes, then users may have to deal with the occasional failed login while the snapshot is recreated.

GURSETHI SSCommitted Points: 1912 More actions · Answer 2

Hi,

I agree with Lynn if we need Point in time data then this methedology is not good. Either you can run your reports with Production server or I must say you can use Transactional Replication and Publish tables which are used for reporting or you can use Log Shiping where Seconday Server is in Ready only mode (but in that as well while applying the TLogs it will kick out existing connections).

So the curx is

Either divers reports to Production Server

or

Use Transactional Replication.

Regards

GURSETHI

cbozdag Valued Member Points: 71 More actions · Answer 3

Lynn Pettis (6/15/2009)
My first question is, does the snapshot really need to be that current? If there are specific reports that require data to be that close to real-time, then perhaps those reports should be run against the production database, not a snapshot off the mirror database. Doing reporting that way makes sense, particularly if the most, or nearly most, current data isn't a requirement.
If you have to drop and recreate the snapshot every 5 minutes, then users may have to deal with the occasional failed login while the snapshot is recreated.

thx for answer,

i cant user the production server for reporting because i have performance problem on the product server. maybe is my methodology is worng.

Better question, how can i use mirror db just for reporting ?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

The question that needs to be answered first is how current MUST the data be for reporting? If it must be as current as possible, then this is not the solution you need, and perhaps you do need to look at transactional replication to a secondary server for reporting.

cbozdag Valued Member Points: 71 More actions · Answer 5

if i use transactional replication , is the db online on the sec server ?

manishankar_83 Old Hand Points: 391 More actions · Answer 6

Hi,

Yes the secondary server will be online and you can use that as on OLAP server which can be used for reporting purpose. The changes from primary to Secondary are tracked under 2PC (2 phase commit)

Under Tran Repl there are 2 variants

Based on the sort of autonomy that you want to give to your secondary server, you can have

1.Transaction replication

2. Transaction replication with updatable subscription

For the first one you can update data on the secondary but those changes will not affect the Primary

With the second option the changes on the secondary server will update the Primary Server as well

Thanks,

Mani

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

You still have not answered the main question, how current does the data need to be for reporting purposes? Do the users really need up to the minute reporting from the reporting database?

Please stop and answer these questions, if not for us for yourself, and then go forward with developing a solution to meet the business requirements.

cbozdag Valued Member Points: 71 More actions · Answer 8

the db must be refresh every 5 min with the production db