Hardware needs for this setup?

[befortin]

I'm trying to determine what kind of hardware I should use for a SharePoint Portal Server (single server). The server should get about 10 RPS (Requests per second), this is why the single server setup will be used. The SQL database (because of the document management) should grow to about 600 GB.

I wonder what hardware I should use to get some good performance from the SQL Database. What kind of difference in performance would the following setups give me?

1 - Would "Setup # 2" be about 10% faster than "Setup # 1"? Or more like 20% Or something else?

2 - What about "Setup # 3"?

3 - Also, how much RAM would that kind of setup need? 2 GB? 4 GB? 8 GB? more?

4 - And then, what about the processor? Single processor? Dual processor ? Quad processor?

5 - Should 36 GB disks be enough for the Transaction log and the TempDB?

I don't think that the IIS part of the setup will use much CPU and RAM (since it shouldn't get more that 10 RPS during peeks), so I focus more on what the SQL Server part needs than on the IIS part.


Setup # 1
=========
OS : 2 X 73 GB @ 15K RPM (RAID 1)
SQL Database + transaction log : 4 X 300 GB @ 10K RPM (RAID 10)


Setup # 2
=========
OS : 2 x 73 GB
SQL Database : 4 X 300 GB @ 10K RPM (RAID 10)
Transaction Log : 2 X 36 GB @ 15K RPM
TempDB : 2 X 36 GB @ 15 K RPM


Setup # 3
=========
OS : 2 x 73 GB
SQL Database : 8 X 146 GB @ 15K RPM (RAID 10)
Transaction Log : 2 X 36 GB @ 15K RPM
TempDB : 2 X 36 GB @ 15 K RPM

 

[abaldwin]

Just my $.02

I would suggest that the data be on a RAID5 set and have another set that is capable of doing an online backup of the db. In other words more hd space.

The trans log I ususally mirror. Also the size of the of the trans log I ususally think that it should allowed to get about 25% the size of the db.

All the RAM you can muster. That may also have something to do with the version of SQL SERVER you can get. Check out MS site on the versions and their capabilities / restrictions on the amount of ram and processors used.

Later.

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[mrdenny]

A 600 Gig sharepoint database is VERY large. Unless something in sharepoint has changed recently the documents aren't kept in the database. Only a pointer to the document is kept in the database. The actual documents should be kept on the web server.

If indead you will have a 600 Gig database of the three options provided #3 would be the best bet. You'll get better performance out of the 146 Gig drives than you will out of the 300 Gig drives as the heads will have less distance to travvel to get to the data (as there is less stored on each physical head). I'd put it on a RAID 5 array as RAID 10 is good for write intensive apps, and databases are mostly read.

You may want to check out my FAQ on disk configs faq962-5747.

I'm not sure how the sharepoint database is setup from a physical or logical setup. If possible I'd create several RAID 5 arrays. One for the database, and one for the indexes. If possible several different arrays for the database so as to maxamize the number of spindals that the database has when searching.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[befortin]

abaldwin : thank you for all this information. So you always must use 25% of database size for the transaction log? I don't know much about databases, but I thought that the transaction log was like "what has changed recently and has not been written to the database", and I don't think that more than a few GB of the database would change each day. I am right to think that I should use the space of what would change in a day (or a few hours, I don't know) for the transaction log?

mrdenny : thank you for the FAQ, it's interesting stuff. As for the size of the database, i read in the SharePoint Resource Kit book that "Windows SharePoint Services stores all documents, lists, view and configuration information in SQL server content stores". So I guess that I really have to prepare for a 600 GB database (the 600 GB includes the overhead for the index, search and database, which is 2.75 X total document size).


Is this the kind of setup that I should use :
- RAID 1 array for the OS;
- RAID 5 array for database;
- RAID 1 array for index;
- RAID 1 array for the transaction log.


Or would this setup provide enough performance for my needs (only a few RPS, remember?)
- RAID 1 array for the OS;
- RAID 5 array for database;
- RAID 1 array for the transaction log.

 

[abaldwin]

befortin, You do not have to always use 25% I just use that as a very good rule of thumb. For this much usage and size I would not short-change myself space, redundancy or ram.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[befortin]

Oh, and I forgot, is it worth having the tempdb on another array too for this much RPS?

 

[SQLBill]

BTW-there are several transactions that can cause a transaction log to grow to huge sizes. If you ever do a DBCC REINDEX your tlog can grow to almost TWICE the size of the database. This happens in case the reindex fails and everything has to rollback.

-SQLBill

Posting advice: FAQ481-4875

 

[befortin]

SQLBill, thanks for this information. Maybe I should just forget about putting transaction log on a different array!? I can't afford buying twice the disks of the database only for the transaction log! Or what about having 136 GB of free space for the transaction log, while the database would have 600 GB of disks?

Since there won't be a lort of request per second, I guess that I could put IIS on an existing array? If so, should I install it on the same array as the OS (on a different partition), or on the same array as the database? I think it would be OK on the same array as the OS, since the OS disks won't be used very much (same for the IIS files, which are very small and will only be used a few times per second)?