which High Avaiblity Option is better?

Question

which High Avaiblity Option is better?

SQlLogic

SSCrazy Eights

Points: 9686
More actions
November 23, 2011 at 7:04 am

#251003

Hi folks,
I have to keep report server real time updated with prodcution server databse . would like to know the suggestions which HA option is better for that?
Currently we refreshing once in week every sunday but my manager weants to keep it updated evry night.
Version :SQL Server 2008 R2
DB Size in TB
Any Ideas please?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

The information you provided is not sufficient. Please go through the article given below for better understanding on HA solutions.

Selecting a High Availability Solution

http://msdn.microsoft.com/en-us/library/bb510414.aspx

george sibbald SSC Guru Points: 104210 More actions · Answer 2

specifically whats the size of the database, whats the network like and what edition of SQL are you using.

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

logicinside22 (11/23/2011)
Hi folks,
I have to keep report server real time updated with prodcution server databse . would like to know the suggestions which HA option is better for that?
Currently we refreshing once in week every sunday but my manager weants to keep it updated evry night.
Version :SQL Server 2008 R2
DB Size in TB
Any Ideas please?
Thanks

If both your servers are on a common SAN, SAN "snapshots" can update a tera-byte database in just a minute or so.... even across instances.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

if your production server can take the load of production OLTP traffic and reporting traffic, you could look at database snapshots

otherwise I would say your looking at either logshipping or replication, mirroring would do the trick as well but as you can only mirror to one node, you may want to keep this for disaster recovery purposes and not reporting.

can get logshipping to go into standby mode and disconnect users so it can restore the TX log at regular intervals, could set it up so that it backups prod every 15 minutes and copies to report server every 15, but only restore every 24 hours at a quiet period, so its a day behind, will keep your prod TX log manageable.

issues on this is that if you do restore the logs every 15 minutes, users will be kicked out every 15 minutes so might get frustrating if reports take longer than that to run.

or put checksums on the end of all tables and have it computed, then use SSIS to lookup the checksum in reports and the checksum in prod, if they match skip over, if different update, if not exist insert, some conditional logic in the data flow tasks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

logicinside22 (11/23/2011)
Hi folks,
I have to keep report server real time updated with prodcution server databse . would like to know the suggestions which HA option is better for that?

That's not a high availability problem, that's a scale-out reporting problem. HA is a warm or hot standby server to be used if and when the primary server fails. That's not what you're describing here.

For scale-out reporting, my preference would be transactional replication.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 6

please when giving extra info that has been requested do it by adding another post rather than editing your original post. People only get notified you have replied when you add a post.

It also stops it from looking like I have asked stupid questions.

---------------------------------------------------------------------

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 7

GilaMonster (11/24/2011)
logicinside22 (11/23/2011)
Hi folks,
I have to keep report server real time updated with prodcution server databse . would like to know the suggestions which HA option is better for that?
That's not a high availability problem, that's a scale-out reporting problem. HA is a warm or hot standby server to be used if and when the primary server fails. That's not what you're describing here.
For scale-out reporting, my preference would be transactional replication.

Have to agree since the original post pointed out real time information as a requirement.

SAN replication, Mirroring with snapshots are all well and good but as soon as the data materialises it begins to go stale.

jamessdba SSCrazy Points: 2256 More actions · Answer 8

Hi ,

Please go thorugh the below link

http://msdn.microsoft.com/en-us/library/ms190202.aspx

Divine Flame SSCoach Points: 15943 More actions · Answer 9

Go with Transactional Replication if you want your report server updated in real time.

However , as you said that your manager wants the report server to be updated every night you can look for log shipping also.

Sujeet Singh

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Divine Flame (12/6/2011)
However , as you said that your manager wants the report server to be updated every night you can look for log shipping also.

Not a bad idea but SAN replication methods will be much easier and faster for such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)