Recommended Hardware Specifications for SQL Server

Support for Micosoft SQL Server in the context of Jiwa installations.

Recommended Hardware Specifications for SQL Server

Postby Mike.Sheen » Wed Feb 20, 2008 11:50 am

We often get asked what hardware people should use for their SQL Server machine - usually when the performance is beginning to degrade and becomes an issue.

We usually find the performance is degrading because the hardware is not up to scratch. It may have worked fine, when first implemented, but as the database and number of users grew, their hardware didn't scale so well.

The first and most important rule is to have a dedicated SQL Server. This means that the machine is running SQL Server and nothing else. By doing so, you're able to tune the hardware in a much more granular and effective fashion than if the machine was acting as a mail, web, kitchen sink, etc server.

Now, what hardware specifications should the SQL server machine have ? This depends largely on the expected number of concurrent users, the size of the database, and the typical loads the server will face. The following recommendations are based on meeting the requirements of almost all our customers – 25 to 50 concurrent users, with a 10GB database size – and being used in loads that are typical of Jiwa customers – 25% sales order entry, 25% warehouse operations, 25% reporting, 10% Purchasing, 15% other.

CPU - As a general rule, the CPU doesn't need to be cutting edge - whilst your typical desktop class CPU would normally suffice, they don't suit the server class motherboards which have essential features such as ECC memory. So, we would recommend an Intel Xeon (single would suffice, but if you've got a budget to consume, dual socket would be a bonus).

Memory - As mentioned above ECC memory is recommended, and 4GB or more would be ideal. Keep in mind to access anything above 3.5GB, you would need Windows Server 2003 (or 2008) 64 bit and SQL Server 2005 (or 2008) 64 bit.

Disk - SCSI RAID. A good, caching SCSI raid controller. For best performance 15K drives. Probably several drives in RAID 1+0 for getting the most out of performance and redundancy.

EDIT : As per the post below by Hyperus, the disk configuration ideally would be :
C: - Raid-1 - (System, Swap, TMP, boot) - easy to snapshot for DR
D: - Raid-1 - (Databases, General Data)
E: - Raid-1 - (DB transaction Logs, System State Backups)


If you really want performance, make 3 raid 1+0 arrays instead :)

Don't be cheap and use a single disk. Don't expect performance from desktop class drives or raid controllers.

Power - Redundant power supplies aren't essential, but you'd be glad you opted for them when a power supply fails (they all fail, given time).

Backup strategy - This is often overlooked, and always regretted. Formulate a backup strategy for the data and the machine. Consult your system engineer to devise a backup and disaster recovery strategy that would work for your business.

Make sure that your strategy includes regular tests. About every 6 months or so, we hear from a customer who discovers - after their server is stolen, burnt to the ground, or simply failed - that the backups they have dutifully been making do not actually contain the files they need to recover.

Maintain a healthy sense of paranoia when it comes to your backups - check them once a month to ensure you could recover your data, if your server suddenly went away one day.

If anyone else has some recommendations, feel free to make them!
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Recommended Hardware Specifications for SQL Server

Postby Hyperus » Thu Feb 21, 2008 10:38 am

Just on the subject regarding backups, I thought that it would be worth mentioning products I have deployed on a great deal of sites. These products do not cost the earth, and give the same protection as much more expensive products.

The first is a Disaster Recovery product that will take a live disk copy (snapshot) of your boot drive (usually drive C:) while the system is running - without disrupting business operations. In the past - products that did this such as Symantec v2i cost as much as $2000 per server license. This product does the same thing for 89 Euro per server (around AU$150) and can also be scripted to run out of hours without disrupting server operations.

Note that the uninterrupted snapshots relies on a choice of different Shadow Copy technologies (either Microsoft VSS or the DriveSnapShot own custom VSS-like technology). Completely uninterrupted operation requires Microsoft SQL server 2005 SP2 and/or Microsoft Exchange Server 2003 SP2 (these versions fully support Microsoft VSS technology for snapshots). Microsoft SQL 2000 Server must be stopped for 60 seconds (approx) to allow the snapshot to start if using SQL2000 version - SQL2000 can be restarted again approximately 60 seconds into the snapshot while the snapshot is still running without issues. This product can be downloaded/trialled/purchased from here :-
http://www.drivesnapshot.de/en/index.htm

The next product that technically is not a backup application, but more a GUI/Shell. It interfaces to Microsoft NTBackup for backups of regular data, Microsoft Exchange Server Databases, and Microsoft SQL2000/2005 Databases. This was developed to de-mystify Microsoft NTBackup a little and make backups somewhat easier for the average person without having to be a brain surgeon. It assists you with different types of backup rotations and helps you even print out the correct labels for your tapes !! :). The base product is AU$329, and the SQL addon module is AU$195. Once again, you can try before you buy to see if it is going to do what you require.

http://www.backupassist.com/

Quick summary of what you should be backing up :-
1. Disaster Recovery Image of your C: Boot Drive - once a week
2. System State/Active Directroy Database Backup - nightly
3. Daily Data/Exchange Databases/SQL Databases - nightly

If each of these 3 are backed up in this way, complete disaster recovery is a reasonably simple 3 step process, providing you have comparable hardware to perform your DR/Data restores. You will more than likely need professional assistance for the DR restore operations, but as long as you have followed a few simple precautions for your backups, everything should be well covered.

With tapes, you should ensure you have a minimum of 15 tapes and use them in a rotation scheme explained in the free BackupAssist tutorials - they have a lot of great advice regarding this on their web site. 15 tapes can give you up to 2 years backup protection !. You should also ensure most tapes are kept in an asbestos-lined data-protection-certified fire-resistant safe. At least 1 backup tape should be kept off site at all times at a minimum.

It is also worth noting for the above to work well, you should consider separate drives for different operations.... We normally use something like this (just a really rough guide):-

C: - Raid-1 - (System, Swap, TMP, boot) - easy to snapshot for DR
D: - Raid-1 - (Databases, General Data)
E: - Raid-1 - (DB transaction Logs, System State Backups, DR Snapshots of C:)

This makes it easy to snapshot C: drive alone for DR snapshots and separates the liability for databases on the rare occasion that disaster strikes. This does not take into account specific requirements for a company - so is just an example.

/Roy Adams
Hyperus
Occasional Contributor
Occasional Contributor
 
Posts: 23
Joined: Mon Feb 18, 2008 1:12 pm
Location: Shailer Park, Queensland


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest