TS & SQL Server - Hardware Opinion

madman045 · 14 Jun 2008 at 19:34

Currently trying to sort out a new TS and SQL server, this is the current spec which hosts internally 50 users

Dual Xeon (Server is about 6yrs old), 4GB, 2x 74GB SCSI (7200rpm) Raid 1 running Windows 2000 Server and SQL 2000

It also runs TS for about 50 users

Now it's very slow, falls over and the main business app running on it falls over on a regular basis, now this is the spec they have been recommend for the upgrade and also to grow another 50 users over the next five years.

TS Box - Xeon 5320, 3GB, 2x146GB 10k SAS Raid 1 on 128mb controller, redundant PSU, 3YRs hardware support, Windows 2003 Standard R2

SQL Box - Dual Xeon 5320, 8GB, 7x 146GB SAS 10k, 2xRaid1, 5xRaid5, Redundant PSU, Windows 2003 Server X64 R2 STD, Single SQL 2005 Standard X64 CPU License

Would this be enough for 50-100 users for the next five years? I had spec'd something quite different, but they want the cheapest option

Thanks

oldbag · 14 Jun 2008 at 19:39

Go for 15k rpm disks and quad core cpu's if you can for SQL. Surely the TS box will need more memory for that many users?

Shaz]sigh[ · 14 Jun 2008 at 20:07

SQL Box - Dual Xeon 5320, 8GB, 7x 146GB SAS 10k, 2xRaid1, 5xRaid5, Redundant PSU, Windows 2003 Server X64 R2 STD, Single SQL 2005 Standard X64 CPU License

That spec appears to be dire.... firstly it's a single physical processor license, therefore get as many cores on it as you can.

Secondly the drive spec is awful, a simple (read consolidated) array config would be something like 2x10k disks in a raid 1 array for the OS, raid 5 15k array for the database (the amount of spindles depends on your IO requirements but remember, raid 5 is n+1), 2xsata disks in a raid 1 array for the logs and then another raid 5 array for the backups..... If you were taking it more seriously you'd also mount tempdb on a raid 1 array or some such, seperate from the main database.

Finally, the memory is quite important, if it's feasible, buy as much memory as you can so that the entire database can sit in memory. Especially on a 64bit host....

TS Box - Xeon 5320, 3GB, 2x146GB 10k SAS Raid 1 on 128mb controller, redundant PSU, 3YRs hardware support, Windows 2003 Standard R2

With that much memory you might get paging and if that happens, with that array, you'll get a performance hit.

As before, quad core (get two?), as much memory as you can afford and seperate your apps from your OS array.

madman045 · 14 Jun 2008 at 20:07

Well this is what I thought, but hey the customer is always right (or not as the case may be)

Here is what I spec'd

TS: Dual Quad Core E5340, 8GB, 4x73GB 15k SAS, Raid 10 with 1x73GB Hotspare on 256mb raid controller with BBU, SAS Controller with external ports, with LTO-4 Backup for both TS and SQL box (and future Exchange 2007 Box), Windows 2003 Server Standard X64, Redundant PSUs etc..

SQL: Quad Core X5450, 8GB, 2x 146GB 15k SAS Raid 1, 6x 146GB Raid 10, 1x146GB SAS Global HS, Redundant PSUs, Windows 2003 Standars X64 and SQL 2005 Standard X64 (Single CPU Licence)

How does that sound?

The 5320 CPU they spec'd is a Quad 1.86Ghz, 1066 with 2x4mb where as the one's im looking to use are 2.66Ghz with 1333 and 2x6mb and the SQL is a 3Ghz, 1333 2x6mb

Edit: They have also suggested, that as they dont want to pay for TS licensing, they want to use their old box for TS instead of the new server so they didnt have to spend more lol

Sone · 15 Jun 2008 at 05:41

for the TS box you only really need 1 Quad core. Every TS box i have used disk and memory are the main bottlenecks.

But then again for what they cost i always buy 2 anyway

nightmare99 · 15 Jun 2008 at 09:00

Also can you get eon E54xx instead of the older E53xx ? You should be able to get a E5410 for the same price as a E5320, its faster got more cache higher FSB 45nm, should be the same for all E53 vs E54 chips.

akakjs · 15 Jun 2008 at 09:42

madman045 said:
TS: Dual Quad Core E5340, 8GB, 4x73GB 15k SAS, Raid 10 with 1x73GB Hotspare on 256mb raid controller with BBU, SAS Controller with external ports, with LTO-4 Backup for both TS and SQL box (and future Exchange 2007 Box), Windows 2003 Server Standard X64, Redundant PSUs etc..

SQL: Quad Core X5450, 8GB, 2x 146GB 15k SAS Raid 1, 6x 146GB Raid 10, 1x146GB SAS Global HS, Redundant PSUs, Windows 2003 Standars X64 and SQL 2005 Standard X64 (Single CPU Licence)

How does that sound?

That sounds ok for a basic SQL server setup, but chances are we can do better if look at the I/O profile of your business application. Is it tempdb heavy (tempdb on it's own array)? What's the read/write operation ratio? Are particular tables hit more than others? (you could setup a second mssql file for those tables on it's own separate array) And how much data are we talking about?

Ben

daz · 15 Jun 2008 at 11:55

We use Dell 2950s with 6 x 300GB 15k SCSI in RAID5 with one as a hot spare for database stuff. Works very well. 8 cores, 8GB RAM.

Secondly the drive spec is awful, a simple (read consolidated) array config would be something like 2x10k disks in a raid 1 array for the OS, raid 5 15k array for the database (the amount of spindles depends on your IO requirements but remember, raid 5 is n+1), 2xsata disks in a raid 1 array for the logs and then another raid 5 array for the backups..... If you were taking it more seriously you'd also mount tempdb on a raid 1 array or some such, seperate from the main database.

Seems a bit overkill you may as well do backups to another machine over gigabit lan or something. Backups on the same machine physical is a bad idea anyway.

madman045 · 15 Jun 2008 at 21:35

As it's not my DB, I cant answer that, however it has 5/6yrs worth of data, the DB itself is about 5GB and the tempdb is only 8mb at the moment, but it's not being used being a sunday. The application itself is made up of multiple programs (mainly vb written) and obviously it runs one app per user as they go into different parts of the software. it did fall over last week when someone ran a report on stock levels.

At the end of the day, as like everything they buy, they always look to the cheapest option and expect it to last years, they do the same with desktops and laptops of which generally the laptops last 6-12 months tops and they just buy another £350 laptop

akakjs · 16 Jun 2008 at 07:56

daz said:
Seems a bit overkill you may as well do backups to another machine over gigabit lan or something. Backups on the same machine physical is a bad idea anyway.

The backup arrays are usually used as a staging area for tapes, transaction log backups and being able to roll-back your database quickly in the even of data corruption. Plus you want to get you backup done as fast as possible so you need a local drive. Backing up directly to a network drive is out anyway as SQL Server won't talk to network drives.

As it's not my DB, I cant answer that, however it has 5/6yrs worth of data, the DB itself is about 5GB and the tempdb is only 8mb at the moment, but it's not being used being a sunday. The application itself is made up of multiple programs (mainly vb written) and obviously it runs one app per user as they go into different parts of the software. it did fall over last week when someone ran a report on stock levels.

If it's just 5Gb of data you'll be laughing with that server.

akakjs

madman045 · 16 Jun 2008 at 09:31

While the DB itself isnt huge, I dont know how much of a mess it's in due to all the application development changes over the years, new stuff added randomly

The idea around the backup is Backup Exec on the TS box and the SQL Agent on the SQL box and backup across the lan and next year they are looking to get an email server, but I'm not looking forward to seeing what they dont want to spend there.