[sims] Move to SQL 2012 - help with higher performance server spec

[siuko]

Due to the requirement to upgrade to SQL2012 for Sims I am also looking at purchasing a new server to run sims on (our old one is 4 yrs old now).

 

Our current Sims Server

 

Windows 2008 R2

Dell Poweredge T310

1 x Intel Xeon X3460 2.80GHz (4Core/8Thread)

12Gb Ram

136GB 15k SAS Raid1 OS Drive

136GB 15k SAS Raid1 Sims Folder, SQL Install and Docstorage Drive

Sims.mdf 5.86GB

Concurrent users - upto about 100+ (70-80 teachers doing registers etc, 20+ office staff running reports, inputing data etc)

 

The CPU usage on the sqlservr.exe can fluctuate but is often around 50-80% for most of the day. It can also max out when running complex reports, deleting students etc.

 

I will be looking to spec a dell server (our dell servers have always been reliable but if anyone can recommend any others please do) as a new replacement but have a few questions if anyone has any input.

 

1) Which OS should I go with? Windows 2008 R2, Windows 2012?

2) I have read on Edugeek where people recommend having the sims.mdf and tempdb.mdf on seperate hard drives. Would this make a big difference? (Our current tempdb.mdf is only 172mb?)

3) If I am to seperate all the data how should this be done? For Example 2 Disk Raid1 OS Drive, 3 Disk or 4 Disk Raid5 or Raid10 drive for sims.mdf, 3 Disk or 4 Disk Raid5 or Raid10 drive for tempdb.mdf. Where then would the docstorage go? And what size server will fit that many drives in!

4) Is it sensible to go with 2 x CPUs? If so would you only purchase Full SQL 2012 for one CPU or both? (Cost will go up)

5) How much ram? (Our present one seems to just eat all available ram)

6) Recommended number of network connections (will 1 x 1Gb be enough - or does more help?)

 

Anyone have anything else they think might be relevant - my main aim is to build a server that has much better performance than the one we have presently and doesnt crawl when we have everyone registering and doing reports.

 

Also I can understand that it might be suggested to virtualise the server (but due to us moving into a new school in sept 2014, moving to 1:1 tablets for sept 2014 and a new VLE I dont think I will have time to add in learning about

virtualisation)

 

Thanks in advance

Edited February 6, 2014 by siuko

[vikpaw]

To prevent having to upgrade the OS it's worth going with 2012, though i'm sure recently a few people recommended sticking with 2008 for now.

 

I'd say, keys things would be to up the RAM to whatever you can afford we have blade servers with 24GB or 36GB and allocate 10-12GB to powerhouse VMs. The systems will consume all you throw at them.

 

Do take the time to learn about VM. It's really very straight forward and then gives you options to alter the specs of the server to suit purpose, and keep a test server on low spec in standby. For example you can fix the RAM for the server to 12GB and allocate the rest elsewhere. Actually you can over-allocate and the system will spread the load. So in theory 12GB could be used to create 2 8GB servers. Plus you have the benefit of snapshot which is a life saver when upgrades go wrong.

 

An extra CPU wouldn't go astray. Especially for helping with adding more VMs. You should licence each, i think you'd have to anyway.

Splitting the files can make a lot of speed difference, check the threads where @jinnantonnixx mentions best practise.

The RAID types seem fine.

Two network connections is ideal, so you have redundancy.

[matt40k]

Those are lovely servers, I think we use the 400 series here. 1x quad core will be ok. Not sure why you'd 136GB 15k SAS for the OS however? I'd though sata be better. RAID-5 or 10 for the data. More discs to split the tempdb and log file off -yer ok in enterprise world you would, but look at the extra costs\hassle vs the benefit of just having 2/3 SSDs - be worth checking prices.

[Drenlin]

You can get your head in a twist if you take on all the advice out there but please remember the size of the database involved. You are not talking about multi terabyte databases.

 

Advice I hear but get confused about is the advice to seperate log data files, log files and the tempdb to seperate disks for performance. But surely this would need seperate RAIDs to get that performance or do you still get it if they are on logical drives on the same RAID? I have not worked that one out yet.

 

What we do implement though is to size your data file for the space it requires with 1 years growth. Also, check your autogrowth rate for the data and log file. Often the default for SIMS (due to the default Model database) is 1Mb growth for data and 10% for log file. Each year, perform a growth again. This cuts down on fragmentation (which can cause a performance hit). Set the data growth to a good size chunk to catch the missed expected growth.

 

As for the log file, look up the topic of 'virtual log files'. This is what your SQL log file is made up from. Too many and this will slow the performance. If you do not set your log file initial size and growth correctly, each time you run the Capita reindex patch you are encouraging a large number of virtual log files.