OT: SQL Server specification - Help

[IPAddis]

Hi all,

I am trying to specify a decent SQL server and web server. I could do with someone knowledgeable in servers to double check the spec to ensure I haven't forgotten anything before I send in the order form.

If you know your RAID 1+0 from your RAID 0+1 and are able to spare 10 minutes or so, please let me know. I will try to arrange some sort of beer tokens as renumeration.

Regards,

Ian A.

[sybaseian]

IPAddis said:

Hi all, I am trying to specify a decent SQL server and web server. I could do with someone knowledgeable in servers to double check the spec to ensure I haven't forgotten anything before I send in the order form. If you know your RAID 1+0 from your RAID 0+1 and are able to spare 10 minutes or so, please let me know. I will try to arrange some sort of beer tokens as renumeration. Regards, Ian A.

Ian - drop me a mail with what you are trying to do. Are you going to use java, cgi, etc. How much budget have you got?

[plewis66]

I'll have a squint, too if you like.

[ATG]

The ideal setup will depend on the way you're going to be using SQL Server. What is the balance btwn inserts and selects? Is it going to have to service lots of simultaneous requests? Do you want to sacrifice performance for reliability, or vice versa?

[sybaseian]

Ian,

there are various options for database software and each has its own advantages/disadvantages for cost verses performance/reliability/scalability. The high-end database servers - Sybase or Oracle can be expensive to license for the web as an enterprise solution, but can be had for free as developer editions on NT or Linux. Both are fully functional, but have limits on the number of concurrent uses, CPUs, etc but are very good to get an idea of what the enterprise version is capable of handling for OLTP and DSS systems.

Of these systems, Sybase is more secure, better performance and much easier to look after (which is why all the major banks use it). If you have used MS SQL Server or even MS Access, you will be familier with the concepts of how the Sybase database server is constructed (MS SQL server was based on Sybase SQL Server Version 4.9, but they had a legal split and Sybase brought out Version 10), which is why they are very similar. Oracle is very different in concept and to administer.

Drop me an e-mail and I'll go over the pro's/con's of each.

Ian

[IPAddis]

Hi all,

Thanks for the replies.

The software solution is fixed, sorted and set in 6 foot thick nuclear grade concrete. Microsoft Windows 2003, IIS 6 (ASP .NET) and SQL Server 2000.

We are programmers and DBAs by profession so we have the software side sorted. We just need to spec the hardware to run it on and hence we are looking for someone who has specific experience with ordering high-end hardware for this type of IIS/SQL setup.

I have been tasked by the boss to find a single person with the right experience. To this person, I can offer 20 quids worth of Pistonheads, TVRCC, Leven Tech or Demon Tweeks vouchers (if such things exists). If you can help, please email me via my profile.

The ratio will be about 90% selects and 10% inserts/updates. There will be lots of little selects, getting small amounts of rows from out of well designed, normalised tables. In some complicated cases, there will be 3 or 4 selects per web page. There are 200 users but they mostly spend their time on the phone so will likely open a page and then not use the computer for 5 minutes.

Ian A.

[sybaseian]

IPAddis said:

Hi all, Thanks for the replies. The software solution is fixed, sorted and set in 6 foot thick nuclear grade concrete. Microsoft Windows 2003, IIS 6 (ASP .NET) and SQL Server 2000. We are programmers and DBAs by profession so we have the software side sorted. We just need to spec the hardware to run it on and hence we are looking for someone who has specific experience with ordering high-end hardware for this type of IIS/SQL setup. I have been tasked by the boss to find a single person with the right experience. To this person, I can offer 20 quids worth of Pistonheads, TVRCC, Leven Tech or Demon Tweeks vouchers (if such things exists). If you can help, please email me via my profile. The ratio will be about 90% selects and 10% inserts/updates. There will be lots of little selects, getting small amounts of rows from out of well designed, normalised tables. In some complicated cases, there will be 3 or 4 selects per web page. There are 200 users but they mostly spend their time on the phone so will likely open a page and then not use the computer for 5 minutes. Ian A.

Ian,

once you have got the hardware/software set up, give me a call for some P&T advice. Earlier this year I did some work for an ISP that was having major performance problems with their web to database. 60 users were creating 204,000,000 disk I/Os per hour, the majority of it on one table with 200,000 rows and were waiting upto 30mins to get the results back to their screen. After a little tweaking it came down to 800,000 disk I/Os and the results getting back in 3ms!

[Mark.S]

IPAddis said:

The ratio will be about 90% selects and 10% inserts/updates. There will be lots of little selects, getting small amounts of rows from out of well designed, normalised tables. In some complicated cases, there will be 3 or 4 selects per web page. There are 200 users but they mostly spend their time on the phone so will likely open a page and then not use the computer for 5 minutes.

Feel free to drop me an email with more details but to start...

Have you carried out any load testing of the solution? After all, if the loads are light why shell out a fortune in hardware?

Load testing will also highlight which parts of the software are creating the greatest loads. It may then be a case of fixing/tuning part of the software or tailoring the hardware to cope.

Top reccomendation would be to consider splitting IIS and SQL to seperate machines. Especially wise if this a web facing system and the data has any value.

If the database will grow to any significant size (and there will be frequent access to a large range of tables/rows), throw plenty of memory in from the start. SQL2000 has far better caching mechanisms than earlier versions and will use whatever memory is available if its warranted.

Disk drive/array choices depends on a number of factors. Is redundancy key? Would downtime be catastrophic to the business? Is read/write performance key?

General wisdom suggests seperate drive(s)/controller for system files, SQL data files and log files. Avoid multiple partitions on drives or arrays where performance is key.

RAID-5 tends to be the best compromise for performance and redundancy for data files. If theres a high proportion of writes, RAID-0 is common for log files.

Processor is generally the last component to be maxed out with SQL server. I read an article about TPC benchmarking sometime ago that suggested you'd need to be running 20-40 disks before the processor became an issue.

Whenever I've been contracted to look at performance issues, it tends to be the database and/or application design thats causing problems. Developers tend not to spend enough time with load testing the database or application.

At the very least, capture an hours worth of 'normal' usage in a SQL trace and run it through 'Index Tuning Wizard'. Preferably, use 'Application Center Test' (from VS.NET) to run and monitor the system at anticipated loads.

I think thats my longest post ever!

[IPAddis]

Mark.S said:

Have you carried out any load testing of the solution? After all, if the loads are light why shell out a fortune in hardware?

That would be nice but the solution needs to go into Alpha testing as soon as possible so we need the hardware to be in place before we have even built the system. This is due to ill-advised time deadlines and the lack of any usage data from the client (and yes we did tell them).

Mark.S said:

Top reccomendation would be to consider splitting IIS and SQL to seperate machines.

They are seperate machines.

Mark.S said:

If the database will grow to any significant size (and there will be frequent access to a large range of tables/rows), throw plenty of memory in from the start. SQL2000 has far better caching mechanisms than earlier versions and will use whatever memory is available if its warranted.

We have 3GB of RAM as the client is supplying Windows 2000 standard edition so can't use the /3GB switch AFAIK. Hence SQL Server is limited to 2GB sadly.

Mark.S said:

Disk drive/array choices depends on a number of factors. Is redundancy key? Would downtime be catastrophic to the business? Is read/write performance key?

Both! Have gone for RAID 1 OS and logs and RAID 1+0 for databases.

Mark.S said:

General wisdom suggests seperate drive(s)/controller for system files, SQL data files and log files.

Have multiple RAID arrays but only a single controller. I suppose that is a single point of failure. Can you have twin SCSI cards servicing the same drives?

Mark.S said:

Processor is generally the last component to be maxed out with SQL server.

Cool that's what I want to hear. I could only afford 1MB L2 cache and 2 x 2.5Ghz processors.

Mark.S said:

Whenever I've been contracted to look at performance issues, it tends to be the database and/or application design thats causing problems.

We can do the software optimisations (DB design, indexes, performance monitoring etc) but we usually install our stuff on a server supplied by the client.

Mark.S said:

I think thats my longest post ever!

And most appreciated! If you can take a squizz at the spec, do you want to drop me a line via my profile?

Ta,

Ian A.

>> Edited by IPAddis on Thursday 9th October 08:37

[joust]

Here are some sample specs of Compaq kit for you.

Web Server - Entry Level SCSI Build
This Server Solution provides a entry level SCSI build, with a higher performance and efficiency than the standard DL320 architecture. Mirroring hard disks can also provide for greater redundancy and lay the foundations for disaster recovery.
Compaq DL360R G3, Xeon 2.4GHz, 512MB RAM, 2x36.4GB SCSI HD(iLO)
Supplier Code Description
292887-421 Rack ProLiant DL360 G3 R03 Xeon DP 2400-512/533 M1 (Ultra2/3 HP Backplane, iLO, 512MB)
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000 rpm Universal Hard Drive (1in)
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000 rpm Universal Hard Drive (1in)
64-0101301 Microsoft Windows 2000 Server with 5 CALs



Web Server
This DL380 is designed ideally for those organisations looking to provide a resilient high end Web and Web Logging architecture. Mirrored OS provides redundancy and storage 36.4Gb of storage capacity to save log files from applications such as Veritas and Web Trends. The 3 x 36.4Gb RAID 5 configuration is designed for the storage of the data and the web site and provides a maximum of 72.8Gb of storage space. The RAID 0 and RAID 5 configurations provide resilience and disaster recovery from disk failure, as a result there is no DAT backup with this solution.
Compaq DL380R, Xeon 2.4GHz, 2.5GB RAM, 2x36.4GB(OS-RAID1), 3x36.4GB(Data-RAID5) SCSI HD
Tetra Code Supplier Code Description
257917-421 Rack ProLiant DL380 G3 R03 Xeon DP 2400-512/400 (Ultra2/3 HP-Backplane, 512MB)
300680-B21 2GB 266MHz DDR PC2100 Registered ECC SDRAM DIMMS (2 * 1GB Interleaved)
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000 rpm Universal Hard Drive
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000 rpm Universal Hard Drive
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000 rpm Universal Hard Drive
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000 rpm Universal Hard Drive
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000 rpm Universal Hard Drive
64-0101301 Microsoft Windows 2000 Server with 5 CALs

Web - Application Server
Designed as a high end Web Server and entry level Application Server, the RAID 5 architecture of the this Server solution, is designed to provide maximum performance for read of data. Storage is also designed to accommodate users looking for a maximum of 72.8Gb of storage space.
Compaq DL380R, Dual 2.4GHz, 2.5GB RAM, 2x36.4GB(OS)RAID1, 3x36.4GB(DATA)RAID5 & AIT (HOT SWAP DAT)
Tetra Code Supplier Code Description
257917-421 Rack ProLiant DL380 G3 R03 Xeon DP 2400-512/400 (Ultra2/3 HP-Backplane, 512MB)
257913-B21 ProLiant ML370 G3 / DL380 G3 Xeon DP 2400/512-400 Processor Option Kit
300680-B21 2GB 266MHz DDR PC2100 Registered ECC SDRAM DIMMS (2 * 1GB Interleaved)
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000rpm Universal HD (1")
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000rpm Universal HD (1")
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000 rpm Universal HD (1")
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000 rpm Universal HD (1")
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000 rpm Universal HD (1")
215487-B21 50/100GB AIT Hot Plug Tape Drive (LVD)


Increased processing power with Dual processors and double 2GB RAM.
Compaq DL580R, Dual Xeon 2.0GHz, 2GB RAM, 2x36.4GB(OS)RAID1, 2x72.8GB (DATA)RAID1
Tetra Code Supplier Code Description
202176-421 Rack ProLiant DL580 G2 R02 Xeon MP 2000-2MB (2P, Ultra2/3 HP Backplane, 2GB)
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000 rpm Universal Hard Drive (1")
286713-B22 36.4GB Pluggable Ultra320 SCSI 10,000 rpm Universal Hard Drive (1")
286714-B22 72.8GB Pluggable Ultra320 SCSI 10,000 rpm Universal Hard Drive (1in)
286714-B22 72.8GB Pluggable Ultra320 SCSI 10,000 rpm Universal Hard Drive (1in)
64-0101301 Microsoft Windows 2000 Server with 5 CALs
06-0221372

No idea what your budget is, but if you want a fully managed server (rather than just buying the hardware) you can get prices from ringing our sales guys (<a href="http://www.easynet.net"><a href="http://www.easynet.net">www.easynet.net</a></a> - if not it should give you a good starting point and just buy the kit from the cheapest place you can find on-line.

J

>> Edited by joust on Thursday 9th October 08:47

[IPAddis]

joust said:

No idea what your budget is, but if you want a fully managed server (rather than just buying the hardware) you can get prices from ringing our sales guys (<a href="http://www.easynet.net"><a href="http://www.easynet.net"><a href="http://www.easynet.net">www.easynet.net</a></a></a> - if not it should give you a good starting point and just buy the kit from the cheapest place you can find on-line.

Thanks for that. I've specced one about middle of that list. We are runnning the service at the clients site for this project but we do have an upcoming project that we may need hosting externally. I will put your company down on the preferred suppliers list.

Thanks,

Ian A.

[joust]

IPAddis said:

Thanks for that. I've specced one about middle of that list. We are runnning the service at the clients site for this project but we do have an upcoming project that we may need hosting externally. I will put your company down on the preferred suppliers list.

Sounds like you have done it about right then.

Glad I could help.

J