What are general pros & cons of various redundancy options?

[jsteph]

Hi all,
I'm wondering if anyone can give me a fairly high-level breakdown of the pros/cons of some of the more common redundancy & failover options for sql server. I have a situation where we are going from an array of multiple servers--each with different databases on them--where if one failed or was down for a while, only a part of the business suffered.

The decision was made to go with a single, very powerful server with one instance of sql server and all databases on this single machine. So now if it goes down, we are dead in the water, full panic. There will be a DR failover server across the country but due to the sheer amount of data and the relatively small pipe to that site, we are looking at log shipping hourly. Also, the purpose of that server is more for a physical disaster--I'm wondering what would be some options that people typically plan to recover from a server failure, on a quicker timeframe, and what would the pros and cons of the various options be?
Thanks,
--Jim

 

[SQLScholar]

if you havent already - take a look here.

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

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

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLBill]

There are many options for disaster recovery setups, but most require more than one server - replication and clustered servers are just the two most common.

For a one server environment, you only have one option that I can see. Backups. You will have to determine how much data the business can afford to lose and then plan backups based on that.

For example, if you can afford to lose a day's worth of data all you need to do is a full backup once a night. If you can only afford to lose an hour's worth of data, you would need to combine full backups with hourly transaction log backups.

The backups would have be saved 'off server'. Either to another server or tapes. We backup to to our local drive and then copy the backups to tape. The next backup overwrites the existing backup on the local drive. So on the server we always have the most recent backup. On tape, we always have the most recent backup and previous backups.

For restoring, if the backup on the local drive is accessible, we restore from there as it is quicker. If not, we restore from tape.

(For our most important servers, we are using the above backup method but also use clustered servers or replication for 24/7 availability).

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875