SQL Server Conundrum

Hi Wes,

The 3GHz recommendation is based on an older CPU generation.

Nowadays we can reach the once "3GHz" performance target with a 1.8GHz CPU.

I'd pick the following:

1-2x E5-2430 - These CPU's reach 2.7GHz in Turbo mode, have a fast QPI link and adequate amounts of L3 cache (15MB). Great for SQL OLTP usage. Doubling the cores will not make up for the lack of speed - it will instead make larger transactions (generating reports) run faster.

8x HDD's - Group 4 to 6 of these in RAID10 and dedicate them to the Data volume where the database(s) will reside. This is also where you want to install SQL. You don't need crazy capacity disks but that all depends on the retention settings configured in Orion and the current size of the database. Note: Larger capacity disks generally provide more IOPs. Use the remaining disks for OS and dedicate at least one disk for SQL Logs/Temp. FYI: If you choose SSD's, make sure the controller supports them, this is very important.

32GB RAM - Adequate but also keeps the cost down for use with Win2k8 R2 Standard & SQL 2008 R2 Standard editions.

I wouldn't recommend running SQL in a virtual environment, not even if the VM had a whole SAN dedicated to it. Too many performance bottlenecks when running such a database. Had it been a data warehouse type database then it'd be a different story, but it isn't... I would instead move everything to a physical setup, and you would want this for a monitoring platform that alerts you about issues, errors, downtime etc so that you avoid dependencies and minimize the performance impact on the existing SAN. At the same time, reclaim a lot of resources, specifically available disk IO. Basically, you want to be able to access your monitoring platform when your virtual environment, SAN and what not is down.

The Orion server performance is just as important, since the server is both a web server (which has its own set of requirements) and a polling engine. A quick way to alleviate some of the web load would be to deploy an additional web server.

Hi Deltona,

Thanks for the detailed response just another question, please forgive my ignorance but how would one configure the sql server to use a different disk/partition for the SQL Logs/Temp. Also would this need to be a seperate disk or for example could the following configuration of disks be used.

4xdisks RAID 10 for database data

2xdisks raid 1 partitioned with two partitions one for OS and one for Log storage.

Thanks

You can configure the location of the database, temp and log directories during the SQL installation. It is possible to change directory locations post install, too.

Your log directory should be on a separate disk to avoid IO contention, since every write must first go through the transaction log. It would be best if you can add one more disk and dedicate it to the logs.

File access to logs is sequential (not random), meaning it will be reading the start of a file all the way to the end, and for this, SSD's fit right in.

We previously had our installation on a VM database server, and performance was horrific.  Constant timeouts trying to retrieve resources and long waits for reports were the norm.  We had the same setup that your client is pushing for, a fibre channel SAN and fairly new dell ESXi hosts.  I would definitely push for a physical server with disk configuration as suggested by Deltona. 

Thanks Matt,

That was my thoughts as well, however i think the client wants to virtulise first as a test so i guess will suck it and see.

Thanks for the replies Matt & Deltona