Hi All,

I'm looking for ways to increase the performance of our current database server.  We can't afford to purchase a new one, but can do some upgrading.  The server is an HP Proliant with one 3GHz Xeon processsor and 3GB RAM.  We have 5 72GB 10k RMP hard drives configured in two volumes via a hardware RAID controller - 3 in RAID5 for the OS (Windows 2003) and SQL Server files, 2 in RAID1 for the data files.  We have an EE/FE database that's 3.4GB and an RE database that's 120MB.  We run Netclassroom, FAWeb, and WebPurchasing off separate webservers.  We're currently running two instances of SQL2005Express, one for FE/EE and one for RE.  Currently, the data and log files for the Blackbaud databases are on the E: drive (the RAID1 volume) with nothing else there.  The OS files, SQL binaries, and System database files (tempdb, etc) are all on the C: drive (RAID5 volume).

I'll have the ability over the summer to completely rebuild the server if I need to, down to recreating the RAID volumes.  I have one open hard drive slot, free RAM slots, and a second open processor socket.  What would y'all recomend?  Should I upgrade to 4GB of RAM?  Should I add a second processor?  Should I buy a 6th HD and configure RAID1/RAID1/RAID1 volumes?  How about RAID1+0/RAID1?  Should I upgrade to SQL 2005 Standard (which I may have to do anyway, given our database size.)  If i do move to SQL 2005 Standard, do I need to run two separate instances (FE/EE & RE), or should I run them both in one instance?

If you'd like to share your server configurations, I'd love to hear what you're doing. In any case, thank you very much for your time & help.

 

Sincerely,

Brian King

 Hi Brian,

How many total users are you supporting with this server? How many simultaneous connections, on average? Any resource-intensive data mining, reporting, or integration happening after hours?

 A few general thoughts:

Max out the RAM. Find out the most your motherboard can support, and max it out.

Upgrade to SQL Server 2005 Standard. (Or 2008 if RE supports it by then.) SQLExpress is limited to using 1Gb of RAM, and won't allow a database to grow past 4Gb in size. In addition, you get a myriad of extra options for backing up and fine tuning your data. You can see all the differences here. Running all the databases in a single instance is fine.

Buy a sixth hard drive, and reconfigure your RAID arrays. Drive 1: No RAID, Just keep OS/Programs (including the SQL engine); Drive 2+3: RAID1, used for the transaction logs (.ldf files); Drive 4+5+6: RAID5, used for the database files (.mdf files). You'll be constantly writing to your transaction logs, especially if you've got the full recovery model enabled. But you'll be reading out of your database much more than you'll be writing back to it. RAID1 is much better at writing, and RAID5 is much better at reading. This configuration of RAID1 for T-Logs and RAID5 for data is pretty standard for most database servers. You'll find a lot of strong and differing opinions on this, but this is the configuration I've always used.

A second CPU is nice but not necessary. You can squeeze a lot of power out of a single socket. The upgrades to RAM and hard drives are far more cost effective at this point, and would result in a bigger bang for your buck. The same is true of uprading to SQL Standard. If you've got room in the budget after all that, then by all means, upgrade the proc, but if your budget is tight, save that for last.

That's about it!

I agree with most of what Ken said.

However, your OS/Programs drive needs to be protected on a RAID array as well. Otherwise, a loss of that single drive will bring your server down catastrophically. Given your limitations, I recommend three RAID 1 volumes. If you had room for more drives, I'd be suggesting RAID 0+1 for the database for maximum performance and sufficient protection, but that isn't an option the way things are.

I'm also not sure what benefit you would experience from having the tansaction logs for multiple databases living on the same separate array. The intent with putting logs on a separate array is so the drive heads don't have to spend time seeking and can just write sequentially. When you have multiple transaction logs on the same drive, the heads are going to have to move more. However, I don't see a better option.

You're going to see the most gains from upgrading to SQL Server Standard (see if TechSoup is an option for you) and adding RAM.

Also, moving towards a new server is going to eventually give you huge performance gains. Modern quad-core processors are amazing for databases and provide huge bang for your buck. I just picked up an HP server with dual quad core processors and 12 GB of RAM for only a few grand.

Mitch

Sigh - my original, highly detailed response got deleted.  Thanks for the tips, though!  On the memory - we're running 32-bit, can we still use more than 4GB of memory, or does EE run on Server 2003 64-bit?

Hi Brian, 

By default SQL Server will only use 2 GB of RAM With 32-bit Operating Systems.  However, you can configure SQL and the OS to utilize larger amounts of memory.  To use more than 2 GB of RAM, SQL Server requires that you enable AWE.  Which in turn requires that you add the /3GB switch to the boot.ini (or the /PAE switch if using more than 4GB of RAM).  You must also grant "Lock Pages in Memory" to the domain account that runs the SQL SErvers service.  See Microsoft Books Online for steps to enable AWE.  To get started take a look at http://msdn.microsoft.com/en-us/library/ms175581(SQL.90).aspx.

Current versions of the Education Edge are supported with 64-bit operating systems and SQL SErver.  SQL Server 64-bit will use more than 2 GB of RAM by default so enabling AWE is not required.