Having difficulty setting Back Up to back up file wihout datetime stamp SQL 2K

Hello,

I'm trying to create a simple back up in the SQL Maintenance Plan that will
make a single back up copy of all database every night at 10 pm. I'd like
the previous nights file to be overwritten, so there will be only a single
back up file for each database (tape back up runs every night, so each days
back up will be saved on tape).

Every night the maintenance plan makes a back up of all the databases to a
new file with a datetime stamp, meaning the previous nights file still
exists. Even when I check "Remove files older than 22 hours" the previous
nights file still exists. Is there any way to create a back up file without
the date time stamp so it overwrites the previous nights file?

Thanks!
RickOn 24.04.2007 15:29, Rico wrote:

Quote:

Originally Posted by

I'm trying to create a simple back up in the SQL Maintenance Plan that will
make a single back up copy of all database every night at 10 pm. I'd like
the previous nights file to be overwritten, so there will be only a single
back up file for each database (tape back up runs every night, so each days
back up will be saved on tape).
>
Every night the maintenance plan makes a back up of all the databases to a
new file with a datetime stamp, meaning the previous nights file still
exists. Even when I check "Remove files older than 22 hours" the previous
nights file still exists. Is there any way to create a back up file without
the date time stamp so it overwrites the previous nights file?

Yes, I believe option is "WITH INIT". Please see BOL for details.

Regards

robert|||Hi Robert,

Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
using T-SQL, just trying to create the Maintenance plan from the Enterprise
Manager.

Rick

"Robert Klemme" <shortcutter@.googlemail.comwrote in message
news:596ghdF2jtij6U1@.mid.individual.net...

Quote:

Originally Posted by

On 24.04.2007 15:29, Rico wrote:

Quote:

Originally Posted by

>I'm trying to create a simple back up in the SQL Maintenance Plan that
>will
>make a single back up copy of all database every night at 10 pm. I'd
>like
>the previous nights file to be overwritten, so there will be only a
>single
>back up file for each database (tape back up runs every night, so each
>days
>back up will be saved on tape).
>>
>Every night the maintenance plan makes a back up of all the databases to
>a
>new file with a datetime stamp, meaning the previous nights file still
>exists. Even when I check "Remove files older than 22 hours" the
>previous
>nights file still exists. Is there any way to create a back up file
>without
>the date time stamp so it overwrites the previous nights file?

>
Yes, I believe option is "WITH INIT". Please see BOL for details.
>
Regards
>
robert

|||Rico (me@.you.com) writes:

Quote:

Originally Posted by

Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
using T-SQL, just trying to create the Maintenance plan from the
Enterprise Manager.

From what you described, you should not use a maintenance plan. Just set up
a scheduled job to run the BACKUP statement. You can do this from the
GUI where you backup databases, and select Schedule somewhere on a button.
In the end you get a one-step job that has a BACKUP job.

Then again, if you have any interest in acquiring basic DBA skills, you
should certainly learn to write basic BACKUP commands in T-SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,

I don't have a problem creating the T-SQL commands, it's using the
Enterprise Manager to do more than create dbs is where I get lost.

I will give that a try (creating a back up job and scheduling)

THanks!
Rick

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns991D5A7E496DYazorman@.127.0.0.1...

Quote:

Originally Posted by

Rico (me@.you.com) writes:

Quote:

Originally Posted by

>Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
>using T-SQL, just trying to create the Maintenance plan from the
>Enterprise Manager.

>
From what you described, you should not use a maintenance plan. Just set
up
a scheduled job to run the BACKUP statement. You can do this from the
GUI where you backup databases, and select Schedule somewhere on a button.
In the end you get a one-step job that has a BACKUP job.
>
Then again, if you have any interest in acquiring basic DBA skills, you
should certainly learn to write basic BACKUP commands in T-SQL.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||
"Rico" <me@.you.comwrote in message news:8LvXh.9$_G.8@.edtnps89...

Quote:

Originally Posted by

Thanks Erland,
>
I don't have a problem creating the T-SQL commands, it's using the
Enterprise Manager to do more than create dbs is where I get lost.

That's the problem with using EM.

BTW... the reason you're seeing the behavior you're seeing is that the
maintenance job takes the conservative approach and assumes that the most
recent backup HAS to succeed before it'll delete the older one.

So setting it to 22 hours or anything won't force it to delete the older
file until the new one is successfully created.

Quote:

Originally Posted by

>
I will give that a try (creating a back up job and scheduling)
>
THanks!
Rick
>
>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns991D5A7E496DYazorman@.127.0.0.1...

Quote:

Originally Posted by

>Rico (me@.you.com) writes:

Quote:

Originally Posted by

>>Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
>>using T-SQL, just trying to create the Maintenance plan from the
>>Enterprise Manager.

>>
>From what you described, you should not use a maintenance plan. Just set
>up
>a scheduled job to run the BACKUP statement. You can do this from the
>GUI where you backup databases, and select Schedule somewhere on a
>button.
>In the end you get a one-step job that has a BACKUP job.
>>
>Then again, if you have any interest in acquiring basic DBA skills, you
>should certainly learn to write basic BACKUP commands in T-SQL.
>>
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Hash join

In some cases when I see the query execution plan I found that Hash
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regards
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regards

Hash join

In some cases when I see the query execution plan I found that Hash
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regardsamish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regardssql

Hash join

In some cases when I see the query execution plan I found that Hash
join or Merge join or sort operation is taking about 50% of execution
time.Can I do something to improve it? What is the cause of this high
proportion of execution time taken by this operation?
Regardsamish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
>
> Regards
Merge joins are generally very quick. They are used when you have sorted
intermediate result sets that must be combined. If they are not
presorted, then a sort operation is visible and this will slow down
thing significantly. Imagine combining two sorted result sets. You start
scanning both of them, top down, looking at key values, and creating a
new result set from the simultaneous scan of both. Easy.
A hash join is more involved and is generally used when a result sets
needs to be joined, but there no keys available to relate them. Hash
values are created from the keys and these values are used for scanning.
A more involved process.
To prevent seeing these types of joins (for most queries) make sure your
joins have index support and the join clauses are SARGable.
Where TABLE1.ID = TABLE2.ID -- Needs indexes that contains the ID column
as the first column on both tables
Where LEFT(TABLE1.ID, 2) = LEFT(TABLE2.ID, 2) -- Not SARGable. Index
will not help
Where ISNULL(TABLE1.ID, 0) = TABLE2.ID -- Not SARGable
Post your tables, ddl, indexes, and query for more help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I guess the real question is: are you satisfied with the query's
performance?
I mean, what do you care which part of the query execution is
responsible for which estimated portion of the execution? SQL-Server
will try to execute the query as fast as possible, and hash joins and
merge joins are tools that are used in the process.
Gert-Jan
amish wrote:
> In some cases when I see the query execution plan I found that Hash
> join or Merge join or sort operation is taking about 50% of execution
> time.Can I do something to improve it? What is the cause of this high
> proportion of execution time taken by this operation?
> Regards

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 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.
>
|||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) (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...
>
> 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...[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:
(for[vbcol=seagreen]
=[vbcol=seagreen]
processor
>
|||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...[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:
(25gb of[vbcol=seagreen]
(6)[vbcol=seagreen]
while[vbcol=seagreen]
the[vbcol=seagreen]
contain[vbcol=seagreen]
18[vbcol=seagreen]
other
>
|||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 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...
>
> (25gb of
>
> (6)
>
> while
>
> the
>
> contain
>
> 18
>
> other
>
>

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 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.
>
|||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) (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...
>
> 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...[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:
(for[vbcol=seagreen]
=[vbcol=seagreen]
processor
>
|||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...[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:
(25gb of[vbcol=seagreen]
(6)[vbcol=seagreen]
while[vbcol=seagreen]
the[vbcol=seagreen]
contain[vbcol=seagreen]
18[vbcol=seagreen]
other
>
|||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 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...
>
> (25gb of
>
> (6)
>
> while
>
> the
>
> contain
>
> 18
>
> other
>
>

Hardware Recommendation

Hello,
I plan to run Microsoft CRM, Navision SQL, and Project Server off the
same SQL box. What would you recommend hardware wise?
Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
Thanks in advance
First, put as much memory as you can afford in the box. Be aware that
anything over 4GB will require Enterprise Edition to take advantage of the
extra RAM. Dual Procs are a minimum. Go with procs with more cache rather
than faster procs, but only by 300-500 MHz. Then we geet to the disk
system. Definitely separate your SQL logs and data onto different spindles.
RAID 1 or 1+0 for logs will improve performance significantly. RAID 1+0 for
data if you can afford it, RAID5 otherwise, but it will impact performance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"CB" <chadb@.dentistryonline.com> wrote in message
news:esoMsGvMEHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I plan to run Microsoft CRM, Navision SQL, and Project Server off the
> same SQL box. What would you recommend hardware wise?
> Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
> Thanks in advance
>

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
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.
>|||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.
> >
> >|||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.
>>
>
>|||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.
> >>
> >>
> >
> >
> >
>|||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.
>>
>>
>|||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.
> >>
> >>
> >>
> >>
> >>
> >
> >
>|||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.
>>>
>>>
>>
>>
>

Hardware Recommendation

Hello,
I plan to run Microsoft CRM, Navision SQL, and Project Server off the
same SQL box. What would you recommend hardware wise?
Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
Thanks in advanceFirst, put as much memory as you can afford in the box. Be aware that
anything over 4GB will require Enterprise Edition to take advantage of the
extra RAM. Dual Procs are a minimum. Go with procs with more cache rather
than faster procs, but only by 300-500 MHz. Then we geet to the disk
system. Definitely separate your SQL logs and data onto different spindles.
RAID 1 or 1+0 for logs will improve performance significantly. RAID 1+0 for
data if you can afford it, RAID5 otherwise, but it will impact performance.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"CB" <chadb@.dentistryonline.com> wrote in message
news:esoMsGvMEHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I plan to run Microsoft CRM, Navision SQL, and Project Server off the
> same SQL box. What would you recommend hardware wise?
> Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
> Thanks in advance
>

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
>
>

Hardware Recommendation

Hello,
I plan to run Microsoft CRM, Navision SQL, and Project Server off the
same SQL box. What would you recommend hardware wise?
Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
Thanks in advanceFirst, put as much memory as you can afford in the box. Be aware that
anything over 4GB will require Enterprise Edition to take advantage of the
extra RAM. Dual Procs are a minimum. Go with procs with more cache rather
than faster procs, but only by 300-500 MHz. Then we geet to the disk
system. Definitely separate your SQL logs and data onto different spindles.
RAID 1 or 1+0 for logs will improve performance significantly. RAID 1+0 for
data if you can afford it, RAID5 otherwise, but it will impact performance.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"CB" <chadb@.dentistryonline.com> wrote in message
news:esoMsGvMEHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I plan to run Microsoft CRM, Navision SQL, and Project Server off the
> same SQL box. What would you recommend hardware wise?
> Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
> Thanks in advance
>