Re: hardware recommendation
From: Eric.Li (anonymous_at_microsoftnews.org)
Date: 06/08/04
- Next message: Rea Peleg: "dts retrieves 0 rows when scheduled"
- Previous message: SBDTech: "Re: Problem with Olap Report on a Windows 2003"
- In reply to: Jéjé: "Re: hardware recommendation"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 08 Jun 2004 07:32:44 -0500
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 Jéjé wrote: > 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 >> >>Jéjé 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... >>> >>> >>>>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 >>>> >>>>Jéjé 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) >>> >>>(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... >>>>> >>>>> >>>>> >>>>>>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 >>>>>> >>>>>> >>>>>>Jéjé wrote: >>>>>> >>>>>> >>>>>> >>>>>>>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. >>>>>>> >>>>>>> >>>>> >>>>> >>>>> >>> > >
- Next message: Rea Peleg: "dts retrieves 0 rows when scheduled"
- Previous message: SBDTech: "Re: Problem with Olap Report on a Windows 2003"
- In reply to: Jéjé: "Re: hardware recommendation"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
