Some suggestions on the following backup methods

HI there I would like to ask the experts about the following:

We have a SQL 2005 server, running a Dynamics Axapta database. Understandibly uptime and continuity is crucial. The databse is roughly about 300GB.

I need to do the following:
1: Implement a high availability sollution for the database. For this I decided to do log shipping as we have had some problems in the past with mirroring. I am hoping to test this every week lets say on Fridays. 2: This same database for this scenario sake I will call it the "Live" database, needs to be backed up every night onto removable storage. I also need to have the significant transaction logs just incase we missed something on the transaction logs. 3: Again this same database will be used as a reports database (crystal reports) so a daily update will be required to keep this up to date.

Any suggestions will be much appreciated.

asked Jan 03 '10 at 07:14 AM in Default

Charnelle
21 ● 1 ● 1 ● 1

(comments are locked)

3 answers:

As far as I know, Log shipping and reporting does not go well together. One will be blocking the other from doing its job.

Also consider doing a proper Data Warehouse for your reporting requirements. It's more work, but your reports will perform. I can recommed reading Ralph Kimball; The Data Warehouse toolkit.

answered Jan 08 '10 at 11:28 AM

Henrik Staun Poulsen
549 ● 13 ● 14 ● 15

(comments are locked)

A few things of note...

"I also need to have the significant transaction logs just incase we missed something on the transaction logs."

This means you need ALL the transaction log backups.

If you're using your DR box for reporting, it'll need a licence. Also, decide how frequently you want to be restoring the transaction logs onto that machine, if you only want your reports updated daily.

Don't backup your database directly to removable storage. Back it up to a disk somewhere, and then copy the backup to removable storage. If you need it, you don't want to have to locate it first.

But absolutely, read whitepapers on High Availability. Go check out Paul Randal's material for lots more information - he's generally the guy on that topic.

answered Jan 03 '10 at 09:46 PM

Rob Farley
5.7k ● 13 ● 17 ● 19

(comments are locked)

I am not entirely sure what the question is, but here are a few thoughts I have on your goals.

There are many, many articles written on different high availability solutions. This is some discussion here http://ask.sqlservercentral.com/questions/367/pros-and-cons-of-ha-solutions amoungst other places. If you are willing to have a manual fail over I tend to use replication instead of log shopping as some of the side uses can be nice, but they both have pros and cons.
This is pretty standard, and can be accomplished in many many ways. You can for instance do it with a database maintenance plan. Personally, I like Red Gate SQL Backup. It costs a little bit of money but if you are doing sophisticated management of many databases then it is well worth every penny.
Depending on your hardware, 300GB is often not considered a VLDB, but on slower hardware you may get backup failures with larger databases especially if the server is also processing other things at the same time or a full backup may simply take unacceptably long. If that is the case, you can break the database up into file groups and then do file backups instead of full backups.
How you do the daily update depends heavily on your situation. If you are importing from other files or other databases, you can often fully automate this so it just happens without human intervention. If the data is coming from more of a live source, you may need to create a custom application for the data entry.