Looking for an alternative to Logshipping

Question

Looking for an alternative to Logshipping

Meredith Ryan

Right there with Babe

Points: 749
More actions
March 27, 2009 at 10:04 am

#130229

Hi all,
I need some advice on what direction to take with my logshipping setup.
Some history:
My environment consists of an active\active sql2005 enterprise cluster and a seperate standalone server running sql2005 ent. I have both transactional and snapshot replication running on node1 of the cluster for a small subset of the total tables in my production db (total of almost 700 gb). I am only concerned with Node1 of the cluster for this business issue.
We use Logshipping to create a read-only copy of our production database for reporting purposes. I realize that this is not ideal, but until I get my developers and leadership groups to buy in to creating a true dataware house it's the setup we have. At the very least it's better than reporting being run on my production db. 🙂
Unfortunately Logshipping is a real pain for me, and requires almost daily attention at times. It seems like the littlest thing causes either the copy or the restore to not work as planned and just about the time I am ready to scrap it completely it starts to run smoothly for a period of time.
Anyways, I am looking for alternate solutions. The DB in question is large (almost 700GB), but using litespeed I get a full backup in less than an hour, and my average restore time is just over 2 hrs so I suppose that even running a full backup and a restore on a daily basis would work.
What other options are out there? Would mirroring accomplish what I am looking for? Would you push harder for a real DW?
Help!
Meredith Ryan-Smith

Viewing 15 posts - 1 through 15 (of 18 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Mirroring will not work as the mirror has to be in-recovery meaning that it would not be accesible.

The best alternative is database replication though you still have to do a bit of work when changes are made. For what you describe Transactional replication would work well;

http://msdn.microsoft.com/en-us/library/ms151198.aspx"> http://msdn.microsoft.com/en-us/library/ms151198.aspx

Or you could write some ssis scripts to copy the data that has changed over time.

But the pain-free way would be backup/restore until you have a proper DW setup going

Akeel.Mughal Mr or Mrs. 500 Points: 579 More actions · Answer 2

Mirroring would only create a secondary database that's "in recovery" mode, so you won't be able to access it. I think you should look into replication.

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

Akeel.Mughal Mr or Mrs. 500 Points: 579 More actions · Answer 3

Akeel.Mughal

Mr or Mrs. 500

Points: 579

March 27, 2009 at 10:18 am

#967581

Sorry, as Steveb said!

Meredith Ryan Right there with Babe Points: 749 More actions · Answer 4

I knew there was something about mirroring that would prevent me from using it...

thanks for the advice. I think I am leaning towards the backup/restore plan while I try to light a fire under a DW.:-)

Meredith Ryan Right there with Babe Points: 749 More actions · Answer 5

no worries! I will look into replicating the data and in the mean time I think my best bet is to get an automated restore setup.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

I thought that you could create a database snapshot of a mirror that you could use to run queries.

Meredith Ryan Right there with Babe Points: 749 More actions · Answer 7

Michael Valentine Jones (3/27/2009)
I thought that you could create a database snapshot of a mirror that you could use to run queries.

oh! that might work. I will look into it.

thanks for the thought.

Meredith

Akeel.Mughal Mr or Mrs. 500 Points: 579 More actions · Answer 8

^Yes, I think you can create a snapshot of the mirror, but only if you've got Enterprise edition.

Meredith Ryan Right there with Babe Points: 749 More actions · Answer 9

akeelm_uk (3/27/2009)
^Yes, I think you can create a snapshot of the mirror, but only if you've got Enterprise edition.

I've got constraint covered.

thanks!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Having done both, I cannot imagine that Replicating all of the tables in a database would be less work and less maintenance & overhead than Log Shipping.(!) If you are having that many problems with Log shipping then I would suggest doing a Root Cause Analysis of that first, because replicating many, many tables is definitely moving towards higher overhead and more intervention, not away from it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 11

I agree with Barry. I can't see replication as less work at all.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Meredith Ryan Right there with Babe Points: 749 More actions · Answer 12

RBarryYoung (3/27/2009)
Having done both, I cannot imagine that Replicating all of the tables in a database would be less work and less maintenance & overhead than Log Shipping.(!) If you are having that many problems with Log shipping then I would suggest doing a Root Cause Analysis of that first, because replicating many, many tables is definitely moving towards higher overhead and more intervention, not away from it.

Good point. I've not had many issues with replication, but as I mentioned I am only replicating a handfull of my total possible tables.

As far as root cause analysis of my logshipping issues there seem to be a few. One issue I have is with the shared folder setup that is needed. On a cluster shared folders on cluster resources need to be recreated after every reboot, or failover. If that doesn't happen then logshipping copies fail. And, of course you really want your backup jobs writting to a cluster resource so that they continue to work in a failover situation.

The other issue that I see most often is that the restore job just doesn't restore the files. I don't get a failure, it just skips the trn logs and tells me that a later trn log must be applied. If I use that same set of files and do a manual restore they work fine and logshipping will most likey do it's thing the next night.

sebastien piche SSC Eights! Points: 960 More actions · Answer 13

other solution is use to SAN BOX whith replication like netapps or equalogics solution is verry fast replication depending if you are on lan or Wan replication.

is totaly different aproche and is maybe expensive but is great product for maximum protecttion

http://www.netapp.com/fr/products/protocols/fc-san/

or

http://www.equallogic.com/default.aspx

Meredith Ryan Right there with Babe Points: 749 More actions · Answer 14

sebastien piche (3/27/2009)

other solution is use to SAN BOX whith replication like netapps or equalogics solution is verry fast replication depending if you are on lan or Wan replication.

is totaly different aproche and is maybe expensive but is great product for maximum protecttion

http://www.netapp.com/fr/products/protocols/fc-san/

or

http://www.equallogic.com/default.aspx%5B/quote%5D

I've got a 3par SAN with replication already in place that my cluster runs on currently. I'm not looking for a DR copy, more a copy of the DB that can be used for querying and reporting. I can't wrap my head around how a SAN replicated copy would work for our use.