Re: Minimal configuration, suggestions wanted.

From: Andrew J. Kelly (sqlmvpnoooospam_at_shadhawk.com)
Date: 04/07/04 

Date: Wed, 7 Apr 2004 08:42:21 -0500

It depends on how you will use the database. If you have a lot of
operations that use tempdb heavily you may wish to place tempdb on it's own
raid as well. Logs should be on their own array unless it is mostly read
only. A typical config for a moderate system is usually something like
this:

RAID 1 OS / SQL Binaries
RAID 1 SQL Transaction Logs
RAID 10 DATA, TempDB 

--
Andrew J. Kelly  SQL MVP
"ben brugman" <ben@niethier.nl> wrote in message
news:c50moh$gtq$1@reader11.wxs.nl...
> Going with your suggestions,
> suppose you have split the total RAID device in 3 groups
> of disks (fysical separate), how would you place
>
> 1 OS
> 2 Logging
> 3 Data
> 4 Backupdata
> 5 Transactionlog backup
>
> Have I missed anything ?
> Or are 2 groups enough ?
> Or do I need more groups ?
>
>
> > on how much data you are willing to loose.  If you can afford to loose
up
> to
> > 15 minutes worth of transactions then use a 15 minute interval etc. Just
>
> If I go from estimates of MTBF of disks the chance of
> losing a mirrored disk is extremely small. So the chance
> of loosing up to 15 minutes of data is extremely small.
> Loosing the complete system because of a major
> dissaster (fire for example) looks more likely and then
> you loose everything since the last time you brought
> the backup to outside of the 'box' or the computerroom.
> Because for a minimal system bringing the data out of the
> room more than once or twice a day would probably be a
> problem.
>
> (Calculation for 2 disks failing, assuming a MTBF of 300000 hours,
> that a failed disk is spotted with in 24 hours. I get a failure of
> two disks every once in 1/(24/300000)^2 days = 156250000 days)
>
> Two of my problems :
>
> 1. Backup of the transaction log is not a 'backup' but a 'move' of the
> 'content',
> so how do I protect the backup of the transaction log, because at that
> moment
> it is the only 'copy' of that data in existence. (Still mirrored I would
> suggest).
>
> 2. If part of the chain of transactionlogs (active of backupped) is lost
the
> datafiles are worthless, so I do not understand that the datafiles
> should be on a separate disk.
> (Performance not being a consideration).
>
> Thanks for your thoughts.
>
> ben brugman
>
>
>
>
>
>
> "Andrew J. Kelly" <sqlmvpnoooospam@shadhawk.com> wrote in message
> news:uRptBADHEHA.2260@TK2MSFTNGP09.phx.gbl...
> > Keep in mind that a server with a 200GB db and moderate load may not be
> the
> > same for a 10GB db with light load and this is addressing the latter as
> you
> > asked.   A RAID 0+1 will give the most fault tolerate Raid with good
> > performance as well.  If the load is light enough (not a lot of
> > transactions) then you can probably get away with a good size (meaning #
> of
> > disks not size of disks) RAID 0+1 or even 1+0 that has everything on it.
> > But it is usually best to place the logs on their own RAID 1 and
separate
> > from the data for performance reasons.  With a minimal configuration a
> good
> > raid is essential along with proper backups.  A full backup once a night
> is
> > usually OK for most applications.  The frequency of the log backups
depend
> > on how much data you are willing to loose.  If you can afford to loose
up
> to
> > 15 minutes worth of transactions then use a 15 minute interval etc. Just
> > ensure the backups are not done to the same drive the logs or data
reside
> > on.
> >
> > --
> > Andrew J. Kelly  SQL MVP
> >
> >
> > "ben brugman" <ben@niethier.nl> wrote in message
> > news:u1zBcW7GEHA.688@tk2msftngp13.phx.gbl...
> > > Minimal configuration.
> > > (Performance is not an issue).
> > >
> > > Dataloss should not happen. (But we do not account
> > > for real dissasters, for example a big fire destroying
> > > the complete production center, resulting
> > > in the dataloss of 24 hours would be acceptable.)
> > >
> > > The shop runs 7 x 24 hours.
> > > Most is done during office hours.
> > > Some is done during all day.
> > > At night the system must be available but it is
> > > used very limited.
> > >
> > > Once a day a tape goes from the production machine to an
> > > offsite safe.
> > > The total amount of data has a disk footprint off
> > > between 1 and 10 Gigabytes.
> > >
> > > Offcourse there is a production machine and
> > > a standby machine probably in another location.
> > > (The second machine will be used for testing and
> > > practicing procedures.)
> > >
> > > Raid is required, raid protects agains single disks failures,
> > > but what are the options for more protection ?
> > > What configuration is considered minimal in these circumstances ?
> > > Esspecially what to do with the disks ?
> > > (OS / Logging / Data / Backupdata / Transactionlog backup) ?
> > > What can be combined and what can not be combined ?
> > > And how often to do the transactionlog backup ?
> > > (Side question what "route" should the transactionlog(-backup)
> > > take).
> > >
> > >
> > > Keep in mind : We are looking for a minimal configuration.
> > >
> > > Later on we will also looking for a 'larger' configuration with
> > > more performance, a larger database etc. (But still no super
> > > configuration. Something like 100-200 Gigabyte and more load).
> > >
> > > Thanks for your thoughts,
> > > ben brugman.
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: [opensuse] 10.2 no RAID to 11.0 RAID 1
    ... IMPOSSIBLE to recover a RAID-1 array that just one day (after properly ... You can even have a third disk as active spare ... Both raid 1 and raid 5 can ... I second that as I have read that RAID is not a backup method. ...
    (SuSE)
  • Re: Back-up media?
    ... Burn DVD: Reliability?? ... RAID HDs: Once set up I won't have to do much ... BUT this is NOT a backup solution. ... I like disk to disk for backups. ...
    (comp.os.linux.security)
  • LaCie Ethernet Disk RAID questions
    ... I am a new user of a LaCie Ethernet RAID system, ... I set it up with the default [RAID 5] configuration since I ... Normally I would not be concerned about a disk failure because ... I have been using the backup utility that is part of the Windows ...
    (comp.arch.storage)
  • Re: Disk Configuration for AD domain controllers
    ... YOu can configure your hardware into 2 RAID 1 arrays. ... need guidance for configuring the disk drives. ... I read that it is recommended to store the AD database and logs on a ...
    (microsoft.public.windows.server.general)
  • Re: disk latencie problems on a brand new exchange server with 2 mailboxes!!!!!!!!
    ... the logs (or a single drive for the OS, a mirror for the logs, and a ... From the description of the problem, odds are all 5 drives are in 1 RAID ... disk, space isn't the only factor; ...
    (microsoft.public.exchange.admin)