better disk config for staging & tempdb files...

Hi,
I want to have your feedback on how to configure my drives to insure a good
performance during loading process & transformation against a staging
database.
Image you have 4 drives available for the staging database & tempdb
database...
loosing these disk is not important, so no redundancy required.
I read from an external database into the staging, I do some updates and
copy into the staging himself, and finally I read the staging to load the
datawarehouse.
I execute more then 70 DTS packages, the sequence of these packages is
optimized. So I could write a fact table in the staging while I read another
to fill the datawarehouse at the same time.
I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
There is "only" 4Gb to 5Gb used by the staging database during the loading
process.
how to configure these disks?
Does it better to setup all disks in raid 0? (the system do everything)
Does it better to keep the 4 disks separatly, and create multiple files &
file groups and dedicating 1 disk by fact table?
Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
Server manage the sharing usage of the disks)
Where to put log files?
thanks for your feedback.
Jerome.
Jerome,
What's the cost to the business if your DW is down for several days? Having
nonredundant disk for tempdb is generally unacceptable. Having nonredundant
disk for staging is a bit unusual. Push back to get more disk.
If I were placed in this situation and could not get more disk, I'd mirror
and strip with the controller and say that's all the performance she's got.
If management and the business owners are ready to signoff on the potential
of several days down time. The amount of down time depends on outside
hardware availability and support staff availability. Put your logs for
tempdb and staging on one disk. Use the disk controller to strip the other
three and place one file per processor (create all the same size) within the
file group. Normally I would break it out more but four disks isn't enough
to do much with.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I want to have your feedback on how to configure my drives to insure a
> good performance during loading process & transformation against a staging
> database.
> Image you have 4 drives available for the staging database & tempdb
> database...
> loosing these disk is not important, so no redundancy required.
> I read from an external database into the staging, I do some updates and
> copy into the staging himself, and finally I read the staging to load the
> datawarehouse.
> I execute more then 70 DTS packages, the sequence of these packages is
> optimized. So I could write a fact table in the staging while I read
> another to fill the datawarehouse at the same time.
> I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
> There is "only" 4Gb to 5Gb used by the staging database during the loading
> process.
> how to configure these disks?
> Does it better to setup all disks in raid 0? (the system do everything)
> Does it better to keep the 4 disks separatly, and create multiple files &
> file groups and dedicating 1 disk by fact table?
> Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
> Server manage the sharing usage of the disks)
> Where to put log files?
> thanks for your feedback.
> Jerome.
>
|||the cost is 0 if we can't load data.
a downtime of 4hour to change a disk if we don't have one in the office is
not an issue.
I don't understand why having the tempdb database on a non-fault taulerant
disk is not a good idea from your point of view!!!
tempdb is the only database recreated when er restart SQL server, so the
downtime in case of a crash is ... 5 minutes?
Thanks for the guide about 1 disk by processor.
I'll plan this config.
"Danny" <someone@.nowhere.com> wrote in message
news:1oRwe.14848$Xr6.6230@.trnddc07...
> Jerome,
> What's the cost to the business if your DW is down for several days?
> Having nonredundant disk for tempdb is generally unacceptable. Having
> nonredundant disk for staging is a bit unusual. Push back to get more
> disk.
> If I were placed in this situation and could not get more disk, I'd mirror
> and strip with the controller and say that's all the performance she's
> got.
> If management and the business owners are ready to signoff on the
> potential of several days down time. The amount of down time depends on
> outside hardware availability and support staff availability. Put your
> logs for tempdb and staging on one disk. Use the disk controller to strip
> the other three and place one file per processor (create all the same
> size) within the file group. Normally I would break it out more but four
> disks isn't enough to do much with.
> "Jj" <willgart@.BBBhotmailAAA.com> wrote in message
> news:eT0XnuEfFHA.3692@.TK2MSFTNGP09.phx.gbl...
>