question

Charnelle avatar image
Charnelle asked

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.

sql-server-2005backuphigh-availabilitycopy-databasevldb
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Henrik Staun Poulsen avatar image
Henrik Staun Poulsen answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

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

  1. 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.

  2. 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.

  3. 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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.