We have a customer that we will be moving from Server 2003 Enterprise with SQL Server 2000 to Server 2008 R2 with SQL Server 2008 R2. There's a lot of back story as to how we got here, but we're finally completing what began a couple years ago as a hardware upgrade reusing the original Windows Server & SQL Server (due to compatibility issues with their software). At the time we originally set this up, we were limited as to our options due to having to move the old Windows installation to the new system, and due to it also at the time acting as the DHCP, DNS & domain controller.
We have since installed an SBS 2011 system that has taken over all domain management roles, so we have the option to completely reconfigure this server prior to installing the new versions of Windows and SQL server.
The server is Intel-based, with dual Xeon X5560's on an S5520HC motherboard with an LSI 8708EM2 RAID card (with the optional battery backup) and 6 x 500GB hard drives in RAID 10 (the hard drives are SATAII Seagate Constellation ES - please don't lecture me about SAS, I could not get them to spend the money on SAS drives at the time this was built) and as a part of this upgrade we're also replacing the original memory with 48GB (6 x 8GB for full 6-channel mode) of Registered ECC memory.
I'm looking for opinions on how best to setup the hard drives. The entire RAID array is currently setup as one drive with one partition. We're planning on at least recreating the partition using the Server 2008 setup rather than leaving the current file system in place, and I was debating partitioning to separate the OS from SQL, which raised a number of questions. They currently have 4 x 25GB databases and about 30 users. One of the databases is used daily with the other three active as prior year-end snapshots for reference only as needed.
1) With the server only acting as an SQL & File server, I want to verify that it is in fact best to partition the drives, and I'm looking for suggestions as to how best to do so. We have about 1.3TB total to work with. I was thinking 3 partitions - System, SQL Storage, and a 3rd for file shares and SQL Server database backups.
2) For partitioning, should we leave the 6-drive RAID 10 array in place or reconfigure the setup? In either case, is it best to handle any partitioning in the RAID controller or to let Windows create the partition(s)?
I may be over-thinking things, but I figure since we have the chance now to go the best route possible with the hardware that we might as well do so.