Choosing Hardware for a SQL Server 2005 Database for Web Applications

When you are tasked with choosing a new database server, let the business requirements dictate the hardware and license choices you make. I am only going to cover SQL Server 2005 Std and Enterprise editions, if you require less horse power then any modern desktop will be ample. If you require more horsepower then you need to talk to one of the Tier 1 vendors Sun, IBM or HP and you’ll need a big cheque book.

SQL Server 2005 Licenses

So lets start with SQL Server Licensing, there are a few different licensing models available, per seat, per device or per CPU. The chances are you will only be interested in per CPU, if your web application is publically available or is an extranet type app you will not have domain users accessing it, a possible exception is an intranet app. From now on I am going to focus on the per CPU licence.

SQL Server 2005 now ships for x32, x64 and IA64 Itanium processors. The price is the same for each at around £4500 for a Std licence and £16000 for an Enterprise license. MS multicore policy is to base this on the physical number of CPUs and not the number of cores. This is important because Intel and Amd are delivering more MIPS by adding cores instead of going after higher clock speeds. When choosing a server it makes financial sense to rember this, for example:

               2005 Std Edition License for a Quad CPU server will cost 4 x 4500 = £18,000
               2005 Std Edition License for a Single CPU Quad Core will cost 1 x 4500 = £4,500

This example is extreme and a single cpu with four cores may not yield the same performance as four CPU’s but it does illustrate how making an informed hardware purchase can keep the server licence costs down. It is also worth remembering that opting for a 64bit server has been shown to yield 200% performance gain in real world applications. If you are building a server that will be dedicated to running your database then choosing x64 should be a no-brainer unless you have some sort of corporate policy that prevents it. If you intend to run other apps then you should check if they are available in x64 versions. As a rule it is a good idea to just run SQL Server on its own. Std Edition will support a maximum of four CPU’s and Enterprise is unlimited.

Server Hardware

RAM or Memory if you prefer is a key component of any database server and it makes a big difference to performance. SQL server 2005 Std and Enterprise both support the maximum provided by the OS. If you choose Std Edition then Windows 2003 x64 R2 is the best OS choice. It supports up to 4 cpu’s and can address up to 32GB of RAM and costs less than £700. If you have chosen Sql Server Enterprise edition then the recommended operating system is 2003 Datacenter x64 it is cheaper than Enterprise at ~£2000 and will support up to 64 Cpu’s and 1 terrabyte of RAM for more info on os licensing use this link. 2003 Server Comparison

AMD or Intel? I don’t think there is a huge difference in performance if you need a dual cpu server, if you are building a quad then AMD’s Opteron performs better but you can only scale up to eight cpu’s where as Intel offer 32 cpu solutions.

SCSI, Fibre Channel, SAN or SAS ? SAS probably it will future proof better you can have over 126,000 drives, prices are a little high at the moment but should fall, fiber offers good performance and the price is good now but I think it will give way to SAS. SAN’s are big money but offer the highest performance. SCSI is starting to look like it is on the way out and limits the number of drives to around fify on a four channel card. SAS Architecture

SAS Articles:
http://www.serialstoragewire.com/Articles/2004_1026/5_vitesse_it_insights_1004.html
http://www.techworld.com/storage/features/index.cfm?featureid=1319&pagtype=samecatsamechan

Application Workload

OLAP or OLTP? What sort of application(s) are going to be accessing the database, if you are going to be doing large numbers of transactions then random disk access is going to be your I/O bottle neck and you will want lots of low capacity high speed disks. If you are building a data warehouse for reporting then you will need to store more data and access will be more sequential so less disks with a higher capacity and a lower speed will make sense. If you are migrating from an older Sql Server then you probably can guage what sort of capacity you require. If not you should do some rough calculations double the results and add a bit more for luck.

Availability and Scale

If your application is enterprise level, has thousands of concurrent users and terrabytes of data and must be available 24/7 365 days a year then you are going to have to shell out the extra £££ for an Enterprise licence. Enterprise has a whole host of features that support availbility and managing very large databases that Std does not. If you have hundreds of users and the buisness does not require the system be available 24/7 and will permit out of office hours down time then Std will be fine.

Server Lifetime

Lifetime of the server? Generally you should be building a server that will give you a guaranteed three years service, it may turn out to be five years before a replacement is needed, a lot of the warranties offered will be five years so this is probably as long as is sensible for a production server. It is almost certain that after three years technology will have of moved on significantly enough to  mean that upgrading to a server twice as powerful server will cost the same or less as the server you are just about to purchase.

 

Environmentally Friendly Computing

I have just replaced my personal webserver, I was running an AMD64 3200+ in a standard tower case, it sounded like a hairdryer and if you placed your hand near the fan outlet it felt like one. I checked out what the recommended psu would be for this system just to get an idea of it’s power consumption. I used this  PSU-Calculator  and it came up with a rating of 192 watts for 50% cpu utilisation.


I wanted something quieter and cooler that used less electrons, so checked out the Via ITX mobo’s. ITX motherboards are tiny at only 12cm x 12cm and the VIA ones come with an embeded CPU and HSF. I opted for the VIA EPIA EN15000 and a Morex Cubid 3688 case with 1GB ram, a Laptop DVD RW Drive and a 2.5″ Laptop drive. I use this as a webserver 24×7 for hosting this blog and I use it as a desktop at home. I have installed Visual Studio 2005 Pro and have had no performance problems. As a desktop it feels just as responsive as the old machine. It is not suitable for video rendering so I do this on my Duo Laptop but I only need to do this occasionally so no big deal. I know this system uses less than 60W because that is the rating on the external power supply. I estimate that at Idle it is using ~20W and it will be at Idle most of the time.


 



The other benefit is desk space the case is only W 20cm x D 27cm x H 7cm or roughly half the width of a standard Hifi separate with similar height and depth. The motherboard comes with onboard graphics, sound and GB Lan it also has four USB ports, S/Video and a RCA port which can be used for TV/Out or SPdif. The embedded processor is Via’s 1500mhz C7 according to VIA this cpu at 100% utilisation uses only 12 Watts and has an average power consumption of 1 watt. How green is your desktop/webserver ?



 


It seems great minds think alike 🙂