hardware recommendation

Hi,
I've to recommend a new server for a datawarehouse server which contain some
data warehouse databases (5 databases of 5Gb each)
I plan 6gb of memory and some SCSI drives (5 * 18 gb in raid 5 + 2 * 18 gb
in raid 0 for the tempdb)
there is 200 users for the system for the moment (not concurrently)
and the usage will be low:
* OLAP Cubes
* Reports with reporting services
* Sharepoint web site (not portal server)
So, I'll plan to use SQL server2000 standard edition with a by processor
licensing
SQL Server will use 3gb of RAM (standard edition is limited)
Analysis services will use 1gb of RAM
and the rest is for sharepoint and reporting services
But I'm not sure about the processor...
what do you think about this?
I know that putting all in 1 server is not good, but we have not the budget
to get 2 SQL Server licenses.
Does another licensing mode can be used in my case?
Maybe I can put sharepoint on a Win 2003 Web edition, and all the other
components on a second biggest server?
what are your recommendations?
thanks.
Jerome.I would like to give more memory to AS, you may run out of memeory while
processing those cubes.
Based on my experience, disk is always the bottle neck, especially if
you have a big cube and somebody runs a query which misses all the
aggregates, the performance will be horrible. You need to fine tune the
aggregates, also you may want to stree test your environment before
putting it into production.
Eric Li
SQL DBA
MCDBA
Jj wrote:
> Hi,
> I've to recommend a new server for a datawarehouse server which contain so
me
> data warehouse databases (5 databases of 5Gb each)
> I plan 6gb of memory and some SCSI drives (5 * 18 gb in raid 5 + 2 * 18 gb
> in raid 0 for the tempdb)
> there is 200 users for the system for the moment (not concurrently)
> and the usage will be low:
> * OLAP Cubes
> * Reports with reporting services
> * Sharepoint web site (not portal server)
> So, I'll plan to use SQL server2000 standard edition with a by processor
> licensing
> SQL Server will use 3gb of RAM (standard edition is limited)
> Analysis services will use 1gb of RAM
> and the rest is for sharepoint and reporting services
> But I'm not sure about the processor...
> what do you think about this?
> I know that putting all in 1 server is not good, but we have not the budge
t
> to get 2 SQL Server licenses.
> Does another licensing mode can be used in my case?
> Maybe I can put sharepoint on a Win 2003 Web edition, and all the other
> components on a second biggest server?
> what are your recommendations?
> thanks.
> Jerome.
>|||well, finally we have an option to reuse an existing server.
With 8 * 32gb SCSI drives, 4gb of memory and 2 proc.
And we'll have 6 small datawarehouses!
I'll reformat this server and reorganize the disks.
I'll add 4gb of memory
We allready have SQL Server 2000 enterprise on this server!
What are your idea about the disk organization?
what do you think about this :
6 disks in RAID 5 (for OS, databases, olap cube) + 2 in RAID 0 (strip) (for
tempdb and windows swap file)
or the 8 disks in Raid 0+1 (strip + mirror) ?
does the AWE option is recommended in this configuration?
because this server will support staging area (6) and datawarehouses (6) =
at least 12 databases
Does it better to create 2 instance of SQL Server? (1 for staging area
database, second for datawarehouse database) and starting the staging
instance only when needed
Does this configuration is better than all in 1 instance?
thanks for your ideas.
"Eric.Li" <anonymous@.microsoftnews.org> a crit dans le message de
news:OD$WSzkSEHA.3756@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I would like to give more memory to AS, you may run out of memeory while
> processing those cubes.
> Based on my experience, disk is always the bottle neck, especially if
> you have a big cube and somebody runs a query which misses all the
> aggregates, the performance will be horrible. You need to fine tune the
> aggregates, also you may want to stree test your environment before
> putting it into production.
> --
> Eric Li
> SQL DBA
> MCDBA
>
> Jj wrote:
some[vbcol=seagreen]
gb[vbcol=seagreen]
budget[vbcol=seagreen]|||It all depends on how big is your DW and staging DB. I would like to
seperate OLTP and OLAP DB because re-processing cubes is very disk/CPU
intensive. You should enable AWE to take advantage of extra RAM. If you
create enough aggregates, I think your current disk config. is OK. If
possible, I would like to dedicate some disks exclusively for AS. You
can always rebuild those cubes if neccessary, but again, depends on your
bussiness requirement, you may not able to do this.
Depends on the size of your cubes, you may run into some performance
problem at the beginning, you have to keep fine tuning your aggreates.
Performancewise, I don't think 2 instance will help you much.
Eric Li
SQL DBA
MCDBA
Jj wrote:

> well, finally we have an option to reuse an existing server.
> With 8 * 32gb SCSI drives, 4gb of memory and 2 proc.
> And we'll have 6 small datawarehouses!
> I'll reformat this server and reorganize the disks.
> I'll add 4gb of memory
> We allready have SQL Server 2000 enterprise on this server!
> What are your idea about the disk organization?
> what do you think about this :
> 6 disks in RAID 5 (for OS, databases, olap cube) + 2 in RAID 0 (strip) (fo
r
> tempdb and windows swap file)
> or the 8 disks in Raid 0+1 (strip + mirror) ?
> does the AWE option is recommended in this configuration?
> because this server will support staging area (6) and datawarehouses (6) =
> at least 12 databases
> Does it better to create 2 instance of SQL Server? (1 for staging area
> database, second for datawarehouse database) and starting the staging
> instance only when needed
> Does this configuration is better than all in 1 instance?
> thanks for your ideas.
> "Eric.Li" <anonymous@.microsoftnews.org> a crit dans le message de
> news:OD$WSzkSEHA.3756@.TK2MSFTNGP11.phx.gbl...
>
> some
>
> gb
>
> budget
>
>
>|||What do you think if I dedicate 1 disk for the cubes ?
So...
5 disks in RAID 5 for my databases
2 in Raid 0 for tempdb
1 disk for the cubes
I'll have a lot of Reporting Services reports with some SQL access.
My OLAP cubes are less used with an estimate of 2 to 4 gb of cubes. (25gb of
atawarehouses)
"Eric.Li" <anonymous@.microsoftnews.org> a crit dans le message de
news:OYhh53JTEHA.3988@.TK2MSFTNGP10.phx.gbl...
> It all depends on how big is your DW and staging DB. I would like to
> seperate OLTP and OLAP DB because re-processing cubes is very disk/CPU
> intensive. You should enable AWE to take advantage of extra RAM. If you
> create enough aggregates, I think your current disk config. is OK. If
> possible, I would like to dedicate some disks exclusively for AS. You
> can always rebuild those cubes if neccessary, but again, depends on your
> bussiness requirement, you may not able to do this.
> Depends on the size of your cubes, you may run into some performance
> problem at the beginning, you have to keep fine tuning your aggreates.
> Performancewise, I don't think 2 instance will help you much.
> --
> Eric Li
> SQL DBA
> MCDBA
> Jj wrote:
>
(for[vbcol=seagreen]
=[vbcol=seagreen]
processor[vbcol=seagreen]
>|||I won't do that, 1 disk for all the cubes is a bad idea.
I suppose your users will create ah-hoc reports, so they will create
reports on the fly, most likely they will miss aggregates, if that
happens, AS will read the whole file. It's just like you join some
tables without using the index, you end up with disk scan. In OLTP
world, you would like to put each individual table on differnet disk,
same idea applies to AS OLAP, but of course it's not practicle. So I
would create RAID 0 and put my cubes there, and update aggregates (usage
based) once in a while. Beware that, if you put alot aggregates, it will
take much longer to process those cubes, and during cube processing,
your CPU will probably shot close to 80%~100%, in other words, your box
is pretty much unaccessable during that time. If your total DW is only
4GB, that's only about 600MB per DW, which is not too bad, assuming you
don't have many concurrent users and they don't do crazy stuff. I used
to manage 50GB cubes and each time a user drill down to the lowest level
and miss the aggregates, it just kill the box.
Eric Li
SQL DBA
MCDBA
Jj wrote:

> What do you think if I dedicate 1 disk for the cubes ?
> So...
> 5 disks in RAID 5 for my databases
> 2 in Raid 0 for tempdb
> 1 disk for the cubes
> I'll have a lot of Reporting Services reports with some SQL access.
> My OLAP cubes are less used with an estimate of 2 to 4 gb of cubes. (25gb
of
> atawarehouses)
>
> "Eric.Li" <anonymous@.microsoftnews.org> a crit dans le message de
> news:OYhh53JTEHA.3988@.TK2MSFTNGP10.phx.gbl...
>
> (for
>
> =
>
> processor
>
>|||ok, last try ;-)
8 Disks available today...
Disk 1 - 2 --> C: --> Raid 1 (mirror) for the OS and local backups prior to
DAT backup (32Gb available)
Disk 3 - 4 --> D: --> Raid 0 (stripped) for the OLAP Cubes (64Gb available)
+ tempdb
Disk 5 - 8 --> E: --> Raid 5 for my databases (96Gb available)
In this case I'm secure (except the cubes, but I can recreate these cubes
through a complete process)
I'm a small performance impact during cube process, but its a weekly
loading. I'm not looking for a perfect process time of my cubes.
Also in this configuration I'm loosing only 2 disks (1 for the mirror, 1 for
the Raid 5)
Later, If we need more power will add some disks to share the workload
regarding the real usage of the server.
"Eric.Li" <anonymous@.microsoftnews.org> a crit dans le message de
news:OV2XE$LTEHA.2972@.TK2MSFTNGP12.phx.gbl...
> I won't do that, 1 disk for all the cubes is a bad idea.
> I suppose your users will create ah-hoc reports, so they will create
> reports on the fly, most likely they will miss aggregates, if that
> happens, AS will read the whole file. It's just like you join some
> tables without using the index, you end up with disk scan. In OLTP
> world, you would like to put each individual table on differnet disk,
> same idea applies to AS OLAP, but of course it's not practicle. So I
> would create RAID 0 and put my cubes there, and update aggregates (usage
> based) once in a while. Beware that, if you put alot aggregates, it will
> take much longer to process those cubes, and during cube processing,
> your CPU will probably shot close to 80%~100%, in other words, your box
> is pretty much unaccessable during that time. If your total DW is only
> 4GB, that's only about 600MB per DW, which is not too bad, assuming you
> don't have many concurrent users and they don't do crazy stuff. I used
> to manage 50GB cubes and each time a user drill down to the lowest level
> and miss the aggregates, it just kill the box.
>
> --
> Eric Li
> SQL DBA
> MCDBA
> Jj wrote:
>
(25gb of[vbcol=seagreen]
(6)[vbcol=seagreen]
while[vbcol=seagreen]
the[vbcol=seagreen]
contain[vbcol=seagreen]
18[vbcol=seagreen]
other[vbcol=seagreen]
>|||This is a very reasonable config. based on the hardware you have. If
it's only a weekly load and your current DW is only about 4 GB, then I
won't worry about reprocessing, unless your fact table is from a very
complicated query, otherwise it shouldn't take too long.
I would schedule a job to archive those cubes after each full process,
it will save your current cube structure and all the aggregate config.
Eric Li
SQL DBA
MCDBA
Jj wrote:

> ok, last try ;-)
> 8 Disks available today...
> Disk 1 - 2 --> C: --> Raid 1 (mirror) for the OS and local backups prior t
o
> DAT backup (32Gb available)
> Disk 3 - 4 --> D: --> Raid 0 (stripped) for the OLAP Cubes (64Gb availabl
e)
> + tempdb
> Disk 5 - 8 --> E: --> Raid 5 for my databases (96Gb available)
> In this case I'm secure (except the cubes, but I can recreate these cubes
> through a complete process)
> I'm a small performance impact during cube process, but its a weekly
> loading. I'm not looking for a perfect process time of my cubes.
> Also in this configuration I'm loosing only 2 disks (1 for the mirror, 1 f
or
> the Raid 5)
> Later, If we need more power will add some disks to share the workload
> regarding the real usage of the server.
> "Eric.Li" <anonymous@.microsoftnews.org> a crit dans le message de
> news:OV2XE$LTEHA.2972@.TK2MSFTNGP12.phx.gbl...
>
> (25gb of
>
> (6)
>
> while
>
> the
>
> contain
>
> 18
>
> other
>
>