SIMS server - SQL 2008, SSDs, recommendations

[3s-gtech]

Hi,

Just looking for some concensus of opinion on what to do with my current SIMS server. We have an HP DL140 G3, so spec is:

E5335 quad core processor

8GB RAM (4 in use)

2 x 146GB SATA HDD

Server 2003, SQL Server Express 2008.

 

Obviously, we're not getting the best from this machine at the moment, and some opinions on which way to go with it would be good. Do you think these upgrades would make a decent difference? SIMS in classrooms is okay, but it's heavily used here and seems a bit sluggish now.

 

Upgrade to Server 2008 R2, utilize all 8GB of RAM.

Upgrade to SQL Server Standard on our EES licence.

Upgrade HDDs to OCZ Vertex 2 (will need to fit a SATA 2 card as onboard is SATA 1)

 

This was never a top model server, and is a few years old now. However, it's been reliable and done the job for over 3 years. Is it worth the spend? I can't afford a new server, but I could virtualise it onto my VM host instead (quad Opteron, SAS 15k RAID5, 5 VMs already).

 

Sorry for all the questions, but I'm stuck on the best course of action with this.

[eejit]

Looks a pretty good server for SIMS, but yeah upgrading to 64bit OS and full SQL 64bit would be my initial thoughts.

I'm interested by your thinking for SSDs for the server though. I've not seen them in any server yet and would be interested in hearing some real life experiences. Any time I look for performance information regarding SSDs there is always a massive debate about how much gain you actually get. I end up thinking that the percentage performance increase is not worth the funds as it may look good on a graph but would the difference be enough for end users to notice?

[eejit]

btw, don't just go Server 2008 R2 without upgrading the SQL to 64bit as I've seen reports that running 32bit SQL on 64bit OS performs worse than running 32bit SQL on 32bit OS.

[Hecate]

If you are running SQL express it only uses 1Gb of RAM no matter how much is in the server. Probably the biggest boost would be to upgrade to SQL standard.

[Butuz]

I wouldn’t use SSDs for an SQL server as their lifespan is considerably affected by how much data is written back to disk - and a SIMS SQL server does a massive amount of writing to disk which means the SSD's may not last long.

 

I think your best value performance increase would be from moving to Windows 2003 x64 or Windows 2008 and Sql 2008. Moving to 64 bit means SQl can address the full 8GB ram (or 16 if you upgrade) and thus fit the entire working dataset into RAM which will speed things up no end.

 

It looks as if your running SAS drives (not SATA due to the fact 146GB is usually a SAS size). You don't have much hard drive upgrade path other than to see if you are running 10,000rpm SAS drives or 15,000. If your on 10k you could upgrade both disks to 15k whilst your rebuilding the server on 64 bit. But that’s a fair bit of expense for not much performance increase.

 

Other than that it's new server time really.

 

Butuz

[Butuz]

Oops - I missed the fact your using SQL 2008 Express. Teach me to read!!

 

As Hecate says - that is your first port of call. Upgrade to SQL 2008 Std.

 

Butuz

[3s-gtech]

Thanks everyone for your help.

 

Yeah 146GB is usually the SAS option - I think these are actually 160GB and 146GB is the formatted size - but these are SATA. It'll take SAS, but they're more expensive than SSDs (though I can then use the fancy RAID card that's in there rather than onboard).

 

I did think about SSD lifetime. There are some white papers on this, pretty dry reading but not sure if the performance benefits will be massive. I certainly have plenty of space yet, two 60GB SSDs will give more than enough storage for this server - would have them non-RAIDed for TRIM with DBase and logs on seperate drives.

 

SQL Server Standard is a definite then. I can easily upgrade that with the inplace upgrade system on the server, which will help. I'm just reluctant to do a full OS install to 2008R2 on these same drives until I'm sure they'll be okay for the job. My hunch is that they're not.

[Butuz]

To be honest if you get 8GB or 16GB of RAM in there with a 64bit O/S and SQL 2008 Std your hard drives will barely be touched. Have a look at a graph of my hard drives now that I have upgraded to 8GB RAM and fit the entire SIMS and FMS databases in RAM. Disks are just ticking over.

 

 

Butuz

[3s-gtech]

Ah, that's just the sort of info I need. My SIMS database is 2.1GB (plus FMS is 2.5GB) so it should easily fit within the RAM once I move to Server 2008 R2 and 'proper' SQL. I think I'll persist with the current drives then - I can always image the OS across in the future.

[eejit]

To be honest if you get 8GB or 16GB of RAM in there with a 64bit O/S and SQL 2008 Std your hard drives will barely be touched. Have a look at a graph of my hard drives now that I have upgraded to 8GB RAM and fit the entire SIMS and FMS databases in RAM. Disks are just ticking over.

 

[ATTACH=CONFIG]9845[/ATTACH]

 

Butuz

 

Great information Butuz - do you mind me asking how you got that data & chart?

[Butuz]

Great information Butuz - do you mind me asking how you got that data & chart?

 

Yes my servers are virtualised on VMWare ESXi and as standard it gives you the ability to view these nice easy to read graphs of the last hour's performance on whatever option you want to look at (CPU, RAM, HDD, NETWORK, etc).

 

Cheers

 

Butuz

[3s-gtech]

Just to conclude for those doing this job - spent a productive day on it today. The old SIMS server was virtualised to keep it running (though the VM couldn't cope - had no end of issues with performance despite powerful host). We stuck Server 2008 R2 on the physical server, I used a proper hardware RAID card and the original SATA disks. SQL 2008 R1 Enterprise trial was installed until my SQL Standard licence comes (we used R1 as that's what the LEA currently support). We then migrated the database across and changed the drive maps - success. Users seem happy - the attendance side seems faster than before, and aside from a few wonky connect.ini which I'll sort with a script, we're away. Thanks for all your help.